Oracle 中 table 函数的应用浅析

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

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;

CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

CREATE TYPE sales_gender_t AS OBJECT (

  YEAR     VARCHAR2 (4),

  country_id  CHAR (2),

  cust_gender  CHAR (1),

  sum_amount_sold NUMBER

);

CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;

CREATE TYPE sales_roll_t AS OBJECT (

  channel_desc  VARCHAR2 (20),

  country_id  CHAR (2),

  sum_amount_sold NUMBER

);

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

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 ;

/

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 ;

/

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 ;

/

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 ;

/

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' )));