Программные единицы PL/SQL

Язык программирования PL/SQL позволяет создавать разнообразные именованные программные единицы – контейнеры для кода. В них входят:

Процедура – программа, выполняющая одну или несколько команд.

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

Пакет – контейнер для процедур, функций и структур данных.

Триггер – программа, выполняемая в ответ на изменение в БД.

Объектный тип – Oracle-версия класса в объектно-ориентированном языке; элементами объектных типов могут быть процедуры и функции. Объектный тип может содержаться в своих методах код PL/SQL.

Общие атрибуты. Следующие ключевые слова могут применяться при определении процедуры или функции:

OR REPLACE – предназначено для пересоздания существующей программной единицы с сохранением ее привилегий.

AUTHID – определяет, с какими привилегиями будет выполняться программа, и задает имена, например, от имени владельца объекта (DEFINER) или от имени пользователя выполняющего функцию (CURRENT_USER). До версии Oracle8i только встроенные пакеты DBMS_SQL и DBMS_UTILITY выполнялись от имени текущего пользователя. Значение AUTHID по умолчанию – DEFINER. Появилось в Oracle8i.

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

Во втором варианте пользователь, вызывающий программу, делает это со своими собственными привилегиями. Анонимные блоки PL/SQL всегда выполняются с правами текущего пользователя. Чтобы создаваемая программа выполнялась с привилегиями вызвавшего ее пользователя, нужно объявить ее с ключевыми словами AUTHID CURRENT_USER.

AGGREGATE USING – требуется для агрегатных функций. Указывает серверу Oracle, что функция обрабатывает группу строк и возвращает единственный результат. Встроенная функция AVG представляет собой агрегатную функцию. Появилось в Oracle9i.

Deterministic – требуется для индексов по ключу-функции (function-based index). Функция является детерминированной (DETERMINISTIC), если она всегда возвращает одно и то же значение при вызове с одинаковыми параметрами. Детерминированные функции не ссылаются на переменные пакета или БД. Встроенная функция INITCAP является детерминированной, а SYSDATE – нет. Появилось в Oracle8i.

PARALLEL_ENABLED [(PARTITION вх_парам BY {ANY|HASH|RANGE})] – сообщает, что функция безопасна для параллельного выполнения. Инструкция PARTITION BY доступна только для функций, имеющих входной параметр REF CURSOR. Применяется для табличных функций и указывает оптимизатору, каким образом можно секционировать входные данные. Появилось в Oracle8i.

PIPELINED – применяется для табличных функций. Сообщает серверу Oracle, что функция может начать возвращать данные по мере их генерирования вместо того, чтобы вернуть все данные сразу после завершения обработки. Появилось в Oracle9i.

Синтаксис создание процедуры:

CREATE [OR REPLACE] PROCEDURE

[(параметр [, параметр]) ]

[AUTHID {CURRENT_USER | DEFINER}]

[DETERMINISTIC]

{IS | AS}

секция_объявления

BEGIN

секция_выполнения

[EXCEPTION

секция_исключений]

END [имя];

Процедура – эта программная единица или модуль, который выполняет одну или более команд и может получать или возвращать значения посредством списка параметров. Процедуру можно вызвать как автономную исполняемую команду PL/SQL, например:

apply_discount(new_company_id, 0.15) –скидка 15%

Общие ключевые слова: AGGREGATE USING, AUTHID, DETERMINISTIC, PARALLEL_ENABLE и PIPELINED.

Синтаксис определения функции:

CREATE [OR REPLACE] FUNCTION имя

[(параметр [, параметр])]

RETURN return_тип_данных

[AUTHID {CURRENT_USER | DEFINER}]

[DETERMINISTIC]

[PARALLEL_ENABLED]

[PIPELINED]

[AGGREGATE USING]

{IS | AS}

[секция_объявления]

BEGIN

секция_выполнения

[EXCEPTION

секция_исключений]

END [имя];

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

