源码-PL/SQL从入门到精通-第十章-使用游标-Part 2

虽说下个项目中要用到PL/SQL，但还不清楚需要用到哪些具体的知识点？估计游标是不可或缺了，如果能在现有代码的基础上改一改就能满足项目要求，现在的工作也就更有价值了。

--代码10.14 基本的Loop循环结构
DECLARE
   dept_row dept%ROWTYPE;                      --定义游标结果记录变量
   CURSOR dept_cursor IS SELECT * FROM dept;   --定义游标变量
BEGIN 
   OPEN dept_cursor;                           --打开游标
   LOOP                                        --简单循环
      FETCH dept_cursor INTO dept_row;         --提取游标数据
      EXIT WHEN dept_cursor%NOTFOUND;          --退出循环的控制语句
      DBMS_OUTPUT.PUT_LINE('部门名称：'||dept_row.dname);
   END LOOP;
   CLOSE dept_cursor;                          --关闭游标
END;   

--代码10.15 使用WHILE循环循环检索游标数据
DECLARE
   dept_row dept%ROWTYPE;                      --定义游标结果记录变量
   CURSOR dept_cursor IS SELECT * FROM dept;   --定义游标变量
BEGIN 
   OPEN dept_cursor;                           --打开游标
   FETCH dept_cursor INTO dept_row;            --提取游标数据   
   WHILE dept_cursor%FOUND  LOOP                 
      DBMS_OUTPUT.PUT_LINE('部门名称：'||dept_row.dname);
      FETCH dept_cursor INTO dept_row;         --提取游标数据         
   END LOOP;
   CLOSE dept_cursor;                          --关闭游标
END;   


--代码10.16 使用游标For循环检索数据
DECLARE
   CURSOR dept_cursor IS SELECT * FROM dept;   --定义游标变量
BEGIN 
   FOR dept_row IN dept_cursor LOOP            --在游标FOR循环中检索数据
     DBMS_OUTPUT.PUT_LINE('部门名称：'||dept_row.dname);
   END LOOP;
END;   

--代码10.17 游标For循环子查询语句（简洁就是好！）
BEGIN 
   FOR dept_row IN (SELECT * FROM dept) LOOP    --在游标FOR循环中检索数据
     DBMS_OUTPUT.PUT_LINE('部门名称：'||dept_row.dname);
   END LOOP;
END;   

--代码10.18 使用游标更新数据
DECLARE
   CURSOR emp_cursor (p_deptno IN NUMBER)
   IS
      SELECT  *
            FROM emp
           WHERE deptno = p_deptno
      FOR UPDATE;                              --使用FOR UPDATE子句添加互斥锁
BEGIN
   FOR emp_row IN emp_cursor (20)              --使用游标FOR循环检索游标
   LOOP
      UPDATE emp                      
         SET comm = comm * 1.12
       WHERE CURRENT OF emp_cursor;            --使用WHERE CURRENT OF更新游标数据
   END LOOP;
   COMMIT;                                     --提交更改
END;

--代码10.18 使用游标删除数据
DECLARE
   CURSOR emp_cursor (p_empno IN NUMBER)
   IS
      SELECT  *
            FROM emp
           WHERE empno = p_empno 
      FOR UPDATE;                              --使用FOR UPDATE子句添加互斥锁
BEGIN
   FOR emp_row IN emp_cursor (7369)              --使用游标FOR循环检索游标
   LOOP
    DELETE FROM emp
       WHERE CURRENT OF emp_cursor;            --使用WHERE CURRENT OF删除游标数据
   END LOOP;
END;

--代码10.20 游标变量使用示例
DECLARE
   TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE;       --定义游标变量类型
   emp_cur   emp_type;                                   --声明游标变量
   emp_row   emp%ROWTYPE;                                --定义游标结果值变量
BEGIN
   OPEN emp_cur FOR SELECT * FROM emp;                   --打开游标
   LOOP
      FETCH emp_cur INTO emp_row;                        --循环提取游标数据
      EXIT WHEN emp_cur%NOTFOUND;                        --循环退出检测
      DBMS_OUTPUT.put_line ('员工名称：' || emp_row.ename);
   END LOOP;
