Материал: Using_MySql,_MS_SQL_Server_and_Oracle
1 |
CREATE PROCEDURE COMPACT_KEYS table_name IN VARCHAR, pk_name IN VARCHAR, |
2 |
keys_changed OUT NUMBER) AS |
3empty_key_query VARCHAR(1000) :=
4max_key_query VARCHAR 1000) := '
5empty_key_value NUMBER := NULL;
6max_key_value NUMBER := NULL; update_key_query VARCHAR(1000) :=
8BEGIN
9keys_changed := 0;
DBMS_OUTPUT.PUT_LINE('Point 1. table_name = ' || table_name ||
12 |
' || pk_name = ' || pk_name || ', keys_changed = ' || keys_changed ; |
13 |
|
14empty_key_query :=
15'SELECT MIN("empty_key") AS "empty_key"
16 |
FROM (SELECT "left"."' |
|| pk_name |
|| '" |
+ 1 AS "empty_key" |
|
|||
17 |
FROM "' |
|| |
table_name |
|| |
'" "left" |
|
|
|
18 |
LEFT OUTER JOIN "' |
|| |
table_name |
|| |
'" |
|||
"right" |
|
|
|
|
|
|
|
|
19 |
|
|
ON "left"."' |
|| pk_name || |
|
|
||
20 |
|
|
'" |
+ 1 = "right"."' || pk_name |
|| '" |
|
||
21WHERE "right"."' || pk_name || '" IS NULL
22UNION
23SELECT 1 AS "empty_key"
24 |
|
FROM |
"' || table_name |
|| |
'" |
|
25 |
|
WHERE |
NOT EXISTS (SELECT "' || pk_name |
|| '" |
||
26 |
|
|
FROM |
"' || table_name || '" |
||
27 |
|
|
WHERE |
"' |
|| pk_name || |
|
28 |
|
|
'" = 1)) "prepared_data" |
|||
29 |
WHERE |
"empty_key" < (SELECT MAX("' || pk_name |
|| '") |
|||
30 |
|
|
FROM "' |
|| |
table_name |
|| '")'; |
31 |
|
|
|
|
|
|
32max_key_query : =
33'SELECT MAX("' || pk_name || '") FROM "' || table_name || '"';
34
35DBMS_OUTPUT.PUT_LINE('Point 2. empty_key_query = ' || empty_key_query ||
36CHR 13) || CHR 10) || ' max_key_query = ' || max_key_query ;
37
38LOOP
39EXECUTE IMMEDIATE empty_key_query INTO empty_key_value
40EXIT WHEN empty_key_value IS NULL;
41EXECUTE IMMEDIATE max_key_query INTO max_key_value
42update_key_query :=
43'UPDATE "' || table_name || '" SET "' || pk_name ||
44'" = ' || TO_CHAR empty_key_value) || ' WHERE "' || pk_name ||
45'" = ' || TO_CHAR max_key_value ;
46 |
DBMS_OUTPUT.PUT_LINE('Point 3. update_key_query = ' || update_key_query ; |
||
47 |
EXECUTE IMMEDIATE update_key_query; |
||
48 |
keys_changed |
:= |
keys_changed + 1; |
49END LOOP;
50END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 405/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
51 /
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 406/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
Получается, что единственные два отличия решения для Oracle от решения для MySQL состоят в способе вывода отладочной информации (строки 11-12, 3536, 46) и синтаксисе описания логики выхода из цикла (строка 40).
Проверить работоспособность полученного решения можно следующими запросами (не забудьте предварительно включить отображение получаемых от сервера сообщений запросом SET SERVEROUTPUT ON).
Oracle |
Решение 5.2.1.a (код для выполнения хранимой процедуры и получения результата её работы) |
1 DECLARE
2keys_changed_in_table NUMBER;
3BEGIN
4 |
COMPACT_KEYS('books', 'b_id', keys_changed_in_table ; |
|
|
5 |
DBMS_OUTPUT.PUT_LINE('Keys changed: ' |
|| keys_changed_in_table |
; |
6 |
|
|
|
|
COMPACT_KEYS('subscriptions', 'sb_id', keys_changed_in_table |
; |
|
|
DBMS_OUTPUT.PUT_LINE('Keys changed: |
' || |
|
|
|
keys_changed_in_table |
; |
9 |
END; |
|
|
На этом решение данной задачи завершено.
'IT Решение 5.2.1.b{375}.
Врешении{375} задачи 5.2.1.a{375} мы уже рассмотрели логику формирования
ивыполнения динамических SQL-запросов в хранимых процедурах. Отличие решения этой задачи будет в том, что результатом работы хранимой процедуры будет не изменение в БД и возвращение числа правок, а возвращение таблицы.
Решение для MySQL выглядит следующим образом.
MySQL I Решение 5.2.1.b (код процедуры) |
1DELIMITER $$
2CREATE PROCEDURE SHOW TABLE OBJECTS (IN table name
3BEGIN
4SET @query text = '
5 |
SELECT \'foreign key\' AS 'object type', |
|
6 |
|
'constraint name' AS 'object name' |
7 |
FROM |
'information schema'.'table constraints' |
8 |
WHERE |
'table schema' = DATABASE() |
9AND 'table name' = \' FP TABLE NAME PLACEHOLDER \'
10AND 'constraint type' = \'FOREIGN KEY\'
11UNION
12 |
SELECT \'trigger\' |
AS 'object_type', |
|
13 |
|
'trigger name' AS 'object name' |
|
14 |
FROM |
'information schema'.'triggers' |
|
15WHERE 'event object schema' = DATABASE()
16AND 'event object table' = \' FP TABLE NAME PLACEHOLDER \'
17UNION
18 |
SELECT \'view\' |
AS 'object type', |
|
19 |
|
'table name' AS 'object name' |
|
20 |
FROM |
'information schema'.'views' |
|
21WHERE 'table schema' = DATABASE()
22AND 'view definition' LIKE \'%' FP TABLE NAME PLACEHOLDER '%\'';
23SET @query text = REPLACE(@query text
24 |
' FP TABLE NAME PLACEHOLDER ', table name ; |
25 |
|
26PREPARE query stmt FROM @query text;
27EXECUTE query stmt
28DEALLOCATE PREPARE query stmt
29END;
30$$
31DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 407/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
Здесь (для разнообразия) текст итогового запроса мы получаем не с использованием функции CONCAT, а путём замены плейсхолдера _FP_TABLE_NAME_PLACEHOLDER_ на реальное имя таблицы в заранее подготовленном полном тексте запроса.
Логика же получения самого списка искомых объектов полностью тривиальная для внешних ключей и триггеров (см. текст запроса в строках 5-16), и только для представлений мы должны анализировать их исходный код, чтобы обнаружить упоминание там имени таблицы, полученной как параметр нашей процедуры (т.к. представления не ассоциируются напрямую с таблицами, а являются независимыми объектами).
Проверить работоспособность полученного решения можно следующим запросом.
MySQL |
Решение 5.2.1.b (код для выполнения хранимой процедуры и получения результата её работы) |
1 .. CALL
SHOW TABLE OBJECTS('subscriptions')
На этом решение для MySQL завершено.
Переходим к MS SQL Server. Здесь логика решения полностью совпадает с решением для MySQL, за исключением того факта, что информацию о триггерах
приходится |
извлекать |
из |
[sys].[triggers] |
как |
аналога |
'information schema'.'triggers'. |
|
|
|
||
MS SQL Решение 5.2.1.b (код процедуры)
1 |
CREATE PROCEDURE SHOW TABLE OBJECTS |
2 |
@table name NVARCHAR 150 |
3WITH EXECUTE AS OWNER
4AS
5 |
DECLARE @query text NVARCHAR 1000 = ''; |
|
6 |
SET @query text = |
|
7 |
'SELECT ''foreign key'' AS [object type], |
|
8 |
|
[constraint name] AS [object name] |
9 |
FROM |
[information schema].[table constraints] |
10WHERE [table catalog] = DB NAME()
11AND [table name] = '' FP TABLE NAME PLACEHOLDER ''
12AND [constraint type] = ''FOREIGN KEY''
13UNION
14SELECT ''trigger'' AS [object type],
15 |
|
[name] |
AS [object name] |
16 |
FROM |
[sys].[triggers] |
|
17WHERE OBJECT NAME([parent id]) = '' FP TABLE NAME PLACEHOLDER ''
18UNION
19 |
SELECT ''view'' |
AS [object type], |
|
20 |
|
[table name] AS [object name] |
|
21 |
FROM |
[information schema].[views] |
|
22WHERE [table catalog] = DB NAME()
23AND [view definition] LIKE ''%[ FP TABLE NAME PLACEHOLDER ]%''';
25SET @query text = REPLACE(@query text ' FP TABLE NAME PLACEHOLDER ',
26 |
@table name); |
27 |
|
28EXECUTE sp.executesql @query_text
29GO
Проверить работоспособность полученного решения можно следующим запросом.
MS SQL |
Решение 5.2.1.b (код для выполнения хранимой процедуры и получения результата её работы) |
|||
1 |
..... EXECUTE .... |
SHOW ... |
TABLE.................................... |
OBJECTS |
'subscriptions'"; ..
На этом решение для MS SQL Server завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 408/545
Пример 38: выполнение динамических запросов с помощью хранимых процедур
Переходим к Oracle. И вот здесь уже появятся радикальные отличия. В основе решения всё равно будет лежать тот же запрос, который мы использовали для MySQL и MS SQL Server, но в Oracle существует одна очень неприятная проблема, усложняющая решение в разы.
Текст представления (в котором мы ищем упоминание интересующей нас таблицы) хранится в поле типа LONG (это — не «длинное целое», это устаревший, но всё ещё используемый иногда текстовый тип данных22), и данные этого типа невозможно ни использовать в выражениях типа LIKE, ни преобразовать простым способом к другому типу (например, VARCHAR2).
Единственный более-менее адекватный способ извлечения LONG-данных с конвертацией к VARCHAR2 — создание хранимой функции. Существует универсаль-
ное решение23, но для простоты мы реализуем вариант, привязанный к конкретному источнику данных.
В представленном ниже коде мы создаём хранимую функцию, возвращающую таблицу (принцип создания и использования таких функций рассмотрен в решении{355} задачи 5.1.1.b{352}). Ключевая идея здесь состоит в том, что поле TEXT объекта all_views_row объявлено как VARCHAR2, и именно такой тип данных будет в выходной таблице. А с V.ARCHAR2-Данными уже можно выполнять операции сравнения.
Вданном конкретном случае функция сначала готовит полный набор данных,
изатем возвращает его в виде сформированной таблицы, а PiPLiNED-решение
будет представлено далее.
Oracle I |
Решение 5.2.1.b (код функции для преобразования LONG в VARCHAR2) |
| |
1CREATE OR REPLACE TYPE "all views row" AS OBJECT
2(
3"VIEW NAME" VARCHAR2(500 ,
4 "TEXT" VARCHAR2(32767)
5);
6/
7CREATE TYPE "all views table" IS TABLE OF "all views row";
8/
9
10CREATE OR REPLACE FUNCTION ALL VIEWS VARCHAR2
11RETURN "all views table"
12AS
13result table "all views table" := "all views table"();
14CURSOR all views table cursor IS
15SELECT VIEW NAME,
16TEXT
17FROM ALL VIEWS
18WHERE OWNER = USER;
19BEGIN
20FOR one row IN all views table cursor
21LOOP
22result table extend;
23result table result table last) :=
24 |
"all views row" one row "VIEW NAME" one row "TEXT" ; |
25END LOOP;
26RETURN result table;
27END;
28/
Теперь, когда проблема с применением выражения LIKE к тексту представ-
22https://docs.oracle.eom/cd/E11882_01/appdev.112/e25519/datatypes.htm#LNPLS346
23https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:839298816582
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 409/545