Pl sql хранимые процедуры. TDD для хранимых процедур Oracle

Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL , который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.

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

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

Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP . Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE PROCEDURE [имя схемы].имя процедуры [имя [(параметр [, параметр, ...])] {IS|AS} программа на PL/SQL;

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если процедура с таким именем уже определена, но ключевое слово OR REPLACE не указано, то возвращается сообщение об ошибке и замещения старого текста не происходит.

При описании переменных хранимой процедуры ключевое слово DECLARE не используется. Блок описания переменных начинается сразу после ключевого слова IS или AS .

PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;

Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL , как показано ниже:

Команда создания хранимой процедуры имеет следующий синтаксис:

CREATE FUNCTION [имя схемы].имя функции [имя [(параметр [, параметр, ...])] RETURN тип данных {IS|AS} программа на PL/SQL;

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

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

Вызвать созданную хранимую функцию можно следующим образом:

Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;

Или DROP [имя схемы].имя функции;

Особенности использования процедур и функций в СУБД Oracle

В этом подразделе рассмотрим некоторые особенности использования процедур и функций в PL/SQL :

• Способы связывания формальных и фактических параметров в процедурах и функциях.
• Использование для параметров значений по умолчанию.
• Возникновение алиасных имен при передаче параметров.

При вызове процедур и функций в PL/SQL допускается передача параметров по имени и по позиции. Это означает, что вы указываете, как происходит связывание формальных и действительных параметров. Например, пусть имеется гипотетическая программа

DECLARE x1 INTEGER; x2 REAL; PROCEDURE proc1 (p1 INTEGER, p2 REAL) IS BEGIN ... END;

Процедуру Proc1 можно вызвать следующими эквивалентными способами:

BEGIN ... proc1 (x1, x2); -- передача параметров по позиции proc1 (p2 => x2, p1 => x1); -- передача параметров по имени proc1 (p1 => x1, p2 => x2); -- передача параметров по имени proc1 (x1, p2 => x2); -- передача параметров и по -- позиции, и по имени END;

При передаче параметра по позиции компилятор PL/SQL последовательно связывает первый фактический параметр с первым формальным параметром, второй фактический параметр - со вторым формальным параметром и так далее.

При передаче параметра по имени стрелка, называемая оператором связывания ( association operator ), связывает формальный параметр слева от стрелки с фактическим параметром справа от стрелки, причем порядок следования таких пар не имеет значения.

Можно комбинировать передачу параметра по позиции и по имени. Но при этом следует соблюдать требование того, чтобы передача параметра по позиции предшествовала передаче параметра по имени.

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

Пример. Для нашей учебной базы данных разработаем процедуру создания нового отдела таблице DEPARTAMENT.

PROCEDURE create_dept (new_dname CHAR DEFAULT "Новый", new_loc CHAR DEFAULT "Москва") IS BEGIN INSERT INTO departament VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;

Если при вызове процедуры фактический параметр не передается, то используется значение по умолчанию. Рассмотрим следующие вызовы этой процедуры:

create_dept; create_dept("Маркетинг"); create_dept("Маркетинг", Черноголовка);

При первом вызове не передается никаких параметров, так что используются значения параметра по умолчанию - Новый отдел в г. Москве. Во втором вызове передается один параметр - первый. Для второго используется значение по умолчанию. В третьем случае передаются оба параметра и значения по умолчанию не используются.

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

Пример SQL и хранимых процедур DB2, которые можно использовать с провайдером данных JDBC.

Провайдер данных JDBC может обрабатывать наборы результатов, возвращенные хранимой процедурой. В хранимую процедуру можно передать строковые или числовые входные параметры. Ниже приведен синтаксис запуска хранимой процедуры: call[: индекс ] имя_процедуры [аргумент] ... Где: индекс Дополнительное целое число, указывающее, какой набор результатов должен использоваться провайдером данных. Этот параметр имеет смысл использовать, если хранимая процедура возвращает несколько наборов результатов, а вы хотите собрать значения только из одного из них. Если индекс не указан, то собираются и возвращаются данные из всех наборов результатов. имя_процедуры Имя хранимой процедуры, которую должен запустить провайдер данных. аргумент Входной аргумент хранимой процедуры. Несколько аргументов разделяются пробелами. Если имя аргумента содержит пробел, то заключите имя в кавычки. Если аргумент можно проанализировать как целое число, то он передается в хранимую процедуру как целочисленный аргумент. Любой аргумент, заключенный в кавычки, передается как строковый аргумент.

Примеры SQL Server

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

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

Если не указан индекс, то собираются данные из всех возвращенных наборов результатов. В этом случае убедитесь, что возвращенные данные совместимы с заданными атрибутами. Agent Builder создает атрибуты из первого возвращенного набора результатов; предполагается, что все ожидаемые дополнительные наборы результатов совместимы с первым набором.

Хранимые процедуры DB2

Ниже приведен пример функции DB2, написанной на SQL. В примере показано, как возвратить результаты, которые может обработать провайдер данных JDBC Agent Builder: -- Этот сценарий запускается так: -- db2 -td# -vf db2sample.sql -- Эта процедура показывает, как возвратить запрос из -- хранимой процедуры DB2; этот запрос может использоваться -- провайдером JDBC Agent Builder. Хранимая процедура -- возвращает следующие столбцы: -- Имя Описание Тип данных -- current_timestamp Текущее системное время отметка времени -- lock_timeout Срок ожидания блокировки числовая шкала 0 -- user Пользователь сеанса строка из 128 символов DROP procedure db2sample# CREATE PROCEDURE db2sample() RESULT SETS 1 LANGUAGE SQL BEGIN ATOMIC -- Задать SQL для запроса DECLARE c1 CURSOR WITH HOLD WITH RETURN FOR SELECT CURRENT TIMESTAMP as current_timestamp, CURRENT LOCK TIMEOUT as lock_timeout, CURRENT USER as user FROM sysibm.sysdummy1; -- Передать запрос и возвратить данные OPEN c1; END#

Эту функцию можно вызвать из Agent Builder, используя синтаксис, определенный для других хранимых процедур. Для выполнения этой хранимой процедуры в качестве оператора JDBC нужно задать call db2sample .

Хранимые процедуры Oracle

Хранимые процедуры Oracle не возвращают наборы результатов. Вместо этого нужно создать функцию, которая возвращает указатель ссылки Oracle. Ниже приведен пример функции Oracle, написанной на PL/SQL ; в примере показано, как возвратить результаты, которые может обработать провайдер данных JDBC Agent Builder: CREATE OR REPLACE FUNCTION ITMTEST RETURN SYS_REFCURSOR IS v_rc SYS_REFCURSOR; НАЧАТЬ OPEN v_rc FOR SELECT * FROM ALL_CLUSTERS; RETURN v_rc; END;

Эту функцию можно вызвать из Agent Builder, используя синтаксис, определенный для других хранимых процедур. Для выполнения этой хранимой процедуры в качестве оператора JDBC нужно задать call ITMTEST . Так как функция Oracle должна возвращать ссылку указателя, функции Oracle могут обрабатывать только один набор результатов. Это значит, что опция индекса не поддерживается для Oracle, так как методов для возвращения нескольких наборов результатов нет.

Хранимая процедура (stored procedure) - это программа, которая вы­полняет некоторые действия с информацией в базе данных и при этом сама хранится в базе данных. В Oracle хранимые процедуры можно мож­но писать на языках PL/SQL и Java.

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

Хранимые процедуры используются для многих целей. Хотя админи­страторы баз данных используют их для выполнения рутинных задач ад­министрирования, главной областью их применения являются все же при­ложения баз данных. Эти процедуры могут вызываться из прикладных программ, написанных на таких языках, как Java, С#, С++ или VB.Net, а также из веб-сценариев, написанных на VBScript или JavaScript. Кроме того, эти процедуры можно вызывать в интерактивном режиме из команд­ной оболочки SQL*Plus.

Можно выделить следующие преимущества хранимых процедур:

В отличие от кода приложений, хранимые процедуры никогда не пере­даются на клиентские компьютеры. Она всегда находятся в базе данных и выполняются СУБД на том компьютере, где располагается сервер базы данных. Таким образом, они более безопасны, чем распространяемый код приложения, а кроме того, снижают сетевой трафик. Хранимые процеду­ры постепенно становятся предпочтительным режимом реализации логи­ки приложения в сети Интернет и корпоративных интрасетях. Еще одно преимущество хранимых процедур заключается в том, что SQL-операторы в них могут быть оптимизированы компилятором СУБД.

Пример хранимой процедуры

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

В листинге 4.6 изображена хранимая процедура, выполняющая эту за­дачу. Процедура, которая называется Customer_Insert, принимает четы­ре параметра: newname (имя нового клиента), newareacode (код региона), newphone (телефон) и artistnationality (национальность художника). Клю­чевое слово IN указывает на то, что все эти параметры являются входны­ми. Выходные параметры (которых у этой процедуры нет) обозначаются ключевым словом OUT, а параметры, играющие роль и входных и вы­ходных, - сочетанием IN OUT. Следует обратить внимание, что для па­раметра указывается только тип данных, а длина не указывается. Oracle определит длину из контекста.

Листинг 4.6.

CREATE OR REPLACE PROCEDURE Customer_Insert (
newname IN char, newareacode IN char, newphone IN char,
artistnationality IN char
AS
rowcount integer(2);
CURSOR artistcursor IS SELECT ArtistID FROM ARTIST
WHERE Nationality = artistnationality;
BEGIN
SELECT Count(*) INTO rowcount FROM CUSTOMER
WHERE Name = newname AND AreaCode = newareacode AND PhoneNumber = newphone;
IF rowcount > 0 THEN BEGIN
DBMS_OUTPUT.PUT_LINE ("There is client in DB! Count is " I I rowcount); RETURN;
END; END IF;
INSERT INTO CUSTOMER
(CustomerlD, Name, AreaCode, PhoneNumber)
VALUES (CustID.NextVal, newname, newareacode, newphone);
FOR artist IN artistcursor LOOP
INSERT INTO CUSTOMER_ARTIST_INT (CustomerlD, ArtistID)
VALUES (CustID.CurrVal, artist.Artist ID); END LOOP;
DBMS_OUTPUT.PUT_LINE ("Client is added!");
END;
/

Раздел объявления переменных следует за ключевым словом AS. Опе­ратор SELECT определяет переменную-курсор (cursor variable) с именем artistcursor. Этот курсор выделяет из таблицы ARTIST для обработки строки всех художников заданной национальности.

В первой части процедуры проверяется, есть ли в базе информация о данном клиенте. В этом случае никакие действия не предпринимаются, а пользователю с помощью пакета Oracle DBMS_OUTPUT выводится со­ответствующее сообщение. Следует обратить внимание, что для вывода строки и значения переменной используется следующий синтаксис:

DBMS_OUTPUT.PUT_LINE ("<строка>" И <переменная>);

Пользователь получит это сообщение только в том случае, если проце­дура будет вызвана из SQL*Plus. В случае вызова процедуры иным путем, например с помощью браузера через Интернет, пользователь не увидит этого сообщения. Чтобы сообщить пользователю об ошибке, разработчик должен воспользоваться выходным параметром или сгенерировать исклю­чение.

Кроме того, чтобы такие сообщения стали видимыми, следует выпол­нить команду

Set serveroutput on;

Если при работе в SQL*Plus вы не видите сообщений, выводимых ва­шими процедурами, то, скорее всего, вы не выполнили этот оператор.

Оставшаяся часть процедуры в листинге 4.6 вставляет данные о новом клиенте и затем перебирает всех художников выбранной национальности. Обратите внимание на использование специальной конструкции PL/SQL FOR artist IN artistcursor. Эта конструкция выполняет несколько задач. Прежде всего, она открывает курсор и считывает первую строку. Затем она последовательно обрабатывает все строки под курсором и по оконча­нии обработки передает управление следующему оператору после FOR. Заметьте также, что обращение к столбцу ArtistID текущей строки про­исходит с использованием синтаксиса artist.ArtistID, где artist - это имя переменной цикла FOR, а не курсора.

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

start Имя_файла_процедуры

Если вы что-то ввели неправильно, у вас могут возникнуть ошибки компиляции. К сожалению, SQL*Plus не покажет вам эти ошибки автома­тически, а выдаст сообщение "Warning: Procedure created with compilation errors "(Предупреждение: При компиляции процедуры обнаружены ошиб­ки). Чтобы увидеть ошибки, введите команду:

Show errors;

Если синтаксических ошибок не было, вы получите сообщение "Procedure created "(Процедура создана). Теперь вы можете вызвать эту процедуру с помощью команды EXECUTE или EXEC:

Exec Customer_Insert("Michael Bench", "203", "555-2014", "US");

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