一、概述
在一般的sql操作中，sql语句基本上都是固定的，如：
SELECT t.empno,t.ename FROM scott.emp t WHERE t.deptno = 20;
但有的时候，从应用的需要或程序的编写出发，都可能需要用到动态SQl，如：
当 from 后的表 不确定时，或者where 后的条件不确定时，都需要用到动态SQL。
使用execute immediate语句可以处理包括ddl（create、alter和drop）、DCL（grant、revoke）、DML（insert、update、delete）以及单行select语句。
execute immediate语句：
|
1 2 3 4 |
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]…] |
define_variable用于指定存放单行查询结果的变量； using in bind_argument用于指定存放传递给动态sql值的变量，即在dynamic中存在占位符时使用； using out bind_argument用于指定存放动态sql返回值的变量。
二、执行ddl、dcl语句
不能使用into和using子句。
|
1 2 3 4 5 |
begin
execute immediate 'create table ma_org(org_codevarchar2(20),org_name varchar2(254))' ; execute immediate 'drop table ma_org' ; end ; |
语句
|
1 2 3 |
begin execute immediate 'grant insert on ma_org to scott' end ; |
三、处理dml语句
1、给动态语句传值(USING 子句)
如果dml语句包含占位符，那么在execute immediate语句之后必须要带有using子句；
|
1 2 3 4 5 6 7 8 9 10 11 |
declare orgcode varchar2(10); orgname varchar2(254); begin orgcode := 1200; execute immediate 'select org_name fromma_org where org_code = :X' into orgname using orgcode; dbms_output.put_line(orgname); end ; |
2、从动态语句检索值(INTO子句)
3、动态调用存储过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 ; |
4、处理包含returing子句的DML语句
如果dml语句带有returning子句，那么在execute immediate语句之后必须带有returning into子句，并且此时只能处理作用的单行上的dml语句，如果dml语句作用在多行上，则必须使用bulk子句。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare orgcode varchar2(10); orgname varchar2(254); rname varchar2(254); begin orgcode := '1200' ; orgname := '天津市分行' ; execute immediate 'update ma_org set org_name=:X where org_code = :Y returning org_name into :rname' using orgname, orgcode returning into rname; dbms_output.put_line(orgname); end ; |
5、在retuing into中使用bulk collect into
四、处理多行查询
oracle通过使用bulk collect into子句处理动态sql中的多行查询可以加快处理速度，从而提高应用程序的性能。当使用bulk子句时，集合类型可以是plsql所支持的索引表、嵌套表和varray，但集合元素必须使用sql数据类型。在oracle9i以后，有三种语句支持bulk子句，execute immediate，fetch和forall。
1、使用动态游标（游标变量）处理多行查询类动态sql语句。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE TYPE ref_cur IS REF CURSOR ; rc ref_cur; emprow emp%ROWTYPE; v_sql VARCHAR2(100):= 'select * from emp where deptno = :x' ; --动态执行的SQL语句 BEGIN OPEN rc FOR v_sql USING 30; --打开游标，绑定执行的SQL语句，并传递参数 LOOP FETCH rc INTO emprow; EXIT WHEN rc%NOTFOUND; dbms_output.put_line( 'name:' ||emprow.ename|| ' sal:' ||emprow.sal); END LOOP; CLOSE rc; END ; |
2、在execute immediate中使用bulk collect into
示例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare type org_table_type is table of ma_org%rowtype; org_table org_table_type; v_orgcode varchar2(20); begin v_orgcode := '%00%' ; execute immediate 'select * from ma_org where org_code like:v_orgcode' bulk collect into org_table using v_orgcode; for i in 1..org_table. count loop dbms_output.put_line(org_table(i).org_code|| ',' ||org_table(i).org_name); end loop; end ; |
3、在forall语句中使用bulk collect into语句
示例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
declare type type_org_code is table of ma_org.org_code%type; type type_org_name is table of ma_org.org_name%type; v_orgcode type_org_code; v_orgname type_org_name; begin v_orgcode := type_org_code( '1100' , '1200' ); forall i in 1..v_orgcode. count execute immediate 'update ma_org set org_name = org_code||org_namewhere org_code = :p1 returning org_name into :p2' using v_orgcode(i) returning bulk collect into v_orgname; for i in v_orgname. first ..v_orgname. last
loop dbms_output.put_line(v_orgname(i)); end loop; end ; |
到此这篇关于Oracle中执行动态SQL的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://www.cnblogs.com/springsnow/archive/2011/12/09/2282528.html