(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

О старом и новом значениях и сообщении ORA-22160

Источник: oracle
Стивен Ферстайн

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

В этой статье говорится о наилучших способах управления старым (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-таблицы для каждой таблицы:

  • Пакет для изменений, который включает все основные операции INSERT, UPDATE и DELETE
  • Пакет для запросов, который позволяет использовать широкий диапазон функций для запроса данных из таблицы
  • Пакет типов, который создает наборы подтипов, которые по существу скрывают объявления %TYPE и %ROWTYPE и гарантируют, что мне никогда не потребуются права на SELECT для таблиц для построения кода высокого качества.

Как это работает? Просто.

Предположим, я строю приложение для поддержки категорий вещей, которые продаёт моя компания. Одна из таблиц, 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 объявлений.

Ссылки по теме


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 16.09.2009 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Standard Edition 2 Processor License
Oracle Database Personal Edition Named User Plus License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Database Standard Edition 2 Named User Plus License
Allround Automation PL/SQL Developer - Annual Service Contract - 5 users
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Компьютерный дизайн - Все графические редакторы
СУБД Oracle "с нуля"
Программирование на Visual Basic/Visual Studio и ASP/ASP.NET
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100