SQL за 24 часа

АРГУМЕНТ [{IN 1 OUT | IN OUT} ] ТИП] ] {IS I AS)

ТЕЛО_ПРОЦЕДУРЫ

Вот пример оператора, создающего достаточно простую процедуру.

CREATE PROCEDURE NEW_PRODUCT

(PROD_ID IN VARCHAR2, PROD_DESC IN VARCHAR2, COST IN NUMBER)

AS

BEGIN

INSERT INTO PRODUCTS_TBL

VALUES (PRODJT.D, PROD_DESC, COST);

COMMIT;

END;

Процедура создана.

Эту процедуру можно использовать для добавления новых строк в таблицу PRODUCTS_TBL.

В Microsoft SQL Server сохраненные процедуры используются следующего образом.

EXECUTE [ @ВОЗВРАЩАЕМОЕ_СОСТОЯНИЕ = ] ИМЯ ПРОЦЕДУРЫ

[[@ИМЯ__ПАРАМЕТРА = ] ЗНАЧЕНИЕ |

[@ИМЯ_ПАРАМЕТРА = } @ПЕРЕМЕННАЯ [ OUTPUT ]]

[WITH RECOMPILE]

В Oracle синтаксис следующий.

EXECUTE [ @ВОЗВРАЩАЕМОЕ_СОСТОЯНИЕ = ] ИМЯ__ПРОЦЕДУРЫ

[[@ИМЯ_ПАРАМЕТРА = ] ЗНАЧЕНИЕ | [@ШЯ ПАРАМЕТРА = ] @ПЕРЕМЕННАЯ [

OUTPUT ]]

[WITH RECOMPILE]

Давайте выполним только что созданную процедуру.

EXECUTENEW_PRODUCT ('9999','INDIAN CORN',1.99);

PL/SQL процедура успешно выполнена.

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

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

Использовать сохраненные ранее процедуры удобнее, чем отдельные операторы SQL по целому ряду причин. Некоторые из этих причин перечислены ниже.

• Операторы сохраненной процедуры уже сохранены в базе данных.

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

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

• Сохраненные процедуры могут вызывать другие процедуры и функции.

• Сохраненные процедуры могут вызываться другими программами.

• При использовании сохраненных процедур результат ответ от базы данных обычно получается быстрее

• Использовать процедуры очень просто.

Триггеры

Триггер - это откомпилированная процедура, используемая для выполнения действий, инициируемых происходящими в базе данных событиями Триггер представляет собой сохраненную в базе данных процедуру, которая выполняется тогда, когда в отношении таблицы выполняются определенные действия (операторы языка манипуляции данными - DML). Триггер может выполняться до или после операторов INSERT, DELETE или UPDATE. Триггеры можно использовать, например, для проверки целостности данных перед выполнением INSERT, DELETE или UPDATE. С помощью триггеров можно отменять транзакции, а также модифицировать данные одних таблиц и читать данные других даже из других баз данных.

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

Оператор CREATE TRIGGER

Триггер можно создать с помощью оператора CREATE TRIGGER. Стандарт ANSI предлагает для этого оператора следующий синтаксис.

CREATE TRIGGER TRIGGER NAME

[[BEFORE | AFTER] TRIGGER EVENT ON TABLE NAME]

[REFERENCING VALUES ALIAS LIST ]

[TRIGGERED ACTION TRIGGER EVENT ::=

INSERT UPDATE | DELETE {OF TRIGGER COLUMN LIST]

TRIGGER COLUMN LIST ::= COLUMN NAME [, COLUMN NAME]

VALUES ALIAS LIST ::=

VALUES ALIAS LIST ::=

OLD [ROW] [AS] OLD VALUES CORRELATION NAME |

NEW {ROW} {AS} NEW VALUES CORRELATION NAME |

OLD TABLE {AS} OLD VALUES TABLE ALIAS |

NEW TABLE {AS} NEW VALUES TABLE ALIAS

OLD VALUES TABLE ALIAS ::= IDENTIFIER

NEW VALUES TABLE ALIAS ::= IDENTIFIER

TRIGGERED ACTION ::=

[FOR EACH [ROW | STATEMENT] [WHEN SEARCH CONDITION]]

TRIGGERED SQL STATEMENT

TRIGGERED SQL STATEMENT ::=

SQL STATEMENT | BEGIN ATOMIC [SQL STATEMENT;]

END

В Microsoft SQL Server синтаксис оператора для создания триггера выглядит следующим образом.

CREATE TRIGGER ИМЯ_ТРИГГЕРА

ON ИМЯ_ТАБЛИЦЫ

FOR { INSERT | UPDATE | DELETE [, ..]}

AS

OnEPATOPH_SQL

[ RETURN ]

В Oracle базовый синтаксис оператора следующий.

CREATE [ OR REPLACE ] TRIGGER ИМЯ_ТРИГГЕРА

[ BEFORE | AFTER]

[ DELETE I INSERT | UPDATE]

ON [ ПОЛЬЗОВАТЕЛЬ.ИМЯ_ТАБЛИЦЫ ]

[ FOR EACH ROW ]

[ WHEN УСЛОВИЕ ]

[ БЛОК PL/SQL ]

Вот пример создания триггера.

CREATE TRIGGER EMP_PAY_TRIG

AFTER UPDATE ON EMPLOYEE_PAY_TBL

FOR EACH ROW

BEGIN

INSERT INTO EMPLOYEE_PAY_HISTORY

(EMP_ID, PREV_PAY_RATE, PAY_RATE, DATE_LAST_RAISE,

TRANSACTION_TYPE)

VALUES

(:NEW.EMP_ID, :OLD.PAY_RATE, :NEW.PAY_RATE,