游标 定义 及 分类 与 游标变量

一、游标定义

游标是一种 PL/SQL 控制结构；可以对SQL语句的处理进行显示控制，便于对表的行数据
逐条进行处理。
游标并不是一个数据库对象，只是存留在内存中
操作步骤：

逐条进行处理。
游标并不是一个数据库对象，只是存留在内存中
操作步骤：
• 声明游标
• 打开游标
• 取出结果，此时的结果取出的是一行数据
• 关闭游标
到底那种类型可以把一行的数据都装进来

• 此时使用 ROWTYPE类型，此类型表示可以把一行的数据都装进来。

二、游标分类

隐式游标、显式游标

隐式游标：是用户操作SQL时自动生成的，而显式游标是在声明块中直接定义的游标。

通过ROWCOUNT查看游标指向的行：

例：

DECLARE

v_count NUMBER;

BEGIN

SELECT COUNT(*) INTO v_count from dept;

--返回一个隐式游标

dbms_output.put_line('SQL%ROWCOUNT = '||SQL%COUNT);

END;

/

执行结果：SQL%ROWCOUNT = 1

因此可以看出系统自动生成一个隐式游标，这个隐式游标指向第1行。

使用ROWCOUNT对游标所操作的行数进行记录。

显式游标：使用游标前要先打开游标，所以最好打开前先判断游标是否打开。

IF mycur%ISOPEN THEN

NULL;--什么也不做

ELSE OPEN mycur;--开启游标

END IF;

在程序结束前要 显式地关闭游标，CLOSE mycur;

例：

--1: 使用游标 和 loop 循环来显示所有部门的名称
DECLARE
CURSOR mycur IS SELECT * FROM dept;
deptinfo dept%ROWTYPE;
BEGIN 
  IF mycur%ISOPEN THEN
    NULL;
    ELSE 
      OPEN mycur;
  END IF;
  LOOP FETCH mycur INTO deptinfo;
  EXIT WHEN mycur%NOTFOUND;
  dbms_output.put_line('部门名称：'||deptinfo.dname);
  END LOOP;
  END;
/




--2： 使用游标 和 loop 循环来显示所有部门的的地理位置（用%found 属性）
DECLARE
CURSOR mycur IS SELECT * FROM dept;
deptinfo dept%ROWTYPE;
BEGIN
   IF mycur%ISOPEN THEN
    NULL;
    ELSE 
      OPEN mycur;
   END IF;
   FETCH mycur INTO deptinfo;--游标移动到下一行
     WHILE(mycur%FOUND)LOOP--这一行如果有数据，进LOOP
       dbms_output.put_line(deptinfo.dname||' 的地理位置是：'||deptinfo.loc);
       FETCH mycur INTO deptinfo;--输出完，接着移动到下一行，进行while判断
     END LOOP;
   END;
/


--3:接收用户输入的部门编号，用 for 循环和游标，打印出此部门的所有雇员的所有信息
--问题：rowtype 只返回一行记录，如何返回多条记录？
--把 empinfo 作为一张表，再创建一个游标，嵌套for循环，遍历每一条记录
DECLARE
 deptno emp.deptno%TYPE ;
 empinfo emp%ROWTYPE;
 empinfos empinfo%ROWTYPE;
 CURSOR mycur IS SELECT * FROM emp;
 CURSOR mycur2 IS SELECT * FROM empinfo;
BEGIN
  deptno := &en ;
  SELECT * INTO empinfos FROM (SELECT * INTO empinfo FROM emp WHERE emp.deptno = deptno);
  FOR empinfo IN mycur LOOP
    FOR empinfos IN mycur2 LOOP
    dbms_output.put_line(empinfo.ename||empinfo.job||empinfo.sal);
    END LOOP;
  END LOOP;
END;
/
--游标传参数(重名 或 查询条件 和 参数名 不一致都会出错)
DECLARE
 CURSOR mycur(no NUMBER) IS SELECT * FROM emp WHERE emp.deptno = no;
 e emp%ROWTYPE;
BEGIN
  FOR e IN mycur(10) LOOP
    dbms_output.put_line('员工：'||e.ename||e.deptno);
  END LOOP;
END;
/
--写活，可以传入任何部门编号
DECLARE
 CURSOR mycur(no NUMBER) IS SELECT * FROM emp WHERE emp.deptno = no;
 e emp%ROWTYPE;
BEGIN
  FOR e IN mycur(&no) LOOP
    dbms_output.put_line('员工：'||e.ename||e.deptno);
  END LOOP;
END;
/
--网络资源
declare 
      CURSOR 
      c_dept(deptno NUMBER(2))
      is
      select * from emp where emp.deptno = deptno;
      e emp%rowtype;
begin
        FOR e in c_dept(to_number(20)) loop
            dbms_output.put_line('员工号：'||e.EMPNO||'员工名：'||e.ENAME||'工资：'||e.SAL);
        end loop;
end;
/
--4：向游标传递一个工种，显示此工种的所有雇员的所有信息
-- myjob varchar2\varchar\nvarchar都行
DECLARE 
 CURSOR mycur(myjob VARCHAR) IS SELECT * FROM emp WHERE emp.job = myjob;
 e emp%ROWTYPE;
BEGIN
  FOR e IN mycur('&job') LOOP
--真正做到传入一个工种，查询该工种下所有人信息
--FOR e IN mycur('MANAGER') LOOP
    dbms_output.put_line('名字：'||e.ename);
  END LOOP;
END;



