在ORACLE的PL/SQL里： EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包. 它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。
-- 使用技巧
1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交如果通过EXECUTE IMMEDIATE处理DML命令，那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分. 如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据
2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.
3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.
4. 在Oracle手册中，未详细覆盖这些功能。下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.
5. 对于Forms开发者,当在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能. EXECUTE IMMEDIATE -- 用法例子
【1】 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
【2】. 给动态语句传值(USING 子句)
using配合绑定变量完成这个功能。绑定变量以“:”打头，名称是任意的，不仅仅可以是“:1”, “:2”等， 还可以是":ename" , ":loc"等等，总之随便符合命名规范的名称都行。
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values(:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
不使用using 和绑定变量下面的也可以执行成功：
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
insert into dept values(50, l_deptname, l_loc)'commit;
end;
【3】. 从动态语句检索值(INTO子句)
declare l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp' into l_cnt;
dbms_output.put_line(l_cnt);
end;
可以给一个变量输出值 INTO VAR1, 也可以给多个变量输出值INTO VAR1, VAR2,...，变量数量较少这样还可以。变量数量多的话，建议使用下面提到的记录类型变量。
【4】. 动态调用例程.
例程中用到的绑定变量参数必须指定参数类型. 黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number; l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4);end;' using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;create or replace procedure ex_routin(para1 IN number, para2 IN number ,para3 OUT number)
is
begin
para3 := para1 + para2;
end;
-------------------------------------------------------------
declare pa1 int :=11;
pa2 int :=22;
pa3 int;
v_sql varchar2(300);
begin
v_sql :='begin ex_routin(:1, :2, :3); end;';
execute immediate v_sql using IN pa1, IN pa2, OUT pa3;
dbms_output.put_line(pa3);
end;
-------------------------------------------------------
declare
pa1 int :=11;
pa2 int :=22;
pa3 int;
v_sql varchar2(200);
func varchar2(50) := 'ex_routin';
begin
v_sql := 'begin ' || func || '(:1, :2 , :3); end;';
execute immediate v_sql using IN pa1, IN pa2, OUT pa3;
dbms_output.put_line(pa3);
end;
-------------------------------------------------------------
1 declare
2 pa1 int :=11;
3 pa2 int :=22;
4 pa3 int;
5 v_sql varchar2(200);
6 func varchar2(50) := 'ex_routin';
7 begin
8 v_sql := 'begin :f ' || '(:1, :2 , :3); end;'; 9 execute immediate v_sql using IN func, IN pa1, IN pa2, OUT pa3;
10 dbms_output.put_line(pa3);
11* end;
> /
declare
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00110: bind variable '' not allowed in this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at line 9
【5】. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4), ename varchar2(20), deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' || 'from emp where empno = 7934' into empdtl;
end;
> select * from dept;
ENAME LOCATION AGE
-------------------- -------------------- ----------
Tom Beijing 50
Jack NewYork 50
1 declare
2 type dept_rec is record( name dept.ename%type, loc dept.location%type);
3 dept_row dept_rec;
4 begin
5 execute immediate 'select ename, location from dept ' into dept_row;
6 dbms_output.put_line(dept_row.name);
7 dbms_output.put_line(dept_row.loc);
8* end;
1 declare
2 dept_row dept%rowtype;
3 begin
4 execute immediate 'select * from dept where ename=''Tom''' into dept_row;
5 dbms_output.put_line(dept_row.ename);
6 dbms_output.put_line(dept_row.location);
7* end;
【6】. 传递并检索值.INTO子句用在USING子句前(*不能颠倒顺序*)
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1' intol_nam, l_loc using l_dept ;
end;
1 declare
2 v_sql varchar2(200);
3 l_name varchar2(20);
4 l_loc varchar2(20);
5 l_age int;
6 begin
7 l_name :='Tom';
8 v_sql := 'select location, age from dept where ename = :name';
9 execute immediate v_sqlinto l_loc, l_age usingIN l_name;
10 dbms_output.put_line(l_loc);
11 dbms_output.put_line(l_age);
end;
【7】. 多行查询选项.对此选项用insert语句填充临时表，用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' || ' select empno, ename from emp ' || ' where sal > :1' using l_sal;
commit;
end;
【8】returning into
return into 输出返回值,并且可以返回多个,这一点与into相似。(*感觉retruning into 返回变量好像是为OUT类型而生的。)
同时出现returning into 和using的时候，returning into 放在最后，注意顺序。
Used only for DML statements that have a RETURNINGclause (without a BULK COLLECTclause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in theRETURNINGINTOclause.
You can place all bind arguments in the USINGclause. The default parameter mode is IN. For DML statements that have a RETURNINGclause, you can place OUTarguments in the RETURNINGI NTOclause without specifying the parameter mode, which, by definition, is OUT.If you use both the USINGclause and the RETURNING INTOclause, the USINGclause can contain only INarguments.
Eample(1):
sqlplus >variable l_ename varchar2(10)
declare
emp_id number(4) := 7566;
begin
sql_stmt := 'update emp set sal=2000 where empno = :1 returning ename into :2';
execute immediate sql_stmt using emp_id returning into :l_ename;
end;
Example (2):
用returning 返回execute immediate 执行后影响的列将返回的信息传递给两个绑定变量占位符(:1,:2)，注意占位符可以使用任意名称，
我们可以对应的在immediate中使用returning接收绑定变量执行后的结果值
Sql代码
v_sql :='update employee set
salary=:salary where empno=:empno returning name,salary into :1,:2';
executeimmediate v_sql using'&员工工资更改为：',&员工编号 returningintov_name,n_salary;
dbms_output.put_line(v_name ||' 的工资更改为 '|| n_salary);
-- 使用Select返回隐式游标的结果集
v_sql :='select * from employee where empno=:empno';v_sql := 'update employee set salary=:salary where empno=:empno returning name,salary into :1,:2';
execute immediate v_sql using '&员工工资更改为：',&员工编号 returning into v_name,n_salary;
dbms_output.put_line(v_name || ' 的工资更改为 ' || n_salary);
-- 使用Select返回隐式游标的结果集
v_sql := 'select * from employee where empno=:empno';
因为Select语句本来就会返回值，所以在execute immediate中
就不再需要写Returning语句了,
记忆的方法是:如果在SQL字符串中含有Returning，那么在execute immediate就需要写Returning，反之就不需要写了
要注意的是在execute immediate子句中的using必须写在最后面
上述8步只能处理返回值为单行的记录，现在如果返回值为多行，那么他们都处理不了了。可以用【9】bulk collect into 、【10】 ref cursor来处理返回多行的情况。
【9】bulk collect into
bulk collect into 和into 顺序一样都是在using 前面。
bulk collect into 返回变量为table类型变量。
into 返回变量可以是单独的变量，也可以是记录类型的变量。
Example:
> select * from emp where empno <7566
2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
可看到返回多行，下面的bulk collect into 处理的最终结果是一致的。
declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
emp_row emp%rowtype;
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
sql_stmt varchar2(200);
begin
sql_stmt := 'select * from emp where empno < :empno';
execute immediate sql_stmt bulk collect into emp_table using &员工编号;
for i in 1..emp_table.count loop
emp_row := emp_table(i);
dbms_output.put_line(emp_row.ename || ' ,当前工资是:' || emp_row.sal || ', 负责的工作是:' || emp_row.job);
end loop;
end;
/
Enter value for 员工编号:7566
old 10: execute immediate sql_stmt bulk collect into emp_table using &员工编号;
new 10: execute immediate sql_stmt bulk collect into emp_table using 7566;
SMITH ,当前工资是:800, 负责的工作是:CLERK
ALLEN ,当前工资是:1600, 负责的工作是:SALESMAN
WARD ,当前工资是:1250, 负责的工作是:SALESMAN
【10】参考游标代替execute immediate
使用游标重写上述过程【9】。
1 declare
2 type emp_table_type is table of emp%rowtype index by binary_integer;
3 emp_table emp_table_type;
4 emp_row emp%rowtype;
5 type emp_cur_type is ref cursor;
6 emp_cur emp_cur_type;
7 sql_stmt varchar2(200);
8 begin
9 sql_stmt := 'select * from emp where empno 10 open emp_cur for sql_stmt using &输出的员工编号2 ;
11 loop
12 fetch emp_cur into emp_row;
13 exit when emp_cur%notfound;
14 dbms_output.put_line(emp_row.ename || ' ,当前工资是:' || emp_row.sal || ', 负责的工作是:' || emp_row.job);
15 end loop;
16* end;
> /
Enter value for 输出的员工编号2: 7566
old 10: open emp_cur for sql_stmt using &输出的员工编号2 ;
new 10: open emp_cur for sql_stmt using 7566 ;
SMITH ,当前工资是:800, 负责的工作是:CLERK
ALLEN ,当前工资是:1600, 负责的工作是:SALESMAN
WARD ,当前工资是:1250, 负责的工作是:SALESMAN
PL/SQL procedure successfully completed.
可见重写后结果是相同的。
对于处理动态语句,EXECUTE IMMEDIATE 比以前可能用到的更容易并且更高效. 当意图执行动态语句时，适当地处理异常更加重要.应该关注于捕获所有可能的异常.
1932
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
