Oracle 游标的使用(二)

Sql代码  

--ref 游标
/*
    显示游标 多行
    隐含游标 数据操纵语言 DML 和单行select into语句

    1.定义游标
        指定游标所对应的select结果集语句
        CURSOR cursor_name IS select_statement;
           select_statement:查询语句
    2.打开游标
        OPEN cursor_name;
         执行游标所对应的select_statement,将结果暂时存放在游标结果集中
    3.提取数据
         使用fetch语句提取游标数据，fetch每次只能提取一行；fetch..bulk collect into可以提取多行
         FETCH cursor_name into variable...;
             variable 接受游标数据的变量
         FETCH cursor_name BULK COLLECT INTO collection...;
             collection 接收游标数据的集合变量
    4.关闭游标
         提取完数据，关闭游标，释放结果集
         CLOSE cursor_name;



    *游标属性
      cursor_name%ISOPEN  游标是否已打开
      cursor_name%FOUND   FETCH语句执行后，用来判断是否有数据被提取
      cursor_name%NOTFOUND 同上
      cursor_name%ROWCOUNT 目前已经fetch的行数

*/
--隐含游标
DECLARE
  v_name varchar2(30);
BEGIN
  select ename into v_name from emp where empno = 7788;
  CASE
  WHEN SQL%FOUND THEN
    dbms_output.put_line('隐身游标的行数肯定是：'||SQL%ROWCOUNT||'，查询的结果:'||v_name);
    --隐身游标的行数肯定是：1，查询的结果:SCOTT
  END CASE;
END;

--显示游标

--fetch提取一行数据
DECLARE
   CURSOR emp_cs is select ename,job from emp;--声明游标
   v_name emp.ename%type;
   v_job  emp.job%type;
BEGIN
   OPEN emp_cs;
   LOOP--因为fetch只提取一条数据，所以用循环多次提取
        FETCH emp_cs INTO v_name,v_job;
        EXIT WHEN emp_cs%NOTFOUND;--当游标中没有数据时，退出循环
        dbms_output.put_line(emp_cs%ROWCOUNT||'--'||v_name||'--'||v_job);
   END LOOP;
   CLOSE emp_cs;
END;

--FETCH ...BULK COLLECTION 提取多行数据
DECLARE
   --声明存储多行数据的集合类型
   TYPE v_table_type IS TABLE OF VARCHAR2(30);
   name_table v_table_type;
   job_table v_table_type;
   CURSOR emp_cs IS select ename,job from emp;
BEGIN
   OPEN emp_cs;
   FETCH emp_cs BULK COLLECT INTO name_table,job_table; --两个嵌套表类型不需要初始化
   FOR i IN 1..name_table.count LOOP
       dbms_output.put_line(name_table(i)||'--'||job_table(i));
   END LOOP;
   CLOSE emp_cs;
END;

--分页提取，每次提取固定条数
DECLARE
   type v_table_type IS TABLE OF int;
   no_tab v_table_type;
   pageNo int :=5;--5条一页
   nums int:=0;--已经输出的条数
   CURSOR emp_cs IS select empno from emp;
BEGIN
   --OPEN emp_cs;
   IF NOT emp_cs%ISOPEN THEN
      OPEN emp_cs;
   END IF;
   LOOP--LIMIT语句限制每次提请的条数，从上次游标rowcount处开始
      FETCH emp_cs BULK COLLECT INTO no_tab LIMIT pageNo;
      --打印刚获取的数据（5条），右边界计算方法：游标总共fetch的条数-已经输出的条数
      FOR I IN 1..(emp_cs%ROWCOUNT - nums) LOOP
          dbms_output.put_line(no_tab(i));
      END LOOP;
      dbms_output.new_line;
      nums := emp_cs%ROWCOUNT;--从新设置已输出的条数
      /*
       --这一句放到最后，因为知道emp表中共有14条记录，可以分为3页，第3次Fecth只有4条数据，
       --这时候已经是NOTFOUND了，如果放到输出前面的话，后4条就不能被输出了
      */
      EXIT WHEN emp_cs%NOTFOUND;
   END LOOP;
   CLOSE emp_cs;
END;


--游标 记录变量
DECLARE
   CURSOR emp_cs IS select ename,job from emp;
   v_record emp_cs%ROWTYPE;--游标行类型定义record
BEGIN
   OPEN emp_cs;
   LOOP
      FETCH emp_cs INTO v_record;
      EXIT WHEN emp_cs%NOTFOUND;
      dbms_output.put_line(v_record.ename||'--'||v_record.job);
   END LOOP;
   CLOSE emp_cs;
