Oracle数据库之PLSQL本地动态SQL

--=========本地动态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;


更详细的参见：那些大神博客篇 第一个链接