Книги, научные публикации
Pages: | 1 | ... | 2 | 3 | 4 | 5 | 6 | ... | 7 | ОФИЦИАЛЬНОЕ АВТОРИЗОВАННОЕ ИЗДАНИЕ ORACLE PRESSЩ ЭКСКЛЮЗИВНЫЕ ПРАВА ПРИНАДЛЕЖАТ ИЗДАТЕЛЬСТВУ OSBORNE Программирование на языке PL/SQL Разработка эффективных приложений )мощью PL/SQL OFFICIAL Х Oracle ...
-- [ Страница 4 ] --В процедуре Register функция исполь зуется в трех случаях. Сначала процедура определяет текущее число сту зарегистрированных в группе, при помощи первого оператора Если этот оператор возвращает NO_DATA_FOUND, то управление программой передается обработчику исключительной ситуации, и применяется для уведомления пользователя о том, что группа не существует. Если же группа существует, процедура ряет, имеется ли свободное место для нового студента. Если свободного мес та нет, вновь применяется функция чтобы сообщить пользователю об отсутствии свободного места. И, наконец, если свободное место есть, проверяется, не зарегистрирован ли студент в группе, это делается с помощью второго оператора SELECT... INTO.
Если все три проверки проходят успешно, студент вносится в список группы путем обновления таблиц registered_students, students и>
Следующий сеанс демонстрирует поведение процедуры Regis ter и инициируемые ошибки. Предполагается, что таблицы находятся в своем исходном состоянии (созданы сценарием -- Этот пример содержится в файле SQL> -- Примеры ошибок ORA-2001 и ORA- SQL> exec Register(10000, ' CS', 999);
BEGIN 999);
END;
ERROR at line CS 999 doesn't exist ORA-06512: at line ORA-06512: at line SQL> exec 102);
BEGIN Register(10000, 102);
END;
, ERROR at line ORA-20002: Student 10000 is already registered for CS ORA-06512: at "DEMO. REGISTER", line at line SQL> -- Зарегистрируем двух студентов в MUS 410, что породит ORA- SQL> exec 410);
PL/SQL procedure successfully completed.
SQL> exec 410);
BEGIN 410);
END;
ERROR at line Can't add more students to MUS ORA-06512: at "DEMO. REGISTER", line at line 246 Сравним эти выходные данные со следующим примером анонимного который порождает исключение Ч Этот пример содержится в файле SQL> BEGIN 2 RAISE 3 END;
4 / BEGIN * ERROR at line no data found at line Формат выходных данных одинаков Ч номер ошибки Oracle и связан ный с ней текст. Отметим, что в обоих случаях выдается сообщение указывающее строку, которая вызвала ошибку. Поэтому APPLICATION_ERROR может использоваться для возврата условий ошибки пользователю в виде, согласованном с другими ошибками Oracle.
Это очень полезно, так как не требуется никакой специальной обработки определенных пользователем ошибок по сравнению со стандартными ошибками.
Распространение исключительных ситуаций Исключительные ситуации могут возникать в разделе объявлений, в вы полняемом разделе или в разделе исключительных ситуаций блока PL/SQL. Выше было показано, что происходит в том случае, когда иск лючительная ситуация инициируется в выполняемом разделе блока и при этом имеется соответствующий обработчик. Но что произойдет, если об работчик отсутствует или исключительная ситуация возникает в другом разделе? Для ответа на этот вопрос следует рассмотреть процесс, называ емый передачей (распространением) ситуаций (exception propagation).
Исключительные возникающие в выполняемом разделе Если исключительная ситуация возникает в выполняемом разделе блока PL/SQL, то для определения обработчика, который должен быть вызван, используется следующий алгоритм:
Если в текущем блоке имеется обработчик данной исключительной ситуации, он выполняется, блок успешно завершается и управление программой передается вышестоящему блоку.
2. Если обработчик отсутствует, исключительная ситуация передается в вышестоящий блок и инициируется там. После этого в вышестоя щем блоке выполняется шаг Если вышестоящего блока не сущест вует, исключительная ситуация будет передана вызывающей среде, такой как Обработка ошибок Прежде всего дадим определение вышестоящему, или охватывающему, блоку (enclosing block). Блок может быть частью другого блока, т.е. внеш ний блок может включать в свой состав внутренний блок. Рассмотрим пример:
DECLARE - Начало внешнего блока.
BEGIN DECLARE - Начало внутреннего блока 1, который встроен во внешний блок.
BEGIN END;
BEGIN - Начало внутреннего блока 2, который также встроен во - внешний блок. Обратите внимание, что в этом блоке - отсутствует раздел объявлений.
END;
- Конец внешнего блока.
END;
В данном случае внешний блок охватывает два внутренних блока. Лю бая исключительная ситуация, не обработанная в блоках 1 и 2, будет пе редана во внешний блок.
При вызове процедуры также может создаваться охватывающий блок, например:
BEGIN - Начало внешнего блока.
- Вызов процедуры. Внешний блок охватывает эту процедуру.
EXCEPTION WHEN OTHERS THEN - Любое исключение, порожденное F, будет перехватываться здесь.
END;
Если процедура F порождает исключительную ситуацию, которая не обрабатывается, то эта исключительная ситуация передается во внешний блок, охватывающий данную процедуру.
Различные варианты работы алгоритма передачи исключительных си туаций демонстрируются в примерах 1, 2 и 3.
Пример В этом примере показано применение правила Исключительная ситуа ция А порождается и обрабатывается во внутреннем блоке. После этого управление программой передается внешнему блоку.
DECLARE A EXCEPTION;
BEGIN Исключительная ситуация А порождается во внутреннем блоке.
BEGIN RAISE A;
EXCEPTION А обрабатывается также WHEN A THEN в этом блоке.
END;
END;
Сюда передается управление программой.
Пример В этом примере показано применение правила 2 для внутреннего блока.
Исключительная ситуация передается охватывающему блоку;
после этого применяется правило 1. Затем охватывающий блок успешно выполняется.
DECLARE A EXCEPTION;
В EXCEPTION;
Исключительная ситуация В порождается во внутреннем блоке.
BEGIN BEGIN RAISE В;
Обработчика исключительной EXCEPTION ситуации В в этом блоке нет.
WHEN A THEN END;
EXCEPTION Исключительная ситуация В WHEN В THEN передается охватывающему блоку и обрабатывается в нем.
END;
После этого управление программой выходит из охватывающего блока, который успешно выполняется.
Обработка ошибок Пример В этом примере правило 2 вновь применяется для внутреннего блока.
Исключительная ситуация передается охватывающему блоку, в котором отсутствует обработчик этой исключительной ситуации. Правило 2 при меняется повторно, и охватывающий блок завершается неуспешно, так как исключительная ситуация не обработана.
DECLARE A EXCEPTION;
В EXCEPTION;
Исключительная ситуация С С EXCEPTION;
устанавливается во внутреннем блоке.
BEGIN BEGIN RAISE С;
EXCEPTION Обработчика WHEN A THEN ситуации С в этом блоке нет.
END;
Исключительная ситуация С передается охватывающему блоку, EXCEPTION но здесь также отсутствует обработчик WHEN В THEN этой исключительной ситуации.
END;
После этого исключительная ситуация передается в вызывающую среду.
Охватывающий блок завершается с необработанной исключительной ситуацией.
Исключительные порождаемые в разделе объявлений Если в операции присваивания раздела объявлений возникает исключи тельная ситуация, она немедленно передается охватывающему блоку.
Здесь используются правила передачи исключительной ситуации, сфор мулированные в предыдущем разделе. Даже если в текущем блоке имеет ся обработчик этой исключительной он не выполняется.
Рассмотрим примеры 4 и 5.
Пример В этом примере порождается исключительная ситуация в объявлении := 250 ГЛАВА Она немедленно передается охватывающему блоку. Хотя в текущем блоке имеется обработчик OTHERS, он не выполняется. Если бы для дан ного блока существовал внешний блок, то исключительная ситуация была бы перехвачена в этом внешнем блоке (см. пример 5).
Неверная операция присваивания приводит к порождению исключительной DECLARE ситуации VALUE_ERROR.
NUMBER (3) = BEGIN Хотя существует обработчик OTHERS, он не выполняется.
EXCEPTION WHEN OTHERS THEN Блок завершается неуспешно END;
с исключительной ситуацией VALUE ERROR.
Пример Как и в примере 4, в разделе объявлений внутреннего блока порождается исключительная ситуация VALUE_ERROR. Она сразу же передается внешнему блоку, в котором существует обработчик OTHERS, поэтому исключительная ситуация обрабатывается, и внешний блок выполняется успешно.
Неверная операция присваивания порождает BEGIN исключительную ситуацию DECLARE VALUE ERROR.
NUMBER (3) := BEGIN Хотя во внутреннем блоке существует обработчик он не выполняется.
EXCEPTION WHEN OTHERS THEN END;
Обработчик во блоке обрабатывает эту EXCEPTION исключительную ситуацию.
WHEN OTHERS THEN После этого управление END;
программой выходит из охватывающего блока, который успешно выполняется.
Обработка ошибок Исключительные ситуации, порождаемые в разделе исключительных ситуаций Исключительные ситуации могут порождаться и в обработчиках исключи тельных ситуаций либо явно, посредством оператора RAISE, либо неявно при ошибке выполнения программы. В любом случае исключительная си туация немедленно передается охватывающему блоку, как случае раздела объявлений блока. Это происходит потому, что в разделе исключи тельных ситуаций в каждый конкретный момент времени активной может быть лишь одна исключительная ситуация. Пока она обрабатывается, мо жет возникнуть другая, однако наличие одновременно нескольких исклю чительных ситуаций недопустимо. Рассмотрим примеры 6, 7 и 8.
Пример В этом примере исключительная ситуация А устанавливается, а затем об рабатывается. Однако в обработчике исключительной ситуации А гене рируется еще одна исключительная ситуация Ч В. Она немедленно передается внешнему блоку, минуя свой обработчик. Как и в примере 5, если бы этот блок был заключен во внешний блок, то внешний блок пере хватил бы исключительную ситуацию В (см. пример 7).
DECLARE Устанавливается A EXCEPTION;
исключительная ситуация А.
В EXCEPTION;
BEGIN Исключительная ситуация А RAISE обрабатывается, а в ее обработчике EXCEPTION порождается исключительная ситуация В.
WHEN A THEN RAISE В WHEN В THEN Хотя здесь существует обработчик В, он не выполняется. Исключительная ситуация передается вовне блока.
END;
Блок заканчивается неуспешно с необработанной исключительной ситуацией В.
Пример Как и в примере в обработчике исключительной ситуации А устанавли вается исключительная ситуация В. Она немедленно передается охваты вающему блоку, минуя внутренний обработчик В. Внешний блок обрабатывает исключительную ситуацию В и успешно ?авершается.
ГЛАВА Устанавливается BEGIN исключительная ситуация А.
DECLARE A EXCEPTION;
* В EXCEPTION;
Исключительная ситуация А BEGIN обрабатывается, а в ее RAISE A;
обработчике порождается EXCEPTION исключительная ситуация В.
WHEN A THEN RAISE WHEN В THEN Хотя здесь существует обработчик В, он не выполняется.
EXCEPTION Исключительная ситуация передается вовне блока.
WHEN В THEN Исключительная ситуация В END;
обрабатывается во внешнем блоке.
Блок выполняется успешно, и управление программой Х передается в вызывающую среду.
Пример Как показано в примерах 6 и 7, для установления исключительной ситуа ции в обработчике можно использовать оператор RAISE без аргументов, что приводит к передаче текущей исключительной ситуации в охватыва ющий блок. Этот метод удобен для регистрации ошибки. Он также при меняется для того, чтобы отменить изменения, вносимые ошибкой, и сообщить о ней охватывающему блоку.
В этом примере после оператора INSERT идет COMMIT. Это гаранти рует, что работа INSERT будет зафиксирована в базе данных в случае от ката (см. главу 4).
Устанавливается DECLARE исключительная ситуация А.
A EXCEPTION;
BEGIN RAISE A;
Исключительная ситуация EXCEPTION обрабатывается, WHEN A THEN и ошибка регистрируется.
INSERT INTO (info) VALUES A COMMIT;
RAISE;
Вновь устанавливается та же самая исключительная END;
ситуация.
Исключительная ситуация передается вовне блока, который завершается неуспешно с необработанной исключительной ситуацией А.
Обработка ошибок Рекомендации по использованию исключительных ситуаций В этом разделе предлагаются рекомендации и советы по использованию исключительных ситуаций в программах. Вы узнаете об области действия исключительных ситуаций, о том, как не допускать иск лючительных ситуаций, и как определить оператор, вызвавший конкрет ную исключительную ситуацию.
Область действия исключительной ситуации Область действия исключительной ситуации аналогична области дейст вия переменной. Если исключительная ситуация, определенная пользова телем, будет передана из блока и окажется вне области своего действия, ссылаться на нее по имени станет невозможно. Рассмотрим пример:
г) Этот пример содержится в файле SQL> BEGIN 2 DECLARE 3 EXCEPTION;
4 BEGIN 5 RAISE e_UserDefinedException;
6 END;
7 EXCEPTION 8 /* вне области своего действия;
9 это исключение можно обработать только при помощи OTHERS. */ 10 WHEN OTHERS THEN /* Инициируем исключение повторно, оно будет 12 передано вызывающей среде. */ 13 RAISE;
14 END;
15 / BEGIN * ERROR at line PL/SQL: unhandled user-defined exception at line Если сообщение об ошибке, определяемой пользователем, нужно пе редать из блока, рекомендуется описывать исключительную ситуацию в модуле так, чтобы она была видима вне этого блока, или следует восполь зоваться функцией (см. ниже). Если со здать модуль Globals и описать в нем e_UserDefinedException, то эта исключительная ситуация будет видима и во внешнем блоке. Например:
Ч Этот пример содержится в файле sql CREATE OR REPLACE PACKAGE Globals AS /* В этом модуле содержатся глобальные объявления.
К здесь объектам можно обращаться из всех других блоков или процедур с помощью квалифицированных ссылок. Заметьте, что у этого 254 ГЛАВА модуля нет тела. */ /* Исключительная ситуация, определяемая пользователем */ EXCEPTION;
END Globals;
С учетом созданного модуля Globals можно переписать рассмотрен ный выше фрагмент программы следующим образом:
ХХ- Этот пример содержится в файле BEGIN BEGIN RAISE END;
EXCEPTION /* Поскольку e_UserDefinedException видима здесь, можно обработать ее явным образом. */ WHEN THEN /* Повторно инициируем исключение, которое будет передано вызывающей среде. */ RAISE;
END;
Помимо исключительных ситуаций, в модуле Globals можно объявить общие таблицы, переменные и типы PL/SQL (см. главы 9 и 10).
Отслеживание всех исключительных ситуаций Не следует допускать возникновения в программах исключительных ситуа ций, которые не обрабатываются. Можно воспользоваться обработчиком OTHERS, создав его на самом верхнем уровне программы. Например, если этот обработчик будет регистрировать факт и время возникновения каж дой ошибки, то ни одна ошибка не будет оставлена без внимания:
DECLARE NUMBER;
- Переменная для хранения кода ошибки - Переменная для хранения текста - сообщения об ошибке.
BEGIN /* Обычная обработка информации */ EXCEPTION WHEN OTHERS THEN /* Будем регистрировать все исключительные ситуации так, чтобы блок был успешно завершен. л/ := SQLCODE;
:= 1, 200);
INSERT INTO log_table (code, message, info) VALUES error occurred TO_CHAR(SYSDATE, END;
Обработка ошибок Выявление места возникновения ошибки Иногда трудно определить, какой из SQL-операторов стал причиной ошибки, так как раздел исключительных ситуаций анализируется для всего блока. Рассмотрим пример:
BEGIN SELECT...
SELECT...
SELECT...
EXCEPTION WHEN NO_DATA_FOUND THEN - Какой из операторов SELECT породил эту исключительную ситуацию?
END;
Решить эту проблему можно двумя способами. Первый Ч создание счетчика, указывающего на SQL-оператор:
Г] - Переменная для хранения номера оператора выбора v_SelectCounter NUMBER := BEGIN SELECT.
v_SelectCounter := 2;
SELECT,..
:= 3;
SELECT.
EXCEPTION WHEN THEN INSERT INTO log_table (info) VALUES data found in [ | END;
Второй способ Ч размещение каждого оператора в собственном внут реннем блоке:
BEGIN BEGIN SELECT...
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table (info) VALUES data found in select END;
BEGIN SELECT...
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table (info) VALUES data found in select END;
BEGIN 256 SELECT EXCEPTION WHEN THEN INSERT INTO log_table (info) VALUES data found in select END;
END;
Универсальный обработчик ошибок Одна из проблем, возникающих при использовании исключительных си заключается в том, что довольно трудно определить, какой имен но фрагмент программы выполняется в момент появления конкретной исключительной ситуации. PL/SQL предлагает функцию которая возвращает значение типа являющееся значением в стеке вызовов (call stack). Рассмотрим пример, в котором используются процедуры А, В и С:
Ч Этот пример содержится в файле CREATE OR REPLACE PROCEDURE С AS v_CallStack VARCHAR2(2000);
BEGIN := INSERT INTO (char_col) VALUES INSERT INTO VALUES (-1);
END C;
CREATE OR REPLACE PROCEDURE В AS BEGIN C;
END CREATE OR REPLACE PROCEDURE A AS BEGIN B;
END A;
Обратите внимание на то, что процедура А вызывает процедуру В, которая в свою очередь вызывает процедуру С. При вызове процедуры А будут получены результаты:
Х - PL/SQL Call Stack Object Line object Handle Number name 16998fO 4 Procedure 1699caO 3 Procedure 169f918 3 Procedure 1667efO 1 Anonymous block Обработка ошибок Подобно функция возвращает текущую последовательность ошибок. С по мощью этих функций можно создать универсальный обработчик ошибок, регистрирующий как местонахождение ошибки, так и ее тип.
Для создания программного модуля нам понадобятся следующие таблицы.
-- Этот пример содержится в файле CREATE TABLE errors ( module NUMBER, NUMBER, call_stack DATE, PRIMARY KEY (module, CREATE TABLE call_stacks ( module NUMBER, call_order NUMBER.
NUMBER, PRIMARY KEY (module, seq_number, call_order), FOREIGN KEY (module, REFERENCES errors ON DELETE CASCADE);
CREATE TABLE error_stacks ( module VARCHAR2(50), NUMBER, error_order NUMBER, facility CHAR(3), NUMBER(5), VARCHAR2(100), PRIMARY KEY (module, error_order), FOREIGN KEY (module, REFERENCES errors ON DELETE CREATE SEQUENCE error_seq START WITH INCREMENT BY Используя приведенные таблицы, создадим модуль ErrorPkg:
Ч Этот пример содержится в файле /* Универсальный программный модуль обработки ошибок, в котором используются функции FORMAT_ERROR_STACK и Этот модуль будет сохранять общую информацию об ошибках в таблице errors, а подробную информацию о стеке вызовов и стеке ошибок соответственно в таблицах call_stacks и error_stacks. */ CREATE OR REPLACE PACKAGE ErrorPkg AS 258 - Начало обработки ошибок. Процедуру следует вызывать - в каждом обработчике исключительной ситуации, - регистрирующем ошибку. Переменная р_Тор должна быть истинной - только на самом высоком уровне На других уровнях -- она должна быть ложной.
PROCEDURE BOOLEAN);
- Выводит на экран стеки вызовов и ошибок (при помощи - для заданных модуля и последовательного номера.
PROCEDURE IN IN - Расшифровывает стеки вызовов и ошибок и сохраняет результаты - в таблицах errors и call_stacks. Возвращает последовательный - номер, под которым запоминается ошибка.
- Если параметр истинен (TRUE), то все операции -- ввода данных завершены.
- Для использования ошибка должна быть - предварительно обработана. Поэтому необходимо всегда вызывать - HandleAll с р_Тор = TRUE.
PROCEDURE IN OUT errors.
BOOLEAN DEFAULT FALSE);
END ErrorPkg;
CREATE OR REPLACE PACKAGE BODY ErrorPkg AS CONSTANT v_Handled BOOLEAN := FALSE;
v_ErrorStack v_CallStack VARCHAR2(2000);
PROCEDURE BOOLEAN) IS BEGIN IF p_Top THEN v_Handled := FALSE;
NOT vjandled THEN vjandled := TRUE;
:= v_CallStack := END IF;
END HandleAll;
PROCEDURE PrlntStacks(p_Module IN IN IS errors.
errors.
CURSOR IS SELECT object_handle, line_num, FROM call_stacks WHERE module = Обработка ошибок AND = BY CURSOR IS SELECT facility, FROM error_stacks WHERE module = AND = ORDER BY error_order;
BEGIN SELECT INTO FROM errors WHERE module = AND = - Выведем общую информацию об ошибке.
DD-MON-YY Module: ' | p_Module);
. Error p_SeqNum -- Выведем стек вызовов.
Call Object Handle Line Number Object - --');
FOR v_CallRec in c_CallCur LOOP ' | || ' | | END LOOP;
- Выведем стек ошибок.
Error FOR v_ErrorRec in c_ErrorCur LOOP | | | | END LOOP;
END PrintStacks;
PROCEDURE IN OUT BOOLEAN DEFAULT FALSE) IS NUMBER;
v_Index NUMBER;
v_Length NUMBER;
NUMBER;
v_Call VARCHAR2(100);
NUMBER := 1;
260 NUMBER := 1;
v_Facility stacks.
BEGIN - Сначала получим последовательный номер ошибки.
SELECT INTO FROM dual;
- Внесем первую часть информации заголовка в таблицу errors.
INSERT INTO errors (module, VALUES (p_Module, v_ErrorStack, v_CallStack, SYSDATE);
- Расшифруем стек ошибок и получим сведения о каждой из них.
Х- Для этого просмотрим список ошибок. Начнем - с индекса в начале этого списка.
v_Index := 1;
- Последовательно просмотрим список, отмечая каждый символ - новой строки. Этот символ заканчивает каждую ошибку в списке.
WHILE v_Index < LENGTH(v_ErrorStack) LOOP - v_End является позицией символа новой строки.
v_End := INSTR(v._ErrorStack, v_NewLine, v_Index);
- Таким образом, сведения об ошибке расположены между - текущим индексом и символом новой строки, v_Error v_Index, v_End - v_Index);
- Перейдем к следующей итерации.
v_Index := v_Index + LENGTH(v_Error) + 1;
- Информация об ошибке выглядит следующим образом:
Необходимо извлечь каждый Х- элемент для последующего ввода в таблицу.
- Средство -- это первые 3 символа в информации об ошибке.
:= SUBSTR(v_Error, 1, 3);
- Удалим средство и дефис (всегда := SUBSTR(v_Error, 5);
Обработка ошибок - Теперь получим номер ошибки.
:= 1, INSTR(v_Error, - Удалим номер ошибки, двоеточие и пробел (всегда 7 символов).
v_Error := 8);
- Осталось сообщение об ошибке.
:= v_Error;
- Введем сведения об ошибках в таблицу, зафиксируем номер и - текст сообщения первой ошибки.
INSERT INTO error_stacks (module, error_order, facility, VALUES IF = 1 THEN := := || ' |l ' : ' END IF;
v_ErrorOrder := + 1;
END LOOP;
- Пополним таблицу errors, введя в нее текст сообщения и код ошибки, UPDATE errors SET = WHERE module = AND seq_number = - Теперь необходимо расшифровать стек вызовов и получить - сведения о каждом из них. Для этого просмотрим список вызовов.
- Начнем с индекса, находящегося после - первого вызова в стеке, т.е. после первой пары, состоящей из - слова и символа новой строки.
:= INSTR(v_CallStack, + 5;
- Последовательно просмотрим список, отмечая каждый символ - новой строки. Этот символ заканчивает каждый вызов в списке.
WHILE v_Index < LOOP - v_End является позицией символа новой строки.
v_End := INSTR(v_CallStack, - Таким образом, сведения о вызове расположены между - текущим индексом и символом новой строки.
v_Call := SUBSTR(v_CallStack, v_Index, - Перейдем к следующей итерации.
v_Index := v_Index + + 262 - В вызове находятся логический номер объекта, - номер строки и имя объекта, разделенные - пробелами. Необходимо выделить каждый из этих элементов для - последующего ввода в таблицу.
- Сначала удалим из вызова пробел.
v_Call := - Получим логический номер объекта.
:= 1, INSTR(v_Call, ' '));
- Теперь удалим из вызова логический номер объекта и пробел.
v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1);
v_Call - Теперь можно узнать номер строки.
:= TO_NUMBER(SUBSTR(v_Call, 1, INSTR(v_Call, ')));
- Удалим номер строки и пробел.
v_Call := + 1);
v_Call := LTRIM(v_Call);
- Осталось имя объекта.
v_ObjectName v_Call;
- Внесем в таблицу все вызовы, исключая вызов ErrorPkg.
IF > 1 THEN INSERT INTO call_order, object_handle, VALUES (p_Module, END IF;
:= + 1;
END LOOP;
/ IF THEN COMMIT;
END IF;
END END ErrorPkg;
Теперь создадим для таблицы триггер (для этого необходи ма привилегия CREATE TRIGGER), устанавливающий исключительную ситуацию и модифицируем процедуры В и С следую щим образом:
ХХ- Этот пример содержится в файле CREATE OR REPLACE TRIGGER BEFORE INSERT ON Обработка ошибок BEGIN RAISE ZERO_DIVIDE;
END temp_insert;
CREATE OR REPLACE PROCEDURE С AS BEGIN INSERT INTO VALUES (7);
EXCEPTION WHEN OTHERS THEN RAISE;
END CREATE OR REPLACE PROCEDURE В AS BEGIN C;
EXCEPTION WHEN OTHERS THEN RAISE;
END CREATE OR REPLACE PROCEDURE A AS v_ErrorSeq NUMBER;
BEGIN B;
EXCEPTION WHEN OTHERS THEN v_ErrorSeq, TRUE);
ErrorPkg. v_ErrorSeq);
END A;
Следует заметить, что, кроме самого высокого уровня вложения (проце дура все обработчики исключительных ситуаций выглядят следующим образом:
WHEN OTHERS THEN RAISE;
Это означает, что модуль должен записать в таблицы (если не обходимо) стеки вызовов и ошибок, а затем передать сведения об ошибке в вызывающую процедуру. На самом высоком уровне процедура должна вызываться с параметром TRUE. Это сообщает модулю о том, что достигнут самый верхний уровень вложения и передавать даль ше сведения об ошибке не нужно. После этого процедура StoreStacks со храняет стеки вызовов и ошибок в таблицах errors, error_stacks и call_stacks, индексированных по имени модуля. В качестве имени модуля (module) должно использоваться имя программного модуля (package) или другой идентификатор. Каждая ошибка сохраняется с указанием как 264 имени модуля, так и номера ошибки, которые возвращаются процедурой StoreStacks. При вызове PrintStacks запрашивается таблица errors, и резуль таты выводятся на экран с помощью модуля DBMS_OUTPUT. Например, если вызвать из процедуру А, то на экране будет отображена следующая информация:
Ч Содержится в файле SQL> SET SERVEROUTPUT ON SIZE 1000000 FORMAT TRUNCATED SQL> exec A;
31-MAY-01 Module: Error Test #1: divisor is equal to Complete Call Stack:
Object Handle Line Number Object Name 16998fO 6 procedure 1699caO 169f918 4 procedure 16570eO 1 anonymous block Complete Error ORA-1476: divisor is equal to zero at line ORA-4088: error during execution of trigger PL/SQL procedure successfully Итоги В этой главе рассказывалось о том, как обнаруживаются ошибки при вы полнении программ PL/SQL и как программы реагируют на ошибки. В ар сенале PL/SQL имеется специальное средство Ч исключительные ситуации и обработчики исключительных ситуаций. Было показано, как описываются исключительные ситуации и как устанавливается соответст вие между ними и различными ошибками: стандартными ошибками Oracle и ошибками, определяемыми пользователями. Кроме того, были рассмот рены алгоритмы, посредством которых осуществляется передача исключи тельных ситуаций между разделами блоков PL/SQL. Даны рекомендации по использованию исключительных ситуаций.
ГЛАВА Сборные конструкции 266 L/SQL позволяет оперировать одновременно несколькими перемен ными как единым целым. Такие типы данных называются сборными конст рукциями (collection). В существовал лишь один тип сборных конструкций Ч индексная таблица. В к этому типу добавлены еще два: вложенные таблицы и изменяемые массивы (varray). предо ставляет возможность создания многоуровневых сборных конструкций, т.е. сборных конструкций сборных конструкций. Каждый из этих типов аналогичен массивам в других языках программирования. В данной главе обсуждаются свойства сборных конструкций.
Объявление и использование типов сборных конструкций PL/SQL имеет два составных типа: записи и сборные конструкции (см.
главу 3). Запись позволяет интерпретировать несколько переменных, на пример все поля таблицы students, как целое. Записи аналогичны струк турам в С. Сборные конструкции также являются составными типами данных в том смысле, что позволяют интерпретировать несколько пере менных как целое. Однако вместо объединения нескольких переменных различных типов, сборная конструкция объединяет элементы одного и того же типа, аналогично массиву С или Java. Существуют три типа сбор ных конструкций: индексные таблицы, вложенные таблицы и изменяемые массивы.
Индексные таблицы (index-by tables) впервые появились в PL/SQL 2. (в редакции 7.0) и были значительно усовершенствованы в PL/SQL 2.3 (в редакции 7.3). Вложенные таблицы (nested tables), появившиеся в расширили функциональные возможности ин дексных таблиц дополнительными методами сборных конструкций (изве стными как табличные атрибуты (table attributes) индексных таблиц).
Вложенные таблицы можно хранить в таблицах базы данных (именно по этому они называются вложенными) и использовать непосредственно в SQL. Индексные же таблицы существуют исключительно в PL/SQL, и их нельзя хранить непосредственно в таблице базы данных. В совокупности индексные таблицы и вложенные таблицы называют таблицами PL/SQL.
Внимание В таблицами PL/SQL назывались индексные таблицы.
В к таблицам PL/SQL относят и индексные, и вложенные таблицы.
Третий тип сборных конструкций Ч изменяемый массив (varray). Изме няемые массивы появились в они сходны с таблицами PL/SQL способом обращения к ним. Однако изменяемые массивы объявляются с фиксированным числом элементов, в то время как для таблиц PL/SQL верхний предел не устанавливается.
Сборные конструкции В этом разделе рассматриваются способы объявления и применения сборных конструкций всех трех типов. Различия между ними и сходства представлены в конце раздела.
Индексные таблицы Синтаксически индексные таблицы аналогичны массивам С и Java. Что бы объявить индексную таблицу, сначала нужно определить ее тип в бло ке PL/SQL, а затем объявить переменную данного типа (так же, как и в случае записей). Общий синтаксис описания индексной таблицы таков:
TYPE IS TABLE OF тип INDEX BY где Ч имя нового типа, а тип Ч предопределенный тип либо ссылка на тип посредством или Приведем примеры объявления различных типов таблиц и переменных PL/SQL.
Х- Этот пример содержится в файле DECLARE TYPE NameTab IS TABLE OF students.
BY BINARY_INTEGER;
TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
NameTab;
v_Dates DateTab;
Внимание Предложение INDEX BY обязательно в описании индексной таблицы, но не требуется для вложенных таблиц.
После того как объявлены тип и переменная, можно ссылаться на от дельные элементы таблицы PL/SQL следующим образом:
где Ч это имя таблицы, а индекс Ч либо переменная, имеющая тип BINARYJNTEGER, либо переменная или выражение, которое может быть преобразовано в тип Продолжим наш блок PL/SQL:
-- Этот пример содержится в файле BEGIN SYSDATE END;
Ссылка на таблицу, как и ссылка на запись или переменную, является именующим выражением (lvalue), так как указывает на область памяти, которая была выделена системой поддержки PL/SQL.
268 ГЛАВА Сравнение индексных таблиц с массивами С и Java Рассмотрим следующий блок PL/SQL, создающий индексную таблицу и присваивающий значения некоторым ее элементам:
-- Этот пример содержится в файле DECLARE TYPE IS TABLE OF INDEX BY BINARY_INTEGER;
BEGIN - Присвоим значения трем элементам таблицы. Обратите внимание на то, - что значения ключа не являются последовательными.
, := := v_Characters(3) := END;
Синтаксически операции присваивания для элементов таблицы анало гичны операциям присваивания для массивов С или Java, но индексные таблицы реализованы по-другому. Индексная таблица схожа с таблицей базы данных и содержит два столбца: key (ключ) и value (значение). Тип ключа Ч BINARYJNTEGER, а тип значения Ч это тип данных, указанный в описании (в примере выше это После выполнения приведенного блока структура данных в будет такой, как показано в таблице 8.1. При работе с индексными табли цами обращайте внимание на следующее:
Х Число строк индексной таблицы может быть любым. Единственное ограничение (кроме доступного объема памяти) Ч это число значе ний ключа, которые представляются типом BINARY_INTEGER (-2147483647...+2147483647).
Х Порядок элементов индексной таблицы необязательно должен быть строго определен. Эти элементы хранятся в памяти не подряд, как в массивах, поэтому они могут вводиться произвольно (если ин дексная таблица передается из PL/SQL в базовый массив С или Java, элементы следует пронумеровать по порядку, начиная с Х Ключи, применяемые в индексной таблице, необязательно дол жны быть последовательными. В качестве индекса таблицы может быть использовано любое значение или выражение, имеющее тип BINARYJNTEGER.
Х Единственный тип данных, разрешенный для ключей, Ч это тип BINARYJNTEGER.
Таблица 8.1. Содержимое Ключ Значение О Harold Х7 Susan 3 Steve Сборные конструкции Несуществующие элементы Присвоение значения несуществующему элементу индексной табли цы создает этот элемент, что напоминает операцию INSERT, выполняе мую над таблицей базы данных. Обращение к элементу похоже на операцию SELECT. Действительно, если ссылка на элемент произво дится до того, как он создан, система поддержки PL/SQL возвращает со общение об ошибке "ORA-1403: no data found" (данные не найдены), как и в случае таблицы базы данных. Рассмотрим следующий сеанс работы с Ч Этот пример содержится в файле SQL> DECLARE 2 TYPE IS TABLE OF NUMBER 3 INDEX BY 4 NumberTab;
5 BEGIN 6 - Присвоим значения некоторым элементам.
7 FOR IN LOOP 8 v_Count * 10;
9 END LOOP;
11 И распечатаем их.
12 elements:
13 FOR IN LOOP 14 v_Count | '):
16 END LOOP;
18 Х- Считаем данные в Поскольку этой переменной не 19 - было присвоено значение, устанавливается 20 BEGIN 21 | | 22 EXCEPTION 23 NO_DATA_FOUND THEN 24 DBMS_OUTPUT. PUT_LINE( 25 data found reading 26 END;
27 END;
28 / Table ГЛАВА No data found reading PL/SQL procedure successfully completed.
Внимание Информация о модуле DBMS_OUTPUT представлена в главе 2, а средства PL/SQL для обработки исключений рассмотрены в главе 7.
Элементы индексной таблицы удаляются с помощью метода DELETE (см. ниже).
Индексные таблицы нескалярных типов данных В редакции 7.3 индексные таблицы поддерживали единственный нескалярный тип данных Ч записи. В допустимы индексные таб лицы объектных типов, а в Ч индексные таблицы любых типов сборных конструкций.
Индексные таблицы записей В следующем примере представлена индекс ная таблица, состоящая из записей:
Ч Этот пример содержится в файле tabRecord.sql DECLARE TYPE IS TABLE OF INDEX BY /* Каждый элемент v_Students является записью. */ v_Students StudentTab;
BEGIN /* Извлекаем запись с идентификатором 10001 и сохраняем ее в v_Students(10001). */ SELECT INTO FROM students WHERE id = 10001;
/* Присвоим значения напрямую.
:= := END;
Каждый элемент этой таблицы является записью, поэтому можно об ращаться к полям данной записи следующим образом:
поле Индексные таблицы объектных типов В разрешены индекс ные таблицы объектных типов, например:
- Этот пример содержится в файле tabObject.sql CREATE OR REPLACE TYPE AS OBJECT ( NUMBER, field2 VARCHAR2(20), DATE);
Сборные конструкции DECLARE TYPE ObjectTab IS TABLE OF MyObject INDEX BY BINARY INTEGER;
/* Каждый элемент является экземпляром типа объекта MyObject */ v_0bjects ObjectTab;
BEGIN /* Присвоим значения v_0bjects(1) напрямую. Сначала необходимо * инициализировать объектный */ v_0bjects(1) := NULL, NULL);
v_0bjects(1).field2 := := SYSDATE;
END;
Внимание Объектные типы создаются сначала с помощью команды SOL CREATE TYPE. Затем могут создаваться экземпляры объектов созданного типа. Дополнительную информацию об объектных типах можно получить в главе 12.
Вложенные таблицы Базовые функциональные возможности вложенных таблиц ничем не от личаются от возможностей индексных таблиц. Вложенную таблицу мож но рассматривать как таблицу базы данных, содержащую два столбца:
столбец ключей и столбец значений (см. выше). Из вложенной таблицы, как и из индексной, можно удалить любые элементы, после чего та стано вится разреженной, с непоследовательными ключами. Однако при созда нии вложенных таблиц ключи должны располагаться по порядку, и ключи не могут быть отрицательными. Кроме того, вложенные таблицы, в отличие от индексных, можно хранить в базе данных, а максимальное число строк вложенной таблицы составляет 2 гигабайта, что является также максимальным значением ключа.
Синтаксис создания вложенной таблицы:
TYPE IS TABLE OF [NOT где Ч это имя нового типа, а Ч тип элементов вложенной таблицы. В качестве типа таблицы может использоваться объ ектный тип, определяемый пользователем, или выражение, в котором применяется но не могут использоваться BOOLEAN, NCHAR, NCLOB, и REF CURSOR. До версии типом табли цы не могли быть также TABLE и VARRAY. Это ограничение снято в (см. ниже). Если указано NOT NULL, то элементы вложенной таблицы не могут быть NULL.
272 ГЛАВА Внимание Единственное синтаксическое отличие индексной таблицы от вложенной заключается в указании в операторе создания таблицы предложения INDEX BY Если это предложение отсутствует, то создается вложенная таблица, а если присутствует Ч то индексная таблица.
Ниже пример объявления вложенных таблиц.
Ч Этот пример содержится в файле DECLARE - Создадим тип вложенной таблицы на основе типа, TYPE ObjectTab IS TABLE OF MyObject;
-- Тип вложенной таблицы, основанный на TYPE StudentsTab IS TABLE OF - Переменные, которые имеют типы, созданные выше v_ClassList StudentsTab;
v_ObjectList ObjectTab;
Внимание Объектный тип MyObject определен в предыдущем разделе.
Инициализация вложенных таблиц При создании индексной таблицы без элементов она будет пустой. А при объявлении вложенной таблицы без элементов (см. предыдущий пример) она, подобно другим типам PL/SQL, инициализируется с помощью NULL. При попытке добавить элемент к вложенной воз вращается сообщение об ошибке Reference to uninitialized col lection" (ссылка на неинициализированную сборную Эта ошибка соответствует предопределенной исключительной ситуации COLLECTION_IS_NULL. При выполнении следующего раздела возник нет ошибка:
Г] Этот пример содержится в файле BEGIN - При выполнении этой операции присваивания будет установлена - исключительная ситуация COLLECTION_IS_NULL, так как v_ObjectList - является NULL.
v_ObjectList(1) := MyObject(-17, END;
Как же инициализировать вложенную таблицу? Это делается при по мощи функции-конструктора. Конструктор вложенной таблицы имеет то же имя, что и сама таблица. Однако число его аргументов варьируется, причем каждый из аргументов должен иметь тип, совместимый с типом таблицы. Аргументы становятся элементами таблицы с последовательными индексами, начиная с индекса 1. В следующем сеансе использу ются конструкторы вложенных таблиц:
Сборные Ч Этот пример содержится в файле 2 TYPE NumbersTab IS TABLE OF NUMBER;
4 - Создадим таблицу с одним элементом.
5 v_Tab1 NumbersTab 7 - Создадим таблицу с пятью элементами.
8 NumbersTab 2, 3, 5, 7);
10 -- Создадим таблицу без 11 v_Tab2 NumbersTab := 12 BEGIN 13 - Присвоим значение При этом значение, заданное 14 -- для v_Tab(1) при инициализации (-1), будет заменено.
15 v_Tab1(1) := 12345;
17 - Распечатаем v_Primes.
18 FOR v_Count IN LOOP 19 || 20 END LOOP;
22 END;
23 / PL/SQL procedure successfully Пустые таблицы Обратите внимание на объявление в предыду щем блоке:
-- Создадим таблицу без элементов.
v_Tab2 NumbersTab v_Tab2 инициализируется вызовом конструктора без аргументов. При этом создается таблица без элементов, однако она не является атомарной NULL-таблицей. Продемонстрируем это с помощью сеанса Г] пример содержится в файле DECLARE 2 TYPE IS TABLE OF 3 - Создадим NULL-таблицу.
4 v_Tab1 WordsTab;
5 Х- Создадим таблицу с одним элементом, значение которого NULL.
6 WordsTab 7 BEGIN 8 IF v_Tab1 IS NULL THEN 9 is 10 ELSE 11 is not ГЛАВА 12 END IF;
13. IF v_Tab2 IS NULL THEN is 15 ELSE is not END IF;
18 END;
19 / is NULL v_Tab2 is not NULL PL/SQL procedure successfully completed.
Добавление элементов в существующую таблицу Хотя для таблицы не устанавливается никаких ограничений, нельзя при своить значение элементу, который еще не существует. Если попытаться это сделать, PL/SQL выдаст сообщение об ошибке "ORA-6533: Subscript beyond count" (неправильный индекс), которая эквивалентна предопре деленной исключительной ситуации Приведем пример:
Ч Этот пример содержится в файле SQL> DECLARE 2 TYPE IS TABLE OF NUMBER;
3 := 2, 3);
4. BEGIN - v_Numbers инициализирована как состоящая из 3 элементов.
6 - Поэтому следующие операции присваивания правильны.
7 7;
8 := -1;
10 - Однако эта операция присваивания вызывает ORA-6533.
11 v_Numbers(4) := 4;
12 END;
13 / DECLARE ERROR at line Subscript beyond count at line Х Совет Можно увеличить размер вложенной таблицы при помощи метода EXTEND (см. ниже).
Изменяемые массивы Изменяемый массив, или массив с переменной длиной (varray, varying array, variable length Ч это тип данных, практически идентичный массиву в языке программирования С или Java. Синтаксически Сборные конструкции обращение к изменяемому массиву происходит так же, как к вложенной или индексной таблице. Однако для размера массива устанавливается фиксированная верхняя граница, указываемая в объявлении типа.
Структура массива Ч это не разреженная структура данных без верхней границы;
элементы вводятся в массив, начиная с индекса 1 и до максима льного значения, заданного в объявлении типа изменяемого массива.
Максимальный размер изменяемого массива Ч 2 гигабайта.
Изменяемый массив хранится так же, как массив в С или Java, Ч непре рывно в памяти, в отличие от вложенной или индексной таблицы, кото рая больше похожа на таблицу базы данных.
Объявление изменяемого массива Тип изменяемого массива объявляется следующим образом:
TYPE | VARYING OF тип_элементов [NOT где Ч это имя нового типа изменяемого массива, Ч целое число, определяющее максимальное количество эле ментов в изменяемом массиве, а Ч скалярный тип, тип записи или объектный тип PL/SQL. Кроме того, тип элементов можно указать при помощи %TYPE, но недопустимы BOOLEAN, NCHAR, NCLOB, REF CURSOR. До версии типом элемен тов не могли быть также TABLE и Приведем пример правильно го использования типов изменяемых массивов:
Ч Этот пример содержится в файле varray.sql DECLARE - Несколько правильных типов изменяемых массивов.
- Это список чисел, каждое из которых не должно быть null.
TYPE IS VARRAY(10) OF NUMBER(3) NOT NULL;
Список записей PL/SQL.
TYPE IS OF -- Список объектов.
TYPE ObjectList IS VARRAY(25) OF MyObject;
Инициализация изменяемых массивов Как и таблицы, изменяемые массивы инициализируются с помощью кон структоров. Число аргументов, передаваемых конструктору, становится начальной длиной изменяемого массива и не должно превышать макси мальной длиной, указанной в его типе. Покажем это на примере сеанса работы в Ч Этот пример содержится в файле SQL> DECLARE 2 - Определим тип изменяемого массива.
3 TYPE Numbers IS VARRAY(20) OF NUMBER(3);
276 ГЛАВА 4 -- Объявим изменяемый массив значений NULL.
5 Numbers;
6 - В этом массиве содержатся 2 элемента.
7 Numbers 2);
8 - В этом массиве содержится 1 элемент, равный NULL 9 v_List2 Numbers := 10 BEGIN 11 IF IS NULL THEN 12 is 13 END IF;
14 v_List2(1) THEN 15 is 16 END IF;
17 END;
18 / v_NullList is NULL v_List2(1) is NULL PL/SQL procedure successfully completed.
Работа с элементами изменяемых массивов Как и в случае вложенной таблицы, начальный размер изменяемого мас сива определяется числом элементов, указываемых в конструкторе при объявлении массива. Присвоение значений элементам, не попадающим в указанный диапазон, к ошибке Subscript beyond co unt" (неправильный индекс). Рассмотрим пример сеанса Г) Ч Этот пример содержится в файле SQL> DECLARE 2 TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
4 - Объявим изменяемый массив, состоящий из трех элементов.
5 v_List Strings := 7 BEGIN 8 - Значение индекса в диапазоне от 1 до 3, поэтому 9 - данная операция присваивания верна.
10 v_List(2) := 12 - Значение вне диапазона;
устанавливается ORA-6533.
13 := 14 END;
15 / DECLARE * ERROR at line ORA-06533: Subscript beyond count at line Сборные конструкции Совет Подобно вложенным таблицам, размер изменяемого массива можно увеличить при помощи метода EXTEND (см. ниже).
Однако в отличие от вложенных таблиц изменяемый не может увеличиться сверх того размера, который был указан как максимальный при объявлении типа изменяемого массива.
При попытке присвоить значения элементам вне максимального разме ра изменяемого массива или расширить его сверх максимального размера устанавливается ошибка "ORA-6532: Subscript outside of limit" (неверный индекс), которая эквивалентна предопределенной исключительной ситуа ции Приведем пример:
Ч Этот пример содержится в файле SQL> DECLARE 2 TYPE Strings IS VARRAY(5) OF VARCHAR2(10);
3 - Объявим изменяемый массив с четырьмя элементами, 4 v_List Strings О BEGIN 7 - Индекс между 1 и 4, так что все правильно.
8 := 10 - Расширим массив до 5 элементов и установим значение 11 -- пятого элемента.
v_List(5) := 15 - Попытаемся расширить массив до 6 элементов. Будет сгенерирована 16 - ORA-6532.
18 END;
19 / DECLARE ERROR at line ORA-06532: Subscript outside of limit at line Многоуровневые сборные конструкции Все приведенные выше примеры содержали одномерные сборные конст рукции. допускает сборные конструкции с большим числом изме рений, т.е. сборные конструкции сборных конструкций. Это называется "многоуровневые сборные конструкции". Объявление типа для много уровневых сборных конструкций такое же, как и для одномерных ных конструкций, за исключением того, что тип сборной конструкции сам является сборной конструкцией. Следующий раздел объявлений по казывает некоторые типы многоуровневых сборных конструкций.
278 ГЛАВА -- Этот пример содержится в файле DECLARE Х- Объявим сначала индексную таблицу чисел TYPE IS TABLE OF NUMBER BY - Теперь объявим тип, будет индексной таблицей - Это многоуровневая сборная конструкция.
TYPE IS TABLE OF INDEX BY BINARY_INTEGER;
-- Можно также получить изменяемый массив индексных таблиц TYPE t_MultiVarray IS OF -- Или вложенную таблицу TYPE IS TABLE OF Элемент многоуровневой сборной конструкции сам является сборной конструкцией, поэтому используются два набора скобок для доступа к эле менту внутренней сборной конструкции:
Ч Этот пример содержится в файле BEGIN (1) (1) := 12345;
END;
Сравнение типов сборных конструкций В этом разделе обсуждаются сходства и различия рассмотренных выше трех типов сборных конструкций. Свойства этих типов сведены в табли цу 8.2.
Таблица 8.2. Типы сборных конструкций Индексные таблицы Вложенные таблицы Изменяемые массивы Впервые появились в Впервые появились в Впервые появились в усовершенствованы в 7. Синтаксис обращения Синтаксис обращения Синтаксис обращения к элементам использует к элементам использует к элементам использует круглые скобки круглые скобки круглые скобки Oracle7.3 предлагает Помимо атрибутов Oracle7.3, Помимо атрибутов табличные атрибуты можно использовать можно использовать для манипуляции дополнительные методы дополнительные методы сборных конструкций сборных конструкций Нельзя хранить в таблицах Можно хранить в таблицах Можно хранить в таблицах базы данных базы данных базы данных Ключи могут быть Ключи должны быть Ключи должны быть положительными положительными положительными или отрицательными Сборные конструкции Таблица 8.2. Типы сборных конструкций (продолжение) Индексные таблицы Вложенные таблицы Изменяемые массивы Не имеют явно заданного Имеют явно заданный Ограничены максимальным максимального размера максимальный размер размером, указываемым в определении типа Могут отображаться Не могут отображаться Не могут отображаться на базовые массивы на базовые массивы на базовые массивы Могут быть разреженными Могут быть разреженными Всегда выделяется область для с непоследовательными с непоследовательными каждого элемента;
значения значениями ключа значениями ключа ключа последовательные Не могут быть атомарным Могут быть атомарным NULL Могут быть атомарным NULL NULL При ссылке на несуществующий При ссылке на несуществующий При ссылке на несуществующий элемент порождается элемент порождается элемент порождается NO_DATA_FOUND SUBSCRIPT_BEYOND_COUNT SUBSCRIPT_BEYOND_COUNT Можно объявлять только Можно объявлять в блоках Можно объявлять в блоках в блоках PL/SQL PL/SQL или вне их с помощью PL/SQL или вне их с помощью CREATE CREATE Значения элементам Перед присвоением значений Перед присвоением значений присваиваются напрямую, элементам таблица должна элементам массив должен без инициализации быть инициализирована быть инициализирован Оператор CREATE TYPE и его использование описываются в разделе "Типы уровня схемы".
Сравнение изменяемых массивов и вложенных таблиц Изменяемые массивы и вложенные таблицы во многом похожи:
Х Оба типа (плюс индексные таблицы) обеспечивают доступ к отдель ным элементам в PL/SQL при помощи индексов.
Х Оба типа можно хранить в таблицах базы данных (при объявлении вне блока Х К обоим типам применимы методы сборных конструкций.
Однако имеются и некоторые отличия:
Х Для изменяемых массивов задается максимальный размер, а для вложенных таблиц максимальный размер явно не задается.
Х При хранении в базе данных изменяемые массивы сохраняют уста новленный порядок значений и значения индексов своих элементов, а вложенные таблицы не сохраняют.
Сравнение вложенных и индексных таблиц Вложенные таблицы во многом похожи на индексные таблицы:
Х Структура этих табличных типов данных одинакова.
Х Обращение к элементам таблиц обоих типов осуществляется при помощи индексов.
280 Х В состав методов, применяемых для вложенных таблиц, входят все атрибуты индексных таблиц.
Однако существует и ряд серьезных различий:
Х Можно работать с вложенными таблицами при помощи SQL и со хранять их в базе в то время как с индексными таблицами этого делать нельзя.
Х Диапазон возможных значений для индексов вложенных таблиц а для индексных таблиц -2147483647... 2147483647, т.е. для индексных таблиц можно использовать отрицательные ин дексы, а для вложенных Ч нельзя.
Х Вложенные таблицы могут быть атомарным NULL (это проверяет ся при помощи операции IS NULL).
Х Для добавления элементов вложенные таблицы необходимо иници ализировать и/или расширить.
Х Для вложенных таблиц существует ряд дополнительных методов, например EXTEND и TRIM (см. ниже).
Х В PL/SQL базовый массив автоматически преобразуется в индекс ную таблицу (и наоборот), но не может быть преобразован во вло женную таблицу (и Сборные конструкции в базе данных В рассмотренных выше примерах сборные конструкции обрабатывались в блоках PL/SQL. Однако вложенные таблицы и изменяемые массивы (но не индексные таблицы) можно хранить еще и в таблицах базы дан ных. В последующих разделах говорится о доступе к хранимым сборным конструкциям и о манипулировании ими.
Особенности хранимых сборных конструкций Хранимые сборные конструкции базы данных различаются по способу объявления табличных типов и по синтаксису создания таблиц со столб цами, имеющими тип сборной конструкции.
Типы уровня схемы Для записи сборной конструкции в таблицу базы данных и для считывания ее оттуда необходимо, чтобы тип конструкции был известен и в PL/SQL, и в SQL. Это значит, что она не может быть локальной для блока PL/SQL и ее нужно объявить с помощью оператора CREATE TYPE, подобно объект ному типу. Например:
Этот пример содержится в файле SQL> - Создадим хранимый тип, видимый в SQL и PL/SQL.
SQL> CREATE OR REPLACE TYPE AS 2 VARRAY(20) OF 3 / Сборные конструкции Type SQL> DECLARE 2 -- Этот тип является локальным для данного блока.
3 TYPE DateList IS OF DATE;
- Здесь можно создать переменные DateList и 5 v_Dates DateList;
6 NameList;
7 BEGIN 8 NULL;
9 END;
10 / PL/SQL procedure successfully completed.
SQL> DECLARE 2 - Поскольку NameList является глобальным для PL/SQL, можно сослаться на 3 - него и в другом блоке.
4 NameList;
5 BEGIN 6 NULL;
7 END;
8 / PL/SQL procedure successfully completed.
Совет При выполнении команды CREATE OR REPLACE TYPE в конечный как в случае блока PL/SQL Дополнительную информацию о можно найти в главе 2.
Тип, создаваемый на уровне схемы (оператором CREATE OR REPLACE TYPE), считается глобальным для PL/SQL, и правила для областей его действия и видимости те же, что и для любого другого объекта базы дан ных. Кроме того, тип уровня схемы можно назначить столбцу базы данных (см. ниже).
Тип же, объявленный локальным в блоке PL/SQL, виден только в этом блоке и не доступен для столбцов базы данных. Тип, объявленный в заголовке модуля, виден во всем PL/SQL, но для столбцов тем не менее не доступен. Столбцам базы данных можно назначать только типы уровня схемы.
Таблица books В следующих разделах будут созданы таблицы и типы дан ных, моделирующие библиотеку, используемую студентами. Для этого нам потребуется таблица:
Г) Ч Этот пример содержится в файле CREATE TABLE books ( PRIMARY KEY, title VARCHAR2(40), VARCHAR2(40), VARCHAR2(40), 282 ГЛАВА VARCHAR2(40), VARCHAR2(40) Здесь catalog_number однозначно идентифицирует конкретную книгу, а в. содержится информация об авторах книги в форме "имя, фамилия". Следующие строки вводятся в books сценарием sql:
INSERT INTO books title, VALUES (1000, Advanced PL/SQL INSERT INTO books title, author2, author3) VALUES (1001, A Michael INSERT INTO books title, authorl, author2, VALUES (1002, Michael INSERT INTO books title, authorl, author2) VALUES (2001, History of the Arlene', INSERT INTO books (catalog_number, title, authorl) VALUES and the Modern Too, INSERT INTO books title, authorl) VALUES (3002, to the Структура хранимых изменяемых массивов Изменяемый массив можно использовать в качестве типа для столбца базы данных. В этом случае весь массив хранится в одной строке базы среди других столбцов. В разных строках содержатся разные изменяемые массивы. Рассмотрим следующие объявления:
Г] Этот пример содержится в файле CREATE OR REPLACE TYPE BookList AS OF NUMBER(4);
CREATE TABLE ( department CHAR(3), course NUMBER(3), required_reading BookList Сборные конструкции Внимание Данные изменяемого которые больше 4 Кбайт, в действительности хранятся отдельно от остальных столбцов таблицы в LOB. В и выше можно определить параметры хранения LOB отдельно в операторе CREATE TABLE.
Дополнительная информация о приводится в главе 12, а об операторе CREATE TABLE Ч в "SQL Guide".
В таблице содержатся номера книг, обязательных для чтения в данной группе. Этот список хранится как столбец типа изменяе мого массива. Тип любого столбца-изменяемого массива должен быть из вестен в базе данных и должен храниться в словаре данных, поэтому требуется оператор CREATE TYPE. Область хранения (с некоторыми данными) представлена на рис. 8.1. Отметим, что весь изме няемый массив (который может содержать до десяти элементов) хранит ся в одном столбце базы данных Department Course Required Reading 100 3001 | CS 102 1000 1001 | HIS 101 Рис. Изменяемые массивы в базе данных Структура хранимых вложенных таблиц Как и изменяемые массивы, вложенные таблицы могут храниться в виде столбцов базы данных. В каждой строке таблицы базы данных может со держаться отдельная вложенная таблица. В качестве примера смоделиру ем каталог библиотеки. Сделаем это с помощью следующих определений:
Г) - Этот пример содержится в файле CREATE OR REPLACE TYPE StudentList AS TABLE OF CREATE TABLE library_catalog ( FOREIGN KEY (catalog_number) REFERENCES NUMBER, NUMBER, StudentList) NESTED TABLE STORE AS В таблице library_catalog четыре столбца, в том числе столбец номе ров книг, являющийся сборной конструкцией, и вложенная таблица с идентификаторами студентов, получивших экземпляры книг. По поводу хранения вложенных таблиц следует сказать несколько слов:
Х Табличный тип используется в определении таблицы точно так же, как и объектный или встроенный тип столбца. Это должен быть тип уровня схемы, создаваемый оператором CREATE TYPE.
284 я Для каждой вложенной таблицы в конкретной таблице базы данных необходимо использовать предложение NESTED TABLE, которое определяет имя таблицы хранения.
Таблица хранения (store table) Ч это таблица, которая создается систе мой и используется для хранения фактических данных вложенной табли цы. В отличие от изменяемых массивов данные вложенной таблицы хранятся отдельно, а не среди остальных столбцов таблицы. Реально в столбце будет содержаться ссылка (REF) на таблицу co_tab, в которой и находится список идентификаторов студентов. Область хране ния таблицы library_catalog представлена на рис. 8.2. Для каждой строки таблицы library_catalog в столбце checked_out содержится ссылка (REF) на соответствующие строки таблицы co_tab.
library_catalog Catalog Number Copies Out Checked out 1000 20 1001 20 1002 10 3 со tab 2001 50 \ 3001 5 3002 5 V Рис. 8.2. Вложенные таблицы в базе данных Внимание Таблица хранения (в рассмотренном примере co_tab) может существовать в другой схеме и может иметь иные параметры хранения, нежели основная таблица. Таблица хранения может быть описана, и она user_tables, но обратиться непосредственно к ней нельзя. При попытке ее модификации или обращения к ней с запросом возвращается ошибка Oracle Cannot reference nested table column's storage table" ссылаться на таблицу хранения столбца вложенной таблицы). Работа с содержимым таблицы хранения осуществляется посредством SQL-операторов, выполняемых над основной таблицей. Дополнительную информацию о предложении NESTED TABLE оператора CREATE TABLE можно найти в "Oracle SOL Reference".
Сборные конструкции Манипуляции со сборными конструкциями С помощью SQL-операторов DML можно выполнять манипуляции над хранимой сборной конструкцией. Операции этого типа воздействуют на сборную конструкцию в целом, а не на отдельные элементы. Для работы с элементами сборной конструкции используется PL/SQL, а также опера торы SQL (см. ниже в разделе "SQL-операции TABLE").
Для ввода сборной конструкции в строку базы данных используется опера тор INSERT. Сборная конструкция должна быть предварительно создана и инициализирована. Кроме того, она может быть переменной PL/SQL.
В следующем примере строки вводятся в таблицу>
рис. 8.1):
Г] ХХ- Этот пример содержится в файле DECLARE v_CSBooks := BookList(1000, 1001, 1002);
:= BEGIN - В INSERT используется вновь создаваемый изменяемый массив из -- элементов.
INSERT INTO VALUES 100, - В INSERT используется ранее инициализированный изменяемый массив из -- 3 элементов.
INSERT INTO VALUES 102, v_CSBooks);
- В INSERT используется ранее инициализированный изменяемый массив из - 1 элемента.
INSERT INTO VALUES 101, END;
UPDATE Для модификации хранимых сборных конструкций используется опера тор UPDATE. После выполнения следующего примера library_ catalog будет выглядеть, как показано на рис. 8.2:
Этот пример содержится в файле DECLARE := 10002, 10003);
:= 10000, 10002, 10003);
10002, 10003);
BEGIN - Сначала введем строки с вложенными NULL-таблицами.
INSERT INTO library_catalog num_copies, VALUES (1000, 20, 3);
INSERT INTO library_catalog VALUES (1001, 20, 3);
INSERT INTO 286 VALUES (1002, 10, 3);
INSERT INTO library_catalog num_copies, VALUES 50, INSERT INTO library_catalog VALUES (3001, 5, 0);
INSERT INTO library_catalog VALUES (3002, 5, 1);
- Теперь обновим таблицу с помощью переменных PL/SQL.
UPDATE library_catalog SET = WHERE = 1000;
UPDATE library_catalog SET = WHERE = UPDATE library_catalog SET = WHERE = 1002;
- А последнюю строку модифицируем, используя новую переменную.
UPDATE library_catalog SET = WHERE = 3002;
END;
DELETE С помощью оператора DELETE можно удалить строку, содержащую сбор ную конструкцию, как показано в следующем примере:
-- Этот пример содержится в файле DELETE FROM library_catalog WHERE = 3001;
SELECT Сборные конструкции, как и любой другой тип базы данных, считывают ся в переменные PL/SQL оператором SELECT, после чего с ними можно работать с помощью процедурных операторов.
Запросы к изменяемым массивам На примере процедуры которая распечатывает книги, обязательные для данной группы, показано, как SELECT помещает хранимый изменяемый массив в переменную PL/SQL, где с ним можно работать дальше:
ХХ- Этот пример содержится в файле CREATE OR REPLACE PROCEDURE IN p_Course IN IS v_Books v_Title BEGIN -- Извлечем весь изменяемый массив.
SELECT required_reading Сборные конструкции INTO v_Books FROM WHERE department = AND course = reading for ' || ' p_Course - В цикле распечатаем каждую строку таблицы.
FOR v_Index IN LOOP SELECT title INTO v_Title FROM books WHERE = ' II v_Title);
END LOOP;
END PrintRequired;
Пусть содержимое таблицы соответствует рис. 8.1. Если мы вызовем PrintRequired, то получим следующий результат:
ХХ- Этот пример содержится в файле SOL> DECLARE 2 CURSOR IS 3 SELECT department, course 4 FROM 5 ORDER BY department;
6 BEGIN 7 FOR v_Rec IN c_Courses LOOP 9 END LOOP;
10 END;
11 / Required reading for CS 102:
1000: Advanced PL/SQL Programming A Beginner's Guide Tuning Required reading for HIS A History of the World Required reading for MUS 100:
Bach and the Modern World 3002: Introduction to the Piano PL/SQL procedure successfully completed.
Запросы к вложенным таблицам Когда вложенная таблица считывает ся в переменную PL/SQL, ей присваиваются ключи со значениями от 1 и до числа, равного количеству элементов таблицы. Значение этого числа можно узнать с помощью метода COUNT. Приведем пример процедуры Library. PrintCheckedOut, распечатывающей имена тех студентов, которые заказали конкретную книгу:
288 ГЛАВА Ч Этот пример содержится в файле CREATE OR REPLACE PACKAGE Library AS - Распечатывает сведения о студентах, заказавших конкретную книгу.
PROCEDURE IN END Library;
CREATE OR REPLACE PACKAGE BODY Library AS PROCEDURE IN IS v_Book BOOLEAN := FALSE;
BEGIN - Извлечем всю вложенную таблицу в переменную PL/SQL.
SELECT checked_out INTO v_.StudentList FROM library_catalog WHERE = SELECT * INTO v_Book FROM books WHERE = who have ' || ': ' II ' checked - Циклически просмотрим вложенную таблицу и распечатаем имена и - фамилии студентов.
IF v_StudentList IS NOT NULL THEN FOR IN LOOP := TRUE;
SELECT * INTO v_Student FROM students WHERE ID = v_StudentList(v_Index);
| | END LOOP;
END IF;
IF NOT THEN END IF;
END END Library;
Сборные конструкции Ниже показаны выходные данные Library. предпола гается, что library_catalog имеет вид, представленный на рис. 8.2:
Этот пример содержится в файле BEGIN 3 END;
4 / Students who have Oracle8i Advanced PL/SQL Programming checked Scott Smith Joanne Junebug Murgatroid PL/SQL procedure successfully Хранимые таблицы с непоследовательными ключами С вложенными таблицами, хранимыми в базе данных, можно работать только в но не напрямую в PL/SQL, и, как следствие, значения ключей при этом не записываются. В последнем примере было показано, что ключи вложен ной таблицы при ее считывании из базы данных нумеруются заново по порядку, начиная с Таким образом, при вводе в базу данных вложенной таблицы с непоследовательными ключами те изменяются. Приведем при мер. Сначала объявим тип вложенной таблицы и создадим использующую его таблицу базы данных:
Ч Этот пример содержится в файле CREATE OR REPLACE TYPE DateTab AS TABLE OF DATE;
/ CREATE TABLE ( key PRIMARY KEY, date_list DateTab) TABLE date_list STORE AS dates_tab;
Теперь можно выполнить следующий блок PL/SQL:
-- Этот пример содержится в файле DECLARE - Создадим вложенную таблицу с 5 датами.
DateTab := Х- Локальная процедура для вывода DateTab PROCEDURE Print(p_Dates IN DateTab) IS v_Index BINARY_INTEGER BEGIN WHILE v_Index LOOP v_Index 290 ГЛАВА := p_Dates.
END LOOP;
END Print;
BEGIN -- Удалим элемент 2 таблицы. Результатом будет таблица, состоящая из - 4 элементов.
of the table:
- Введем вложенную таблицу в базу данных, а затем извлечем ее еще INSERT INTO VALUES (key, VALUES in American v_Dates);
SELECT date_list INTO v_Dates FROM WHERE key = in American after INSERT and SELECT:
END;
Этот блок создает вложенную таблицу из пяти элементов, затем удаляет второй элемент, после чего таблица вводится в базу данных и считывается вновь. Результат таков:
Initial value of the table:
1:
3: 05-JUN- 4: 26-JAN- 5: Table after INSERT and SELECT:
1: 04-JUL- 2:
3: 26-JAN- 4: 01-JAN- Данные остались теми же, однако нумерация ключей изменилась.
Работа с отдельными элементами сборных конструкций В рассмотренных выше примерах сборные конструкции модифицирова лись целиком, однако можно манипулировать и отдельными их элементами, применяя для этого операции PL/SQL и SQL.
Работа в PL/SQL В модуле Library содержатся процедуры Checkout и каждая из кото рых считывает вложенную таблицу в переменную PL/SQL, обрабатывает Сборные конструкции ее, а затем обновляет таблицу Процедура была рассмотрена выше, а теперь дополним модуль:
Ч Этот пример содержится в файле CREATE OR REPLACE PACKAGE Library AS - Отмечает книгу под номером взятую студентом с - идентификатором PROCEDURE IN IN - Отмечает книгу под номером принесенную студентом с - идентификатором PROCEDURE IN IN NUMBER);
END Library;
CREATE OR REPLACE PACKAGE BODY Library AS Ч Отмечает книгу под номером взятую студентом с Ч идентификатором IN IN NUMBER) IS BEGIN - Сначала проверим, что книга существует и что есть - экземпляр, который можно взять.
BEGIN SELECT INTO FROM library_catalog WHERE = FOR UPDATE;
EXCEPTION WHEN NO_DATA_FOUND THEN is no book with catalog number ' p_CatalogNumber ' in the END;
IF = THEN of the copies of book ' ' are checked END IF;
292 ГЛАВА - Просмотрим список и проверим, нет ли этой книги у студента.
IF v_CheckedOut IS NOT NULL THEN FOR IN LOOP IF = p_StudentID THEN RAISE_APPLICATION_ERROR(-20002, ' ' already has book ' ' checked END IF;
END LOOP;
END IF;
- Выделим место в списке.
IF IS NULL THEN v_CheckedOut := StudentList(NULL);
ELSE END IF;
-- Отметим книгу, добавив ее в список.
:= - И зафиксируем это в базе данных, прибавив 1 к UPDATE library_catalog SET checked_out = v_CheckedOut, num_out = + WHERE = END Checkout;
- Отмечает книгу под номером принесенную студентом с - идентификатором p_StudentID.
PROCEDURE IN p_StudentID IN NUMBER) IS v_AlreadyCheckedOut BOOLEAN := FALSE;
BEGIN - Сначала проверим, что книга существует.
BEGIN SELECT checked_out INTO FROM library_catalog WHERE = FOR UPDATE;
EXCEPTION WHEN NO_DATA_FOUND THEN is no book with catalog number ' ' in the END;
Сборные конструкции - Просмотрим список и проверим, взял ли студент эту книгу.
IF IS NOT NULL THEN FOR IN LOOP IF v_CheckedOut(v_Counter) = THEN TRUE;
- Удалим ее из списка.
END IF;
END LOOP;
END IF;
IF NOT THEN ' ' does not have book ' | ' checked END IF;
- И зафиксируем это в базе данных, вычтя 1 из UPDATE library_catalog SET = = - WHERE = p_CatalogNumber;
END END Library;
Вызовем Checkout и Checkln. Если содержимое таблиц и library_catalog соответствует рис. 8.1 и 8.2, то будут получены следую щие результаты:
Этот пример содержится в файле SQL> DECLARE 2 CURSOR c_History101Students IS 3 SELECT student_ID FROM registered_students 5 WHERE department = 6 AND course 101;
7 v_RequiredReading 8 BEGIN 9 - Отметим книги, обязательные для всех студентов HIS 101, 11 -- Выясним, какие книги обязательны для HIS 12 SELECT required_reading 13 v_RequiredReading 14 FROM 15 WHERE department = 16 AND course = 101;
18 - В цикле просмотрим сведения о студентах History 19 FOR IN c_History101Students LOOP 20 - В цикле просмотрим список обязательного чтения.
21 FOR v_Index IN 22 -- И пометим взятую книгу!
24 END LOOP;
END LOOP;
\ 27 - Распечатаем сведения о студентах, взявших книгу.
30 - Отметим книгу, возвращенную некоторыми студентами.
31 10001);
32 10002);
33 10003);
- И распечатаем информацию снова.
37 END;
38 / Students who have A History of the World checked Scott Smith Margaret Mason Joanne Junebug Murgatroid Patrick Poll Timothy Taller Barbara Blues David Dinsmore Ester Elegant Rose Riznit Rita Razmataz Students who have A History of the World checked out:
Scott Smith Patrick Poll Timothy Taller Barbara Blues David Dinsmore Ester Elegant Rose Riznit Rita Razmataz PL/SQL procedure successfully completed.
SQL-операции TABLE Работать с элементами хранимых вложенных таблиц можно непосредст I и выше венно в SQL с помощью оператора TABLE. При использовании этого оператора не нужно извлекать вложенную таблицу в переменную PL/SQL, выполнять над ней действия, а затем обновлять в базе данных.
Однако с элементами изменяемых массивов нельзя работать непосред ственно в SQL Ч ими манипулируют с помощью PL/SQL.
Сборные конструкции TABLE определяется следующим образом:
подзапрос) где подзапрос Ч запрос, возвращающий столбец вложенной таблицы.
Внимание * Оператор был введен в В был доступен оператор THE, который действует так же, как и TABLE. Однако в от THE отказались, и вместо него должен использоваться TABLE.
Например, можно изменить процедуру Library. восполь зовавшись TABLE:
Г] Этот пример содержится в файле PROCEDURE PrintCheckedOut( IN IS StudentList;
v_Book BOOLEAN := FALSE;
CURSOR c_CheckedOut IS SELECT ID FROM TABLE(SELECT FROM library_catalog WHERE = BEGIN SELECT * INTO v_Book FROM books WHERE = who have ' ' ' checked - В цикле просмотрим вложенную таблицу и распечатаем имена и Х- фамилии студентов.
FOR IN LOOP v_FoundOne := TRUE;
SELECT * INTO v_Student FROM students WHERE ID = ' ' II END LOOP;
IF NOT v FoundOne THEN 296 END IF;
END PrintCheckedOut;
Дополнительная информация о TABLE содержится в "Oracle SQL Reference".
Запрос к хранимым изменяемым массивам Элементами хранимого из меняемого массива нельзя манипулировать с помощью инструкций DML (в отличие от вложенных таблиц), однако изменяемый массив можно запра шивать с помощью оператора TABLE. В этом случае TABLE извлекает столбец изменяемого массива и возвращает его элементы, как если бы из меняемый массив сам был отдельной одностолбцовой таблицей. Имя столбца Ч Например, можно запросить следую щим образом:
SQL> SELECT department, course, 2 FROM DEP 100 100 cs 102 CG 102 CS 102 H 6 selected.
Методы сборных конструкций Вложенные таблицы и изменяемые массивы Ч это объектные и по этому для них определяется ряд методов. Индексные таблицы обладают атрибутами. Методы и атрибуты сборных конструкций вызываются сле дующим образом:
где Ч это переменная сборной конструк ции (не имя а Ч один из методов или атрибутов (см. ниже). Методы сборных конструкций можно вызывать только из процедурных операторов, но не из SQL-операторов.
Условимся, что во всех примерах, рассматриваемых сделаны следующие объявления:
Этот пример содержится в файле CREATE OR REPLACE TYPE AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE AS VARRAY(25) OF NUMBER;
CREATE OR REPLACE PACKAGE IndexBy AS TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
END IndexBy;
Сборные конструкции Внимание Модуль IndexBy позволяет обращаться к NumTab из блоков PL/SQL с помощью синтаксиса IndexBy. NumTab.
Дополнительную информацию о модулях можно найти в главах 9 и 10.
Методы сборных конструкций перечислены в таблице 8.3.
Таблица 8.3. Методы сборных конструкций Метод Возвращаемый Описание Допустим для или атрибут тип EXISTS BOOLEAN Возвращает TRUE, если Индексных таблиц, вложенных существует указанный таблиц, изменяемых массивов элемент сборной конструкции COUNT NUMBER Возвращает число Индексных таблиц, вложенных сборной конструкции таблиц, изменяемых массивов LIMIT NUMBER Возвращает максимальное Вложенных таблиц (всегда число элементов сборной возвращает NULL), конструкции изменяемых массивов FIRST Возвращает индекс первого Индексных таблиц, вложенных и LAST INTEGER (последнего) элемента таблиц, изменяемых массивов сборной конструкции NEXT Возвращает индекс элемента Индексных таблиц, вложенных и PRIOR INTEGER сборной конструкции, таблиц, изменяемых массивов следующего или предыдущего по отношению к данному элементу EXTEND Нет Добавляет элементы Вложенных таблиц, в сборную конструкцию изменяемых массивов (до максимального размера типа) TRIM Нет Удаляет элементы, начиная Вложенных таблиц, с конца сборной конструкции изменяемых массивов DELETE Нет Удаляет указанные элементы Индексных таблиц, вложенных из сборной конструкции таблиц EXISTS Метод EXISTS позволяет определить, существует ли реально элемент сборной конструкции, на который производится ссылка. Синтаксис этого метода:
где Ч целочисленное выражение. Если элемент, указываемый п, сущест вует, возвращается TRUE (даже если элемент является NULL). Если же значение п лежит вне заданного диапазона, то EXISTS не порождает иск лючительную ситуацию SUBCRIPT_OUTSIDE_LIMIT (для вложенных таблиц и изменяемых массивов) или ошибку (для индексных 298 х а возвращает FALSE. Методы EXISTS и DELETE часто ся для работы с разреженными вложенными таблицами. Приведем пример:
Г) ХХ- Этот пример содержится в файле SQL> DECLARE 2 v_NestedTable NumTab := 14.3, 3.14159, NULL, 0);
3 v_Count BINARY_INTEGER := 1;
5 BEGIN 6 - В цикле просмотрим v_NestedTable и выведем на экран список 7 - элементов, указав с помощью EXISTS конец цикла.
8 LOOP 9 IF THEN 11 ' 13 := + 1;
14 ELSE 15 EXIT;
16 END IF;
17 END LOOP;
18 - Присвоим те же элементы индексной таблице.
19 v_IndexByTable(1) := -7;
20 v_IndexByTable(2) 14.3;
21 v_IndexByTable(3) 3.14159;
22 v_IndexByTable(4) := NULL;
23 v_IndexByTable(5) := 0;
24 - И организуем аналогичный цикл.
25 v_Count := 1;
26 LOOP 27 IF THEN 31 := v_Count + 32 ELSE 33 EXIT;
34 END IF;
35 END LOOP;
36 END;
37 / - 14. - Сборные конструкции 3. PL/SOL procedure successfully completed.
Как видно из примера, если элемент сборной конструкции содер жит NULL, возвращает TRUE. Метод EXISTS применим к вло женным таблицам и к изменяемым массивам, являющимся атомарными NULL- конструкциями;
в этом случае всегда будет возвращаться FALSE.
COUNT Метод COUNT возвращает текущее количество элементов сборной рукции в виде целого числа. COUNT не имеет аргументов и применяется везде, где разрешены целочисленные выражения. Покажем использование этого метода на примере сеанса Ч Этот пример содержится в файле SOL> DECLARE 2 v_NestedTable := 2, 3);
3 v_Varray := -2, -3, -4);
4 v_IndexByTable IndexBy.
5 BEGIN 6 - Сначала добавим несколько элементов к индексной таблице.
7 - Обратите внимание: индексные значения не являются 8 := 1;
9 v_IndexByTable(8) := 8;
10 := -1;
11 := 100;
13 - Распечатаем результаты.
15 Table 17 Count:
19 Table Count: ' 20 END;
21 / Nested Table Varray Count: Index-By Table PL/SQL procedure successfully completed.
Для изменяемых массивов метод COUNT эквивалентен методу LAST (см. ниже), так как элементы изменяемых массивов удалять нельзя. Однако из вложенной таблицы можно удалять элементы, поэтому для нее методы COUNT и LAST могут работать по-разному. Метод COUNT наиболее эф фективен при выборе вложенной таблицы в базе данных, поскольку в тот момент количество элементов таблицы неизвестно. При вычислении гового значения COUNT игнорирует удаленные элементы.
300 ГЛАВА LIMIT Метод LIMIT возвращает текущее максимальное число элементов сбор ной конструкции. Для вложенных таблиц максимальный размер не зада ется, поэтому при применении к вложенным таблицам LIMIT всегда возвращает NULL. К индексным таблицам LIMIT применять нельзя. Про иллюстрируем работу метода LIMIT на примере следующего сеанса Г) ХХ- Этот содержится в файле DECLARE 2 v_Table 2, 3);
3 v_Varray := NumVar(1234, 4321);
4 BEGIN 5 - Выведем максимальное число и текущее число элементов 6 - сборных конструкций.
7 limit:
8 count:
9 IF v_Table. LIMIT IS NULL THEN 10 limit is 11 ELSE 12 limit: ' | | v_Table.
13 END IF;
14 count:
15 END;
16 / Varray Varray Table is NULL Table PL/SQL procedure successfully completed.
Обратите внимание: пределом для изменяемого массива является ментов, что определено в операторе CREATE TYPE, хотя в данный момент массив содержит только два элемента. COUNT возвращает текущее число элементов (см. выше).
FIRST и Метод FIRST возвращает индекс первого элемента сборной конструкции, а метод LAST Ч индекс последнего элемента. Для изменяемого массива FIRST всегда возвращает 1, a LAST Ч значение COUNT, так как массив является плотным и его элементы удалять нельзя. Методы FIRST и LAST использу ются совместно с методами NEXT и PRIOR для циклического просмотра сборных конструкций.
NEXT и PRIOR Методы NEXT и PRIOR служат для увеличения или уменьшения ключа сборных конструкций. Синтаксис их таков:
Сборные конструкции где целочисленное выражение. возвращает ключ элемента, следующего сразу же за элементом п, a Ч ключ элемента, непо средственно предшествующего элементу п. Если предшествующего или следующего элемента нет, PRIOR или NEXT возвращает NULL. В приве денном ниже сеансе показано, как использовать NEXT и PRIOR совместно с FIRST и LAST для циклического просмотра вложенной таблицы:
Г) Ч Этот содержится в файле SQL> DECLARE 2 CharTabIS TABLE OF 3 v_Characters CharTab := 4 ' d', ', ', ' ', 5 ' ' 7 INTEGER;
8 BEGIN 9 - Циклически просмотрим таблицу вперед.
10 v_Index WHILE v_Index <= LAST LOOP 13 v_Index := 14 END LOOP;
17 - Циклически просмотрим таблицу назад.
18 v_Index LAST;
19 WHILE v_Index >= v_Characters. FIRST LOOP 20, 21 v_Index := 22 END LOOP;
24 END;
25 / Madam, I'm Adam PL/SQL procedure successfully Методы FIRST, LAST, NEXT и PRIOR аналогичным образом работают с изменяемыми массивами и индексными таблицами.
EXTEND Метод EXTEND служит для добавления элементов в конец вложенной таблицы или изменяемого массива. EXTEND имеет три формы:
EXTEND 302 ГЛАВА EXTEND без аргументов добавляет элемент NULL в конец сборной конструкции с индексом LAST + EXTEND добавляет в конец табли цы п элементов NULL, a добавляет в конец таблицы п ко пий элемента г. Если сборная конструкция была создана с ограничением NOT NULL, то можно применять только последнюю форму, так как при этом NULL- элементы не добавляются.
Для вложенных таблиц максимальный размер не устанавливается явно, поэтому можно вызывать EXTEND со сколь угодно большим п (мак симальный размер Ч 2 гигабайта при наличии достаточного объема памя ти). Изменяемый же массив можно расширять только до размера, объявленного максимальным, поэтому значение п не должно превышать Покажем использование EXTEND на примере сеанса Ч Этот пример в файле SQL> DECLARE 2 := 2, 3, 4, 5);
3 := 2, 3, 4, 5);
4 BEGIN 5 BEGIN 6 -- При выполнении этой операции присваивания генерируется исключение 7 - так как в содержатся 8 -- только 5 элементов.
9 := -7;
10 EXCEPTION 11 WHEN THEN 13 raised for assignment to 14 END:
16 - Можно решить эту проблему, добавив 30 элементов в 17 - 20 - А теперь выполним присваивание.
21 := -7;
23 - Изменяемый массив можно расширить только до максимального размера 24 - (определяемого также с помощью Например, в этом случае 25 - устанавливается 26 BEGIN 28 EXCEPTION 29 WHEN THEN 31 raised for 32 END;
Сборные конструкции 34 Х- А это правильно.
37 - Теперь присвоим значение последнему элементу массива.
38 := 25;
39 END;
40 / ORA-6533 raised for assignment to ORA-6532 raised for PL/SQL procedure successfully completed.
Метод EXTEND работает с внутренним размером сборной конструк ции, в котором учитываются все удаленные элементы (для вложенной таблицы). При удалении элемента (с помощью метода DELETE, описан ного ниже) удаляются его данные, но ключ остается. Продемонстрируем взаимодействие между EXTEND и DELETE на примере:
Г) Этот пример содержится в файле DECLARE - Инициализируем вложенную таблицу 5 элементами.
:= -1, 0, 1, 2);
Х- Локальная процедура для вывода таблицы на экран.
- Обратите внимание на использование FIRST, LAST и NEXT.
PROCEDURE Print(p_Table IN NumTab) IS v_Index INTEGER;
BEGIN v_Index := WHILE <= LOOP || v_Index || v_Index := END LOOP;
END Print;
BEGIN initialization, - Удалим элемент 3. При этом удаляется но место его хранения остается.
delete, Print(v_Numbers);
- Добавим в таблицу 2 копии элемента Будут введены 6 и 1);
extend, v_Numbers Print(v_Numbers);
= || = ' || 304 ГЛАВА Ниже приведен результат выполнения этого примера. Обратите вни мание на значения COUNT и LAST после выполнения операций DELETE и EXTEND.
At initialization, contains Element - Element 2:
- Element Element Element After delete, contains Element - Element 2:
- Element Element 5: After extend, contains Element - Element 2:
- Element 4: Element Element - Element 7:
- = TRIM TRIM служит для удаления элементов в конце вложенной таблицы или из меняемого массива. Этот метод имеет две формы:
TRIM Без аргументов TRIM удаляет один последний элемент сборной конст рукции. Во втором случае удаляются п элементов. Если п больше COUNT, то порождается исключительная ситуация После работы метода TRIM значение COUNT уменьшается, так как TRIM удаляет элементы конструкции.
Как и EXTEND, метод TRIM работает с внутренним размером сборной конструкции, т.е. учитывает элементы, удаленные с помощью DELETE.
Приведем пример:
Ч Этот пример содержится в файле DECLARE - Инициализируем таблицу 7 элементами.
NumTab -2, -1, 0, 1, 2, 3);
- Локальная процедура для вывода таблицы на экран.
PROCEDURE Print(p_Table IN NumTab) IS v_Index INTEGER;
BEGIN v_Index := FIRST;
Сборные конструкции WHILE <= LOOP ' v_Index := p_Table.
END LOOP;
= || END Print;
BEGIN initialization, -- Удалим элемент 6.
delete - Удалим последние 3 элемента. При этом будут удалены 2 и 3, - а также место хранения (теперь пустое) 1.
trim, END;
Этот пример дает следующий результат:
At initialization, contains Element - Element 2:
- Element 3:
- Element 4: Element Element - Element 7: COUNT = LAST = After delete, contains Element - Element 2:
- Element 3:
- Element Element 5: Element 7: COUNT = LAST = After trim, contains Element - Element 2:
- Element - Element COUNT = LAST = 306 ГЛАВА DELETE Метод DELETE удаляет 1 или более элементов из индексной или вложен ной таблицы. DELETE не оказывает воздействия на изменяемые масси вы, так как их размер фиксирован (вообще, вызывать DELETE для изменяемого массива Метод DELETE имеет три формы:
DELETE DELETE без аргументов удаляет всю таблицу. удаляет эле мент, имеющий индекс п, a Ч все элементы, находящиеся меж ду элементами с индексами После работы метода DELETE значение COUNT уменьшается, отражая новый размер таблицы. Если удаляемый элемент не существует, DELETE не устанавливает никакой исключитель ной ситуации, а пропускает этот элемент. Рассмотрим использование DELETE на примере:
Ч Этот пример содержится в файле DECLARE - Инициализируем 10 элементами.
:=Х 20, 30, 40, 50, 60, 70, 80, 90, 100);
- Локальная процедура для вывода таблицы на экран.
PROCEDURE IN NumTab) IS INTEGER;
BEGIN WHILE v_Index <= LOOP ' := END LOOP;
= || = ' END Print;
BEGIN initialization, - Удалим элемент 6.
delete(6), v_Numbers.DELETE(6);
- Удалим элементы с 7 по 9.
END;
Сборные конструкции В результате выполнения этого примера получим следующий результат:
At initialization, contains Element Element 2: Element Element 4: Element 5: Element Element 7: Element Element 9: Element 10: COUNT = LAST = After delete (6), v_Numbers contains Element Element Element Element 4: Element Element Element Element 10: COUNT = LAST = After delete contains Element Element Element Element 4: Element Element 10: COUNT = LAST = Итоги Сборные конструкции полезны в любом языке программирования. В этой главе были рассмотрены индексные таблицы, вложенные таблицы и изме няемые массивы. Были показаны различия между типами сборных конст рукций, в том числе в использовании методов сборных конструкций, представлены способы хранения сборных конструкций в базе данных и ма нипулирования ими. Какой тип сборной конструкции применять, зависит от конкретных потребностей пользователя.
ЧАСТЬ III возможности ГЛАВА Создание процедур, функций и модулей 312 два основных вида блоков PL/SQL: анонимные и имено ванные (см. главу 3). Анонимные блоки (начинающиеся с DECLARE или с BEGIN) компилируются каждый раз при их использовании. Они не хра нятся в базе данных, и их нельзя непосредственно вызывать из других блоков PL/SQL. Программные конструкции, которые рассматриваются в этой и двух последующих главах (процедуры, функции, модули и тригге являются именованными блоками, они не имеют вышеназванных ограничений. Такие конструкции можно хранить в базе данных и испол нять по мере необходимости. В этой главе обсуждается синтаксис созда ния процедур, функций и модулей. В главе 10 показано, как пользоваться ими и некоторыми из их особенностей. Глава 11 посвящена триггерам базы данных.
Процедуры и функции Процедуры и функции PL/SQL очень похожи на процедуры и функции, используемые в других языках третьего поколения, и обладают аналогич ными свойствами. В совокупности процедуры и функции называются под программами В качестве примера рассмотрим следующий фрагмент программы создания процедуры в базе данных:
Г] Ч Этот пример содержится в файле CREATE OR REPLACE PROCEDURE ( p_FirstName AS BEGIN - Внесем новую строку в таблицу students. Воспользуемся - для - идентификатора нового студента и 0 для -- current_credits, INSERT INTO students (ID, major, VALUES p_LastName, END AddNewStudent;
Созданную процедуру можно вызвать из другого блока PL/SQL:
Ч Этот содержится в файле BEGIN 'Zudnik', END;
В примере демонстрируется ряд важных аспектов:
Создание процедур, функций и модулей Х Процедура создается при помощи оператора CREATE OR REPLACE PROCEDURE. После создания процедуры она сначала компилируется, а затем сохраняется в базе данных в скомпилиро ванном виде. Скомпилированный код можно впоследствии выпол нить из другого блока PL/SQL. Исходный код процедуры также сохраняется в базе данных (см. главу 10).
Х При вызове процедуры ей можно передавать параметры. В рассмот ренном примере процедуре во время ее выполнения передаются имя, фамилия и профилирующий предмет студента. Внутри процеду ры параметр p_FirstName будет иметь значение a Ч так как именно эти литералы указаны при вызове процедуры.
Х Вызов процедуры Ч это оператор PL/SQL;
в выражениях процеду ры не вызываются. При вызове процедуры управление программой передается первому исполняемому оператору внутри нее. Когда процедура заканчивается, управление возвращается оператору, сле дующему за вызовом процедуры. В этом смысле процедуры PL/SQL функционируют точно так же, как и процедуры других языков тре тьего поколения (языков 3GL). Функции вызываются в выражениях (см. ниже).
Х Процедура Ч это блок PL/SQL, в состав которого входят раздел объ явлений, выполняемый раздел и раздел обработки исключительных ситуаций. Как и в анонимных блоках, необходимым здесь является только выполняемый раздел. Например, в процедуре AddNewStudent содержится лишь выполняемый раздел.
ч Создание подпрограмм Как и другие объекты словаря данных, подпрограммы создаются операто ром CREATE (процедуры Ч оператором CREATE PROCEDURE, а функ ции - оператором CREATE Создание процедуры Синтаксис оператора CREATE [OR REPLACE] PROCEDURE следующий:
CREATE [OR REPLACE] PROCEDURE [ (аргумент [ OUT тип, \ OUT | где Ч это имя создаваемой процедуры, аргумент Ч имя пара метра процедуры, типЧ это тип соответствующего параметра, а Ч блок PL/SQL, в котором содержится код процедуры. Список аргументов является необязательным;
в этом случае скобки отсутствуют как в объявлении, так и в вызове процедуры.
314 ГЛАВА Внимание В к оператору CREATE [OR REPLACE] PROCEDURE добавлены ключевое слово ниже раздел "Передача параметров по ссылке и по значению") и (см. главу 10).
Чтобы изменить текст процедуры, необходимо удалить и повторно здать ее. Во время разработки процедур эта операция выполняется доволь но часто, поэтому ключевые слова OR REPLACE (или заменить) позволяют выполнить такую операцию за один раз. Если процедура существует, она сначала удаляется безо всякого предупреждения (для удаления процедур используется команда DROP PROCEDURE;
см. ниже). Если процедура до этого не существовала, то она создается. Если процедура существует, а ключевые слова OR REPLACE не указаны, то оператор CREATE возвра щает ошибку Oracle: Name is already used by an existing object" (имя уже используется существующим объектом).
Как и другие операторы CREATE, создание процедуры является опе рацией DDL, поэтому до и после создания процедуры неявно выполняют ся операторы COMMIT. При этом можно использовать как ключевое слово IS, так и ключевое слово AS Ч они эквивалентны друг другу.
Тело процедуры Тело (body) процедуры Ч это блок PL/SQL, содержа щий раздел объявлений, выполняемый раздел и раздел исключительных ситуаций. Раздел объявлений располагается между ключевым словом IS или AS и ключевым словом BEGIN;
выполняемый раздел (единственный обязательный) Ч между ключевыми словами BEGIN и EXCEPTION, а раз дел исключительных ситуаций Ч между ключевыми словами EXCEPTION END.
Совет В объявлении процедуры или функции ключевое слово DECLARE отсутствует. Вместо него используется ключевое слово IS или AS. В этом просматривается аналогия PL/SQL с языком программирования Ada.
Следовательно, структура оператора создания процедуры такова:
CREATE OR REPLACE PROCEDURE AS /* Раздел объявлений */ BEGIN /* Выполняемый раздел */ EXCEPTION /* Раздел исключительных ситуаций */ END При объявлении процедуры ее имя можно при желании указать после последнего оператора END. Если после END идет идентификатор, он должен соответствовать имени процедуры.
Создание процедур, функций и модулей Совет Указание имени процедуры в конце является хорошим стилем так как выделяет оператор END, соответствующий оператору CREATE, что позволяет выявлять несоответствия в парах на самых ранних этапах программирования.
Создание функции Функции похожи на процедуры. И те, и другие получают некоторые пара метры того или иного вида (параметры описываются ниже). Функции и процедуры Ч это различные формы блоков PL/SQL, в состав каждого из торых могут входить раздел объявлений, выполняемый раздел и раздел иск лючительных ситуаций. Как так и процедуры можно хранить в базе данных или объявлять в блоке (процедуры и функции, не хранимые в базе данных, рассматриваются в главе 10). Однако вызов процедуры являет ся оператором PL/SQL, в то время как вызов функции Ч это часть некото рого выражения. Ниже приведен пример вызова функции, возвращающей TRUE, если указанная учебная группа заполнена более чем на 80%, и FALSE в противном случае.
Г) Этот пример содержится в CREATE OR REPLACE FUNCTION (>
RETURN BOOLEAN IS NUMBER;
NUMBER;
v_ReturnValue BOOLEAN;
CONSTANT NUMBER 80;
BEGIN - Получим текущее и максимальное число студентов в указанной группе.
SELECT INTO v_CurrentStudents, FROM>
IF / * 100) >= THEN := TRUE;
ELSE v_ReturnValue := FALSE;
END IF;
RETURN END AlmostFull;
316 ГЛАВА Функция возвращает логическое значение. Ниже приводит ся блок PL/SQL, в котором вызывается эта функция. Обратите внимание на то, что вызов функции не является оператором Ч он представляет со бой фрагмент условного оператора IF, расположенного внутри цикла.
Этот пример содержится в файле SQL> DECLARE 2 CURSOR c_Classes IS 3 SELECT department, course 4 FROM>
5 BEGIN 6 FOR IN c_Classes LOOP - Выведем информацию обо всех группах, в которых 8 осталось слишком мало места.
9 IF 10 THEN 12 || 13 ' is almost 14 END IF;
15 END LOOP;
16 END;
17 / 410 is almost full!
PL/SQL procedure succefully completed.
Внимание Процедура выводить свой аргумент на экране в при условии, что используется режим set on (см. главу 2).
Синтаксис функций Синтаксис, применяемый при создании хранимой функции, похож на синтаксис создания процедуры:
CREATE [OR REPLACE] FUNCTION | OUT тип, \ OUT RETURN | Ч это имя функции, аргумент и тип аналогичны аргумен ту и типу, указываемым при создании процедуры, Ч это тип значения, возвращаемого функцией, а Ч блок PL/SQL, содержащий программный код данной функции. Для тела функции при менимы те же правила, что и для тела процедуры. Например, имя функ ции можно при желании указать после закрывающего END.
Как и для процедур, список аргументов необязателен. В этом случае ни при описании функции, ни при ее вызове круглые скобки указывать не нужно. Однако необходим тип, возвращаемый функцией, так как вызов Создание функций и модулей функции является частью некоторого выражения. Тип функции исполь зуется для определения типа выражения, содержащего вызов этой функции.
Внимание Как и для процедур, в можно использовать ключевое слово предложение AUTHID.
Оператор RETURN Внутри тела функции оператор RETURN использует ся для возврата управления программой в среду с некоторым значением. Общий синтаксис оператора RETURN:
RETURN выражение;
где выражение Ч это возвращаемое значение. Значение выражения преоб разуется в тип, указанный в команде RETURN при описании функции, если, конечно, это значение уже не имеет данный тип. При выполнении оператора RETURN управление программой сразу же возвращается в вы зывающую среду.
В функции может быть несколько операторов хотя выполня ться будет только один из них. Отсутствие в функции оператора RETURN является ошибкой. Ниже приведен пример использования в одной функ ции нескольких операторов RETURN. Хотя в этой функции пять различ ных операторов RETURN, выполняется лишь один из них, какой имен но Ч зависит от заполнения учебной группы, указанной при помощи и -- Этот пример содержится в файле CREATE OR REPLACE FUNCTION ( /* Возвращает если группа заполнена до предела, Some если группа заполнена на 80%, если группа заполнена на 60%, Lots of если группа заполнена менее чем на 60%, и если ни одного студента не зарегистрировано. */ p_Department RETURN VARCHAR2 IS NUMBER;
v_MaxStudents NUMBER;
v_PercentFull NUMBER;
BEGIN - Получим текущее и максимальное число студентов в указанной группе.
SELECT INTO FROM>
- Рассчитаем текущее соотношение.
:= v_CurrentStudents / * 100;
318 IF = 100 THEN RETURN v_PercentFull > 80 THEN RETURN ELSIF v_PercentFull > 60 THEN RETURN ELSIF > 0 THEN RETURN of ELSE RETURN END IF;
END При использовании оператора RETURN в функции с ним должно быть связано некоторое выражение. Однако RETURN можно применять и в процедуре. В этом случае аргументы, приводящие к немедленной передаче управления в вызывающую среду, не указываются. Текущие значения фор мальных параметров, описанных как OUT или IN OUT, присваиваются фактическим параметрам, и выполнение программы продолжается с ратора, следующего за вызовом процедуры (см. ниже).
Удаление процедур и функций Как и таблицы, процедуры и функции могут быть удалены. При выполне нии этой операции процедура или функция удаляется из словаря данных.
Синтаксис удаления процедуры выглядит следующим образом:
DROP PROCEDURE А синтаксис удаления функции:
DROP FUNCTION где Ч имя существующей процедуры, а Ч имя существующей функции. Например, при помощи следующего оператора удаляется процедура DROP PROCEDURE AddNewStudent;
Если удаляемый объект является функцией, то нужно использовать опе ратор DROP FUNCTION, а если - процедурой, то DROP PROCEDURE.
DROP Ч это команда DDL, поэтому перед оператором DROP и после него неявно выполняется оператор COMMIT. Если подпрограмма не существу ет, оператор DROP порождает ошибку Object does not exist".
Параметры подпрограмм Как и в других языках программирования третьего поколения, можно созда вать процедуры и функции с параметрами. Параметры могут быть разного вида, и их разрешается передавать по значению или по ссылке.
Создание процедур, функций и модулей Виды параметров Обратимся к рассмотренной выше процедуре и вызовем ее из следующего анонимного блока PL/SQL:
Ч Этот пример содержится в файле callANS.sql DECLARE - Переменные, описывающие нового студента.
v_NewFirstName students. := v_NewLastName students. := := BEGIN - Введем сведения о Cynthia Camino в базу данных.
END;
Переменные передаются про цедуре AddNewStudent в качестве аргументов. В этом контексте они называ ются параметрами (actual parameter), а параметры, указанные при объявлении процедуры являются параметрами (formal Фактические пара метры содержат значения, передаваемые процедуре при ее вызове, и в них записываются результаты, возвращаемые процедурой (в зависимости от вида параметра). Именно значения фактических параметров используют ся в процедуре. Формальные параметры выступают в роли места располо жения фактических параметров. При вызове процедуры формальным параметрам присваиваются значения фактических параметров. Внутри процедуры все действия выполняются над формальными параметрами.
Когда процедура заканчивается, фактическим параметрам присваиваются значения формальных параметров. Все операции присваивания соответст вуют стандартным правилам, установленным в PL/SQL для таких опера ций, в том числе правилам преобразования типов.
Формальные параметры бывают трех видов: IN (в), OUT (из) и IN OUT (в добавлен модификатор NOCOPY). Если для формально го параметра вид не указан, то по умолчанию устанавливается IN. Разли чия между этими видами описаны в таблице 9.1. Приведем пример:
Внимание Пример Jest показывает допустимые и недопустимые присваивания PL/SQL Если удалить символ комментария у недопустимых операторов, будут возникать ошибки компиляции.
Этот пример содержится в файле CREATE OR REPLACE PROCEDURE ( IN NUMBER, OUT NUMBER, IN OUT NUMBER) IS v_LocalVariable 320 ГЛАВА BEGIN IF IS NULL) THEN is ELSE = ' || END IF;
IF IS NULL) THEN p_OutParameter is ELSE ' END IF;
IF IS NULL) THEN is ELSE END IF;
/* Присвоим переменной Это допустимая операция, так как чтение параметра IN и информация в него не */ v_LocalVariable -- Правильно.
/* Присвоим 7 параметру Этого делать НЕЛЬЗЯ, так как производится запись в параметр IN. */ Х- := 7;
Ч Неправильно.
/* Присвоим 7 параметру Это допустимая операция, так как производится запись в параметр OUT. */ := 7;
- Правильно.
/* Присвоим переменной v_LocalVariable. В версии 7.3.4 и в версии 8.0.4 и выше (в том числе и в это разрешается делать. В версиях Oracle, предшествующих этого делать НЕЛЬЗЯ, так как производится чтение параметра v_LocalVariable := -- Скорее всего, неправильно.
/* Присвоим переменной v_LocalVariable. Это допустимая операция, так как производится чтение параметра IN OUT. */ := -- Правильно.
/* Присвоим 8 параметру Это допустимая операция, так как производится запись в параметр IN OUT. */ := 8;
Ч Правильно.
end of IF IS NULL) THEN is ELSE = || Создание процедур, функций и модулей END IF;
IF IS NULL) THEN pJMParameter is ELSE pJMParameter = || END IF;
IF IS NULL) THEN p_InOutParameter is ELSE = END IF;
END ModeTest;
Внимание В версиях Oracle до 7.3.4 и в версии 8.0.3 не разрешается считывать параметры OUT, это можно делать в версии 8.0.4 и выше (см. ниже раздел "Чтение параметров OUT").
Таблица 9.1. Виды параметров Вид Описание IN Значение фактического параметра передается в процедуру при ее вызове.
Внутри процедуры формальный параметр рассматривается в качестве константы PL/SQL, т.е. В качестве параметра только чтения, и не может быть изменен.
Когда процедура завершается и управление программой возвращается в вызывающую среду, фактический параметр не изменяется.
OUT Любое значение, которое имеет фактический параметр при вызове процедуры, игнорируется. Внутри процедуры формальный параметр рассматривается в качестве неинициализированной переменной PL/SQL, т.е. содержит NULL- значение, и можно как записать в него значение, так и считать значение из него. Когда процедура завершается и управление программой возвращается в вызывающую среду, содержимое формального параметра присваивается фактическому параметру (в этот режим можно изменить с помощью модификатора NOCOPY;
см. Ниже раздел "Передача параметров по ссылке и по значению").
IN OUT Этот вид представляет собой комбинацию IN и OUT. Значение фактического параметра передается в процедуру при ее вызове. Внутри процедуры формальный параметр рассматривается в качестве инициализированной переменной, и можно как записать в значение, так и считать значение из него. Когда процедура завершается и управление программой возвращается в вызывающую среду, содержимое формального параметра присваивается фактическому параметру (в может применяться модификатор NOCOPY, как и для параметров OUT).
322 Обмен значениями между формальными и фактическими парамет рами Вызовем процедуру ModeTest в следующем блоке:
Х- Этот пример содержится в файле DECLARE v_In NUMBER := 1;
NUMBER := 2;
NUMBER := 3;
BEGIN calling = ' || v_In || ' = ' | I ' = ' | I ModeTest(v_In, v_0ut, calling v_In = v_In = ' | | v_0ut ' v_InOut = ' END;
Результаты работы процедуры будут следующими:
Before calling ModeTest:
v_In = 1 = 2 = Inside = 1 is NULL = At end of = 1 = 7 p_InOutParameter = After calling ModeTest:
v_In = 1 = 7 v_InOut = Можно видеть, что внутри процедуры параметр OUT инициализиро ван NULL- значением. Кроме того, значения формальных параметров IN и IN OUT в конце процедуры скопированы в фактические параметры.
Внимание Если в процедуре инициализируется исключительная ситуация, то значения формальных параметров IN OUT и OUT не копируются в соответствующие фактические параметры (в это определяется модификатором NOCOPY).
См. ниже раздел "Исключительные ситуации, возникающие в подпрограммах".
Литералы и константы в качестве фактических параметров Посколь ку значения копируются, фактический параметр, пара метру IN OUT или OUT, должен быть переменной и не может быть константой или выражением. Должна существовать область для хранения возвращаемого значения. Например, при вызове ModeTest можно заме нить литералом:
Создание процедур, функций и модулей Ч Этот пример содержится в файле DECLARE v_0ut NUMBER := 2;
NUMBER 3;
BEGIN ModeTest(1, END;
Но если заменить v_0ut пример станет неверным:
Этот пример содержится в файле callMT.
SOL> DECLARE 2 NUMBER := 3;
3 BEGIN 4 2, 5 END;
6 / DECLARE ERROR at line line 4, column 15:
PLS-00363: expression cannot be used as an assignment target ORA-06550: line 4, PL/SQL: Statement ignored Проверка во время компиляции При создании процедуры компилятор PL/SQL проверяет правильность операций присваивания. Например, если удалить знак комментария из строки с операцией присваивания для и попытаться скомпилировать ModeTest, то будет выдано со общение об ошибке:
PLS-363: expression cannot be used as an assignment target (выражение нельзя использовать в качестве объекта операции присваивания) Чтение параметров OUT До версии 7.3.4 и в версии 8.0.3 считывать дан ные из параметров OUT процедур запрещалось. Если, к примеру, попыта ться скомпилировать ModeTest для базы данных 8.0.3, то будет выдано сообщение об ошибке:
Г) is an OUT parameter and cannot be является параметром OUT и не может быть считан) Таблица 9.2. Возможность чтения параметров OUT Версия Чтение параметров OUT До 7.3.4 Запрещено 7.3.4 Разрешено 8.0.3 Запрещено 8.0.4 и выше Разрешено 324 ГЛАВА Обойти это можно, объявив параметр OUT как IN OUT. В таблице 9. показано, в каких версиях Oracle допускается чтение параметров OUT, a в каких Ч нет.
Ограничения на формальные параметры При вызове процедуры ей передаются значения фактических парамет ров, и внутри процедуры к этим значениям обращаются при помощи формальных параметров. При этом передаются не только значения, но и ограничения, наложенные на переменные, как часть механизма передачи параметров. При описании процедуры запрещается ограничивать длину параметров типа CHAR и а также точность и/или масштаб параметров типа NUMBER, поскольку ограничения принимаются от фак тических параметров. К примеру, следующее описание процедуры невер но и приводит к выдаче ошибки компиляции:
-- Этот пример содержится в файле CREATE OR REPLACE PROCEDURE ( p_Parameter1 IN OUT IN OUT AS BEGIN r1 := END Правильное объявление этой процедуры должно выглядеть так:
Ч Этот пример содержится в файле CREATE OR REPLACE PROCEDURE ParameterLength ( IN OUT VARCHAR2, IN OUT NUMBER) AS BEGIN p_Parameter1 := p_Parameter2 := 12.3;
END ParameterLength;
Итак, ограничения, накладываемые на p_Parameter1 и p_Parameter2, приходят вместе с фактическими параметрами. Если вызвать Parameter Length следующим образом:
Ч содержится в файле DECLARE v_Variable1 VARCHAR2(40);
v_Variable2 NUMBER(7,3);
BEGIN END;
то параметр r1 будет иметь максимальную длину 40 (задается факти ческим параметром v_Variable1), а для параметра будут ны точность 7 и масштаб 3 (задаются фактическим параметром v_Variable2).
Рассмотрим еще один блок, в котором вызывается ParameterLength:
Создание процедур, функций и модулей Г] -- Этот пример содержится в файле DECLARE v_Variable1 VARCHAR2(10);
BEGIN END;
Единственным отличием между этим блоком и предыдущим является то, что длина и, следовательно, равна 10, а не 40.
В процедуре ParameterLength параметру (и v_Variable1) при сваивается строка, состоящая из 15 символов, поэтому пространства для нее недостаточно. В результате при вызове данной процедуры Oracle вы дает следующую ошибку:
DECLARE * ERROR at line PL/SQL: numeric or value error: character string buffer too small at line at line Причина ошибки заключается не в процедуре, а в программе, вызыва ющей эту процедуру. К тому же Ч это ошибка этапа выполне ния, а не компиляции. Таким образом, блок скомпилирован успешно, а ошибка на самом деле возникла тогда, когда процедура возвратила значе ние, и система поддержки PL/SQL попыталась скопировать фактическое значение в формальный параметр.
Совет бо избежание ошибок, подобных ORA-6502, при создании процедур документируйте все ограничения, налагаемые на фактические параметры, вносите в сохраняемые процедуры комментарии, а также записывайте, какие функции выполняет сама процедура. В качестве альтернативы для объявления формальных параметров можно использовать TYPE и параметры процедур Единственным способом наложения ограничений на формальные параметры является использование атрибу та Если формальный параметр объявлен при помощи a базовый тип ограничен, то это ограничение распространяется не на фак тический параметр, а на формальный. Объявим следую щим образом:
Ч Этот пример содержится в файле CREATE OR REPLACE PROCEDURE ParameterLength ( IN OUT VARCHAR2, IN OUT AS BEGIN p_Parameter2 := 12345;
326 ГЛАВА END будет ограничен точностью 3, поскольку эта точность задана для столбца Даже если вызвать с фактиче ским параметром большей точности, будет использована точность фор мального параметра. Ниже приводится пример, в результате выполнения которого возвращается ошибка Ч Этот содержится в файле SQL> DECLARE 2 VARCHAR2(1);
3 - Объявим v_Variable2 без ограничений.
4 v_Variable2 NUMBER;
5 BEGIN 6 - Хотя в фактическом параметре достаточно места для 12345, 7 - учитывается ограничение, наложенное на формальный параметр, 8 - и при вызове этой процедуры выдается ошибка ORA-6502.
9 v_Variable2);
10 END;
11 / DECLARE ERROR at line PL/SQL: numeric or value error: number precision too large ORA-06512: at line at line Внимание Сообщение об ошибке ORA-6502 было изменено в Oracle3\.
До при ее возникновении сообщалось "ORA-6502:
PL/SQL numeric or value error" независимо от реальной причины ошибки. Например, текст сообщения об ошибке различен в двух предыдущих примерах, что отражает поведение Исключительные устанавливаемые в подпрограммах В случае возникновения ошибки в подпрограмме устанавливается исклю чительная ситуация, которая может быть описана предварительно или определена пользователем. Если в процедуре отсутствует обработчик дан ной исключительной ситуации, управление программой сразу же переда ется из процедуры в вызывающую среду в соответствии с правилами, установленными для передачи исключительных ситуаций (см. главу 7).
Однако в этом случае значения формальных параметров OUT и IN OUT не возвращаются фактическим параметрам. Фактические параметры бу дут иметь те же значения, которые они имели бы, если бы процедура не вызывалась. Предположим, что создается следующая процедура:
Ч Этот пример содержится в файле /* Демонстрация поведения необрабатываемых исключительных ситуаций и переменных OUT. Если значение p_Raise истинно, то устанавливается необрабатываемая ошибка. Если значение Создание процедур, функций и модулей p_Raise ложно, то процедура успешно завершается. */ CREATE OR REPLACE PROCEDURE RaiseError ( p_Raise IN BOOLEAN, OUT NUMBER) AS BEGIN 7;
IF THEN /* Хотя параметру присвоено значение 7, эта необрабатываемая исключительная ситуация вызывает немедленный возврат управления программой;
при этом значение 7 не передается фактическому параметру, связанному с параметром */ RAISE DUP_VAL_ON_INDEX;
ELSE - Возврат без ошибок. параметру передается - значение 7.
RETURN;
END IF;
END RaiseError;
Если вызвать в следующем блоке:
-- Этот пример содержится в файле DECLARE NUMBER := 1;
BEGIN value: || RaiseError(FALSE, v_TempVar);
after successful call:
2;
2nd call:
RaiseError(TRUE, EXCEPTION WHEN OTHERS THEN after unsuccessful END;
то будут получены такие результаты:
Initial value: Value after successful Value before 2nd call: Value after unsuccessful call: Перед первым вызовом RaiseError в переменной содержа лось значение Первый вызов был успешен, и было присвоено значение 7. Перед вторым вызовом RaiseError значение измени лось на 2. Второй вызов завершился неуспешно, и переменная осталась равной 2 (а не изменилась на Внимание Семантика обработки исключительной ситуации если параметр или IN OUT объявляется с помощью NOCOPY (см. ниже раздел "Семантика исключительной ситуации при использовании Передача параметров по ссылке и по значению Параметры подпрограмм передаются одним из двух способов: по ссыл ке или по значению. Когда параметр передается по ссылке (by referen ce), соответствующему формальному параметру передается указатель на фактический. Если же параметр передается по значению (by value), оно копируется из фактического параметра в формальный. Передача по ссылке обычно осуществляется быстрее, так как при этом опускает ся операция копирования;
особенно это касается параметров сборных конструкций (таблиц и изменяемых массивов;
см. главу 8). По умолча нию в PL/SQL параметры IN передаются по ссылке, а параметры IN OUT и OUT Ч по значению. Это сделано для того, чтобы сохранить се мантику исключительных ситуаций (см. предыдущий раздел) и чтобы иметь возможность проверять ограничения, наложенные на фактиче ские параметры. До изменить этот режим было нельзя.
Использование NOCOPY В существует специальное компилятору Ч NOCOPY. Синтаксис объявления параметра с этим мо дификатором таков:
[вид] NOCOPY где Ч это имя параметра, вид Ч вид параметра (IN, OUT или IN а тип данных Ч тип параметра. Если задано NOCOPY, ком пилятор PL/SQL пытается передать параметр по ссылке, а не по значе нию. NOCOPY Ч это указание компилятору, а не директива, поэтому не всегда принимается (см. ниже). Рассмотрим пример:
Х- Этот пример содержится в файле CREATE OR REPLACE PROCEDURE NoCopyTest ( IN NUMBER, OUT NOCOPY VARCHAR2, IN OUT NOCOPY CHAR) IS BEGIN NULL;
END NoCopyTest;
При указании NOCOPY для параметра IN генерируется ошибка компи ляции, поскольку параметры IN всегда передаются по ссылке и NOCOPY к ним не применим.
Семантика исключительных ситуаций при использовании NOCOPY При модификации фактического параметра, передаваемого по ссылке, изменяется и формальный параметр, так как оба они указывают на одну и ту же область памяти. Это значит, что при выходе из процедуры с необра ботанной исключительной ситуацией после изменения формального Создание процедур, и модулей параметра исходное значение фактического параметра теряется. Моди фицируем RaiseError, указав NOCOPY:
ХХ- Этот пример содержится в файле CREATE OR REPLACE PROCEDURE RaiseError ( p_Raise IN BOOLEAN, OUT NOCOPY NUMBER) AS BEGIN 7;
IF p_Raise THEN RAISE ELSE RETURN;
END IF;
END RaiseError;
Единственное изменение здесь в том, что параметр теперь передается по ссылке, а не по значению. Предположим, что RaiseError так:
Ч Этот пример содержится в файле DECLARE vJempVar NUMBER := 1;
BEGIN value: || RaiseError(FALSE, after successful call: || 2;
before | RaiseError(TRUE, EXCEPTION WHEN OTHERS THEN after unsuccessful call: | | END;
Это тот самый блок, который рассматривался в разделе "Исключитель ные ситуации, устанавливаемые в подпрограммах". Результат же теперь иной:
Initial value: Value after successful call: Value before 2nd call: Value after unsuccessful call: Фактический параметр изменился в обоих случаях, даже при установ ке исключительной ситуации.
Ограничения на использование NOCOPY В некоторых случаях указа ние NOCOPY игнорируется и параметр передается по значению. При 330 ГЛАВА этом никаких ошибок не генерируется. Не забывайте о том, что NOCOPY это указание, и компилятор не обязан всегда следовать ему. NOCOPY иг норируется в следующих ситуациях:
Х Фактический параметр является членом индексной таблицы. Если же фактический параметр Ч вся таблица, это ограничение не действует.
Х Фактический параметр ограничен точностью, масштабом или зна чением NOT NULL. Однако это не применимо по отношению к сим вольным параметрам, ограниченным максимальной длиной. Дело в том, что компилятор PL/SQL проверяет нарушения ограничений только при возвращении из подпрограммы, т.е. при копировании значения из формального параметра в фактический параметр. Если имеется нарушение ограничения, первоначальное значение факти ческого параметра необходимо изменить, что невозможно в случае NOCOPY.
Х И фактический, и формальный параметры являются записями, объ явленными либо неявно, как в случае управляющей переменной цикла, либо при помощи а ограничения соответствую щих полей отличаются друг от друга.
Х Для передачи фактического параметра необходимо неявно преоб разовывать типы данных.
Х Подпрограмма выполняется во время вызова удаленной процедуры remote procedure call), т.е. при вызове процедуры на удален ном сервере посредством соединения с базой данных. Так как пара метры должны передаваться по сети, то их невозможно передать по ссылке.
Совет Если подпрограмма является частью RPC, NOCOPY игнорируется. Если модифицировать существующее приложение, внеся вместо локальных несколько вызовов RPC, то семантика исключительных ситуаций может измениться.
Полезные свойства NOCOPY Главное преимущество NOCOPY в том, что при его использовании можно повысить производительность системы.
Это особенно ценно при передаче больших таблиц PL/SQL, например:
Ч Этот пример содержится в файле CREATE OR PACKAGE CopyFast AS - PL/SQL-таблица TYPE StudentArray IS TABLE OF - Три процедуры, принимающие параметр StudentArray разными способами.
- Эти процедуры ничего не делают.
PROCEDURE IN StudentArray);
PROCEDURE IN OUT PROCEDURE IN OUT NOCOPY StudentArray);
Создание функций и модулей - Проверим процедуру.
PROCEDURE Go;
END CopyFast;
CREATE OR REPLACE PACKAGE BODY CopyFast AS PROCEDURE IN IS NULL;
END PROCEDURE IN OUT StudentArray) IS BEGIN NULL;
END IN OUT NOCOPY StudentArray) IS BEGIN NULL;
END PROCEDURE Go IS StudentArray := StudentArray(NULL);
v_StudentRec NUMBER;
NUMBER;
NUMBER;
NUMBER;
BEGIN -- Заполним массив 50001 копией записи о David SELECT * INTO v_StudentArray(1) FROM students WHERE ID = 10007;
1);
- Вызовем каждый вариант PassStudents и измерим время выполнения.
- возвращает текущее время (в сотых долях - := := - Выведем результаты.
to pass IN: || - / to pass IN OUT: | - v_Time2) to pass IN OUT NOCOPY: | | 332 - v_Time3) / Go;
' Внимание В этом примере для группирования связанных между собой процедур используется модуль. Модули описываются ниже в разделе "Модули", сборные конструкции и метод EXTEND Ч в главе 8, a в приложении А.
Процедуры PassStudents ничего не делают Ч они лишь принимают па раметр, представляющий собой со сведениями о студен тах. Параметр Ч это 50001 запись, т.е. довольно большое значение.
Различаются процедуры тем, что принимает параметр как IN, PassStudents2 - как IN OUT, a - как IN OUT NOCOPY.
Таким образом, в PassStudents2 параметр должен передаваться по значе нию, а в двух других процедурах по ссылке. Это видно при анализе резуль татов вызова Go:
Г) SQL> BEGIN 4 / Time to pass Time to pass IN OUT:
Time to pass IN OUT PL/SQL procedure successfully completed.
Реальные результаты могут варьироваться в зависимости от системы, тем не менее время на передачу параметра IN OUT по значению существен но больше, чем на передачу параметров IN и IN OUT NOCOPY по ссылке.
Подпрограммы без параметров Если в процедуре нет параметров, не нужно указывать круглые скобки ни в ее объявлении, ни при ее вызове. То же самое справедливо и для функ ций. Приведем пример:
Этот пример содержится в файле CREATE OR REPLACE NoParamsP AS BEGIN END NoParamsP;
CREATE OR REPLACE FUNCTION NoParamsF RETURN DATE AS BEGIN RETURN SYSDATE;
END NoParamsF;
BEGIN NoParamsP;
Создание процедур, функций и модулей on ' END;
Внимание В синтаксисе CALL, доступном в Oracle8\, круглые скобки необязательны.
Позиционное и именное представления Во всех приведенных выше примерах фактические аргументы были свя заны с формальными по позициям. В случае объявления процедуры Г) -- Этот пример содержится в файле CREATE OR REPLACE PROCEDURE ( VARCHAR2, NUMBER, BOOLEAN, DATE) AS BEGIN NULL;
END CallMe;
и вызывающего блока ХХ- Этот пример содержится в файле CallMe.sql DECLARE v_Variable1 VARCHAR2(10);
v._Variable v_Variable3 BOOLEAN;
DATE;
BEGIN CallMe(v_Variable1, можно видеть, что фактические параметры связаны с формальными по позициям: v_Variable1 с v_Variable2 с и т.д. Это называется позиционным представлением (positional notation). Позицион ное представление применяется наиболее часто, и именно оно использу ется в языках третьего поколения, например в С.
В качестве альтернативы можно вызвать процедуру при помощи имен ного представления (named notation):
Ч Этот пример содержится в файле DECLARE VARCHAR2(10);
BOOLEAN;
DATE;
BEGIN => => v_Variable2, 334 ГЛАВА => v_Variable3, => v_Variable4);
END;
При именном для каждого аргумента указываются как формальный, так и фактический параметры. Это дает возможность при желании установить собственный порядок аргументов. Например, в сле дующем блоке вызывается CallMe с теми же аргументами:
Ч Этот пример содержится в файле DECLARE v_Variable v_Variable3 BOOLEAN;
DATE;
BEGIN => v_Variable2, => => v_Variable4, => END;
Кроме того, при необходимости позиционное и именное представле ния можно комбинировать в одном вызове. Первые аргументы нужно ука зывать по позициям, а оставшиеся можно указать по именам. Приведем пример:
Ч Этот пример содержится в файле DECLARE v_Variable1 VARCHAR2(10);
v_Variable BOOLEAN;
DATE;
BEGIN - Первые 2 параметра передаются по позициям, а другие 2 - по именам.
CallMe(v_Variable1, v_Variable2, => v_Variable3, p_ParameterD => v_Variable4);
END;
Именное представление Ч это еще одно свойство PL/SQL, которое за имствовано из языка Ada. Когда же нужно использовать позиционное представление, а когда именное? Ни одно из них не является более эф фективным, чем другое, поэтому все зависит от программиста и от его стиля программирования. Некоторые из стилистических особенностей этих представлений, а также различия между ними приведены в таблице 9.3.
Тем, кто предпочитает создавать краткие программы, рекомендуем ис пользовать позиционное представление, не забывая давать фактическим параметрам подходящие имена. Если же процедура принимает большое число аргументов (больше десяти), что встречается довольно редко, то желательно применять именное представление, поскольку при этом Создание процедур, функций и модулей м проще установить соответствие между формальными и фактическими параметрами. Именное представление полезно также для процедур с ар гументами, используемыми по умолчанию (см. ниже).
Совет Чем больше в процедуре, тем сложнее ее вызовы и тем труднее проверить наличие всех требуемых параметров.
Если необходимо передать в процедуру или получить из нее большое число рекомендуется определить тип записи, полями которой будут эти параметры. Затем можно использовать единственный параметр, имеющий тип записи (заметим, однако, что если вызывающая среда не PL/SQL, то может оказаться невозможным применение записи). В PL/SQL не установлено явного ограничения на число параметров.
Таблица 9.3. Позиционное и именное представления Позиционное представление Именное представление Для фактических параметров задаются более Четче показывает связь между фактическими понятные имена, отражающие назначение и формальными каждого из них.
Имена, используемые для формальных Сложно изменить все вызовы процедуры и фактических параметров, не зависят друг при изменении имен формальных от друга;
одно может быть изменено без модификации другого.
Сложно изменить все вызовы процедуры при Порядок указания формальных параметров не изменении порядка формальных зависит от порядка указания фактических;
один может быть изменен без модификации другого.
Более краткое, чем именное представление. Программы сложнее, так как при вызове про цедуры указываются как так и фактические параметры.
Параметры, значения которых заданы Для формальных параметров разрешается по умолчанию, нужно указывать в конце списка использовать значения по умолчанию аргументов. независимо от того, какой параметр имеет значение по умолчанию.
Значения параметров по умолчанию Как и при объявлении переменных, формальные параметры процедуры или функции могут иметь значения по умолчанию. Если параметр имеет значение по умолчанию, то его не нужно передавать из вызывающей сре ды. Если же параметр передается, то вместо значения по умолчанию бе рется фактический параметр. Значение по умолчанию для параметра указывается следующим образом:
[вид] | 336 ГЛАВА где Ч это имя формального параметра, вид Ч вид параметра (IN, OUT или IN OUT), Ч тип параметра (либо льно определенный, либо определяемый пользователем), а Ч присваиваемое формальному параметру по умолчанию.
Можно применять символы := или ключевое слово DEFAULT. Для приме ра перепишем процедуру присвоив по умолчанию всем но вым студентам профилирующую дисциплину "Экономика" (если это не переопределяется явным аргументом):
Этот пример содержится в файле CREATE OR REPLACE PROCEDURE AddNewStudent ( DEFAULT AS - Внесем новую строку в таблицу students. Воспользуемся - для того, чтобы - сгенерировать идентификатор для нового студента, и 0 для -- current_credits.
INSERT INTO students VALUES 0);
END AddNewStudent;
Если формальный параметр не связан в вызове процедуры с фактическим параметром, используется значение по умолчанию. Связать параметры можно при помощи позиционного представления:
Г] Ч Этот пример содержится в файле BEGIN END;
или именного представления:
..- Этот пример содержится в файле BEGIN => p_LastName => 'Vassily');
END;
Если применяется позиционное представление, то все параметры со значениями по умолчанию, не имеющие соответствующих фактических параметров, должны находиться в конце списка параметров. Рассмотрим следующий пример:
Этот пример содержится в файле sql CREATE OR REPLACE PROCEDURE DefaultTest ( NUMBER DEFAULT 10, p_ParameterB VARCHAR2 DEFAULT, DATE DEFAULT SYSDATE) AS BEGIN Создание процедур, функций и ' p_ParameterA С:
END DefaultTest;
Все три параметра процедуры DefaultTest используют аргументы, за данные по умолчанию. Если нужно задать значение по умолчанию только для параметра p_ParameterB, а для параметров и требуется указать некоторые значения, необходимо использовать имен ное представление:
Этот пример содержится в файле SQL> BEGIN 2 => 7, => 3 END;
4 / A: 7 abcdef C:
PL/SQL procedure succefully completed.
Если требуется использовать значение по умолчанию для параметра p_ParameterB и применять при этом позиционное представление, то для параметра также нужно указать значение по умолчанию. При использовании позиционного представления все параметры по умолча нию, не имеющие соответствующих фактических параметров, должны находиться в конце списка параметров. Приведем пример:
Г) -- Этот пример содержится в файле SQL> BEGIN 2 - Значения по умолчанию задаются как для параметра Л - так и для параметра 4 DefaultTest(7);
5 END;
6 / А: 7 В: abcdef С: 17-ОСТ- PL/SQL procedure succefully completed.
Совет Если возможно, указывайте значения по умолчанию последними в списке аргументов. В этом случае можно использовать как позиционное, так и именное представление.
Оператор добавляет новый оператор SQL для вызова хранимых подпрог рамм: оператор CALL. Он может использоваться для вызова подпрог рамм PL/SQL Java с помощью оболочки PL/SQL и имеет синтаксис:
CALL [INTO где Ч это автономная или модульная подпрограмма либо метод объектного типа, который может находиться в удаленной 338 ГЛАВА базе данных. Ч разделенный запятыми список аргумен тов, а используется для извлечения возвращаемого функцией значения. Следующий сеанс демонстрирует некото рые допустимые и недопустимые применения оператора CALL:
Х- Этот пример содержится в файле SQL> CREATE OR REPLACE PROCEDURE CallProd (p1 IN VARCHAR2 := NULL) AS 2 BEGIN 3 called with || p1);
4 END CallProd;
5 / Procedure SQL> CREATE OR REPLACE PROCEDURE CallProc2 (p1 IN OUT VARCHAR2) AS 2 BEGIN 3 called 4 p1 := p1 ' 5 END CallProc2;
6 / Procedure SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2) 2 RETURN VARCHAR2 AS 3 BEGIN 4 called with || p1);
5 RETURN 6 END CallFunc;
Pages: | 1 | ... | 2 | 3 | 4 | 5 | 6 | ... | 7 | Книги, научные публикации