END;


--参数游标 就是差不多相当与函数了 根据不同的参数值有不同的游标数据
--参数一般会在游标的select语句中使用，否则也就没意义了
/*
   CURSOR cursor_name(parametr_name datatype...) IS select_statement;
   *参数只能定义类型，不能定义长度，
*/

DECLARE
   CURSOR emp_cs(param1 varchar2) IS select ename from emp where job =param1;
   v_name emp.ename%type;
   v_job varchar2(30) := '&job';
BEGIN
   OPEN emp_cs(v_job);
   LOOP
       FETCH emp_cs INTO v_name;
       EXIT WHEN emp_cs%NOTFOUND;
       dbms_output.put_line(v_name);
   END LOOP;
   CLOSE emp_cs;
END;



--使用游标更新或删除数据
/*
   游标不仅可以查询数据，也可以修改数据，由于游标是操作的多条数据更新，为
   保证事物的原子性，我也不知道怎么称呼，反正就是防止游标中的数据行在修改的过程中
   有其它的事务修改这些列 要使用FOR UPDATE 子句
   如：游标中有5条数据，你这时正执行修改第3条数据，而这时另一个事务修改了第4条数据。
   而游标是临时缓存结果集，4的修改就不会被看到。这样就乱套了

   CURSOR cursor_name(param datatype) IS
    select_statement FOR UPDATE [OF column_reference][NOWAIT];

    FOR UPDATE在游标结果集数据行上加行共享锁，防止其它用户在相应
    行上执行DML操作。
*/
DECLARE
    CURSOR emp_cs IS select comm from emp FOR UPDATE;
    v_comm number(7,2);
BEGIN
    OPEN emp_cs;
    LOOP
       FETCH emp_cs INTO v_comm;
       EXIT WHEN emp_cs%NOTFOUND;
       IF v_comm is null THEN
          UPDATE emp SET comm=1 WHERE CURRENT OF emp_cs;
       END IF;
    END LOOP;
    CLOSE emp_cs;
END;
commit;

/*
游标for循环
简化了对游标的处理，使用游标for循环时，oracle会隐含的打开游标、
提取数据并关闭游标
*/
--1
DECLARE
    CURSOR emp_cs IS select ename,job from emp;
BEGIN
    FOR emp_record IN emp_cs LOOP--emp_record是隐含定义的记录变量名
        dbms_output.put_line(emp_cs%ROWCOUNT||'-'||emp_record.ename);
    END LOOP;
END;

--2 在游标for循环中直接是哦那个select语句定义游标
--不过这样就不能使用游标属性了，因为没有游标名字
BEGIN
    FOR emp_record IN (select ename,job from emp) LOOP
        dbms_output.put_line(emp_record.ename);
    END LOOP;
END;


/*
    使用游标变量
    使用显示游标时，需要在定义部分指定所对应的静态select语句；
    当使用游标变量时，就可在打开游标变量时指定其所对应的select语句。
    定义游标变量
    TYPE ref_type IS REF CURSOR[RETURN return_type];
    cursor_var ref_type;
*/
--exp1
DECLARE
    TYPE emp_cursor_type IS REF CURSOR; --定义一个游标变量类型
    emp_cs emp_cursor_type;--使用这个游标类型定义一个游标变量
    emp_record emp%ROWTYPE;
BEGIN
    OPEN emp_cs FOR select * from emp;--打开游标变量时指定其所对应的select语句
    LOOP
       FETCH emp_cs INTO emp_record;--取数据就和普通的游标一样了
       EXIT WHEN emp_cs%NOTFOUND;
       dbms_output.put_line(emp_record.ename);
    END LOOP;
    CLOSE emp_cs;
END;

--exp2 定义游标变量是指定返回类型（智能是record）
DECLARE
    TYPE v_record_type IS RECORD(
         name varchar2(10),sal number
    );
    TYPE v_cursor_type IS REF CURSOR RETURN v_record_type;--返回上面定义的record类型
    --声明一个record变量，一个cursor变量
    v_record v_record_type;
    v_cursor v_cursor_type;
BEGIN
    OPEN v_cursor FOR select ename,sal from emp where deptno=10;
    LOOP
       FETCH v_cursor INTO v_record;
       EXIT WHEN v_cursor%NOTFOUND;
       dbms_output.put_line(v_record.name||'-'||v_record.sal);
    END LOOP;
    CLOSE v_cursor;
END;