PL/SQL游标详解

刚打开游标的时候，是位于一个空行，要用fetch into 才能到第一行。


只是要注意用更新游标的时候，不能在游标期间commit. 否则会报
ORA-01002: fetch out of sequence
      就是COMMIT;导致错误
       在打开有for update的cursor时，系统会给取出的数据加上排他锁(exclusive),
       这样在这个锁释放前其他用户不能对这些记录作update、delete和加锁。
       而我一旦执行了commit,锁就释放了，游标也变成无效的,再去fetch数据时就出现错误了。
       因而要把commit放在循环外，等到所有数据处理完成后再commit,然后关闭cursor


隐含游标
--------
又名SQL游标，用于处理单行select into 和 DML语句。
SQL%ISOPEN SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT


显示游标
--------
用户处理select语句返回的多行数据。
select语句返回多行数据处理方式：[1]显示游标；[2]select ... bulk collect into 集合变量...；


【1】显示游标属性


[1] %ISOPEN 检测游标是否打开。
[2] %FOUND 检测游标结果集是否存在数据。
[3] %NOTFOUND 是否不存在数据。
[4] %ROWCOUNT 游标已提取的实际行数。


【2】使用显示游标


[1] 定义游标


CURSOR cursor_name IS select_statement;


[2] 打开游标


OPEN cursor_name;


[3] 提取数据


FECTH cursor_name INTO variable,...;


[4] 关闭数据


CLOSE cursor_name;


使用标量变量：
-------------

[sql] view plaincopy
 
  1. DECLARE  
  2. Cursor emp_cur IS select ename,sal from emp order by empno;  
  3. v_ename emp.ename%TYPE;  
  4. v_sal   emp.sal%TYPE;  
  5. BEGIN  
  6. IF NOT emp_cur%ISOPEN THEN  
  7.    OPEN emp_cur;   
  8.    DBMS_OUTPUT.PUT_LINE('打开游标');  
  9. END IF;  
  10. NULL;  
  11. LOOP  
  12.    FETCH emp_cur INTO v_ename,v_sal;  
  13.    EXIT WHEN emp_cur%NOTFOUND;  
  14.    DBMS_OUTPUT.PUT_LINE('用户名：'||v_ename||'，工资：'||v_sal);  
  15. END LOOP;  
  16. NULL;  
  17. IF emp_cur%ISOPEN THEN  
  18.    CLOSE emp_cur;  
  19.    DBMS_OUTPUT.PUT_LINE('关闭游标');  
  20. END IF;  
  21. END;  


使用PLSQL记录变量
-----------------

[sql] view plaincopy
 
  1. DECLARE  
  2. Cursor emp_cur IS select ename,sal from emp order by empno;  
  3. emp_record emp_cur%ROWTYPE;  
  4. BEGIN  
  5. IF NOT emp_cur%ISOPEN THEN  
  6.    OPEN emp_cur;   
  7.    DBMS_OUTPUT.PUT_LINE('打开游标');  
  8. END IF;  
  9. NULL;  
  10. LOOP  
  11.    FETCH emp_cur INTO emp_record;  
  12.    EXIT WHEN emp_cur%NOTFOUND;  
  13.    DBMS_OUTPUT.PUT_LINE('用户名：'||emp_record.ename||'，工资：'||emp_record.sal);  
  14. END LOOP;  
  15. NULL;  
  16. IF emp_cur%ISOPEN THEN  
  17.    CLOSE emp_cur;  
  18.    DBMS_OUTPUT.PUT_LINE('关闭游标');  
  19. END IF;  
  20. END;  


使用PLSQL集合变量
-----------------

