Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 36: выборка и модификация данных с использованием хранимых функций
MS SQL Решение 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
41RETURN 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 Стр: 360/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Oracle позволяет реализовывать хранимые функции, возвращающие таблицы, двумя способами (подробности можно узнать в официальной документации или, например, здесь18), которые мы и рассмотрим:
•с предварительной подготовкой всех данных внутри функции и последующей передачей их в вызывающий код;
•с мгновенной передачей данных (по мере их готовности) в вызывающий код
(т.н. pipelined-функции).
Первый вариант решения: функция по-прежнему ориентируется только на одну таблицу и возвращает все данные после их полной подготовки.
Oracle |
Решение 5.1.1.b (первый вариант решения) |
1-- Удаление старых версий типов данных:
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" |
|
|
|
|
|
|
|
35FROM 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/
18 http://stackoverflow.com/questions/21171349/difference-between-table-function-and-pipelined-function
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 361/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 (второй вариант решения) |
1-- Удаление старых версий типов данных:
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;
18CREATE OR REPLACE FUNCTION GET_FREE_KEYS (table_name IN VARCHAR2,
19 |
|
pk_name IN VARCHAR2) |
20RETURN "t_tf_free_keys_table"
21AS
22result_tab "t_tf_free_keys_table" := "t_tf_free_keys_table"();
23TYPE type_free_keys_cursor IS REF CURSOR;
24free_keys_cursor type_free_keys_cursor;
25start_value NUMBER;
26stop_value NUMBER;
27final_query VARCHAR2(1024);
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 362/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Oracle |
Решение 5.1.1.b (второй вариант решения) (продолжение) |
28BEGIN
29final_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"
45WHERE "stop" >= "start"
46ORDER BY "start",
47 |
|
"stop"'; |
48 |
|
|
49OPEN free_keys_cursor FOR final_query;
50LOOP
51FETCH free_keys_cursor INTO start_value, stop_value;
52EXIT WHEN free_keys_cursor%NOTFOUND;
53result_tab.extend;
54result_tab(result_tab.last) :=
55 |
|
"t_tf_free_keys_row"(start_value, stop_value); |
56 |
|
END LOOP; |
57 |
|
|
58CLOSE free_keys_cursor;
59RETURN result_tab;
60END;
61/
Получить результат работы функции можно следующим запросом.
Oracle Решение 5.1.1.b (запрос для получения результата работы функции)
1 SELECT * FROM TABLE(GET_FREE_KEYS('subscriptions', 'sb_id'))
Третий вариант решения: функция всё также принимает имя таблицы и первичного ключа, но возвращает табличные данные без предварительной полной генерации (экономится память).
Здесь иначе выглядит тело цикла работы с курсором: вместо того, чтобы формировать новый элемент коллекции данных, мы извлекаем данные в переменные (строка 49), проверяем успех операции и выходим из цикла, если данных больше нет (строка 50), и передаём данные в вызывающий код, если они есть (строка 51).
Oracle |
Решение 5.1.1.b (третий вариант решения) |
1-- Удаление старых версий типов данных:
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/
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 363/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Oracle |
Решение 5.1.1.b (третий вариант решения) (продолжение) |
15-- Сама функция:
16DROP FUNCTION GET_FREE_KEYS;
17CREATE OR REPLACE FUNCTION GET_FREE_KEYS (table_name IN VARCHAR2,
18 |
pk_name IN VARCHAR2) |
19RETURN "t_tf_free_keys_table" PIPELINED
20AS
21TYPE type_free_keys_cursor IS REF CURSOR;
22free_keys_cursor type_free_keys_cursor;
23start_value NUMBER;
24stop_value NUMBER;
25final_query VARCHAR2(1024);
26BEGIN
27final_query := 'SELECT "start",
28 |
|
|
"stop" |
|
29 |
|
FROM |
(SELECT "min_t"."' || pk_name || |
|
30 |
|
|
'" + 1 |
AS "start", |
31 |
|
(SELECT |
MIN("' || pk_name || '") - 1 |
|
32 |
|
FROM |
"' || table_name || '" "x" |
|
33 |
|
WHERE |
"x"."' || pk_name || '" > "min_t"."' || |
|
34 |
|
|
pk_name || '") AS "stop" |
|
35 |
|
FROM "' || table_name || '" "min_t" |
|
|
36 |
|
UNION |
|
|
37 |
|
SELECT 1 |
|
AS "start", |
38 |
|
(SELECT |
MIN("' || pk_name || '") - 1 |
|
39 |
|
FROM |
"' || table_name || '" "x" |
|
40 |
|
WHERE |
"' || pk_name || '" > 0) |
AS "stop" |
41 |
|
FROM dual |
|
|
42) "data"
43WHERE "stop" >= "start"
44ORDER BY "start",
45 |
|
"stop"'; |
46 |
|
|
47OPEN free_keys_cursor FOR final_query;
48LOOP
49FETCH free_keys_cursor INTO start_value, stop_value;
50EXIT WHEN free_keys_cursor%NOTFOUND;
51PIPE ROW("t_tf_free_keys_row"(start_value, stop_value));
52END LOOP;
53
54CLOSE free_keys_cursor;
55RETURN;
56END;
57/
Получить результат работы функции можно следующим запросом.
Oracle Решение 5.1.1.b (запрос для получения результата работы функции)
1 SELECT * FROM TABLE(GET_FREE_KEYS('subscriptions', 'sb_id'))
Второй и третий варианты решений, даже будучи универсальными в плане возможности работы с любой таблицей, обладают одним небольшим недостатком
— помимо имени обрабатываемой таблицы необходимо также передавать имя её первичного ключа. Это, конечно, мелочь, но от неё достаточно просто избавиться.
В представленном далее решении осознанно (для упрощения логики) не выполняются проверки на: существование первичного ключа, тип данных первичного ключа, простоту первичного ключа (состоит ли он из одного поля, или же является составным, т.е. состоит из нескольких полей) и т.д.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 364/545