Создание хранимых процедур

Хранимая процедура – это модуль, написанный на процедурном языке сервера БД (в нашем случае InterBase) и хранящийся в базе данных как метаданные (то есть данные о данных). Хранимую процедуру можно вызвать из приложения.

Существует две разновидности хранимых процедур:

- процедуры выбора;

- процедуры действия.

Процедуры выбора могут возвращать более одного значения. В приложении имя хранимой процедуры выбора подставляется в оператор SELECT вместо имени таблицы или просмотра.

Процедуры действия вообще могут не возвращать данных и используются для реализации каких-либо действий.

Хранимым процедурам можно передавать параметры и получать обратно значения параметров, измененные в соответствии с алгоритмами работы хранимых процедур.

Пример хранимой процедуры, добавляющей запись в таблицу и вызывающей генератор TOVAR_GEN для заполнения ключевого поля NOMER_TOVAR (хранимые процедуры для нашей БД описаны ниже и имеют несколько другой вид):

 

CREATE PROCEDURE ADD_TOVAR (

NAME_TOVAR VARCHAR(20))

AS

BEGIN

INSERT INTO TOVAR(NOMER_TOVAR, NAME_TOVAR)

VALUES(GEN_ID(TOVAR_GEN,1),:NAME_TOVAR);

END

 

Вызов данной процедуры в окне SQL-редактора можно осуществить так:

 

EXECUTE ADD_TOVAR(‘Сахар’);

 

Преимущества использования хранимых процедур:

- одну процедуру можно использовать многими приложениями;

- разгрузка приложений клиента путем переноса части кода на сервер (упрощение клиентских приложений);

- при изменении хранимой процедуры все изменения немедленно становятся доступными для всех клиентских приложений;

- улучшенные характеристики выполнения, связанные с тем, что хранимые процедуры выполняются сервером, в частности – уменьшенный сетевой трафик.

Синтаксис хранимой процедуры:

 

CREATE PROCEDURE Имя_процедуры

[ (входной_параметр тип_данных

[, входной_параметр тип_данных …] ) ]

[RETURNS

( выходной_параметр тип_данных

[, выходной_параметр тип_данных …] ) ]

AS

<тело процедуры>;

 

И входные, и выходные параметры могут быть опущены, если в них нет необходимости.

Тело процедуры имеет следующий формат:

 

[<объявление локальных переменных процедуры>]

BEGIN

<операторы SQL>

END

 

При использовании имени параметра или переменной в операторе SQL этому имени предшествует двоеточие.

Формат объявления локальных переменных:

 

DECLARE VARIABLE переменная тип;

[DECLARE VARIABLE переменная тип; …]

 

В теле хранимой процедуры выполнения можно использовать операторы:

· Оператор присваивания

 

переменная= выражение

 

· Переход к последнему оператору END процедуры

 

EXIT

 

· Повторение операторов составного блока, следующих за DO для каждой строки результата оператора SELECT

 

FOR оператор SELECTDO составной оператор

 

· Проверка условия

 

IF (условие)

THEN составной оператор

[ELSE составной оператор]

 

· Цикл: пока условие TRUE, продолжается выполнение составного оператора.

 

WHILE (условие) DO составной оператор

 

Пример:

 

CREATE PROCEDURE SUM_INT (i INTEGER) RETURNS (s INTEGER)

AS

BEGIN

s = 0;

WHILE (i > 0) DO

BEGIN

s = s + i;

i = i – 1;

END

END

 

· любой оператор SQL (INSERT, UPDATE, DELETE, SELECT):

 

Для создания процедуры следует выбрать пункт менюDataBase, New procedure в IBExpert. Появится окно заготовки процедуры (рис. 16).

Рис. 16. Заготовка процедуры в режиме Lazy on.

Посмотреть текст процедуры полностью можно, отключив режим Lazy (рис. 17).

Рис. 17. Заготовка процедуры в режиме Lazy off.

Например, процедура добавления новой записи в таблицу Class может выглядеть следующим образом:

 

CREATE PROCEDURE INS_CLASS (

PNUM_CLASS INTEGER,

PNAMECLASS VARCHAR(3))

AS

DECLARE VARIABLE K INTEGER;

BEGIN

SELECT COUNT(*) FROM CLASS

WHERE NAMECLASS=:PNAMECLASS INTO :K;

IF (:K=0) THEN

INSERT INTO CLASS(NUM_CLASS,NAMECLASS,KOL_PUPIL)

VALUES(:PNUM_CLASS,:PNAMECLASS,0);

ELSE

EXCEPTION HASCLASS; /*ВЫЗОВ ИСКЛЮЧЕНИЯ HASCLASS */

END

 

Данная процедура является процедурой действия и имеет два входных параметра PNUM_CLASS и PNAMECLASS. В ней также объявлена переменная K целого типа. В данную переменную записывается количество записей в таблице CLASS с именем класса =:PNAMECLASS.

