PL/SQL游标详解

2023-08-15 16:02:58

刚打开游标的时候，是位于一个空行，要用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;

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

  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记录变量
-----------------

  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集合变量
-----------------

  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;

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

  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. begin
  9. for emp_record in (select ename,sal from scott.emp where deptno = &no order by empno) loop
  10. dbms_output.put_line('姓名：'||emp_record.ename||'，工资：'||emp_record.sal);
  11. end loop;
  12. end;

【4】参数游标

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

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

示例1：
------

  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
-----

  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】更新、删除游标行

  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;

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

  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. declare
  24. cursor test_cursor is select empno,ename,sal,deptno from scott.test for update;
  25. v_deptno NUMBER:=&dno;
  26. begin
  27. for test_record in test_cursor loop
  28. if test_record.deptno = v_deptno then
  29. dbms_output.put_line('姓名：'||test_record.ename||'，旧工资：'||test_record.sal);
  30. update scott.test set sal = sal*1.5 where current of test_cursor;
  31. end if;
  32. end loop;
  33. end;

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

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

  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子句
----------------

  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子句
--------------

  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. DECLARE
  15. Type emp_record_type IS RECORD(
  16. ename emp.ename%TYPE,
  17. salary emp.sal%TYPE,
  18. deptno emp.deptno%TYPE);
  19. emp_record emp_record_type;
  20. TYPE ref_type_table IS REF CURSOR RETURN emp_record_type;
  21. v_cursor            ref_type_table;
  22. BEGIN
  23. OPEN v_cursor FOR select ename,sal,deptno from emp where deptno=&no;
  24. LOOP
  25. FETCH v_cursor INTO emp_record;
  26. EXIT WHEN v_cursor%NOTFOUND;
  27. dbms_output.put_line('员工号：'||emp_record.ename||'，部门号：'||emp_record.deptno||'，工资：'||emp_record.salary);
  28. END LOOP;
  29. CLOSE v_cursor;
  30. END;

【7】使用游标批量获取

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

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

  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;

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

  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表达式

作用嵌套游标。

  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

上一篇：Web 页面性能分析笔记


下一篇：Asp.Net MVC4+EF6 Code First 权限管理系统 源码下载