[sql] view plaincopy
 
  1. DECLARE  
  2. Cursor emp_cur IS select ename,sal from emp order by empno;  
  3. TYPE emp_table_type IS TABLE OF emp_cur%ROWTYPE INDEX BY BINARY_INTEGER;  
  4. emp_table emp_table_type;  
  5. i number;  
  6. BEGIN  
  7. i := 1;  
  8. IF NOT emp_cur%ISOPEN THEN  
  9.    OPEN emp_cur;   
  10.    DBMS_OUTPUT.PUT_LINE('打开游标');  
  11. END IF;  
  12. NULL;  
  13. LOOP  
  14.    FETCH emp_cur INTO emp_table(i);  
  15.    EXIT WHEN emp_cur%NOTFOUND;  
  16.    DBMS_OUTPUT.PUT_LINE('用户名：'||emp_table(i).ename||'，工资：'||emp_table(i).sal);  
  17.    i := i + 1;  
  18. END LOOP;  
  19. NULL;  
  20. IF emp_cur%ISOPEN THEN  
  21.    CLOSE emp_cur;  
  22.    DBMS_OUTPUT.PUT_LINE('关闭游标');  
  23. END IF;  
  24. END;  


【3】循环游标


FOR record_name IN cursor_name|select_statement LOOP
   statement;
   ....
END LOOP;


使用循环游标
------------

[sql] view plaincopy
 
  1. declare   
  2. cursor emp_cusor is select ename,sal from emp where deptno = &no order by empno;  
  3. begin  
  4. for emp_record in emp_cusor loop  
  5.       dbms_output.put_line('姓名：'||emp_record.ename||'，工资：'||emp_record.sal);  
  6. end loop;  
  7. end;  
  8.   
  9. begin  
  10. for emp_record in (select ename,sal from scott.emp where deptno = &no order by empno) loop  
  11.       dbms_output.put_line('姓名：'||emp_record.ename||'，工资：'||emp_record.sal);  
  12. end loop;  
  13. end;  


【4】参数游标


CURSOR cursor_name(parameter_name datatype) IS select_statement; --只能制定类型，不能指定具体大小
OPEN cursor_name(参数值);
FECTH cursor_name INTO variable,...;
CLOSE cursor_name;


使用游标参数
------------



示例1：
------

[sql] view plaincopy
 
  1. declare   
  2. cursor emp_cursor(v_depnto number) is select ename,sal from scott.emp where deptno = v_depnto order by empno;  
  3. emp_record emp_cursor%rowtype;  
  4. v_dno number;  
  5. begin  
  6. v_dno := &no;  
  7. if not emp_cursor%isopen then  
  8.       open emp_cursor(v_dno);  
  9. end if;   
  10. null;  
  11. loop  
  12.         fetch emp_cursor into emp_record;   
  13.         exit when emp_cursor%notfound;  
  14.         dbms_output.put_line('姓名：'||emp_record.ename||'，工资：'||emp_record.sal);  
  15. end loop;  
  16. null;  
  17. if emp_cursor%isopen then  
  18.        close emp_cursor;  
  19. end if;  
  20. end;  

示例2
-----

[sql] view plaincopy
 
  1. declare   
  2. cursor emp_cursor(v_depnto number) is select ename,sal from scott.emp where deptno = v_depnto order by empno;  
  3. v_dno number;  
  4. begin  
  5. v_dno := &no;  
  6. for emp_record in emp_cursor(v_dno) loop  
  7.       dbms_output.put_line('姓名：'||emp_record.ename||'，工资：'||emp_record.sal);  
  8. end loop;  
  9. end;  


【5】更新、删除游标行

[sql] view plaincopy
 
  1. CURSOR cursor_name IS select_statement  
  2. FOR UPDATE [OF column_reference] [NOWAITE];   -- OF子句指定对特定表加锁。  
  3. UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;  
  4. DELETE table_name WHERE CURRENT OF cursor_name;  


使用游标更新数据
----------------

