Oracle 中 table 函數的應用淺析

表函數可接受查詢語句或游標作為輸入參數，並可輸出多行數據。該函數可以平行執行，並可持續輸出數據流，被稱作管道式輸出。應用表函數可將數據轉換分階段處理，並省去中間結果的存儲和緩沖表。

1. 用游標傳遞數據

利用游標 REF CURSOR 可將數據集（多行記錄）傳遞到PL/SQL函數：

SELECT *
 FROM TABLE (myfunction (CURSOR (SELECT *
         FROM mytab)));  

2. 利用兩個實體化視圖（或表）作為樣板數據

CREATE MATERIALIZED VIEW sum_sales_country_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR, c.country_id country,
   SUM (sum_amount_sold) sum_amount_sold
 FROM sum_sales_month_mv s, customers c
 WHERE s.cust_id = c.cust_id
  AND c.country_id IN ('US', 'UK', 'FR', 'ES', 'JP', 'AU')
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4), c.country_id

CREATE MATERIALIZED VIEW sum_es_gend_mv
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR,
   s.calendar_month_desc cal_month, c.cust_gender,
   SUM (sum_amount_sold) sum_amount_sold
 FROM sum_sales_month_mv s, customer c
 WHERE s.cust_id = c.cust_id
  AND c.country_id = 'ES'
  AND sunstr (s.calendar_month_desc, 1, 4) = '2000'
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4),
   s.calendar_month_desc,
   c.cust_gender;

3. 定義對象類型和基於對象類型的表類型

定義對象類型並且為進一步引用做好准備。

（1）定義對象類型：TYPE sales_country_t

CREATE MATERIALIZED VIEW sum_es_gend_mv
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT SUBSTR (s.calendar_month_desc, 1, 4) YEAR,
   s.calendar_month_desc cal_month, c.cust_gender,
   SUM (sum_amount_sold) sum_amount_sold
 FROM sum_sales_month_mv s, customer c
 WHERE s.cust_id = c.cust_id
  AND c.country_id = 'ES'
  AND sunstr (s.calendar_month_desc, 1, 4) = '2000'
GROUP BY SUBSTR (s.calendar_month_desc, 1, 4),
   s.calendar_month_desc,
   c.cust_gender;

（2）定義表類型：TYPE SUM_SALES_COUNTRY_T_TAB

CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

（3）定義對象類型：TYPE sales_gender_t

CREATE TYPE sales_gender_t AS OBJECT (
 YEAR    VARCHAR2 (4),
 country_id  CHAR (2),
 cust_gender  CHAR (1),
 sum_amount_sold NUMBER
);

（4）定義表類型：TYPE SUM_SALES_GENDER_T_TAB

CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;

（5）定義對象類型：TYPE sales_roll_t

CREATE TYPE sales_roll_t AS OBJECT (
 channel_desc  VARCHAR2 (20),
 country_id  CHAR (2),
 sum_amount_sold NUMBER
);

（6）定義表類型：TYPE SUM_SALES_ROLL_T_TAB

CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;

（7）檢查一下建立的類型

SELECT object_name, object_type, status
 FROM user_objects
 WHERE object_type = 'TYPE';

4. 定義包：Create package and define REF CURSOR

CREATE OR REPLACE PACKAGE cursor_pkg
I TYPE sales_country_t_rec IS RECORD (
  YEAR    VARCHAR (4),
  country   CHAR (2),
  sum_amount_sold NUMBER
 );
 TYPE sales_gender_t_rec IS RECORD (
  YEAR    VARCHAR2 (4),
  country_id  CHAR (2),
  cust_gender  CHAR (1),
  sum_amount_sold NUMBER
 );
 TYPE sales_roll_t_rec IS RECORD (
  channel_desc  VARCHAR2 (20),
  country_id  CHAR (2),
  sum_amount_sold NUMBER
 );
 TYPE sales_country_t_rectab IS TABLE OF sales_country_t_rec;
 TYPE sales_roll_t_rectab IS TABLE OF sales_roll_t_rec;
 TYPE strong_refcur_t IS REF CURSOR
  RETURN sales_country_t_rec;
 TYPE row_refcur_t IS REF CURSOR
  RETURN sum_sales_country_mv%ROWTYPE;
 TYPE roll_refcur_t IS REF CURSOR
  RETURN sales_roll_t_rec;
 TYPE refcur_t IS REF CURSOR;
END corsor_pkg;

5. 定義表函數

（1）定義表函數：FUNCTION Table_Ref_Cur_Week

CREATE OR REPLACE FUNCTION table_ref_cur_week (cur CURSOR.refcur_t)
 RETURN sum_sales_country_t_tab
IS
 YEAR    VARCHAR (4);
 country   CHAR (2);
 sum_amount_sold NUMBER;
 objset   sum_sales_country_t_tab := sum_sales_country_t_tab ();
 i     NUMBER     := 0;
BEGIN
 LOOP
-- Fetch from cursor variable
  FETCH cur
  INTO YEAR, country, sum_amount_sold;
  EXIT WHEN cur%NOTFOUND;
      -- exit when last row is fetched
-- append to collection
  i := i + 1;
  objset.EXTEND;
  objset (i) := sales_country_t (YEAR, country, sum_amount_sold);
 END LOOP;
 CLOSE cur;
 RETURN objset;
END;
/

（2）定義表函數：FUNCTION Table_Ref_Cur_Strong

CREATE OR REPLACE FUNCTION table_ref_cur_strong (cur cursor_pkg.strong_refcur_t)
 RETURN sum_sales_country_t_tab PIPELINED
IS
 YEAR    VARCHAR (4);
 country   CHAR (2);
 sum_amount_sold NUMBER;
 i     NUMBER  := 0;
BEGIN
 LOOP
  FETCH cur
  INTO YEAR, country, sum_amount_sold;
  EXIT WHEN cur%NOTFOUND;     -- exit when last row fetched
  PIPE ROW (sales_country_t (YEAR, country, sum_amount_sold));
 END LOOP;
 CLOSE cur;
 RETURN;
END;
/

（3）定義表函數：FUNCTION Table_Ref_Cur_row

CREATE OR REPLACE FUNCTION table_ref_cur_row (cur cursor_pkg.row_refcur_t)
 RETURN sum_sales_country_t_tab PIPELINED
IS
 in_rec cur%ROWTYPE;
 out_rec sales_country_t := sales_country_t (NULL, NULL, NULL);
BEGIN
 LOOP
  FETCH cur
  INTO in_rec;
  EXIT WHEN cur%NOTFOUND;    -- exit when last row is fetched
  out_rec.YEAR := in_rec.YEAR;
  out_rec.country := in_rec.country;
  out_rec.sum_amount_sold := in_rec.sum_amount_sold;
  PIPE ROW (out_rec);
 END LOOP;
 CLOSE cur;
 RETURN;
END;
/

（4）定義表函數：FUNCTION Gender_Table_Ref_Cur_Week

CREATE OR REPLACE FUNCTION gender_table_ref_cur_week (cur cursor_pkg.refcur_t)
 RETURN sum_sales_gender_t_tab
IS
 YEAR    VARCHAR2 (4);
 country_id  CHAR (2);
 cust_gender  CHAR (1);
 sum_amount_sold NUMBER;
 objset   sum_sales_gender_t_tab := sum_sales_gender_t_tab ();
 i     NUMBER     := 0;
BEGIN
 LOOP
  FETCH cur
  INTO YEAR, country_id, cust_gender, sum_amount_sold;
  EXIT WHEN cur%NOTFOUND;    -- exit when last row is fetched
  i := i + 1;
  objset.EXTEND;
  objset (i) :=
   sum_sales_gender_t (YEAR, country_id, cust_gender, sum_amount_sold);
 END LOOP;
 CLOSE cur;
 RETURN objset;
END;
/

6. 調用表函數

下列 SQL 查詢語句調用已被定義的表函數。

SELECT *
 FROM TABLE (table_ref_cur_week (CURSOR (SELECT *
           FROM sum_sales_country_mv)));
SELECT *
 FROM TABLE (table_ref_cur_strong (CURSOR (SELECT *
            FROM sum_sales_country_mv)));
SELECT *
 FROM TABLE (table_ref_cur_row (CURSOR (SELECT *
           FROM sum_sales_country_mv)));
SELECT *
 FROM TABLE (table_ref_cur_week (CURSOR (SELECT *
           FROM sum_sales_country_mv
           WHERE country = 'AU')));

以上所述是小編給大家介紹的Oracle 中 table 函數的應用淺析，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對網站的支持！