Oracle PL/SQL оператор WITH — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать оператор WITH в Oracle PL/SQL с синтаксисом и примерами.

Описание

Oracle PL/SQL оператор WITH позволяет дать блоку подзапроса имя/псевдоним, на которое можно ссылаться в нескольких местах основного SQL-запроса. Имя, присвоенное подзапросу, обрабатывается так, как если бы оно было встроенным представлением или таблицей. SQL оператор WITH по сути является заменой обычному подзапросу.

Синтаксис

Cинтаксис Oracle PL/SQL WITH с одним подзапросом:

WITH query_name AS (SELECT expressions FROM table_A)
SELECT column_list
FROM query_name [,table_name]
[WHERE conditions]

или

Cинтаксис Oracle PL/SQL WITH с с несколькими подзапросами:

WITH query_name_A AS
(SELECT expressions FROM table_A),
query_name_B AS
([SELECT expressions FROM query_name_A] | [SELECT expressions FROM table_B])
SELECT column_list
FROM query_name_A, query_name_B [,table_X | Join table_Z]
[WHERE conditions]

expressions - поля или расчеты подзапроса.
column_list - поля или расчеты основного запроса.
table_A, table_B, table_X, table_Z - таблицы или соединения для подзапросов.
query_name_A, query_name_B - псевдоним подзапроса. Если подзапросов несколько, то они перечисляются через запятую.
WHERE conditions - условия которые должны быть выполнены для основных запросов.

Примечание

Формально предложение WITH называется факторингом подзапроса.
Предложение SQL WITH используется, когда подзапрос выполняется несколько раз.
Подзапросы в WITH перечисляются через запятую.

Применение

SQL-предложение WITH было введено Oracle в базе данных Oracle 9i выпуск 2.
Начиная с Oracle Database 12c Release 1 (12.1) в операторе WITH можно определять функции и процедуры.

Для примера использования оператор WITH, с одним и двумя подзапросами, создадим несколько таблиц с данными.

--удалим таблицы из базы данных, если существуют
-- DROP TABLE EMP PURGE;
-- DROP TABLE DEPT PURGE;

--Создадим две таблицы EMP и DEPT:
CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  CITY VARCHAR2(15)
) ;

CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

--И добавим в таблицы EMP и DEPT следующие данные:
--DEPT
	INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
	INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
	INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
	INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
--EMP
	INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17.12.1980','dd.mm.yyyy'),800,NULL,20);
	INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20.2.1981','dd.mm.yyyy'),1600,300,30);
	INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22.2.1981','dd.mm.yyyy'),1250,500,30);
	INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2.4.1981','dd.mm.yyyy'),2975,NULL,20);
	INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28.9.1981','dd.mm.yyyy'),1250,1400,30);
	INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1.5.1981','dd.mm.yyyy'),2850,NULL,30);
	INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9.6.1981','dd.mm.yyyy'),2450,NULL,10);
	INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13.7.87','dd.mm.rr').85,3000,NULL,20);
	INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17.11.1981','dd.mm.yyyy'),5000,NULL,10);
	INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8.9.1981','dd.mm.yyyy'),1500,0,30);
	INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13.7.87', 'dd.mm.rr').51,1100,NULL,20);
	INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3.12.1981','dd.mm.yyyy'),950,NULL,30);
	INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3.12.1981','dd.mm.yyyy'),3000,NULL,20);
	INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23.1.1982','dd.mm.yyyy'),1300,NULL,10);
COMMIT;

--удалим таблицы из базы данных, если существуют

-- DROP TABLE EMP PURGE;

-- DROP TABLE DEPT PURGE;

--Создадим две таблицы EMP и DEPT:

CREATE TABLE DEPT (

DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

DNAME VARCHAR2(14),

CITY VARCHAR2(15)

) ;

CREATE TABLE EMP (

EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT

);

--И добавим в таблицы EMP и DEPT следующие данные:

--DEPT

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

