PIPELINED FUNCTION aneb jak na data dynamicky

Výsledný SQL dotaz v Oracle databázi lze maximálně dynamicky ovlivnit/generovat pomocí takzvaných PIPELINED FUNKCÍ. Jejich použití si ukážeme na následujícím příkladu:

[English version – PIPELINED FUNCTION – how to get data dynamically]

Vytvoříme nové datové typy PersonType a PersonTypeSet

Do těchto datových typů budeme ukládat naše data.

CREATE TYPE PersonType AS OBJECT
(
  id number,
  first_name varchar2(2000),
  last_name varchar2(2000),
  description varchar2(2000)
)
/

CREATE TYPE PersonTypeSet AS TABLE OF PersonType
/

Vytvoříme PIPELINED FUNKCI

CREATE OR REPLACE FUNCTION GET_PERSONS RETURN PersonTypeSet
PIPELINED
IS
    l_one_row PersonType := PersonType(NULL, NULL, NULL, NULL);

BEGIN

    FOR i IN 1..10 LOOP
        l_one_row.id := i;
        l_one_row.first_name := 'Johnny (' || i || ')';
        l_one_row.last_name := 'English';
        l_one_row.description := 'British Super Agent';
        PIPE ROW(l_one_row);
    END LOOP;

    RETURN;
END GET_PERSONS;
/

Podíváme se na data (SQL dotaz nad pipelined funkcí!)

SQL> SELECT * FROM TABLE(GET_PERSONS());

Pipelined function result (SQL query)

UPDATE 14.09.2010 – Při výskytu chyby „ORA-22905: nelze přistupovat k řádkám z položky tabulky, která není vnořená“ je nutné zavolat CAST takto: „SELECT * FROM TABLE(CAST(GET_PERSONS() AS PersonTypeSet));“protože Oracle potřebuje znát objektový typ před tím, než nahradí hvězdičku názvy sloupců!

Post to Twitter

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *