EXECUTE IMMEDIATE 常见使用方法

  在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;
SELENE@orcl> /
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;


SELENE@orcl> 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' into l_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_sql into l_loc, l_age using IN 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 RETURNING clause (without a BULK COLLECT clause), 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 USING clause. The default parameter mode is IN.  For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNINGI NTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

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代码
  1. v_sql := 'update employee set 
  2. salary=:salary where empno=:empno returning name,salary into :1,:2';   
  3. execute immediate v_sql using '&员工工资更改为:',&员工编号 returning into v_name,n_salary;   
  4. dbms_output.put_line(v_name || ' 的工资更改为 ' || n_salary);   
  5. -- 使用Select返回隐式游标的结果集  
  6. 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:

SCOTT@orcl> 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 <: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;
SCOTT@orcl> /
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 比以前可能用到的更容易并且更高效. 当意图执行动态语句时,适当地处理异常更加重要.应该关注于捕获所有可能的异常.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-739404/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/27042095/viewspace-739404/

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值