PLSQL入门与精通（第46章：动态SQL返回多行的语法）

上一次介绍了动态SQL返回一行结果的语法结构。

本次解说用动态SQL返回复数行的语法结构。

首先动态SQL如果返回多行的时候，是不能用EXECUTE IMMEDIATE的，否则会发生异常。
可以使用游标变量，实现动态SQL多行数据的接收。

首先，我们回忆一下使用显式游标在普通嵌入式SQL中返回多行的例子：

<嵌入式SQL+多行+游标例子>

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 /*********************************************/
3 --声明显式游标
4 /*********************************************/
5 CURSOR CUR_EMP IS
6 SELECT * FROM EMP WHERE DEPTNO = 10;

7 /*********************************************/
8 --声明记录类型的变量
9 /*********************************************/
10 REC EMP%ROWTYPE;
11 BEGIN

12 /*********************************************/
13 --打开游标
14 /*********************************************/
15 OPEN CUR_EMP;

16 /*********************************************/
17- --逐行获取数据并显示在画面上
18 /*********************************************/
19 LOOP
20 FETCH CUR_EMP INTO REC;
21 EXIT WHEN CUR_EMP%NOTFOUND;
22 DBMS_OUTPUT.PUT_LINE(REC.EMPNO || ’ ’ || REC.ENAME);
23 END LOOP;

24 /*********************************************/
25 --关闭游标
26 /*********************************************/
27 CLOSE CUR_EMP;
28 END;
29 /

7782 CLARK
7839 KING

PL/SQL过程成功完成。

上述例子功能：从员工表（EMP表）中查询部门编号为10的员工，并在画面上逐行显示。

详细说明如下：

第5-6行：定义显式游标，SQL是“SELECT*FROM EMP WERE DEPTNO=10”，返回多行数据。
这里定义的游标名是“CUR_EMP”。
第15行：打开CUR_EMP游标。
这样就可以进行实际的查询了。查询结果的行（结果集）放在在内存上以供后续使用。
第20行：用FETCH语句从该结果集中取出其中1行并保存到记录变量中。
第22行：将保存在记录变量中的内容显示在画面上。
第19行和第23行：用LOOP和END LOOP的循环，重复处理。
第21行：设置退出循环的条件：当FETCH处理没数据的时候，把%NOTUND设置成TRUE，终止循环处理。
第27行：用CLOSE语句，把从结果集从内存中释放掉。

以上是典型的显示的游标处理流程。

接下来，把上边例子改成动态SQL:

<动态SQL+多行+游标例子>

SQL> DECLARE
2 /*********************************************/
3 --声明游标变量
4 /*********************************************/
5 TYPE CUR_EMP_TYPE IS REF CURSOR;
6 CUR_EMP CUR_EMP_TYPE;

7 /*********************************************/
8 --声明记录类型变量
9 /*********************************************/
10 REC EMP%ROWTYPE;
11 BEGIN
12 /*********************************************/

13 --打开游标
14 /*********************************************/
15 OPEN CUR_EMP FOR ‘SELECT * FROM EMP WHERE DEPTNO = 10’;
16 /*********************************************/

17 --逐行获取数据并显示在画面上
18 /*********************************************/
19 LOOP
20 FETCH CUR_EMP INTO REC;
21 EXIT WHEN CUR_EMP%NOTFOUND;
22 DBMS_OUTPUT.PUT_LINE(REC.EMPNO || ’ ’ || REC.ENAME);
23 END LOOP;

24 /*********************************************/
25 --关闭游标
26 /*********************************************/
27 CLOSE CUR_EMP;

28 END;
29 /

7782 CLARK
7839 KING

PL/SQL过程成功完成。

嵌入式SQL方式和动态SQL方式的主要差别有2个：

第一个不同之处是游标定义：
光标变量的定义方法分为两个阶段，以下是语法。
①TYPE 游标变量名称 IS REF CURSOR；
（※IS REF CURSOR 是固定的）
②光标变量名称 上述①的类型名称；

这里，游标变量名称用CUR_EMP的名称定义。

第二个不同的游标的OPEN处理（第15行):
用OPEN打开游标变量的时候，需要使用FOR语句与查询SQL语句（SELECT语句）相关联。
这了最重要的差别就是：“FOR指定的SELECT文是动态的SQL（字符串的SQL）”。

总结一下，OPEN处理的语法如下：
③OPEN 游标变量名称 FOR’SELECT……’；
OPEN之后的处理就完全和通常的明示游标处理相同。
在嵌入式SQL中，“CUR_EMP”这个名字是显式游标名；动态SQL的时候，“CUR_EMP”这个名字是游标变量名。

上述就是动态SQL返回多行的时候的处理。