Константы, переменные и типы в PL/SQL
Переменная или константа определяется своим именем, которое является допустимым именем в СУБД Oracle. Любая константа или переменная должна иметь один из допустимых в СУБД Oracle типов. Константа идентифицируется ключевым словом CONSTANT и отличается от переменной тем, что попытка изменить ее значение в программе приведет к ошибке.
Для определения констант и переменных используется следующий синтаксис:
Имя [CONSTANT] тип данных [:= значение]; Пример. Рассмотрим пример простой программы, которая вычисляет значение синуса двух углов, кратных p.
-- переменные окружения set serveroutput on; set echo on; DECLARE Pi CONSTANT real :=3.14; x real :=1; BEGIN DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); x:=x+1; DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); END; /
Установка переменных окружения определяет режим вывода на терминал пользователя. Системная процедура DBMS_OUTPUT.PUT_LINE обеспечивает вывод данных на терминал. Символ / указывает на завершение текста программы и является командой к выполнению программы.
Курсоры PL/SQL
Курсор - это поименованный запрос, содержащий некоторое число строк в выборке. По сути, курсор является некоторой структурой, через которую пользователь получает доступ к строкам результирующей таблицы запроса.
Рассмотрим пример доступа к информации, которая хранится в базе данных, с использованием курсоров.
Пусть в базе данных есть таблица базы данных с именем T01, такая, как показана ниже.
| A1 number | 1 | 2 | 3 |
| A2 varchar2(5) | abc | cba | bca |
| A3 char(1) | A | B | C |
Опишем курсор для доступа к данным таблицы Т01.
CURSOR cur01 IS SELECT * FROM T01;
Работа с курсором выполняется по следующему алгоритму:
Открываем курсор:
OPEN cur01;
Выбираем данные из курсора в набор совместимых по типу переменных командой FETCH:
FETCH cur01 INTO x1,x2,x3;
Обрабатываем полученные данные.Выполняем команду FETCH для получения данных из следующей строки результирующей таблицы запроса.
И т.д.
В PL/SQL для курсоров предусмотрено несколько методов. Метод %NOTFOUND возвращает булевское истинное значение, если выборка в курсор пуста. Метод %FOUND возвращает булевское истинное значение, если выборка в курсор непуста. После открытия курсора до первой команды FETCH значения, возвращаемые этими методами, равны NULL. Метод %ROWCOUNT возвращает число строк в выборке после открытия курсора.
Предопределенный в PL/SQL метод %TYPE позволяет определить тип переменной как совпадающий с типом переменной таблицы.
PL/SQL поддерживает тип данных RECORD, который позволяет создать объект, соответствующий строке таблицы, как показано в примере ниже.
DECLARE TYPE t01_rec_type IS RECORD ( x1 t01.A1%TYPE, x2 t01.A2%TYPE, x3 t01.A3%TYPE); t01_rec t01_rec_type; … FETCH cur1 INTO t01_rec; DBMS_OUTPUT.PUT_LINE (cur1%ROWCOUNT||' '||t01_rec.x2); ѕ.
Обработка исключительных ситуаций в PL/SQL
Исключительная ситуация - это возникновение предопределенного и описанного события в системе. Например, ошибки преобразования типов переменных или переполнения при делении на нуль. Пример некоторых предопределенных ситуаций, распознаваемых в PL/SQL, приведен в таблице 12.1 ниже. Для получения полного списка таких ситуаций следует обратиться к документации по PL/SQL.
| NOT_LOGGED_ON | Попытка выполнить действие без подключения к серверу |
| INVALID_CURSOR | Ссылка на недопустимый курсор или недопустимая операция с курсором |
| NO_DATA_FOUND | Не найдены данные, соответствующие команде SELECT INTO |
| DUP_VAL_ON_INDEX | Попытка вставить дубликат значения в колонку с ограничением на уникальное значение |
| VALUE_ERROR | Арифметическая ошибка, ошибка усечения или преобразования |
Операторы управления выполнением программы PL/SQL
Операторы PL/SQL выполняются последовательно. Такая схема называется потоком команд. Изменить последовательный порядок выполнения команд можно с помощью команд управления потоком - оператором ветвления, оператором цикла и командой выхода из цикла.
Оператор IF условие TNEN группа операторов 1 ELSE группа операторов 2 позволяет проверить условие и в зависимости от результата проверки выполнить различные группы операторов. Если условие принимает значение TRUE, то выполняется группа операторов 1, в противном случае - группа операторов 2. Границы действия оператора IF определяются закрывающейся операторной скобкой END IF. Для расширения структуры ветвления предусмотрена операторная скобка ELSIF.
Синтаксис оператора ветвления следующий:
IF Условие THEN группа операторов 1 ELSIF условие 1 THEN Группа операторов 3 ELSIF … ELSE группа операторов 2 END IF Пример. Изменение потока команд
DECLARE Pi CONSTANT real :=3.14; x real :=1; BEGIN x:=Input_Data; IF (x>0.5) THEN DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); ELSIF (x< 0.4) THEN DBMS_OUTPUT.PUT_LINE ('y ='|| cos(Pi*x)); ELSE x:=x+1; DBMS_OUTPUT.PUT_LINE ('y ='|| sin(Pi*x)); END IF; END; /
Переменная, имени которой предшествует знак &, вводится с терминала пользователя.
Организация цикла в программах на PL/SQL может быть выполнена несколькими способами. Рассмотрим примеры вычисления суммы.
Пример. Оператор LOOP.
DECLARE X number; I number; Limit number:=1000; BEGIN I:=0; X:=0; LOOP EXIT WHEN I > Limit; I:=I+1; X:=X+I*I; END LOOP; DBMS_OUTPUT.PUT_LINE (x); END; /
Оператор LOOP открывает цикл. Конструкция EXIT WHEN обеспечивает выход из цикла при выполнении условия, а закрывающая операторная скобка END LOOP завершает цикл.
Пример. Оператор WHILE
DECLARE X number; I number; Limit number:=1000; BEGIN I:=0; X:=0; WHILE I <= Limit LOOP I:=I+1; X:=X+I*I; END LOOP; DBMS_OUTPUT.PUT_LINE (x); END; /
Управление потоком команд типа WHILE обеспечивает выполнение цикла до тех пор, пока условие является истинным.
Пример. Оператор FOR
DECLARE X number; Limit number:=1000; BEGIN I:=0; X:=0; FOR I IN 0..Limit LOOP X:=X+I*I; END LOOP; DBMS_OUTPUT.PUT_LINE (x); END; /
Цикл, управляемый оператором FOR, используется, когда точно известно, сколько раз нужно выполнить операторы цикла. Переменную цикла описывать в блоке DECLARE не нужно.
Определение хранимых процедур и функций в PL/SQL
Процедура или функция PL/SQL имеет уникальное имя. Как и программы на PL/SQL, процедуры и функции имеют блок объявлений, блок исполняемого кода и, опционально, блок обработки исключительных ситуаций. Но процедура может принимать и возвращать значения параметром, а функция дополнительно возвращает значение.
Описание процедуры имеет следующий синтаксис:
PROCEDURE имя [(параметр [, параметр, ...])] IS [объявление локальных переменных, пользовательских типов данных, пользовательских исключительных ситуаций, локальных подпрограмм и функций] BEGIN Исполняемый код [EXCEPTION обработчики исключительных ситуаций] END [имя];
Параметры процедуры обеспечивают входные и выходные данные процедуры. Параметров у процедуры может и не быть, так что этот раздел не является обязательным. Для задания параметров используется следующий синтаксис:
Имя параметра [IN | OUT | IN OUT] тип данных [{:= | DEFAULT} выражение]
В определении параметров нельзя использовать ограничение NOT NULL, а в определении типа данных нельзя использовать никакие ограничения. Для каждого параметра должен быть указан его тип (parameter mode) - IN, OUT или IN OUT. Указание типа IN означает, что значение параметра определяется при обращении к процедуре и не изменяется процедурой. Попытка изменить такой параметр в теле процедуры приведет к возникновению ошибки. Указание типа OUT предполагает изменение значения параметра в процессе выполнения процедуры, т.е. это возвращаемый параметр. Указание типа IN OUT говорит о том, что при вызове процедуры такому параметру должно быть присвоено значение, которое может быть изменено в теле процедуры. Типом по умолчанию считается IN. Ниже в таблице 12.2 суммирована информация о типах параметров.
| Умолчание | Должен быть задан | Должен быть задан |
| Передает значение в процедуру или функцию | Возвращает значение из процедуры или функции | Передает значение в процедуру или функцию и возвращает измененное значение |
| Формальный параметр действует как константа | Формальный параметр действует как неинициализированная переменная | Формальный параметр действует как неинициализированная переменная |
| Формальному параметру не может быть присвоено значение | Формальный параметр не может быть использован в выражении, и ему должно быть присвоено значение | Формальному параметру можно присваивать значение |
| Действительный параметр может быть константой, инициализированной переменной, литеролом или выражением | Действительный параметр должен быть переменной | Действительный параметр должен быть переменной |
Определение процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END. Тело процедуры состоит из трех частей, которые отвечают блокам программы PL/SQL.
Пример. Для нашей учебной базы данных можно рассмотреть вопрос о создании процедуры, которая увеличивает зарплату сотрудника по значению его номера.
PROCEDURE raise_salary (empid INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE employee SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с таким номером'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
При вызове процедура принимает номер сотрудника и величину прибавки к зарплате в качестве параметров. Номер сотрудника используется далее для выборки значения текущей зарплаты из таблицы EMPLOYEE в локальную переменную current_salary. Если номер сотрудника не найден в базе данных или значение зарплаты не установлено, то возникают исключительные ситуации, стандартная и определенная пользователем соответственно. В противном случае зарплата обновляется.
Процедура вызывается как команда PL/SQL
raise_salary (emp_num, amount);
В примере использована вспомогательная таблица базы данных emp_audit для отслеживания возникновения ошибочных ситуаций при работе с базой данных.
Рассмотрим еще один пример использования процедуры для выборки зарплаты служащего по его фамилии из таблицы EMPLOYEE учебной базы данных.
PROCEDURE emp_salary (sName INTEGER, sal_p OUT REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE ENAME=:sName'; IF current_salary IS NULL THEN RAISE salary_missing; ELSE sal_p = current_salary END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с такой фамилией'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
Определение процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END. Тело процедуры состоит из трех частей, которые отвечают блокам программы PL/SQL.
Пример. Для нашей учебной базы данных можно рассмотреть вопрос о создании процедуры, которая увеличивает зарплату сотрудника по значению его номера.
PROCEDURE raise_salary (empid INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ELSE UPDATE employee SET sal = sal + increase WHERE empno = emp_id; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с таким номером'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
При вызове процедура принимает номер сотрудника и величину прибавки к зарплате в качестве параметров. Номер сотрудника используется далее для выборки значения текущей зарплаты из таблицы EMPLOYEE в локальную переменную current_salary. Если номер сотрудника не найден в базе данных или значение зарплаты не установлено, то возникают исключительные ситуации, стандартная и определенная пользователем соответственно. В противном случае зарплата обновляется.
Процедура вызывается как команда PL/SQL
raise_salary (emp_num, amount);
В примере использована вспомогательная таблица базы данных emp_audit для отслеживания возникновения ошибочных ситуаций при работе с базой данных.
Рассмотрим еще один пример использования процедуры для выборки зарплаты служащего по его фамилии из таблицы EMPLOYEE учебной базы данных.
PROCEDURE emp_salary (sName INTEGER, sal_p OUT REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM employee WHERE ENAME=:sName'; IF current_salary IS NULL THEN RAISE salary_missing; ELSE sal_p = current_salary END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника с такой фамилией'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена'); END raise_salary;
Особенности использования процедур и функций в СУБД Oracle
В этом подразделе рассмотрим некоторые особенности использования процедур и функций в PL/SQL:
Способы связывания формальных и фактических параметров в процедурах и функциях.Использование для параметров значений по умолчанию.Возникновение алиасных имен при передаче параметров.Перезагрузка имен процедур и функций.
При вызове процедур и функций в PL/SQL допускается передача параметров по имени и по позиции. Это означает, что вы указываете, как происходит связывание формальных и действительных параметров. Например, пусть имеется гипотетическая программа
DECLARE x1 INTEGER; x2 REAL; PROCEDURE proc1 (p1 INTEGER, p2 REAL) IS BEGIN ... END;
Процедуру Proc1 можно вызвать следующими эквивалентными способами:
BEGIN ... proc1 (x1, x2); -- передача параметров по позиции proc1 (p2 => x2, p1 => x1); -- передача параметров по имени proc1 (p1 => x1, p2 => x2); -- передача параметров по имени proc1 (x1, p2 => x2); -- передача параметров и по -- позиции, и по имени END;
При передаче параметра по позиции компилятор PL/SQL последовательно связывает первый фактический параметр с первым формальным параметром, второй фактический параметр - со вторым формальным параметром и так далее.
При передаче параметра по имени стрелка, называемая оператором связывания (association operator), связывает формальный параметр слева от стрелки с фактическим параметром справа от стрелки, причем порядок следования таких пар не имеет значения.
Можно комбинировать передачу параметра по позиции и по имени. Но при этом следует соблюдать требование того, чтобы передача параметра по позиции предшествовала передаче параметра по имени.
В процедуре или функции можно инициализировать параметр типа IN значением по умолчанию. Таким способом можно передавать различное число действительных параметров в процедуры или функции, принимая или переопределяя значение по умолчанию. Рассмотрим пример.
Пример. Для нашей учебной базы данных разработаем процедуру создания нового отдела таблице DEPARTAMENT.
PROCEDURE create_dept (new_dname CHAR DEFAULT 'Новый', new_loc CHAR DEFAULT 'Москва') IS BEGIN INSERT INTO departament VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;
Если при вызове процедуры фактический параметр не передается, то используется значение по умолчанию. Рассмотрим следующие вызовы этой процедуры:
create_dept; create_dept('Маркетинг'); create_dept('Маркетинг', Черноголовка);
При первом вызове не передается никаких параметров, так что используются значения параметра по умолчанию - Новый отдел в г. Москве. Во втором вызове передается один параметр - первый. Для второго используется значение по умолчанию. В третьем случае передаются оба параметра и значения по умолчанию не используются.
Для оптимизации выполнения процедур и функций компилятор PL/SQL может выбирать различные методы передачи параметров (по значению или по ссылке) для различных параметров в одном и том же вызове процедуры или функции. Когда компилятор выбирает метод передачи параметра по значению, то его значение копируется в процедуре или функции. Когда компилятор выбирает метод передачи параметра по ссылке, то адрес фактического параметра передается в процедуру или функцию. Может создаться ситуация когда переменная в процедуре или функции будет иметь два имени - так называемая проблема алиасных имен. В этом случае результат будет неопределенным. Это происходит, когда глобальная переменная передается по адресу, как в примере ниже.
Пример
DECLARE rent REAL; PROCEDURE raise_rent (increase IN OUT REAL) IS BEGIN rent := rent + increase; -- в случае передачи параметра по адресу -- одна и та же переменная будет иметь два имени. END raise_rent; BEGIN ... raise_rent(rent); -- indeterminate
Такая ситуация может также возникнуть, когда фактическая переменная дважды передается в процедуру или функцию. Если оба формальных параметра имеют тип IN, то результат не определен.
Пример. Рассмотрим процедуру реверсирования строки.
DECLARE str VARCHAR2(10); PROCEDURE reverse (in_str VARCHAR2, out_str OUT VARCHAR2) IS BEGIN ... END reverse; ... BEGIN str := 'abcd'; reverse(str, str); -- Не определен
Результат выполнения процедуры будет зависеть от выбранного компилятором способа передачи параметра в процедуру.
Компилятор PL/SQL позволяет также перезагружать (overload) имена процедур и функций, т.е. вы можете использовать одно и то же имя для нескольких различных процедур или функций. При этом число параметров, их порядок и типы данных могут быть различными.
Таким образом, основными преимуществами использования процедур и функций в базах данных являются:
обеспечение расширяемости, т.е. они расширяют декларативный язык SQL возможностями процедурной обработки;обеспечение модульности, т.е. позволяют использовать поэтапный подход обработки данных из базы данных на основе хорошо определенной бизнес-логики;создание серверного кода базы данных с целью увеличения производительности за счет сокращения сетевого трафика и накладных расходов на компиляцию кода.
Создание хранимых процедур и функций
Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL, который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.
Обычно хранимые процедуры используются для выполнения набора типовых действий, характерных для достижения целей создания базы данных, а хранимые функции - для вычисления значений.
Код хранимых процедур и функций хранится в базе данных. Поэтому использование хранимых процедур и функций для выполнения типовых операций, характерных для приложения базы данных, приводит к упрощению приложений за счет передачи обработки на сервер базы данных. Такой подход уменьшает накладные расходы за счет сокращения фазы синтаксического анализа и уменьшения времени передачи запроса на конкретное действие по вычислительной сети, что приводит в целом к снижению трафика сети. Обычно при этом улучшается производительность, но это необязательно для маломощных серверов баз данных.
Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP. Команда создания хранимой процедуры имеет следующий синтаксис:
CREATE [OR REPLACE] PROCEDURE [имя схемы].имя процедуры [имя [(параметр [, параметр, ...])] {IS|AS} программа на PL/SQL;
Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если процедура с таким именем уже определена, но ключевое слово OR REPLACE не указано, то возвращается сообщение об ошибке и замещения старого текста не происходит.
При описании переменных хранимой процедуры ключевое слово DECLARE не используется. Блок описания переменных начинается сразу после ключевого слова IS или AS.
Пример.
PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;
Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL, как показано ниже:
EXEC ;
Команда создания хранимой процедуры имеет следующий синтаксис:
CREATE [OR REPLACE] FUNCTION [имя схемы].имя функции [имя [(параметр [, параметр, ...])] RETURN тип данных {IS|AS} программа на PL/SQL;
Как можно увидеть, синтаксис команды создания хранимой функции похож на синтаксис команды создания хранимой процедуры, за исключением того, что в описании функции используется предложение RETURN. Описание типа данных для возвращаемого значения требуется обязательно.
Пример. Для нашей учебной базы данных создадим функцию, которая считает число сотрудников, поступивших в организацию за определенный период:
CREATE OR REPLACE FUNCTION emp_cnts (data1 IN date, data2 IN date) RETURN Integer IS I_count number:=0; BEGIN SELECT COUNT(*) INTO i_count FRON employee WHERE hiredate BETWEEN date1 AND date2; RETURN i_count; END;
Вызвать созданную хранимую функцию можно следующим образом:
EXEC DBMS_OUTPUT.PUT_LINE(emp_cnts('01-may-02', ''01-jul-02'));
Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;
Или
DROP [имя схемы].имя функции;
Создание пакетов PL/SQL
Процедуры, функции и глобальные переменные, объединенные общим функциональным замыслом, часто оформляются в виде специального объекта базы данных - пакета. Прием оформления родственных программ в пакет хорошо известен из программистской практики. Особенностью PL/SQL является раздельная компиляция и хранение интерфейсной и исполнительной частей пакета. Таким образом, пакет (package) есть объект базы данных, который группирует логически связанные типы, объекты, процедуры и функции PL/SQL. Пакет состоит из двух частей: спецификации пакета и тела пакета, хотя тело пакета иногда опускается. Спецификация пакета есть интерфейс для вашего приложения. В ней хранится описание процедур, функций, глобальных переменных, констант и курсоров, которые доступны из внешних приложений. Тело пакета полностью определяет курсоры, процедуры и функции, являясь, таким образом, реализацией спецификации. В теле пакета могут также быть определены переменные, курсоры, процедуры и функции. Они являются локальн ыми, т.е. не доступными из внешних приложений.
Синтаксис определения пакета в базе данных состоит также из двух частей. Оператор создания интерфейсной части имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE [имя схемы].имя AS Определения типов и объектов Спецификации процедур и функций END [имя];
Ключевое слово OR REPLACE указывает на безусловное замещение предыдущего кода спецификации пакета. Если оно не указано, а пакет определен в базе данных, то замещения старого значения спецификации пакета не происходит и возвращается сообщение об ошибке.
Спецификация пакета начинается с объявления констант и переменных, при этом ключевое слово DECLARE не используется. Рассмотрим пример создания спецификации пакета.
Пример
CREATE OR REPLACE PACKAGE paket1 AS A1 CONSTANT number:= 1.3; PROCEDURE Pr1; FUNCTION F01 (x1 real) RETURN real; END; /
Оператор создания тела пакета имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE BODY [имя схемы].имя AS Объявления локальных типов и переменных Тела процедур и функций [BEGIN команды инициализации END [имя];
Ключевое слово OR REPLACE указывает на безусловное замещение предыдущего кода тела пакета. Если оно не указано, а пакет определен в базе данных, то замещения старого значения тела пакета не происходит и возвращается сообщение об ошибке.
Рассмотрим пример создания тела пакета для спецификации, определенной в предыдущем примере.
Пример. Пусть функция пакета F01 умножает аргумент на заданное число, а процедура Pr1 фиксирует факт обращения к функции пакета в таблице базы данных. Предполагается, что таблица T01 уже существует.
CREATE OR REPLACE PACKAGE BODY paket1 AS Cnt number:=0; FUNCTION F01(x1 real) RETURN real IS BEGIN Pr1; RETURN x1*A1; END; PROCEDURE Pr1 IS BEGIN Cnt:=Cnt+1; INSERT INTO T01 VALUES(Cnt,SYSDATE); COMMIT; END; END; /
Отметим, что инициализация локальных переменных, таких как переменная Cnt примера, происходит при запуске сервера СУБД Oracle.
Константа или переменная, определенная в спецификации пакета, может быть доступна из пользовательской программы, при условии, если такой доступ разрешен администратором базы данных. Чтобы обратиться к глобальной переменной или константе пакета, нужно использовать квалифицируемое имя, указав в качестве префикса к имени переменной или константы имя пакета. Аналогично происходит обращение к процедурам или функциям пакета, определенных в спецификации пакета. Например,
package_name.type_name package_name.object_name package_name.subprogram_name
Чтобы уничтожить пакет для освобождения ресурсов сервера, используется команда SQL DROP в следующем формате:
DROP PACKAGE [BODY] [имя схемы].имя пакета;
Необязательное ключевое слово BODY указывает, что уничтожается только тело пакета, в противном случае удаляется и спецификация, и тело пакета.
Что дает проектировщику базы данных использование пакетов? Пакеты обладают рядом преимуществ, к которым принято относить следующие:
Модульность. Пакеты позволяют проектировщику базы данных инкапсулировать логически связанные типы данных, объекты, процедуры и функции в поименованный модуль PL/SQL.
Использование пакетов позволяет проектировщику базы данных сделать приложения проще, яснее и хорошо определенными. Поскольку каждый пакет в отдельности проще для понимания, связи между пакетами через хорошо определенные интерфейсы яснее и проще.Упрощение проектирования приложений базы данных. Когда проектировщик базы данных проектирует приложение, ему в случае использования пакетов необходимо сначала спроектировать только интерфейсную часть пакетов, т.е. определить спецификации пакета, а только потом разрабатывать тело пакета. Поскольку компиляция спецификации и тела пакета происходит раздельно, то нет необходимости полностью определять тело пакета до тех пор, пока проектировщик базы данных не завершит проектирования приложения базы данных.Скрытие информации. При разработке пакета проектировщик базы данных может определить, какие типы, объекты, процедуры и функции будут доступны внешним приложениям, а какие будут локальными в пакете, т.е. скрытыми от внешних программ. Этот прием упрощает сопровождение и расширение приложений базы данных в дальнейшем. В частности, скрытие деталей реализации пакета от пользователей защищает целостность пакета.Дополнительная функциональность. Глобальные переменные и курсоры пакета сохраняются в течение сессии работы пользователя с базой данных. Поэтому они могут разделяться всеми процедурами и функциями, которые могут выполняться в программной среде сессии. Они также позволяют сопровождать некоторые данные транзакции без сохранения их в базе данных.Улучшение производительности базы данных. Когда элементы пакета вызываются в первый раз, пакет полностью загружается в оперативную память сервера. Следовательно, последующие вызовы процедур и функций пакета будут происходить без обращения к диску, что сократит число операций ввода/вывода.
Рассмотрим для нашей учебной базы данных пример принятия решений проектировщиком базы данных о создании пакета.
Предположим, что в организации высокая степень текучести кадров. Пользователь базы данных, который работает с персоналом, проводит одну сессию работы с базой данных в начале недели.
В рамках этой сессии он фиксирует в базе данных новых сотрудников, которые приступают к работе с понедельника (бизнес-правило организации - принимать на работу только с понедельника), и удаляет информацию о сотрудниках из базы данных (бизнес-правило организации - не сохранять информацию об уволенном сотруднике в базе данных), которые ушли из организации. Тогда проектировщик базы данных может рассмотреть создание пакета, содержащего две процедуры: одну для занесения информации о сотруднике в базу данных, а другую - для удаления информации о сотруднике из базы данных. Код пакета может быть таким, как показано ниже.
CREATE PACKAGE emp_actions AS TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary RETURN EmpRecTyp; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions;
CREATE PACKAGE BODY emp_actions AS CURSOR desc_salary RETURN EmpRecTyp IS SELECT empno, sal FROM employee ORDER BY sal DESC; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO employee VALUES (empno, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employee WHERE empno = emp_id; END fire_employee; END emp_actions;
Создание триггеров PL/SQL
Триггер базы данных (database trigger) является объектом реляционной базы данных, который активизирует выполнение хранимой (или встроенной) PL/SQL-процедуры при изменении пользователем данных в таблице. Событие, управляющее запуском триггера, описывается в виде логических условий. Например, попытка модифицировать данные в таблице активизирует триггер, соответствующий данной команде манипулирования данными. Число триггеров на таблицу базы данных не ограничено.
Обычно триггеры используют для реализации ограничений ссылочной целостности, для предотвращения несогласованных изменений в базе данных (поддержка целостности базы данных), для выполнения скрытых операций при модификации, а также для снижения сетевого трафика за счет передачи обработки на сервер. Операции завершения транзакции выполняются после обработки триггеров.
При выполнении команды UPDATE с помощью триггера можно проверить, что модифицируемые данные удовлетворяют ограничениям целостности базы данных до выполнения операции (при этом возможен доступ к новым данным!). После выполнения операции с помощью триггера можно выполнить скрытую обработку данных с учетом поступивших изменений (старые данные также могут быть доступны).
При выполнении команды INSERT также можно проверить данные до вставки в таблицу на допустимость ограничениям целостности, а после - выполнить операции над только что вставленными данными.
При выполнении команды DELETE можно проверить данные до их удаления или восстановить данные после удаления.
Для создания триггера предусмотрена специальная команда SQL CREATE TPIGGER. Эта команда создает триггер на таблице, которой владеет пользователь. Невозможно создать триггер для виртуальной таблицы.
Синтаксис команды следующий:
CREATE [OR REPLACE] TPIGGER [имя схемы.]имя триггера {BEFORE|AFTER} {INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]} [OR {INSERT|DELETE|UPDATE [OF имя колонки [, имя колонки ѕ]]}] ON [имя схемы.]{имя таблицы|имя представления} {FOR EACH ROW][WHEN условие] спецификация пакета на PL/SQL
Ключевое слово OR REPLACE указывает на безусловное замещение старого теста триггера. Если оно не указывается, а триггер определен в базе данных, то замещения старого триггера не происходит, и возвращается сообщение об ошибке.
Определение триггера состоит из нескольких частей:
задание имени триггера;указание команды SQL, к которой относится триггер;указание таблицы или представления, для которой определяется триггер;задание ограничений триггера;задание действия в теле триггера.
Если имя схемы опущено, то триггер создается в схеме текущего пользователя.
Первая часть - это указание команды, которая запускает триггер. При создании триггера необходимо указывать, к какой команде манипулирования данными он относится - INSERT, DELETE или UPDATE. Для последней модно указывать конкретные колонки, указав фразу OF имя_колонки [, имя_колонки ...] в предложении UPDATE.
Ключевое слово ON задает имя таблицы или представления, для которого создается триггер.
Ограничения триггера указывают дополнительные условия, которые должны удовлетворяться для строки, чтобы триггер запустился.
Триггер имеет время действия - до или после выполнения команды манипулирования данными, - которое задается ключевыми словами BEFORE или AFTER, соответственно. Обычно нельзя определить два триггера на одну и ту же операцию с одинаковым временем действия.
Необязательное ключевое слово ON EACH ROW определяет триггер как строчный, т.е. запускаемый для каждой строки результирующего множества команды SQL. Если оно опущено, то триггер запускается только один раз в начале обработки команды. Таким образом, условие "для каждой строки" активизируется, только когда есть строки (например, предложение WHERE дает истинное значение условий поиска), в то время как для условия "для каждой команды" триггер сработает и в этом случае.
Дополнительные условия, сужающие область действия триггера, могут быть заданы в предложении WHEN. Условия, задаваемые в этом предложении, являются стандартными для SQL условиями, должны содержать корреляционные имена и не могут содержать запрос.
Это предложение может быть указано только для строчного триггера.
Действие, которое выполняет триггер, задается в теле триггера блоком кода PL/SQL, который не может содержать команд управления транзакциями, таких как COMMIT, ROLLBACK и SAVEPOINT.
Для того чтобы задать корреляционные имена, т.е. чтобы видеть старые и новые значения колонок при обновлении, нужно воспользоваться предложением REFERENCING OLD AS имя_таблицы_старых_значений NEW AS имя_таблицы_новых_значений. Определяемые имена являются псевдонимами для обновляемой таблицы и должны быть различны. На эти имена можно ссылаться в теле триггера. По умолчанию корреляционные имена есть OLD и NEW для старого и нового значения строки.
С каждым триггером можно связать его тип. Тип триггера определяется следующими условиями:
когда запускается триггер (т.е. какое у него время действия);сколько раз запускается триггер.
Таким образом, тип триггера определяется всевозможными комбинациями ключевых слов BEFORE, AFTER и FOR EACH ROW, что дает четыре основных типа триггера, как показано в таблице 12.3 ниже.
| BEFORE | BEFORE: СУБД запускает триггер до выполнения команд | BEFORE: СУБД запускает триггер до модификации каждой строки, обрабатываемой командой |
| AFTER | AFTER: СУБД запускает триггер после выполнения команды | AFTER: СУБД запускает триггер после модификации каждой строки, обрабатываемой командой |
Пример
CREATE TRIGGER emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON employee DECLARE dummy INTEGER; BEGIN /* Если сегодня суббота или воскресенье, то ошибка. */ IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR TO_CHAR(SYSDATE, 'DY') = 'SUN') THEN raise_application_error( -20501, 'Можно изменять таблицу employee только в рабочие дни'); END IF; /* Если праздник, то тоже ошибка */ SELECT COUNT(*) INTO dummy FROM company_holidays WHERE day = TRUNC(SYSDATE); IF dummy > 0 THEN raise_application_error( -20501, 'Нельзя изменять таблицу employee по праздникам'); END IF; /* Если текущее время меньше 8:00 часов утра или 5:00 часов вечера, то ошибка */ IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR TO_CHAR(SYSDATE, 'HH24') >= 17) THEN raise_application_error( -20502, 'Нельзя изменять таблицу employee в нерабочие часы'); END IF; END;
Структура программы на PL/SQL
Модельным прототипом для создания языка PL/SQL послужил язык программирования ADA, поэтому он обладает набором средств, характерных для любого современного языка программирования. Всякая программа на PL/SQL состоит из трех блоков: блока описаний, блока исполняемого кода и блока обработки исключительных ситуаций. Блок исполняемого кода может быть структурирован с помощью операторных скобок BEGIN … END.
Синтаксически программа на PL/SQL оформляется следующим образом:
DECLALE Описание переменных и констант BEGIN Операторы EXCEPTION Операторы END;
Перед блоком DECLALE могут располагаться команды установки переменных окружения. В блоке DECLALE описываются константы, переменные и определенные пользователем типы данных. Первый оператор BEGIN отмечает начало тела основной программы. В тело программы могут быть включены другие блоки, ограниченные операторными скобками. Блок EXCEPTION определяет фрагменты программного кода для обработки исключительных ситуаций в программе. Последний оператор END указывает конец тела программы. В любые части программы могут быть включены комментарии, т.е. текст, который начинается с символов -- и продолжается до конца текущей строки. Строка, начинающаяся с ключевого слова REM, также рассматривается как комментарий.
