Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 36: выборка и модификация данных с использованием хранимых функций

Информацию о первичном ключе таблицы можно получить запросом вида19:

Oracle Решение 5.1.1.b (получение информации о первичном ключе таблицы)

1SELECT cols.table_name,

2cols.column_name,

3cols.position,

4cons.status,

5cons.owner

9AND cons.constraint_type = 'P'

10AND cons.constraint_name = cols.constraint_name

11AND cons.owner = cols.owner

12ORDER BY cols.table_name,

13cols.position

Поскольку нас будет интересовать только ситуация с простым первичным ключом и только в таблице, относящейся к текущему пользователю (от имени которого установлено соединение), мы добавим два ограничения: cons.owner = USER

— показывать только данные по текущему пользователю, ROWNUM = 1 — показывать только первое поле (на случай составного первичного ключа).

Четвёртый вариант решения: доработка третьего варианта с автоматическим определением имени первичного ключа таблицы.

В строках 30-37 формируется запрос, с помощью которого будет определено имя первичного ключа обрабатываемой таблицы, а в строке 42 он выполняется, а его результат помещается в переменную pk_name.

Если вы захотите раскомментировать отладочный вывод (строки 40, 45, 68), не забудьте выполнить команду SET SERVEROUTPUT ON (иначе выводимые данные не будут отображаться).

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)

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-11g

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 365/545

Пример 36: выборка и модификация данных с использованием хранимых функций

Oracle Решение 5.1.1.b (четвёртый вариант решения) (продолжение)

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);

62) "data"

63WHERE "stop" >= "start"

64ORDER BY "start",

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 Стр: 366/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.c (создание таблицы и инициализация данных)

1-- Создание таблицы:

2CREATE TABLE `books_statistics`

3(

4`total` INTEGER UNSIGNED NOT NULL,

5`given` INTEGER UNSIGNED NOT NULL,

9-- Инициализация данных:

10INSERT INTO `books_statistics`

14SELECT IFNULL(`total`, 0),

15IFNULL(`given`, 0),

16IFNULL(`total` - `given`, 0) AS `rest`

Внутри кода функции остаётся лишь написать запрос на обновление данных. Получить результат работы функции можно следующим запросом.

MySQL Решение 5.1.1.c (запрос для получения результата работы функции)

1 SELECT BOOKS_DELTA()

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 367/545

Пример 36: выборка и модификация данных с использованием хранимых функций

1DROP FUNCTION IF EXISTS BOOKS_DELTA;

2DELIMITER $$

3CREATE FUNCTION BOOKS_DELTA() RETURNS INT

4BEGIN

5DECLARE old_books_count INT DEFAULT 0;

6DECLARE new_books_count INT DEFAULT 0;

7

8 SET old_books_count := (SELECT `total` FROM `books_statistics`); 9

10UPDATE `books_statistics`

11JOIN

12(SELECT IFNULL(`total`, 0) AS `total`,

13IFNULL(`given`, 0) AS `given`,

14IFNULL(`total` - `given`, 0) AS `rest`

20AS `prepared_data`) AS `src`

21SET `books_statistics`.`total` = `src`.`total`,

22`books_statistics`.`given` = `src`.`given`,

23`books_statistics`.`rest` = `src`.`rest`;

24

25 SET new_books_count := (SELECT `total` FROM `books_statistics`); 26

27RETURN (new_books_count - old_books_count);

28END;

29$$

30DELIMITER ;

Решение для MySQL готово, а т.к. решения для MS SQL Server не существует, сразу переходим к Oracle, где полностью воспроизведём логику решения для MySQL.

Oracle Решение 5.1.1.c (создание таблицы и инициализация данных)

1-- Создание таблицы:

2CREATE TABLE "books_statistics"

3(

4"total" NUMBER(10),

5"given" NUMBER(10),

6"rest" NUMBER(10)

7);

8

9-- Инициализация данных:

10INSERT INTO "books_statistics"

14SELECT "total",

15"given",

16("total" - "given") AS "rest"

21WHERE "sb_is_active" = 'Y')

22ON 1 = 1;

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 368/545

Пример 36: выборка и модификация данных с использованием хранимых функций

1CREATE OR REPLACE FUNCTION BOOKS_DELTA RETURN NUMBER IS

2PRAGMA AUTONOMOUS_TRANSACTION;

3old_books_count NUMBER;

4new_books_count NUMBER;

5BEGIN

6SELECT "total" INTO old_books_count FROM "books_statistics";

7COMMIT;

8

9UPDATE "books_statistics"

10SET ("total", "given", "rest") =

11(SELECT "total",

12"given",

13("total" - "given") AS "rest"

18WHERE "sb_is_active" = 'Y')

19ON 1 = 1);

20COMMIT;

21

22SELECT "total" INTO new_books_count FROM "books_statistics";

23COMMIT;

24

25RETURN (new_books_count - old_books_count);

26END;

Поскольку у Oracle есть ряд ограничений на модификацию данных из кода хранимых функций, нам нужно реализовать две идеи:

•выполнять функцию в автономной транзакции (строка 2);

•подтверждать транзакцию в теле функции после выполнения каждого запроса (строки 7, 20, 23), т.к. в противном случае возникает ситуация взаимной блокировки между запросами на чтение и обновление данных).

Получить результат выполнения функции можно запросом.

Oracle Решение 5.1.1.c (запрос для получения результата работы функции)

1 SELECT BOOKS_DELTA FROM dual

На этом решение данной задачи завершено.

Задание 5.1.1.TSK.A: создать хранимую функцию, получающую на вход идентификатор читателя и возвращающую список идентификаторов книг, которые он уже прочитал и вернул в библиотеку.

Задание 5.1.1.TSK.B: создать хранимую функцию, возвращающую список первого диапазона свободных значений автоинкрементируемых первичных ключей в указанной таблице (например, если в таблице есть первичные ключи 1, 4, 8, то первый свободный диапазон — это значения 2 и 3).

Задание 5.1.1.TSK.C: дополнить решение{355} задачи 5.1.1.b{352} для Oracle двумя вариантами реализации хранимой функции, в которых:

•функция возвращает таблицу из одного поля, в котором хранится весь список значений свободных ключей;

•функция возвращает строку, в которой через запятую перечислены все значения свободных ключей.

Задание 5.1.1.TSK.D: создать хранимую функцию, актуализирующую данные в таблице subscriptions_ready (см. задачу 3.1.2.b{215}) и возвращающую число, показывающее изменение количества выдач книг.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 369/545