第十一章本地动态SQL

最新推荐文章于 2025-12-05 12:37:18 发布

最新推荐文章于 2025-12-05 12:37:18 发布 · 101 阅读

文章标签：

<div id="chapter">第十一章本地动态SQL</div>

一、什么是动态SQL
大多数PL/SQL都做着一件特殊的结果可预知的工作。例如，一个存储过程可能接受一个雇员的编号和他的提薪金额，然后更新表emp中的信息。在这
种情况下，UPDATE的全部文本内容在编译期就完全确定下来，这样的语句不会随着程序的执行而发生变化。所以，称它们为静态SQL语句。 
但是，有些程序只能是在运行时建立并处理不同的SQL语句。例如，一般用途的报告打印就可能会根据用户的选择内容不同，而使我们的SELECT内容也随之变化，然后打印出相应的数据来。这样的语句在编译期是无法确定它的内容的，所以称它们为动态SQL语句。 
动态SQL语句是在运行时由程序创建的字符串，它们必须是有效的SQL语句或PL/SQL块。它们也可以包含用于数据绑定的占位符。占位符是未声明的标识符，所以，它的名称并不重要，只需以冒号开头。例如，对于下面的字符串来说，PL/SQL不会认为它们有什么不同： 
<blockquote>
<table border="0"><tbody><tr>
<td>
'DELETEFROMempWHEREsal>:my_salANDcomm<:my_comm'
 'DELETEFROMempWHEREsal>:sANDcomm<:c'