Функция может вызываться в любом месте, где возможно использование выражения того же типа. Вызвать функцию можно следующими способами:

1. в команде присваивания:

sales95:=tot_sales(1995, ‘C’);

2. при задании значения по умолчанию:

DECLARE

sales95 NUMBER DEFAULT tot_sales(1995, ‘C’);

BEGIN

3. в логическом выражении:

IF tot_sales(1995, ‘C’) > 10000

THEN

…

4. в команде SQL:

SELECT first_name, surname

FROM sellers

WHERE tot_sales(1995, ‘C’) > 1000;

5. как аргумент в списке параметров другой программной единицы.

Табличные функции – это функции, принимающие на входе коллекцию или REF CURSOR (набор строк) и возвращающие на выходе коллекцию записей (набор строк). Табличные функции появились в Oracle8i. Начиная с Oracle9i существует возможность применять команду TYPE ROW для идентификации входного и выходного потоков. Такая организация потоков позволяет организовывать конвейер из нескольких функций, избегая необходимости хранения промежуточных таблиц. Табличные функции обычно появляются в инструкции FROM запроса.

Локальные программы – это процедуры или функции, определенные в секции объявления блока PL/SQL. Объявление локальной программы должно находиться в конце секции объявлений, после всех объявлений типов, записей, курсоров, переменных и исключений.

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

Следующая программа определяет локальную процедуру и локальную функцию:

PROCEDURE track_revenue

IS

PROCEDURE calc_total (year_in IN INTEGER) IS

BEGIN

calculations here …

END;

FUNCTION below_minimum (comp_id IN INTEGER)

RETURN BOOLEAN

IS

BEGIN

…

END;

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

Перегрузка программ. PL/SQL позволяет определить внутри любой секции объявлений (в том числе в спецификации или теле пакета) несколько программ с одинаковыми именами. Такой прием называется перегрузкой. Если две или более программ имеют одно и то же имя, они должны отличаться чем-то другим, чтобы компилятор мог определить, которую из них следует использовать.

Рассмотрим пример перегруженных программ в одной из спецификаций встроенного пакета Oracle:

PACKAGE DBMS_OUTPUT

IS

PROCEDURE PUT_LINE (a VARCHAR2);

PROCEDURE PUT_LINE (a NUMBER);

PROCEDURE PUT_LINE (a DATE);

END;

Все процедуры PUT_LINE идентичны во всем, кроме типа данных параметра. Для компилятора такого различия достаточно.

Для успешной перегрузки необходимо, чтобы было выполнено хотя бы одно из приведенных ниже условий:

- наборы параметров должны отличаться по типам данных (числовой, строковый, дата-и-время, логический);

- должны отличаться типы программ (можно создать функцию и процедуру с одинаковыми именами и идентичными списками параметров);

- количество параметров должно быть разным.

Программы не удастся перегрузить, если:

- отличаются только типы данных в инструкции RETURN для функций;

- типы данных параметров относятся к одному семейству (СHAR и VARCHAR2, NUMBER и INTEGER и т.д.);

- отличаются только режимом использования параметров.

Пакеты. Пакет (package) – это группа элементов кода PL/SQL. Пакет может включать в себя такие элементы, как процедуры, функции, константы, переменные, курсоры, имена исключений и команды TYPE (для ассоциативных массивов, записей, REF CURSOR и т.д.).

Пакет может состоять из двух частей: спецификации пакета и тела пакета.

Спецификация пакета имеет следующий синтаксис:

CREATE [OR REPLACE] PACKAGE имя_пакета

[AUTHID {CURRENT_USER | DEFINER}]

{IS | AS}

[ определения публичных типов,

объявления публичных переменных, типов и объектов,

объявления исключений,

прагмы,

объявления курсоров, процедур и функций,

заголовки процедур и функций ]

END [ имя_пакета ];