END;

--代码10.21 定义游标变量
DECLARE
   TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE;      --定义游标类型
   TYPE gen_type IS REF CURSOR;                         
   emp_cur emp_type;                                   --声明游标变量
   gen_cur gen_type;
BEGIN
   OPEN emp_cur FOR SELECT * FROM emp WHERE deptno=20;
END;   
   

DECLARE
    gen_type SYS_REFCURSOR;
BEGIN
END;       

--代码10.22 大咖游标变量示例
DECLARE
   TYPE emp_curtype IS REF CURSOR;                       --定义游标类型
   emp_cur emp_curtype;                                  --声明游标类型的变量
BEGIN
   OPEN emp_cur FOR SELECT * FROM emp;                   --打开游标,查询emp所有列
   OPEN emp_cur FOR SELECT empno FROM emp;               --打开游标,查询emp表empno列 
   OPEN emp_cur FOR SELECT deptno FROM dept;             --打开游标,查询dept表deptno列
END;


--使用Fetch语句提取游标变量数据
DECLARE
   TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE;       --定义游标类型
   emp_cur emp_type;                                     --声明游标变量
   emp_row emp%ROWTYPE;
BEGIN
   IF NOT emp_cur%ISOPEN THEN                            --如果游标变量没有打开
   OPEN emp_cur FOR SELECT * FROM emp WHERE deptno=20;   --打开游标变量
   END IF;
   LOOP
     FETCH emp_cur INTO emp_row;                         --提取游标变量
     EXIT WHEN emp_cur%NOTFOUND;                         --如果提取完成则退出循环
     DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename
                         ||' 员工职位：'||emp_row.job);  --输出员工信息
   END LOOP;
END;   


--使用close语句关闭游标变量（不关闭也不会报错）
DECLARE
   TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE;       --定义游标类型
   emp_cur emp_type;                                     --声明游标变量
   emp_row emp%ROWTYPE;
BEGIN
   OPEN emp_cur FOR SELECT * FROM emp WHERE deptno=20;   --打开游标
   FETCH emp_cur INTO emp_row;                           --提取游标
   WHILE emp_cur%FOUND LOOP                              --循环提取游标
     DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename);
     FETCH emp_cur INTO emp_row;       
   END LOOP;
   CLOSE emp_cur;                                        --关闭游标
END;   

--以下代码不能通过编译（使用fetch语句前，应先使用open语句）
DECLARE
   TYPE emp_curtype IS REF CURSOR;                       --定义游标类型
   emp_cur emp_curtype;                                  --声明游标类型的变量
   emp_row emp%ROWTYPE;
BEGIN   
   FETCH emp_cur INTO emp_row;
END;

--代码10.25 处理Invalid_cursor异常（不太明白）
DECLARE
   TYPE emp_curtype IS REF CURSOR;                       --定义游标类型
   emp_cur1 emp_curtype;                                 --声明游标类型的变量
   emp_cur2 emp_curtype;
   emp_row emp%ROWTYPE;                                  --定义保存游标数据的记录类型
BEGIN   
   OPEN emp_cur1 FOR SELECT * FROM emp WHERE deptno=20;  --打开第1个游标
   FETCH emp_cur1 INTO emp_row;                          --提取并显示游标信息
   DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename||' 部门编号：'||emp_row.deptno);
   FETCH emp_cur2 INTO emp_row;                          --提取第2个游标变量将引发异常
EXCEPTION
   WHEN INVALID_CURSOR THEN                              --异常处理
      emp_cur2:=emp_cur1;                                --将emp_cur1指向的查询区域赋给emp_cur2
      FETCH emp_cur2 INTO emp_row;                       --现在emp_cur1与emp_cur2指向相同的查询
      DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename||' 部门编号：'||emp_row.deptno);
      OPEN emp_cur2 FOR SELECT * FROM emp WHERE deptno=30; --重新打开emp_cur2游标变量，利用相同的查询区域
      FETCH emp_cur1 INTO emp_row;                         --由于emp_cur1与emp_cur2共享相同的查询区域，因此结果相同
      DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename||' 部门编号：'||emp_row.deptno);      
