Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 37: контроль операций с данными с использованием хранимых функций
'ЬАЙ'
Решение 5.1.2.b{370}.
По сравнению с предыдущей задачей здесь всё будет ещё проще: нужно просто «обернуть» в функцию две проверки, на основе результата которых возвратить 1 или 0.
Только в решении для MS SQL Server будет одна необычная деталь: мы вынуждены использовать промежуточную переменную и возвращать её значение в самом конце тела функции, т.к. данная СУБД требует, чтобы последним выражением внутри хранимой функции был оператор RETURN.
MySQL |
Решение 5.1.2.b (код функции) [ |
|
|
1 |
DROP ... FUNCTION. IF |
EXISTS CHECK_SUBSCRIBER_NAME; |
|
2 |
DELIMITER $$ |
|
|
3 |
CREATE FUNCTION CHECK_SUBSCRIBER_NAME |
subscriber_name VARCHAR(150 ) |
|
4RETURNS
5INT DETERMINISTIC
6BEGIN
7IF ((CAST subscriber_name AS CHAR CHARACTER SET cp1251 REGEXP
8CAST('Л[a-zA-Za-яА-ЯёЁХ'-]+([лa-zA-Zа-яА-ЯёЁ\'-]+[a-zA-Za-HA-
9ЯёЁ\'.-]+){1,}$' AS CHAR CHARACTER SET cp1251)) = 0)
10 |
OR (LOCATE('.', |
subscriber_name) = 0) |
11THEN
12RETURN 0; ELSE
13RETURN 1; END IF;
14END; $$ DELIMITER ;
16MySQL Решение 5.1.2.b (код для проверки работы функции)
17 |
1 |
SELECT CHECK_SUBSCRIBER_NAME('Иванов'); |
|
2 |
SELECTCHECK_SUBSCRIBER_NAME('Иванов И' ); |
3 |
SELECT |
CHECK SUBSCRIBER NAME('Иванов И.'); |
MS SQL Решение 5.1.2.b (код функции)
1CREATE FUNCTION CHECK SUBSCRIBER NAME @subscriber name NVARCHAR 150))
2RETURNS INT
3WITH SCHEMABINDING
4AS
5BEGIN
6DECLARE @result INT = -1;
7
8IF ((CHARINDEX(' ', LTRIM(RTRIM @subscriber name )) = 0) OR
9(CHARINDEX('.', @subscriber name = 0 )
10BEGIN
11SET @result = 0;
12END
13ELSE
14BEGIN
15SET @result = 1;
16END;
17
18RETURN @result
19END;
20GO
MS SQL |
Решение 5.1.2.b (код для проверки работы функции) |
1SELECT dbo.CHECK_SUBSCRIBER_NAME('Иванов');
2SELECT dbo.CHECK_SUBSCRIBER_NAME('Иванов И' );
3SELECT dbo.CHECK SUBSCRIBER NAME('Иванов И.' );
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 395/545
Пример 37: контроль операций с данными с использованием хранимых функций |
|||||
Oracl |
і |
Решение 5.1.2.b (код функции) |
| |
|
|
e |
|
|
|||
|
|
|
|
|
|
1 |
CREATE OR REPLACE |
|
|
|
|
2 |
FUNCTION CHECK SUBSCRIBER NAME (subscriber name NVARCHAR2) |
||||
3 |
RETURN NUMBER DETERMINISTIC IS |
|
|
||
4 |
BEGIN |
|
|
|
|
5 |
IF ((NOT REGEXP LIKE subscriber name, 'Л[a-zA-Zа-яA-ЯёЁ''-]+([Лa-zA-Zа-яA- |
||||
6 |
ЯёЁ' '-^[a-zA-Za-яА-ЯёЁ' ' .-]+){1,}$')) |
|
|||
7 |
|
OR (INSTRC subscriber name |
'.', 1, 1 |
= 0)) |
|
8 |
|
THEN |
|
|
|
9 |
|
RETURN 0; |
|
|
|
10 |
|
ELSE |
|
|
|
11 |
|
RETURN 1; |
|
|
|
12 |
|
END IF; |
|
|
|
13 |
END; |
|
|
|
|
Oracle |
Решение 5.1.2.b (код для |
|
|
|
|
1 |
SELECT CHECK_SUBSCRIBER_NAME(N'Иванов') FROM dual; |
||||
2 |
SELECT CHECK_SUBSCRIBER_NAME(N'Иванов И') FROM dual; |
||||
3 |
SELECT CHECK SUBSCRIBER NAME(N'Иванов И.') FROM dual; |
||||
|
На этом решение данной задачи завершено. |
|
|||
Задание 5.1.2.TSK.A: переписать решения*315*, *338} задач 4.2.1.a*315} и 4.2.2. a*338} с использованием хранимых функций, созданных в решениях*37^ *373} задач 5.1.2.a*370} и 5.1.2.b*370} соответственно.
Задание 5.1.2.TSK.B: создать хранимую функцию, автоматизирующую &проверку условий задачи 4.2.1.b*315}, т.е. возвращающую 1, если у читателя на
руках сейчас менее десяти книг, и 0 в противном случае.
& Задание 5.1.2.TSK.C: создать хранимую функцию, автоматизирующую проверку условий задачи 4.2.2.b*338}, т.е. возвращающую 1, если книга издана менее ста лет назад, и 0 в противном случае.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 396/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
5.2. Использование хранимых процедур
5.2.1.Пример 38: выполнение динамических запросов с помощью хранимых процедур
ОЗадача 5.2.1.a{375}: создать хранимую процедуру, устраняющую промежутки в последовательности значений первичного ключа для заданной таблицы
(например, если значения первичного ключа были равны 4, 7, 9, то после выполнения хранимой процедуры они станут равны 1,2, 3).
□ Задача 5.2.1.b{382}: создать хранимую процедуру, формирующую список представлений, триггеров и внешних ключей для указанной таблицы.
Ожидаемый результат 5.2.1.a.
После выполнения хранимой процедуры, в которую первыми двумя параметрами передано имя обрабатываемой таблицы и её первичного ключа, значения первичного ключа в таблице принимают вид 1, 2, 3, ... (т.е. начинаются с 1 и идут без пропусков), а сама хранимая процедура возвращает информацию о том, сколько значений первичного ключа было изменено.
Ожидаемый результат 5.2.1.b.
После выполнения хранимой процедуры, в которую первым параметром передано имя обрабатываемой таблицы, формируется и возвращается результирующая таблица вида:
object_type |
object_name |
|
foreign_key |
FK_1 |
|
foreign key |
FK 2 |
|
trigger |
TRG_1 |
|
trigger |
TRG 2 |
|
view |
VIEW 1 |
|
view |
VIEW_2 |
|
|
'Vf Решение 5.2.1 .a{375}. |
|
Т радиционно начнём решение задачи с MySQL. В отличие от хранимых функций в хранимые процедуры данной СУБД позволяют формировать и выполнять динамические SQL-запросы.
Прежде, чем начать рассмотрение кода хранимой процедуры, сделаем два важных замечания:
•выполнять динамические запросы и помещать результаты их работы в переменные можно только с использованием т.н. «сессионных переменных20» (имена которых начинаются со знака @);
•имена переменных, в которые помещается результат выполнения запроса, не должны совпадать с именами параметров хранимой процедуры (и, в некоторых случаях, с именами полей, возвращаемых запросом21).
20http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference
21http://dba.stackexchange.com/questions/112285/select-into-variable-results-in-null-or-idk
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 397/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 399/545