В спецификации перечисляются все объекты, которые доступны для общего использования в приложениях. Кроме того, спецификация пакета предоставляет всю информацию, необходимую разработчику для использования объектов пакета; можно сказать, что спецификация пакета представляет собой его API (Application Programming Interface). Спецификация необходима. В случае если спецификация или пакет не содержит никаких процедур и функций и нет необходимости в закрытом коде, то тело пакета не требуется.

Тело пакета имеет следующий синтаксис:

CREATE [OR REPLACE] PACKAGE BODY имя_пакета

{IS | AS}

[ определения закрытых типов,

объявления закрытых переменных, типов и объектов,

полные определения курсоров,

полные определения процедур и функций ]

[BEGIN

выполняемые_команды

[EXCEPTION

обработки исключений ]]

END [ имя_пакета ];

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

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

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

Рассмотрим пример пакета:

CREATE [OR REPLACE] PACKAGE time_pkg IS

FUNCTION GetTimestamp RETURN DATE;

PRAGMA RESTRICT_REFERENCES (GetTimestamp, WNDS);

PROCEDURE ResetTimestamp;

END time_pkg;

CREATE [OR REPLACE] PACKAGE BODY time_pkg IS

StartTimeStamp DATE:=SYSDATE;

-- StartTimeStamp – это данные пакета.

FUNCTION GetTimestamp RETURN DATE IS

BEGIN

RETURN StartTimeStamp;

END GetTimeStamp;

PROCEDURE ResetTimeStamp IS

BEGIN

StartimeStamp:=SYSDATE;

END ResetTimeStamp;

END time_pkg;

Любое изменение заголовка делает тело пакет недействительным (invalid). Поэтому следует помнить, что необходимо перекомпилировать пакет. Если не требуется вносить в тело какие-либо изменения, то это можно сделать так:

ALTER PACKAGE test_pkg COMPILE;

Обращение к элементам пакета. На элементы, объявленные в спецификации, из вызывающего приложения ссылается при помощи точечной нотации:

имя_пакета.элемент_пакета

Например, встроенный пакет DBMS_OUTPUT содержит процедуру с именем PUT_LINE, и обращение к ней будет выглядеть следующим образом:

DBMS_OUTPUT.PUT_LINE (Эта строка – параметр);

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

Когда производится вызов какой-либо составляющей пакета, Oracle загружает пакет в память, где он остается все то время, пока пользователь соединен с базой данных. При наличии нескольких сеансов переменные пакета и их значения становятся разделяемыми; следует также заметить, что обращение к объектам, помещенным в пакет, в последующих сеансах может происходить быстрее.

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

При работе с данными пакета не забывайте о следующем:

- на состояние переменных пакета не влияют COMMIT и ROLLBACK;

- oбъявленный в пакете курсор – глобальный. Он остается открытый до тех пор, пока не будет закрыт явно или же до конца сеанса;

- хорошей практикой следует считать скрытие структур данных в теле пакета и создание программ для чтения и записи этих данных.

Директива компилятора SERIALLY_REUSABLE. Если необходимо, чтобы данные пакета существовали только на протяжении выполнения функции или процедуры пакета, но не между вызовами в текущем сеансе, то вы можете сэкономить память, используя директиву компилятора SERIALLY_REUSABLE. Её необходимо включить как в спецификацию, так и в тело пакета. После каждого вызова PL/SQL будет закрывать курсоры и освобождать занятую пакетом память.

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

Триггеры. Триггер (trigger) – это программа, выполняемая в ответ на изменение данных или на определенное событие, происходящее в БД. Существует предопределенный набор сообщений, которые могут быть связаны с триггерами, что позволяет объединять пользовательскую обработку с обработкой, которую выполняет сама СУБД. Запускает или выполняет триггер инициирующее событие.

Триггеры включаются при создании (посредством команды CREATE TRIGGER) и могут быть отменены (с тем, чтобы не срабатывали) при помощи команды ALTER TRIGGER или ALTER TABLE:

ALTER TRIGGER имя_триггера {ENABLE | DISABLE};

