В этом учебном пособии вы узнаете, как использовать оператор WITH в Oracle PL/SQL с синтаксисом и примерами.
Описание
Oracle PL/SQL оператор WITH позволяет дать блоку подзапроса имя/псевдоним, на которое можно ссылаться в нескольких местах основного SQL-запроса. Имя, присвоенное подзапросу, обрабатывается так, как если бы оно было встроенным представлением или таблицей. SQL оператор WITH по сути является заменой обычному подзапросу.
Синтаксис
Cинтаксис Oracle PL/SQL WITH с одним подзапросом:
SELECT column_list
FROM query_name [,table_name]
[WHERE conditions]
или
Cинтаксис Oracle PL/SQL WITH с с несколькими подзапросами:
(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 можно определять функции и процедуры.
Примеры Oracle PL/SQL оператор WITH
- Пример WITH с одним подзапросом
- Пример WITH с двумя подзапросами
- Пример WITH с функцией
- Пример WITH с процедурой
- Пример PRAGMA UDF
Для примера использования оператор WITH, с одним и двумя подзапросами, создадим несколько таблиц с данными.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
--удалим таблицы из базы данных, если существуют -- 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.
Например:
|
1 2 3 4 |
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.
В следующем примере мы хотим узнать сколько людей в отделе для каждого сотрудника.
|
1 2 3 4 5 6 7 8 |
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 определим среднюю зарплату по отделам.
Например.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 необходимо создать следующую тестовую таблицу.
|
1 2 3 4 5 6 7 8 9 10 |
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, как показано ниже.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
С точки зрения разрешения имен функций, определенных в разделе объявлений PL/SQL оператора WITH, имеют приоритет над объектами с тем же именем, определенным на уровне схемы.
Пример WITH с процедурой
Мы также можем определить процедуры в разделе объявлений оператора WITH, даже если они не используются.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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, только если бы планировали вызывать процедуру из функции в разделе объявлений.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
Похоже, что эта функция не поддерживается PL/SQL. Любая попытка ее использования приводит к ошибкам компиляции, как показано ниже.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 позволяет обойти это ограничение.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 заключается в повышении производительности.
Создайте обычную функцию для использования в качестве сравнения.
|
1 2 3 4 |
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END; |
Запустите следующий тест, который измеряет затраченное время и загрузку ЦП запроса, используя определение встроенной функции.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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. Следующий код переопределяет предыдущую обычную функцию для использования этой прагмы.
|
1 2 3 4 5 |
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS PRAGMA UDF; BEGIN RETURN p_id; END; |
Как только функция скомпилирована, выполнение теста из предыдущего раздела для этой функции дает довольно интересные результаты.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
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, не удастся. Это не похоже на случайность.
|
1 2 3 4 5 6 7 |
DECLARE l_number NUMBER; BEGIN l_number := normal_function(1); END; PL/SQL procedure successfully completed. |