Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 18 Хранимые процедуры 18.1. Введение Напомним, что PL/SQL — язык Oracle четвертого поколения, объединяющий структурированные элементы процедурного языка программирования с язы- ком SQL, разработанный специально для организации вычислений в среде клиент-сервер. Он позволяет передать на сервер программный блок PL/SQL, содержащий логику приложения, как оператор SQL, одним запросом. Ис- пользуя PL/SQL, можно значительно уменьшить объем обработки в клиент- ской части приложения и нагрузку на сеть. Например, может понадобиться выполнить различные наборы операторов SQL в зависимости от результата некоторого запроса. Запрос, последующие операторы SQL и операторы ус- ловного управления могут быть включены в один блок PL/SQL и пересланы серверу за одно обращение к сети. При этом вся логика приложений делится на клиентскую и серверную части. Серверная часть может быть реализована в виде функций, хранимых про- цедур, триггеров и пакетов. Функции — часть логики приложения, ориентированной на выполнение кон- кретного комплекса операций на сервере, результат которых возвращается в виде значения функции. Откомпилированные функции и их исходные тек- сты содержатся в базе данных. Хранимые процедуры — часть логики приложения, особенно нуждающаяся в доступе к базе данных, может храниться там, где она обрабатывается (на сер- вере). Хранимые процедуры не возвращают значения результата, обеспечивают удобный и эффективный механизм безопасности. Откомпилированные храни- мые процедуры и их исходные тексты содержатся в базе данных. Часть VI. Создание приложений на SQL 316 Пакеты — часть логики приложения: функций и процедур, предназначенных для решения задач в рамках одной подсистемы автоматизированной инфор- мационной системы. Триггерыбазы данных. Можно использовать триггеры, чтобы организовать сложный контроль целостности, выполнять протоколирование (аудит) и дру- гие функции безопасности, реализовать в приложениях выдачу предупреж- дений и мониторинг. Декларативная целостность. Ограничения активизируются сервером всякий раз, когда записи вставляются, обновляются или удаляются. В дополнение к ограничениям ссылочной целостности, которые проверяют соответствие первичного и внешнего ключей, можно также накладывать ограничения на значения, содержащиеся в столбцах таблицы. Поддержка целостности на сервере уменьшает размер кода клиентской части, необходимого для провер- ки допустимости данных, и увеличивает устойчивость бизнес-модели, опре- деленной в базе данных. 18.2. Хранимые процедуры Недостатком выполнения сценариев анонимных процедур (см. разд. 17.2.1) в распределенной вычислительной среде является то, что необходимо хра- нить актуальную версию этих сценариев на большом числе компьютеров. Программное обеспечение намного проще хранить в базе данных (в одном месте), чтобы к нему мог обратиться каждый. Для этого используются хра- нимые процедуры (функции и пакеты). 18.2.1. Создание описания процедуры Предложение для создания ( CREATE ) или замены ( CREATE OR REPLACE ) неза- висимой (не входящей в состав пакета) процедуры имеет вид: CREATE [OR REPLACE] PROCEDURE [схема.]имя_процедуры [(параметр1 [IN | OUT | IN OUT] [NOCOPY] тип_данных [,...] ) ] {IS | AS} {блок_кода | LANGUAGE {JAVA NAME имя_внешней_программы | С [NAME имя_внешней_программы] LIBRARY имя_библиотеки [AGENT IN (аргумент [,...]) [WITH CONTEXT]] [PARAMETERS (параметр [,...]) ] }; Глава 18. Хранимые процедуры 317 Создается новая пользовательская процедура. Здесь: OR REPLACE предназначено длязамены существующей процедуры без предварительного ее удаления и последующего переназначения всех связанных с ней объектов. [IN | OUT | IN OUT] Определяет, является ли параметр для процедуры входным, выходным или и тем и другим одновременно. NOCOPY Этот параметр увеличивает производительность, если аргумент типа OUT или IN OUT очень велик (например, относится к типу VARRAY или RECORD ). IS | AS Ключевые слова IS | AS в Oracle идентичны. Любое из них начинает блок_кода блок_кода Тело (текст) процедуры на языке PL/SQL. LANGUAGE В качестве альтернативы (для хранимых процедур на Java и С) можно ис- пользовать предложение LANGUAGE , в котором определяется реализация внешней программы на Java или С. Параметры и семантика объявлений спе- цифичны для Java и С, а не для SQL. 18.2.2. Удаление описания процедуры Для удаления описания процедуры используется предложение: DROP PROCEDURE [схема.]имя_процедуры При удалении хранимой процедуры неработоспособными становятся все за- висимые объекты. Зависимые объекты будут возвращать ошибку при обра- щении к ним до тех пор, пока не будет создана хранимая процедура. Если хранимая процедура воссоздана и осуществляется обращение к зависимому объекту, зависимый объект будет перекомпилирован. 18.2.3. Перекомпиляция процедуры Для перекомпиляции процедуры используется предложение: ALTER PROCEDURE [схема.]имя_процедуры COMPILE; Часть VI. Создание приложений на SQL 318 18.2.4. Пример создания процедуры Создадим хранимую процедуру, выполняющую те же действия, что и ано- нимный блок (см. разд. 17.2.1). Текст ее тела отличается от текста тела аноним- ного блока лишь отсутствием обращения к утилите DBMS_OUTPUT.PUT_LINE , используемой для вывода результата: CREATE OR REPLACE PROCEDURE p_fio -- имя процедуры (tekst VARCHAR2, -- входной параметр (опущен умалчиваемый IN) result OUT VARCHAR2) -- параметр с OUT позволяет возвращать -- обновленное значение подпрограмме, вызывающей данную процедуру IS -- описание локальных переменных, используемых в теле процедуры kol INTEGER; /* Количество символов в проверяемом тексте */ BEGIN /* исполняемая часть (тело) процедуры*/ result := LOWER(RTRIM(LTRIM(tekst))); /* первый вариант результата, полученный после удаления из входного текста начальных (LTRIM) и конечных (RTRIM) пробелов, а также преобразования всех букв текста в строчные (LOWER) */ kol := LENGTH(result); /* определение числа символов, оставшихся в тексте после удаления начальных и конечных пробелов */ IF kol > 0 THEN /* если текст содержит символы, то его дальнейшее преобразование и проверка на наличие запрещенных символов */ result := REPLACE(result,' ',' '); -- замена двух пробелов на один result := REPLACE(result,'- ','-'); -- замена тире и пробела на тире result := REPLACE(result,' -','-'); -- замена пробела и тире на тире kol := LENGTH(result); -- определение числа оставшихся символов FOR i IN 1..kol LOOP /* перебор всех символов текста */ IF INSTR('- абвгдеѐжзийклмнопрстуфхцчшщъыьэюя',SUBSTR(result,i,1))=0 THEN /* с помощью функции INSTR определяется позиция первого включения i-го символа текста (вырезанного с помощью функции SUBSTR) в набор '-абвгдеѐжзийклмнопрстуфхцчшщъыьэюя', и если эта позиция равна 0 (i-го символа нет в наборе), то производится: */ result := '0'; -- установка нулевого результата и EXIT; -- выход из цикла Глава 18. Хранимые процедуры 319 END IF; END LOOP; ELSE result := '0'; -- установка нулевого результата при отсутствии -- в тексте символов, отличных от пробелов END IF; IF result <> '0' THEN -- если текст содержит символы, то: result := INITCAP(result); /* преобразование первых букв слов текста в заглавные */ END IF; END p_fio; / Так же как и текст анонимного блока, текст команды по созданию процедуры можно ввести с помощью текстового редактора в файл (например, с именем p_fio.sql) и выполнить в SQL*Plus команду @p_fio.sql или вводить строчка за строчкой прямо в SQL*Plus. После создания процедуры p_fio ее можно вызвать из другой процедуры или анонимного блока: DECLARE vchod VARCHAR2(20) := 'Жан - жак руссо'; rezult VARCHAR2(20); BEGIN p_fio (vchod, rezult); dbms_output.put_line(rezult); /* использование встроенного пакета DBMS_OUTPUT для вывода на экран результата преобразования; если ранее не выполнялась установка SET SERVEROUTPUT ON, разрешающая вывод на экран информации, заданной в DBMS_OUTPUT, то ее надо выполнить до выполнения блока */ END; / При обращении к процедуре в качестве фактических параметров можно ис- пользовать любые выражения и, естественно, их составляющие, например, константы: p_fio ('Жан - жак руссо', rezult); или p_fio ('Жан'||' - жак'||' руссо', rezult); В том и другом случае на экран будет выведен текст: Жан-Жак Руссо Часть VI. Создание приложений на SQL 320 18.3. Функции Функция PL/SQL отличается от процедуры тем, что возвращаемое значение расположено в ее имени так же, как и в стандартных функциях (например, SIN , ABS , SUBSTR ). 18.3.1. Создание описания функции Предложение для создания функции имеет следующий синтаксис: CREATE [OR REPLACE] FUNCTION [схема.]имя_функции [(параметр1 [IN | OUT | IN OUT] [NOCOPY] тип_данных [,...] ) ] RETURN тип_данных {IS | AS} {блок_кода | LANGUAGE {JAVA NAME имя_внешней_программы | С [NAME имя_внешней_программы] LIBRARY имя_библиотеки [AGENT IN (аргумент [,...]) [WITH CONTEXT]] [PARAMETERS (параметр [,...]) ] }; Создается новая пользовательская функция. Предложение OR REPLACE пред- назначено длязамены существующей функции без предварительного ее уда- ления и последующего переназначения всех связанных с ней объектов. [IN | OUT | IN OUT] Определяет, является ли параметр для функции входным, выходным или и тем и другим одновременно. NOCOPY Этот параметр увеличивает производительность, если аргумент типа OUT или IN OUT очень велик (например, относится к типу VARRAY или RECORD ). RETURN тип_данных Используется для описания типа данных значения, которое будет размещено в имени функции. IS | AS Ключевые слова IS | AS в Oracle идентичны. Любое из них начинает блок_кода блок_кода Тело (текст) функции на языке PL/SQL. LANGUAGE В качестве альтернативы (для функций на Java и С) можно использовать предложение LANGUAGE , в котором определяется реализация внешней про- Глава 18. Хранимые процедуры 321 граммы на Java или С. Параметры и семантика объявлений специфичны для Java и С, а не для SQL. 18.3.2. Удаление описания функции Для удаления описания функции используется команда: DROP FUNCTION [схема.]имя_функции; 18.3.3. Перекомпиляция функции Для перекомпиляции функции используется команда: ALTER FUNCTION [схема.]имя_функции COMPILE ; 18.3.4. Пример создания функции Создадим функцию, выполняющую те же действия, что и процедура (см. разд. 18.2), текст ее тела отличается от текста тела процедуры наличием опи- сания возвращаемого значения ( RETURN VARCHAR2 ) и предложением RETURN result , завершающим выполнение функции и присваивающим значение ее имени: CREATE OR REPLACE FUNCTION fio -- имя функции (tekst VARCHAR2) -- параметр (текст фамилии, имени или отчества) -- и тип данных этого текста RETURN VARCHAR2 -- тип возвращаемого значения IS -- описание локальных переменных, используемых в теле функции result VARCHAR2(20); kol INTEGER; /* Количество символов в проверяемом тексте */ BEGIN -- начало тела функции result := LOWER(RTRIM(LTRIM(tekst))); /* первый вариант результата, полученный после удаления из входного текста начальных (LTRIM) и конечных (RTRIM) пробелов, а также преобразования всех букв текста в строчные (LOWER) */ kol := LENGTH(result); /* определение числа символов, оставшихся в тексте после удаления начальных и конечных пробелов */ Часть VI. Создание приложений на SQL 322 IF kol > 0 THEN /* если текст содержит символы, то его дальнейшее преобразование и проверка на наличие запрещенных символов */ result := REPLACE(result,' ',' '); -- замена двух пробелов на один result := REPLACE(result,'- ','-'); -- замена тире и пробела на тире result := REPLACE(result,' -','-'); -- замена пробела и тире на тире kol := LENGTH(result); -- определение числа оставшихся символов FOR i IN 1..kol LOOP /* перебор всех символов текста */ IF INSTR('- абвгдеѐжзийклмнопрстуфхцчшщъыьэюя',SUBSTR(result,i,1))=0 THEN /* с помощью функции INSTR определяется позиция первого включения i-го символа текста (вырезанного с помощью функции SUBSTR) в набор '-абвгдеѐжзийклмнопрстуфхцчшщъыьэюя', и если эта позиция равна 0 (i-го символа нет в наборе), то производится: */ result := '0'; -- установка нулевого результата и EXIT; -- выход из цикла END IF; END LOOP; ELSE result := '0'; -- установка нулевого результата при отсутствии -- в тексте символов, отличных от пробелов END IF; IF result <> '0' THEN -- если текст содержит символы, то: result := INITCAP(result); /* преобразование первых букв слов текста в заглавные */ END IF; RETURN result; END fio; / Текст команды по созданию функции вводится в Oracle так же, как и текст процедуры. Вызов функции PL/SQL можно осуществить в тех же местах, где и вызов стандартной функции. Например, в списке фразы SELECT : SELECT fio('Жан - жак руссо') Фамилия FROM DUAL; Результат ее выполнения имеет вид: ФАМИЛИЯ -------------------- Жан-Жак Руссо Глава 18. Хранимые процедуры 323 Здесь использовалась специальная однострочная таблица DUAL , которая соз- дается Oracle для каждой схемы и обычно используется для вывода значения каких-либо выражений. 18.4. Триггеры Триггер — это сочетание хранимой в базе данных процедуры и события, ко- торое заставляет ее выполняться. Такими событиями могут быть: ввод новой строки таблицы, изменение значений одного или нескольких ее столбцов и (или) удаление строки таблицы. При любом из этих событий автоматически запускаются один или несколько заранее созданных триггеров, которые про- изводят проверку запрограммированных в них условий, и если они не выпол- няются, отменяют ввод, изменение или удаление, посылая об этом заранее подготовленное сообщение пользователю. Триггеры похожи на процедуры и функции тем, что также являются имено- ванными блоками PL/SQL и имеют раздел объявлений, выполняемый раздел и раздел обработки исключительных ситуаций. Подобно процедурам и функ- циям, триггеры хранятся как автономные объекты в базе данных. Триггеры позволяют: реализовывать сложные ограничения целостности данных, которые не- возможно реализовать через ограничения, устанавливаемые при создании таблицы; контролировать информацию, хранимую в таблице, посредством регист- рации вносимых изменений и пользователей, производящих эти измене- ния; автоматически оповещать другие программы о том, что необходимо де- лать в случае изменения информации, содержащейся в таблице; публиковать информацию о различных событиях. Триггеры также делятся на три основных типа. Триггеры DML активизируются предложениями ввода, обновления и уда- ления информации ( INSERT , UPDATE , DELETE ) до или после выполнения предложения, на уровне строки или таблицы. Триггеры замещения (instead of) можно создавать только для представле- ний (либо объектных, либо реляционных). В отличие от триггеров DML, которые выполняются в дополнение к предложениям DML, триггеры за- мещения выполняются вместо предложений DML, вызывающих их сраба- тывание. Триггеры замещения должны быть строковыми триггерами. Часть VI. Создание приложений на SQL 324 Системные триггеры активизируется не на предложение DML, выпол- няемое над таблицей, а на системное событие, например, на запуск или останов базы данных. Системные триггеры срабатывают и на предложе- ния DDL, такие как создание таблицы. Триггеры также подразделяются на строковые (строчные или уровня строки таблицы) и табличные (операторные или уровня всей таблицы). Строковый триггер запускается один раз для каждой строки, обрабатываемой активизи- рующим предложением. Табличный триггер выполняется только один раз, независимо от того, сколько строк содержит запускающее его предложение. Выбор того или иного типа триггера зависит от его логики. Внутри строкового триггера можно обращаться к данным обрабатываемой строки. Для этого служат два идентификатора корреляции — :NEW и :OLD Идентификатор корреляции (correlation identifier) — это переменная привяз- ки PL/SQL особого рода. Двоеточие перед идентификатором указывает на то, что это переменные привязки (подобны базовым переменным, используемым во встроенном PL/SQL), а не обычные переменные PL/SQL. 18.4.1. Создание описания триггера Предложение для создания ( CREATE ) или замены ( CREATE OR REPLACE ) тригге- ра имеет вид: CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | AFTER | INSTEAD OF} { {[событие_объекта] [событие_базы_даннах] [...]ON {DATABASE | схема.SCHEMA}} | {[DELETE] [OR] [INSERT] [0R] [UPDATE [OF столбец [,...] ]] [,...]} ON {имя_таблицы | [NESTED TABLE имя_столбца OF] имя_представления} [REFERENCING {[OLD [AS] имя_для_старых] [NEW [AS] имя_для_новых]} [FOR EACH ROW] } [WHEN (условия)] 6лок_кода; Здесь: CREATE [OR REPLACE] TRIGGER имя_триггера Создается новый с именем имя_триггера или пересоздается ( OR REPLACE ) су- ществующий триггер, которому присваиваются новые определения. {BEFORE | AFTER | INSTEAD OF} Указывается, что логика триггера запускается до ( BEFORE ) или после ( AFTER ) выполнения предложения по манипуляции данными. Триггеры с фразой BEFORE запускаются до выполнения операций вставки, обновления или уда- Глава 18. Хранимые процедуры 325 ления, и это позволяет даже вообще обходить операции по манипуляции. Триггеры с предложением AFTER запускаются после завершения операции по манипуляции, и они полезны для операций, совершаемых постфактум (на- пример, при пересчете промежуточных сумм). Условие активизации INSTEAD OF (вместо) позволяет изменять представления путем изменения базовых таблиц, входящих в его состав. событие_объекта В качестве дополнения к стандартным событиям, связанным с модификацией данных, Oracle позволяет запускать триггеры в зависимости от событий объ- ектов. Операции с такими событиями могут использоваться с ключевыми словами BEFORE и AFTER .Событие объекта запускает триггер при возникнове- нии данного события, при этом используются ключевые слова: ALTER , ANALYZE , ASSOCIATE STATISTICS , AUDIT , COMMENT , DDL , DISASSOCIATE STATISTICS , DROP , GRANT , NOAUDIT , RENAME , REVOKE , TRUNCATE событие_базы_данных Помимо стандартных событий, связанных с модификацией данных, Oracle позволяет запускать триггеры на основе событий базы данных. Такие собы- тия можно использовать с ключевыми словами BEFORE и AFTER . Список дос- тупных ключевых слов для предложения событие_базы_данных : LOGON , LOGOFF , SERVERERROR , SHUTDOWN , STARTUP , SUSPEND ON {DATABASE | cxeмa.SCHEMA} Объявляется, что триггер запускается, если любой пользователь базы данных вызвал событие, запускающее триггер, при наличии предложения ON DATABASE Затем триггер запускает события, возникающие в любом месте базы данных. При указании предложения ON cxeмa.SCHEMA триггер запускается, если поль- зователь, вызвавший запускающее событие, входил в систему под схемой с именем схема . Затем триггер запускают события, возникающие в любом месте текущей схемы. {[DELETE] [OR] [INSERT] [0R] [UPDATE [OF столбец [,...] ]] [,...]} Определяется предложение по манипуляции данными, которое запускает триггер: DELETE , INSERT , UPDATE или любые их сочетания, объединяемые пре- дикатом OR . С помощью фразы UPDATE OF столбец [,.,.] можно указать, какие столбцы будут запускать триггер обновления. Если обновление будет касаться столбцов, не входящих в список, триггер запускаться не будет. ON {имя_таблицы | [NESTED TABLE имя_столбца OF] имя_представления} Указывается таблица с именем имя_таблицы , от которой зависит триггер или указывается, что триггер запускается только в том случае, когда операция по манипулированию данными применяется к столбцу (столбцам) представления Часть VI. Создание приложений на SQL 326 с именем имя_представления . Предложение ON NESTED TABLE совместимо только триггерами INSTEAD OF REFERENCING {[OLD [AS] имя_для_старых] [NEW [AS] имя_для_новых]} Используется для изменения заданных по умолчанию префиксов NEW и OLD , которые указываются перед именами столбцов, новые или старые значения которых должны использоваться в тексте тела строчного триггера. При вы- полнении предложения INSERT во вводимой строке существуют только новые значения. При выполнении предложения DELETE — только старые значения, а при выполнении UPDATE — и те и другие. REFERENCING используется тогда, когда имя столбца должно уточняться именем таблицы, которую зачем-то назвали NEW или OLD , и, следовательно, возникает неоднозначность в описа- нии столбца. [FOR EACH ROW] Этот параметр устанавливается для триггера строчного типа (который дол- жен запускаться в процессе ввода (удаления, изменения) каждой строки таб- лицы; при отсутствии FOR EACH ROW создается триггер табличного типа, за- пускаемый только один раз, независимо от того, сколько строк должно быть введено (удалено или изменено) при выполнении заданного SQL-предложения. [WHEN (условие)] Используется для отбора тех вводимых, удаляемых или изменяемых строк, которые должны вызвать запуск строчного триггера (по умолчанию строч- ный триггер запускается при вводе, удалении или изменении каждой строки соответствующей таблицы). 6лок_кода Тело (текст) триггера на языке PL/SQL. |