ALTER TABLE имя_таблицы {ENABLE | DISABLE} ALL TRIGGERS;

Нельзя создавать триггеры для объектов, принадлежащих пользователю SYS.

CREATE TRIGGER – создать триггер.

CREATE [OR REPLACE] TRIGGER имя_триггера

{BEFORE | AFTER | INSTEAD OF} иниц_событие

ON

[NESTED TABLE столбец_вложенной_таблицы OF представление]

| ссылка_на _таблицу_или_представление | DATABASE [ инструкция_ссылок ]

[FOR EACH ROW [WHEN условие_триггера ]]

END_триггера;

Тело триггера, включенное в команду CREATE TRIGGER - это стандартный блок PL/SQL.

Ключевые слова:

BEFORE | AFTER – триггеры могут запускаться до (BEFORE) или после (AFTER) инициирующего события. Триггер AFTER считается более эффективным, чем триггер BEFORE.

INSTEAD OF – обычно используется с необновляемыми по своей сути представлениями для того, чтобы разрешить обновление базовых таблиц представления при помощи команд INSERT, UPDATE или DELETE.

иниц_событие – одно из перечисленных ниже событий:

INSERT – срабатывает при каждом добавлении строки в таблицу или представление, заданные параметром ссылка_на _таблицу_или_представление.

UPDATE – срабатывает при каждом изменении таблицы или представления, заданного параметра ссылка_на _таблицу_или_представление посредством UPDATE. UPDATE-триггеры могут дополнительно содержать инструкцию OF для того, запретить срабатывание при обновлении определенных (указанных в этой инструкции) столбцов.

DELETE – срабатывает при каждом удалении строки из таблицы или представления, переданного параметра ссылка_на _таблицу_или_представление. Не срабатывает в случае удалении команды TRUNCATE.

CREATE – срабатывает при каждом добавлении нового объекта БД посредством команды CREATE.

ALTER – срабатывает при каждом добавлении нового объекта БД посредством команды ALTER.

DROP – срабатывает при каждом добавлении нового объекта БД посредством команды DROP.

В контексте CREATE, ALTER, DROP объекты понимаются как таблицы или пакеты (все то, что находится в ALL_OBJECTS). Такой триггер может применяться к одной схеме или ко всей БД.

SERVERERROR – срабатывает при записи серверного сообщения об ошибке. Для таких событий разрешены только триггеры AFTER.

LOGON – срабатывает при создании сеанса (подключении пользователя к БД). Для таких событий разрешены только триггеры AFTER.

LOGOFF – срабатывает при закрытии сеанса (отключении пользователя от БД). Для таких событий разрешены только триггеры BEFORE.

STARTUP – срабатывает при открытии БД. Для таких событий разрешены только триггеры AFTER.

SHUTDOWN – срабатывает при закрытии БД. Для таких событий разрешены только триггеры BEFORE.

инструкция_ссылок разрешена только для таких событий, как INSERT, UPDATE, DELETE. Позволяет дать имя не по умолчанию для старой и новой псевдозаписи. Эти псевдозаписи обеспечивают для программы видимость значений, которые были до обновления БД и которые будут после обновлений БД. Применяются для триггеров действующих на уровне строк. Такие записи определяются как записи %ROWTYPE с той лишь разницей, что запрещены ссылки на столбцы типа LONG и LONG RAW. Для них применяется точечная нотация, в теле триггера они предваряются двоеточием. В отличие от других записей, для полей разрешено только индивидуальное присваивание. Для триггеров INSERT все старые поля содержат NULL, для триггеров DELETE все новые поля содержат NULL.

FOR EACH ROW – означает, что триггер действует на уровне строк. Такие триггеры запускаются по одному разу для каждой обработанной строки. По умолчанию триггеры действуют на уровне команды, т. е. запускаются по одному для каждой инициирующей команды.

