动态SQL

Oracle PL/SQL块的编译分为两种，前期联编和后期联编。

前期联编即在编译期间SQL语句就已经准备好，大部分都属于这种，静态SQL就属于前期联编；

后期联编是在SQL在运行时才能确定，动态SQL属于这种编译方式；

 

动态SQL是不确定的sql，语法为：EXECUTE IMMDEIATE  '动态SQL语句' INTO '输出参数列表' USING  '邦定参数列表' ;

说明：

1、动态SQL是指DDL和不确定的DML（即带参数的DML）；

2、绑定参数列表为输入参数列表，即其类型为in类型，在运行时刻与动态SQL语句中的参数（实际上占位符，可以理解为函数里面的形式参数）进行绑定；

3、输出参数列表为动态SQL语句执行后返回的参数列表；

4、由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性。

-- 例子：

--1)、动态执行DDL语句：
CREATE OR REPLACE PROCEDURE sl_create_table_p(
     is_tablename IN VARCHAR2,
     is_fieldname1 IN VARCHAR2,
     is_fliedtype1 IN VARCHAR2,
     is_fieldname2 IN VARCHAR2,
     is_fliedtype2 IN VARCHAR2,
     is_fieldname3 IN VARCHAR2,
     is_fliedtype3 IN VARCHAR2
)AS
     vs_sql VARCHAR2(100);
BEGIN
     vs_sql := 'create table '||is_tablename||'('||is_fieldname1||' '||is_fliedtype1||
         ','||is_fieldname2||' '||is_fliedtype2||','is_fieldname3||' '||is_fliedtype3||
         ') TABLESPACE TS_TAB_BASE';
     DBMS_OUTPUT.put_line(vs_sql);
     EXECUTE IMMEDIATE vs_sql;
END;
/

--执行create_table_p 
SQL>EXECUTE sl_create_table_p('sl_test_table_t','f_id','number(10) not null','f_name','varchar2(20)');  


--2)、动态执行DML语句
CREATE OR REPLACE PROCEDURE create_table_p(
     is_table_name IN VARCHAR2,
     in_id IN NUMBER,
     is_name IN VARCHAR2
)AS
     vs_sql VARCHAR2(100);
     vn_count NUMBER(2);
BEGIN
     --插入
     vs_sql := 'insert into :1 (f_id,f_name) values(:2,:3)';
     EXECUTE IMMEDIATE vs_sql USING in_id,is_name;
     DBMS_OUTPUT.put_line(vs_sql);
 
     --查找
     vs_sql := 'select count(*) from '||is_table_name||' t where t.f_id=:f_id';
     EXECUTE IMMEDIATE vs_sql INTO vn_count USING in_id;
     DBMS_OUTPUT.put_line(vs_sql);
END;
/

--执行create_table_p SQL>
SQL>EXECUTE create_table_p('sl_test_table_t',1,'xiaoming');  


--3、动态执行DDL语句:采用辅值方式
DECLARE 
     vs_query_sql VARCHAR2(100);
     vs_insert_sql VARCHAR2(100);
     vs_update_sql VARCHAR2(100);
     vs_table_name VARCHAR2(30) := 'sl_test_table_t';
     vn_f_id NUMBER:=1;
     vs_f_name VARCHAR2(10) := 'xiaoming';
BEGIN
     --查询
     vs_query_sql := 'select * from '||vs_table_name
          ||' where vn_f_id='||vn_f_id||',vs_f_name='''||vs_f_name||'''';
     DBMS_OUTPUT.put_line(vs_query_sql);
     EXECUTE IMMEDIATE vs_query_sql;
 
     --插入
     vs_insert_sql := 'insert into '||vs_table_name||'(f_id,f_name,f_date) values('||
          vn_f_id||','''||vs_f_name||','''||SYSDATE||')''';
     DBMS_OUTPUT.put_line(vs_insert_sql);
     EXECUTE IMMEDIATE vs_insert_sql;
 
     --更新
     vs_update_sql := 'update '||vs_table_name||' set f_name='''||vs_f_name||
          ''',f_date='''||to_date('20070101','yyyymmdd')||''' where f_id='||vn_f_id;
     DBMS_OUTPUT.put_line(vs_update_sql);
     EXECUTE IMMEDIATE vs_update_sql;
 
     COMMIT;
END;
/