--=========本地动态sql:(采用execute immediate)
--根据业务的需要,如果输入不同查询条件,
--则生成不同的执行SQL查询语句,对于这种情况需要使用动态SQL来完成
--Notice : PLSQL只能执行:DML(insert ,update , delete ) ,TCL(commit)语句
---------执行DDL语句,采用execute immediate
--首先看下如下PLSQL程序
declare
sql_stat VARCHAR2(100);
v_commit varchar2(20);
begin
-- 静态SQL :第一次运行时进行编译,而后续再次调用,则不再编译该过程。
-- 即一次编译,多次调用,使用的相同的执行计划
insert into emp values ( 8080,'out','developer',7900,sysdate,8999.9,2222,10);
-- 动态SQL: 每次运行时需要事先对其编译。即多次调用则需要多次编译.(结尾不能要 ; 号);
sql_stat :='DELETE FROM emp WHERE empno=8080'; --使用动态SQL来删除记录
v_commit := 'commit';
EXECUTE IMMEDIATE sql_stat; --每次执行 每次编译
EXECUTE IMMEDIATE v_commit;
END;
-----执行过程:
----1.pl/sql程序分为编译和执行阶段
----2.sql语句分为解析(编译) 和执行
----3.静态sql语句编译(分析) 发生在pl/sql程序的编译阶段.
----4.动态SQL( 用'' 的语句) 是在plsql执行的时候 才会进行分析和执行
--分析下面的SQL
declare
begin
execute immediate 'create table t1 (c1 number(2) )';
insert into t1 values 1;
commit;
end;
-- ERROR:表或者视图不存在
-- why ?
--在PLSQL编译阶段 ''中的内容当成字符串,不会解析成SQL
-- insert 是静态SQL,在PLSQL编译阶段,会完成语句的分析(语句语法,提及对象,确认对象权限)
-- 但是在提及对象的时候发现 t1 不存在,所以会出错.
------修改如下----
declare
begin
execute immediate 'create table t1 (c1 number(2) )';
execute immediate 'insert into t1 values (1)';
execute immediate 'commit';
end;
-- ok:分析
--在PLSQL编译阶段 create , insert , commit 都是字符段,不会解析SQL
--在PLSQL执行阶段,完成 create , insert ,commit的解析和执行操作
--====================DML语句+参数操作(using子句)====================
-- 对于使用了参数传入的动态SQL,需要使用USING子句来指明传入的参数
-- 在DML语句中使占位符,
--占位符: 用以冒号开头,紧跟任意字母或数字表示
-- 因此在使用EXECUTE IMMEDIATE使用USING子句为其指定其参数。
-- DDL语句不能使用 占位符
declare
v_c1 number(2) := 1;
v_c2 varchar2(10) :='aa';
begin
--error: PLSQL不支持 静态DDL语句 sql不能执行
--create table t1 (c1 number(1), c2 varchar2(10) ;--error
-- 动态SQL 创建表
execute immediate 'create table t1 (c1 number(1), c2 varchar2(10))';
-- 动态SQL插入+ using参数
-- DML语句中使用了占位符( :c1, :c2 )
execute immediate 'insert into t1 values'||
' ( :c1 , :c2)' using v_c1 , v_c2;--为占位符指定参数或值
execute immediate 'commit';
end;
--======Returning 子句 +DML语句==============
--更新7900的sal,返回更新后的sal
DECLARE
salary number(6, 2);
sql_stat varchar2(100);
v_deptno number(4) :=7900;
BEGIN
sql_stat := 'UPDATE emp SET sal = 999.99' ||
' WHERE empno = :eno RETURNING sal INTO :salary'; --使用了占位符:eno,:salary,以及RETURNING子句
EXECUTE IMMEDIATE sql_stat USING v_deptno RETURNING INTO salary; --必须使用USING及RETURNING子句
COMMIT;
dbms_output.put_line('salary: ' || salary);
END;
更详细的参见:那些大神博客篇 第一个链接