动态SQL之EXECUTE IMMEDIATE
1 EXECUTE IMMEDIATEoracle中DBMS_SQL package包和EXECUTE IMMEDIATE都可以用来解析并执行动态SQL语句或非运行时创建的PL/SQL块，相比较而言，EXECUTE IMMEDIATE使用较简单，能够满足较常用的需要。
1.1 语法
EXECUTE IMMEDIATE v_sql 。
说明：
1、v_sql为varchar2类型或clob（11g才支持），可以为DDL、DML等动态拼接的sql字符串。用在pl/sql代码中时，如果是varchar2类型，则长度不能大于32767(32K)。
2、v_sql为DML动态语句时，执行后不会提交，需要使用commit显式提交。如果为DDL命令,执行后则会提交所有之前改变的。
3、如果需要从动态sql返回值，则可以定义返回值变量，BULK COLLECT INTO返回多行值，此时定义的变量需是数组变量的列表或记录表类型；INTO返回单行，此时定义的变量可以使多个pl/sql变量的列表或记录类型。
4、如果动态sql中需要绑定变量，则使用USING，通常绑定的变量为输入入参，此时变量的in可以省略；如果需要绑定输出变量（如调用过程时可能需要输出），则在变量前用out显示指明。
1.2 实例说明
1.2.1 动态DDL
DECLAREv_sql VARCHAR2(1000);v_table VARCHAR2(30) := 'test_ynamic_sql';BEGINv_sql := ' create table ' || v_table || ' (id varchar2(10),name varchar2(100))';EXECUTE IMMEDIATE v_sql;END;
1.2.2 动态DML insert
1.2.2.1 不绑定输入变量
DECLAREv_sql VARCHAR2(1000);v_table VARCHAR2(30) := 'test_ynamic_sql';BEGIN--1、不绑定输入变量v_sql := ' insert into ' || v_table || ' values (''1'',''no_binding_in_variable'')';EXECUTE IMMEDIATE v_sql;COMMIT; --dml需要显示提交END;
1.2.2.2 绑定输入变量
DECLAREv_sql VARCHAR2(1000);v_table VARCHAR2(30) := 'test_ynamic_sql';BEGIN--1、绑定输入变量v_sql := ' insert into ' || v_table || ' values (:1,:2)';EXECUTE IMMEDIATE v_sql USING '2', 'binding_in_variable'; --使用using绑定输入变量END;
1.2.3 动态DML select
1.2.3.1 返回单行值
DECLAREv_sql VARCHAR2(1000);v_table VARCHAR2(30) := 'test_ynamic_sql';--1、使用简单pl/sql变量v_id,v_name获得单行输出v_id VARCHAR2(10);v_name VARCHAR2(100);--2、使用基于test_ynamic_sql表的记录变量获得单行输出TYPE test_ynamic_sql_record IS RECORD( v_id test_ynamic_sql.ID%TYPE, v_name test_ynamic_sql.NAME%TYPE);test_ynamic_sql_row test_ynamic_sql_record;BEGIN--1、使用简单pl/sql变量v_id,v_name获得单行输出v_sql := ' select id,name from ' || v_table || ' where id=:1 ';EXECUTE IMMEDIATE v_sql INTO v_id, v_name USING '1';DBMS_OUTPUT.put_line('id=' || v_id || ',name=' || v_name);--2、使用基于test_ynamic_sql表的记录变量获得单行输出EXECUTE IMMEDIATE v_sql INTO test_ynamic_sql_row USING '1';DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_row.v_id || ',name=' || test_ynamic_sql_row.v_name);END;
1.2.3.2 返回多行值
1.2.3.2.1 使用记录表获取
DECLAREv_sql VARCHAR2(1000);v_table VARCHAR2(30) := 'test_ynamic_sql';--1、使用基于test_ynamic_sql表的记录变量获得多行输出TYPE test_ynamic_sql_record IS RECORD( id test_ynamic_sql.ID%TYPE, NAME test_ynamic_sql.NAME%TYPE);TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql_record INDEX BY BINARY_INTEGER;/*可以用以下方式定义记录表*/--TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql%ROWTYPE INDEX BY BINARY_INTEGER;test_ynamic_sql_multi_row test_ynamic_sql_table_type;BEGIN--1、使用基于test_ynamic_sql表的记录变量获得多行输出v_sql := ' select id,name from ' || v_table;EXECUTE IMMEDIATE v_sql BULK COLLECT INTO test_ynamic_sql_multi_row;FOR m IN 1 .. test_ynamic_sql_multi_row.COUNT LOOP DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row(m) .id || ',name=' || test_ynamic_sql_multi_row(m).NAME);END LOOP;END;
1.2.3.2.2 使用多个嵌套表获取
DECLAREv_sql VARCHAR2(1000);v_table VARCHAR2(30) := 'test_ynamic_sql';--1、使用基于多个嵌套表获取多行输出TYPE test_ynamic_sql_id_type IS TABLE OF test_ynamic_sql.ID%TYPE INDEX BY BINARY_INTEGER;TYPE test_ynamic_sql_name_type IS TABLE OF test_ynamic_sql.NAME%TYPE INDEX BY BINARY_INTEGER;test_ynamic_sql_multi_row_id test_ynamic_sql_id_type;test_ynamic_sql_multi_row_name test_ynamic_sql_name_type;BEGIN--1、使用基于多个嵌套表获取多行输出v_sql := ' select id,name from ' || v_table;EXECUTE IMMEDIATE v_sql BULK COLLECT INTO test_ynamic_sql_multi_row_id, test_ynamic_sql_multi_row_name;FOR m IN 1 .. test_ynamic_sql_multi_row_id.COUNT LOOP DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row_id(m) || ',name=' || test_ynamic_sql_multi_row_name(m));END LOOP;END;
1.2.4 动态调用函数
1.2.4.1 使用select 获取返回值
DECLAREv_sqlVARCHAR2(1000);v_name VARCHAR2(100);BEGIN--1、先创建测试函数v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2) RETURN VARCHAR2 IS v_name VARCHAR2(100); BEGIN SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id; RETURN v_name; END ; ';EXECUTE IMMEDIATE v_sql;--2、使用select 获取返回值v_sql := ' select f_test_ynamic_sql(:1) from dual';EXECUTE IMMEDIATE v_sql INTO v_name USING '1';DBMS_OUTPUT.put_line(' NAME = ' || v_name);END;
1.2.4.2 使用begin .. end绑定函数输出变量
DECLAREv_sql VARCHAR2(1000);v_name_o VARCHAR2(100);BEGIN--1、先创建测试函数v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) RETURN VARCHAR2 IS v_name VARCHAR2(100); BEGIN SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id; v_name_o:=v_name; RETURN v_name; END ; ';EXECUTE IMMEDIATE v_sql;--2、使用begin .. end绑定函数输出变量v_sql := ' declare v_name varchar2(100); beginv_name:=f_test_ynamic_sql(:1,:2); end;';EXECUTE IMMEDIATE v_sql USING '1', OUT v_name_o;DBMS_OUTPUT.put_line('name_o=' || v_name_o); --using中的输出变量需要显示说明END;
1.2.5 动态调用过程
DECLAREv_sql VARCHAR2(1000);v_name_o VARCHAR2(100);BEGIN--1、先创建测试过程v_sql := ' CREATE OR REPLACE procedure p_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) IS BEGIN SELECT NAME INTO v_name_o FROM test_ynamic_sql WHERE id = v_id; END ; ';EXECUTE IMMEDIATE v_sql;--2、使用begin .. end绑定过程输出变量v_sql := ' beginp_test_ynamic_sql(:1,:2); end;';EXECUTE IMMEDIATE v_sql USING '1', OUT v_name_o; --using中的输出变量需要显示说明DBMS_OUTPUT.put_line('name_o=' || v_name_o);END;
页:
[1]