SQL for Oracle | Табличные функции с DML операциями

Аналог хранимой процедуры, выполняющей DML команды и возвращающей табличные данные – табличная функция с автономной транзакцией (PRAGMA AUTONOMOUS_TRANSACTION).

 
Такие функции можно определять только из пакета.

Пример:

--DROP FUNCTION FUNCTION_NAME;

--DROP TYPE RESULT_TABLE;

CREATE OR REPLACE TYPE OBJECT_NAME IS OBJECT (FIELD_NAME VARCHAR2(20)); --TYPE AS OBJECT

/

CREATE OR REPLACE TYPE RESULT_TABLE IS TABLE OF OBJECT_NAME; --TYPE AS TABLE

/

CREATE OR REPLACE PACKAGE PACKAGE_NAME AS --PACKAGE SPECIFICATION

 FUNCTION FUNCTION_NAME RETURN RESULT_TABLE;

END;

/

CREATE OR REPLACE PACKAGE BODY PACKAGE_NAME AS --PACKAGE BODY

 FUNCTION FUNCTION_NAME RETURN RESULT_TABLE

 IS

         PRAGMA AUTONOMOUS_TRANSACTION;

         RES RESULT_TABLE;

 BEGIN

         --SQL DML COMMAND 1 ;

         --SQL DML COMMAND 2 ;

         COMMIT;

         SELECT CAST(MULTISET(

                 SELECT FIELD_NAME FROM TABLE_NAME --SELECT STATEMENT TO RETURN

                 ) AS RESULT_TABLE)

                 INTO RES FROM DUAL;

         RETURN RES;

 END;

END;

/

См. также Вызов табличной функции из пакета