[sql] view plaincopy
 
  1. declare  
  2.        cursor test_cursor is select empno,ename,sal,deptno from scott.test for update;  
  3.        test_record test_cursor%rowtype;  
  4.        v_deptno number;  
  5.        v_sal test.sal%type;  
  6. begin  
  7.        v_deptno := &no;  
  8.        if not test_cursor%isopen then  
  9.           open test_cursor;  
  10.        end if;  
  11.        loop  
  12.           fetch test_cursor into test_record;  
  13.           exit when test_cursor%notfound;  
  14.           dbms_output.put_line('姓名：'||test_record.ename||'，旧工资：'||test_record.sal);  
  15.           if test_record.deptno = v_deptno then  
  16.              update scott.test set sal=2*sal where current of test_cursor;  
  17.           else  
  18.              update scott.test set sal=3*sal where current of test_cursor;  
  19.           end if;  
  20.        end loop;  
  21.        close test_cursor;  
  22. end;  
  23.          
  24. declare  
  25. cursor test_cursor is select empno,ename,sal,deptno from scott.test for update;  
  26.       v_deptno NUMBER:=&dno;  
  27. begin  
  28. for test_record in test_cursor loop  
  29.         if test_record.deptno = v_deptno then  
  30.                  dbms_output.put_line('姓名：'||test_record.ename||'，旧工资：'||test_record.sal);  
  31.                  update scott.test set sal = sal*1.5 where current of test_cursor;  
  32.            end if;  
  33.        end loop;  
  34. end;  



表test换成emp出现ORA-01410: 无效的 ROWID错误，什么原因？？？


使用游标删除数据
----------------

[sql] view plaincopy
 
  1. declare   
  2. cursor test_cursor(v_deptno number) is select deptno,empno,ename,comm from scott.test where deptno = v_deptno for update;  
  3. v_dno test.deptno%type := &dno;  
  4. begin  
  5.      for test_record in test_cursor(v_dno) loop  
  6.          if test_record.comm is null then  
  7.             dbms_output.put_line('用户名：'||test_record.ename||'部门：'||test_record.deptno);  
  8.             delete from scott.test where current of test_cursor;  
  9.          end if;  
  10.      end loop;       
  11. end;  

【6】游标变量


指向内存地址的指针。可以在打开游标时指定其所对应的SELECT语句，实现动态游标。


[1]定义REF CURSOR类型和游标变量：
Type ref_type_name IS REF CURSOR [RETURN return_type --必须是PL/SQL记录类型]; 
说明：如果指定RETURN子句，那么在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型匹配。
cursor_variable ref_type name;


SYS_REFCURSOR


[2]打开游标，指定对应的SELECT语句：
OPEN cursor_variable FOR select_statement;
[3]提取数据
FETCH cursor_variable INTO variable1,variable,...;
[4]关闭游标
CLOSE cursor_variable;


不使用RETURN子句
----------------

[sql] view plaincopy
 
  1. DECLARE  
  2. TYPE ref_type_table IS REF CURSOR;  
  3. v_cursor            ref_type_table;  
  4. emp_record          emp%rowtype;  
  5. BEGIN  
  6.      OPEN v_cursor FOR select * from emp where deptno=&no;  
  7.      LOOP  
  8.          FETCH v_cursor INTO emp_record;  
  9.          EXIT WHEN v_cursor%NOTFOUND;  
  10.          dbms_output.put_line('员工号：'||emp_record.ename||'部门号：'||emp_record.deptno);  
  11.      END LOOP;  
  12.      CLOSE v_cursor;  
  13. END;  


使用RETURN子句
--------------

[sql] view plaincopy
 
  1. DECLARE  
  2. emp_record          emp%rowtype;  
  3. TYPE ref_type_table IS REF CURSOR RETURN emp%rowtype;  
  4. v_cursor            ref_type_table;  
  5. BEGIN  
  6.      OPEN v_cursor FOR select * from emp where deptno=&no;  
  7.      LOOP  
  8.          FETCH v_cursor INTO emp_record;  
  9.          EXIT WHEN v_cursor%NOTFOUND;  
  10.          dbms_output.put_line('员工号：'||emp_record.ename||'部门号：'||emp_record.deptno);  
  11.      END LOOP;  
  12.      CLOSE v_cursor;  
  13. END;  
  14.   
  15. DECLARE  
  16. Type emp_record_type IS RECORD(  
  17.        ename emp.ename%TYPE,  
  18.        salary emp.sal%TYPE,  
  19.        deptno emp.deptno%TYPE);  
  20. emp_record emp_record_type;  
  21.   
  22. TYPE ref_type_table IS REF CURSOR RETURN emp_record_type;  
  23. v_cursor            ref_type_table;  
  24. BEGIN  
  25.      OPEN v_cursor FOR select ename,sal,deptno from emp where deptno=&no;  
  26.      LOOP  
  27.          FETCH v_cursor INTO emp_record;  
  28.          EXIT WHEN v_cursor%NOTFOUND;  
  29.          dbms_output.put_line('员工号：'||emp_record.ename||'，部门号：'||emp_record.deptno||'，工资：'||emp_record.salary);  
  30.      END LOOP;  
  31.      CLOSE v_cursor;  
  32. END;  


