熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle中table函數的應用

2013-11-13 22:08:40  來源: Oracle 

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

   用游標傳遞數據

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

  SELECT *

  FROM TABLE (myfunction (CURSOR (SELECT *

  FROM mytab)));

   利用兩個實體化視圖(或表)作為樣板數據

  CREATE MATERIALIZED VIEW sum_sales_country_mv

  BUILD IMMEDIATE

  REFRESH COMPLETE

  ENABLE QUERY REWRITE

  AS

  SELECT   SUBSTR (scalendar_month_desc ) YEAR untry_id country

  SUM (sum_amount_sold) sum_amount_sold

  FROM sum_sales_month_mv s customers c

  WHERE scust_id = ccust_id

  AND untry_id IN (US UK FR ES JP AU)

  GROUP BY SUBSTR (scalendar_month_desc ) untry_id;

  CREATE MATERIALIZED VIEW sum_es_gend_mv

  BUILD DEFERRED

  REFRESH FAST

  ENABLE QUERY REWRITE

  AS

  SELECT   SUBSTR (scalendar_month_desc ) YEAR

  scalendar_month_desc cal_month ccust_gender

  SUM (sum_amount_sold) sum_amount_sold

  FROM sum_sales_month_mv s customer c

  WHERE scust_id = ccust_id

  AND untry_id = ES

  AND sunstr (scalendar_month_desc ) =

  GROUP BY SUBSTR (scalendar_month_desc )

  scalendar_month_desc

  ccust_gender;

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

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

  ()定義對象類型TYPE sales_country_t

  CREATE TYPE sales_country_t AS OBJECT (

  YEAR              VARCHAR ()

  country           CHAR ()

  sum_amount_sold   NUMBER

  );

  ()定義表類型TYPE SUM_SALES_COUNTRY_T_TAB

  CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

  ()定義對象類型TYPE sales_gender_t

  CREATE TYPE sales_gender_t AS OBJECT (

  YEAR              VARCHAR ()

  country_id        CHAR ()

  cust_gender       CHAR ()

  sum_amount_sold   NUMBER

  );

  ()定義表類型TYPE SUM_SALES_GENDER_T_TAB

  CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;

  ()定義對象類型TYPE sales_roll_t

  CREATE TYPE sales_roll_t AS OBJECT (

  channel_desc      VARCHAR ()

  country_id        CHAR ()

  sum_amount_sold   NUMBER

  );

  ()定義表類型TYPE SUM_SALES_ROLL_T_TAB

  CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;

  ()檢查一下建立的類型

  SELECT object_name object_type status

  FROM user_objects

  WHERE object_type = TYPE;

   定義包Create package and define REF CURSOR

  CREATE OR REPLACE PACKAGE cursor_pkg

  IS

  TYPE sales_country_t_rec IS RECORD (

  YEAR              VARCHAR ()

  country           CHAR ()

  sum_amount_sold   NUMBER

  );

  TYPE sales_gender_t_rec IS RECORD (

  YEAR              VARCHAR ()

  country_id        CHAR ()

  cust_gender       CHAR ()

  sum_amount_sold   NUMBER

  );

  TYPE sales_roll_t_rec IS RECORD (

  channel_desc      VARCHAR ()

  country_id        CHAR ()

  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;

   定義表函數

  ()定義表函數FUNCTION Table_Ref_Cur_Week

  CREATE OR REPLACE FUNCTION table_ref_cur_week (cur CURSORrefcur_t)

  RETURN sum_sales_country_t_tab

  IS

  YEAR              VARCHAR ();

  country           CHAR ();

  sum_amount_sold   NUMBER;

  objset            sum_sales_country_t_tab := sum_sales_country_t_tab ();

  i                 NUMBER                  := ;

  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 + ;

  objsetEXTEND;

  objset (i) := sales_country_t (YEAR country sum_amount_sold);

  END LOOP;

  CLOSE cur;

  RETURN objset;

  END;

  /

  ()定義表函數FUNCTION Table_Ref_Cur_Strong

  CREATE OR REPLACE FUNCTION table_ref_cur_strong (cur cursor_pkgstrong_refcur_t)

  RETURN sum_sales_country_t_tab PIPELINED

  IS

  YEAR              VARCHAR ();

  country           CHAR ();

  sum_amount_sold   NUMBER;

  i                 NUMBER      := ;

  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;

  /

  ()定義表函數FUNCTION Table_Ref_Cur_row

  CREATE OR REPLACE FUNCTION table_ref_cur_row (cur cursor_pkgrow_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_recYEAR := in_recYEAR;

  untry := untry;

  out_recsum_amount_sold := in_recsum_amount_sold;

  PIPE ROW (out_rec);

  END LOOP;

  CLOSE cur;

  RETURN;

  END;

  /

  ()定義表函數FUNCTION Gender_Table_Ref_Cur_Week

  CREATE OR REPLACE FUNCTION gender_table_ref_cur_week (cur cursor_pkgrefcur_t)

  RETURN sum_sales_gender_t_tab

  IS

  YEAR              VARCHAR ();

  country_id        CHAR ();

  cust_gender       CHAR ();

  sum_amount_sold   NUMBER;

  objset            sum_sales_gender_t_tab := sum_sales_gender_t_tab ();

  i                 NUMBER                 := ;

  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 + ;

  objsetEXTEND;

  objset (i) :=

  sum_sales_gender_t (YEAR country_id cust_gender sum_amount_sold);

  END LOOP;

  CLOSE cur;

  RETURN objset;

  END;

  /

   調用表函數

  下列 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)));


From:http://tw.wingwit.com/Article/program/Oracle/201311/18328.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.