Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
17.4.4. Операторы EXIT, EXIT-WHEN и NULL EXIT используется для завершения цикла, когда дальнейшая обработка неже- лательна или невозможна. Внутри цикла можно помещать один или большее количество операторов EXIT . Имеются две формы EXIT : EXIT и EXIT-WHEN Часть VI. Создание приложений на SQL 294 По оператору EXIT цикл завершается немедленно и управление переходит к следующему за END LOOP оператору. По оператору EXIT-WHEN цикл завершится только в том случае, когда стано- вится истинным условие в предложении WHEN Оператор EXIT-WHEN позволяет завершать цикл преждевременно. Например, следующий цикл обычно выполняется десять раз, но как только не находится значение s1 , цикл завершается независимо от того, сколько раз цикл выпол- нился. FOR j IN 1..10 LOOP FETCH s1 INTO ShRec; EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки END LOOP; NULL — пустой оператор; он передает управление к следующему за ним опе- ратору. Однако к нему может передаваться управление и его наличие часто улучшает читаемость программы. Он также полезен для создания фиктивных подпрограмм для резервирования областей определения функций и процедур при отладке программ. 17.5. SQL- предложения в PL/SQL Из всех SQL-предложений в программах PL/SQL можно применять лишь предложения DML (см. разд. 4.4.1) и управления транзакциями. Предложе- ния DDL использовать нельзя. Предложение EXPLAIN PLAN (объяснить план) — хотя оно и относится к категории DML — применять также не раз- решается. Чтобы пояснить смысл этих ограничений, рассмотрим принципы создания программ PL/SQL. В любом языке программирования привязка переменных может быть либо ранней, либо поздней. Привязка (binding) переменной — это процесс указа- ния области памяти, соответствующей идентификатору программы. В PL/SQL в процесс привязки входит также проверка базы данных на нали- чие полномочий, позволяющих обращаться к объектам схем. В том языке, где используется ранняя привязка (early binding), этот процесс осуществляет- ся на этапе компиляции программы, а в языке, где применяется поздняя при- вязка (late binding), она откладывается до этапа выполнения программы. Ран- няя привязка означает, что компиляция программы будет занимать большее время (так как при этом нужно привязывать переменные), однако выполняться такая программа будет быстрее, потому что привязка уже завершена. Поздняя Глава 17. Процедурные расширения SQL 295 привязка сокращает время компиляции, но увеличивает время выполнения программы. При разработке PL/SQL было принято решение об использовании ранней привязки, чтобы к моменту выполнения блока объекты базы данных были уже проверены, и блок выполнялся максимально быстро. Это вполне оправ- данно, поскольку блоки PL/SQL можно хранить в базе данных как процеду- ры, функции, пакеты (модули) и триггеры. Такие объекты хранятся в ском- пилированном виде, т. е. при необходимости их можно загрузить из базы данных и выполнить (см. главу 18). Именно поэтому нельзя использовать операторы DDL. Оператор DDL модифицирует объект базы данных, следова- тельно, полномочия на объект должны быть подтверждены вновь. Процесс подтверждения полномочий требует привязки идентификаторов, а это уже было сделано во время компиляции. Тем не менее, существует способ, обеспечивающий выполнение в PL/SQL всех допустимых предложений SQL, включая DDL. Это динамический SQL (см. разд. 17.8). Он позволяет создавать оператор SQL динамически, во время выполнения программы, а затем проводить его синтаксический ана- лиз и выполнение. Такой оператор до момента выполнения программы фактически еще не создан, поэтому от компилятора PL/SQL не требуется привязывать идентификаторы этого оператора, что дает возможность ском- пилировать блок. 17.5.1. SELECT...INTO В тех случаях, когда программе необходимо иметь значения столбцов из од- ной строки таблицы, можно воспользоваться предложением SELECT...INTO , формат которого имеет вид: SELECT [{ALL | DISTINCT}] отбираемый_элемент [AS псевдоним] [,...] INTO имя_переменной [,...] | имя_записи FROМ [ONLY | OUTER] { имя_таблицы [[AS] псевдоним] | имя_представления [[AS] псевдоним] }[,...] [ [тип_соединения] JOIN условие_соединения ] [WHERE условие_поиска [ {AND | OR | NOT} условие_поиска [...]] [GROUP BY группировка_по_выражению { группировка_по_столбцам | ROLLUP группировка_по_столбцам | CUBE группировка_по_столбцам | GROUPING SETS ( список_наборов_группировок ) | ( ) | набор_группировок, список__наборов_ группировок } [HAVING условие_поиска] ]; Часть VI. Создание приложений на SQL 296 Практически это обычный SELECT (см. разд. 5.1), выполняющий присвоение выбираемых значений столбцов переменным, перечисленным во фразе INTO Однако такое присвоение происходит только в том случае, если фразы WHERE или GROUP BY обеспечивают возвращение по запросу лишь одной строки и переменные заранее описаны в декларативной части блока PL/SQL. 17.5.2. INSERT, UPDATE и DELETE Эти предложения отличаются от аналогичных предложений интерактивного SQL, в основном, лишь тем, что в их скалярных_выражениях могут использо- ваться переменные PL/SQL. Кроме того, в WHERE -фразах предложений UPDATE и DELETE может быть указа- на конструкция CURRENT OF имя_курсора (см. разд. 17.7.2). Также необходи- мо обеспечить, чтобы эти предложения производили изменения только по одной строке. 17.6. Обработка ошибок Нельзя создать приложение, которое будет безошибочно работать в любых ситуациях: возможны аппаратные сбои, невыявленные ошибки приложения и ошибки из-за некорректных действий пользователей приложения (клиентов). Если при этом программная ошибка произошла в блоке PL/SQL, вложенном в другой блок, а тот, в свою очередь, вложен в третий блок и т. д., то она мо- жет дойти до клиентского приложения. Чтобы устранить возможную отмену большого объема ранее выполненных операций и трафик из-за возвращае- мых клиенту ошибок, чтобы посылать клиенту точные сообщения о причине ошибки и способе ее устранения (если она все же дошла до клиента), разра- ботчики приложения должны предусматривать возможные программные ошибки и создавать процедуры, адекватно реагирующие на них. В PL/SQL предусмотрены механизмы перехвата и обработки ошибок, возни- кающих при выполнении программы. Эти механизмы называются исключи- тельными ситуациями. Когда программа обнаруживает заданное условие ошибки, то вызывается соответствующая исключительная ситуация. Обра- ботка исключительных ситуаций в программе производится в разделе EXCEPTION (см. разд. 17.2). При обнаружении исключительной ситуации, обработка основного тела про- граммы останавливается, и управление передается соответствующему обра- ботчику исключительной ситуации, который определяет дальнейшие действия. Глава 17. Процедурные расширения SQL 297 В PL/SQL используются следующие типы исключительных ситуаций: встроенные исключительные ситуации; исключительные ситуации, определяемые пользователем; обработчик OTHERS Примеры использования механизмов перехвата и обработки ошибок приве- дены в разд. 18.4. 17.6.1. Встроенные исключительные ситуации Oracle включает двадцать две встроенные исключительные ситуации, соот- ветствующих типовым ошибкам, приведенным в табл. 17.1. Таблица 17.1. Встроенные исключительные ситуации Исключительная ситуация Ошибка Oracle Описание CURSOR_ALREADY_OPEN ORA-06511 Попытка открытия уже открытого курсора DUP_VAL_ON_INDEX ORA-00001 Попытка вставить дубликат зна- чения для уникального индекса INVALID_CURSOR ORA-01001 Попытка выполнения запрещенной операции с курсором (например, закрытие неоткрытого курсора) INVALID_NUMBER ORA-01722 Отказ преобразования строки символов в число LOGIN_DENIED ORA-01017 Неправильное имя пользовате- ля/пароль NO_DATA_FOUND ORA-01403 Предложение SELECT...INTO возвращает ноль строк NOT_LOGGED_ON ORA-01012 Нет подключения к Oracle PROGRAM_ERROR ORA-06501 Внутренняя ошибка PL/SQL STORAGE_ERROR ORA-06500 Пакет PL/SQL вышел из пределов памяти или если память разрушена TIMEOUT_ON_RESOURCE ORA-00051 Истекло время ожидания ресурса Oracle Часть VI. Создание приложений на SQL 298 Таблица 17.1 ( окончание) Исключительная ситуация Ошибка Oracle Описание TOO_MANY_ROWS ORA-01422 Предложение SELECT...INTO возвращает более одной строки TRANSACTION_BACKED_OUT ORA-00061 Удаленный сервер отменил тран- закцию ZERO_DIVIDE ORA-01476 Попытка деления на ноль PROGRAM_ERROR ORA-06501 Внутренняя ошибка PL/SQL VALUE_ERROR ORA-06502 Ошибка усечения, арифметиче- ская ошибка или ошибка преобра- зования ROWTYPE_MISMATCH ORA-06504 Базовая курсорная переменная и курсорная переменная PL/SQL имеют несовместимые типы строк ACCESSJNTO_NUll ORA-06530 Попытка присвоить значение ат- рибуту NULL - объекта COLLECTION IS NULL ORA-06531 Попытка применить к таблице или изменяемому массиву PL/SQL, содержащему NULL , метод, отлич- ный от EXISTS SUBSCRIPT_OUTSIDE_LIMIT ORA-0653 Ссылка на индекс вложенной таб- лицы или изменяемого массива, лежащий вне объявленного диа- пазона (например, – 1) SUBSCRIPT_BEYOND_COUNT ORA-06533 Ссылка на индекс таблицы или изменяемого массива, больший, чем число элементов данной сборной конструкции CASE_NOT_FOUND ORA-06592 Не найдено соответствующее предложение WHEN в операторе CASE SELF IS NULL ORA-30625 Попытка вызвать метод экземпля- ра NULL - объекта Глава 17. Процедурные расширения SQL 299 Если в раздел EXCEPTION программы (блока) включена фраза WHEN имя_исключения THEN текст_обработчика_исключения; с именем какого-либо встроенного исключения и возникла соответствующая ошибка, то вместо прекращения исполнения программы и выдачи типового сообщения об ошибке, будет исполняться созданный пользователем текст обработчика исключения. Такой обработчик может, например, выяснить си- туацию, при которой произошло деление на ноль, и выдать правдоподобный результат операции деления или прервать исполнение программы и дать со- общение об изменении каких-либо данных. В последнем случае это может быть не типовое сообщение "Вы пытаетесь делить на ноль", а любое подго- товленное пользователем сообщение. Для выдачи сообщения об ошибке, обеспечения возврата в среду, из которой вызывалась текущая программа (блок) и отмены всех действий, выполнен- ных в текущей транзакции, целесообразно использовать процедуру RAISE_APPLICATION_ERROR (номер_ошибки, сообщение); где номер_ошибки — отрицательное целое число в диапазоне от 20 000 до 20 999 и сообщение — символьная строка длиной до 2048 символов. 17.6.2. Исключительные ситуации, определяемые пользователем Кроме встроенных могут быть использованы собственные исключительные ситуации, имена которых необходимо описать в разделе DECLARE блока PL/SQL (например, err_nachalo EXCEPTION ). В разделе EXCEPTION блока должен быть описан соответствующий обработчик исключительной ситуа- ции, например: WHEN err_nachalo THEN RAISE_APPLICATION_ERROR(-20013, 'Дата начала должна быть больше '||to_char(nach)); Определяемые пользователем ошибки обычно проверяются в основной про- грамме с помощью операторов условия ( IF-THEN ). Для передачи управления обработчику пользовательской исключительной ситуации в случае обнару- жения ошибки используется предложение: RAISE имя_пользовательского_исключения Например: IF :new.nachalo <> kon + 1 THEN RAISE err_nachalo; END IF; Часть VI. Создание приложений на SQL 300 17.6.3. Обработчик OTHERS Если исключительная ситуация явно не обрабатывается в блоке и для ее пе- рехвата не используется обработчик OTHERS , то PL/SQL отменяет выполняе- мые блоком транзакции и возвращает необработанную исключительную си- туацию обратно в вызывающую среду. Обработчик особых ситуаций OTHERS описывается последним в блоке для перехвата всех исключительных ситуаций. Он может иметь вид: WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20099,'Какая-то другая ошибка'); 17.7. Курсоры 17.7.1. Связь объектов PL/SQL с таблицами базы данных Чтобы программа PL/SQL могла работать с информацией, содержащейся в базах данных, необходимо организовать обмен между значениями столбцов таблиц баз данных и переменными PL/SQL. Известно, что для выбора информации из таблиц используется SQL- предложение SELECT . При его выполнении Oracle создает специальную рабо- чую область, содержащую: информацию о самом SELECT , данные, которые требуются для его выполнения (например, результаты подзапросов), окончательный результат выполнения SELECT PL/SQL имеет несколько механизмов доступа к этой рабочей области. Одним из них является курсор, с помощью которого можно присвоить имя этой ра- бочей области и манипулировать содержащейся в ней информацией, после- довательно выбирая строки результата и пересылая значения столбцов теку- щей строки в переменные PL/SQL. Существуют и другие механизмы, не требующие создания явного курсора. 17.7.2. Явный курсор Курсор — это средство языка SQL, позволяющее с помощью команд OPEN , FETCH и CLOSE получить построчный доступ к результату запроса к базе данных. Глава 17. Процедурные расширения SQL 301 Будем также называть курсором и сам набор строк, полученный в результате выполнения запроса. Для использования курсора его надо сначала объявить, т. е. дать ему имя и указать (с помощью предложения SELECT ), какие столбцы и строки базовых таблиц должны быть помещены в набор строк, названный этим именем. Команда OPEN инициализирует получение указанного набора и установку перед первой его строкой указателя текущей строки. Команда FETCH служит для: выборки из текущей строки курсора значений указанных столбцов с пере- сылкой их в переменные PL/SQL; установки указателя текущей строки на следующую запись. Выполнением FETCH в цикле можно последовательно выбрать информацию из всех строк курсора. Наконец, команда CLOSE позволяет закрыть (удалить из памяти) набор строк (при этом описание курсора сохраняется и его можно снова открыть коман- дой OPEN ). Существует модификация ("Курсор в цикле FOR "), позволяющая организовать последовательный выбор строк объявленного курсора без явного использо- вания команд OPEN , FETCH и CLOSE (см. разд. 17.7.2). Объявление курсора Перед работой с курсором его следует объявить в разделе DECLARE или дру- гом допустимом разделе, используя синтаксис: CURSOR имя_курсора [ (параметр, параметр, ... ] ) ] IS SELECT ... где имя_курсора — имя курсора, формируемое по правилам, описанным, на- пример, в разд. 4.4.2. SELECT ... — предложение SELECT , определяющее строки курсора. параметр имеет следующий синтаксис: имя_переменной [IN] имя_типа [ { := | DEFAULT } значение ] а имя_типа — любой тип данных PL/SQL без указания ограничений (напри- мер, длины символьных значений). Формальные параметры курсора используются только для передачи значений в WHERE -фразу предложения SELECT с целью отбора нужных строк запроса. Передача таких значений производится во время открытия курсора командой OPEN . Если значения формальных параметров отсутствуют в команде OPEN и не заданы по умолчанию ( :=значение или DEFAULT значение ), то выдается Часть VI. Создание приложений на SQL 302 ошибка. При наличии параметров в описании курсора и команде OPEN , ис- пользуются параметры из команды OPEN Пример курсора приведен в листинге 17.1. Листинг 17.1. Пример явного курсора SET SERVEROUTPUT ON; -- разрешение вывода на экран информации, заданной в -- DBMS_OUTPUT DECLARE trap VARCHAR2(7); -- входной параметр курсора (Имя трапезы) mesto INTEGER; -- входной параметр курсора (Место за столом) date_m DATE; -- входной параметр курсора (Дата меню) -- Описание курсора с входными параметрами CURSOR menu_mesto (trap VARCHAR2 := 'Завтрак', mesto INTEGER := 20, date_m DATE := '15.05.1989') IS -- Запрос, определяющий строки курсра SELECT трапеза,вид,блюдо,дата FROM меню,блюда,виды_блюд,трапезы,выбор WHERE меню.код_блюда = блюда.код_блюда AND блюда.код_вида = виды_блюд.код_вида AND меню.код_трапезы = трапезы.код_трапезы AND меню.строка = выбор.строка AND трапезы.трапеза = trap AND выбор.место = mesto AND меню.дата = date_m; -- Описание типа записи TYPE rec_menu_type IS RECORD (trapeza трапезы.трапеза%TYPE, vid виды_блюд.вид%TYPE, bludo блюда.блюдо%TYPE, date_menu меню.дата%TYPE ); -- Описание записи, использующейся при выборе строк rec_menu rec_menu_type; BEGIN OPEN menu_mesto; -- Открытие курсора LOOP --Начало цикла по выбору строк курсора FETCH menu_mesto INTO rec_menu; -- Выбор строк курсора в запись EXIT WHEN menu_mesto%NOTFOUND; -- Выход при отсутствии -- возвращаемой строки Глава 17. Процедурные расширения SQL 303 -- Вывод на экран полей записи DBMS_OUTPUT.PUT_LINE(rec_menu.trapeza||' '||rpad(rec_menu.vid,10)|| rpad(rec_menu.bludo,20)||rec_menu.date_menu); END LOOP; -- Конец цикла CLOSE menu_mesto; -- Закрытие курсора END; / Открытие курсора ( OPEN) Команда OPEN имеет следующий синтаксис: OPEN имя_курсора [ (значение [,значение] ...) ]; где список значений (значение [,значение] ...) используется для переда- чи параметров курсора и должен по числу и типу данных совпадать с описа- нием этих параметров. Команда выполняет объявленное в курсоре из листинга 17.1 предложение SELECT... , используя (если есть параметры) передаваемые из OPEN значения или значения, указанные при объявлении курсора, создавая набор строк и устанавливая указатель текущей строки перед первой из них. Так, по команде OPEN menu_mesto; будет создан набор, приведенный в листинге 17.2. Листинг 17.2 Завтрак Закуска Салат витаминный 15.05.89 Завтрак Горячее Пудинг рисовый 15.05.89 Завтрак Напиток Молочный напиток 15.05.89 где использовались значения параметров, заданные при описании. По команде OPEN menu_mesto ('Обед', 20, '15.05.1989'); будет создан другой набор — листинг 17.3. Листинг 17.3 Обед Закуска Салат летний 15.05.89 Обед Суп Суп харчо 15.05.89 Обед Горячее Сырники 15.05.89 Обед Десерт Яблоки печеные 15.05.89 Часть VI. Создание приложений на SQL 304 И, наконец, по команде OPEN menu_mesto ('Ужин', 17, '15.05.1989'); выдается набор из листинга 17.4. Листинг 17.4 Ужин Закуска Мясо с гарниром 15.05.89 Ужин Горячее Драчена 15.05.89 Ужин Напиток Молочный напиток 15.05.89 Выборка строк из курсора ( FETCH) Команда FETCH , используемая для продвижения на один шаг указателя теку- щей строки курсора и пересылки ее значений в переменные или запись, име- ет следующий синтаксис: FETCH имя_курсора INTO { имя_переменной1[,имя_переменной2]...} | имя_записи ; Для каждого значения столбца, возвращенного запросом, в списке INTO должна иметься переменная или поле записи соответствующего типа данных. Такие переменные или записи должны быть заранее описаны в декларатив- ной части блока PL/SQL. Закрытие курсора ( CLOSE) Команда CLOSE используется для освобождения всех ресурсов, которые поддерживались открытым курсором (при этом описание курсора сохраня- ется и его можно снова открыть командой OPEN ). Синтаксис команды CLOSE имеет вид: CLOSE имя_курсора; Использование курсора в цикле FOR В большинстве ситуаций, которые требуют явного курсора, текст программы может быть упрощен при использовании "курсора в цикле FOR ", заменяюще- го команды OPEN , FETCH и CLOSE . Курсор в цикле FOR : неявно объявляет индекс цикла записью, поля которой соответствуют столбцам (псевдонимам) предложения SELECT... из описания курсора; передает параметры курсора (если они есть) и открывает курсор; Глава 17. Процедурные расширения SQL 305 выбирает в цикле строки из полученного набора в индекс цикла (поля записи); закрывает курсор после обработки всех строк набора или по досрочному выходу из него с помощью команд EXIT или GOTO Синтаксис курсора в цикле FOR имеет вид: FOR имя_индекса_цикла IN имя_курсора [ (значение [,значение]...) ] LOOP тело_цикла END LOOP; где имя_индекса_цикла — индекс цикла, в котором при первом прохождении цикла хранится первая строка набора, при втором прохождении цикла вторая строка и т. д. (значение [,значение]...) — список значений, используемый для передачи параметров курсора (он заменяет в данном случае список из команды OPEN ). тело_цикла содержит нужные строки повторяющейся части программы, в ко- торых используются переменные с именами имя_индекса_цикла.имя_столбца , а имя_столбца — имя столбца из перечня столбцов предложения SELECT в описании курсора. Вот как сокращается текст предыдущего анонимного блока при использова- нии цикла FOR : SET SERVEROUTPUT ON; DECLARE trap VARCHAR2(7); -- входной параметр курсора (Имя трапезы) mesto INTEGER; -- входной параметр курсора (Место за столом) date_m DATE; -- входной параметр курсора (Дата меню) -- Описание курсора с входными параметрами CURSOR menu_mesto (trap VARCHAR2 := 'Завтрак', mesto INTEGER := 20, date_m DATE := '15.05.1989') IS -- Запрос, определяющий строки курсра SELECT трапеза,вид,блюдо,дата FROM меню,блюда,виды_блюд,трапезы,выбор WHERE меню.код_блюда = блюда.код_блюда AND блюда.код_вида = виды_блюд.код_вида AND меню.код_трапезы = трапезы.код_трапезы AND меню.строка = выбор.строка AND трапезы.трапеза = trap AND выбор.место = mesto AND меню.дата = date_m; BEGIN FOR rec_menu IN menu_mesto LOOP Часть VI. Создание приложений на SQL 306 -- Вывод на экран полей записи DBMS_OUTPUT.PUT_LINE(rec_menu.трапеза||' '||rpad(rec_menu.вид,10)|| rpad(rec_menu.блюдо,20)||rec_menu.дата); END LOOP; END; / В итоге будет получен результат, приведенный в листинге 17.2, а при исполь- зовании параметров во фразе FOR : FOR rec_menu IN menu_mesto ('Ужин', 17, '15.05.1989') LOOP, получим листинг 17.4. Атрибуты явного курсора Для анализа состояния курсора используются специальные переменные, имена которых составляются из имени курсора и суффиксов %FOUND , %NOTFOUND , %ROWCOUNT и %ISOPEN , называемых атрибутами курсора. Если курсор назван имя_курсора , то эти переменные имеют имена: имя_курсора%NOTFOUND , имя_курсора%FOUND , имя_курсора%ROWCOUNT и имя_курсора%ISOPEN Значения таких переменных анализируются при выполнении программы с помощью различных операторов управления ( IF-THEN , EXIT WHEN и т. п.). Эти операторы изменяют (при необходимости) ход выполнения программы. Следует отметить, что ссылка на специальные переменные до открытия кур- сора приводит к появлению сообщения INVALID_CURSOR Переменная с атрибутом %ISOPEN позволяет определить, открыт ли курсор. Если он открыт, то эта переменная возвращает TRUE , иначе — FALSE . Например: IF NOT menu_mesto %ISOPEN THEN -- курсор не открыт ? OPEN menu_mesto; -- открыть курсор ! IF END; FETCH ... Переменные с %NOTFOUND и %FOUND атрибутами показывают состояние теку- щей позиции курсора (перед первой выборкой строки курсора обе перемен- ных имеют значение NULL ). Переменная с %NOTFOUND принимает значение FALSE тогда, когда выборка возвратила строку (при этом переменная с %FOUND принимает значение TRUE ). Если же в результате выборки строка не возвра- щается, то переменные с %NOTFOUND и %FOUND принимают значения TRUE и FALSE соответственно. Пример использования %NOTFOUND был рассмотрен в листинге 17.1. Переменная с атрибутом %ROWCOUNT содержит количество строк, выбранных из курсора на текущий момент (при открытии курсора эта переменная содер- Глава 17. Процедурные расширения SQL 307 жит ноль). В следующем примере переменная menu_mesto%ROWCOUNT ограни- чивает выборку из курсора menu_mesto двумя строками: LOOP FETCH menu_mesto INTO rec_menu; IF menu_mesto%ROWCOUNT <=2 THEN DBMS_OUTPUT.PUT_LINE(rec_menu.trapeza||’ '||rpad(rec_menu.vid,10)|| rpad(rec_menu.bludo,20)||rec_menu.date_menu); ELSE EXIT; END IF; END LOOP; Изменение или удаление текущей строки курсора Существуют два предложения, позволяющие изменить или удалить ту строку таблицы базы данных, на которую позиционирована текущая строка курсора: UPDATE [имя_схемы.]имя_таблицы | имя представления} [псевдоним] SET { (имя_столбца [,имя_столбца] ...) = (подзапрос) | имя_столбца = { expr | (subquery) } } [, { (имя_столбца [,имя_столбца] ...) = (подзапрос) | имя_столбца = { expr | (подзапрос) } } ] ... WHERE CURRENT OF имя_курсора; DELETE [FROM] [имя_схемы.]имя_таблицы | имя представления} [псевдоним] WHERE CURRENT OF имя_курсора; Для этого необходимо, чтобы при объявлении курсора предложение SELECT... содержало фразу: FOR UPDATE OF [[имя_схемы.]имя_таблицы | имя представления] имя_столбца [, [[имя_схемы.]имя_таблицы | имя представления] имя_столбца ] ... ; в которой следует привести список обновляемых столбцов. 17.7.3. Неявный курсор (SQL - курсор) Для всех команд языка SQL, не связанных с объявлением курсора (явного курсора), PL/SQL открывает курсор (неявный курсор), на который можно ссылаться по курсорному имени SQL% . При работе с таким курсором нельзя использовать команды OPEN , FETCH и CLOSE , но можно использовать атрибуты курсора, чтобы получить информацию о его текущем состоянии. Часть VI. Создание приложений на SQL 308 Атрибуты неявного курсора (SQL - курсора) Для анализа результата выполнения предложений SELECT...INTO , INSERT , UPDATE и DELETE используются три переменные: SQL%NOTFOUND , SQL%FOUND и SQL%ROWCOUNT (Oracle закрывает SQL-курсор сразу после выполнения SQL- предложения, что делает бессмысленным использование переменной SQL%ISOPEN , так как ее значение всегда равно FALSE ). Перед выполнением предложений SELECT...INTO , INSERT , UPDATE и DELETE переменные SQL%NOTFOUND и SQL%FOUND имеют значение NULL . Переменная SQL%NOTFOUND принимает значение TRUE , если INSERT , UPDATE и DELETE не произвели изменений таблиц базы данных или SELECT...INTO не возвратил строк (при этом переменная SQL%FOUND принимает значение FALSE ). В про- тивном случае переменная SQL%NOTFOUND принимает значение FALSE , а пере- менная SQL%FOUND — TRUE 17.8. Динамический SQL в PL/SQL PL/SQL использует раннее связывание для выполнения предложений SQL. Следствием этого является то, что только предложения DML могут непо- средственно включаться в блоки PL/SQL. Однако можно решить эту пробле- му с помощью динамического SQL. Динамический SQL разбирается и ис- полняется во время выполнения, а не синтаксического разбора блока PL/SQL. Существуют два способа выполнения динамического SQL в PL/SQL: исполь- зование встроенного динамического SQL или встроенного модуля (пакета) DBMS_SQL. Встроенный динамический SQL, появившийся в Oracle 8, является составной частью самого языка. Вследствие этого он значительно проще в применении и быстрее, чем пакет DBMS_SQL. Базовым оператором, используемым в предложениях DML, DDL и блоках PL/SQL, является предложение EXECUTE IMMEDIATE (выполнить немедленно). В нем производится подготовка и немедленное выполнение заданного в тек- стовой форме оператора SQL, который не должен содержать формальных параметров и комментариев. Синтаксис EXECUTE IMMEDIATE выглядит следующим образом: EXECUTE IMMEDIATE строковое_выражение [INTO { имя_переменной [,имя_переменной]... | имя_записи }] [USING [IN | OUT | IN OUT] имя_параметра [, [IN | OUT | IN OUT] имя_параметра]...]; Глава 17. Процедурные расширения SQL 309 где строковое_выражение — SQL-предложение или PL/SQL-блок. имя_переменной — переменная, в которой сохраняется значение выбранного столбца. имя_записи — запись, которая должна иметь тип, объявленный пользовате- лем или %ROWTYPE , в ней будет содержаться выбранная строка. имя_параметра — параметр, значение которого передается в динамическое SQL-предложение или PL/SQL-блок. Каждый параметр необходимо включить в оператор USING . Тип параметра по умолчанию IN , если он не указан явно. Во время выполнения программы ка- ждый параметр оператора USING замещает соответствующий шаблон SQL- предложения или PL/SQL-блока. Поэтому каждому шаблону должен соответ- ствовать параметр оператора USING . В операторе USING возможно использо- вание числовых, символьных и строковых значений. Значения типа Boolean ( TRUE , FALSE , NULL ) не допускаются. Приведем примеры использования динамического SQL. Пример 17.1. Пояснение особенности синтаксиса. SET SERVEROUTPUT ON; -- для вывода на экран информации по DBMS_OUTPUT DECLARE sql_stmt VARCHAR2(100); plsql_block VARCHAR2(200); kod_trapezy NUMBER(1) := 4; trapeza VARCHAR2(7) := 'Полдник'; trap_rec трапезы%ROWTYPE; BEGIN -- Вставить строку в таблицу Трапезы, используя параметры sql_stmt := 'INSERT INTO трапезы VALUES (:1, :2)'; EXECUTE IMMEDIATE sql_stmt USING kod_trapezy, trapeza; -- Удалить строку из таблицы Трапезы, используя параметр EXECUTE IMMEDIATE 'DELETE FROM трапезы WHERE код_трапезы = :n' USING kod_trapezy; -- Напечатать указанную строку таблицы Трапезы sql_stmt := 'SELECT * FROM трапезы WHERE код_трапезы = :kt'; EXECUTE IMMEDIATE sql_stmt INTO trap_rec USING 3; DBMS_OUTPUT.PUT_LINE(trap_rec.код_трапезы||' '||trap_rec.трапеза); -- Напечатать содержимое таблицы с помощью анонимного блока PL/SQL. -- Здесь целый блок (включая точку с запятой) помещается в одну строку. plsql_block := 'BEGIN Часть VI. Создание приложений на SQL 310 FOR trap_rec IN (SELECT * FROM трапезы) LOOP DBMS_OUTPUT.PUT_LINE (trap_rec.код_трапезы||'' ''||trap_rec.трапеза); END LOOP; END;'; EXECUTE IMMEDIATE plsql_block; -- Создать таблицу temp EXECUTE IMMEDIATE 'CREATE TABLE temp (t1 number(2), t2 varchar2(2))'; -- Удалить таблицу temp EXECUTE IMMEDIATE 'DROP TABLE temp'; END; / Пример 17.2. В разд. 8.3.2 рассматривался пример создания таблицы К_меню , содержащей калорийность и стоимость всех блюд, которые можно пригото- вить из имеющихся продуктов. Эта таблица будет использоваться шеф- поваром для составления меню на следующий день. Применим для создания таблицы К_меню и заполнения ее данными анонимный блок: BEGIN -- Создание временной таблицы К_меню EXECUTE IMMEDIATE 'CREATE TABLE К_меню ' || '( Код_вида NUMBER(2),'|| ' Блюдо VARCHAR2(16),'|| ' Калор_блюда NUMBER(4),'|| ' Стоим_блюда NUMBER(4,2) )'; -- -- Загрузка актуальных данных в созданную таблицу EXECUTE IMMEDIATE 'INSERT INTO К_меню'|| ' SELECT Блюда.Код_вида, Блюдо,'|| 'ROUND(SUM(((Белки+Углев)*4.1+Жиры*9.3) * Вес/1000)) Колор_блюда, '|| 'ROUND((SUM(Стоимость/К_во*Вес/1000) + MIN(Труд/100))*10,2) Стоим_блюда '|| 'FROM Блюда, Виды_блюд, Состав, Продукты, Наличие '|| 'WHERE Блюда.Код_блюда = Состав. Код_блюда '|| 'AND Состав.Код_продукта = Продукты.Код_продукта '|| 'AND Состав.Код_продукта = Наличие.Код_продукта '|| 'AND Блюда.Код_вида = Виды_блюд.Код_вида '|| 'AND Блюда.Код_блюда NOT IN '|| Глава 17. Процедурные расширения SQL 311 ' (SELECT Код_блюда '|| ' FROM Состав '|| ' WHERE Код_продукта IN '|| ' (SELECT Код_продукта '|| ' FROM Наличие '|| ' WHERE К_во = 0)) '|| 'GROUP BY Блюда.Код_вида, Блюдо '|| 'ORDER BY Блюда.Код_вида, Колор_блюда'; END; / -- -- Анонимный блок для удаления таблицы К_меню после ее использования: BEGIN EXECUTE IMMEDIATE 'DROP TABLE К_меню'; END; / Пример 17.3. Используя сведения из представлений словаря данных, полу- чить информацию обо всех таблицах текущей схемы пользователя, в которой бы содержалось имя таблицы, число столбцов и строк каждой из них. В примере 14.2 был приведен запрос, позволяющий получить сведения о таб- лицах текущей схемы с указанием числа столбцов в каждой из них: SELECT table_name, COUNT(column_name) Столбцов FROM USER_TAB_COLUMNS WHERE table_name NOT IN (SELECT view_name FROM USER_VIEWS) GROUP BY table_name; Вроде бы достаточно вставить во фразу SELECT подзапрос, подсчитывающий число строк в таблице table_name : SELECT table_name, COUNT(column_name) Столбцов, (SELECT COUNT(*) FROM table_name) Строк -- Подсчет количества строк FROM USER_TAB_COLUMNS WHERE table_name NOT IN (SELECT view_name FROM USER_VIEWS) GROUP BY table_name; Однако в этом случае будет получено сообщение об ошибке: ORA-00942: таблица или представление пользователя не существует Действительно, на момент выполнения синтаксического разбора этого запро- са, SQL не знает таблицы table_name . Имена таблиц текущей схемы будут определяться по мере вывода строк запроса. Часть VI. Создание приложений на SQL 312 Например, запрос: SELECT table_name, COUNT(column_name) Столбцов, (SELECT COUNT(*) FROM Блюда) Строк FROM USER_TAB_COLUMNS WHERE table_name NOT IN (SELECT view_name FROM USER_VIEWS) GROUP BY table_name; где вместо (SELECT COUNT(*) FROM table_name) вставлен подзапрос с конкретной таблицей Блюда (SELECT COUNT(*) FROM Блюда) дает результат, содержащий список таблиц, числа из столбцов и количества строк таблицы Блюда для каждой из этих таблиц: TABLE_NAME СТОЛБЦОВ СТРОК -------------- ---------- ---------- БЛЮДА 6 38 ВИДЫ_БЛЮД 2 38 ВЫБОР 2 38 МЕНЮ 4 38 ПОСТАВКИ 5 38 ПОСТАВЩИКИ 6 38 ПРОДУКТЫ 11 38 РЕЦЕПТЫ 4 38 СОСТАВ 3 38 ТРАПЕЗЫ 2 38 Поэтому для решения поставленной задачи, придется создать PL/SQL-блок, в котором при помощи курсора будут последовательно выводиться строки запроса SELECT table_name, COUNT(column_name) Столбцов FROM USER_TAB_COLUMNS WHERE table_name NOT IN (SELECT view_name FROM USER_VIEWS) GROUP BY table_name; и каждая из них будет обрабатываться динамическим SQL для определения числа строк той таблицы, сведения о которой выводятся в текущей строке курсора. SET SERVEROUTPUT ON DECLARE /* курсор для выбора информации о таблицах схемы */ CURSOR rec_tab IS SELECT table_name, COUNT(column_name) Столбцов FROM USER_TAB_COLUMNS Глава 17. Процедурные расширения SQL 313 WHERE table_name NOT IN (SELECT view_name FROM USER_VIEWS) GROUP BY table_name; STROK NUMBER(6); /* Переменная для подсчета количества строк. */ BEGIN -- Вывод на экран "Шапки" таблицы. Здесь функция RPAD дополняет -- выводимое значение пробелами до заданной длины. DBMS_OUTPUT.PUT_LINE (RPAD('Имя таблицы',25)||RPAD('Столбцов',10)||RPAD('Строк',10)); DBMS_OUTPUT.PUT_LINE('------------------------------------------'); FOR ind_tab IN rec_tab LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||ind_tab.table_name INTO STROK; -- Вывод на экран полей записи DBMS_OUTPUT.PUT_LINE (rpad(ind_tab.table_name,30)||' '||rpad(ind_tab.Столбцов,6)||STROK); END LOOP; DBMS_OUTPUT.PUT_LINE('------------------------------------------'); END; / При запуске этого блока в схеме COOK , будет получен следующий результат: Имя таблицы Столбцов Строк ------------------------------------------ БЛЮДА 6 38 ВИДЫ_БЛЮД 2 5 ВЫБОР 2 320 МЕНЮ 4 21 ПОСТАВКИ 5 25 ПОСТАВЩИКИ 6 8 ПРОДУКТЫ 11 20 РЕЦЕПТЫ 4 34 СОСТАВ 3 148 ТРАПЕЗЫ 2 3 ------------------------------------------ PL/SQL procedure successfully completed Запуск блока в схеме UCHEB даст такой результат: Имя таблицы Столбцов Строк ------------------------------------------ Н_ВЕДОМОСТИ 16 48944 Н_ВЕДОМОСТИ_НОМЕРА 11 0 Н_ВИДЫ_ОБУЧЕНИЯ 8 6 Часть VI. Создание приложений на SQL 314 Н_ВИДЫ_РАБОТ 9 45 Н_ГРУППЫ_ПЛАНОВ 6 5040 Н_ДИСЦИПЛИНЫ 7 1756 Н_ИЗМ_ЛЮДИ 13 4 Н_ИЗМ_ОТДЕЛЫ 14 0 Н_КВАЛИФИКАЦИИ 6 22 Н_КОМПОНЕНТЫ 7 7 Н_ЛЮДИ 15 2067 Н_НАПР_СПЕЦ 7 277 Н_НАПРАВЛЕНИЯ_СПЕЦИАЛ 12 377 Н_ОБУЧЕНИЯ 7 903 Н_ОТДЕЛЫ 15 227 Н_ОЦЕНКИ 3 12 Н_ПЛАНЫ 23 3915 Н_СВОЙСТВА_ВР 7 9 Н_СВОЙСТВА_ОТДЕЛОВ 7 14 Н_СЕССИЯ 16 12498 Н_СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК 9 203871 Н_СТРОКИ_ПЛАНОВ 11 46912 Н_ТИПЫ_ВЕДОМОСТЕЙ 6 3 Н_ТИПЫ_ПЛАНОВ 7 5 Н_ТИПЫ_СТАНДАРТОВ 6 8 Н_УЧЕБНЫЕ_ГОДА 3 13 Н_УЧЕНИКИ 19 4267 Н_ФОРМЫ_ОБУЧЕНИЯ 12 4 Н_ХАРАКТЕРИСТИКИ_ВИДОВ_РАБОТ 6 41 Н_ХАРАКТЕРИСТИКИ_ОТДЕЛОВ 8 164 Н_ЦИКЛЫ_ДИСЦИПЛИН 7 14 Н_ЭКЗ_ЛИСТЫ_НОМЕРА 13 0 Н_ЭЛЕМЕНТЫ_СТРОК 9 59079 ------------------------------------------ PL/SQL procedure successfully completed |