【7】使用游标批量获取


FETCH ... BULK COLLECT INTO ...[LIMIT row_number];


不限制行数
----------

[sql] view plaincopy
 
  1. DECLARE  
  2.      CURSOR emp_cursor(v_deptno number) IS SELECT * FROM EMP WHERE deptno = v_deptno;  
  3.      TYPE type_emp_table IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;  
  4.      emp_table type_emp_table;  
  5.      v_dno emp.deptno%TYPE;  
  6. BEGIN  
  7.      v_dno := &no;  
  8.      OPEN emp_cursor(v_dno);  
  9.      FETCH emp_cursor BULK COLLECT INTO emp_table;  
  10.      CLOSE emp_cursor;  
  11.      FOR i IN 1..emp_table.COUNT LOOP  
  12.          dbms_output.put_line('员工号：'||emp_table(i).ename||'工资：'||emp_table(i).sal);  
  13.      END LOOP;  
  14.      CLOSE emp_cursor;  
  15. END;  



限制行数
--------

[sql] view plaincopy
 
  1. DECLARE  
  2.      CURSOR emp_cursor(v_deptno number) IS SELECT * FROM EMP WHERE deptno = v_deptno;  
  3.      TYPE type_emp_table IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;  
  4.      emp_table type_emp_table;  
  5.      v_dno emp.deptno%TYPE;  
  6. BEGIN  
  7.      v_dno := &no;  
  8.      OPEN emp_cursor(v_dno);  
  9.      FETCH emp_cursor BULK COLLECT INTO emp_table LIMIT &2;  
  10.      CLOSE emp_cursor;  
  11.      FOR i IN 1..emp_table.COUNT LOOP  
  12.          dbms_output.put_line('员工号：'||emp_table(i).ename||'工资：'||emp_table(i).sal);  
  13.      END LOOP;  
  14.      CLOSE emp_cursor;  
  15. END;  





【8】使用CURSOR表达式


作用嵌套游标。


[sql] view plaincopy
 
  1. DECLARE  
  2.        CURSOR dept_emp_cursor(v_deptno number) IS   
  3.    SELECT dname,cursor(SELECT * FROM emp e WHERE e.deptno = d.deptno)   
  4.    FROM dept d WHERE deptno = v_deptno;  
  5.        TYPE emp_cursor_type IS REF CURSOR;  
  6.        emp_cursor emp_cursor_type;  
  7.        emp_record emp%ROWTYPE;  
  8.        v_name dept.dname%TYPE;  
  9.        v_dno emp.deptno%TYPE;  
  10. BEGIN  
  11.      v_dno := &no;  
  12.      OPEN dept_emp_cursor(v_dno);  
  13.      loop   
  14.           FETCH dept_emp_cursor INTO v_name,emp_cursor;  
  15.           EXIT WHEN dept_emp_cursor%NOTFOUND;  
  16.           dbms_output.put_line('部门名称：'||v_name);  
  17.           LOOP  
  18.               FETCH emp_cursor INTO emp_record;              
  19.               EXIT WHEN emp_cursor%NOTFOUND;  
  20.               dbms_output.put_line('员工名称：'||emp_record.ename||'，工资：'||emp_record.sal);  
  21.           END LOOP;  
  22.      end loop;  
  23.      CLOSE dept_emp_cursor;  
  24. END;  



转自：http://hi.baidu.com/tangwei%5Fbd/blog/item/f4adbe02e578391b738b6533.html

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jieqing/p/3338599.html