Автор: Стивен Ферстайн
В этой статье говорится о наилучших способах управления старым (old) и новым (new) значениями и о предотвращение ошибок FORALL.
Вопрос:
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, то получаем вот такое сообщение об ошибке:
ORA-22160: element at index [2750] does not exist
Как избежать этой ошибки и получить все наши добавленные записи?
Ответ:
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_tIS 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 предотвратит в этом случае проблему.
Использование инкапсуляции без привилегий
Вопрос:
И вот моя проблема: есть ещё другая практика - избегать закодированных объявлений и связывать переменные с таблицами базы данных и столбцами через %TYPE и %ROWTYPE. Однако мои люди не могут этого сделать, потому что я отобрал привилегии, а им нужна привилегия SELECT на таблицу для того, чтобы это сделать.
Что это за наилучше-практико-ориентированное чудище?
Ответ:
Я весьма поражён, узнав о вашем решении отобрать привилегии на таблицы. Это сложное для выполнения дело, но раз уж оно сделано, то трудоёмкость приложения может существенно увеличиться.
А ещё, раз уж об этом заговорили, такой подход приводит к интересному конфликту лучших практик.
Ниже показано, как решить эту проблему: я генерирую пакеты для 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 объявлений.