提示：公众号代码会自动换行，建议横屏阅读或者左右滑动代码观看
当开发动态SQL时，可以使用本地动态SQL和DBMS_SQL。因为本地动态SQL具有简单快速的特征，所以当开发动态SQL时，应该尽可能使用本地动态SQL。但是，如果要在客户端应用程序中使用动态SQL，或者实现更加灵活的动态SQL（输入输出未知），必须使用DBMS_SQL包实现动态SQL。下面通过示例说明使用DBMS_SQL实现动态SQL的方法。
一 DBMS_SQL常用变量
1.DBMS_SQL常量
1v6 constant INTEGER :=0;
2native constant INTEGER :=1;
3v7 constant INTEGER :=2;
2.DESC_REC记录类型
该记录类型用于存放动态SQL的单列信息，语法如下：
1TYPE desc_rec IS RECORD(
2 col_type BINARY_INTEGER :=0,
3 col_max_len BINARY_INTEGER :=0,
4 col_name VARCHAR2(32) :='',
5 col_name_len BINARY_INTEGER :=0,
6 col_schema VARCHAR2(32) :='',
7 col_schema_len BINARY_INTEGER :=0,
8 col_precision BINARY_INTEGER :=0,
9 col_scale BINARY_INTEGER :=0,
10 col_charsetform BINARY_INTEGER :=0,
11 col_null_ok BOOLEAN :=TRUE
12);
如上所示，col_type用于标识列类型，col_max_len用于标识列最大长度，col_name用于标识列名，col_name_len用于标识列名长度，col_schema_name用于标识列方案名，col_schema_name_len用于标识列方案名长度，col_precision用于标识列精度，col_scal用于标识列标度，col_charsetform用于标识字符集格式，col_null_ok用于标识是否允许NULL。其中还有DESC_REC2和DESC_REC3这三者几乎大体上相同。
3.DBMS_SQL表类型
1TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;
2TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;
3TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;
4TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
5TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
6TYPE date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
7TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;
8TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;
9TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER;
4.DBMS_SQL异常
1inconsistent_type EXCEPTION;
2PRAGMA exception_init(inconsistant_type,-6562);
5.子程序参数
（1）. c:该参数用于指定游标ID。
（2）.name：该参数用于指定变量名。
（3）.position：用于指定游标中的相对位置。
（4）.table_variable用于指定特定类型的PL/SQL表变量。
6.BIND_ARRAY
该过程用于绑定游标结果到特定PL/SQL表变量，语法如下：
1DBMS_SQL.bind_array(c IN INTEGER,name IN VARCHAR2,<table_variable>IN <datetype>[,index1 IN INTEGER,index2 IN INTEGER]);
如上所示，index1用于指定PL/SQL表元素的下界，index2用于指定PL/SQL表元素的上界。
7.CLOSE_CURSOR
该过程用于关闭特定游标，语法如下：
1DBMS_SQL.close_cursor(c IN OUT INTEGER);
8.EXECUTE
该函数用于执行游标查询语句，并返回总行数，语法如下：
1DBMS_SQL.execute(c IN INTEGER) RETURN INTEGER;
9.OPEN_CURSOR
该函数用于打开游标，并返回游标ID，语法如下：
1DBMS_SQL.OPEN_CURSOR RETURN INTEGER;
2PRAGMA RESTRICT_REFERENCES(open_cursor,RNDS,WNDS);
10.PARSE
该函数用于解析游标，注意，DDL语句在解析时被执行，语法如下：
1DBMS_SQL.parse(c IN INTEGER,statement IN VARCHAR2,language_flag IN INTEGER);
2
3DBMS_SQL.parse(c IN INTEGER,statement IN CLOB,language_flag IN INTEGER);
二 示例
1.建立过程dyn_ddl
当使用动态SQL执行DDL或者无绑定变量的DDL语句时，需要包括打开游标，解析语句，执行语句，关闭游标等步骤。下面以建立并执行过程dyn_ddl为例，说明使用动态SQL执行DDL或者无绑定变量的DDL语句：
1CREATE OR REPLACE PROCEDURE dyn_ddl(string VARCHAR2)
2 IS
3 C INTEGER;
4 BEGIN
5 C:=DBMS_SQL.open_cursor;
6 DBMS_SQL.parse(C,string ,DBMS_SQL.native);
7 RET:=DBMS_SQL.EXCUTE(C);
8 DBMS_SQL.close_cursor(C);
9 END;
10
11EXEC SCOTT.dyn_ddl('CREATE TABLE my(mason int)');
12EXEC SCOTT.dyn_ddl('INSERT INTO scott.my values(1)');
13
2.建立过程bulk_inssert_emp
当只用DBMS_SQL包实现批量插入时，需要包括打开游标，解析语句，绑定数组，执行语句，关闭游标等步骤。下面以建立并调用过程bulk_insert_emp为例，说明使用DBMS_SQL实现批量插入的方法：
1CREATE OR REPLACE PROCEDURE bulk_insert_emp(
2 empno_array DBMS_SQL.Number_Table,
3 ename_array DBMS_SQL.Varchar2_Table,
4 sal_array DBMS_SQL.Number_Table
5 )
6 IS
7 c INTEGER;
8 ret INTEGER;
9 stmt VARCHAR2(100);
10 BEGIN
11 stmt:='INSERT INTO emp(empno,ename,sal) '||'VALUES(:num_array,:name_array,:salary_array)';
12 c:=DBMS_SQL.OPEN_CURSOR;
13 DBMS_SQL.parse(c,stmt,DBMS_SQL.native);
14 DBMS_SQL.bind_array(c,':num_array',empno_array);
15 DBMS_SQL.bind_array(c,':name_array',ename_array);
16 DBMS_SQL.bind_array(c,':salary_array',sal_array);
17 ret:=DBMS_SQL.execute(c);
18 DBMS_SQL.close_cursor(c);
19 EXCEPTION
20 WHEN OTHERS THEN
21 DBMS_SQL.close_cursor(c);
22 END;
23 BEGIN
24 FOR i IN 0..9 LOOP
25 empno_array(i):=1000+i;
26 ename_array(i):='name'||i;
27 sal_array(i):=1000+10*i;
28 END LOOP;
29 bulk_insert_emp(empno_array,ename_array,sal_array);
30END;
3.建立过程bulk_update_emp
当使用DBMS_SQL包实现批量更新时，需要包括打开游标，解析语句，绑定数组，执行语句，关闭游标等步骤，下面以建立并调用过程bulk_update_emp为例，说明使用DBMS_SQL实现批量更新的方法：
1CREATE OR REPLACE PROCEDURE bulk_update_emp(
2 empno_array DBMS_SQL.Number_Table,
3 sal_array DBMS_SQL.Number_Table
4 )
5 IS
6 c INTEGER;
7 ret INTEGER;
8 stmt VARCHAR2(100);
9 BEGIN
10 stmt:='UPDATE emp SET sal=:salary_arry '||'WHERE empno=:num_array';
11 c:=DBMS_SQL.OPEN_CURSOR;
12 DBMS_SQL.parse(c,stmt,DBMS_SQL.native);
13 DBMS_SQL.bind_array(c,':num_array',empno_array);
14 DBMS_SQL.bind_array(c,':salary_array',sal_array);
15 ret:=DBMS_SQL.execute(c);
16 DBMS_SQL.close_cursor(c);
17 EXCEPTION
18 WHEN OTHERS THEN
19 DBMS_SQL.close_cursor(c);
20 END;
21 DECLARE
22 empno_array DBMS_SQL.Number_Table;
23 sal_array DBMS_SQL.Number_Table;
24 BEGIN
25 FOR i IN 0..9 LOOP
26 empno_array(i):=1000+i;
27 sal_array(i):=1000+10*i;
28 END LOOP;
29 bulk_insert_emp(empno_array,sal_array);
30END;
4.建立过程bulk_delete_emp
当使用DBMS_SQL包实现批量删除时，需要包括打开游标，解析语句，绑定数组，执行语句，关闭游标等，下面以建立并调用过程bulk_delete_emp为例，说明使用DBMS_SQL实现批量删除的方法：
1CREATE OR REPLACE PROCEDURE bulk_update_emp(
2 empno_array DBMS_SQL.Number_Table
3 )
4 IS
5 c INTEGER;
6 ret INTEGER;
7 stmt VARCHAR2(100);
8 BEGIN
9 stmt:='DELETE emp WHERE empno=:num_array';
10 c:=DBMS_SQL.OPEN_CURSOR;
11 DBMS_SQL.parse(c,stmt,DBMS_SQL.native);
12 DBMS_SQL.bind_array(c,':num_array',empno_array);
13 ret:=DBMS_SQL.execute(c);
14 DBMS_SQL.close_cursor(c);
15 EXCEPTION
16 WHEN OTHERS THEN
17 DBMS_SQL.close_cursor(c);
18 END;
19 DECLARE
20 empno_array DBMS_SQL.Number_Table;
21 BEGIN
22 FOR i IN 0..9 LOOP
23 empno_array(i):=1000+i;
24 END LOOP;
25 bulk_insert_emp(empno_array);
26END;
5.建立过程bulk_query_emp
当使用DBMS_SQL包实现批量提取时，需要包括打开游标，解析语句，定义数组，执行语句，提取数据，取得列值，关闭游标等步骤。下面以建立并调用过程bilk_query_emp为例说明使用DBMS_SQL实现批量提取的方法：
1CREATE OR REPLACE PROCEDURE bulk_query_emp(
2 dno NUMBER;
3 empno_array OUT DBMS_SQL.Number_Table,
4 ename_array OUT DBMS_SQL.Varchar2_Table,
5 sal_array OUT DBMS_SQL.Number_Table
6 )
7 IS
8 c INTEGER;
9 ret INTEGER;
10 stmt VARCHAR2(100);
11 BEGIN
12 stmt:='SELECT empno,ename,sal FROM emp WHERE deptno=:no';
13 c:=DBMS_SQL.OPEN_CURSOR;
14 DBMS_SQL.parse(c,stmt,DBMS_SQL.native);
15 DBMS_SQL.bind_variable(c,':no',dno);
16 DBMS_SQL.bind_array(c,1,empno_array,100,1);
17 DBMS_SQL.bind_array(c,2,ename_array,100,1);
18 DBMS_SQL.bind_array(c,3,sal_array,100,1);
19 ret:=DBMS_SQL.execute(c);
20 ret:=DBMS_SQL.FETCH_ROWS(c);
21 DBMS_SQL.column_value(c,1,empno_array);
22 DBMS_SQL.column_value(c,2,empno_array);
23 DBMS_SQL.column_value(c,3,empno_array);
24 DBMS_SQL.close_cursor(c);
25END;
26DECLARE
27 empno_array DBSM_SQL.NUMBER_TABLE;
28 ename_array DBMS_SQL.VARCHAR2_TABLE;
29 sal_array DBMS_SQL.Number_Table;
30 BEGIN
31 bulk_query_emp(&dno,empno_array,ename_array,sal_array);
32 FOR i IN 1..empno_array.COUNT LOOP
33 DBMS_OUTPUT.put_line(empno_array(i)||' '||ename_array(i)||' '||sal_array(i));
34 END LOOP;
35END;
扫描下方二维码关注关注号，了解相关更新
