打算写一系列的文章介绍11g的新特性和变化。

11g中PL/SQL新增了很多特性，在性能和易用性方面做了不少的提升，还有一些功能性的增强。

这篇介绍一下PLSQL的动态SQL方面的增强。

Oracle11新特性——PLSQL新特性（一）：http://yangtingkun.itpub.net/post/468/395965

Oracle11新特性——PLSQL新特性（二）：http://yangtingkun.itpub.net/post/468/396571

Oracle11新特性——PLSQL新特性（三）：http://yangtingkun.itpub.net/post/468/396994

Oracle11新特性——PLSQL新特性（四）：http://yangtingkun.itpub.net/post/468/397350

Oracle11新特性——PLSQL新特性（五）：http://yangtingkun.itpub.net/post/468/398314

Oracle11新特性——PLSQL新特性（六）：http://yangtingkun.itpub.net/post/468/399632

Oracle11g中对于动态sql也做了很多功能性的增强。

其中最明显的一个增强就是EXECUTE IMMEDIATE和DBMS_SQL包的PARSE都支持CLOB字段作为输入。这就彻底解决了EXECUTE IMMEDIATE语法不支持32k以上sql语句的限制。同时DBMS_SQL包对于32k以上SQL的处理也得到了简化。

首先建立一张大表，建表语句的长度超过32k。建表语句没有必要列出来，这里就省略了：

