2016年9月9日 星期五

[Oracle][PL/SQL]動態執行Cursor-Dynamic Cursor

說明:在寫PL/SQL時,遇到需要在Function或Stored Procedure中,動態宣告Cursor以及取得Cursor欄位及值。
文中我們使用Function來做範例:
image
1.定義一個Function,F_Dynamic_Cursor_Demo
CREATE OR REPLACE PROCEDURE F_DYNAMIC_CURSOR_DEMO AS 

    --宣告Type

    TYPE TYPE_CUR IS RECORD ( 

      V_SQL VARCHAR(1000),

      V_CUR NUMBER,

      V_DTBL DBMS_SQL.DESC_TAB,

      V_CNT NUMBER,

      V_STATUS NUMBER,

      V_VAL VARCHAR2(200)); 

    

    REC_CUR TYPE_CUR;

BEGIN

    --查詢SQL語法

    REC_CUR.V_SQL:='SELECT ''GOOGLE'' AS NAME,18 AS AGE FROM DUAL';

    --動態設定CURSOR';

    REC_CUR.V_CUR:= DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(REC_CUR.V_CUR,REC_CUR.V_SQL,DBMS_SQL.NATIVE);

    DBMS_SQL.DESCRIBE_COLUMNS(REC_CUR.V_CUR,REC_CUR.V_CNT,REC_CUR.V_DTBL);

    --動態定義CURSOR欄位;

    FOR I IN 1..REC_CUR.V_CNT LOOP

      --定義欄位及值還有Size

      DBMS_SQL.DEFINE_COLUMN(REC_CUR.V_CUR,I,REC_CUR.V_VAL,200);

    END LOOP;

    --動態執行CURSOR;

    REC_CUR.V_STATUS := DBMS_SQL.EXECUTE(REC_CUR.V_CUR);

    --動態取得CURSOR資料,巡覽每一筆資料

    WHILE ( DBMS_SQL.FETCH_ROWS(REC_CUR.V_CUR) > 0 ) LOOP

        --動態取得CURSOR欄位名稱及值

        FOR I IN 1..REC_CUR.V_CNT LOOP             

                  DBMS_SQL.COLUMN_VALUE(REC_CUR.V_CUR,I,REC_CUR.V_VAL);

                  DBMS_OUTPUT.PUT_LINE(REC_CUR.V_DTBL(I).COL_NAME||' --> '||REC_CUR.V_VAL);

        END LOOP;    

     END LOOP;

     DBMS_SQL.CLOSE_CURSOR(REC_CUR.V_CUR);       

END F_DYNAMIC_CURSOR_DEMO;

2.執行F_Dynamic_Cursor_Demo
begin

  F_DYNAMIC_CURSOR_DEMO;

end;

3.結果
NAME --> GOOGLE
AGE –> 18