--EMP

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17.12.1980','dd.mm.yyyy'),800,NULL,20);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20.2.1981','dd.mm.yyyy'),1600,300,30);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22.2.1981','dd.mm.yyyy'),1250,500,30);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2.4.1981','dd.mm.yyyy'),2975,NULL,20);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28.9.1981','dd.mm.yyyy'),1250,1400,30);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1.5.1981','dd.mm.yyyy'),2850,NULL,30);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9.6.1981','dd.mm.yyyy'),2450,NULL,10);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13.7.87','dd.mm.rr').85,3000,NULL,20);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17.11.1981','dd.mm.yyyy'),5000,NULL,10);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8.9.1981','dd.mm.yyyy'),1500,0,30);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13.7.87', 'dd.mm.rr').51,1100,NULL,20);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3.12.1981','dd.mm.yyyy'),950,NULL,30);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3.12.1981','dd.mm.yyyy'),3000,NULL,20);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23.1.1982','dd.mm.yyyy'),1300,NULL,10);

COMMIT;

Пример WITH с одним подзапросом

Рассмотрим пример sql оператора with c одним подзапросом, чтобы понять как использовать оператор with в Oracle PL/SQL.
Например:

WITH employee AS (SELECT * FROM emp)
SELECT * FROM employee WHERE empno > 7900
 UNION ALL
SELECT * FROM employee WHERE sal > 3000

WITH employee AS (SELECT * FROM emp)

SELECT * FROM employee WHERE empno > 7900

UNION ALL

SELECT * FROM employee WHERE sal > 3000

В этом примере мы создали подзапрос с псевдонимом employee из таблицы Employees (сотрудников). Затем с помощью двух основных запросов и оператора UNION ALL запросили данные всех сотрудников у которых empno > 7900 и всех сотрудников у которых sal > 3000.

В следующем примере мы хотим узнать сколько людей в отделе для каждого сотрудника.

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
    FROM emp
   GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
  FROM emp e
  JOIN dept_count dc ON e.deptno = dc.deptno;

WITH dept_count AS (

SELECT deptno, COUNT(*) AS dept_count

FROM emp

GROUP BY deptno)

SELECT e.ename AS employee_name,

dc.dept_count AS emp_dept_count

FROM emp e

JOIN dept_count dc ON e.deptno = dc.deptno;

Пример WITH с двумя подзапросами

Для примера использования оператор WITH, создадим несколько таблиц с данными.

Например нам может понадобится выбрать отделы с заработной платой выше среднего. Для этого сначала в первом подзапросе dept_costs определим сумму зарплат по отделам. Затем во втором подзапросе avg_cost определим среднюю зарплату по отделам.
Например.

WITH
  dept_costs AS (
    SELECT dname, SUM(sal) dept_total
      FROM   emp e, dept d
     WHERE  e.deptno = d.deptno
     GROUP BY dname
	 ),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
      FROM dept_costs
	  )
SELECT *
  FROM dept_costs
 WHERE  dept_total > (SELECT avg FROM avg_cost)
 ORDER BY dname;

WITH

