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 过程已成功完成。
