----------------------------------------------------------------
ЧАСТЬ I
РУКОВОДСТВО ПОЛЬЗОВАТЕЛЯ
ГЛАВА 1
----------------------------------------------------------------
ОБЩИЕ СВЕДЕНИЯ
Границы моего языка означают границы моего мира.
Людвиг Виттгенштейн
PL/SQL включил в себя многие достижения языков программирования,
появившиеся за 1970-е и 1980-е годы. В этой главе
обрисовываются основные свойства PL/SQL и указываются
преимущества, предлагаемые этими свойствами. Эта глава также
знакомит вас с концепциями, на которых основан PL/SQL, и общим
видом программ PL/SQL. Вы увидите, как PL/SQL закрывает
промежуток между технологией баз данных и процедурными языками
программирования.
Общие сведения 1-1
----------------
Основные свойства
Хороший способ познакомиться с PL/SQL - это взглянуть на
приведенную ниже программу. Эта программа обрабатывает заказ на
теннисные ракетки. Прежде всего она объявляет переменную типа
NUMBER, в которой будет храниться количество имеющихся ракеток.
Затем она извлекает это количество из таблицы базы данных с
именем inventory. Если ракетки есть, программа обновляет
таблицу, чтобы отразить продажу теннисной ракетки, и вставляет
запись о продаже в другую таблицу, purchase_record. В противном
случае программа вставляет в purchase_record запись о том, что
ракетки кончились.
-- доступен на диске в файле EXAMP1
DECLARE
num_in_stock number(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = 'TENNIS RACKET'
for UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- проверить количество
UPDATE inventory SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purcase_record
VALUES ('Tennis racket purchased.', SYSDATE);
ELSE
INSERT INTO purcase_record
VALUES ('Out of tennis rackets.', SYSDATE);
END IF;
COMMIT;
END;
PL/SQL не только позволяет вам вставлять, удалять, обновлять и
извлекать данные ORACLE и управлять потоком предложений для
обработки этих данных. Более того, вы можете объявлять
константы и переменные, определять подпрограммы (процедуры и
функции) и перехватывать ошибки времени выполнения. Таким
образом, PL/SQL комбинирует мощь манипулирования данными SQL с
мощью обработки данных процедурных языков.
1-2 Руководство пользователя и справочник по PL/SQL
Структура блоков
----------------
PL/SQL - это язык, структурированный блоками. Это значит, что
основные единицы (процедуры, функции и анонимные блоки),
составляющие программу PL/SQL, являются логическими БЛОКАМИ,
которые могут содержать любое число вложенных в них подблоков.
Обычно каждый логический блок соответствует некоторой проблеме
или подпроблеме, которую он решает. Таким образом, PL/SQL
поддерживает подход к решению задач по принципу "разделяй и
властвуй", известный как ПОШАГОВОЕ УТОЧНЕНИЕ.
Блок (или подблок) позволяет вам группировать логически
связанные объявления и предложения. Благодаря этому вы можете
размещать объявления близко к тем местам, где они используются.
Объявления локальны в блоке, и перестают существовать, когда
блок завершается.
Как показывает Рис.1-1, блок PL/SQL имеет три части:
декларативную часть, исполняемую часть и часть обработки
исключений. (ИСКЛЮЧЕНИЕМ в PL/SQL называется условие,
вызывающее предупреждение или ошибку.) Исполняемая часть
обязательна; две остальные части блока могут отсутствовать.
Рис.1-1
Структура блока PL/SQL
Блок PL/SQL
----------------------------------------------¬
¦ DECLARE ¦
¦ ---------------------------------¬ ¦
¦ ¦ Объявления ¦ ¦
¦ ¦ ¦ ¦
¦ L--------------------------------- ¦
¦ BEGIN ¦
¦ ---------------------------------¬ ¦
¦ ¦ Выполняемые предложения ¦ ¦
¦ ¦ ¦ ¦
¦ L--------------------------------- ¦
¦ EXCEPTION ¦
¦ ---------------------------------¬ ¦
¦ ¦ Обработчики исключений ¦ ¦
¦ ¦ ¦ ¦
¦ L--------------------------------- ¦
¦ END; ¦
L----------------------------------------------
Порядок частей блока логичен. Блок начинается с декларативной
части, в которой объявляются объекты. С объявленными объектами
осуществляются манипуляции в исполнительной части. Исключения,
возбуждаемые во время исполнения, могут быть обработаны в части
обработки исключений.
Каждый блок может содержать другие блоки; иными словами, блоки
могут быть вложены друг в друга. Вложенный блок называется
подблоком; он вложен в окружающий блок. Вы можете вкладывать
блоки в исполнительной части или части обработки исключений
блока PL/SQL, но не в декларативной части. Кроме того, вы
можете определять локальные подпрограммы в декларативной части
любого блока. Однако вызывать локальные подпрограммы можно
только из того блока, в котором они определены.
Общие сведения 1-3
Переменные и константы
----------------------
PL/SQL позволяет вам объявить переменные и константы, а затем
использовать их в SQL и процедурных предложениях в любом месте,
где допускается использование выражения. Однако ссылки вперед
не допускаются. Таким образом, вы должны объявить переменную
или константу прежде, чем сможете ссылаться на нее в других
предложениях, в том числе в других объявлениях.
Объявления переменных
Объявляемая переменная может иметь любой тип данных, присущий
SQL, такой как NUMBER, CHAR и DATE, или присущий PL/SQL, такой
как BOOLEAN или BINARY_INTEGER. Например, предположим, что вы
хотите объявить переменную с именем part_no так, чтобы она могла
хранить 4-значные числовые значения, и переменную с именем
in_stock, которая может принимать будевские значения TRUE или
FALSE. Вы объявляете эти переменные так:
part_no NUMBER(4);
in_stock BOOLEAN;
Вы можете также объявлять записи и таблицы PL/SQL, используя
составные типы данных PL/SQL: RECORD и TABLE.
Присваивания переменным
Вы можете присваивать переменным значения двумя способами.
Первый способ использует оператор присваивания := (двоеточие, за
которым следует знак равенства). Слева от оператора
присваивания кодируется имя переменной, а справа - выражение.
Примеры правильных присваиваний:
tax := price * tax_rate;
bonus := current_salary * 0.10;
raise := TO_NUMBER(SUBSTR('750 raise', 1, 3));
valid := FALSE;
Второй способ присвоить значение переменной - это извлечь в нее
значение из базы данных посредством фразы INTO предложения
SELECT или FETCH. Например, вы можете заставить ORACLE
вычислить 10% премию при извлечении жалованья сотрудника:
SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
После этого значение переменной bonus можно использовать в
других вычислениях, либо вставить его в таблицу базы данных.
Объявления констант
Объявление константы аналогично объявлению переменной, с той
разницей, что вы должны добавить ключевое слово CONSTANT и
немедленно присвоить константе значение. Впоследствии никакие
присваивания константе не допускаются. В следующем примере вы
объявляете константу с именем minimum_balance:
minimum_balance CONSTANT REAL := 10.00;
1-4 Руководство пользователя и справочник по PL/SQL
Атрибуты
--------
Переменные и константы PL/SQL имеют АТРИБУТЫ, т.е. свойства,
позволяющие вам ссылаться на тип данных и структуру объекта, не
повторяя его объявление. Аналогичные атрибуты имеются у таблиц
и столбцов базы данных, что позволяет вам упростить объявления
переменных и констант.
Атрибут %TYPE
Атрибут %TYPE представляет тип данных переменной, константы или
столбца. Он особенно полезен при объявлении переменной, которая
ссылается на столбец из таблицы базы данных. Например,
предположим, что таблица books содержит столбец с именем title.
Чтобы дать переменной my_title тот же тип данных, что у столбца
title, не зная точного определения этого столбца в базе данных,
объявите my_title с использованием атрибута %TYPE:
my_title books.title%TYPE;
Такое объявление переменной имеет два преимущества. Во-первых,
вы не обязаны знать точный тип данных столбца title. Во-вторых,
если определение столбца title в базе данных изменится
(например, увеличится его длина), тип данных переменной my_title
изменится соответственно во время выполнения.
Атрибут %ROWTYPE
В PL/SQL для группирования данных используются записи. Запись
состоит из нескольких полей, в которых могут храниться значения
данных. Атрибут %ROWTYPE обозначает тип записи, представляющей
строку в таблице. Такая запись (т.е. переменная, объявленная с
атрибутом %ROWTYPE) может хранить целую строку данных, выбранную
из таблицы или извлеченную из курсора (что обсуждается позже).
Столбцы в строке таблицы и соответствующие поля в записи имеют
одинаковые имена и типы данных. В следующем примере вы
объявляете запись с именем dept_rec. Ее поля имеют те же имена
и типы данных, что и соответствующие столбцы в таблице dept.
DECLARE
dept_rec dept%ROWTYPE;
...
Для обращения к значениям полей записи вы используете
квалифицированные ссылки, как показывает следующий пример:
my_deptno := dept_rec.deptno;
Общие сведения 1-5
Если вы объявляете курсор, извлекающий, скажем, фамилию,
жалованье, дату приема и должность сотрудника, то PL/SQL
позволяет вам создать запись, содержащую такую же информацию.
Вы делаете это с помощью атрибута %ROWTYPE. Предположим, вы
записали следующие объявления курсора и записи:
DECLARE
CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp;
emp_rec c1%ROWTYPE;
...
При выполнении предложения
FETCH c1 INTO emp_rec;
значение столбца ename таблицы emp будет присвоено полю ename
записи emp_rec, значение столбца sal будет присвоено полю sal, и
т.д. Рис. 1-2 показывает, как мог бы выглядеть результат.
Рис.1-2
Запись %ROWTYPE
Запись emp_rec
-----------------¬
emp_rec.ename ------ ¦ JAMES ¦
+----------------+
emp_rec.sal ------ ¦ 950.00 ¦
+----------------+
emp_rec.hiredate ------ ¦ 03-DEC-81 ¦
+----------------+
emp_rec.job ------ ¦ CLERK ¦
L-----------------
1-6 Руководство пользователя и справочник по PL/SQL
Управляющие структуры
---------------------
Управляющие структуры составляют наиболее важное расширение
языка SQL в PL/SQL. Благодаря им вы не просто можете
манипулировать данными ORACLE, но можете управлять потоком
выполнения, используя предложения условного, итеративного и
последовательного управления выполнением, такими как
IF-THEN-ELSE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN и GOTO. В
совокупности, эти предложения могут обработать любую ситуацию.
Условное управление
Часто бывает необходимо предпринять альтернативные действия в
зависимости от обстоятельств. Предложение IF-THEN-ELSE
позволяет вам управлять тем, будет ли выполняться
последовательность предложений. Фраза IF проверяет условие;
фраза THEN определяет, что делать, если это условие истинно
(TRUE); фраза ELSE определяет, что делать, если это условие
ложно (FALSE) или пусто (NULL).
Рассмотрим следующую программу, которая обрабатывает банковскую
транзакцию. Прежде чем позволить вам изъять $500 со счета номер
3, эта программа проверяет, что на счету достаточно денег, чтобы
удовлетворить запрос. Если это так, программа дебитует счет; в
противном случае она вставляет запись в аудиторскую таблицу.
-- доступен на диске в файле EXAMP2
DECLARE
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(4) := 3;
debit_amt CONSTANT NUMBER(5,2) := 500.00;
BEGIN
SELECT bal INTO acct_balance FROM accounts
WHERE account_id = ACCT
FOR UPDATE OF bal;
IF acct_balance >= debit_amt THEN
UPDATE accounts SET bal = bal - debit_amt
WHERE account_id = acct;
ELSE
INSERT INTO temp VALUES
(acct, acct_balance, 'Insufficient funds');
-- вставить счет, текущий баланс и сообщение
END IF;
COMMIT;
END;
Использование результатов запросов для определения того, какое
из нескольких предложений SQL выполнять, является общей логикой
для многих приложений баз данных. Еще одна общая
последовательность - вставлять или удалять строку, только если в
другой таблице найдется соответствующая запись. Вы можете
сгруппировать эти типовые последовательности предложений SQL в
блок PL/SQL, использущий традиционную логику. Это может
улучшить производительность и упростить проверки целостности,
встраиваемые в приложения SQL*Forms.
Общие сведения 1-7
Итеративное управление
----------------------
Предложения LOOP позволяют вам выполнять последовательность
предложений несколько раз. Вы помещаете ключевое слово LOOP
непосредственно перед первым предложением той последовательности
предложений, которую вы хотите повторять, и ключевые слова END
LOOP непосредственно за последним предложением этой
последовательности. Следующий пример показывает простейшую
форму цикла. Он повторяет последовательность предложений
бесконечно.
LOOP
-- поместите сюда последовательность предложений
...
END LOOP;
Для цикла FOR-LOOP вы задаете интервал целых чисел, и
предложения внутри цикла выполняются один раз для каждого целого
в этом интервале. Допустим, вы изготавливаете автомобили по
заказу, и каждый автомобиль имеет порядковый номер. Чтобы
отслеживать, какой покупатель купил какой автомобиль, вы можете
использовать следующий цикл FOR:
FOR i IN 1..order_qty LOOP
UPDATE sales SET custno = customer_id
WHERE snum = snum_seq.nextval;
END LOOP;
Чтобы организовать цикл WHILE-LOOP, вы ассоциируете условие с
последовательностью предложений. Перед каждым повторением цикла
это условие вычисляется. Если оно дает TRUE, то предложения
цикла выполняются, и управление возвращается на начало цикла.
Если условие дает FALSE или NULL, то цикл завершается, и
управление передается на следующее за циклом предложение.
В следующем примере вы отыскиваете первого сотрудника, имеющего
жалованье не менее $4000, продвигаясь по иерархии начальников
служащего 7902:
-- доступен на диске в файле EXAMP3
DECLARE
salary emp.sal%TYPE;
mgr_num emp.mgr%TYPE;
last_name emp.ename%TYPE;
starting_empno CONSTANT NUMBER(4) := 7902;
BEGIN
SELECT sal, mgr INTO salary, mgr_num FROM emp
WHERE empno = starting_empno;
WHILE salary < 4000 LOOP
SELECT sal, mgr, ename INTO salary, mgr_num, last_name
FROM emp
WHERE empno = mgr_num;
END LOOP;
INSERT INTO temp VALUES (null, salary, last_name);
COMMIT;
END;
1-8 Руководство пользователя и справочник по PL/SQL
Предложение EXIT-WHEN позволяет прекратить работу цикла, когда
вы обнаруживаете условия, которые делают дальнейшее повторение
цикла нежелательным или невозможным. Когда встречается
предложение EXIT, вычисляется условие, которое специфицировано
фразой WHEN. Если это условие истинно, то цикл завершается, и
управление передается на следующее за циклом предложение. В
следующем примере выход из цикла осуществляется, когда значение
total превышает 25000:
LOOP
...
total := total + salary;
EXIT WHEN total > 25000; -- выйти, если условие истинно
END LOOP;
-- управление будет передано сюда
Последовательное управление
Предложение GOTO позволяет передать управление на метку
безусловно. Метка, которая представляет собой необъявляемый
идентификатор, заключенный в двойные угловые скобки, должна
предшествовать выполняемому предложению или блоку PL/SQL. При
своем выполнении предложение GOTO передает управление на
помеченное предложение или блок, как показывает следующий
пример:
IF rating > 90 THEN
GOTO calc_raise; -- перейти к метке
END IF;
...
<>
IF job_title = 'SALESMAN' THEN -- управление передается сюда
raise := commission * 0.25;
ELSE
raise := salary * 0.10;
END IF;
Общие сведения 1-9
Курсоры
-------
Для выполнения предложений SQL и хранения их результатов ORACLE
использует рабочие области, называемые ЛИЧНЫМИ ОБЛАСТЯМИ SQL.
Конструкт PL/SQL, называемый КУРСОРОМ, позволяет вам обращаться
к личной области SQL по имени и извлекать из нее информацию.
Есть два вида курсоров: НЕЯВНЫЕ и ЯВНЫЕ. PL/SQL неявно
объявляет курсор для любого предложения манипулирования данными
SQL, в том числе для запроса, возвращающего только одну строку.
Для запросов, возвращающих более одной строки, вы можете явно
объявить курсор, чтобы обрабатывать возвращаемые строки по
одной. Например:
DECLARE
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
...
Множество строк, возвращаемых многострочным запросом, называется
АКТИВНЫМ МНОЖЕСТВОМ. Его размер равен числу строк,
удовлетворяющих вашим условиям поиска. Как показывает рис.1-3,
явный курсор "указывает" на ТЕКУЩУЮ СТРОКУ в активном множестве.
Это позволяет вашей программе обрабатывать строки по одной за
раз.
Рис.1-3
Обработка запроса
Запрос
SELECT empno, ename, job FROM emp WHERE deptno = 20;
Активное множество
----------------------------¬
¦ 7369 SMITH CLERK ¦
¦ 7566 JONES MANAGER ¦
Курсор --> ¦ 7788 SCOTT ANALYST ¦ Текущая строка
¦ 7876 ADAMS CLERK ¦
¦ 7902 FORD ANALYST ¦
L----------------------------
Обработка многострочного запроса напоминает обработку файла.
Например, программа на языке COBOL открывает файл, обрабатывает
записи, а затем закрывает файл. Аналогично, программа PL/SQL
открывает курсор, обрабатывает строки, возвращенные запросом, а
затем закрывает курсор. Точно так же, как указатель файла
отмечает текущую позицию в открытом файле, курсор отмечает
текущую позицию в активном множестве.
1-10 Руководство пользователя и справочник по PL/SQL
Как показывает рис.1-4, для управления курсором вы используете
предложения OPEN, FETCH и CLOSE.
Рис.1-4
Управление курсором
OPEN Курсор --> Активное множество
----------------------------¬
FETCH Курсор --> ¦ 7369 SMITH CLERK ¦ Текущая строка
¦ ¦ 7566 JONES MANAGER ¦ ¦
¦ ¦ 7788 SCOTT ANALYST ¦ ¦
¦ ¦ 7876 ADAMS CLERK ¦ ¦
¦ 7902 FORD ANALYST ¦
L----------------------------
CLOSE Курсор -->
Предложение OPEN выполняет запрос, ассоциированный с курсором,
идентифицирует активное множество и позиционирует курсор перед
его первой строкой. Предложение FETCH извлекает текущую строку
и продвигает курсор к следующей строке. После того, как
обработана последняя строка, предложение CLOSE закрывает курсор.
Курсорные циклы FOR
В большинстве случаев, требующих явного курсора, вы можете
использовать курсорные циклы FOR вместо предложений OPEN, FETCH
и CLOSE, чтобы упростить кодирование. Курсорный цикл FOR неявно
объявляет индекс своего цикла как запись %ROWTYPE, открывает
курсор, итеративно извлекает строки данных из активного
множества в поля записи, и закрывает курсор после того, как все
строки обработаны. В следующем примере курсорный цикл FOR
неявно объявляет запись emp_rec как принадлежащую типу
c1%ROWTYPE:
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
...
BEGIN
...
FOR emp_rec IN c1 LOOP
...
salary_total := salary_total + emp_rec.sal;
END LOOP;
END;
Как показывает этот пример, вы используете квалифицированные
ссылки, чтобы обращаться к отдельным полям записи.
Общие сведения 1-11
Обработка ошибок
----------------
В PL/SQL вы можете обрабатывать как внутренне определенные, так
и определенные вами условия ошибок, называемые ИСКЛЮЧЕНИЯМИ.
Когда возникает ошибка, исключение ВОЗБУЖДАЕТСЯ. Это значит,
что нормальное выполнение прекращается, и управление передается
на часть обработки исключений вашего блока или подпрограммы
PL/SQL. Для обработки исключений вы пишете специальные
программы - ОБРАБОТЧИКИ ИСКЛЮЧЕНИЙ.
Предопределенные исключения возбуждаются неявно исполнительной
системой. Например, при попытке деления числа на 0
автоматически возбуждается предопределенное исключение
ZERO_DIVIDE. Пользовательские исключения должны возбуждаться
явно, через предложения RAISE.
Вы можете определить ваши собственные исключения в декларативной
части любого блока или подпрограммы PL/SQL. В исполнительной
части вы проверяете условие, требующее специального внимания.
Если вы обнаруживаете, что это условие существует, вы выдаете
команду RAISE, указав имя исключения. В следующем примере вы
вычисляете премию на основе жалованья и комиссионных для каждого
продавца в вашей компании. Все продавцы должны иметь непустые
комиссионные. Поэтому, если вы обнаружите продавца с пустыми
комиссионными, вы возбуждаете исключение с именем comm_missing.
DECLARE
salary NUMBER(7,2);
commission NUMBER(7,2);
comm_missing EXCEPTION; -- объявление исключения
BEGIN
SELECT sal, comm INTO salary, commission FROM emp
WHERE empno = :emp_id;
IF commission IS NULL THEN
RAISE comm_missing; -- возбуждение исключения
ELSE
:bonus := (salary * 0.05) + (commission * 0.15);
END IF;
EXCEPTION -- здесь начинаются обработчики исключений
WHEN comm_missing THEN
-- здесь обработать ошибку
END;
Переменные emp_id и bonus объявляются и получают значения во
внешнем окружении. Для более подробной информации обратитесь к
подразделу "Окружение прекомпилятора ORACLE" в главе 8.
1-12 Руководство пользователя и справочник по PL/SQL
Модульность
-----------
Модульность позволяет вам разбивать приложение на управляемые,
хорошо определенные логические модули. Путем последовательного
уточнения вы можете свести комплексную проблему к множеству
простых проблем, имеющих легко реализуемые решения. PL/SQL
предлагает для этой цели конструкты, называемые ПРОГРАММНЫМИ
ЕДИНИЦАМИ. Помимо таких программных единиц, как блоки и
подпрограммы, предусматривается специальный конструкт ПАКЕТ,
который позволяет вам группировать взаимосвязанные объекты
программы в единицы большего размера.
Подпрограммы
PL/SQL имеет два типа подпрограмм - ПРОЦЕДУРЫ и ФУНКЦИИ; всем им
можно передавать параметры при вызове. Как показывает следующий
пример, подпрограмма выглядит как миниатюрная программа. Она
начинается с заголовка, за которым следуют необязательная
декларативная часть, исполнительная часть и необязательная часть
обработки исключений:
PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN
SELECT comm * 0.25 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus WHERE empno=emp_id;
END IF;
EXCEPTION
WHEN comm_missing THEN
...
END award_bonus;
При вызове эта процедура принимает номер сотрудника. Она
использует этот номер для выбора из таблицы базы данных значения
комиссионных для этого сотрудника, и, одновременно, для
вычисления 25-процентной премии. Затем процедура проверяет
значение премии. Если это значение пусто, возбуждается
исключение; в противном случае обновляется платежная запись для
этого сотрудника.
Общие сведения 1-13
Пакеты
PL/SQL позволяет вам объединять логически связанные типы,
программные объекты и подпрограммы в ПАКЕТ. Каждый пакет легко
понять, а интерфейсы между пакетами просты, ясны и хорошо
определены. Это облегчает разработку приложений.
Пакет обычно состоит из двух частей: спецификации и тела.
СПЕЦИФИКАЦИЯ - это интерфейс пакета с вашими приложениями; она
объявляет типы, константы, переменные, исключения, курсоры и
подпрограммы, доступные для использования. ТЕЛО пакета
определяет курсоры и подпрограммы, и тем самым реализует
спецификацию пакета. В следующем примере вы объединяете в пакет
две процедуры управления кадрами:
PACKAGE emp_actions IS -- спецификация пакета
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
PACKAGE BODY emp_actions IS -- тело пакета
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Только объявления в спецификации пакета являются видимыми и
доступными приложениям. Детали реализации в теле пакета
остаются скрытыми и недоступными.
Если у вас есть Процедурное расширение базы данных, то пакеты
можно компилировать и сохранять в базе данных ORACLE, где их
содержимое может совместно использоваться многими приложениями.
Когда вы первый раз обращаетесь к пакетированной подпрограмме, в
память загружается весь пакет. Поэтому последующие обращения к
другим подпрограммам пакета не требуют дисковых операций. Таким
образом, пакеты могут повысить продуктивность разработки и
улучшить производительность выполнения.
1-14 Руководство пользователя и справочник по PL/SQL
Скрытие информации
------------------
Благодаря скрытию информации вы видите лишь те детали, которые
относятся к данному уровню алгоритма и структуры данных.
Скрытие информации поддерживает проектные решения высокого
уровня отдельно от низкоуровневых деталей проекта, которые более
подвержены изменениям.
Алгоритмы
Скрытие информации для алгоритмов реализуется через принцип
ПРОЕКТИРОВАНИЯ СВЕРХУ ВНИЗ. Один раз определив назначение
низкоуровневой процедуры и спецификации ее интерфейса, вы можете
игнорировать детали ее реализации. На высоких уровнях эти
детали скрыты. Например, реализация процедуры с именем
raise_salary скрыта. Все, что вам необходимо знать - это то,
что данная процедура увеличивает оклад заданного сотрудника на
заданную величину. Все изменения в определении (т.е. в
реализации) процедуры raise_salary прозрачны для вызывающих ее
приложений.
Структуры данных
Скрытие информации для структур данных реализуется через
ИНКАПСУЛЯЦИЮ ДАННЫХ. Разработав комплект подпрограмм-утилит для
некоторой структуры данных, вы изолируете саму эту структуру от
пользователей и других разработчиков. Таким образом, другие
разработчики знают, как используются подпрограммы, работающие с
этой структурой данных, но не знают, как представлена сама эта
структура.
В пакетах PL/SQL вы можете специфицировать, какие из типов,
программных объектов и подпрограмм являются общими, а какие
личными. Таким способом пакеты реализуют инкаспуляцию данных,
позволяя вам помещать ваши декларации в "черный ящик".
Определение личного типа скрыто и недоступно. Если это
определение изменяется, это воздействует только на сам пакет, но
не на ваше приложение. Это упрощает сопровождение и развитие.
Общие сведения 1-15
----------------
Архитектура
Исполнительная система PL/SQL - это технология, а не независимый
продукт. Рассматривайте эту технологию как процессор, который
исполняет блоки и подпрограммы PL/SQL. Этот процессор может
быть установлен в сервере ORACLE или в инструменте разработки
приложений, таком как SQL*Forms, SQL*Menu или SQL*ReportWriter.
Таким образом, PL/SQL может располагаться в двух окружениях:
* в сервере ORACLE
* в инструментах ORACLE
Эти два окружения независимы. PL/SQL может быть доступен в
сервере, но недоступен в инструментах, или наоборот. В любом
окружении, процессор PL/SQL принимает как ввод любой
действительный блок или подпрограмму PL/SQL. На рис.1-5
показано, как процессор PL/SQL обрабатывает блок PL/SQL.
Рис.1-5
Обработка блока процессором PL/SQL
-----------------------------------------¬
¦ ¦
¦ Процессор PL/SQL ¦
¦ ¦
¦ -------------¬ ¦
----------¬ ¦ ----------¬процедуры ¦ Исполнитель¦ ¦
¦ Блок ¦ ----+- ¦ Блок ¦---------¦ процедурных¦ ¦
¦ PL/SQL ¦ ¦ ¦ PL/SQL ¦----¬ ¦ предложений¦ ¦
L---------- ¦ L----------SQL ¦ L------------- ¦
¦ ¦ ¦
L------------------+----------------------
Процессор PL/SQL исполняет процедурные предложения, но посылает
предложения SQL исполнителю предложений SQL в сервере ORACLE.
1-16 Руководство пользователя и справочник по PL/SQL
В сервере ORACLE
----------------
Инструменты разработки приложений, в которых нет локального
процессора PL/SQL, вынуждены обращаться к ORACLE для обработки
блоков и подпрограмм PL/SQL. Сервер ORACLE, когда он содержит
процессор PL/SQL, может обрабатывать как блоки и подпрограммы
PL/SQL, так и одиночные предложения SQL. Сервер передает блоки и
подпрограммы своему локальному процессору PL/SQL.
Анонимные блоки
Анонимные блоки PL/SQL могут встраиваться в программы
прекомпиляторов или OCI-программы. Во время выполнения такая
программа, если локальный процессор PL/SQL отсутствует, передает
эти блоки серверу ORACLE, где они компилируются и исполняются.
Аналогично, интерактивные инструменты, такие как SQL*Plus и
SQL*DBA, если в них нет локального процессора PL/SQL, должны
посылать анонимные блоки серверу ORACLE. На рис.1-6 показана
обработка анонимного блока PL/SQL, посылаемого в ORACLE из
хост-программы или инструмента ORACLE.
Рис.1-6
Передача анонимного блока в СУБД
--------------¬ ----------------------------------------------¬
¦ ¦ ¦ Сервер ORACLE ¦
¦ Хост- ¦ ¦ ¦
¦ программа ¦ ¦ -----------------------------------------¬ ¦
¦ или ¦ ¦ ¦ ¦ ¦
¦ инструмент ¦ ¦ ¦ Процессор PL/SQL ¦ ¦
¦ ORACLE ¦ ¦ ¦ ¦ ¦
¦ ¦ ¦ ¦ -------------¬ ¦ ¦
¦ ----------¬ ¦ ¦ ¦ ----------¬процедуры ¦ Исполнитель¦ ¦ ¦
¦ ¦ Аноним- ¦ ¦ ¦ ¦ ¦ Аноним- ¦---------¦ процедурных¦ ¦ ¦
¦ ¦ ный блок¦-+----+--+- ¦ ный блок¦ ¦ предложений¦ ¦ ¦
¦ ¦ PL/SQL ¦ ¦ ¦ ¦ ¦ PL/SQL ¦----¬ ¦ ¦ ¦ ¦
¦ L---------- ¦ ¦ ¦ L----------SQL ¦ L------------- ¦ ¦
L-------------- ¦ ¦ ¦ ¦ ¦
¦ L------------------+---------------------- ¦
¦ ¦
¦ ------------------------------¬ ¦
¦ ¦ Исполнитель предложений SQL ¦ ¦
¦ L------------------------------ ¦
L----------------------------------------------
Общие сведения 1-17
Хранимые подпрограммы
Если у вас есть Процедурное расширение базы данных, то
именованные блоки (подпрограммы) PL/SQL можно компилировать
отдельно и передавать на постоянное хранение в базу данных
ORACLE, где они готовы к исполнению.
Подпрограмма, явно созданная (посредством CREATE) с помощью
инструмента ORACLE, называется ХРАНИМОЙ подпрограммой. Однажды
откомпилированная и сохраненная в словаре данных, она является
объектом базы данных, к которому можно обращаться из любого
приложения, соединенного с этой базой данных.
Хранимые подпрограммы, определенные внутри пакета, называются
ПАКЕТИРОВАННЫМИ подпрограммами; подпрограммы, определенные вне
пакета, называются НЕЗАВИСИМЫМИ подпрограммами. (Подпрограммы,
определенные внутри других подпрограмм или внутри блока PL/SQL,
называются ЛОКАЛЬНЫМИ подпрограммами. Такие подпрограммы
недоступны для других приложений, и существуют лишь для удобства
окружающего блока.)
Хранимые подпрограммы повышают продуктивность, улучшают
производительность, экономят память, обеспечивают лучшую
целостность и безопасность. Например, проектируя приложения на
базе библиотеки хранимых процедур и функций, вы избежите
повторов в кодировании и увеличите вашу продуктивность.
Вы можете вызывать хранимые подпрограммы из триггера базы
данных, другой хранимой подпрограммы, приложения прекомпилятора
ORACLE, приложения OCI, или интерактивно из SQL*Plus или
SQL*DBA. Например, из SQL*Plus вы могли бы вызвать независимую
процедуру create_dept следующим способом:
SQL> EXECUTE create_dept('FINANCE', 'NEW_YORK');
Подпрограммы хранятся в синтаксически разобранной,
откомпилированной форме. Поэтому при вызове они загружаются и
передаются процессору PL/SQL немедленно. Более того, хранимые
подпрограммы используют преимущества разделяемой памяти в
ORACLE. Лишь одна копия подпрограммы должна быть загружена в
память, чтобы быть доступной многим пользователям. На рис.1-7
показано, как хост-программа или инструмент ORACLE выдает
удаленный вызов (RPC) к хранимой процедуре.
1-18 Руководство пользователя и справочник по PL/SQL
Рис.1-7
Выдача удаленного вызова (RPC)
--------------¬ ----------------------------------------------¬
¦ Хост- ¦ ¦ Сервер ORACLE ¦
¦ программа ¦ ¦ ¦
¦ или ¦ ¦ ¦
¦ инструмент ¦ ¦ ¦
¦ ORACLE ¦ ¦ ¦
¦ ¦ ¦ -----------¬ ¦
¦ ¦ ¦ ¦ Хранимая ¦ ¦
¦ RPC -----+----+-----¦ процедура¦ ¦
¦ ¦ ¦ ¦ ¦ ¦
L-------------- ¦ L----------- ¦
¦ ¦ ¦
¦ ---------+-------------------------------¬ ¦
¦ ¦ ¦ ¦ ¦
¦ ¦ ¦ Процессор PL/SQL ¦ ¦
¦ ¦ ¦ ¦ ¦
¦ ¦ -------------¬ ¦ ¦
¦ ¦ -----------¬процедуры¦ Исполнитель¦ ¦ ¦
¦ ¦ ¦ Хранимая ¦--------¦ процедурных¦ ¦ ¦
¦ ¦ ¦ процедура¦ ¦ предложений¦ ¦ ¦
¦ ¦ ¦ ¦---¬ ¦ ¦ ¦ ¦
¦ ¦ L-----------SQL¦ L------------- ¦ ¦
¦ ¦ ¦ ¦ ¦
¦ L------------------+---------------------- ¦
¦ ¦
¦ ------------------------------¬ ¦
¦ ¦ Исполнитель предложений SQL ¦ ¦
¦ L------------------------------ ¦
L----------------------------------------------
Триггеры базы данных
Триггер базы данных (не триггер SQL*Forms) - это хранимая
подпрограмма, ассоциированная с таблицей. Вы можете заставить
ORACLE автоматически возбуждать триггер базы данных перед или
после исполнения предложения INSERT, UPDATE или DELETE для
данной таблицы. Одним из многих применений триггеров базы
данных является аудитинг (отслеживание) модификаций данных.
Например, следующий триггер базы данных возбуждается при каждом
обновлении жалованья в таблице emp:
CREATE TRIGGER audit_sal
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_audit VALUES ...
END;
Вы можете использовать все предложения манипулирования данными
SQL и любые процедурные предложения в исполняемой части триггера
базы данных.
Общие сведения 1-19
В инструментах ORACLE
---------------------
Инструмент разработки приложений может обрабатывать блоки
PL/SQL, если он содержит процессор PL/SQL. Инструмент передает
эти блоки своему локальному процессору PL/SQL. Процессор PL/SQL
исполняет все процедурные предложения на стороне приложения, а в
ORACLE посылает лишь предложения SQL. Таким образом, большая
часть работы выполняется на стороне приложения, а не на стороне
сервера.
Более того, если блок не содержит предложений SQL, то процессор
исполняет весь блок на стороне приложения. Это полезно, если
ваше приложение выгадывает за счет условного и итеративного
управления. На рис.1-8 показана обработка блока PL/SQL, когда
процессор PL/SQL располагается в SQL*Forms. Те же концепции
применимы к последним версиям SQL*Menu и SQL*ReportWriter.
Рис.1-8
Процессор PL/SQL в SQL*Forms
---------------------------------------------------------------¬
¦ SQL*Forms ¦
¦ ¦
¦ -----------------------------------------¬ ¦
¦ ¦ ¦ ¦
¦ ¦ Процессор PL/SQL ¦ ¦
¦ ------------¬ ¦ ¦ ¦
¦ ¦ Триггер ¦ ¦ -------------¬ ¦ ¦
¦ ¦----------¬¦ ¦ ----------¬процедуры ¦ Исполнитель¦ ¦ ¦
¦ ¦¦ Блок ¦+----+- ¦ Блок ¦---------¦ процедурных¦ ¦ ¦
¦ ¦¦ PL/SQL ¦¦ ¦ ¦ PL/SQL ¦----¬ ¦ предложений¦ ¦ ¦
¦ ¦L----------¦ ¦ L----------SQL ¦ L------------- ¦ ¦
¦ L------------ ¦ ¦ ¦ ¦
¦ L------------------+---------------------- ¦
L--------------------------------------+------------------------
¦
---------------------------------------+-----------------------¬
¦ ¦
¦ ------------------------------¬ ¦
¦ ¦ Исполнитель предложений SQL ¦ ¦
¦ L------------------------------ ¦
¦ ¦
¦ Сервер ORACLE ¦
¦ ¦
L---------------------------------------------------------------
Зачастую приложения SQL*Forms используют предложения SQL лишь
для того, чтобы просто проверить входное значение поля или
выполнить простые вычисления. Используя вместо этого PL/SQL, вы
можете избежать обращений к серверу, который может оказаться
удаленным. Более того, для манипуляций с значениями полей вы
можете использовать функции PL/SQL.
1-20 Руководство пользователя и справочник по PL/SQL
----------------
Преимущества PL/SQL
PL/SQL - это полностью переносимый, высокопроизводительный язык
обработки транзакций, предлагающий следующие преимущества:
* поддержку SQL
* повышение продуктивности разработки
* улучшение производительности выполнения
* переносимость
* интеграцию с ORACLE
Поддержка SQL
-------------
SQL стал признанным языком баз данных благодаря своей гибкости,
мощи и простоте изучения. Небольшое число предложений,
напоминающих естественный английский язык, позволяет легко
манипулировать данными, хранящимися в реляционной базе данных.
SQL - непроцедурный язык, т.е. вы указываете, что вы хотите
сделать, не указывая, как это делать. ORACLE сам определяет
наилучший способ удовлетворения вашего запроса. Более того,
необходимая связь между последовательными предложениями
отсутствует, поскольку ORACLE выполняет предложения SQL по
одному за раз.
PL/SQL позволяет вам использовать как все предложения
манипулирования данными языка SQL, команды управления курсорами
и транзакциями, так и все функции, операторы и псевдостолбцы
SQL. Таким образом, вы имеете возможность гибко и безопасно
манипулировать данными ORACLE.
Улучшенная продуктивность
-------------------------
PL/SQL придает дополнительную функциональность непроцедурным
инструментам, таким как SQL*Forms, SQL*Menu и SQL*ReportWriter.
Когда PL/SQL встроен в эти инструменты, разработчики
программного обеспечения могут использовать привычные конструкты
процедурных языков при написании приложений. Например, при
использовании SQL*Forms можно ввести целый блок PL/SQL как один
триггер; не требуется применять многошаговых триггеров, макросов
или пользовательских выходов. Таким образом, улучшенный
инструментарий в руках разработчиков повышает продуктивность
разработки.
Более того, PL/SQL один и тот же в любом окружении. Поэтому,
освоив PL/SQL с одним инструментом, разработчики могут улучшить
свою продуктивность во всех прочих инструментах, поддерживающих
PL/SQL.
Общие сведения 1-21
Улучшенная производительность
-----------------------------
Без PL/SQL система ORACLE должна обрабатывать предложения SQL по
одному за раз. Каждое предложение SQL приводит к очередному
обращению к ORACLE и дополнительным накладным расходам. Эти
накладные расходы могут стать существенными, когда вы выдаете
много предложений SQL в сетевой среде. Каждое выдаваемое
предложение SQL должно быть послано по сети, утяжеляя сетевой
трафик.
При PL/SQL, однако, целый блок предложений может быть послан в
ORACLE за один раз. Это позволяет радикально сократить общение
между приложением и ORACLE. Как показывает рис.1-9, если ваше
приложение интенсивно использует базу данных, вы можете
сгруппировать предложения SQL, используя управляющие структуры,
а затем послать этот сгруппированный блок в ORACLE для
исполнения. Например, чтобы выполнить десять индивидуальных
предложений SQL, требуется десять вызовов, но для выполнения
подпрограммы, содержащей десять предложений SQL, необходим лишь
один вызов ORACLE.
Рис.1-9
PL/SQL повышает производительность
---------------------------------------------------------------¬
¦ PL/SQL РЕЗКО УВЕЛИЧИВАЕТ ПРОИЗВОДИТЕЛЬНОСТЬ ¦
¦ особенно в сетевых окружениях ¦
¦ ------¬ ¦
¦ -------------¬ ¦ SQL ¦ ------¬ ---------------¬ ¦
¦ ¦ ¦-------L------ ¦ SQL ¦---¦ ¦ ¦
¦ ¦ Приложение ¦------- L---------¦ Другие ¦ ¦
¦ ¦ ¦-------------¬ ---¦ СУБД ¦ ¦
¦ L------------- ¦ SQL ¦ ------¬ L--------------- ¦
¦ L------ ¦ SQL ¦ ¦
¦ L------ ¦
¦ ¦
¦ -------------¬ ¦
¦ ¦ SQL ¦ ¦
¦ -------------¬ ¦ IF...THEN ¦ ---------------¬ ¦
¦ ¦ ¦ ¦ SQL ¦ ¦ ¦ ¦
¦ ¦ Приложение ¦-------¦ ELSE ¦-----¦ ORACLE ¦ ¦
¦ ¦ ¦ ¦ SQL ¦ ¦ с PL/SQL ¦ ¦
¦ L------------- ¦ END IF ¦ L--------------- ¦
¦ ¦ SQL ¦ ¦
¦ L------------- ¦
¦ ¦
¦ -------------¬ ---------------¬ ¦
¦ ¦ ¦ ¦ ORACLE ¦ ¦
¦ ¦ Приложение ¦----------- RPC ----------¦ с PL/SQL ¦ ¦
¦ ¦ ¦ ¦ и хранимыми ¦ ¦
¦ L------------- ¦ процедурами ¦ ¦
¦ L--------------- ¦
L---------------------------------------------------------------
PL/SQL может также взаимодействовать с инструментами разработки
приложений Oracle, такими как SQL*Forms, SQL*Menu и
SQL*ReportWriter. Добавляя мощь процедурной обработки в такие
инструменты, PL/SQL увеличивает их производительность. При
помощи PL/SQL инструмент способен выполнять все вычисления с
данными быстро и эффективно, не обращаясь к ORACLE. Это
экономит время, а в сетевом окружении уменьшает сетевой трафик.
1-22 Руководство пользователя и справочник по PL/SQL
Переносимость
-------------
Приложения, написанные на PL/SQL, переносимы на любое
оборудование и в среду любой операционной системы, на которых
выполняется ORACLE. Иными словами, программы PL/SQL могут
выполняться всюду, где может выполняться ORACLE; вам не
требуется перенастраивать их на каждое новое окружение. Это
значит, что вы можете разрабатывать библиотеки переносимых
программ, которые можно использовать в различных окружениях.
Интеграция с ORACLE
-------------------
Как PL/SQL, так и ORACLE основываются на SQL. Более того, PL/SQL
поддерживает все типы данных SQL. В сочетании с прямым доступом
к ORACLE, который обеспечивает SQL, эти объявления естественных
для ORACLE типов данных интегрируют PL/SQL со словарем данных
ORACLE.
Атрибуты %TYPE и %ROWTYPE предоставляет еще большую интеграцию
PL/SQL со словарем данных. Например, вы можете использовать
атрибут %TYPE для объявлений переменных, базирующихся на
определениях столбцов в базе данных. Если определение столбца
изменится, определение соответствующей переменной будет изменено
автоматически во время выполнения. Это обеспечивает
независимость от данных, уменьшает стоимость сопровождения и
позволяет программам адаптироваться к изменениям в базе данных.