SQL> SELECT DBMS_LOB.GETLENGTH(DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL')) FROM DUAL;

DBMS_LOB.GETLENGTH(DBMS_METADATA.GET_DDL('TABLE','T_LONG_SQL'))
---------------------------------------------------------------
35974

如果希望通过动态SQL的方法重建，原来只能使用DBMS_SQL的VARCHAR2S接口，但是这种方面比较麻烦，现在可以直接通过CLOB的方式来实现。

SQL> DECLARE
2 V_CLOB CLOB;
3 V_CURSOR NUMBER DEFAULT DBMS_SQL.OPEN_CURSOR;
4 V_VARCHAR2S DBMS_SQL.VARCHAR2S;
5 I NUMBER DEFAULT 1;
6 BEGIN
7 SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL') INTO V_CLOB FROM DUAL;
8 LOOP
9 V_VARCHAR2S(I) := DBMS_LOB.SUBSTR(V_CLOB, 200, (I - 1) * 200 + 1);
10 EXIT WHEN LENGTH(V_VARCHAR2S(I)) < 200;
11 I := I + 1;
12 END LOOP;
13 EXECUTE IMMEDIATE 'DROP TABLE T_LONG_SQL PURGE';
14 DBMS_SQL.PARSE(V_CURSOR, V_VARCHAR2S, 1, V_VARCHAR2S.COUNT, FALSE, DBMS_SQL.NATIVE);
15 END;
16 /

PL/SQL 过程已成功完成。

这是11g以前处理32k以上SQL的方法，下面看看11g中提供的CLOB接口的方法：

SQL> DECLARE
2 V_CLOB CLOB;
3 V_CURSOR NUMBER DEFAULT DBMS_SQL.OPEN_CURSOR;
4 BEGIN
5 SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL') INTO V_CLOB FROM DUAL;
6 EXECUTE IMMEDIATE 'DROP TABLE T_LONG_SQL PURGE';
7 DBMS_SQL.PARSE(V_CURSOR, V_CLOB, DBMS_SQL.NATIVE);
8 END;
9 /

PL/SQL 过程已成功完成。

这个方法就简单很多了，而使用EXECUTE IMMEDIATE则更加简单：

SQL> DECLARE
2 V_CLOB CLOB;
3 BEGIN
4 SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_LONG_SQL') INTO V_CLOB FROM DUAL;
5 EXECUTE IMMEDIATE 'DROP TABLE T_LONG_SQL PURGE';
6 EXECUTE IMMEDIATE V_CLOB;
7 END;
8 /

PL/SQL 过程已成功完成。

除了增加对CLOB类型的支持外，DBMS_SQL包还增加了将REF CURSOR和DBMS_SQL包的CURSOR进行相互转化的功能。

在打开CURSOR且提前数据前，可以利用DBMS_SQL包将CURSOR类型进行转化。

最后看看DBMS_SQL的CUROSR和REF CURSOR转化的一个例子：

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB;

已创建3行。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 TYPE T_CURSOR IS REF CURSOR;
3 TYPE T_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4 TYPE T_VARCHAR2 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
5 V_REF_CURSOR T_CURSOR;
6 V_ID T_NUMBER;
7 V_NAME T_VARCHAR2;
8 V_DBMS_SQL_CURSOR NUMBER := DBMS_SQL.OPEN_CURSOR;
9 V_SQL VARCHAR2(32767) := 'SELECT * FROM T';
10 V_RES NUMBER;
11 BEGIN
12 DBMS_SQL.PARSE(V_DBMS_SQL_CURSOR, V_SQL, DBMS_SQL.NATIVE);
13 V_RES := DBMS_SQL.EXECUTE(V_DBMS_SQL_CURSOR);
14 V_REF_CURSOR := DBMS_SQL.TO_REFCURSOR(V_DBMS_SQL_CURSOR);
15 FETCH V_REF_CURSOR BULK COLLECT INTO V_ID, V_NAME;
16 DBMS_OUTPUT.PUT_LINE(LPAD('ID', 10, ' ') || ' ' || 'NAME');
17 FOR I IN 1..V_ID.COUNT LOOP
18 DBMS_OUTPUT.PUT_LINE(LPAD(V_ID(I), 10, ' ') || ' ' || V_NAME(I));
19 END LOOP;
20 CLOSE V_REF_CURSOR;
21 END;
22 /
ID NAME
1 T
2 T_SESSION
3 T_SESSION_STAT

PL/SQL 过程已成功完成。

SQL> DECLARE
2 TYPE T_CURSOR IS REF CURSOR;
3 V_REF_CURSOR T_CURSOR;
4 V_DBMS_SQL_CURSOR NUMBER;
5 V_COLUMN_COUNT NUMBER;
6 V_DEST_TABLE DBMS_SQL.DESC_TAB;
7 V_ID DBMS_SQL.NUMBER_TABLE;
8 V_NAME DBMS_SQL.VARCHAR2_TABLE;
9 V_RES NUMBER;
10 BEGIN
11 OPEN V_REF_CURSOR FOR 'SELECT * FROM T';
12 V_DBMS_SQL_CURSOR := DBMS_SQL.TO_CURSOR_NUMBER(V_REF_CURSOR);
13 DBMS_SQL.DEFINE_ARRAY(V_DBMS_SQL_CURSOR, 1, V_ID, 10000, 1);
14 DBMS_SQL.DEFINE_ARRAY(V_DBMS_SQL_CURSOR, 2, V_NAME, 10000, 1);
15 V_RES := DBMS_SQL.FETCH_ROWS(V_DBMS_SQL_CURSOR);
16 DBMS_SQL.COLUMN_VALUE(V_DBMS_SQL_CURSOR, 1, V_ID);
17 DBMS_SQL.COLUMN_VALUE(V_DBMS_SQL_CURSOR, 2, V_NAME);
18 DBMS_OUTPUT.PUT_LINE(LPAD('ID', 10, ' ') || ' ' || 'NAME');
19 FOR I IN 1..V_RES LOOP
20 DBMS_OUTPUT.PUT_LINE(LPAD(V_ID(I), 10, ' ') || ' ' || V_NAME(I));
21 END LOOP;
22 DBMS_SQL.CLOSE_CURSOR(V_DBMS_SQL_CURSOR);
23 END;
24 /
ID NAME
1 T
2 T_SESSION
3 T_SESSION_STAT

PL/SQL 过程已成功完成。