</td>
</tr></tbody></table>
</blockquote>
我们使用EXECUTE IMMEDIATE语句处理大多数的动态SQL语句。但是，要处理多行查询(SELECT语句)，就必须使用OPEN-FOR、FETCH和CLOSE语句。 
二、动态SQL的需求
只有在下的情况下我们才需要使用动态SQL： 
<ol>
<li>执行数据定义语句(如CREAET)，数据控制语句(如GRANT)或会话控制语句(如ALTER SESSION)。因为在PL/SQL中，这样的语句是不允许静态执行的。
</li>
<li>为了获取更多的灵活性。例如，我们想在运行时根据我们自己的实际需求来为SELECT语句的WHERE子句选择不同的schema对象。
</li>
<li>动态地使用包DBMS_SQL执行SQL语句，但是为了获得更好的性能，灵活方便或是DBMS_SQL不支持的功能(如对象和集合的操作)。 </li>
</ol>
三、使用EXECUTE IMMEDIATE语句
EXECUTE IMMEDIATE语句能分析要执行动态的SQL语句或是匿名PL/SQL块。语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
EXECUTE
IMMEDIATE
dynamic_string 
[INTO
{define_variable[,define_variable]...|record}] 
[USING[IN
|OUT
|IN
OUT
]bind_argument 
[,[IN
|OUT
|IN
OUT
]bind_argument]...] 
[{RETURNING|RETURN
}INTO
bind_argument[,bind_argument]...]; </td>
</tr></tbody></table>
</blockquote>
dynamic_string是代表一条SQL语句或一个PL/SQL块的字符串表达式，define_variable是用于存放被选出的字段值
的变量，record是用户定义或%ROWTYPE类型的记录，用来存放被选出的行记录。输入bind_argument参数是一个表达式，它的值将被传
入(IN模式)或传出(OUT模式)或先传入再传出(IN
OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。
除了多行查询外，动态字符串可以包含任何SQL语句(不含终结符)或PL/SQL块(含终结符)。字符串中可以包括用于参数绑定的占位符。但是，不可以使用绑定参数为动态SQL传递模式对象。 
在用于单行查询时，INTO子句要指明用于存放检索值的变量或记录。对于查询检索出来的每一个值，INTO子句中都必须有一个与之对应的、类型兼容
的变量或字段。在用于DML操作时，RETURNING
INTO子句要指明用于存放返回值的变量或记录。对于DML语句返回的每一个值，INTO子句中都必须有一个与之对应的、类型兼容的变量或字段。 
我们可以把所有的绑定参数放到USING子句中。默认的参数模式是IN。对于含有RETURNING子句的DML语句来说，我们可以把OUT参数放
到RETURNING INTO之后，并且不用指定它们的参数模式，因为默认就是OUT。如果我们既使用了USING又使用RETURNING
INTO，那么，USING子句中就只能包含IN模式的参数了。 
运行时，动态字符串中的绑定参数会替换相对应的占位符。所以，每个占位符必须与USING子句和/或RETURNING
INTO子句中的一个绑定参数对应。我们可以使用数字、字符和字符串作为绑定参数，但不能使用布尔类型(TRUE，FALSE和NULL)。要把空值传递
给动态字符串，我们就必须使用工作区。 
动态SQL支持所有的SQL类型。所以，定义变量和绑定变量都可以是集合、LOB，对象类型实例和引用。作为一项规则，动态SQL是不支持PL/SQL特有的类型的。这样，它就不能使用布尔型或索引表。 
我们可以重复为绑定变量指定新值执行动态SQL语句。但是，每次都会消耗很多资源，因为EXECUTE IMMEDIATE在每次执行之前都需要对动态字符串进行预处理。 
1、动态SQL实例
下面的PL/SQL块包含了几个动态SQL的例子： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
sql_stmtVARCHAR2
(200); 
plsql_blockVARCHAR2
(500); 
emp_idNUMBER
(4):=7566; 
salaryNUMBER
(7,2); 
dept_idNUMBER
(2):=50; 
dept_nameVARCHAR2
(14):='PERSONNEL'
; 
LOCATIONVARCHAR2
(13):='DALLAS'
; 
emp_recemp%ROWTYPE
; BEGIN
 EXECUTE
IMMEDIATE
'CREATETABLEbonus(idNUMBER,amtNUMBER)'
; 
sql_stmt:='INSERTINTOdeptVALUES(:1,:2,:3)'
; EXECUTE
IMMEDIATE
sql_stmt 
USINGdept_id,dept_name,LOCATION; 
sql_stmt:='SELECT*FROMempWHEREempno=:id'
; EXECUTE
IMMEDIATE
sql_stmt INTO
emp_rec 
USINGemp_id; 
plsql_block:='BEGINemp_pkg.raise_salary(:id,:amt);END;'
; EXECUTE
IMMEDIATE
plsql_block 
USING7788,500; 
sql_stmt:= 'UPDATEempSETsal=2000WHEREempno=:1RETURNINGsalINTO:2'
; EXECUTE
IMMEDIATE
sql_stmt 
USINGemp_id 
RETURNINGINTO
salary; EXECUTE
IMMEDIATE
'DELETEFROMdeptWHEREdeptno=:num'
 
USINGdept_id; EXECUTE
IMMEDIATE
'ALTERSESSIONSETSQL_TRACETRUE'
; END
; </td>
</tr></tbody></table>
</blockquote>
下例中，过程接受一个数据表名(如"emp")和一个可选的WHERE子句(如"sal > 2000")。如果我们没有提供WHERE条件，程序会删除指定表中所有的行，否则就会按照给定的条件删除行： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
delete_rows( 
table_nameIN
VARCHAR2
, 
conditionIN
VARCHAR2
DEFAULT
NULL
 
)AS
 
where_clauseVARCHAR2
(100):='WHERE
'
||condition; BEGIN
 IF
conditionIS
NULL
THEN
 
where_clause:=NULL
; END
IF
; EXECUTE
IMMEDIATE
'DELETE
FROM
'
||table_name||where_clause; EXCEPTION
 
... END
; </td>
</tr></tbody></table>
</blockquote>
2、USING子句的向后兼容
当动态INSERT、UPDATE或DELETE语句有一个RETURNING子句时，输出绑定参数可以放到RETURNING
INTO或USING子句的后面。XXXXXXXXXX在新的应用程序中要使用RETURNING
INTO，而旧的应用程序可以继续使用USING，如下例： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
sql_stmtVARCHAR2
(200); 
my_empnoNUMBER
(4):=7902; 
my_enameVARCHAR2
(10); 
my_jobVARCHAR2
(9); 
my_salNUMBER
(7,2):=3250.00; BEGIN
 
sql_stmt:='UPDATEempSETsal=:1WHEREempno=:2'
 
||'RETURNINGename,jobINTO:3,:4'
; /*BindreturnedvaluesthroughUSINGclause.*/
 EXECUTE
IMMEDIATE
sql_stmt 
USINGmy_sal,my_empno,OUT
my_ename,OUT
my_job; /*BindreturnedvaluesthroughRETURNINGINTO
clause.*/
 EXECUTE
IMMEDIATE
sql_stmt 
USINGmy_sal,my_empno 
RETURNINGINTO
my_ename,my_job; 
... END
; </td>
</tr></tbody></table>
</blockquote>
3、指定参数模式
使用USING子句时，我们不需要为输入参数指定模式，因为默认的就是IN；而RETURNING INTO子句中我们是不可以指定输出参数的模式的，因为定义中它就是OUT模式。看一下下面的例子： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
sql_stmtVARCHAR2
(200); 
dept_idNUMBER
(2):=30; 
old_locVARCHAR2
(13); BEGIN
 
sql_stmt:='DELETEFROMdeptWHEREdeptno=:1RETURNINGlocINTO:2'
; EXECUTE
IMMEDIATE
sql_stmt 
USINGdept_id 
RETURNINGINTO
old_loc; 
... END
; </td>
</tr></tbody></table>
</blockquote>
在适当的时候，我们必须为绑定参数指定OUT或IN OUT模式。例如，假定我们想调用下面的过程： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
create_dept( 
deptnoIN
OUT
NUMBER
, 
dnameIN
VARCHAR2
, 
locIN
VARCHAR2
 
)AS
 BEGIN
 SELECT
deptno_seq.NEXTVAL
 INTO
deptno FROM
DUAL; INSERT
INTO
dept VALUES
(deptno,dname,loc); END
; </td>
</tr></tbody></table>
</blockquote>
要从动态PL/SQL块调用过程，就必须为与形参关联的绑定参数指定IN OUT模式，如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
plsql_blockVARCHAR2
(500); 
new_deptnoNUMBER
(2); 
new_dnameVARCHAR2
(14):='ADVERTISING'
; 
new_locVARCHAR2
(13):='NEWYORK'
; BEGIN
 
plsql_block:='BEGINcreate_dept(:a,:b,:c);END;'
; EXECUTE
IMMEDIATE
plsql_block 
USINGIN
OUT
new_deptno,new_dname,new_loc; IF
new_deptno>90THEN
... END
; </td>
</tr></tbody></table>
</blockquote>
四、使用OPEN-FOR、FETCH和CLOSE语句
我们可以使用三种语句来处理动态多行查询：OPEN-FOR，FETCH和CLOSE。首先，用OPEN打开多行查询的游标变量。然后，用FETCH语句把数据从结果集中取出来。当所有的数据都处理完以后，就可以用CLOSE语句关闭游标变量了。 
1、打开游标变量
OPEN-FOR语句可以把游标变量和一个多行查询关联起来，然后执行查询，确定结果集，并把游标放到结果集的第一行，然后把%ROWCOUNT值初始化为零。 
与OPEN-FOR的静态形式不同的是，动态形式有一个可选的USING子句。在运行时，USING子句中的绑定变量可以替换动态SELECT语句中相对应的占位符，语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
OPEN
{cursor_variable|:host_cursor_variable}FOR
dynamic_string 
[USINGbind_argument[,bind_argument]...]; </td>
</tr></tbody></table>
</blockquote>
其中，cursor_variable是一个弱类型(没有返回类型)的游标变量，host_cursor_variable是声明在PL/SQL主环境中的游标变量，dynamic_string是字符串表达式，代表一个多行查询。 
在下面的例子中，我们声明一个游标变量，并把它和动态SELECT语句关联起来： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
empcurtypIS
REF
CURSOR
;--defineweakREFCURSORtype
 
emp_cvempcurtyp;--declarecursorvariable
 
my_enameVARCHAR2
(15); 
my_salNUMBER
:=1000; BEGIN
 OPEN
emp_cvFOR
--opencursorvariable
 'SELECTename,salFROMempWHEREsal>:s'
USINGmy_sal; 
... END
; </td>
</tr></tbody></table>
</blockquote>
其中绑定参数的值只在游标变量打开时计算一次。所以，如果我们想使用一个新的绑定值进行查询，就必须重新打开游标变量。 
2、从游标变量取得数据
FETCH语句可以从多行查询的结果集中返回单独的一行数据，并把数据内容赋值给INTO子句后的对应的变量，然后属性%ROWCOUNT增加一，游标移到下一行，语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
FETCH
{cursor_variable|:host_cursor_variable} INTO
{define_variable[,define_variable]...|record}; </td>
</tr></tbody></table>
</blockquote>
继续上面的例子，我们把从游标变量emp_cv取得的数据放到变量my_ename和my_sal：
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 FETCH
emp_cv INTO
my_ename,my_sal;--fetchnextrow
 EXIT
WHEN
emp_cv%NOTFOUND;--exitloopwhenlastrowisfetched
 --processrow
 END
LOOP
; </td>
</tr></tbody></table>
</blockquote>
3、关闭游标变量
CLOSE语句能够关闭游标变量，语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CLOSE
{cursor_variable|:host_cursor_variable}; </td>
</tr></tbody></table>
</blockquote>
在下面的例子中，当最后一行数据处理完毕之后，我们就可以关闭游标变量emp_cv了： 
<blockquote>
<table border="0"><tbody><tr>
<td>
LOOP
 FETCH
emp_cv INTO
my_ename,my_sal; EXIT
WHEN
emp_cv%NOTFOUND; --processrow
 END
LOOP
; CLOSE
emp_cv;--closecursorvariable
</td>
</tr></tbody></table>
</blockquote>
4、记录，集合和对象类型的动态SQL举例
下面，演示一下如何从结果集中取得数据放到一个记录中去： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
empcurtypIS
REF
CURSOR
; 
emp_cvempcurtyp; 
emp_recemp%ROWTYPE
; 
sql_stmtVARCHAR2
(200); 
my_jobVARCHAR2
(15):='CLERK'
; BEGIN
 
sql_stmt:='SELECT*FROMempWHEREjob=:j'
; OPEN
emp_cvFOR
sql_stmtUSINGmy_job; LOOP
 FETCH
emp_cv INTO
emp_rec; EXIT
WHEN
emp_cv%NOTFOUND; --processrecord
 END
LOOP
; CLOSE
emp_cv; END
; </td>
</tr></tbody></table>
</blockquote>
下例演示对象和集合的用法。假定我们定义了对象类型Person和VARRAY类型Hobbises： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
TYPE
PersonAS
OBJECT(nameVARCHAR2
(25),ageNUMBER
); CREATE
TYPE
HobbiesIS
VARRAY(10)OF
VARCHAR2
(25); </td>
</tr></tbody></table>
</blockquote>
现在，我们使用动态SQL编写一个利用到这些类型的包： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PACKAGE
teamsAS
 PROCEDURE
create_table(tab_nameVARCHAR2
); PROCEDURE
insert_row(tab_nameVARCHAR2
,pperson,hhobbies); PROCEDURE
print_table(tab_nameVARCHAR2
); END
; CREATE
PACKAGE
BODY
teamsAS
 PROCEDURE
create_table(tab_nameVARCHAR2
)IS
 BEGIN
 EXECUTE
IMMEDIATE
'CREATETABLE'
 
||tab_name 
||'(persPerson,hobbsHobbies)'
; END
; PROCEDURE
insert_row(tab_nameVARCHAR2
,pperson,hhobbies)IS
 BEGIN
 EXECUTE
IMMEDIATE
'INSERTINTO'
||tab_name||'VALUES(:1,:2)'
 
USINGp,h; END
; PROCEDURE
print_table(tab_nameVARCHAR2
)IS
 TYPE
refcurtypIS
REF
CURSOR
; 
CVrefcurtyp; 
pperson; 
hhobbies; BEGIN
 OPEN
CVFOR
'SELECTpers,hobbsFROM'
||tab_name; LOOP
 FETCH
CV INTO
p,h; EXIT
WHEN
CV%NOTFOUND; --printattributesof'p'andelementsof'h'
 END
LOOP
; CLOSE
CV; END
; END
; </td>
</tr></tbody></table>
</blockquote>
我们可以像下面这样从匿名块中调用包teams中的过程：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
team_nameVARCHAR2
(15); 
... BEGIN
 
... 
team_name:='Notables'
; 
teams.create_table(team_name); 
teams.insert_row(team_name,person('John'
,31), 
hobbies('skiing'
,'coincollecting'
,'tennis'
)); 
teams.insert_row(team_name,person('Mary'
,28), 
hobbies('golf'
,'quilting'
,'rockclimbing'
)); 
teams.print_table(team_name); END
; </td>
</tr></tbody></table>
</blockquote>
五、使用批量动态SQL
批量绑定能减少PL/SQL和SQL引擎之间的切换，改善性能。使用下面的命令、子句和游标属性，我们就能构建批量绑定的SQL语句，然后在运行时动态地执行： 
<blockquote>
<table border="0"><tbody><tr>
<td>
BULK
FETCH
语句 BULK
EXECUTE
IMMEDIATE
语句 FORALL
语句 COLLECT
INTO
子句 
RETURNINGINTO
子句 
%BULK_ROWCOUNT游标属性 </td>
</tr></tbody></table>
</blockquote>
1、动态批量绑定语法
批量绑定能让Oracle把SQL语句中的一个变量与一个集合相绑定。集合类型可以是任何PL/SQL集合类型(索引表、嵌套表或变长数组)。但
是，集合元素必须是SQL数据类型，如CHAR、DATE或NUMBER。有三种语句支持动态批量绑定：EXECUTE
IMMEDIATE、FETCH和FOR ALL。 
<ul>
<li>批量EXECUTE IMMEDIATE </li>
</ul>
这个语句能让我们把变量或OUT绑定参数批量绑定到一个动态的SQL语句，语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
EXECUTE
IMMEDIATE
dynamic_string 
[[BULK
COLLECT
]INTO
define_variable[,define_variable...]] 
[USINGbind_argument[,bind_argument...]] 
[{RETURNING|RETURN
} BULK
COLLECT
INTO
bind_argument[,bind_argument...]]; </td>
</tr></tbody></table>
</blockquote>
在动态多行查询中，我们可以使用BULK COLLECT INTO子句来绑定变量。在返回多行结果的动态INSERT、UPDATE或DELETE语句中，我们可以使用RETURNING BULK COLLECT INTO子句来批量绑定输出变量。 
<ul>
<li>批量FETCH </li>
</ul>
这个语句能让我们从动态游标中取得数据，就跟从静态游标中取得的方法是一样的。语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
FETCH
dynamic_cursor BULK
COLLECT
INTO
define_variable[,define_variable...]; </td>
</tr></tbody></table>
</blockquote>
如果在BULK COLLECT INTO中的变量个数超过查询的字段个数，Oracle就会产生错误。 
<ul>
<li>批量FORALL </li>
</ul>
这个语句能让我们在动态SQL语句中批量绑定输入参数。此外，我们还可以在FORALL内部使用EXECUTE IMMEDIATE语句。语法如下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
FORALL
indexIN
lowerbound..upperbound EXECUTE
IMMEDIATE
dynamic_string 
USINGbind_argument|bind_argument(index) 
[,bind_argument|bind_argument(index)]... 
[{RETURNING|RETURN
}BULK
COLLECT
 INTO
bind_argument[,bind_argument...]]; </td>
</tr></tbody></table>
</blockquote>
动态字符串必须是一个INSERT、UPDATE或DELETE语句(不可以是SELECT语句)。 
2、动态批量绑定实例
我们可以在动态查询中使用BULK COLLECT INTO子句来绑定变量。如下例所示，我们可以在批量的FETCH或EXECUTE IMMEDIATE语句中使用BULK COLLECT INTO。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
empcurtypIS
REF
CURSOR
; TYPE
numlistIS
TABLE
OF
NUMBER
; TYPE
namelistIS
TABLE
OF
VARCHAR2
(15); 
emp_cvempcurtyp; 
empnosnumlist; 
enamesnamelist; 
salsnumlist; BEGIN
 OPEN
emp_cvFOR
'SELECTempno,enameFROMemp'
; FETCH
emp_cv BULK
COLLECT
INTO
empnos,enames; CLOSE
emp_cv; EXECUTE
IMMEDIATE
'SELECTsalFROMemp'
 BULK
COLLECT
INTO
sals; END
; </td>
</tr></tbody></table>
</blockquote>
只有INSERT、UPDATE和DELETE语句才能拥有输出绑定参数。我们可以在EXECUTE IMMDIATE的BULK RETURNING INTO子句中进行绑定： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
namelistIS
TABLE
OF
VARCHAR2
(15); 
enamesnamelist; 
bonus_amtNUMBER
:=500; 
sql_stmtVARCHAR
(200); BEGIN
 
sql_stmt:='UPDATEempSETbonus=:1RETURNINGenameINTO:2'
; EXECUTE
IMMEDIATE
sql_stmt 
USINGbonus_amt 
RETURNINGBULK
COLLECT
INTO
enames; END
; </td>
</tr></tbody></table>
</blockquote>
要在SQL语句中绑定输入参数，就要使用FORALL语句和USING子句，但这时的SQL语句不能是查询语句，如下例： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 TYPE
numlistIS
TABLE
OF
NUMBER
; TYPE
namelistIS
TABLE
OF
VARCHAR2
(15); 
empnosnumlist; 
enamesnamelist; BEGIN
 
empnos:=numlist(1,2,3,4,5); FORALL
iIN
1..5 EXECUTE
IMMEDIATE
'UPDATEempSETsal=sal*1.1WHEREempno=:1'
|| 'RETURNINGenameINTO:2'
 
USINGempnos(i) 
RETURNINGBULK
COLLECT
INTO
enames; 
... END
; </td>
</tr></tbody></table>
</blockquote>
六、动态SQL的技巧与陷阱
这节会让我们了解如何完全利用动态SQL语句并避免一些常见的缺陷。 
1、改善性能
下例中，Oracle为每个不同的emp_id单独打开一个游标。这就造成资源浪费并降低了效率： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
fire_employee(emp_idNUMBER
)AS
 BEGIN
 EXECUTE
IMMEDIATE
'DELETEFROMempWHEREempno='
||TO_CHAR(emp_id); END
; </td>
</tr></tbody></table>
</blockquote>
我们可以使用绑定变量来改善性能，如下例所示。这就能让Oracle为不同的emp_id值重用同一个游标。 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
fire_employee(emp_idNUMBER
)AS
 BEGIN
 EXECUTE
IMMEDIATE
'DELETEFROMempWHEREempno=:num'
 
USINGemp_id; END
; </td>
</tr></tbody></table>
</blockquote>
2、让过程对任意模式对象起作用
假设我们需要一个过程，让它接受数据表名，然后将指定的表从数据库中删除。我们可能会下面这样编写使用动态SQL的独立过程： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
drop_table(table_nameIN
VARCHAR2
)AS
 BEGIN
 EXECUTE
IMMEDIATE
'DROPTABLE:tab'
 
USINGtable_name; END
; </td>
</tr></tbody></table>
</blockquote>
但是，在运行的时候，这个过程可能会因为表名错误而无法执行成功。这就是我们为什么不能用参数绑定来为动态SQL传递模式对象的名称。解决方法是直接把参数嵌套到字符串中。我们把上面的EXECUTE IMMEDIATE语句修改一下： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
drop_table(table_nameIN
VARCHAR2
)AS
 BEGIN
 EXECUTE
IMMEDIATE
'DROP
TABLE
'
||table_name; END
; </td>
</tr></tbody></table>
</blockquote>
这样，我们就可以向动态SQL语句传递任意数据表名称了。 
3、使用重复占位符
动态SQL语句中的占位符与USING子句中的绑定参数是位置关联的，而不是名称关联。所以，如果在SQL语句中同样的占位符出现两次或多次，那么，它的每次出现都必须与一个USING子句中的绑定参数相关联。例如下面的动态字符串： 
<blockquote>
<table border="0"><tbody><tr>
<td>sql_stmt:='INSERTINTOpayrollVALUES(:x,:x,:y,:x)'
; </td>
</tr></tbody></table>
</blockquote>
我们可以为动态字符串编写对应的USING子句： 
<blockquote>
<table border="0"><tbody><tr>
<td>
EXECUTE
IMMEDIATE
sql_stmtUSINGa,a,b,a; </td>
</tr></tbody></table>
</blockquote>
但是，动态PL/SQL块中只有唯一的占位符才与USING子句中的绑定参数按位置对应。所以，如果一个占位符在PL/SQL块中出现两次或多次，
那么所有这样相同的占位符都只与USING语句中的一个绑定参数相对应。比如下面的例子，第一个占位符(x)与第一个绑定参数(a)关联，第二个占位符
(y)与第二个绑定参数(b)关联。 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
aNUMBER
:=4; 
bNUMBER
:=7; BEGIN
 
plsql_block:='BEGINcalc_stats(:x,:x,:y,:x);END'
; EXECUTE
IMMEDIATE
plsql_block 
USINGa,b; 
... END
; </td>
</tr></tbody></table>
</blockquote>
4、使用游标属性
每个显式的游标都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。它们都能返回与静态或动态SQL语句执行结果相关的有用信息。 
为处理SQL数据操作语句，Oracle会打开一个名为SQL的隐式游标。它的属性会返回最近一次执行的INSERT、UPDATE、DELETE或单行SELECT的相关信息。例如，下面函数就使用%ROWCOUNT返回从数据表中删除的行数： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
FUNCTION
rows_deleted(table_nameIN
VARCHAR2
,conditionIN
VARCHAR2
) RETURN
INTEGER
AS
 BEGIN
 EXECUTE
IMMEDIATE
'DELETEFROM'
||table_name||'WHERE'
||condition; RETURN
SQL
%ROWCOUNT;--returnnumberofrowsdeleted
 END
; </td>
</tr></tbody></table>
</blockquote>
同样，当我们把游标变量的名字附加进去时，游标的属性也能返回多行查询执行结果的相关信息。 
5、传递空值
下面，我们来为动态SQL传递空值，见下面的EXECUTE IMMEDIATE语句： 
<blockquote>
<table border="0"><tbody><tr>
<td>
EXECUTE
IMMEDIATE
'UPDATEempSETcomm=:x'
USINGNULL
; </td>
</tr></tbody></table>
</blockquote>
但是，这个语句会因为在USING子句中使用NULL而执行失败，因为USING语句中所传递的参数是不能为空的。所以，要想解决这个问题，直接使用字符串就可以了： 
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
a_nullCHAR
(1);--settoNULLautomaticallyatruntime
 BEGIN
 EXECUTE
IMMEDIATE
'UPDATEempSETcomm=:x'
 
USINGa_null; END
; </td>
</tr></tbody></table>
</blockquote>
6、远程操作
如下例所示，PL/SQL子程序能够执行引用远程数据库对象的动态SQL语句： 
<blockquote>
<table border="0"><tbody><tr>
<td>
PROCEDURE
delete_dept(db_linkVARCHAR2
,dept_idINTEGER
)IS
 BEGIN
 EXECUTE
IMMEDIATE
'DELETEFROMdept@'
||db_link||'WHEREdeptno=:num'
 
USINGdept_id; END
; </td>
</tr></tbody></table>
</blockquote>
同样，远程过程调用(RPC)的目标也包括动态SQL语句。例如，假设下面返回数据表中记录个数的函数存放在Chicago的数据库上： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
FUNCTION
row_count(tab_nameVARCHAR2
) RETURN
INTEGER
AS
 
ROWSINTEGER
; BEGIN
 EXECUTE
IMMEDIATE
'SELECTCOUNT(*)FROM'
||tab_name INTO
ROWS; RETURN
ROWS; END
; </td>
</tr></tbody></table>
</blockquote>
下面是一个从匿名SQL块调用远程函数的例子：
<blockquote>
<table border="0"><tbody><tr>
<td>
DECLARE
 
emp_countINTEGER
; BEGIN
 
emp_count:=row_count@chicago('emp'
); END
; </td>
</tr></tbody></table>
</blockquote>
7、使用调用者权限
默认情况下，存储过程是使用定义者权限执行的，而不是调用者权限。这样的过程是绑定在它们所属的模式对象上的。假设下面用于删除数据库对象的过程存放在模式对象scott上： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
drop_it(kindIN
VARCHAR2
,NAMEIN
VARCHAR2
)AS
 BEGIN
 EXECUTE
IMMEDIATE
'DROP'
||kind||''
||NAME; END
; </td>
</tr></tbody></table>
</blockquote>
我们把用户jones赋予允许他执行上面的存储过程的EXECUTE权限。当用户jones调用drop_it时，动态SQL就会使用用户scott的权限来执行语句： 
<blockquote>
<table border="0"><tbody><tr>
<td>
SQL
>CALLdrop_it('TABLE'
,'dept'
); </td>
</tr></tbody></table>
</blockquote>
这样的话，由于数据表dept的前面并没有限定修饰词进行限制，语句执行时删除的就是scott上的数据表，而不是jones上的。 
但是，AUTHID子句可以让存储过程按它的调用者权限来执行，这样的存储过程就不会绑定在一个特定的schema对象上。例如下面的新版本drop_it就会按调用者权限执行： 
<blockquote>
<table border="0"><tbody><tr>
<td>
CREATE
PROCEDURE
drop_it(kindIN
VARCHAR2
,NAMEIN
VARCHAR2
) AUTHID
CURRENT_USERAS
 BEGIN
 EXECUTE
IMMEDIATE
'DROP'
||kind||''
||NAME; END
; </td>
</tr></tbody></table>
</blockquote>
8、使用RESTRICT_REFERENCES
从SQL语句调用函数时，我们必须要遵守一定的规则来避免负面印象。为了检测冲突，我们可以使用编译指示RESTRICT_REFERENCES。它能确保函数没有读和/或写数据表和/或打包变量。 
但是，如果函数体包含了动态INSERT、UPDATE或DELETE语句，那它就总与规则"write no database state"
(WNDS)和"read no database state"
(RNDS)相冲突。这是因为动态SQL语句是在运行时才被检验，而不是编译期。在一个EXECUTE
IMMEDIATE语句中，只有INTO子句才能在编译期检验是否与RNDS冲突。 
9、避免死锁
有些情况下，执行SQL数据定义语句会导致死锁。例如，下面的过程就能引起死锁，因为它尝试着删除自身。为了避免死锁，就不要用ALTER或DROP来操作正在使用的子程序或包。 


CREATE
PROCEDURE
calc_bonus(emp_idNUMBER
)AS
 BEGIN
 
... EXECUTE
IMMEDIATE
'DROPPROCEDUREcalc_bonus'
;