Триггеры уровня строки имеют программный доступ к значениям записи до и после изменения данных через псевдозаписи. По умолчанию имена этих псевдозаписей:new и:old. Эти записи подобны записям, определённым с помощью атрибута %ROWTYPE, но отличаются тем, что в них нельзя ссылаться на столбцы типа LONG и LONG ROW. Для псевдозаписей (в отличие от обычных записей) нельзя использовать агрегатное присваивание - значение полям можно назначать только индивидуально.

Обозначения:new и:old предшествующие имени поля также называют квалификаторами.

В триггерах INSERT значения всех полей старой записи — NULL, а в триггерах DELETE значения всех полей новой записи — NULL.

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

тело_триггера – это стандартный блок PL/SQL.

Последовательность событий при срабатывании триггеров. Для одной таблицы может быть определено множество различных типов триггеров. Необходимо понимать, в каком порядке они срабатывают. Основное влияние на последовательность выполнения оказывает конструкция FOR EАСH RОW.

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

Триггеры DML, если они определены, запускаются в следующем порядке:

1. триггер BEFORE уровня команды;

2. для каждой строки, обработанной командой;

3. триггер BEFORE уровня строки;

4. инициирующая команда;

5. триггер AFTER уровня строки;

6. триггер AFTER уровня команды.

К событиям DDL относят CREATE, ALTER, DROP. DDL-триггеры могут применяться к какой-то одной схеме или же ко всей БД.

К событиям БД относятся SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDDOWN. Триггер SHUTDOWN срабатывает в случае события SHUTDDOWN NORMAL и SHUTDOWN IMMEDIATE, но не SHUTDOWN ABORT.

Предикаты триггера. Если один триггер применяется для нескольких событий, то в условии триггера не будeт применяться предикаты INSERTING, UPDATING и DELETING для идентификации инициирующего события. Рассмотрим пример, иллюстрирующий пример предикатов триггера:

CREATE OR REPLACE TRIGGER emp_log_t

AFTER INSERT OR UPDATE OR DELETE ON emp

FOR EACH ROW

DECLARE

dmltype CHAR(1);

BEGIN

IF INSERTING THEN

dmltype:= ‘I’;

INSERT INTO emp_log (emp_no, who, operation)

VALUES (:new.empno, USER, dmltype);

ELSIF UPDATING THEN

dmltype:= ‘U’;

INSERT INTO emp_log (emp_no, who, operation)

VALUES (: new.empno, USER, dmltype);

END IF;

END;

Вызов функций PL/SQL в SQL. Хранимые функции могут вызываться из команд SQL подобно тому, как вызываются встроенные функции, такие как DECODE, NVL или RTRIM. При осуществлении таких вызовов необходимо помнить о ряде мер предосторожности и ограничений.

Предложенный ниже формат применяется для вызова собственной функции из SQL. Такая возможность позволяет «переделать» SQL в соответствии с требованиями конкретного приложения.

Синтаксис:

[ имя_схемы. ][ имя_пакета. ] имя_функции [ @db_link ]

[ список_параметров ]

где имя_схемы – это пользователь/владелец функции или пакета (может отсутствовать).

имя_пакета – это пакет, содержащий вызываемую функцию (может отсутствовать).

имя_функции – имя вызываемой функции.

@db_link – имя канала связи с удаленной БД, содержащей функцию (не обязательно).

список_параметров – это список параметров функции (не обязателен).

К вызову хранимых функций в SQL предъявляется ряд требований:

1. Все параметры должны быть входными, параметры IN OUT и OUT не разрешены.

2. Типы данных параметров функции и возвращаемый тип (RETURN) должны быть совместимы с типами данных БД. Аргументы и возвращаемые значения не могут относиться к типу BOOLEAN, типу пользовательских записей, быть ассоциативным массивом и т. д.

3. Для передаваемых функции параметров должно использоваться позиционное представление; представление по имени не поддерживается.

4. Функция должна храниться в БД, а не в локальной программе.

Дата добавления: 2013-12-12