oracle cursor (游标)

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;