Eine kurze Analyse der Anwendung von Tabellenfunktionen in Oracle

Tabellenfunktionen können Abfrageanweisungen oder Cursor als Eingabeparameter akzeptieren und mehrere Datenzeilen ausgeben. Diese Funktion kann parallel ausgeführt werden und kontinuierlich einen Datenstrom ausgeben, der als Pipeline-Ausgabe bezeichnet wird. Durch die Anwendung von Tabellenfunktionen werden Datentransformationen stufenweise durchgeführt und die Speicherung und Pufferung von Tabellen mit Zwischenergebnissen entfällt.

1. Verwenden Sie den Cursor, um Daten zu übertragen

Verwenden Sie den Cursor REF CURSOR, um den Datensatz (mehrzeilige Datensätze) an die PL/SQL-Funktion zu übertragen:

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

2. Verwenden Sie zwei materialisierte Ansichten (oder Tabellen) als Vorlagendaten

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. Objekttypen und Tabellentypen basierend auf Objekttypen definieren

Objekttypen definieren und für weitere Referenzen vorbereiten.

(1) Definieren Sie den Objekttyp: 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) Definieren Sie den Tabellentyp: TYPE SUM_SALES_COUNTRY_T_TAB

CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

(3) Objekttyp definieren: 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 ) Definition Tabellentyp: TYPE SUM_SALES_GENDER_T_TAB

CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;

(5) Objekttyp definieren: TYPE sales_roll_t

CREATE TYPE sales_roll_t AS OBJECT (
 channel_desc  VARCHAR2 (20),
 country_id  CHAR (2),
 sum_amount_sold NUMBER
);

(6) Definieren Sie den Tabellentyp: TYPE SUM_SALES_ROLL_T_TAB

CREATE TYPE sum_sales_roll_t_tab AS TABLE OF sales_roll_t;

(7) Überprüfen Sie den erstellten Typ

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

4. Paket erstellen und REF CURSOR definieren

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 Funktion

(1) Tabellenfunktion definieren: 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) Tabellenfunktion definieren: 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) Tabellenfunktion definieren: 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 ) Tabellenfunktion definieren: 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. Tabellenfunktion aufrufen

Die folgende SQL-Abfrageanweisung ruft die definierte Tabellenfunktion auf.

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

Das Obige ist eine kurze Analyse der vom Herausgeber vorgestellten Tabellenfunktionen. Ich hoffe, sie wird Ihnen hilfreich sein. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht Der Herausgeber wird rechtzeitig antworten. Ich möchte mich auch bei Ihnen allen für Ihre Unterstützung der chinesischen PHP-Website bedanken!

Weitere Artikel zur Anwendung von Tabellenfunktionen in Oracle finden Sie auf der chinesischen PHP-Website!