📄 第十一章 本地动态sql - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> dynamic_string<BR>[<STRONG>INTO</STRONG> {define_variable[, define_variable]... | record}]<BR>[USING [<STRONG>IN</STRONG> | <STRONG>OUT</STRONG> | <STRONG>IN</STRONG> <STRONG>OUT</STRONG>] bind_argument<BR> [, [<STRONG>IN</STRONG> | <STRONG>OUT</STRONG> | <STRONG>IN</STRONG> <STRONG>OUT</STRONG>] bind_argument]...]<BR>[{RETURNING | <STRONG>RETURN</STRONG>} <STRONG>INTO</STRONG> bind_argument[, bind_argument]...];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>dynamic_string是代表一条SQL语句或一个PL/SQL块的字符串表达式，define_variable是用于存放被选出的字段值的变量，record是用户定义或%ROWTYPE类型的记录，用来存放被选出的行记录。输入bind_argument参数是一个表达式，它的值将被传入(IN模式)或传出(OUT模式)或先传入再传出(IN
OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。</P>
<P>除了多行查询外，动态字符串可以包含任何SQL语句(不含终结符)或PL/SQL块(含终结符)。字符串中可以包括用于参数绑定的占位符。但是，不可以使用绑定参数为动态SQL传递模式对象。
</P>
<P>在用于单行查询时，INTO子句要指明用于存放检索值的变量或记录。对于查询检索出来的每一个值，INTO子句中都必须有一个与之对应的、类型兼容的变量或字段。在用于DML操作时，RETURNING
INTO子句要指明用于存放返回值的变量或记录。对于DML语句返回的每一个值，INTO子句中都必须有一个与之对应的、类型兼容的变量或字段。
</P>
<P>我们可以把所有的绑定参数放到USING子句中。默认的参数模式是IN。对于含有RETURNING子句的DML语句来说，我们可以把OUT参数放到RETURNING
INTO之后，并且不用指定它们的参数模式，因为默认就是OUT。如果我们既使用了USING又使用RETURNING
INTO，那么，USING子句中就只能包含IN模式的参数了。 </P>
<P>运行时，动态字符串中的绑定参数会替换相对应的占位符。所以，每个占位符必须与USING子句和/或RETURNING
INTO子句中的一个绑定参数对应。我们可以使用数字、字符和字符串作为绑定参数，但不能使用布尔类型(TRUE，FALSE和NULL)。要把空值传递给动态字符串，我们就必须使用工作区。
</P>
<P>动态SQL支持所有的SQL类型。所以，定义变量和绑定变量都可以是集合、LOB，对象类型实例和引用。作为一项规则，动态SQL是不支持PL/SQL特有的类型的。这样，它就不能使用布尔型或索引表。
</P>
<P>我们可以重复为绑定变量指定新值执行动态SQL语句。但是，每次都会消耗很多资源，因为EXECUTE
IMMEDIATE在每次执行之前都需要对动态字符串进行预处理。 </P>
<P class=title2>1、动态SQL实例</P>
<P>下面的PL/SQL块包含了几个动态SQL的例子： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> sql_stmt <STRONG>VARCHAR2</STRONG>(200);<BR> plsql_block <STRONG>VARCHAR2</STRONG>(500);<BR> emp_id <STRONG>NUMBER</STRONG>(4) := 7566;<BR> salary <STRONG>NUMBER</STRONG>(7, 2);<BR> dept_id <STRONG>NUMBER</STRONG>(2) := 50;<BR> dept_name <STRONG>VARCHAR2</STRONG>(14) := <EM>'PERSONNEL'</EM>;<BR> LOCATION <STRONG>VARCHAR2</STRONG>(13) := <EM>'DALLAS'</EM>;<BR> emp_rec emp%<STRONG>ROWTYPE</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'CREATE TABLE bonus (id NUMBER, amt NUMBER)'</EM>;<BR><BR> sql_stmt := <EM>'INSERT INTO dept VALUES (:1, :2, :3)'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING dept_id, dept_name, LOCATION;<BR><BR> sql_stmt := <EM>'SELECT * FROM emp WHERE empno = :id'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> <STRONG>INTO</STRONG> emp_rec<BR> USING emp_id;<BR><BR> plsql_block := <EM>'BEGIN emp_pkg.raise_salary(:id, :amt); END;'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> plsql_block<BR> USING 7788, 500;<BR><BR> sql_stmt :=<BR> <EM>'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING emp_id<BR> RETURNING <STRONG>INTO</STRONG> salary;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'DELETE FROM dept WHERE deptno = :num'</EM><BR> USING dept_id;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'ALTER SESSION SET SQL_TRACE TRUE'</EM>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>下例中，过程接受一个数据表名(如"emp")和一个可选的WHERE子句(如"sal >
2000")。如果我们没有提供WHERE条件，程序会删除指定表中所有的行，否则就会按照给定的条件删除行：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> delete_rows(<BR> table_name <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>,<BR> condition <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG> <STRONG>DEFAULT</STRONG> <STRONG>NULL</STRONG><BR>) <STRONG>AS</STRONG><BR> where_clause <STRONG>VARCHAR2</STRONG>(100) := <EM>' <STRONG>WHERE</STRONG> '</EM> || condition;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>IF</STRONG> condition <STRONG>IS</STRONG> <STRONG>NULL</STRONG> <STRONG>THEN</STRONG><BR> where_clause := <STRONG>NULL</STRONG>;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'<STRONG>DELETE</STRONG> <STRONG>FROM</STRONG> '</EM> || table_name || where_clause;<BR><STRONG>EXCEPTION</STRONG><BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、USING子句的向后兼容</P>
<P>当动态INSERT、UPDATE或DELETE语句有一个RETURNING子句时，输出绑定参数可以放到RETURNING
INTO或USING子句的后面。XXXXXXXXXX在新的应用程序中要使用RETURNING
INTO，而旧的应用程序可以继续使用USING，如下例： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> sql_stmt <STRONG>VARCHAR2</STRONG>(200);<BR> my_empno <STRONG>NUMBER</STRONG>(4) := 7902;<BR> my_ename <STRONG>VARCHAR2</STRONG>(10);<BR> my_job <STRONG>VARCHAR2</STRONG>(9);<BR> my_sal <STRONG>NUMBER</STRONG>(7, 2) := 3250.00;<BR><STRONG>BEGIN</STRONG><BR> sql_stmt := <EM>'UPDATE emp SET sal = :1 WHERE empno = :2 '</EM><BR> || <EM>'RETURNING ename, job INTO :3, :4'</EM>;<BR><BR> <EM>/* Bind returned values through USING clause. */</EM><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING my_sal, my_empno, <STRONG>OUT</STRONG> my_ename, <STRONG>OUT</STRONG> my_job;<BR><BR> <EM>/* Bind returned values through RETURNING <STRONG>INTO</STRONG> clause. */</EM><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING my_sal, my_empno<BR> RETURNING <STRONG>INTO</STRONG> my_ename, my_job;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>3、指定参数模式</P>
<P>使用USING子句时，我们不需要为输入参数指定模式，因为默认的就是IN；而RETURNING
INTO子句中我们是不可以指定输出参数的模式的，因为定义中它就是OUT模式。看一下下面的例子： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> sql_stmt <STRONG>VARCHAR2</STRONG>(200);<BR> dept_id <STRONG>NUMBER</STRONG>(2) := 30;<BR> old_loc <STRONG>VARCHAR2</STRONG>(13);<BR><STRONG>BEGIN</STRONG><BR> sql_stmt := <EM>'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING dept_id<BR> RETURNING <STRONG>INTO</STRONG> old_loc;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在适当的时候，我们必须为绑定参数指定OUT或IN OUT模式。例如，假定我们想调用下面的过程： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> create_dept(<BR> deptno <STRONG>IN</STRONG> <STRONG>OUT</STRONG> <STRONG>NUMBER</STRONG>,<BR> dname <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>,<BR> loc <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG><BR>) <STRONG>AS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> deptno_seq.<STRONG>NEXTVAL</STRONG><BR> <STRONG>INTO</STRONG> deptno<BR> <STRONG>FROM</STRONG> DUAL;<BR><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> dept<BR> <STRONG>VALUES</STRONG> (deptno, dname, loc);<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>要从动态PL/SQL块调用过程，就必须为与形参关联的绑定参数指定IN OUT模式，如下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> plsql_block <STRONG>VARCHAR2</STRONG>(500);<BR> new_deptno <STRONG>NUMBER</STRONG>(2);<BR> new_dname <STRONG>VARCHAR2</STRONG>(14) := <EM>'ADVERTISING'</EM>;<BR> new_loc <STRONG>VARCHAR2</STRONG>(13) := <EM>'NEW YORK'</EM>;<BR><STRONG>BEGIN</STRONG><BR> plsql_block := <EM>'BEGIN create_dept(:a, :b, :c); END;'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> plsql_block<BR> USING <STRONG>IN</STRONG> <STRONG>OUT</STRONG> new_deptno, new_dname, new_loc;<BR><BR> <STRONG>IF</STRONG> new_deptno > 90 <STRONG>THEN</STRONG> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>四、使用OPEN-FOR、FETCH和CLOSE语句</P>
<P>我们可以使用三种语句来处理动态多行查询：OPEN-FOR，FETCH和CLOSE。首先，用OPEN打开多行查询的游标变量。然后，用FETCH语句把数据从结果集中取出来。当所有的数据都处理完以后，就可以用CLOSE语句关闭游标变量了。
</P>
<P class=title2>1、打开游标变量</P>
<P>OPEN-FOR语句可以把游标变量和一个多行查询关联起来，然后执行查询，确定结果集，并把游标放到结果集的第一行，然后把%ROWCOUNT值初始化为零。
</P>
<P>与OPEN-FOR的静态形式不同的是，动态形式有一个可选的USING子句。在运行时，USING子句中的绑定变量可以替换动态SELECT语句中相对应的占位符，语法如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>OPEN</STRONG> {cursor_variable | :host_cursor_variable} <STRONG>FOR</STRONG> dynamic_string<BR>[USING bind_argument[, bind_argument]...];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>其中，cursor_variable是一个弱类型(没有返回类型)的游标变量，host_cursor_variable是声明在PL/SQL主环境中的游标变量，dynamic_string是字符串表达式，代表一个多行查询。
</P>
<P>在下面的例子中，我们声明一个游标变量，并把它和动态SELECT语句关联起来： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> empcurtyp <STRONG>IS</STRONG> <STRONG>REF</STRONG> <STRONG>CURSOR</STRONG>; <EM>-- define weak REF CURSOR type</EM><BR><BR> emp_cv empcurtyp; <EM>-- declare cursor variable</EM><BR> my_ename <STRONG>VARCHAR2</STRONG>(15);<BR> my_sal <STRONG>NUMBER</STRONG> := 1000;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> emp_cv <STRONG>FOR</STRONG> <EM>-- open cursor variable</EM><BR> <EM>'SELECT ename, sal FROM emp WHERE sal > :s'</EM> USING my_sal;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>其中绑定参数的值只在游标变量打开时计算一次。所以，如果我们想使用一个新的绑定值进行查询，就必须重新打开游标变量。
</P>
<P class=title2>2、从游标变量取得数据</P>
<P>FETCH语句可以从多行查询的结果集中返回单独的一行数据，并把数据内容赋值给INTO子句后的对应的变量，然后属性%ROWCOUNT增加一，游标移到下一行，语法如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FETCH</STRONG> {cursor_variable | :host_cursor_variable}<BR> <STRONG>INTO</STRONG> {define_variable[, define_variable]... | record};
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>继续上面的例子，我们把从游标变量emp_cv取得的数据放到变量my_ename和my_sal：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -