一、概述
在通常的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语句语法:
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子句)
begin
execute immediate 'create table ma_org(org_codevarchar2(20),org_name varchar2(254))';
execute immediate 'drop table ma_org';
end;
begin
execute immediate 'grant insert on ma_org to scott'
end;
三、处理 DML 语句
3.1、给动态语句传值(USING 子句)
如果dml语句包含占位符,那么在execute immediate语句之后必须要带有using子句;
declare
orgcode varchar2(10);
orgname varchar2(254);
begin
orgcode := 1200;
execute immediate 'select org_name fromma_org
where org_code = :X'
into orgname
-- 传参给x的值
using orgcode;
dbms_output.put_line(orgname);
end;
3.2、动态调用存储过程
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;