W Cat - SQL за 24 часа

• Название: SQL за 24 часа
• Автор: W Cat
• Жанр: Базы данных

---

DECLARE CURSOR EMP_CURSOR IS

SELECT * FROM EMPLOYEE_TBL

{ ДРУГИЕ ОПЕРАТОРЫ ПРОГРАММЫ }

После определения курсора для доступа к нему стандартом ANSI предусмотрены следующие операторы

OPEN Открывает определенный ранее курсор

FETCH Извлекает данные курсора, помещая их в переменную программы

CLOSE Закрывает курсор после завершения работы с ним

Открытие курсора

При открытии курсора выполняется указанный в определении этого курсора оператор SELECT, и результат выполнения оператора сохраняется в специальной области памяти

Синтаксис оператора для открытия курсора в dBase следующий

OPEN ИМЯ_КУРСОРА

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

OPEN ИМЯ_КУРСОРА [ ПАРАМЕТР1 [, ПАРАМЕТР2 ]]

Например, следующий оператор открывает курсор EMP_CURSOR: OPEN EMP_CURSOR

Доступ к данным курсора

После того как курсор будет открыт, его содержимое (т. е. результат выполнения его запроса) можно прочитать с помощью оператора FETCH.

Синтаксис оператора FETCH в Microsoft SQL Server следующий.

FETCH ИМЯ_КУРСОРА [ INTO СПИСОК_ПОЛУЧАТЕЛЬ ]

Синтаксис оператора в Oracle следующий.

FETCH CURSOR ИМЯ_КУРСОРА {INTO : ГЛАВНАЯ_ПЕРЕМЕННАЯ }

[[ INDICATOR ] : ПЕРЕМЕННАЯ_ИНДИКАТОР ]]

[, : ГЛАВНАЯ_ПЕРЕМЕННАЯ

[[ INDICATOR ] : ПЕРЕМЕННАЯ_ИНДИКАТОР ]]

I USING DESCRIPTOR ДЕСКРИПТОР ]

Синтаксис оператора в dBase следующий.

FETCH ИМЯ_КУРСОРА INTO ПЕРЕМЕННЫЕ_ПАМЯТИ

Например, чтобы извлечь содержимое курсора EMP_CURSOR и поместить в переменную с именем EMP_RECORD, можно использовать оператор FETCH следующего вида.

FETCH EMP_CURSOR INTO EMP_RECORD

Закрытие курсора

Очевидно, если имеется возможность открыть курсор, должна быть предусмотрена и возможность закрыть его. Закрыть курсор несложно, только следует помнить, что после закрытия курсор становится недоступным для программ пользователей.

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

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

CLOSE ИМЯ_КУРСОРА

DEALLOCATE CURSOR ИМЯ_КУРСОРА

При закрытии курсора в Oracle занимаемые им ресурсы освобождаются автоматически без использования оператора DEALLOCATE. Синтаксис оператора закрытия курсора в Oracle следующий.

CLOSE ИМЯ_КУРСОРА

Для освобождения ресурсов в dBase объект должен быть закрыт и открыт вновь. Тогда ресурсы окажутся свободными, а имена - вновь доступными для использования. Синтаксис оператора закрытия курсора в dBase следующий.

CLOSE ИМЯ_КУРСОРА

Как вы могли видеть из предыдущих примеров вариации операторов в разных базах данных могут быть значительными, особенно если эти операторы используют возможности расширений SQL, о которых пойдет речь в ходе урока 24, "Расширения стандартного SQL". По поводу правильного использования курсоров в вашем конкретном случае обратитесь к документации, сопровождающей базу данных.

Сохраненные процедуры и функции

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

Возможность сохранять процедуры - основа процедурного программирования. Команды SQL (CREATE TABLE, INSERT, UPDATE, SELECT'и т.д.) дают вам возможность сообщить базе данных, что делать, но не как делать. Посредством составления процедур вы получаете возможность сообщить ядру базы данных, каким образом следует обрабатывать данные.

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

Функции вызываются процедурами. При вызове функции ей, как процедуре, могут передаваться параметры, затем функция вычисляет некоторое значение и возвращает его вызывающей процедуре для дальнейшего использования.

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

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

CREATE PROCEDURE ИМЯ_ПРОЦЕДУРЫ

[ [ (] @ИМЯ_ПАРАМЕТРА

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ] [, МАСШТАБ ])

[ = DEFAULT ][ OUTPUT ]]

[, @ ИМЯ_ПАРАМЕТРА

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ] [, МАСШТАБ ])

[ = DEFAULT ][ OUTPUT ]] [)]]

[ WITH RECOMPILE ]

AS ОПЕРАТОРЫ_SQL

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

CREATE [ OR REPLACE ] PROCEDURE ИМЯ__ПРОЦЕДУРЫ

[ (АРГУМЕНТ [{IN | OUT | IN OUT) ] ТИП,

АРГУМЕНТ [{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]

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

EXECUTE NEW_PRODUCT ('9999','INDIAN CORN',1.99);

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

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

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

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

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

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

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

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

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

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

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

Триггеры

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

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