管道函式及表函式的測試PIPELINED__PIPE ROW

 --先定義包及包體，定義SYS_REFCURSOR的遊標型別及記錄型別
                                   CREATE OR REPLACE PACKAGE PKG_CURSOR IS
                                    --遊標，多行，單行，非全列單行
                                    TYPE TYP_REFCURSOR IS REF CURSOR RETURN DEPT%ROWTYPE;
                                    TYPE TYP_REC IS RECORD(V_DEPTNO DEPT.DEPTNO%TYPE,V_DNAME DEPT.DNAME%TYPE);
                                    TYPE TYP_TAB_REC IS TABLE OF TYP_REC;
                                    FUNCTION FUNC_IN_CURSOR(TYP_REFCURSOR_1 TYP_REFCURSOR)
                                      RETURN TYP_TAB_REC PIPELINED;

                                   END PKG_CURSOR;

-------------------------------------------------------------------------------------------------------------

 

 --定義包體
                                   --定義包體浪費了好多時間
                                   --小結：如果函式的步驟太多，每步標上作什麼註解，這樣出錯便於定位除錯，不然一行行看會暈的
                                   --     
                                   CREATE OR REPLACE PACKAGE body PKG_CURSOR IS
                                    --1，函式的遊標輸入引數
                                    --2，
                                    --定義函式
                                    FUNCTION FUNC_IN_CURSOR(TYP_REFCURSOR_1 IN TYP_REFCURSOR) --輸入引數是包中定義的遊標型別
                                     RETURN TYP_TAB_REC PIPELINED --返回型別必須是集合型別，此集合型別也在包中定義
                                     IS
                                     TYP_REC_1       TYP_REC;--記錄型別在包中定義
                                     TYP_REFCURSOR_2 TYP_REFCURSOR_1%rowtype;--此步是關鍵，此變數的型別是基於輸入引數和%ROWTYPE，這樣就把輸入引數與單行記錄聯絡起來
                                     BEGIN
                                      LOOP
                                       --獲取輸入變數遊標變數到標註是：此步是關鍵 的變數中
                                       FETCH TYP_REFCURSOR_1 INTO  TYP_REFCURSOR_2;--另一個單記錄變數
                                      
                                       --遊標退出的條件，注：是遊標變數，即輸入引數
                                       EXIT WHEN TYP_REFCURSOR_1%NOTFOUND;--
                                      
                                       --用此步是關鍵把值傳遞給記錄型別的變數中，這樣就把輸入引數遊標與此步是關鍵所對應的變數及記錄變型聯絡起來了
                                       TYP_REC_1.V_DEPTNO:=TYP_REFCURSOR_2.DEPTNO;
                                       TYP_REC_1.V_DNAME:=TYP_REFCURSOR_2.DNAME;
                                      
                                       --管道函式一定要用此句PIPE ROW，後跟要傳遞的記錄變數，即最終顯示的變數
                                       PIPE ROW(TYP_REC_1);
                                      END LOOP;
                                     
                                      --記：如下的關閉遊標與RETURN在LOOP END LOOP之外發生
                                      --用完遊標一定要關閉，此和常規的遊標操作一樣，不然浪費資源
                                      CLOSE TYP_REFCURSOR_1;
                                     
                                      --這是管道函式特有的，用於返回控制權給呼叫方
                                      RETURN;
                                     END;
                                   END PKG_CURSOR;

                                   --包及包體的遊標輸入引數的函式製作完，下面是測試結果
                                   --有幾點注意：
                                   --1,TABLE關鍵字，用於標記是表函式
                                   --2,TABLE後跟包名.包中的過程或函式，在此為函式
                                   --3,上述函式後是CURSOR，表明輸入引數是遊標
                                   --4，CURSOR裡面就是要傳遞給遊標的結果集,通常是一個SELECT查詢，也可以是其它的結果集或其它的表函式
                                 SQL> SELECT * FROM TABLE(PKG_CURSOR.FUNC_IN_CURSOR(CURSOR(SELECT * FROM DEPT)));
                                
                                
                                   V_DEPTNO V_DNAME
                                 ---------- --------------
                                         10 ACCOUNTING
                                         20 RESEARCH
                                         30 SALES
                                         40 OPERATIONS