Shulou(Shulou.com)06/01 Report--

Table functions can accept query statements or cursors as input parameters, and can output multiple rows of data. This function can be executed in parallel and can continuously output the data stream, which is called pipeline output. With the application of table function, the data conversion can be processed in stages, and the storage and buffer table of intermediate results can be saved.

1. Transfer data with cursors

Using the cursor REF CURSOR, you can pass the dataset (multi-row records) to the PL/SQL function:

SELECT *

FROM TABLE (myfunction (CURSOR (SELECT *)

FROM mytab)

two。 Using two materialized views (or tables) as template data

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. Define object types and table types based on object types

Define object types and prepare for further references.

(1) define the object type: 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) define the table type: TYPE SUM_SALES_COUNTRY_T_TAB

CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t

(3) define the object type: 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) define the table type: TYPE SUM_SALES_GENDER_T_TAB

CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t

(5) define object type: TYPE sales_roll_t

CREATE TYPE sales_roll_t AS OBJECT (

Channel_desc VARCHAR2 (20)

Country_id CHAR (2)

Sum_amount_sold NUMBER

);

(6) define the table type: TYPE SUM_SALES_ROLL_T_TAB

CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t

(7) check the type of establishment

SELECT object_name, object_type, status

FROM user_objects

WHERE object_type = 'TYPE'

4. Definition package: 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. Define table function

(1) define table function: 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) define table function: 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) define table function: 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) define table function: 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. Call table function

The following SQL query statement invokes a defined table function.

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

The above is the analysis of the application of the table function in Oracle introduced by the editor of aspphp.online. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.