Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 36: выборка и модификация данных с использованием хранимых функций
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 Стр: 385/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Oracl |
і |
Решение 5.1.1 .b (третий вариант решения) (продолжение) |
| |
|
|||||
e |
|
||||||||
|
|
|
|
|
|
|
|
|
|
15 |
-- Сама функция: |
|
|
|
|
|
|
||
16 |
DROP FUNCTION GET FREE KEYS; |
|
|
|
|
||||
17 |
CREATE OR REPLACE FUNCTION GET FREE KEYS |
table name IN VARCHAR2, |
|||||||
18 |
|
|
|
|
|
|
pk name IN VARCHAR2) |
||
19 |
RETURN "t tf free keys table" PIPELINED |
|
|
|
|
||||
20 |
AS |
|
|
|
|
|
|
|
|
21 |
TYPE type free keys cursor IS REF CURSOR; |
|
|
|
|||||
22 |
free keys cursor type free keys cursor |
|
|
|
|
||||
23 |
start value NUMBER; |
|
|
|
|
|
|||
24 |
stop value NUMBER; |
|
|
|
|
|
|||
25 |
final query VARCHAR2 1024 ; |
|
|
|
|
||||
26 |
BEGIN |
|
|
|
|
|
|
|
|
27 |
|
final 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" |
|
|
|
|
|
|
43 |
|
WHERE |
"stop" >= "start" |
|
|
|
|
||
44 |
|
ORDER BY "start", |
|
|
|
|
|
||
45 |
|
|
"stop"'; |
|
|
|
|
|
|
46 |
|
|
|
|
|
|
|
|
|
47 |
|
OPEN free keys cursor FOR final query |
|
|
|
|
|||
48 |
|
LOOP |
|
|
|
|
|
|
|
49 |
|
FETCH free keys cursor INTO start value |
stop value |
|
|||||
50 |
|
EXIT WHEN free keys cursor%NOTFOUND; |
|
|
|
|
|||
51 |
|
PIPE ROW("t_tf_free_keys_row" start_value, stop_value)); |
|||||||
52 |
|
END LOOP; |
|
|
|
|
|
|
|
53 |
|
|
|
|
|
|
|
|
|
54 |
|
CLOSE free keys cursor |
|
|
|
|
|
||
55 |
|
RETURN; |
|
|
|
|
|
|
|
56 |
END; |
|
|
|
|
|
|
|
|
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 Стр: 386/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Информацию о первичном ключе таблицы можно получить запросом вида19:
Oracl |
і Решение 5.1.1 .b (получение информации о первичном ключе таблицы) |
| |
|||
e |
|||||
|
|
|
|
||
1 |
SELECT cols table name |
|
|
||
2 |
|
cols column name |
|
|
|
3 |
|
cols position |
|
|
|
4 |
|
cons status, |
|
|
|
5 |
|
cons owner |
|
|
|
6 |
FROM |
all constraints cons |
|
|
|
7 |
|
all cons columns cols |
|
|
|
8 |
WHERE |
cols table name = 'TABLE NAME' |
|
||
9 |
|
AND cons constraint type = |
'P' |
|
|
10 |
|
AND cons constraint name = cols constraint name |
|||
11 |
|
AND cons owner = cols owner |
|
|
|
12 |
ORDER BY cols table name, |
|
|
||
13 |
|
cols position |
|
|
|
Поскольку нас будет интересовать только ситуация с простым первичным ключом и только в таблице, относящейся к текущему пользователю (от имени которого установлено соединение), мы добавим два ограничения: cons.owner = USER
— показывать только данные по текущему пользователю, ROWNUM = 1 — показы вать только первое поле (на случай составного первичного ключа).
Четвёртый вариант решения: доработка третьего варианта с автоматическим определением имени первичного ключа таблицы.
В строках 30-37 формируется запрос, с помощью которого будет определено имя первичного ключа обрабатываемой таблицы, а в строке 42 он выполняется, а его результат помещается в переменную pk_name.
Если вы захотите раскомментировать отладочный вывод (строки 40, 45, 68), не забудьте выполнить команду SET SERVEROUTPUT ON (иначе выводимые данные не будут отображаться).
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;
18CREATE OR REPLACE FUNCTION GET FREE KEYS (table 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;
25pk name VARCHAR2 1024 ;
26getpk query VARCHAR2 1024 ;
27final query VARCHAR2 1024 ;
19 http://stackoverflow.com/questions/5353522/how-to-query-for-a-primary-key-in-oracle-11 g
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 387/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Oracl |
і Решение 5.1.1 .b (четвёртый вариант решения) (продолжение) | |
|
e |
||
|
||
28 |
BEGIN |
|
29 |
|
30getpk query := 'SELECT cols.column name FROM all constraints cons,
31all cons columns cols
32WHERE cols.table name = ''' || table name || '''
33AND cons.constraint type = ''P''
34AND cons.constraint name = cols.constraint name
35AND cons.owner = cols.owner
36AND cons.owner = USER
37AND ROWNUM = 1';
38
39— Раскомментируйте, чтобы увидеть весь запрос для получения имени ПК:
40— DBMS_OUTPUT.PUT_LINE(getpk_query);
41
42 EXECUTE IMMEDIATE getpk_query INTO pk_name; 43
44— Раскомментируйте, чтобы увидеть имя ПК:
45— DBMS_OUTPUT.PUT_LINE(pk_name);
46 |
|
|
|
|
47 |
final query := 'SELECT "start", |
|
||
48 |
|
|
"stop" |
|
49 |
|
FROM |
(SELECT "min t"."' || pk name || |
|
50 |
|
|
'" + 1 |
AS "start", |
51 |
|
(SELECT MIN("' || pk name || '") - 1 |
|
|
52 |
|
FROM |
"' || table name || '" "x" |
|
53 |
|
WHERE |
"x"."' || pk name || '" > "min t"."' || |
|
54 |
|
|
pk name || |
'") AS "stop" |
55 |
FROM |
"' || table name || '" "min t" |
|
|
56 |
UNION |
|
|
|
57 |
SELECT 1 |
|
AS "start", |
|
58 |
|
(SELECT MIN("' || pk name || '") - 1 |
|
|
59 |
|
FROM |
"' || table name || '" "x" |
|
60 |
|
WHERE |
"' || pk name || '" > 0) |
AS "stop" |
61 |
FROM dual |
|
|
|
62 |
) "data" |
|
|
|
63WHERE "stop" >= "start"
64ORDER BY "start",
65 |
"stop"'; |
66 |
|
67— Раскомментируйте, чтобы увидеть финальный запрос:
68— DBMS_OUTPUT.PUT_LINE(final_query);
69
70OPEN free keys cursor FOR final query
71LOOP
72FETCH free keys cursor INTO start value stop value
73EXIT WHEN free keys cursor NOTFOUND
74PIPE ROW("t_tf_free_keys_row" start_value, stop_value));
75END LOOP;
76
77CLOSE free keys cursor;
78RETURN;
79END;
80/
Получить результат работы функции можно следующим запросом.
Oracle Решение 5.1.1 .b (запрос для получения результата работы функции)
1 SELECT * FROM TABLE(GET_FREE_KEYS('subscriptions'))
На этом решение данной задачи завершено.
Реализовать ещё два варианта поведения функции, в которых она возвращает таблицу из одного поля со списком ключей или строку со списком ключей вам предлагается самостоятельно в задании 5.1.1.TSK.C{369}.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 388/545
Пример 36: выборка и модификация данных с использованием хранимых функций
Решение 5.1.1 .c{352}.
Поскольку решение данной задачи базируется на решении{216} задачи
3.1.2. a{215}), нам остаётся только «обернуть» в функцию запрос на обновление данных. Исходное и конечное значение количества книг в библиотеке мы будем получать обычным SELECT-запросом до и после обновления данных.
Также отметим, что:
•решения данной задачи для MS SQL Server не существует, т.к. эта СУБД не позволяет выполнять операции модификации данных в хранимых функциях;
•решение данной задачи для Oracle придётся реализовывать через удаление ранее созданного материализованного представления и создания агрегирующей таблицы по аналогии с MySQL, т.к. в противном случае задача не имеет смысла — данные в материализованном представлении итак находятся в актуальном состоянии, а наша функция всегда будет возвращать значение 0.
Всамой задаче 3.1.2.a{215} требовалось создать представление, хранящее в себе фактические значения агрегированных данных, но MySQL не поддерживает такие представления, что оказывается очень кстати для решения данной задачи: в случае MySQL у нас роль такого представления играет реальная таблица, данные которой мы и будем обновлять.
MySQL Решение 5.1.1.С (создание таблицы и инициализация
1-- Создание таблицы:
2CREATE TABLE 'books statistics'
3(
4 |
|
'total' INTEGER UNSIGNED |
NOTNULL, |
5 |
|
'given' INTEGER UNSIGNED |
NOTNULL, |
6 |
|
'rest' INTEGER UNSIGNED |
NOTNULL |
7 |
8 |
); |
|
|
|
9-- Инициализация данных:
10INSERT INTO 'books statistics'
11 |
('total', |
12 |
'given', |
13 |
'rest') |
14SELECT IFNULL('total', 0),
15IFNULL('given', 0),
16 |
IFNULL('total' |
- |
, 0) AS ':rest' |
|
17 |
FROM (SELECT (SELECT |
SUM('b quantity') |
|
|
18 |
FROM |
'books') |
|
AS 'total' |
19 |
(SELECTCOUNT('sb book') |
|
||
20 |
FROM |
'subscriptions' |
|
|
21 |
WHERE |
' sb is active' |
'Y') AS 'given') |
|
22 |
AS 'prepared data'; |
|
|
|
Внутри кода функции остаётся лишь написать запрос на обновление данных. Получить результат работы функции можно следующим запросом.
MySQL I |
Решение 5.1 1 .c (запрос для получения результата работы функции) | |
1 SELECT BOOKS DELTA()
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 389/545