📄 第十一章 本地动态sql - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> namelist <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>VARCHAR2</STRONG>(15);<BR><BR> enames namelist;<BR> bonus_amt <STRONG>NUMBER</STRONG> := 500;<BR> sql_stmt <STRONG>VARCHAR</STRONG>(200);<BR><STRONG>BEGIN</STRONG><BR> sql_stmt := <EM>'UPDATE emp SET bonus = :1 RETURNING ename INTO :2'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt<BR> USING bonus_amt<BR> RETURNING <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> enames;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>要在SQL语句中绑定输入参数，就要使用FORALL语句和USING子句，但这时的SQL语句不能是查询语句，如下例：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numlist <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>;<BR><BR> <STRONG>TYPE</STRONG> namelist <STRONG>IS</STRONG> <STRONG>TABLE</STRONG> <STRONG>OF</STRONG> <STRONG>VARCHAR2</STRONG>(15);<BR><BR> empnos numlist;<BR> enames namelist;<BR><STRONG>BEGIN</STRONG><BR> empnos := numlist(1, 2, 3, 4, 5);<BR> <STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> 1 .. 5<BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1 '</EM> ||<BR> <EM>'RETURNING ename INTO :2'</EM><BR> USING empnos(i)<BR> RETURNING <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> enames;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>六、动态SQL的技巧与陷阱</P>
<P>这节会让我们了解如何完全利用动态SQL语句并避免一些常见的缺陷。 </P>
<P class=title2>1、改善性能</P>
<P>下例中，Oracle为每个不同的emp_id单独打开一个游标。这就造成资源浪费并降低了效率： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> fire_employee(emp_id <STRONG>NUMBER</STRONG>) <STRONG>AS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'DELETE FROM emp WHERE empno = '</EM> || TO_CHAR(emp_id);<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>我们可以使用绑定变量来改善性能，如下例所示。这就能让Oracle为不同的emp_id值重用同一个游标。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> fire_employee(emp_id <STRONG>NUMBER</STRONG>) <STRONG>AS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'DELETE FROM emp WHERE empno = :num'</EM><BR> USING emp_id;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、让过程对任意模式对象起作用</P>
<P>假设我们需要一个过程，让它接受数据表名，然后将指定的表从数据库中删除。我们可能会下面这样编写使用动态SQL的独立过程：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> drop_table(table_name <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>) <STRONG>AS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'DROP TABLE :tab'</EM><BR> USING table_name;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>但是，在运行的时候，这个过程可能会因为表名错误而无法执行成功。这就是我们为什么不能用参数绑定来为动态SQL传递模式对象的名称。解决方法是直接把参数嵌套到字符串中。我们把上面的EXECUTE
IMMEDIATE语句修改一下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PROCEDURE</STRONG> drop_table(table_name <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>) <STRONG>AS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'<STRONG>DROP</STRONG> <STRONG>TABLE</STRONG> '</EM> || table_name;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>这样，我们就可以向动态SQL语句传递任意数据表名称了。 </P>
<P class=title2>3、使用重复占位符</P>
<P>动态SQL语句中的占位符与USING子句中的绑定参数是位置关联的，而不是名称关联。所以，如果在SQL语句中同样的占位符出现两次或多次，那么，它的每次出现都必须与一个USING子句中的绑定参数相关联。例如下面的动态字符串：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap>sql_stmt := <EM>'INSERT INTO payroll VALUES (:x, :x, :y, :x)'</EM>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>我们可以为动态字符串编写对应的USING子句： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> sql_stmt USING a, a, b, a;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>但是，动态PL/SQL块中只有唯一的占位符才与USING子句中的绑定参数按位置对应。所以，如果一个占位符在PL/SQL块中出现两次或多次，那么所有这样相同的占位符都只与USING语句中的一个绑定参数相对应。比如下面的例子，第一个占位符(x)与第一个绑定参数(a)关联，第二个占位符
(y)与第二个绑定参数(b)关联。 </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> a <STRONG>NUMBER</STRONG> := 4;<BR> b <STRONG>NUMBER</STRONG> := 7;<BR><STRONG>BEGIN</STRONG><BR> plsql_block := <EM>'BEGIN calc_stats(:x, :x, :y, :x); END'</EM>;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> plsql_block<BR> USING a, b;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>4、使用游标属性</P>
<P>每个显式的游标都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。它们都能返回与静态或动态SQL语句执行结果相关的有用信息。
</P>
<P>为处理SQL数据操作语句，Oracle会打开一个名为SQL的隐式游标。它的属性会返回最近一次执行的INSERT、UPDATE、DELETE或单行SELECT的相关信息。例如，下面函数就使用%ROWCOUNT返回从数据表中删除的行数：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>FUNCTION</STRONG> rows_deleted(table_name <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>, condition <STRONG>IN</STRONG> <STRONG>VARCHAR2</STRONG>)<BR> <STRONG>RETURN</STRONG> <STRONG>INTEGER</STRONG> <STRONG>AS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'DELETE FROM '</EM> || table_name || <EM>' WHERE '</EM> || condition;<BR><BR> <STRONG>RETURN</STRONG> <STRONG>SQL</STRONG>%ROWCOUNT; <EM>-- return number of rows deleted</EM><BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>同样，当我们把游标变量的名字附加进去时，游标的属性也能返回多行查询执行结果的相关信息。 </P>
<P class=title2>5、传递空值</P>
<P>下面，我们来为动态SQL传递空值，见下面的EXECUTE IMMEDIATE语句： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'UPDATE emp SET comm = :x'</EM> USING <STRONG>NULL</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>但是，这个语句会因为在USING子句中使用NULL而执行失败，因为USING语句中所传递的参数是不能为空的。所以，要想解决这个问题，直接使用字符串就可以了：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> a_null <STRONG>CHAR</STRONG>(1); <EM>-- set to NULL automatically at run time</EM><BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'UPDATE emp SET comm = :x'</EM><BR> USING a_null;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>6、远程操作</P>
<P>如下例所示，PL/SQL子程序能够执行引用远程数据库对象的动态SQL语句： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PROCEDURE</STRONG> delete_dept(db_link <STRONG>VARCHAR2</STRONG>, dept_id <STRONG>INTEGER</STRONG>) <STRONG>IS</STRONG><BR><STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'DELETE FROM dept@'</EM> || db_link || <EM>' WHERE deptno = :num'</EM><BR> USING dept_id;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>同样，远程过程调用(RPC)的目标也包括动态SQL语句。例如，⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -