Oracle11新特性——PLSQL新特性（七）

打算寫一系列的文章介紹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 過程已成功完成。