--写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0
CREATE OR REPLACE FUNCTION empfun(en emp.ename%TYPE) RETURN NUMBER
AS is_exist NUMBER;
BEGIN
  SELECT COUNT(*) INTO is_exist FROM emp WHERE ename  = UPPER(en);
  RETURN is_exist;
  END;
/


SELECT empfun('smith') FROM dual;


--1.写一个函数，传入员工编号，返回所在部门名称--必须为返回值指定 类型 和 范围
CREATE OR REPLACE FUNCTION myempno(eno emp.empno%TYPE) RETURN VARCHAR2 
  AS ddname VARCHAR2(20);
 BEGIN
   SELECT dept.dname INTO ddname FROM dept JOIN emp ON(dept.deptno = emp.deptno AND emp.empno = eno);
 RETURN ddname;
 END;
/
SELECT myempno(7369) FROM dual; 


--2.写一个函数，传入时间，返回入职时间比这个时间早的所有员工的平均工资
CREATE OR REPLACE FUNCTION mydate(edate emp.hiredate%TYPE ) RETURN NUMBER
AS avgsal NUMBER;
BEGIN
  SELECT AVG(sal) INTO avgsal FROM emp WHERE edate > emp.hiredate;
  RETURN avgsal;
  END;
/
--传入格式不匹配，传入参数类型或个数错误
SELECT mydate(1981/4/2) FROM dual;


--使用嵌套表接受游标数据
DECLARE
   TYPE dept_nested IS TABLE OF dept%ROWTYPE;--定义dept嵌套表
   v_dept dept_nested;
   CURSOR cur_dept IS SELECT * FROM dept;
BEGIN
   IF cur_dept%ISOPEN THEN 
     NULL;
   ELSE
     OPEN cur_dept;
   END IF;
   FETCH cur_dept BULK COLLECT INTO v_dept;--保存整个游标
   FOR x IN v_dept.first.. v_dept.last LOOP
     dbms_output.put_line(v_dept(x).deptno||'，名称：'||v_dept(x).dname||',位置：'||v_dept(x).loc);
   END LOOP;
   CLOSE cur_dept;
END;
/
--这种操作即使在游标关闭了之后，数据也可以使用，但是数据不应该太大。
--如果害怕数据取大了，那么最简单的做法是使用可变数组保存。
--fetch 游标名称 bulk collect into .. limit,可以限制取得数据量
DECLARE
   TYPE dept_varray IS VARRAY(2) OF dept%ROWTYPE;--定义dept可变数组
   v_dept dept_varray;
   CURSOR cur_dept IS SELECT * FROM dept;
   v_rows NUMBER :=2; --每次取两行记录
   v_count NUMBER := 1;--每次少显示1行记录,偏移量，一般工程中常用的操作，并不直接把2改1
BEGIN
   IF cur_dept%ISOPEN THEN 
     NULL;
   ELSE
     OPEN cur_dept;
   END IF;
   FETCH cur_dept BULK COLLECT INTO v_dept LIMIT v_rows;--保存指定多少行的数据
   CLOSE cur_dept;
 --FOR x IN v_dept.first.. v_dept.last LOOP
 FOR x IN v_dept.first.. (v_dept.last-v_count) LOOP
     dbms_output.put_line(v_dept(x).deptno||'，名称：'||v_dept(x).dname||',位置：'||v_dept(x).loc);
   END LOOP;
END;
/


--record 类型
declare
    type type_record_dept is record
    (
    deptno dept.deptno%type,
 dname dept.dname%type,
    loc dept.loc%type
    );--声明了一个新的变量类型




    v_temp type_record_dept;
begin
  v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(v_temp.deptno||'  '||v_temp.dname);
end;
/


CREATE TABLE emp2 AS SELECT * FROM emp;


begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;

for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
/


create sequence seq_errorlog minvalue 0 start with 0 increment by 1;
CREATE TABLE errorlog(ID NUMBER(10));


declare
  v_errcode number;
  v_errmsg varchar2(1024);
begin
  dbms_output.put_line(2/0);--dml
exception
  when others then
    rollback;
    v_errcode:=SQLCODE;--出错代码  
    v_errmsg:=SQLERRM;--出错信息
    insert into errorlog values(seq_errorlog.nextval,user,v_errcode,v_errmsg,sysdate);
    commit;
END;

三、游标变量

定义游标时 加 return 是强类型定义，不加 return 是弱类型定义，强类型定义表示返回值类型必须和return要求的返回类型一致，弱类型可以动态决定操作返回值类型。

在使用游标变量时，for不能用，loop可以用。

变量声明类型：

1、%TYPE类型

例：name emp.ename%TYPE,表示声明了一个和emp表中ename字段的数据类型一致的变量name.

2、table数组类型

TYPE type_table_emp_empno is table of emp.empno%TYPE index of binary_integer;

说明：

TYPE关键字，说明一种数组类型

type_table_emp_empno类型名

is table of 是...的数组

emp.empno%TYPE数组中的数据类型

 index of binary_integer;索引下表是整数

例：v_empnos type_table_emp_empno;--使用自定义的数组类型

在BEGIN块中赋值   v_empnos(0) := 7369;  v_empnos(1) :=8888;   v_empnos(-1) := 9999;

3、record类型

TYPE type_record_dept is record (字段1   类型， 字段2   类型...) v_temp type_record_dept;

使用record变量类型时，如果表的结构发生改变（字段增删），需要从新定义record类型，比较麻烦，可以用ROWTYPE声明record变量类型。

4、%ROWTYPE类型

例：v_name emp%ROWTYPE,表示返回emp表中一行记录。

/