Хранимые процедуры - это объекты базы данных, которые представляют собой небольшие программы, манипулирующие данными и выполняемые на сервере.
Если необходимо разработать хранимую процедуру, теперь есть как минимум три возможности: использовать язык PL/SQL, Java или С. Я перечислил их в порядке предпочтения. Большую часть обработки в базе данных можно выполнить на PL/SQL. Если что-то нельзя сделать с помощью PL/SQL (в основном это касается интерфейсов с ОС), вступает в игру язык Java. Язык С используется при наличии уже созданного кода на С или в тех случаях, когда нельзя решить задачу средствами Java.
Выполнение в базе данных хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества:
необходимые операторы уже содержатся в базе данных;
все они прошли этап синтаксического анализа и находятся в исполняемом формате; перед выполнением хранимой процедуры SQL Server генерирует для нее план исполнения, выполняет ее оптимизацию и компиляцию;
хранимые процедуры поддерживают модульное программирование, так как позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части;
хранимые процедуры могут вызывать другие хранимые процедуры и функции;
хранимые процедуры могут быть вызваны из прикладных программ других типов;
как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных операторов;
хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно указать всего лишь имя нужной хранимой процедуры. Это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.
Создатель. Пользователь, создавший скомпилированный хранимый объект и владеющий им. (Говорят также, что объект находится в схеме пользователя.) К скомпилированным хранимым объектам относятся пакеты, процедуры, функции, триггеры и представления,
• Вызывающий. Пользователь, с привилегиями которого работает текущий сеанс. Это может быть текущий зарегистрированный пользователь, но может быть и другой пользователь.
До версии Oracle 8i все скомпилированные хранимые объекты выполнялись с правами создателя объекта. По отношению к соответствующей схеме происходило и разрешение имен. Другими словами, набор привилегий, непосредственно предоставленных владельцу (создателю) объекта использовался при компиляции для определения того:
• к каким объектам (таблицам и т.д.) фактически обращаться;
• есть ли у создателя необходимые для доступа к этим объектам привилегии.
Это статическое связывание, выполняемое на этапе компиляции, распространяется так далеко, что учитываются только непосредственно предоставленные создателю привилегии (другими словами, в ходе компиляции и выполнения хранимой процедуры роли не учитываются). Кроме того, при выполнении процедуры с правами создателя, она будет работать с базовым набором привилегий создателя, а не вызвавшего процедуру.
Начиная с Oracle 8i появилась возможность выполнять процедуры с правами вызывающего, что позволяет создавать процедуры, функции и пакеты, выполняющиеся с набором привилегий вызывающего, а не создателя.
Возможность выполнять код с правами вызывающего позволяет, например, создать хранимую процедуру, работающую с набором привилегий пользователя, который ее выполняет. В результате хранимая процедура может работать правильно и корректно для одного пользователя (который имеет доступ ко всем необходимым объектам), но не работать для другого (у которого такого доступа нет). Причина состоит в том, что доступ к базовым объектам проверяется не во время компиляции, а во время выполнения
Представления и триггеры создаются и работают только с правами создателя.
Пример:
Для создания процедуры, работающей с правами создателя, необходим следующий код:
create or replace procedure definer_proc
as
begin
<тело процедуры>
End
Для создания такой же процедуры, работающей с правами вызывающего, код надо немного изменить:
create or replace procedure invoker_proc
AUTHID CURRENT_USER
as
begin
<тело процедуры>
end
Создадим процедуру, которая в качестве параметра получает фамилию сотрудника и печатает список всех договоров, которые он курирует. Это второй способ возвращения значений из процедуры - печать результата выполнения команды SELECT.
CREATE PROCEDURE show_contracts @name Varchar(30)
AS
SELECT contract_num, contract_date, contract_type
FROM k_contract c JOIN k_staff s ON c.staff_num=s.staff_num
WHERE s.staff_name=@name
Для запуска этой процедуры на выполнение нужно выполнить, например, команду
EXEC show_contracts 'Иванов'
Дополнительно:
Объявление переменных имеет вид
DECLARE имя_переменной тип_переменной
Блок операторов заключается в команды BEGIN ... END
Оператор присвоения выглядит довольно странно:
SELECT переменная=значение
Зато с помощью такого синтаксиса при выполнении команды SELECT можно сохранять значения в переменных. Можно также использовать синтаксис
SET переменная=значение
Условный оператор выглядит так:
IF условие
Оператор1
[ELSE
Оператор2]
Цикл по счетчику отсутствует, есть только цикл по условию
WHILE условие
Оператор
Для прерывания цикла используется команда BREAK
Для прерывания итерации цикла используется команда CONTINUE
Оператор печати имеет вид PRINT выражение
Выход из процедуры: RETURN [код_завершения]. Это первый способ возвращения значения из процедуры - только целочисленное значение.
Команда RAISERROR сообщение, уровень_опасности, код_состояния применяется для вывода сообщений об ошибках и прочих предупреждений в стандартной для SQL server форме.
Выражение CASE применяется для селективного выбора на основании нескольких опций: