Oracle 存储过程 动态sql执行

1          EXECUTE IMMEDIATE

from：http://wushuangyan26.iteye.com/blog/1306094

        oracle中DBMS_SQL package包和EXECUTE IMMEDIATE都可以用来解析并执行动态SQL语句或非运行时创建的PL/SQL块，相比较而言，EXECUTE IMMEDIATE使用较简单，能够满足较常用的需要。

 

1.1         语法

 

           EXECUTE IMMEDIATE v_sql  [BULK COLLECT INTO 或INTO 返回值变量] [INTO 入参 1,.., out 出参1,..]。

说明：

      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

   

Sql代码  

1. DECLARE  
2.   v_sql   VARCHAR2(1000);  
3.   v_table VARCHAR2(30) := 'test_ynamic_sql';  
4. BEGIN  
5.   v_sql := ' create table ' || v_table ||  
6.            ' (id varchar2(10),name varchar2(100))';  
7.   EXECUTE IMMEDIATE v_sql;  
8. END;  

 

 

1.2.2        动态DML insert

1.2.2.1       不绑定输入变量

 

Sql代码  

1. DECLARE  
2.   v_sql   VARCHAR2(1000);  
3.   v_table VARCHAR2(30) := 'test_ynamic_sql';  
4. BEGIN  
5.   --1、不绑定输入变量  
6.   v_sql := ' insert into ' || v_table ||  
7.            ' values (''1'',''no_binding_in_variable'')';  
8.   EXECUTE IMMEDIATE v_sql;  
9.   COMMIT; --dml需要显示提交  
10. END;  

  

1.2.2.2       绑定输入变量

Sql代码  

1. DECLARE  
2.   v_sql   VARCHAR2(1000);  
3.   v_table VARCHAR2(30) := 'test_ynamic_sql';  
4. BEGIN  
5.   --1、绑定输入变量  
6.   v_sql := ' insert into ' || v_table || ' values (:1,:2)';  
7.   EXECUTE IMMEDIATE v_sql  
8.     USING '2', 'binding_in_variable'; --使用using绑定输入变量  
9. END;  

  

1.2.3        动态DML select

1.2.3.1       返回单行值

Sql代码  

1. DECLARE  
2.   v_sql   VARCHAR2(1000);  
3.   v_table VARCHAR2(30) := 'test_ynamic_sql';  
4.   --1、使用简单pl/sql变量v_id,v_name获得单行输出  
5.   v_id   VARCHAR2(10);  
6.   v_name VARCHAR2(100);  
7.   --2、使用基于test_ynamic_sql表的记录变量获得单行输出  
8.   TYPE test_ynamic_sql_record IS RECORD(  
9.     v_id   test_ynamic_sql.ID%TYPE,  
10.     v_name test_ynamic_sql.NAME%TYPE);  
11.   test_ynamic_sql_row test_ynamic_sql_record;  
12. BEGIN  
13.   --1、使用简单pl/sql变量v_id,v_name获得单行输出  
14.   v_sql := ' select id,name from ' || v_table || ' where id=:1 ';  
15.   EXECUTE IMMEDIATE v_sql  
16.     INTO v_id, v_name  
17.     USING '1';  
18.   DBMS_OUTPUT.put_line('id=' || v_id || ',name=' || v_name);  
19.   --2、使用基于test_ynamic_sql表的记录变量获得单行输出  
20.   EXECUTE IMMEDIATE v_sql  
21.     INTO test_ynamic_sql_row  
22.     USING '1';  
23.   DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_row.v_id || ',name=' ||  
24.                        test_ynamic_sql_row.v_name);  
25. END;  

 

1.2.3.2       返回多行值

1.2.3.2.1      使用记录表获取

 

Sql代码  

1. DECLARE  
2.   v_sql   VARCHAR2(1000);  
3.   v_table VARCHAR2(30) := 'test_ynamic_sql';  
4.   --1、使用基于test_ynamic_sql表的记录变量获得多行输出  
5.   TYPE test_ynamic_sql_record IS RECORD(  
6.     id   test_ynamic_sql.ID%TYPE,  
7.     NAME test_ynamic_sql.NAME%TYPE);  
8.   TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql_record INDEX BY BINARY_INTEGER;  
9.   /*可以用以下方式定义记录表*/  
10.   --TYPE test_ynamic_sql_table_type IS TABLE OF test_ynamic_sql%ROWTYPE INDEX BY BINARY_INTEGER;  
11.   test_ynamic_sql_multi_row test_ynamic_sql_table_type;  
12. BEGIN  
13.   --1、使用基于test_ynamic_sql表的记录变量获得多行输出  
14.   v_sql := ' select id,name from ' || v_table;  
15.   EXECUTE IMMEDIATE v_sql BULK COLLECT  
16.     INTO test_ynamic_sql_multi_row;  
17.   FOR m IN 1 .. test_ynamic_sql_multi_row.COUNT LOOP  
18.     DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row(m)  
19.                          .id || ',name=' || test_ynamic_sql_multi_row(m).NAME);  
20.   END LOOP;  
21. END;  

 

 

 

