Про старому і новому значеннях і повідомленні ORA-22160, Інші СУБД, Бази даних, статті

Автор: Стівен Ферстайн


У цій статті йдеться про найкращі способи управління старим (old) і новим (new) значеннями і про запобігання помилок FORALL.

Питання: Я хочу вести аудит змін у таблиці, зберігаючи для кожного стовпця таблиці значення “до” і “після”. Для цього мені зручно було б передавати значення: NEW і: OLD як аргументи процедур. Однак цей спосіб не хоче працювати. Я хотів би уникнути кодування назв стовпців, тому що виникнуть помилки компіляції при додаванні, видаленні або перейменування стовпців. Чи можливо це? Зараз я роблю так:

CREATE TABLE load_a (
a1 VARCHAR2 (10), a2 VARCHAR2 (10) )
/
CREATE OR REPLACE TRIGGER ins_load_a
AFTER UPDATE
ON scott.load_a
FOR EACH ROW
DECLARE
a_rec scott.load_a%ROWTYPE;
BEGIN
a_rec.a1 := :OLD.a1;
a_rec.a2 := :OLD.a2;
save_old_values (a_rec);
END;
/

Відповідь: Погано те, що аж до Oracle Database 10 g не можна передавати: OLD і: NEW як аргументи процедур. Добре те, що, по крайней мере, для цього не потрібно писати весь код.

Давайте подивимося ще раз: ви хочете передати старе і нове значення, доступні в тригері, збереженої процедури, щоб обробити дані (записати інформацію в журнальну таблицю, виконати перевірку коректності значень та ін.) Не можна передати: OLD і: NEW у вигляді запису. Замість цього необхідно скопіювати окремі поля цієї псевдозапісі в “справжню” запис, який вже може передаватися процедурою.

Звичайно, утомливо і затратно по часу писати сам цей код, особливо для таблиць, в яких багато стовпців. На щастя, набагато легше бажаний код згенерувати.

Щоб допомогти вам в цьому, я розробив процедуру gen_audit_trigger_text, показану в лістингу 1. Я запускаю цю програму для таблиці employees і після деякого форматування отримую результат, показаний в Лістингу 2.

Лістинг 1: gen_audit_trigger_text

