A brief analysis of the application of table functions in Oracle-PHP开发-php.cn

The table function can accept query statements or cursors as input parameters, and can output multiple rows of data. This function can be executed in parallel and continuously output a stream of data, which is called pipelined output. Applying table functions handles data transformation in stages and eliminates the need for storage and buffering tables of intermediate results.

1. Use a cursor to transfer data

Use the cursor REF CURSOR to pass the data set (multiple row records) to the PL/SQL function:

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

Copy after login

2. Use 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

Copy after login

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;

Copy after login

3. Define object types and table types based on object types

Define object types and prepare them 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;

Copy after login

(2) Define the table type: TYPE SUM_SALES_COUNTRY_T_TAB

CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

Copy after login

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

Copy after login

(4) Define the table type: TYPE SUM_SALES_GENDER_T_TAB

CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;

Copy after login

(5) Definition of 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 );

Copy after login

(6) Definition Table type: TYPE SUM_SALES_ROLL_T_TAB

CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;

Copy after login

(7) Check the created type

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

Copy after login

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;

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

6. Call table functions

The following SQL query statements call defined table functions.

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

Copy after login

The above is a brief analysis of the application of table functions in Oracle introduced by the editor. 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. . I would also like to thank you all for your support of the PHP Chinese website!

For more articles related to the application of table functions in Oracle, please pay attention to the PHP Chinese website!