Если k<>0, то в таблице уже присутствует запись с таким именем класса (вызывается исключение HASCLASS). В противном случае в таблицу вставляется новая запись. Поле KOL_PUPIL (количество учеников в новом классе) принимает значение 0.

После написания тела процедуры ее следует откомпилировать и при отсутствии ошибок нажать кнопку Commit.

Проверить работоспособность процедуры можно нажатием кнопки Execute Procedure.

При этом появится окно, в котором будут запрашиваться значения параметров процедуры:

Рис. 18. Выполнение процедуры для ее отладки.

Процедура удаления класса выглядит так:

 

CREATE PROCEDURE DEL_CLASS (

PNUM_CLASS INTEGER)

AS

DECLARE VARIABLE K INTEGER;

BEGIN

SELECT COUNT(*) FROM PUPIL

WHERE NUM_CLASS=:PNUM_CLASS INTO :K;

IF (:K=0) THEN

DELETE FROM CLASS WHERE NUM_CLASS=:PNUM_CLASS;

ELSE EXCEPTION CLASSNOTNILL;

END

 

Здесь вызывается исключение CLASSNOTNILL, если в данном классе содержатся ученики.

Процедура обновления класса:

 

CREATE PROCEDURE UPD_CLASS (

PNUM_CLASS INTEGER,

PNAMECLASS VARCHAR(3))

AS

DECLARE VARIABLE K INTEGER;

BEGIN

SELECT COUNT(*) FROM CLASS

WHERE NAMECLASS=:PNAMECLASS INTO :K;

IF (:K=0) THEN

UPDATE CLASS

SET NAMECLASS=:PNAMECLASS

WHERE NUM_CLASS=:PNUM_CLASS;

ELSE

EXCEPTION HASCLASS;

END

 

Процедура обновления таблицы, имеющей более одного неключевого поля, будет несколько отличаться от приведенной выше. Сначала выполняется обновление таблицы с помощью оператора UPDATE, затем ищется количество записей с одинаковыми уникальными значениями. Если количество К равно двум, то следует вызвать исключение. Также можно применять предложение not exists (Select …).

Пример процедуры добавления нагрузки учителя (связующая таблица Teach_Pred):

 

CREATE PROCEDURE INS_TEACH_PRED (

PNUM_TEACH_PRED INTEGER,

PNUM_TEACHER INTEGER,

PNUM_PREDMET INTEGER,

PNAGRUZKA INTEGER)

AS

DECLARE VARIABLE K INTEGER;

BEGIN

SELECT COUNT(*) FROM TEACH_PRED

WHERE NUM_TEACHER=:PNUM_TEACHER AND

NUM_PREDMET=:PNUM_PREDMET INTO :K;

IF (:K=0) THEN

INSERT INTO TEACH_PRED

(NUM_TEACH_PRED,NUM_TEACHER,NUM_PREDMET,NAGRUZKA)

VALUES

(:PNUM_TEACH_PRED,:PNUM_TEACHER,:PNUM_PREDMET,:PNAGRUZKA);

ELSE

EXCEPTION HASNAGRUZKA;

END

 

Аналогичным образом создаются хранимые процедуры, осуществляющие добавление, удаление и обновление данных, для каждой таблицы. Единственное, в нашей таблице классов присутствует поле, определяющее количество учеников в классе. Таким образом, при добавлении нового ученика следует прибавить единицу к этому количеству, а при удалении вычесть единицу. Тогда процедура добавления записи в таблицу учеников следующая:

 

CREATE PROCEDURE INS_PUPIL (

PNUM_PUPIL INTEGER,

PNUM_CLASS INTEGER,

PFIO_PUPIL VARCHAR(20),

PBALL NUMERIC(4,2))

AS

DECLARE VARIABLE K INTEGER;

BEGIN

SELECT COUNT(*) FROM PUPIL

WHERE FIO_PUPIL=:PFIO_PUPIL AND NUM_CLASS=:PNUM_CLASS

INTO :K;

IF (:K=0) THEN

BEGIN

INSERT INTO PUPIL(NUM_PUPIL,NUM_CLASS,FIO_PUPIL,BALL)

VALUES(:PNUM_PUPIL,:PNUM_CLASS,:PFIO_PUPIL,:PBALL);

/*ОБНОВЛЕНИЕ КОЛИЧЕСТВА УЧЕНИКОВ В КЛАССЕ С НОМЕРОМ :PNUM_CLASS*/

UPDATE CLASS SET KOL_PUPIL=KOL_PUPIL+1

WHERE CLASS.NUM_CLASS=:PNUM_CLASS;

END

ELSE

EXCEPTION HASPUPIL;

END

 

Процедура удаления ученика:

 

CREATE PROCEDURE DEL_PUPIL (

PNUM_PUPIL INTEGER)

AS

DECLARE VARIABLE CL INTEGER;

BEGIN

/*ПОЛУЧЕНИЕ НОМЕРА КЛАССА*/

SELECT PUPIL.NUM_CLASS FROM PUPIL WHERE NUM_PUPIL=:PNUM_PUPIL

