В этом учебном пособии вы узнаете, как использовать
оператор 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 можно определять функции и процедуры.
Примеры Oracle PL/SQL оператор WITH
Пример WITH с одним подзапросом
Пример WITH с двумя подзапросами
Пример WITH с функцией
Пример WITH с процедурой
Пример PRAGMA UDF
Для примера использования оператор 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;
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.
Например:
В этом примере мы создали подзапрос с псевдонимом
employee
из таблицы
Employees
(сотрудников). Затем с помощью двух основных запросов и оператора
UNION ALL
запросили данные всех сотрудников у которых
empno
> 7900 и всех сотрудников у которых
sal
> 3000.
В следующем примере мы хотим узнать сколько людей в отделе для каждого сотрудника.
Пример 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;
-- Сбор статистики на CTAS больше не требуется в 12c,
-- при условии, что запрос выдается не-SYS пользователем.
-- EXEC DBMS_STATS.gather_table_stats (USER, 't1');
С точки зрения разрешения имен функций, определенных в разделе объявлений PL/SQL оператора WITH, имеют приоритет над объектами с тем же именем, определенным на уровне схемы.
Пример WITH с процедурой
Мы также можем определить процедуры в разделе объявлений оператора 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
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:
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
OPEN l_cursor FOR l_sql;
FETCH l_cursor INTO l_value;
DBMS_OUTPUT.put_line('l_value=' || l_value);
CLOSE l_cursor;
l_value=1
PL/SQL procedure successfully completed.
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 заключается в повышении производительности.
Создайте обычную функцию для использования в качестве сравнения.
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
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 ');
WITH_FUNCTION : Time=45 hsecs CPU Time=39 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs
PL/SQL procedure successfully completed.
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. Следующий код переопределяет предыдущую обычную функцию для использования этой прагмы.
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
PRAGMA UDF;
BEGIN
RETURN p_id;
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 ');
WITH_FUNCTION : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs
PL/SQL procedure successfully completed.
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, не удастся. Это не похоже на случайность.
