Технологии бд теоретические основы организации бд. Реляционная модель данны



страница12/20
Дата23.04.2016
Размер2.56 Mb.
1   ...   8   9   10   11   12   13   14   15   ...   20

3.5.Работа с хранимыми процедурами

3.5.1.Понятие хранимой процедуры


Хранимые процедуры это процедуры и функции, хранящиеся непосредственно в базе данных в откомпилированном виде и которые могут запускаться пользователями или приложениями, работающими с базой данных. Хранимые процедуры обычно пишутся либо на специальном процедурном расширении языка SQL (например, PL/SQL для ORACLE или Transact-SQL для MS SQL Server), или на некотором универсальном языке программирования, например, C++, с включением в код операторов SQL в соответствии со специальными правилами такого включения. Основное назначение хранимых процедур - реализация бизнес-процессов предметной области.

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



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

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

3.5.2.Преимущества использования ХП:


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

    2. разгрузка приложения клиента путем переноса части кода на сервер и вследствие этого – упрощение клиентских приложений;

    3. при изменении ХП все изменения немедленно становятся доступными для всех клиентских приложений;

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

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


Хранимая процедура создается оператором:

CREATE PROCEDURE ИмяПроцедур

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

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

[ RETURNS

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

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

AS

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

BEGIN

< оператор >

[ < оператор > … ]

END ;
Пример:

CREATE PROCEDURE FIND_MAX_KOLVO (IN_TOVAR VARCHAR(20))

RETURNS (MAX_KOLVO INTEGER)

AS

BEGIN

SELECT MAX(KOLVO)

FROM RASHOD

WHERE TOVAR = :IN_TOVAR

INTO :MAX_KOLVO;

SUSPEND;

END ;

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


3.5.4.Алгоритмический язык хранимых процедур

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

DECLARE VARIABLE <имя переменной> < тип данных > ;

Пример:

CREATE PROCEDURE FULL_ADR (TOVAR VARCHAR(20) )

RETURNS GOROD_ADRES VARCHAR(40)) AS

DECLARE VARIABLE NAIDEN_POKUPATEL VARCHAR(20);

DECLARE VARIABLE MAX_KOLVO INTEGER;

BEGIN

….

END ;
Операторные скобки :

Используются для указания границ составного оператора

BEGIN

...

END ;
Оператор присваивания:

Имя_переменной = выражение ;

Пример.

OUT_TOVAR = UPPER(TOVAR);
Оператор условия :

IF ( < условие> ) THEN < оператор 1>

[ELSE < оператор 2 > ]:
Оператор SELECT

Оператор SELECT используется в ХП для выдачи единичной строки. К обычному формату SELECT в процедурный оператор добавлено предложение:

INTO :переменная [, переменная … ]

Пример:



SELECT AVG(KOLVO), SUM(KOLVO)

FROM RASHOD

WHERE TOVAR = :IN_TOVAR

INTO :AVG_KOLVO, :SUM_KOLVO;

Оператор FOR SELECT … DO

FOR < оператор SELECT >

DO < оператор > ;

Оператор SELECT используется в ХП в расширенном синтаксисе с INTO. Для каждой строки полученного результирующего НД выполняется оператор, следующий за словом DO. Этим оператором часто бывает SUSPEND, который приводит к возврату выходных параметров в вызывающее приложение.



Пример:

CREATE PROCEDURE RASHOD_TOVARA (IN_TOVAR VARCHAR(20) )

RETURNS (OUT_DAT DATE, OUT_POKUP VARCHAR(20),

OUT_KOLVO INTEGER) AS

BEGIN

FOR SELECT DAT_RASH, POKUP, KOLVO FROM RASHOD

WHERE TOVAR = :IN_TOVAR

INTO :OUT_DAT, :OUT_POKUP, :OUT_KOLVO

DO

SUSPEND;

END
Оператор SUSPEND

Оператор SUSPEND передает в вызывающее приложение значения результирующих параметров (перечисленных после слова RETURNS в описании функции), имеющие место на момент выполнения SUSPEND. Когда от оператора SELECT вызывающего приложения приходит запрос на следующее значение выходных параметров , выполнение хранимой процедуры возобновляется.

Пример:

CREATE PROCEDURE POK_LIST (IN_TOVAR VARCHAR(20) )

RETURNS (POK VARCHAR(20) ) AS

DECLARE VARIABLE AVG_KOLVO INTEGER;

BEGIN

SELECT AVG(KOLVO) FROM RASHOD

WHERE TOVAR = :IN_TOVAR

INTO :AVG_KOLVO;

FOR SELECT POKUP FROM RASHOD

WHERE KOLVO >= :AVG_KOLVO

INTO :POK

DO

BEGIN

IF (:POK IS NULL) THEN

POK = “Покупатель не указан”;

SUSPEND;

END

END
Оператор WHILE … DO

WHILE (<условие>) DO

< оператор > ;

В цикле проверяется выполнение условия. Если истинно – выполняется оператор. Цикл продолжается пока условие не станет ложным.



Пример:

CREATE PROCEDURE SUM_0_N ( N INTEGER )

RETURNS ( S INTEGER ) AS

DECLARE VARIABLE TMP INTEGER;

BEGIN

S = 0;

TMP=1;

WHILE ( TMP <= N ) DO

BEGIN

S = S+TMP;

TM = TPM + 1;

END

END
Оператор EXIT

Оператор EXIT инициирует прекращение выполнения процедуры и выход в вызывающее приложение.

Пример:

CREATE PROCEDURE MAX_VALUE ( А INTEGER, B INTEGER )

RETURNS ( M_V INTEGER ) AS

DECLARE VARIABLE TMP INTEGER;

BEGIN

IF ( :A IS NULL OR :B IS NULL ) THEN EXIT;

IF ( :A > :B ) THEN M_V = :A ;

ELSE M_V = :B ;

END
Оператор EXECUTE PROCEDURE

Оператор вызова другой хранимой процедуры:

EXECUTE PROCEDURE имя [параметр [, параметр … ] ] ;

[ RETURNING_VALUES параметр [, параметр … ] ];

Пример:



EXECUTE PROCEDURE AVG_KOLVO (:IN_TOVAR)



RETURNING_VALUE :AVG_KOLVO_TOVAR;


Оператор POST_EVENT

Оператор применяется для посылки сервером клиентским приложениям сообщения о наступлении какой-либо ситуации, связанной с именем события.

POST_EVENT “Имя события”;

После выполнения оператора наступает событие с указанным именем. Сервер БД уведомляет о наступлении этого события все активные приложения, зарегистрировавшие свой интерес к нему с помощью оператора EVENT INIT.



Обмен событиями между сервером и приложениями может широко использоваться. Например, с его помощью можно создать клиентское приложение, которое будет видеть все изменения, вносимые в БД другими пользователями. Для этого нужно, чтобы в соответствующий момент возбуждалось заданное событие. Получив сообщение, программа-наблюдатель отображает внесенные изменения.

3.5.5.Изменение и удаление ХП


Изменение ХП:

ALTER PROCEDURE ИмяПроцедур

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

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

[ RETURNS

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

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

AS

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

Удаление ХП:

DROP PROCEDURE ИмяПроцедуры
1   ...   8   9   10   11   12   13   14   15   ...   20


База данных защищена авторским правом ©bezogr.ru 2016
обратиться к администрации

    Главная страница