CREATE OR REPLACE PROCEDURE gen_audit_trigger_text (
table_in IN VARCHAR2
, owner_in IN VARCHAR2 := USER
, program_name_in IN VARCHAR2 := “process_data”
)
IS
c_rowtype CONSTANT VARCHAR2 (100) := table_in // “%ROWTYPE”;
l_columns DBMS_SQL.varchar2s;
PROCEDURE gen_copy_proc (old_or_new_in IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line ( “FUNCTION copy_”
// old_or_new_in
// ” RETURN ”
// c_rowtype
// ” IS l_return ”
// c_rowtype
// “; BEGIN ”
);
FOR indx IN 1.. l_columns.COUNT
LOOP
DBMS_OUTPUT.put_line ( ” l_return.”
// l_columns (indx)
// ” := ”
// “:”
// old_or_new_in
// “.”
// l_columns (indx)
// “;”
);
END LOOP;
DBMS_OUTPUT.put_line (“RETURN l_return;”);
DBMS_OUTPUT.put_line (“END copy_” // old_or_new_in // “;”);
END gen_copy_proc;
BEGIN
SELECT LOWER (column_name) column_name
BULK COLLECT INTO l_columns
FROM all_tab_columns
WHERE owner = UPPER (owner_in) AND table_name = UPPER (table_in);
DBMS_OUTPUT.put_line (“DECLARE”);
DBMS_OUTPUT.put_line (” my_Old ” // table_in // “%ROWTYPE;”);
DBMS_OUTPUT.put_line (” my_New ” // table_in // “%ROWTYPE;”);
gen_copy_proc (“old”);
gen_copy_proc (“new”);
DBMS_OUTPUT.put_line (“BEGIN”);
DBMS_OUTPUT.put_line (” my_Old := copy_Old ();”);
DBMS_OUTPUT.put_line (” my_New := copy_New ();”);
DBMS_OUTPUT.put_line (” ” // program_name_in // “(my_Old, my_new);”);
DBMS_OUTPUT.put_line (“END;”);
END gen_audit_trigger_text;
/

Лістинг 2: Результат роботи процедури gen_audit_trigger_text для таблиці employees

DECLARE
my_old employees%ROWTYPE;
my_new employees%ROWTYPE;
FUNCTION copy_old
RETURN employees%ROWTYPE
IS
l_return employees%ROWTYPE;
BEGIN
l_return.employee_id := :OLD.employee_id;
l_return.first_name := :OLD.first_name;
l_return.last_name := :OLD.last_name;
l_return.email := :OLD.email;
l_return.phone_number := :OLD.phone_number;
l_return.hire_date := :OLD.hire_date;
l_return.job_id := :OLD.job_id;
l_return.salary := :OLD.salary;
l_return.commission_pct := :OLD.commission_pct;
l_return.manager_id := :OLD.manager_id;
l_return.department_id := :OLD.department_id;
RETURN l_return;
END copy_old;
FUNCTION copy_new
RETURN employees%ROWTYPE
IS
l_return employees%ROWTYPE;
BEGIN
l_return.employee_id := :NEW.employee_id;
l_return.first_name := :NEW.first_name;
l_return.last_name := :NEW.last_name;
l_return.email := :NEW.email;
l_return.phone_number := :NEW.phone_number;
l_return.hire_date := :NEW.hire_date;
l_return.job_id := :NEW.job_id;
l_return.salary := :NEW.salary;
l_return.commission_pct := :NEW.commission_pct;
l_return.manager_id := :NEW.manager_id;
l_return.department_id := :NEW.department_id;
RETURN l_return;
END copy_new;
BEGIN
my_old := copy_old ();
my_new := copy_new ();
process_data (my_old, my_new);
END;

Ви легко можете покращити gen_audit_trigger_text, щоб також був згенерований заголовок тригера. Я пропоную це завдання читачам як вправи.

Помилка в FORALL?

Питання: Ми були вражені, наскільки краще наші програми виконуються при використанні FORALL для виконання вставок і змін даних. Тепер ми розробляємо наш додаток на Oracle Database 10 g Release 2, але у нас виникла проблема. У всіх попередніх випадках використання FORALL ми зазвичай використовували колекцію, яка заповнювалася оператором BULK COLLECT і містилася в одну або кілька таблиць.

Тепер у нас більш складний сценарій, в якому необхідно переглянути колекцію з “кандидатами” на вставку і видалити зі списку деякі (а може, і все) рядки перед виконанням вставки. Коли ми намагаємося використовувати FORALL, то отримуємо ось таке повідомлення про помилку:

ORA-22160: element at index [2750] does not exist

Як уникнути цієї помилки і отримати всі наші додані записи?

Відповідь: Я вважаю, що FORALL – чудове, одне з найбільш істотних удосконалень в PL / SQL, починаючи з Oracle8 i. Озираючись на Oracle8 i і Oracle9 i Database, FORALL використовувався в них тільки в такій формі:

FORALL index_variable
IN low_value.. high_value
<DML_Statement>;

І як в “звичайному” числовому циклі FOR, FORALL итеративно проходить по кожному числу між low_value і high_value, використовуючи це число для ідентифікації елемента у всій колекції, яка пов’язана з DML-пропозицією для index_variable. Якщо не існує жодного елемента у відповідному індексному значенні, Oracle Database генерує виняток, як можна бачити в Лістингу 3.

Лістинг 3: Генерування ORA-22160

SQL> DECLARE
2 TYPE list_of_names_t IS TABLE OF VARCHAR2 (32767)
3 INDEX BY PLS_INTEGER;
4
5 happyfamily list_of_names_t;
6 BEGIN
7 happyfamily (1) := “Eli”;
8 happyfamily (2) := “Chris”;
9 happyfamily (3) := “Veva”;
10 happyfamily (5) := “Steven”;
11 FORALL indx IN happyfamily.FIRST.. happyfamily.LAST
12 INSERT INTO first_names
13 VALUES (happyfamily (indx));
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-22160: element at index [4] does not exist

Іншими словами, FORALL вимагає послідовно і щільно заповненою колекції. Тому, якщо ви все ще використовуєте Oracle8 i або Oracle9 i Database і хочете обробити цю ситуацію, то вам необхідно скопіювати дані з колекції з пропущеними значеннями в неї ж без пропусків. З точки зору продуктивності, тут немає про що хвилюватися; маніпуляції з колекцією дуже швидкі. Однак це зажадає написання і налагодження порівняно більшого коду. В Oracle Database 10 g в пропозицію FORALL були додані два нових оператора INDICES OF і VALUES OF. Вони дозволяють уникнути обмеження використання колекцій з пропущеними значеннями. Замість використання діапазону значень в операторі IN, можна звернутися до колекції (зазвичай, але не обов’язково, до тієї ж самої колекції, яка обробляється в DML-реченні FORALL), і сказати, наприклад, “Використовуй тільки ті значення індексу, що визначені в цій іншій колекції “(INDICES OF) або” Використовуй тільки ті значення індексу, які знаходяться в елементах цієї іншої колекції “(VALUES OF).

Нижче показаний переписаний код лістингу 3, який запобігає помилку ORA-22160:

DECLARE
TYPE list_of_names_t

IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;
happyfamily list_of_names_t;
BEGIN
happyfamily (1) := “Eli”;
happyfamily (2) := “Chris”;
happyfamily (3) := “Veva”;
happyfamily (5) := “Steven”;
FORALL indx
IN INDICES OF happyfamily
INSERT INTO first_names
VALUES (happyfamily (indx));
END;
/


Це приклад найпростішого способу застосування INDICES OF: “самопосилання” на ту ж колекцію, що використовується в DML-пропозиції, щоб легко уникнути помилок через перепусток.

Тепер давайте розглянемо VALUES OF. Цей оператор стає дуже корисним, коли необхідно використовувати тільки підмножину колекції, зазначеної в DML-пропозиції.

Припустимо, наприклад, що у мене є процедура, параметр якої це колекція записів про співробітників і необхідно вставити тільки ті записи про співробітників, зарплата яких $ 10,000 і більше. Лістинг 4 містить специфікацію пакета і тіло для програми employees_dml.

Лістинг 4: Пакет і тіло пакета employees_dml

CREATE OR REPLACE PACKAGE employees_dml
IS
TYPE employees_aat IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
PROCEDURE insert_some (employees_in IN employees_aat);
END employees_dml;
/
SQL>CREATE OR REPLACE PACKAGE BODY employees_dml
2 IS
3 PROCEDURE insert_some (employees_in IN employees_aat)
4 IS
5 TYPE index_values_aat IS TABLE OF PLS_INTEGER
6 INDEX BY PLS_INTEGER;
7
8 l_values_of index_values_aat;
9 l_index PLS_INTEGER;
10 BEGIN 11 – Вставляємо тільки тих співробітників, зарплата яких> = 10000.
12 l_index := employees_in.FIRST;
13
14 WHILE (l_index IS NOT NULL)
15 LOOP
16 IF employees_in (l_index).salary >= 10000
17 THEN
18 l_values_of (l_values_of.COUNT + 1) := l_index;
19 END IF;
20
21 l_index := employees_in.NEXT (l_index);
22 END LOOP;
23
24 FORALL indx IN VALUES OF l_values_of
25 INSERT INTO employees
26 VALUES employees_in (indx);
27 END insert_some;
28 END employees_dml;

Рядки з 5 по 9 на Лістингу 4 оголошують колекцію VALUES OF, як колекцію значень типу PLS_INTEGER. Потім у циклі WHILE (рядки з 14 по 22), заповнюється запис l_values_of значеннями індексів для employees_in, тільки в тому випадку, якщо зарплата в цьому записі як мінімум $ 10,000.

Таким чином, коли викликається пропозицію FORALL (рядки з 24 по 26), оператор VALUES OF забезпечує, що всі інші записи про співробітників будуть ігноруватися.

Якщо у вас є стандартна таблиця Oracle employees, встановлена ​​з даними за замовчуванням, ви можете запустити скрипт лістингу 5, щоб перевірити поведінку пакета employees_dml.

Лістинг 5: Перевірка поведінки пакета employees_dml

SELECT COUNT(*)
FROM employees
WHERE salary < 10000
/
DECLARE
l_employees employees_dml.employees_aat;
BEGIN
SELECT *
BULK COLLECT INTO l_employees
FROM employees;
DELETE FROM employees;
employees_dml.insert_some (l_employees);
END;
/
SELECT COUNT(*)
FROM employees
WHERE salary < 10000
/
ROLLBACK
/
COUNT(*)
————————-
88
1 row selected.
PL/SQL procedure successfully completed.
 
COUNT(*)
————————-
0
1 row selected.
Rollback complete.

І, нарешті, ви також можете використовувати INDICES OF з абсолютно іншою колекцією, яка служить таким собі фільтром для колекцій, що використовуються в DML-пропозиції. Лістинг 6 показує приклад такого застосування.

Лістинг 6: Використання INDICES OF як фільтра

SQL> DECLARE
2 TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
3 INDEX BY PLS_INTEGER;
4
5 l_employees employee_aat;
6
7 TYPE boolean_aat IS TABLE OF Boolean
8 INDEX BY PLS_INTEGER;
9
10 l_employee_indices boolean_aat;
11 BEGIN
12 l_employees (1) := 137;
13 l_employees (100) := 126;
14 l_employees (500) := 147;
15 —
16 l_employee_indices (1) := false;
17 l_employee_indices (500) := TRUE;
18 l_employee_indices (799) := null;
19 —
21 BETWEEN 1 AND 500
22 UPDATE employees
23 SET salary = 10000
24 WHERE employee_id = l_employees (l_index);
25 END;

У цьому коді я використав індексні значення окремих елементів колекції l_employee_indices для визначення, які елементи колекції l_employees collection слід використовувати в реченні update. Зауважте, що в рядку 21 лістингу 6 я поставив оператор BETWEEN, щоб обмежити використовувані індексні значення l_employee_indice. Тому INDICES OF запобіжить в цьому випадку проблему.

Використання інкапсуляції без привілеїв

Питання: Я прийняв близько до серця рекомендацію про написання SQL-пропозицій (не писати SQL на рівні коду програми; приховувати їх у пакетованих API зі стільком кількістю згенерованих пакетів, скільки це можливо). Я також вирішив (а я глава команди, тому моє рішення має деякий вага) піти до кінця і відібрав привілеї на таблиці, тому мої розробники не мають вибору і змушені використовувати інкапсульовані пакети.

І ось моя проблема: є ще інша практика – уникати закодованих оголошень і зв’язувати змінні з таблицями бази даних і стовпцями через% TYPE та% ROWTYPE. Однак мої люди не можуть цього зробити, бо я відібрав привілеї, а їм потрібна привілей SELECT на таблицю для того, щоб це зробити.

Що це за найкраще-практико-орієнтований чудовисько?

Відповідь: Отже, приємно чути, що ви маєте намір інкапсулювати виклики! Я сам використовував їх декілька останніх років в кожному зі своїх технічних проектах, і не уявляю, як можна повернути “старий спосіб” написання всіх SQL-пропозицій кожен раз, коли вони потрібні.

Я дуже вражений, дізнавшись про ваше рішення відібрати привілеї на таблиці. Це складне для виконання справа, але якщо вже воно зроблено, то трудомісткість програми може істотно збільшитися.

А ще, якщо вже про це заговорили, такий підхід призводить до цікавого конфлікту кращих практик.

Нижче показано, як вирішити цю проблему: я генеруючи пакети для API-таблиці для кожної таблиці:


Як це працює? Просто.

Припустимо, я строю додаток для підтримки категорій речей, які продає моя компанія. Одна з таблиць, cat_tools, містить інформацію про ці речі. У таблиці є назва, опис і universal_id (Первинний ключ, описаний як GUID, тобто глобальний унікальний ідентифікатор). Лістинг 7 включає частину типів пакета для цієї таблиці.

Лістинг 7: Створення пакета типів як APIV

CREATE OR REPLACE PACKAGE cat_tools_tp
IS
SUBTYPE cat_tools_rt IS cat_tools%ROWTYPE;
SUBTYPE universal_id_t IS cat_tools.universal_id%TYPE;
SUBTYPE name_t IS cat_tools.NAME%TYPE;
SUBTYPE description_t IS cat_tools.description%TYPE;
TYPE table_refcur IS REF CURSOR
RETURN cat_tools%ROWTYPE;
TYPE cat_tools_tc IS TABLE OF cat_tools%ROWTYPE
INDEX BY PLS_INTEGER;
TYPE universal_id_cc IS TABLE OF cat_tools.universal_id%TYPE
INDEX BY BINARY_INTEGER;
END cat_tools_tp;
/

А тепер припустимо, що пакети таблиці cat_tools, cat_tools_tp (для типів) і cat_tools_qp (для запитів), визначені в схемі CATALOG. Я видаю EXECUTE на cat_tools_tp і cat_tools_qp користувачеві HR. Потім у схемі HR я можу написати код, показаний в лістингу 8.

Лістинг 8: Доступ з використанням пакетів типів і запитів

DECLARE / * Строкова мінлива для назви інструменту * /
l_name CATALOG.cat_tools_tp.name_t;
/ * Колекція для зберігання набору рядків про інструменти. * /
l_tools CATALOG.cat_tools_tp.cat_tools_tc;
BEGIN / * Функція allrows запитує всі рядки з cat_tools. * /
l_tools := CATALOG.cat_tools_qp.allrows;
/ * Прив’язка кожної назви в колекції до локальної змінної. * /
FOR indx IN 1.. l_tools.COUNT
LOOP
l_name := l_tools (indx).NAME;
END LOOP;
END;
/

Як бачите, навіть через схему HR немає доступу до таблиці cat_tools, я зміг оголосити змінні з використанням типів даних, які прив’язані (опосередковано) до таблиці та її стовпцями. Я можу також запросити вміст таблиці.

І якщо таблиця змінюється, пакети типів будуть також змінюватися (їх треба буде перегенеріровать). Потім всі програми, які посилаються на цей пакет, повинні будуть перекомпіліровать, і, таким чином, виходять такі ж залежності від% ROWTYPE та% TYPE оголошень.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*