数据库技术:executeimmediate

execute immediate dynamic_string [into {define_variable[,define_variable]…| record}] [using [in | out | in out] bind_argument[,[in | out  in out] bind_argumnet]…] [{returning | return} into bind_argument[,bind_argument]…];

本地动态sql语句一个优点是可以在代码中是使用绑定变量。

先说说绑定变量 “:“称绑定变量指示符，解释如下：

它是用户放入查询中的占位符，它会告诉oracle” 现在生成一个方案框架，实际执行语句的时候，会提供应该使用的实际值”。

例子如下：
select * from emp where dep=’sale’ ;                   //不使用绑定变量
select * from emp where dep=:sale                    //使用绑定变量

用法
　　　　处理ddl操作(create,alter,drop)
　　　　create or replace procedure drop_table(table_name varchar2)
　　　　is
　　　　 sql_statemet varchar2(100);
　　　　begin 
　　　　 sql_statement:=’drop table’ || table_name;
　　　　 execute immediate sql_statement;
　　　　end;
　　　　/
　　　　建立过程drop_table后，调用如下：
　　　　sql> exec drop_table(‘worker’)
　　　　处理dcl操作(grant revoke)
　　　　sql> conn system/manager
　　　　create or replace procedure grant_sys_priv(priv varchar2,username varchar2)
　　　　is
　　　　 sql_stat varchar2(100);
　　　　begin
　　　　 sql_stat:=’grant “ || priv|| ’ to ’|| username;
　　　　execute immediate sql_stat;
　　　　end;
　　　　/
　　　　调用
　　　　sql> exec grant_sys_priv(‘create session’,’scott’)
　　　　处理dml操作(insert update delete)
　　　　如果dml语句带有占位符，那么在e i语句中则要带using子句
　　　　如果dml语句带有returning子句，那么e i语句中要带有returninginto子句
　　　　例子，处理单行查询：
　　　　declare
　　　　 sql_stat varchar2(100);
　　　　 emp_record tbl%rowtype;
　　　　begin
　　　　 sql-stat:=’select * from tbl where tblno=:no’;
　　　　 execute immediate sql_stat into emp_record using &1;
　　　　 dbms_output.put_line(emp_record.ename||emp_record.sal);
　　　　end;

execute immediate — 用法例子

1. 在pl/sql运行ddl语句

begin
   execute immediate ‘set role all’;
end;

2. 给动态语句传值(using 子句)

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;

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;

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;

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;

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;

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;

需要了解更多数据库技术:execute immediate，都可以关注数据库技术分享栏目—编程笔记