Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 36: выборка и модификация данных с использованием хранимых функций
Получить результат рабоы функции нможно следующим запросом.
MS SQL Решение 5.1.1 .b (запрос для получения результата работы функции)
1 SELECT * FROM GET FREE KEYS IN SUBSCRIPTIONS()
Во втором варианте решения мы возвратим таблицу с одним полем, которое будет содержать полный перечень свободных ключей. Это решение очень похоже на решение для MySQL с тем лишь отличием, что во вложенным цикле мы не накапливаем значения свободных ключей в строковой переменной, а помещаем их в результирующую таблицу.
MS SQL Решение 5.1.1 .b (второй вариант
1CREATEрешенияFUNCTION) GET FREE KEYS IN SUBSCRIPTIONS()
2RETURNS @free keys TABLE
3(
4[key] INT
5)
6AS
7BEGIN
8DECLARE @start value INT;
9DECLARE @stop value INT;
10DECLARE free keys cursor CURSOR LOCAL FAST FORWARD FOR
11SELECT [start],
12[stop]
13 |
FROM |
(SELECT [min t] [sb id] + 1 |
AS [start], |
||
14 |
|
|
(SELECT MIN [sb id]) - 1 |
|
|
15 |
|
|
FROM |
[subscriptions] AS [x] |
|
16 |
|
|
WHERE |
[x] [sb id] > [min t] [sb id]) AS [stop] |
|
17 |
|
FROM |
[subscriptions] AS [min t] |
|
|
18 |
|
UNION |
|
|
|
19 |
|
SELECT 1 |
|
AS [start], |
|
20 |
|
|
(SELECT MIN [sb id]) - 1 |
|
|
21 |
|
|
FROM |
[subscriptions] AS [x] |
|
22 |
|
|
WHERE |
[sb id] > 0) |
AS [stop] |
23) AS [data]
24WHERE [stop] >= [start]
25ORDER BY [start],
26 |
[stop] |
27 |
|
28OPEN free keys cursor
29FETCH NEXT FROM free keys cursor INTO @start value, @stop value;
30WHILE @@FETCH STATUS = 0
31BEGIN
32WHILE @start value <= @stop value
33BEGIN
34 INSERT INTO @free keys [key] VALUES @start value);
35SET @start value = @start value + 1;
36END;
37FETCH NEXT FROM free keys cursor INTO @start value @stop value
38END;
39CLOSE free keys cursor
40DEALLOCATE free keys cursor;
41
42RETURN
43END;
44GO
Для получения результата работы функции здесь, как и в первом варианте решения, необходимо выполнить запрос следующего вида.
MS SQL Решение 5.1.1 .b (запрос для получения результата работы функции)
1 SELECT * FROM GET FREE KEYS IN SUBSCRIPTIONS()
И, наконец, реализуем третий вариант решения, который полностью повторяет логику решения для MySQL.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 380/545
Пример 36: выборка и модификация данных с использованием хранимых функций
MS SQL I |
Решение 5.1.1.b (третий вариант решения) |
| |
1CREATE FUNCTION GET FREE KEYS IN SUBSCRIPTIONS()
2RETURNS VARCHAR(max)
3AS
4BEGIN
5DECLARE @start value INT;
6DECLARE @stop value INT;
7DECLARE @free keys string VARCHAR(max);
8DECLARE free keys cursor CURSOR LOCAL FAST FORWARD FOR
9SELECT [start],
10[stop]
11 |
FROM |
(SELECT [min t] [sb id] + 1 |
AS [start], |
||
12 |
|
|
(SELECT MIN [sb id]) - 1 |
|
|
13 |
|
|
FROM |
[subscriptions] AS [x] |
|
14 |
|
|
WHERE |
[x] [sb id] > [min t] [sb id]) AS [stop] |
|
15 |
|
FROM |
[subscriptions] AS [min t] |
|
|
16 |
|
UNION |
|
|
|
17 |
|
SELECT 1 |
|
AS [start], |
|
18 |
|
|
(SELECT MIN [sb id]) - 1 |
|
|
19 |
|
|
FROM |
[subscriptions] AS [x] |
|
20 |
|
|
WHERE |
[sb id] > 0) |
AS [stop] |
21) AS [data]
22WHERE [stop] >= [start]
23ORDER BY [start],
24 |
[stop] |
25 |
|
26OPEN free keys cursor
27FETCH NEXT FROM free keys cursor INTO @start value, @stop value;
28WHILE @@FETCH STATUS = 0
29BEGIN
30WHILE @start value <= @stop value
31BEGIN
32SET @free keys string = CONCAT @free keys string
33 |
@start value ','); |
34SET @start value = @start value + 1;
35END;
36FETCH NEXT FROM free keys cursor INTO @start value @stop value
37END;
38CLOSE free keys cursor
39DEALLOCATE free_keys_cursor;
40 |
|
|
41 |
RETURN LEFT @free keys string LEN @free keys string |
- 1 ; |
42END;
43GO
Получить результат рабоы функции нможно следующим запросом.
MS SQL Решение 5.1.1 .b (запрос для получения результата работы функции)
1 SELECT dbo.GET FREE KEYS IN SUBSCRIPTIONS()
Итак, решение для MS SQL Server завершено. Переходим к Oracle.
Здесь решение хоть и базируется на всё том же основном запросе (рассмотренном в решении для MySQL), но технологически является более сложным. К тому же Oracle — единственная из трёх СУБД, позволяющая выполнять внутри хранимых функций динамические SQL-запросы, что позволит нам в полной мере выполнить условие исходной задачи и создать универсальную функцию, возвращающую информацию по свободным ключам любой таблицы.
В первую очередь (из соображений единообразия) реализуем хранимую функцию по аналогии с решением для MS SQL Server: функция жёстко привязана к одной таблице, а на выходе возвращает таблицу с двумя полями, хранящими начало и конец диапазонов свободных ключей.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 381/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Oracle позволяет реализовывать хранимые функции, возвращающие таблицы, двумя способами (подробности можно узнать в официальной документации или, например, здесь18), которые мы и рассмотрим:
•с предварительной подготовкой всех данных внутри функции и последующей передачей их в вызывающий код;
•с мгновенной передачей данных (по мере их готовности) в вызывающий код
(т.н. pipelined-функции).
Первый вариант решения: функция по-прежнему ориентируется только на одну таблицу и возвращает все данные после их полной подготовки.
Oracle |
Решение 5.1.1 |
1-- Удалени.b е старых версий типов данных:
2DROP TYPE "t tf free keys table";
3/
4DROP TYPE "t tf free keys row"
5/
6-- Создание типа данных, описывающего ряд итоговой таблицы:
7CREATE TYPE "t tf free keys row" AS OBJECT (
8"start" NUMBER,
9"stop" NUMBER
10);
11/
12-- Создание типа данных, описывающего итоговую таблицу:
13CREATE TYPE "t tf free keys table" IS TABLE OF "t tf free keys row";
14/
15
16-- Сама функция:
17DROP FUNCTION GET FREE KEYS IN SUBSCRIPTIONS;
18CREATE OR REPLACE FUNCTION GET FREE KEYS IN SUBSCRIPTIONS
19RETURN "t tf free keys table"
20AS
21result_tab "t_tf_free_keys_table" := "t_tf_free_keys_table" );
22CURSOR free keys cursor IS
23SELECT "start",
24"stop"
25 |
FROM |
(SELECT "min t" "sb id" + 1 |
AS "start", |
||
26 |
|
|
(SELECT |
MIN "sb id") - 1 |
|
27 |
|
|
FROM |
"subscriptions" "x" |
|
28 |
|
|
WHERE |
"x" "sb id" > "min t" "sb id" |
AS "stop" |
29 |
|
FROM |
"subscriptions" "min_t" |
|
|
30 |
|
UNION |
|
|
|
31 |
|
SELECT 1 |
|
AS "start", |
|
32 |
|
|
(SELECT |
MIN "sb id") - 1 |
|
33 |
|
|
FROM |
"subscriptions" "x" |
|
34 |
|
|
WHERE |
"sb id" > 1) |
AS "stop" |
35 |
|
FROM dual |
|
|
|
36) "data"
37WHERE "stop" >= "start"
38ORDER BY "start"
39 |
"stop" |
40BEGIN
41FOR one row IN free keys cursor
42LOOP
43result tab extend;
44result tab result tab last' :=
45 |
"t_tf_free_keys_row" one_row "start", one_row "stop"); |
46 |
END LOOP; |
47 |
|
48RETURN result tab
49END;
50/
18http://stackoverflow.com/questions/21171349/difference-between-table-function-and-pipelined-function
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 382/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 383/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Прежде, чем приступить к рассмотрению кода самих функций, отметим, что Oracle требует создания специальных типов данных, позволяющих хранимым функциям возвращать таблицы (строки 1-14 всех представленных решений посвящены именно этой подзадаче).
Ключевые отличия реализации данной функции в Oracle (по сравнению с MS SQL Server) заключены в логике работы с курсором и формирования итогового результата.
Во-первых, здесь поддерживается вполне полноценный цикл FOR (строки 41-
46).
Во-вторых, для формирования итогового набора данных нам нужно выполнять две операции: добавлять в набор данных новый элемент (строка 43) и наполнять его реальными данными (строки 44-45).
В остальном здесь нет принципиальных отличий от реализации для MS SQL
Server.
Для получения результата работы функции в этом варианте решения необходимо выполнить запрос следующего вида.
Oracle Решение 5.1.1 .b (запрос для получения результата работы функции)
1 SELECT * FROM TABLE(GET FREE KEYS IN SUBSCRIPTIONS)
Второй вариант решения: функция возвращает все данные после их полной подготовки, но уже принимает имя таблицы и имя её первичного ключа.
Здесь в строках 29-47 происходит формирование значения текстовой переменной, которая представляет собой SQL-запрос, сформированный с учётом полученных через параметры функции имени таблицы и её первичного ключа.
Второе незначительное отличие заключается в том, что вместо «обычного курсора» (работающего для готовых статических SQL-запросов) мы используем т.н. REF CURSOR, который может применяться для динамического SQL.
Oracle |
|
Решение 5.1.1 .b |
|
|
|
|
|
|
|
|
Oracl |
і |
Решение 5.1.1.b (второй вариант решения) (продолжение) |
| |
|
|
|
||||
e |
|
|
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
28 |
BEGIN |
|
|
|
|
|
|
|
|
|
29 |
|
final query := 'SELECT "start", |
|
|
|
|
|
|||
30 |
|
|
|
|
"stop" |
|
|
|
|
|
31 |
|
|
|
FROM |
(SELECT "min t"."' || pk name || |
|
|
|
||
32 |
|
|
|
|
'" + 1 |
|
|
|
AS "start", |
|
33 |
|
|
|
(SELECT MIN("' || pk name || '") - 1 |
|
|
|
|||
34 |
|
|
|
FROM |
"' || table name || '" "x" |
|
|
|
||
35 |
|
|
|
WHERE |
"x"."' || pk name || '" > "min t"."' || |
|
||||
36 |
|
|
|
|
|
|
pk name || |
'") AS "stop" |
|
|
37 |
|
|
FROM |
"' || table name || '" "min t" |
|
|
|
|||
38 |
|
|
UNION |
|
|
|
|
|
|
|
39 |
|
|
SELECT 1 |
|
|
|
|
AS "start", |
|
|
40 |
|
|
|
(SELECT MIN("' || pk name || '") - 1 |
|
|
|
|||
41 |
|
|
|
FROM |
"' || table name || '" "x" |
|
|
|
||
42 |
|
|
|
WHERE |
"' || pk name || '" > 0) |
|
AS "stop" |
|
||
43 |
|
|
FROM dual |
|
|
|
|
|
|
|
44 |
|
|
) "data" |
|
|
|
|
|
|
|
45 |
|
WHERE |
"stop" >= "start" |
|
|
|
|
|
||
46 |
|
ORDER BY "start", |
|
|
|
|
|
|
||
47 |
|
|
"stop"'; |
|
|
|
|
|
|
|
48 |
|
|
|
|
|
|
|
|
|
|
49 |
|
OPEN free keys cursor FOR final query |
|
|
|
|
|
|||
50 |
|
LOOP |
|
|
|
|
|
|
|
|
51 |
|
FETCH free keys cursor INTO start value |
stop value |
|
|
|
||||
52 |
|
EXIT WHEN free keys cursor%NOTFOUND; |
|
|
|
|
|
|||
53 |
|
result tab extend |
|
|
|
|
|
|
||
54 |
|
result tab result tab last) := |
|
|
|
|
|
|||
55 |
|
|
|
|
"t_tf_free_keys_row" start_value |
stop_value |
; |
|||
56 |
|
END LOOP; |
|
|
|
|
|
|
|
|
57 |
|
|
|
|
|
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 384/545