END;


--处理Rowtype_mismatch异常
DECLARE
   TYPE emp_curtype IS REF CURSOR;                       --定义游标类型
   emp_cur emp_curtype;                                  --声明游标类型的变量
   emp_row emp%ROWTYPE;                                  --声明游标数据结果类型
   dept_row dept%ROWTYPE;                               
BEGIN   
   OPEN emp_cur FOR SELECT * FROM emp WHERE deptno=20;   --打开游标变量
   FETCH emp_cur INTO dept_row;                          --提取到一个不匹配的类型中
EXCEPTION
   WHEN ROWTYPE_MISMATCH THEN                            --处理ROWTYPE_MISMATCH异常
     FETCH emp_cur INTO emp_row;                         --再次提取游标变量数据，输出结果
     DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename||' 部门编号：'||emp_row.deptno);       
END;


--使用Sys_refcursor类型
DECLARE
   emp_cur SYS_REFCURSOR;                               --定义弱类型游标变量
   emp_row emp%ROWTYPE;
   dept_row dept%ROWTYPE;
BEGIN   
   OPEN emp_cur FOR SELECT * FROM emp WHERE deptno=20;  --打开游标数据
   FETCH emp_cur INTO dept_row;
EXCEPTION
   WHEN ROWTYPE_MISMATCH THEN                           --处理ROWTYPE_MISMATCH异常
     FETCH emp_cur INTO emp_row;                        --重新提取并输出异常结果
     DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename||' 部门编号：'||emp_row.deptno);       
END;

--代码10.28 在包中使用游标变量
--创建包规范
CREATE OR REPLACE PACKAGE emp_data_action AS
   TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE;         --定义强类型游标类型
   --定义使游标变量的子程序  
   PROCEDURE getempbydeptno(emp_cur IN OUT emp_type,p_deptno NUMBER); 
END emp_data_action;

--实现包体
CREATE OR REPLACE PACKAGE BODY emp_data_action AS
   --创建在包规范中定义的过程   
   PROCEDURE getempbydeptno(emp_cur IN OUT emp_type,p_deptno NUMBER) IS
     emp_row emp%ROWTYPE;
   BEGIN
     OPEN emp_cur FOR SELECT * from emp WHERE deptno=p_deptno;  --打开游标变量
     LOOP
       FETCH emp_cur INTO emp_row;                              --提取数据
       EXIT WHEN emp_cur%NOTFOUND;
       --输出游标数据
       DBMS_OUTPUT.PUT_LINE('员工名称：'||emp_row.ename||' 部门编号：'||emp_row.deptno);
     END LOOP;
     CLOSE emp_cur;
   END;
END emp_data_action;




DECLARE
   emp_cursors   emp_data_action.emp_type;         --定义在包中定义的游标类型
BEGIN
   emp_data_action.getempbydeptno (emp_cursors, 20);   --调用在包中定义的过程
END;



--创建包规范
CREATE OR REPLACE PACKAGE emp_data_action_err AS
   TYPE emp_type IS REF CURSOR RETURN emp%ROWTYPE;         --定义强类型游标类型
   emp_cur emp_type;
   --定义使游标变量的子程序  
   PROCEDURE getempbydeptno(emp_cur IN OUT emp_type,p_deptno NUMBER);   
END emp_data_action_err;



DECLARE
   TYPE emp_curtype IS REF CURSOR;                       --定义游标类型
   emp_cur emp_curtype;                                  --声明游标类型的变量
BEGIN   
   FOR emp_row IN emp_cur LOOP
      DBMS_OUTPUT.PUT_LINE(emp_row.ename);
   END LOOP;
END;

源码-PL/SQL从入门到精通-第十章-使用游标-Part 2

原文：http://blog.csdn.net/hpdlzu80100/article/details/51969354