INTO :CL;

/*УДАЛЕНИЕ ЗАПИСИ ОБ УЧЕНИКЕ*/

DELETE FROM PUPIL WHERE NUM_PUPIL=:PNUM_PUPIL;

/*ОБНОВЛЕНИЕ КОЛИЧЕСТВА УЧЕНИКОВ В КЛАССЕ С НОМЕРОМ :PNUM_CLASS*/

UPDATE CLASS SET KOL_PUPIL=KOL_PUPIL-1

WHERE CLASS.NUM_CLASS=:CL;

END

 

Так как в клиентском приложении данные будем отображать в табличном виде, позволяя изменять содержимое этой таблице, нам потребуется создать для каждой таблицы хранимую процедуру, возвращающую следующее значение генератора. (Эти процедуры не создаются, если приложение выполняется в Delphi 6, 7). В дальнейшем эти процедуры будем вызывать из приложения клиента. Пример такой процедуры для таблицы классов:

 

CREATE PROCEDURE N_CLASS

RETURNS (

N INTEGER)

AS

BEGIN

N=GEN_ID(CLASS_GEN,1);

END

 

Все описанные выше процедуры являются процедурами действия (выполнения). Процедура выбора должна возвращать несколько выходных параметров и которые могут использоваться в операторе SELECT аналогично таблице или просмотру; для возврата значений в вызывающую программу используется оператор SUSPEND.

Например, вернуть список учеников-отличников можно так:

 

CREATE PROCEDURE GET_BEST

RETURNS (FIO VARCHAR(20)) AS

BEGIN

FOR SELECT FIO_PUPIL FROM PUPIL

WHERE BALL=5 INTO :FIO

DO

SUSPEND;/* Этот оператор возвращает указанные в SELECT значения для каждого шага цикла FOR */

END

 

Вызов такой процедуры происходит с помощью оператора вида SELECT * FROM GET_BEST (можно выполнить данный оператор в окне редактора SQL). Процедуры выбора также могут иметь входные параметры.

Рассмотрим более сложный пример процедуры выбора. Пусть нам требуется выводить фамилии учителей и их суммарную нагрузку. Если суммарная нагрузка учителя равна нулю, то такие записи не будем включать в результирующий набор данных. Текст процедуры снабжен комментариями.

 

CREATE PROCEDURE TOTAL_NAGR

RETURNS (

FIO VARCHAR(20),

SUMNAGR INTEGER) /*Суммарная нагрузка учителя*/

AS

DECLARE VARIABLE NAGR INTEGER;

DECLARE VARIABLE N INTEGER;

BEGIN

/*Выбираем номер и фамилию учителя в переменные N и FIO*/

FOR SELECT TEACHER.NUM_TEACHER,TEACHER.FIO_TEACHER

FROM TEACHER INTO :N,:FIO

DO

BEGIN

/*Считаем нагрузку по предметам выбранного учителя в переменную NAGR*/

SUMNAGR=0;

FOR SELECT TP.NAGRUZKA

FROM TEACH_PRED TP

WHERE TP.NUM_TEACHER=:N

INTO :NAGR

DO SUMNAGR=SUMNAGR+NAGR; /*Суммируем нагрузки*/

IF (SUMNAGR>0) THEN /*Если нагрузка есть, то выводим*/

SUSPEND; /*Возвращаем фамилию и нагрузку учителя*/

END

END

 

Так можно подсчитать процент отличников в классе с номером CL:

 

CREATE PROCEDURE PROCENT_OTL (

CL INTEGER)

RETURNS (

PROC NUMERIC(6,2))

AS

DECLARE VARIABLE ALLS INTEGER; /*Число учеников в классе*/

DECLARE VARIABLE CNT INTEGER;/*Число отличников в классе*/

BEGIN

SELECT COUNT(*)

FROM PUPIL

WHERE (NUM_CLASS=:CL) AND (BALL=5) INTO :CNT;

SELECT COUNT(*)

FROM PUPIL WHERE NUM_CLASS=:CL INTO :ALLS;

PROC = :CNT*100/:ALLS;

END

 

Таким образом можно найти процент учеников в классе с именем NCL от общего количества учеников в школе:

 

CREATE PROCEDURE PROC_PUPIL (

NCL VARCHAR(20))

RETURNS (

PROC_IN_CL NUMERIC(6,2))

AS

DECLARE VARIABLE ALL_SUM INTEGER; /*Число учеников в школе*/

DECLARE VARIABLE KOL_IN_CLASS INTEGER; /*Число ученико в классе*/

BEGIN

SELECT SUM(KOL_PUPIL) FROM CLASS INTO :ALL_SUM;

SELECT KOL_PUPIL FROM CLASS

WHERE NAMECLASS=:NCL INTO :KOL_IN_CLASS;

PROC_IN_CL=KOL_IN_CLASS*100/ALL_SUM;

END

 

Приведенные выше процедуры будут использованы в дальнейшем в приложении клиента.