📄 第十一章 本地动态sql - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<TR>
<TD
noWrap><STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> emp_cv<BR> <STRONG>INTO</STRONG> my_ename, my_sal; <EM>-- fetch next row</EM><BR><BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> emp_cv%NOTFOUND; <EM>-- exit loop when last row is fetched</EM><BR> <EM>-- process row</EM><BR><STRONG>END</STRONG> <STRONG>LOOP</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>3、关闭游标变量</P>
<P>CLOSE语句能够关闭游标变量，语法如下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CLOSE</STRONG> {cursor_variable | :host_cursor_variable};
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在下面的例子中，当最后一行数据处理完毕之后，我们就可以关闭游标变量emp_cv了： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> emp_cv<BR> <STRONG>INTO</STRONG> my_ename, my_sal;<BR><BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> emp_cv%NOTFOUND;<BR> <EM>-- process row</EM><BR><STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR><STRONG>CLOSE</STRONG> emp_cv; <EM>-- close cursor variable</EM>
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>4、记录，集合和对象类型的动态SQL举例</P>
<P>下面，演示一下如何从结果集中取得数据放到一个记录中去： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> empcurtyp <STRONG>IS</STRONG> <STRONG>REF</STRONG> <STRONG>CURSOR</STRONG>;<BR><BR> emp_cv empcurtyp;<BR> emp_rec emp%<STRONG>ROWTYPE</STRONG>;<BR> sql_stmt <STRONG>VARCHAR2</STRONG>(200);<BR> my_job <STRONG>VARCHAR2</STRONG>(15) := <EM>'CLERK'</EM>;<BR><STRONG>BEGIN</STRONG><BR> sql_stmt := <EM>'SELECT * FROM emp WHERE job = :j'</EM>;<BR><BR> <STRONG>OPEN</STRONG> emp_cv <STRONG>FOR</STRONG> sql_stmt USING my_job;<BR><BR> <STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> emp_cv<BR> <STRONG>INTO</STRONG> emp_rec;<BR><BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> emp_cv%NOTFOUND;<BR> <EM>-- process record</EM><BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>CLOSE</STRONG> emp_cv;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>下例演示对象和集合的用法。假定我们定义了对象类型Person和VARRAY类型Hobbises： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> Person <STRONG>AS</STRONG> OBJECT (name <STRONG>VARCHAR2</STRONG>(25), age <STRONG>NUMBER</STRONG>);<BR><STRONG>CREATE</STRONG> <STRONG>TYPE</STRONG> Hobbies <STRONG>IS</STRONG> VARRAY(10) <STRONG>OF</STRONG> <STRONG>VARCHAR2</STRONG>(25);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>现在，我们使用动态SQL编写一个利用到这些类型的包： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> teams <STRONG>AS</STRONG><BR> <STRONG>PROCEDURE</STRONG> create_table(tab_name <STRONG>VARCHAR2</STRONG>);<BR><BR> <STRONG>PROCEDURE</STRONG> insert_row(tab_name <STRONG>VARCHAR2</STRONG>, p person, h hobbies);<BR><BR> <STRONG>PROCEDURE</STRONG> print_table(tab_name <STRONG>VARCHAR2</STRONG>);<BR><STRONG>END</STRONG>;<BR><BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> teams <STRONG>AS</STRONG><BR> <STRONG>PROCEDURE</STRONG> create_table(tab_name <STRONG>VARCHAR2</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'CREATE TABLE '</EM><BR> || tab_name<BR> || <EM>' (pers Person, hobbs Hobbies)'</EM>;<BR> <STRONG>END</STRONG>;<BR><BR> <STRONG>PROCEDURE</STRONG> insert_row(tab_name <STRONG>VARCHAR2</STRONG>, p person, h hobbies) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'INSERT INTO '</EM> || tab_name || <EM>' VALUES (:1, :2)'</EM><BR> USING p, h;<BR> <STRONG>END</STRONG>;<BR><BR> <STRONG>PROCEDURE</STRONG> print_table(tab_name <STRONG>VARCHAR2</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>TYPE</STRONG> refcurtyp <STRONG>IS</STRONG> <STRONG>REF</STRONG> <STRONG>CURSOR</STRONG>;<BR><BR> CV refcurtyp;<BR> p person;<BR> h hobbies;<BR> <STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> CV <STRONG>FOR</STRONG> <EM>'SELECT pers, hobbs FROM '</EM> || tab_name;<BR><BR> <STRONG>LOOP</STRONG><BR> <STRONG>FETCH</STRONG> CV<BR> <STRONG>INTO</STRONG> p, h;<BR><BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> CV%NOTFOUND;<BR> <EM>-- print attributes of 'p' and elements of 'h'</EM><BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>CLOSE</STRONG> CV;<BR> <STRONG>END</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>我们可以像下面这样从匿名块中调用包teams中的过程：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> team_name <STRONG>VARCHAR2</STRONG>(15);<BR> ...<BR><STRONG>BEGIN</STRONG><BR> ...<BR> team_name := <EM>'Notables'</EM>;<BR> teams.create_table(team_name);<BR> teams.insert_row(team_name, person(<EM>'John'</EM>, 31),<BR> hobbies(<EM>'skiing'</EM>, <EM>'coin collecting'</EM>, <EM>'tennis'</EM>));<BR> teams.insert_row(team_name, person(<EM>'Mary'</EM>, 28),<BR> hobbies(<EM>'golf'</EM>, <EM>'quilting'</EM>, <EM>'rock climbing'</EM>));<BR> teams.print_table(team_name);<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>五、使用批量动态SQL</P>
<P>批量绑定能减少PL/SQL和SQL引擎之间的切换，改善性能。使用下面的命令、子句和游标属性，我们就能构建批量绑定的SQL语句，然后在运行时动态地执行：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>BULK</STRONG> <STRONG>FETCH</STRONG> 语句<BR><STRONG>BULK</STRONG> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> 语句<BR><STRONG>FORALL</STRONG> 语句<BR><STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> 子句<BR>RETURNING <STRONG>INTO</STRONG> 子句<BR>%BULK_ROWCOUNT 游标属性
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>1、动态批量绑定语法</P>
<P>批量绑定能让Oracle把SQL语句中的一个变量与一个集合相绑定。集合类型可以是任何PL/SQL集合类型(索引表、嵌套表或变长数组)。但是，集合元素必须是SQL数据类型，如CHAR、DATE或NUMBER。有三种语句支持动态批量绑定：EXECUTE
IMMEDIATE、FETCH和FOR ALL。 </P>
<UL>
<LI>批量EXECUTE IMMEDIATE </LI></UL>
<P>这个语句能让我们把变量或OUT绑定参数批量绑定到一个动态的SQL语句，语法如下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> dynamic_string<BR> [[<STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG>] <STRONG>INTO</STRONG> define_variable[, define_variable ...]]<BR> [USING bind_argument[, bind_argument ...]]<BR> [{RETURNING | <STRONG>RETURN</STRONG>}<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> bind_argument[, bind_argument ...]];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在动态多行查询中，我们可以使用BULK COLLECT
INTO子句来绑定变量。在返回多行结果的动态INSERT、UPDATE或DELETE语句中，我们可以使用RETURNING
BULK COLLECT INTO子句来批量绑定输出变量。 </P>
<UL>
<LI>批量FETCH </LI></UL>
<P>这个语句能让我们从动态游标中取得数据，就跟从静态游标中取得的方法是一样的。语法如下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FETCH</STRONG> dynamic_cursor<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> define_variable[, define_variable ...];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>如果在BULK COLLECT INTO中的变量个数超过查询的字段个数，Oracle就会产生错误。
</P>
<UL>
<LI>批量FORALL </LI></UL>
<P>这个语句能让我们在动态SQL语句中批量绑定输入参数。此外，我们还可以在FORALL内部使用EXECUTE
IMMEDIATE语句。语法如下： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FORALL</STRONG> index <STRONG>IN</STRONG> lower bound..upper bound<BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> dynamic_string<BR> USING bind_argument | bind_argument(index)<BR> [, bind_argument | bind_argument(index)] ...<BR> [{RETURNING | <STRONG>RETURN</STRONG>} <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG><BR> <STRONG>INTO</STRONG> bind_argument[, bind_argument ... ]];
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>动态字符串必须是一个INSERT、UPDATE或DELETE语句(不可以是SELECT语句)。 </P>
<P class=title2>2、动态批量绑定实例</P>
<P>我们可以在动态查询中使用BULK COLLECT
INTO子句来绑定变量。如下例所示，我们可以在批量的FETCH或EXECUTE
IMMEDIATE语句中使用BULK COLLECT INTO。 </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> empcurtyp <STRONG>IS</STRONG> <STRONG>REF</STRONG> <STRONG>CURSOR</STRONG>;<BR><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> emp_cv empcurtyp;<BR> empnos numlist;<BR> enames namelist;<BR> sals numlist;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> emp_cv <STRONG>FOR</STRONG> <EM>'SELECT empno, ename FROM emp'</EM>;<BR><BR> <STRONG>FETCH</STRONG> emp_cv<BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> empnos, enames;<BR><BR> <STRONG>CLOSE</STRONG> emp_cv;<BR><BR> <STRONG>EXECUTE</STRONG> <STRONG>IMMEDIATE</STRONG> <EM>'SELECT sal FROM emp'</EM><BR> <STRONG>BULK</STRONG> <STRONG>COLLECT</STRONG> <STRONG>INTO</STRONG> sals;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>只有INSERT、UPDATE和DELETE语句才能拥有输出绑定参数。我们可以在EXECUTE
IMMDIATE的BULK RETURNING INTO子句中进行绑定： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -