V久久国产精品免费一区二区三区,亚洲AV无码成H人在线观看

主頁(yè) > 知識(shí)庫(kù) > Oracle 中 table 函數(shù)的應(yīng)用淺析

Oracle 中 table 函數(shù)的應(yīng)用淺析

表函數(shù)可接受查詢語(yǔ)句或游標(biāo)作為輸入?yún)?shù)，并可輸出多行數(shù)據(jù)。該函數(shù)可以平行執(zhí)行，并可持續(xù)輸出數(shù)據(jù)流，被稱作管道式輸出。應(yīng)用表函數(shù)可將數(shù)據(jù)轉(zhuǎn)換分階段處理，并省去中間結(jié)果的存儲(chǔ)和緩沖表。

1. 用游標(biāo)傳遞數(shù)據(jù)

利用游標(biāo) REF CURSOR 可將數(shù)據(jù)集（多行記錄）傳遞到PL/SQL函數(shù)：

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

2. 利用兩個(gè)實(shí)體化視圖（或表）作為樣板數(shù)據(jù)

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. 定義對(duì)象類型和基于對(duì)象類型的表類型

定義對(duì)象類型并且為進(jìn)一步引用做好準(zhǔn)備。

（1）定義對(duì)象類型：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）定義對(duì)象類型：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）定義對(duì)象類型：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. 定義表函數(shù)

（1）定義表函數(shù)：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）定義表函數(shù)：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）定義表函數(shù)：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）定義表函數(shù)：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. 調(diào)用表函數(shù)

下列 SQL 查詢語(yǔ)句調(diào)用已被定義的表函數(shù)。

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 函數(shù)的應(yīng)用淺析，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章:

Oracle Table Demo語(yǔ)句應(yīng)用介紹
oracle中變長(zhǎng)數(shù)組varray,嵌套表,集合使用方法
sql – Oracle中匿名TABLE/VARRAY類型示例詳解

標(biāo)簽：海東昭通百色寧波紹興中衛(wèi) 自貢遼源

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle 中 table 函數(shù)的應(yīng)用淺析》，本文關(guān)鍵詞 Oracle,中,table,函數(shù),的,應(yīng)用,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。