源码-Oracle数据库管理-第十七章-动态SQL语句-Part 3（多行查询语句）

当动态SELECT语句返回多行数据时，可以使用另一种动态SQL语句的语法，即，OPEN FOR语句，它可以执行动态SQL语句，返回一个游标类型。

--2017/01/08
--17.3 多行查询语句
--17.3.1 使用OPEN FOR语句
--代码17.11 使用OPEN FOR语句执行多行SELECT语句
DECLARE
   TYPE emp_cur_type IS REF CURSOR;       --定义游标类型
   emp_cur emp_cur_type;                    --定义游标变量
   v_deptno NUMBER(4) := '&deptno';          --定义部门编号替换变量
   v_empno NUMBER(4);                                         
   v_ename VARCHAR2(25);
BEGIN
   OPEN emp_cur FOR                       --打开动态游标
      'SELECT empno, ename FROM emp '||
      'WHERE deptno = :1'
   USING v_deptno;
   NULL;
END;


--17.3.2 使用FETCH 语句
--代码17.12 使用FETCH语句提取游标数据
DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;           --定义一个弱游标类型
  v_emp_cursor    EmpCurTyp;               --定义一个游标变量
  emp_record      emp%ROWTYPE;             --定义保存游标数据的记录类型
  v_stmt_str      VARCHAR2(200);           --定义一个变量保存动态SQL语句
  v_e_job         emp.job%TYPE:='职员';    --定义绑定变量值
BEGIN
  --定义动态SELECT语句和绑定占位符
  v_stmt_str := 'SELECT * FROM scott.emp WHERE job = :j';
  --使用动态SQL语句打开游标变量，用USING子句指定绑定变量
  OPEN v_emp_cursor FOR v_stmt_str USING v_e_job;
  --在一个LOOP循环中开始循环输出多行结果值
  LOOP
    FETCH v_emp_cursor INTO emp_record;      --将行记录写入到记录类型
    EXIT WHEN v_emp_cursor%NOTFOUND;         --游标提取完成退出循环
    DBMS_OUTPUT.put_line('员工工号：'||emp_record.empno||' '||'员工姓名：'||emp_record.ename);    
  END LOOP;
  CLOSE v_emp_cursor;                        --游标变量操作完成关闭游标变量以释放资源
END;
/


--17.3.3 关闭游标变量
DECLARE
   TYPE emp_cur_type IS REF CURSOR;      --定义游标类型
   emp_cur emp_cur_type;                    --定义游标变量
   v_deptno NUMBER(4) := &deptno;              --定义部门编号绑定变量
   v_empno NUMBER(4);                                         
   v_ename VARCHAR2(25);
BEGIN
   OPEN emp_cur FOR                           --打开动态游标
      'SELECT empno, ename FROM emp '||
      'WHERE deptno = :1'
   USING v_deptno;
   LOOP
      FETCH emp_cur INTO v_empno, v_ename;    --循环提取游标数据  
      IF emp_cur%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE ('没有符合条件的记录'); --added by PD
        END IF;
      EXIT WHEN emp_cur%NOTFOUND;          --没有数据时退出循环
      DBMS_OUTPUT.PUT_LINE ('员工编号: '||v_empno);
      DBMS_OUTPUT.PUT_LINE ('员工名称:  '||v_ename);
   END LOOP;
   CLOSE emp_cur;                              --关闭游标变量
EXCEPTION
   WHEN OTHERS THEN   
      IF emp_cur%ISOPEN THEN                 --如果出现异常，游标变量未关闭
         CLOSE emp_cur;                        --关闭游标
      END IF;   
      DBMS_OUTPUT.PUT_LINE ('ERROR: '||
         SUBSTR(SQLERRM, 1, 200));         
END;