使用DMSQL程序的主要目的是对DM数据库进行访问，因此DMSQL程序中支持使用SQL语句进行对数据库对象的SELECT、INSERT、UPDATE、DELETE等DML操作，还可以定义和操纵游标等。DMSQL程序支持的SQL语句具体包括：
- 数据查询语句(SELECT)
- 数据操纵语句(INSERT、DELETE、UPDATE)
- 游标定义及操纵语句(DECLARE、OPEN、FETCH、CLOSE)
- 事务控制语句(COMMIT、ROLLBACK、SAVEPOINT)
- 动态SQL执行语句(EXECUTE IMMEDIATE)
通过SQL语句及上一章介绍的各种控制结构，用户可以编写复杂的DMSQL程序，实现复杂逻辑的数据库访问应用。
5.1 普通静态SQL语句
5.1.1 数据操纵
在DMSQL程序中，可以直接使用INSERT、DELETE和UPDATE语句对数据库中的表进行增、删、改操作。
例如：
DECLARE
catogory CONSTANT VARCHAR(50):='天文';
BEGIN
INSERT INTO PRODUCTION.PRODUCT_CATEGORY VALUES(catogory);
END;
/
上面的例子中在INSERT语句中直接使用了常量作为列值进行插入。在实际应用中，由于DMSQL程序的编程特性，更常见的使用方式是在DML语句中使用DMSQL程序中定义的变量，如下例所示。
CREATE OR REPLACE PROCEDURE proc_ins(category IN VARCHAR(50)) AS
BEGIN
INSERT INTO PRODUCTION.PRODUCT_CATEGORY VALUES(category);
END;
/
CALL PROC_INS('动漫');
UPDATE和DELETE语句的WHERE子句中也可以使用变量，例如：
DECLARE
catogory1 VARCHAR(50);
catogory2 VARCHAR(50);
BEGIN
catogory1='动漫';
catogory2='高等数学';
DELETE PRODUCTION.PRODUCT_CATEGORY WHERE NAME=catogory1;
UPDATE PRODUCTION.PRODUCT_CATEGORY SET NAME=catogory2 WHERE NAME='数学';
END;
/
5.1.2 数据查询
在DMSQL程序中可直接使用SELECT语句从数据库中查询数据。由于通常对数据进行查询的目的是为了进一步进行处理，而不仅仅是显示出来，所以SELECT语句可以采用下面的语法将查询到的数据赋给相应变量以便后面对它进行引用：
SELECT<列名>{,<列名>} INTO <变量>{,<变量>} FROM……
与一般SELECT语句不同，上面语法所示的SELECT语句使用INTO子句将查询到的数据存放到变量中。查询的列与INTO子句中的变量在数目、类型上要一致，否则会报错。
下面的例子查询书名和出版社，存放到对应变量中并打印出来。
DECLARE
p_name VARCHAR(50);
p_publish VARCHAR(50);
BEGIN
SELECT NAME,PUBLISHER INTO p_name,p_publish FROM PRODUCTION.PRODUCT WHERE AUTHOR
LIKE '曹雪芹,高鹗';
PRINT p_name;
PRINT p_publish;
EXCEPTION
WHENNO_DATA_FOUND OR TOO_MANY_ROWS THEN
PRINT'NO_DATA_FOUND OR TOO_MANY_ROWS';
WHEN OTHERS THEN
PRINT 'ERROR OCCURS';
END;
/
SELECT…INTO语句要求查询只能返回一条记录，执行时可能会发生两种例外情况：
- 没有查询到满足条件的记录，系统返回预定义异常NO_DATA_FOUND；
- 存在多行满足条件的记录，系统返回预定义异常TOO_MANY_ROWS，对于这样的异常必须做出相应处理，否则会影响DMSQL程序的正确执行。
5.1.3 事务控制
可以在DMSQL程序中直接使用COMMIT、ROLLBACK和SAVEPOINT语句进行事务控制：
COMMIT[ WORK];语句提交一个事务ROLLBACK[ WORK];语句回滚一个事务SAVEPOINT \<保存点名\>;语句在事务中设置一个保存点ROLLBACK [WORK] TO SAVEPOINT \<保存点名\>;语句将事务回滚到指定的保存点
5.2 游标
5.1.2节中介绍了DMSQL程序中使用SELECT…INTO语句将查询结果存放到变量中进行处理的方法，但这种方法只能返回一条记录，否则就会产生TOO_MANY_ROWS错误。为了解决这个问题，DMSQL程序引入了游标，允许程序对多行数据进行逐条处理。
5.2.1 静态游标
静态游标是只读游标，它总是按照打开游标时的原样显示结果集，在编译时就能确定静态游标使用的查询。
静态游标又分为两种：隐式游标和显式游标。
5.2.1.1 隐式游标
隐式游标无需用户进行定义，每当用户在DMSQL程序中执行一个DML语句（INSERT、UPDATE、DELETE）或者SELECT...INTO语句时，DMSQL程序都会自动声明一个隐式游标并管理这个游标。
隐式游标的名称为“SQL”，用户可以通过隐式游标获取语句执行的一些信息。DMSQL程序中的每个游标都有%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT四个属性，对于隐式游标，这四个属性的意义如下：
- %FOUND：语句是否修改或查询到了记录，是返回TRUE，否则返回FALSE；
- %NOTFOUND：语句是否未能成功修改或查询到记录，是返回TRUE，否则返回FALSE；
- %ISOPEN：游标是否打开。是返回TRUE，否返回FALSE。由于系统在语句执行完成后会自动关闭隐式游标，因此隐式游标的%ISOPEN属性永远为FALSE；
- %ROWCOUNT：DML语句执行影响的行数，或SELECT…INTO语句返回的行数。
例如，将孙丽的电话号码修改为13818882888。
BEGIN
UPDATE PERSON.PERSON SET PHONE=13818882888 WHERE NAME='孙丽';
IF SQL%NOTFOUND THEN
PRINT '此人不存在';
ELSE
PRINT '已修改';
END IF;
END;
/
5.2.1.2 显式游标
显式游标指向一个查询语句执行后的结果集区域。当需要处理返回多条记录的查询时，应显式地定义游标以处理结果集的每一行。
使用显式游标一般包括四个步骤：
- 定义游标：在DMSQL程序的声明部分定义游标，声明游标及其关联的查询语句；
- 打开游标：执行游标关联的语句，将查询结果装入游标工作区，将游标定位到结果集的第一行之前；
- 拨动游标：根据应用需要将游标位置移动到结果集的合适位置；
- 关闭游标：游标使用完后应关闭，以释放其占有的资源。
下面对这四个步骤进行具体介绍。
- 定义显示游标
在DMSQL程序的声明部分定义显示游标，其语法如下：
CURSOR <游标名> [FAST | NO FAST] <cursor选项>;
或
<游标名> CURSOR [FAST | NO FAST] <cursor选项>;
<cursor选项> :=<cursor选项1>|<cursor选项2>|<cursor选项3>|<cursor选项4>
<cursor选项1>:= <IS|FOR> {<查询表达式>|<连接表>}
<cursor选项2>:= <IS|FOR> TABLE <表名>
<cursor选项3>:= (<参数声明> {,<参数声明>})IS <查询表达式>
<cursor选项4>:= [(<参数声明> {,<参数声明>})] RETURN <DMSQL数据类型> IS <查询表达式>
<参数声明> ::= <参数名> [IN] <参数类型> [ DEFAULT|:= <缺省值> ]
<DMSQL数据类型> ::= <普通数据类型>
| <变量名> %TYPE
| <表名> %ROWTYPE
| CURSOR
| REF <游标名>
语法中的“FAST”指定游标是否为快速游标。缺省为NO FAST，为普通游标。快速游标提前返回结果集，速度上提升明显，但是存在以下的使用约束：
- FAST属性只在显示游标中支持；
- 使用快速游标的DMSQL程序语句块中不能修改快速游标所涉及的表。这点需用户自己保证，否则可能导致结果不正确；
- 不支持游标更新和删除；
- 不支持NEXT以外的FETCH方向；
- 不支持快速游标作为函数返回值；
- MPP环境下不支持对快速游标进行FETCH操作。
必须先定义一个游标，之后才能在别的语句中使用它。定义显示游标时指定游标名和与其关联的查询语句。可以指定游标的返回类型，也可以指定关联的查询语句中的WHERE子句使用的参数。
下面的程序片段说明了如何使用不同语法定义各种显示游标。
DECLARE
CURSOR c1 IS SELECT TITLE FROM RESOURCES.EMPLOYEE WHERE MANAGERID = 3;
CURSOR c2 RETURN RESOURCES.EMPLOYEE%ROWTYPE IS SELECT * FROM
RESOURCES.EMPLOYEE;
c3 CURSOR IS TABLE RESOURCES.EMPLOYEE;
……
- 打开显示游标
打开一个显示游标的语法如下：
OPEN <游标名>;
指定打开的游标必须是已定义的游标，此时系统执行这个游标所关联的查询语句，获得结果集，并将游标定位到结果集的第一行之前。
注意当再次打开一个已打开的游标时，游标会被重新初始化，游标属性数据可能会发生变化。
- 拨动游标
拨动游标的语法为：
FETCH [<fetch选项> [FROM]] <游标名> [ [BULK COLLECT] INTO <主变量名>{,<主变量名>} ] [LIMIT <rows>];
<fetch选项>::= NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n
被拨动的游标必须是已打开的游标。
fetch选项指定将游标移动到结果集的某个位置：
- NEXT：游标下移一行
- PRIOR：游标前移一行
- FIRST：游标移动到第一行
- LAST：游标移动到最后一行
- ABSOLUTE n：游标移动到第n行
- RELATIVE n：游标移动到当前指示行后的第n行
FETCH语句每次只获取一条记录，除非指定了“BULK COLLECT”。若不指定FETCH选项，则第一次执行FETCH语句时，游标下移，指向结果集的第一行，以后每执行一次FETCH语句，游标均顺序下移一行，使这一行成为当前行。
INTO子句中的变量个数、类型必须与游标关联的查询语句中各SELECT项的个数、类型一一对应。典型的使用方式是在LOOP循环中使用FETCH语句将每一条记录数据赋给变量，并进行处理，使用%FOUND或%NOTFOUND来判断是否处理完数据并退出循环。如下例所示：
DECLARE
v_name VARCHAR(50);
v_phone VARCHAR(50);
c1 CURSOR;
BEGIN
OPEN c1 FOR SELECT NAME,PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE
A.PERSONID=B.PERSONID;
LOOP
FETCH c1 INTO v_name,v_phone;
EXIT WHEN c1%NOTFOUND;
PRINT v_name || v_phone;
END LOOP;
CLOSE c1;
END;
/
使用FETCH…BULK COLLECTINTO可以将查询结果批量地、一次性地赋给集合变量。FETCH…BULK COLLECTINTO和LIMIT rows 配合使用，可以限制每次获取数据的行数。
下面的例子说明了FETCH…BULK COLLECTINTO的使用方法。
DECLARE
TYPE V_rd IS RECORD(V_NAME VARCHAR(50),V_PHONE VARCHAR(50));
TYPE V_type IS TABLE OF V_rd INDEX BY INT;
v_info V_type;
c1 CURSOR IS SELECT NAME,PHONE FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE
A.PERSONID=B.PERSONID;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO v_info;
CLOSE c1;
FOR I IN 1..v_info.COUNT LOOP
PRINT v_info(I).V_NAME ||v_info(I).V_PHONE;
END LOOP;
END;
/
BULK COLLECT可以和SELECT INTO、FETCH INTO、RETURNING INTO一起使用，BULK COLLECT之后INTO的变量必须是集合类型。
- 关闭游标
关闭游标的语法为：
CLOSE <游标名>;
游标在使用完后应及时关闭，以释放它所占用的内存空间。当游标关闭后，不能再从游标中获取数据，否则将报错。如果需要，可以再次打开游标。
5.2.1.1节中介绍了隐式游标的属性，同样地，每一个显示游标也有%FOUND、%NOTFOUND、%ISOPEN和%ROWCOUNT四个属性，但这些属性的意义与隐式游标的有一些区别。
- %FOUND：如果游标未打开，产生一个异常。否则，在第一次拨动游标之前，其值为NULL。如果最近一次拨动游标时取到了数据，其值为TRUE，否则为FALSE。
- %NOTFOUND：如果游标未打开，产生一个异常。否则，在第一次拨动游标之前，其值为NULL。如果最近一次拨动游标时取到了数据，其值为FALSE，否则为TRUE。
- %ISOPEN：游标打开时为TRUE，否则为FALSE。
- %ROWCOUNT：如果游标未打开，产生一个异常。如游标已打开，在第一次拨动游标之前其值为0，否则为最近一次拨动后已经取到的元组数。
下面的例子说明了显示游标属性的使用方法,，对于基表EMPSALARY，输出表中的前5行数据。如果表中的数据不足5行，则输出表中的全部数据。
DECLARE
CURSOR c1 FOR SELECT * FROM OTHER.EMPSALARY;
my_ename CHAR(10);
my_empno NUMERIC(4);
my_sal NUMERIC(7,2);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND; /* 当游标取不到数据时跳出循环 */
PRINT my_ename || ' ' || my_empno || ' ' || my_sal;
EXIT WHEN c1%ROWCOUNT=5; /* 已经输出了5行数据，跳出循环*/
END LOOP;
CLOSE c1;
END;
/
5.2.2 动态游标
与静态游标不同，动态游标在声明部分只是先声明一个游标类型的变量，并不指定其关联的查询语句，在执行部分打开游标时才指定查询语句。动态游标的使用主要在定义和打开时与显式游标不同，下面进行详细介绍。
- 定义动态游标
定义动态游标的语法如下：
CURSOR <游标名>；
- 打开动态游标
打开动态游标的语法如下：
OPEN <游标名><for表达式>;
<for表达式>::=<for_item1>|<for_item2>
<for_item1>::= FOR <查询表达式>
<for_item2>::= FOR <表达式> [USING <绑定参数> {,<绑定参数>}]
动态游标在OPEN时通过FOR子句指定与其关联的查询语句。
下面的例子使用动态游标输出员工的姓名、工号和薪水。
DECLARE
my_ename CHAR(10);
my_empno NUMERIC(4);
my_sal NUMERIC(7,2);
c1 CURSOR;
BEGIN
OPEN C1 FOR SELECT * FROM OTHER.EMPSALARY;
LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND;
PRINT '姓名'||my_ename || '工号' || my_empno || ' 薪水' || my_sal;
END LOOP;
CLOSE c1;
END;
/
动态游标关联的查询语句还可以带有参数，参数以“?”指定，同时在打开游标语句中使用USING子句指定参数，且参数的个数和类型与语句中的“?”必须一一匹配。下面的例子说明了如何使用关联的语句中带有参数的动态游标。
DECLARE
str VARCHAR;
CURSOR csr;
BEGIN
OPEN csr FOR 'SELECT LOGINID FROM RESOURCES.EMPLOYEE WHERE TITLE =? OR TITLE =?'
USING '销售经理','总经理';
LOOP
FETCH csr INTO str;
EXIT WHEN csr%NOTFOUND;
PRINT str;
END LOOP;
CLOSE csr;
END;
/
5.2.3 游标变量（引用游标）
游标变量不是真正的游标对象，而是指向游标对象的一个指针，因此是一种引用类型，也可以称为引用游标。
定义游标变量的语法为：
<游标变量名> CURSOR[:= <源游标名>];
或
TYPE <类型名> IS REF CURSOR [RETURN <DMSQL数据类型>];
<游标变量名><类型名>;
如果定义引用游标的时候没有赋值。可以在 \<执行部分\>中对它赋值。此时引用游标可以继承所有源游标的属性。如果源游标已经打开，则此引用游标也已经打开，引用游标指向的位置和源游标也是完全一样的。
还可以像使用动态游标一样，在打开引用游标时为其动态关联一条查询语句。
引用游标有以下几个特点：
- 引用游标不局限于一个查询，可以为一个查询声明或者打开一个引用游标，然后对其结果集进行处理，之后又可以将这个引用游标为其它的查询打开；
- 可以对引用游标进行赋值；
- 可以像用一个变量一样在一个表达式中使用引用游标；
- 引用游标可以作为一个子程序的参数；
- 可以使用引用游标在DMSQL程序的不同子程序中传递结果集。
下面的例子使用引用游标在子程序中传递结果集。
DECLARE
TYPE Emptype IS REF CURSOR RETURN PERSON.PERSON%ROWTYPE;
emp Emptype;
PROCEDURE process_emp(emp_v IN Emptype) IS
person PERSON.PERSON%ROWTYPE;
BEGIN
LOOP
FETCH emp_v INTO person;
EXIT WHEN emp_v%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名：'||person.NAME || ' 电话：' || person.PHONE);
END LOOP;
END;
BEGIN
OPEN emp FOR SELECT A.* FROM PERSON.PERSON A,RESOURCES.EMPLOYEE B WHERE
A.PERSONID=B.PERSONID;
process_emp(emp);
CLOSE emp;
END;
/
5.2.4 使用静态游标更新、删除数据
可以使用静态游标更新或删除结果集中的数据。若需要使用游标更新或删除数据，则在游标关联的查询语句中一定要使用“FOR UPDATE选项”。FOR UPDATE选项出现在查询语句中，用于对要修改的行上锁，以防止用户在同一行上进行修改操作。关于FOR UPDATE选项的详细介绍可参考《DM8_SQL语言使用手册》。
当游标拨动到需要更新或删除的行时，就可以使用UPDATE/DELETE语句进行数据更新/删除。此时必须在UPDATE/DELETE语句结尾使用“WHERE CURRENT OF子句”，以限定删除/更新游标当前所指的行。语法如下：
< WHERE CURRENT OF子句> ::=WHERE CURRENT OF <游标名>
例如，使用游标更新表中的数据。
DECLARE
CURSOR csr is SELECT SALARY FROM RESOURCES.EMPLOYEE WHERE TITLE='销售经理' FOR
UPDATE;
BEGIN
OPEN csr;
IF csr%ISOPEN THEN
FETCH csr;
UPDATE RESOURCES.EMPLOYEE SET SALARY = SALARY + 10000 WHERE CURRENT OF csr;
ELSE
PRINT 'CURSOR IS NOT OPENED';
END IF;
CLOSE csr;
END;
/
下面的例子使用游标删除表中的数据。
DECLARE
CURSOR dcsr is SELECT EMPNO FROM OTHER.EMPSALARY WHERE ENAME='KING' FOR UPDATE;
BEGIN
OPEN dcsr;
IF dcsr%ISOPEN THEN
FETCH dcsr;
DELETE FROM OTHER.EMPSALARY WHERE CURRENT OF dcsr;
ELSE
PRINT 'CURSOR IS NOT OPENED';
END IF;
CLOSE dcsr;
END;
/
5.2.5 使用游标FOR循环
游标通常与循环联合使用，以遍历结果集数据。实际上，DMSQL程序还提供了一种将两者综合在一起的语句，即游标FOR循环语句。游标FOR循环自动使用FOR循环依次读取结果集中的数据。当FOR循环开始时，游标会自动打开（不需要使用OPEN方法）；每循环一次系统自动读取游标当前行的数据（不需要使用FETCH）；当数据遍历完毕退出FOR循环时，游标被自动关闭（不需要使用CLOSE），大大降低了应用程序的复杂度。
5.2.5.1 隐式游标FOR循环
隐式游标FOR循环的语法如下：
FOR <cursor_record> IN (<查询语句>)
LOOP
<执行部分>
END LOOP;
其中，\<cursor_record\>是一个记录类型的变量。它是DMSQL程序根据SQL%ROWTYPE属性隐式声明出来的，不需要显式声明。也不能显式声明一个与 \<cursor_record\>同名的记录，会导致逻辑错误。
FOR循环不断地将行数据读入变量 \<cursor_record\>中，在循环中也可以存取 \<cursor_record\>中的字段。
例如，下面的例子使用了隐式游标FOR循环。
BEGIN
FOR v_emp IN (SELECT * FROM RESOURCES.EMPLOYEE)
LOOP
DBMS_OUTPUT.PUT_LINE(V_EMP.TITLE || '的工资' || V_EMP.SALARY);
END LOOP;
END;
/
5.2.5.2 显式游标FOR循环
显式游标FOR循环的语法如下：
FOR <cursor_record> IN <游标名>
LOOP
<执行部分>
END LOOP;
显式游标FOR循环与隐式游标的语法和使用方式都非常相似，只是关键字“IN”后不指定查询语句而是指定显式游标名，\<cursor_record\>则为 \<游标名\>%ROWTYPE类型的变量。
下面的例子使用的是显式游标FOR循环。
DECLARE
CURSOR cur_emp IS SELECT * FROM RESOURCES.EMPLOYEE;
BEGIN
FOR V_EMP IN CUR_EMP LOOP
DBMS_OUTPUT.PUT_LINE(V_EMP.TITLE || '的工资' || V_EMP.SALARY);
END LOOP;
END;
/
5.3 动态SQL
本章前面的小节中介绍的大都是DMSQL程序中的静态SQL，静态SQL在DMSQL程序进行编译时是明确的SQL语句，处理的数据库对象也是明确的，这些语句在编译时就可进行语法和语义分析处理。与之对应，DMSQL程序还支持动态SQL，动态SQL指在DMSQL程序进行编译时是不确定的SQL，编译时对动态SQL不进行处理，在DMSQL程序运行时才动态地生成并执行这些SQL。
在应用中，常常需要根据用户的选择（如表名、列名、排序方式等）来生成SQL语句并执行，这些SQL不能在应用开发时确定，此时就需要使用动态SQL。另外，在DMSQL程序中，DDL语句只能通过动态SQL执行。
使用EXECUTE IMMEDIATE动态地准备和执行一条SQL语句，语法如下：
EXECUTE IMMEDIATE <SQL动态语句文本> [USING <参数> {,<参数>}];
动态SQL的执行首先分析SQL动态语句文本，检查是否有错误，如果有错误则不执行，并在SQLCODE中返回错误码；如果没发现错误则继续执行。
注意下列语句不能作动态SQL语句：CLOSE、DECLARE、FETCH、OPEN。
动态SQL中可以使用参数，并支持两种指定参数的方式：用“?”表示参数和用“:variable”表示参数。
当用“?”表示参数时，在指定参数值时可以是任意的值，但参数值个数一定要与“?”的个数相同，同时数据类型一定要匹配（能够互相转换也可以），不然会报数据类型不匹配的错误。
例如：
CREATE OR REPLACE PROCEDURE proc(cate IN INT, time IN DATE) AS
DECLARE
str_sql varchar := 'SELECT NAME,PUBLISHER from PRODUCTION.PRODUCT WHERE
PRODUCT_SUBCATEGORYID = ? AND PUBLISHTIME> ?';
BEGIN
EXECUTE IMMEDIATE str_sql USING cate,time;
EXCEPTION
WHEN OTHERS THEN PRINT 'error';
END;
/
CALL proc(4, '2001-01-01');
当用“:variable”表示参数时，若SQL动态语句文本为普通语句方式，则系统将其转化为“?”进行处理。若SQL动态语句文本为脚本方式（语句块方式或多条用分号分隔的SQL语句），则保留“:variable”参数形式。此时需要注意，若参数列表中有同名参数，系统将这些同名参数视为同一个参数，只需要对应传入一个参数值即可，否则会报“参数个数不匹配.error code = -3205”的错误。
例如，下面的DMSQL程序中sql_str采用普通语句方式，其中的两个:x被转化为?进行处理，程序执行成功。
DECLARE
sql_str VARCHAR := 'UPDATE PRODUCTION.PRODUCT SET SELLSTARTTIME=:x,
SELLENDTIME=:x WHERE NAME=''红楼梦'';';
BEGIN
EXECUTE IMMEDIATE SQL_STR USING '2015-01-01','2018-01-01';
END;
/
而在下面的DMSQL程序中sql_str写为脚本方式，则将两个:x视为同名参数，因此在动态执行时指定两个实际参数会报错。
DECLARE
sql_str VARCHAR := 'BEGIN UPDATE PRODUCTION.PRODUCT SET SELLSTARTTIME=:x,
SELLENDTIME=:x WHERE NAME=''红楼梦'';END;';
BEGIN
EXECUTE IMMEDIATE SQL_STR USING '2015-01-01','2018-01-01';
END;
/
[-5402] 参数个数不匹配.
如果SQL动态语句文本为SELECT语句，且结果集只会是一条记录，可通过INTO语句来操作查询到的结果集，把查询到的结果存储到变量中。其语法如下：
EXECUTE IMMEDIATE <SQL动态语句文本> [INTO <变量>{,<变量>}][USING <参数>{,<参数>}];
例如：
DECLARE
p_name VARCHAR(50);
p_publish VARCHAR(50);
str_sql VARCHAR := 'SELECT NAME,PUBLISHER from PRODUCTION.PRODUCT WHERE AUTHOR =
?';
BEGIN
EXECUTE IMMEDIATE str_sql INTO p_name,p_publish USING '曹雪芹,高鹗';
PRINT p_name;
PRINT p_publish;
EXCEPTION
WHEN OTHERS THEN PRINT 'error';
END;
/
5.4 返回查询结果集
在存储过程或客户端DMSQL程序中执行了不带INTO子句的查询语句时，系统将在调用结束时将该查询结果集返回给调用者。当出现多个查询语句时，只有第一个查询语句的结果集被返回，如果想查看其他结果集，可在DIsql中输入命令“more”查看下一个结果集。
例如：
CREATE OR REPLACE PROCEDURE PRODUCTION.proc_result(p_publisher VARCHAR(50)) AS
BEGIN
SELECT NAME,AUTHOR FROM PRODUCTION.PRODUCT WHERE PUBLISHER=p_publisher;
SELECT NOWPRICE FROM PRODUCTION.PRODUCT WHERE PUBLISHER=p_publisher;
END;
/
调用PRODUCTION.proc_result过程，仅返回第一条查询语句的结果集：
SQL> CALL PRODUCTION.proc_result('中华书局');
行号 NAME AUTHOR
---------- ------ --------------
1 红楼梦曹雪芹,高鹗
2 水浒传施耐庵，罗贯中
再调用more命令，返回第二条查询语句的结果集：
SQL> more
行号 NOWPRICE
---------- --------
1 15.2000
2 14.3000
注意存储函数中不能包含返回结果集的查询语句。
5.5 自治事务
通过将一个DMSQL语句块定义成自治事务，可以将该块中的DML语句和调用程序的事务环境隔离开。如此一来，该语句块就成为一个由其他事务启动的独立的事务，前一个事务被称为主事务。在自治事务块中，主事务是挂起的；等待自治事务完成后，会话自动切换回主事务。
一个定义了自治事务的语句块称为自治例程。
5.5.1 定义自治事务
定义自治事务，需要在DMSQL程序的声明部分添加如下语法的语句：
PRAGMA AUTONOMOUS_TRANSACTION;
自治事务的定义语句可以放在DMSQL程序声明部分的任何地方，但推荐放在数据结构声明之前。
作为自治事务的DMSQL语句块可以是下面中的一种：
- 最顶层的（不是嵌套的）匿名DMSQL语句块
- 函数和过程，或者在一个包里定义或者是一个独立的程序
- 对象类型的方法
- 数据库触发器
- 嵌套子过程
例如：
CREATE TABLE test(c VARCHAR);
INSERT INTO testVALUES('主事务');
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test VALUES('自治事务');
COMMIT; --一定要提交，否则会报错。详情请参考5.5.2节
END;
/
ROLLBACK;
查询表test:
SELECT * FROM test;
行号 C
---------- --------
1 自治事务
可以看到，表test中有记录“自治事务”，其所在自治事务提交，不受主事务回滚影响，而主事务插入的记录“主事务”被回滚。
5.5.2 自治事务完整性与死锁检测
自治事务的语句块执行完成时，如果事务仍处于活动状态，则系统需要报错：检测到活动的自治事务处理，并将未提交事务回滚，所以需要在语句块结束处显式添加COMMIT或ROLLBACK语句。
另一方面，COMMIT和ROLLBACK语句只是结束了活动的自治事务，但不会终止自治例程。实际上，可以在一个自治块中使用多个COMMIT、ROLLBACK语句。
例如：
CREATE TABLE test (c VARCHAR);
INSERT INTO test VALUES('主事务');
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test VALUES('自治事务1');
ROLLBACK;
INSERT INTO test VALUES('自治事务2');
COMMIT;
END;
/
ROLLBACK;
查询表test:
SELECT * FROM test;
行号 C
---------- ---------
1 自治事务2
自治事务相对主事务是完全独立的。由于执行自治事务时主事务处于挂起状态，如果自治事务需要的锁资源已经被主事务拥有则会产生死锁报错。
5.5.3 自治事务嵌套
自治块中可以调用其它被定义自治事务的过程，从而产生嵌套的自治事务。例如：
CREATE TABLE TEST (C VARCHAR);
CREATE OR REPLACE PROCEDURE PROC_AUTO1
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE TEST WHERE C='嵌套事务';
COMMIT;
INSERT INTO TEST VALUES('自治嵌套事务');
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE PROC_AUTO2
IS
declare
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
CALL PROC_AUTO1;
INSERT INTO TEST VALUES('自治语句块');
COMMIT;
END;
/
INSERT INTO TEST VALUES('嵌套事务');
COMMIT;
INSERT INTO TEST VALUES('主事务');
CALL PROC_AUTO2;
ROLLBACK;
查询表test:
SELECT * FROM test;
行号 C
---------- ------------
1 自治嵌套事务
2 自治语句块
DM的INI参数TRANSACTIONS指定一个会话中可以并发的自治事务数量，其默认值为75。如果实际生成自治事务数超出该参数限定值时，服务器将报错：“并发事务数超出上限”。该参数理论值上下限分别为1000和1。
