Les fonctions de table peuvent accepter des instructions de requête ou des curseurs comme paramètres d'entrée et peuvent générer plusieurs lignes de données. Cette fonction peut être exécutée en parallèle et générer en continu un flux de données, appelé sortie pipeline. L'application de fonctions de table gère la transformation des données par étapes et élimine le besoin de tables de stockage et de mise en mémoire tampon des résultats intermédiaires.
1. Utilisez le curseur pour transférer des données
Utilisez le curseur REF CURSOR pour transmettre l'ensemble de données (enregistrements multi-lignes) à la fonction PL/SQL :
SELECT * FROM TABLE (myfunction (CURSOR (SELECT * FROM mytab)));
2. Utilisez deux vues matérialisées (ou tables) comme données de modèle
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. Définissez les types d'objets et les types de tables en fonction des types d'objets
Définissez les types d'objets et préparez-vous à d'autres références.
(1) Définir le type d'objet : 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) Définir le type de table : TYPE SUM_SALES_COUNTRY_T_TAB
CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;
(3) Définir le type d'objet : 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 ) Définition Type de table : TYPE SUM_SALES_GENDER_T_TAB
CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;
(5) Définir le type d'objet : TYPE sales_roll_t
CREATE TYPE sales_roll_t AS OBJECT ( channel_desc VARCHAR2 (20), country_id CHAR (2), sum_amount_sold NUMBER );
(6) Définir le type de table : TYPE SUM_SALES_ROLL_T_TAB
CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;
(7) Vérifier le type créé
SELECT object_name, object_type, status FROM user_objects WHERE object_type = 'TYPE';
4. Créer un package et définir 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. fonction
(1) Définir la fonction de table : 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) Définir la fonction de table : 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) Définir la fonction de table : 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 ) Définir la fonction de table : 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. Appeler la fonction de table
L'instruction de requête SQL suivante appelle la fonction de table définie.
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')));
Ce qui précède est une brève analyse de l'application des fonctions de table dans Oracle présentée par l'éditeur. J'espère que cela vous sera utile. Si vous avez des questions, veuillez me laisser un message et le. l'éditeur répondra à temps. Je voudrais également vous remercier tous pour votre soutien au site Web PHP chinois !
Pour plus d'articles sur l'application des fonctions de table dans Oracle, veuillez faire attention au site Web PHP chinois !