dept_costs AS (

SELECT dname, SUM(sal) dept_total

FROM emp e, dept d

WHERE e.deptno = d.deptno

GROUP BY dname

avg_cost AS (

SELECT SUM(dept_total)/COUNT(*) avg

FROM dept_costs

)

SELECT *

FROM dept_costs

WHERE dept_total > (SELECT avg FROM avg_cost)

ORDER BY dname;

Пример WITH с функцией

Для примеров оператора WITH необходимо создать следующую тестовую таблицу.

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM   dual
CONNECT BY level <= 1000000;

-- Сбор статистики на CTAS больше не требуется в 12c,
-- при условии, что запрос выдается не-SYS пользователем.
-- EXEC DBMS_STATS.gather_table_stats (USER, 't1');

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS

SELECT 1 AS id

FROM dual

CONNECT BY level <= 1000000;

-- Сбор статистики на CTAS больше не требуется в 12c,

-- при условии, что запрос выдается не-SYS пользователем.

-- EXEC DBMS_STATS.gather_table_stats (USER, 't1');

В этом операторе WITH раздел объявления может использоваться для определения функций PL/SQL, как показано ниже.

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
  FROM   t1
 WHERE  rownum = 1

--результат
WITH_FUNCTION(ID)
-----------------
                1

WITH

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_id;

END;

SELECT with_function(id)

FROM t1

WHERE rownum = 1

--результат

WITH_FUNCTION(ID)

-----------------

С точки зрения разрешения имен функций, определенных в разделе объявлений PL/SQL оператора WITH, имеют приоритет над объектами с тем же именем, определенным на уровне схемы.

Пример WITH с процедурой

Мы также можем определить процедуры в разделе объявлений оператора WITH, даже если они не используются.

WITH
  PROCEDURE with_procedure(p_id IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('p_id=' || p_id);
  END;
SELECT id
  FROM   t1
 WHERE  rownum = 1

--результат        
		ID
----------
         1

WITH

PROCEDURE with_procedure(p_id IN NUMBER) IS

BEGIN

DBMS_OUTPUT.put_line('p_id=' || p_id);

END;

SELECT id

FROM t1

WHERE rownum = 1

--результат

----------

В действительности вы поместили бы процедуру в операторе WITH, только если бы планировали вызывать процедуру из функции в разделе объявлений.

WITH
  PROCEDURE with_procedure(p_id IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('p_id=' || p_id);
  END;

  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    with_procedure(p_id);
    RETURN p_id;
  END;
SELECT with_function(id)
  FROM   t1
 WHERE  rownum = 1

--результат
WITH_FUNCTION(ID)
-----------------
                1

p_id=1

WITH

PROCEDURE with_procedure(p_id IN NUMBER) IS

BEGIN

DBMS_OUTPUT.put_line('p_id=' || p_id);

END;

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS

BEGIN

with_procedure(p_id);

RETURN p_id;

END;

SELECT with_function(id)

FROM t1

WHERE rownum = 1

--результат

WITH_FUNCTION(ID)

-----------------

p_id=1

Похоже, что эта функция не поддерживается PL/SQL. Любая попытка ее использования приводит к ошибкам компиляции, как показано ниже.

BEGIN
  FOR cur_rec IN (WITH
                    FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
                    BEGIN
                      RETURN p_id;
                    END;
                  SELECT with_function(id)
                  FROM   t1
                  WHERE  rownum = 1)
  LOOP
    NULL;
  END LOOP;
END;

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
                             *
ERROR at line 3:
ORA-06550: line 3, column 30:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop

BEGIN

FOR cur_rec IN (WITH

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_id;

END;

SELECT with_function(id)

FROM t1

WHERE rownum = 1)

LOOP

NULL;

END LOOP;

END;

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS

ERROR at line 3:

ORA-06550: line 3, column 30:

PL/SQL: ORA-00905: missing keyword

ORA-06550: line 2, column 19:

PL/SQL: SQL Statement ignored

ORA-06550: line 5, column 34:

PLS-00103: Encountered the symbol ";" when expecting one of the following:

loop

Использование динамического SQL позволяет обойти это ограничение.

SET SERVEROUTPUT ON
DECLARE
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  l_value   NUMBER;
BEGIN
  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1
            WHERE  rownum = 1';
  
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor INTO l_value;
  DBMS_OUTPUT.put_line('l_value=' || l_value);
  CLOSE l_cursor;
END;

l_value=1

PL/SQL procedure successfully completed.

SET SERVEROUTPUT ON

DECLARE

l_sql VARCHAR2(32767);

l_cursor SYS_REFCURSOR;

l_value NUMBER;

BEGIN

l_sql := 'WITH

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_id;

END;

SELECT with_function(id)

FROM t1

WHERE rownum = 1';

OPEN l_cursor FOR l_sql;

FETCH l_cursor INTO l_value;

DBMS_OUTPUT.put_line('l_value=' || l_value);

CLOSE l_cursor;

END;

l_value=1

PL/SQL procedure successfully completed.

Поддержка этой функции с использованием статического SQL внутри PL/SQL ожидается в следующем релизе Oracle.

Преимущества производительности

Вся причина определения встроенного кода PL/SQL заключается в повышении производительности.
Создайте обычную функцию для использования в качестве сравнения.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_id;
END;

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_id;

END;

Запустите следующий тест, который измеряет затраченное время и загрузку ЦП запроса, используя определение встроенной функции.

DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
  
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab t_tab;
BEGIN
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('WITH_FUNCTION  : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'SELECT normal_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 
END;

WITH_FUNCTION  : Time=45 hsecs CPU Time=39 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs

PL/SQL procedure successfully completed.

DECLARE

l_time PLS_INTEGER;

l_cpu PLS_INTEGER;

l_sql VARCHAR2(32767);

l_cursor SYS_REFCURSOR;

TYPE t_tab IS TABLE OF NUMBER;

l_tab t_tab;

BEGIN

l_time := DBMS_UTILITY.get_time;

l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'WITH

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_id;

END;

SELECT with_function(id)

FROM t1';

OPEN l_cursor FOR l_sql;

FETCH l_cursor

BULK COLLECT INTO l_tab;

CLOSE l_cursor;

DBMS_OUTPUT.put_line('WITH_FUNCTION : ' ||

'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||

'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

l_time := DBMS_UTILITY.get_time;

l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'SELECT normal_function(id)

FROM t1';

OPEN l_cursor FOR l_sql;

FETCH l_cursor

BULK COLLECT INTO l_tab;

CLOSE l_cursor;

DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||

'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||

'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

END;

WITH_FUNCTION : Time=45 hsecs CPU Time=39 hsecs

NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs

PL/SQL procedure successfully completed.

Из этого мы видим, что определение встроенной функции занимает приблизительно одну треть затраченного времени и времени процессора для завершения.

Пример PRAGMA UDF

В ряде презентаций, предшествовавших официальному выпуску 12c, выступавшие упоминали PRAGMA UDF(User Defined Function), которая предположительно дает вам преимущества производительности встроенного PL/SQL, в то же время позволяя вам определять объект PL/SQL вне оператора SQL. Следующий код переопределяет предыдущую обычную функцию для использования этой прагмы.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
  PRAGMA UDF;
BEGIN
  RETURN p_id;
END;

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS

PRAGMA UDF;

BEGIN

RETURN p_id;

END;

Как только функция скомпилирована, выполнение теста из предыдущего раздела для этой функции дает довольно интересные результаты.

SET SERVEROUTPUT ON
DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
  
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab t_tab;
BEGIN
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('WITH_FUNCTION  : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'SELECT normal_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 
END;

WITH_FUNCTION  : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs

PL/SQL procedure successfully completed.

SET SERVEROUTPUT ON

DECLARE

l_time PLS_INTEGER;

l_cpu PLS_INTEGER;

l_sql VARCHAR2(32767);

l_cursor SYS_REFCURSOR;

TYPE t_tab IS TABLE OF NUMBER;

l_tab t_tab;

BEGIN

l_time := DBMS_UTILITY.get_time;

l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'WITH

FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN p_id;

END;

SELECT with_function(id)

FROM t1';

OPEN l_cursor FOR l_sql;

FETCH l_cursor

BULK COLLECT INTO l_tab;

CLOSE l_cursor;

DBMS_OUTPUT.put_line('WITH_FUNCTION : ' ||

'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||

'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

l_time := DBMS_UTILITY.get_time;

l_cpu := DBMS_UTILITY.get_cpu_time;

l_sql := 'SELECT normal_function(id)

FROM t1';

OPEN l_cursor FOR l_sql;

FETCH l_cursor

BULK COLLECT INTO l_tab;

CLOSE l_cursor;

DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||

'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||

'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

END;

WITH_FUNCTION : Time=44 hsecs CPU Time=40 hsecs

NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs

PL/SQL procedure successfully completed.

Кажется, что автономная функция, использующая PRAGMA UDF, последовательно выполняет встроенную функцию.

У меня сложилось впечатление, что вызов функции, определенной с помощью PRAGMA UDF напрямую из PL / SQL, не удастся. Это не похоже на случайность.

DECLARE
  l_number NUMBER;
BEGIN
  l_number := normal_function(1);
END;

PL/SQL procedure successfully completed.

DECLARE

l_number NUMBER;

BEGIN

l_number := normal_function(1);

END;

PL/SQL procedure successfully completed.