Рисунок 7.3 - Синтаксис оператора
CREATE PROCEDURE
| Каталог | Индекс раздела |
| Назад | Оглавление | Вперед |
7.3.1 Очевидно, что, как и в любых других приложениях, в приложениях баз данных часто возникает необходимость выполнения одних и тех же или сходных подзадач, включающих в себя одинаковое манипулирование данными, хранимыми в базе данных. Как и в процедурных языках, в языке SQL возникла концепция процедур - отдельно описанных, повторно используемых последовательностей операций, выполняющих, как правило, определенную прикладную функцию. В современных приложения баз данных эта концепция развилась в концепцию хранимых процедур. Хранимая процедура представляет собой процедуру, находящуюся и выполняющуюся на сервере базы данных и содержащую операторы SQL и операторы процедурной логики (операторы процедурного SQL или другого языка программирования). В современных информационных технологиях перенос как можно большей части бизнес-логики в хранимые процедуры базы данных является стратегической концепцией. Хранимые процедуры обеспечивают приложениям баз данных следующие преимущества:
Как уже упоминалось, процедурная логика в хранимых процедурах может быть реализована на любом языке, допускающем включение в программу обращения к базе данных. В Oracle традиционно основным языком хранимых процедур является язык PL/SQL, но поддерживаются и процедуры на других языках, прежде всего - на языке C, а в последнее время - также на языке Java. В DB2 в отсутствие процедурного расширения SQL существовал богатый набор языков, применяющихся для написания хранимых процедур: C, REXX, COBOL и т.д. С развитием языка Java он становится в IBM стратегическим средством для написания хранимых процедур, которое поддерживается специальными средствами (например, Java Stored Procedure Builder). В DB2 версии 7.1 в хранимых процедурах становится возможным применение процедурного SQL, что составляет второе стратегическое направление.
К сожалению, объем и направленность нашего учебного пособия на дает нам возможности в полной мере рассмотреть свойства хранимых процедур и методы их создания. Поскольку в большой степени хранимые процедуры предназначены для реализации бизнес-логики, их рассмотрение должно относится к курсу или разделу "Проектирование приложений баз данных". Мы ограничимся рассмотрением основных свойств и одним примером.
7.3.2 Хранимая процедура создается оператором SQL CREATE PROCEDURE, общий синтаксис которого представлен на рис. 7.3.
Рисунок 7.3 - Синтаксис оператора CREATE PROCEDURE
Этот оператор во многом похож на определения процедур в процедурных языках программирования (Pascal, C, etc.). Принципиальное отличие процедуры SQL состоит в том, что для процедуры обязательно должен быть описан режим использования каждого параметра: IN (только входной), OUT (только выходной), INOUT (и входной, и выходной). В Oracle режим использования указывается после имени параметра, в DB2 - перед именем параметра.
Результат хранимой процедуры может передаваться через выходные параметры или представлять собой множество строк, выбранное из базы данных. В последнем случае в процедуре открывается курсор (возможно, несколько курсоров) и вызвавшее процедуру приложение выбирает данные из этого курсора. В DB2 для этого случая в операторе CREATE PROCEDURE применяются дополнительные конструкции.
Специально обращаем ваше внимание на то, что тело процедуры в DB2 представляет собой оператор SQL (в частности, это может быть составной оператор BEGIN ... END), а в Oracle тело процедуры строится по правилам PL/SQL.
Здесь рассмотрен только случай для хранимых процедур, написанных на языке SQL (PL/SQL), но обе СУБД предусматривают также и использование для этих целей других языков. В последнем случае процедуры являются "внешними" (описаны отдельно) и синтаксис оператора CREATE PROCEDURE несколько отличается для этого случая.
Хранимая процедура вызывается из приложения оператором SQL CALL (стандарт и DB2):
CALL имя_процедуры (список_фактических_параметров)
В PL/SQL Oracle вызов процедуры состоит просто в употреблении ее имени со списком параметров:
имя_процедуры (список_фактических_параметров)
Обе СУБД обеспечивают также процедуры-функции (оператор CREATE FUNCTION).
7.3.3 В качестве примера хранимой процедуры рассмотрим те задачи, с которыми мы "не справились", создавая триггеры для Oracle:
Мы создадим одну процедуру с именем upd_salary, которая будет обеспечивать указанные ограничения при изменении зарплаты любого сотрудника. Эта процедура должна выполняться при изменении зарплаты любого сотрудника вместо оператора UPDATE. Параметры процедуры:
id - код сотрудника, зарплата которого изменяется;
salary - новое значение зарплаты или добавка к зарплате;
type - признак, принимающий значения: 'S', если задается абсолютное значение зарплаты, или 'A', если задается добавка к зарплате.
CREATE PROCEDURE upd_salary
(id IN NUMBER, salary IN NUMBER, type IN CHAR)
IS
old_salary NUMBER; /* старая зарплата */
p_salary NUMBER; /* зарплата президента */
new_salary NUMBER; /* новая зарплата */
x_job NUMBER; /* код должности сотрудника */
BEGIN
/* выбирается старая зарплата и код должности
сотрудника, зарплата которого изменяется */
SELECT salary,job_id INTO old_salary,x_job FROM employee WHERE employee_id=id;
/* вычисляется новая зарплата */
IF type='S' THEN new_salary:=salary;
ELSIF type='A' THEN new_salary:=old_salary+salary;
ELSE raise_application_error(-20777,'Param error');
END IF;
/* является ли сотрудник президентом? */
IF x_job=672 THEN
/* уменьшается ли зарплата президента ? */
IF (old_salary>new_salary)THEN
/* да - изменяется зарплата у всех */
UPDATE employee SET salary=salary*new_salary/old_salary;
ELSE
/* нет - только зарплата президента */
UPDATE employee SET salary=new_salary WHERE employee_id=id;
END IF;
ELSE
/* если сотрудник - не президент, выбирается зарплата президента */
SELECT salary INTO p_salary FROM employee WHERE job_id=672;
/* зарплата больше президентской ? */
IF p_salary<new_salary THEN
/* да - ошибка */
raise_application_error(-20777,'Salary limit');
ELSE
/* нет - изменение выполняется */
UPDATE employee SET salary=new_salary WHERE employee_id=id;
END IF;
END IF;
END;
/
Хотя мы и справились с этими задачами в триггерах DB2, реализуем то же самое в виде хранимой процедуры DB2.
CREATE PROCEDURE UPD_SALARY (IN id DECIMAL(8,2), IN salary DECIMAL(8,2), IN type CHAR(1))
LANGUAGE SQL
BEGIN
-- старая зарплата
DECLARE old_salary DECIMAL(8,2);
-- зарплата президента
DECLARE p_salary DECIMAL(8,2);
-- новая зарплата
DECLARE new_salary DECIMAL(8,2);
-- код должности сотрудника
DECLARE x_job INTEGER;
-- выбирается старая зарплата и код должности сотрудника, зарплата которого изменяется
SET old_salary= (SELECT salary FROM employee WHERE employee_id=id);
SET x_job= (SELECT job_id FROM employee WHERE employee_id=id);
-- вычисляется новая зарплата
IF type='S' THEN SET new_salary=salary;
ELSEIF type='A' THEN SET new_salary=old_salary+salary;
ELSE SIGNAL SQLSTATE '75001' ('Parameter error');
END IF;
-- является ли сотрудник президентом?
IF x_job=672 THEN
-- уменьшается ли зарплата президента ?
IF (old_salary>new_salary) THEN
-- да - изменяется зарплата у всех
UPDATE employee SET salary=salary*new_salary/old_salary;
ELSE
-- нет - только зарплата президента
UPDATE employee SET salary=new_salary WHERE employee_id=id;
END IF;
ELSE
-- если сотрудник - не президент, выбирается зарплата президента
SELECT salary INTO p_salary FROM employee WHERE job_id=672;
-- зарплата больше президентской ?
IF p_salary<new_salary THEN
-- да - ошибка
SIGNAL SQLSTATE '75001' ('Salary limitation');
ELSE
-- нет - изменение выполняется
UPDATE employee SET salary=new_salary WHERE employee_id=id;
END IF;
END IF;
END
@
| Назад | Оглавление | Вперед |
| Каталог | Индекс раздела |