1.2.3.2.2      使用多个嵌套表获取

Sql代码  

1. DECLARE  
2.   v_sql   VARCHAR2(1000);  
3.   v_table VARCHAR2(30) := 'test_ynamic_sql';  
4.   --1、使用基于多个嵌套表获取多行输出  
5.   TYPE test_ynamic_sql_id_type IS TABLE OF test_ynamic_sql.ID%TYPE INDEX BY BINARY_INTEGER;  
6.   TYPE test_ynamic_sql_name_type IS TABLE OF test_ynamic_sql.NAME%TYPE INDEX BY BINARY_INTEGER;  
7.   test_ynamic_sql_multi_row_id   test_ynamic_sql_id_type;  
8.   test_ynamic_sql_multi_row_name test_ynamic_sql_name_type;  
9. BEGIN  
10.   --1、使用基于多个嵌套表获取多行输出  
11.   v_sql := ' select id,name from ' || v_table;  
12.   EXECUTE IMMEDIATE v_sql BULK COLLECT  
13.     INTO test_ynamic_sql_multi_row_id, test_ynamic_sql_multi_row_name;  
14.   FOR m IN 1 .. test_ynamic_sql_multi_row_id.COUNT LOOP  
15.     DBMS_OUTPUT.put_line('id=' || test_ynamic_sql_multi_row_id(m) ||  
16.                          ',name=' || test_ynamic_sql_multi_row_name(m));  
17.   END LOOP;  
18. END;  

 

1.2.4        动态调用函数

 1.2.4.1       使用select 获取返回值

 

Sql代码  

1. DECLARE  
2.   v_sql  VARCHAR2(1000);  
3.   v_name VARCHAR2(100);  
4. BEGIN  
5.   --1、先创建测试函数  
6.   v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2) RETURN VARCHAR2 IS  
7.              v_name VARCHAR2(100);  
8.                BEGIN  
9.                SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id;  
10.              RETURN v_name;  
11.             END ; ';  
12.   EXECUTE IMMEDIATE v_sql;  
13.   --2、  使用select 获取返回值  
14.   v_sql := ' select f_test_ynamic_sql(:1) from dual';  
15.   EXECUTE IMMEDIATE v_sql  
16.     INTO v_name  
17.     USING '1';  
18.   DBMS_OUTPUT.put_line(' NAME = ' || v_name);  
19. END;  

 

 

1.2.4.2       使用begin .. end绑定函数输出变量

 

 

Sql代码  

1. DECLARE  
2.   v_sql    VARCHAR2(1000);  
3.   v_name_o VARCHAR2(100);  
4. BEGIN  
5.   --1、先创建测试函数  
6.   v_sql := ' CREATE OR REPLACE FUNCTION f_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) RETURN VARCHAR2 IS  
7.              v_name VARCHAR2(100);  
8.                BEGIN  
9.                SELECT NAME INTO v_name FROM test_ynamic_sql WHERE id = v_id;  
10.                v_name_o:=v_name;  
11.              RETURN v_name;  
12.             END ; ';  
13.   EXECUTE IMMEDIATE v_sql;  
14.   --2、使用begin .. end绑定函数输出变量  
15.   v_sql := ' declare v_name varchar2(100);   
16.            begin  v_name:=f_test_ynamic_sql(:1,:2); end;';  
17.   EXECUTE IMMEDIATE v_sql  
18.     USING '1', OUT v_name_o;  
19.   DBMS_OUTPUT.put_line('name_o=' || v_name_o); --using中的输出变量需要显示说明  
20. END;  

 

1.2.5        动态调用过程

 

Sql代码  

1. DECLARE  
2.   v_sql    VARCHAR2(1000);  
3.   v_name_o VARCHAR2(100);  
4. BEGIN  
5.   --1、先创建测试过程  
6.   v_sql := ' CREATE OR REPLACE procedure p_test_ynamic_sql(v_id VARCHAR2,v_name_o out varchar2) IS  
7.                BEGIN  
8.                SELECT NAME INTO v_name_o FROM test_ynamic_sql WHERE id = v_id;  
9.             END ; ';  
10.   EXECUTE IMMEDIATE v_sql;  
11.   --2、使用begin .. end绑定过程输出变量  
12.   v_sql := ' begin  p_test_ynamic_sql(:1,:2); end;';  
13.   EXECUTE IMMEDIATE v_sql  
14.     USING '1', OUT v_name_o; --using中的输出变量需要显示说明  
15.   DBMS_OUTPUT.put_line('name_o=' || v_name_o);  
16. END;  

 

 

• oracle动态sql之EXECUTE_IMMEDIATE.rar (26.4 KB)
• 下载次数: 18