Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
Глава 17 Процедурные расширения SQL 17.1. Введение Так как описание всех процедурных расширений языка SQL (SQLpl в DB2, PL/SQL в Oracle, PL/pgSQL в PostgreSQL, Trunsact-SQL в SQL Server и др.) не входило в задачу данной книги, то познакомим читателя с одним из них — PL/SQL. Попытаемся дать здесь краткое его описание, достаточное, с нашей точки зрения, для создания простых приложений на этом языке. Для получе- ния дополнительных сведений о PL/SQL предлагаем обратиться к книге Скотта Урмана [10] и материалам приложения А. В приложении А помещена электронная версия книги Урмана (Oracle 9i. Программирование на языке PL/SQL. Скотт Урман. — М.: Издательство "ЛОРИ", 2004, 548 с.) и докумен- тация к Oracle Database 10g Release 2, в которой есть раздел: "PL/SQL User's Guide and Reference". 17.2. Основы PL/SQL PL/SQL (Procedural Language / Structured Query Language) — процедурный язык программирования, разработанный в корпорации Oracle. Является про- цедурным расширением языка SQL. Базируется на языке Ада. PL/SQL — это развитый язык программирования, используемый для доступа к базам данных Oracle из различных сред. PL/SQL интегрирован с сервером базы данных, поэтому программы PL/SQL обрабатываются быстро и эффек- тивно. Этот язык доступен и в некоторых клиентских инструментальных средствах Oracle. Он дает возможность использовать переменные, операто- ры, массивы, курсоры и исключения. Стандартный SQL является декларативным языком программирования. Это накладывает на язык определенные ограничения, такие как, например, Часть VI. Создание приложений на SQL 284 отсутствие прямой поддержки циклов. PL/SQL же, как полноценный про- цедурный язык программирования, позволяет разработчикам обрабатывать реляционную базу данных Oracle, используя (более привычный) императив- ный стиль программирования. Операторы SQL могут быть легко вызваны непосредственно из кода PL/SQL-процедуры, функции или триггера. PL/SQL — блочно-структурированный язык. Это означает, что основные единицы программ PL/SQL (анонимные блоки, процедуры и функции) явля- ются логическими блоками, которые могут содержать любое число вложен- ных в них блоков. Структура блока имеет следующий вид: DECLARE /* Раздел объявлений — здесь перечисляются переменные, типы, курсоры и локальные подпрограммы PL/SQL. */ BEGIN /* Выполняемый раздел — процедурные и SQL-операторы. Это основной раздел блока и единственный обязательный. */ EXCEPTION /* Раздел обработки исключительных ситуаций — здесь находятся операторы обработки ошибок. */ END; Обязательным является только выполняемый раздел ( BEGIN ). Он должен со- держать по меньшей мере один выполняемый оператор (например, NULL; ). 17.2.1. Анонимный блок PL/SQL В качестве примера структуры блока PL/SQL, рассмотрим анонимный блок (неименованную программу), в котором выполняется проверка правильности ввода (изменения) фамилии, имени или отчества и, если необходимо, их кор- ректировка. Фамилия (имя) должна состоять только из русских букв и символов тире и пробел. Первая буква фамилии (имени или отчества) должна быть за- главной, а остальные — строчными. В двойных (тройных) фамилиях и име- нах каждая часть должна начинаться с заглавной буквы (например, Смирнов- Сокольский, Жан-Жак Руссо, Гай Юлий Цезарь). DECLARE /* декларативная часть анонимного блока описание локальных переменных */ tekst VARCHAR2(20) := 'Жан - жак руссо'; /* Проверяемый текст (фамилия, имя или отчество)*/ result VARCHAR2(20); /* Результат (проверенный или исправленный входной текст или "0" при неисправимой ошибке)*/ kol INTEGER; /* Количество символов в проверяемом тексте */ Глава 17. Процедурные расширения SQL 285 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; -- выход из цикла END IF; END LOOP; ELSE result := '0'; -- установка нулевого результата при отсутствии -- в тексте символов, отличных от пробелов END IF; IF result <> '0' THEN -- если текст содержит символы, то: result := INITCAP(result); /* преобразование первых букв слов текста в заглавные */ END IF; DBMS_OUTPUT.PUT_LINE(result); /* использование встроенного пакета DBMS_OUTPUT для вывода на экран результата преобразования; если ранее не выполнялась Часть VI. Создание приложений на SQL 286 установка SET SERVEROUTPUT ON, разрешающая вывод на экран информации, заданной в DBMS_OUTPUT, то ее надо выполнить до выполнения этого блока */ END; / -- наклонная черта, указывающая на окончание текста блока PL/SQL Этот анонимный блок можно ввести с помощью текстового редактора в файл (например, с именем an_fio.sql) или вводить строчка за строчкой прямо в SQL*Plus. В последнем случае ввод в первой строке одного из зарезервиро- ванных слов DECLARE или BEGIN переводит SQL*Plus в режим построчного ввода строк анонимного блока. После ввода наклонной черты блок выполняется и на экран выдается резуль- тат его работы: Жан-Жак Руссо Процедура PL/SQL успешно завершена. SQL> _ Если текст анонимного блока был сохранен в файле an_fio.sql, то такой сце- нарий SQL можно выполнить с помощью команды @an_fio.sql или @an_fio (расширение sql, указывающее, что данный файл является файлом запроса, можно опустить): SQL> @an_fio Жан-Жак Руссо Процедура PL/SQL успешно завершена. SQL> _ Синтаксис команд PL/SQL и выражений практически не отличается от тех, которые описаны в главе 4. 17.3. Переменные, константы, записи PL/SQL В программах PL/SQL могут использоваться переменные и константы, опи- сываемые в разделе DECLARE с помощью конструкции вида: Имя_переменной [CONSTANT] тип_данных [NOT NULL] [ { := | DEFAULT } выра- жение ] Если в объявлении переменной указано CONSTANT , то она должна быть ини- циализирована, и ее начальное значение не может быть изменено. Констант- ная переменная рассматривается в блоке в качестве переменной "только для Глава 17. Процедурные расширения SQL 287 чтения". Константы часто используются для хранения тех значений, которые известны к моменту создания блока. Например: rojdenie DATE; kol_vo NUMBER(9) := 0; priznak VARCHAR2(6) NOT NULL := 'академ'; pi CONSTANT REAL := 3.14159; area REAL := pi*radius**2; valid_id BOOLEAN; valid_id VARCHAR2(5); -- недопустимое вторичное описание valid_id i, j, k NUMBER(9); -- нельзя описывать список, надо: -- i NUMBER(9); j NUMBER(9); k NUMBER(9); credit NUMBER(9,2); debit credit%TYPE; -- тип данных, аналогичный типу данных -- "credit" Записи PL/SQL — это совокупность полей, каждое из которых должно иметь уникальное имя (в пределах записи). Эти поля могут принадлежать различ- ным типам данных. Если создаваемая запись ( bludo ) соответствует описанию столбцов какой- либо базовой таблицы (например, Блюда ), то ее объявление можно осущест- вить в разделе DECLARE с помощью атрибута %ROWTYPE : bludo Блюда%ROWTYPE; В противном случае для объявления записи необходимо сначала определить ее тип. Для описания типа записи используется синтаксис: TYPE имя_типа_записи IS RECORD ( имя_столбца1 {тип_данных | имя_переменной%TYPE | имя_таблицы.имя_столбца%TYPE | имя_таблицы%ROWTYPE} [NOT NULL], имя_столбца2 {тип_данных | имя_переменной%TYPE | имя_таблицы.имя_столбца%TYPE | имя_таблицы%ROWTYPE} [NOT NULL], ...); где имя_типа_записи — спецификатор типа, используемый в последующих объявлениях записей PL/SQL, и тип_данных — тип данных. С помощью атри- бута %TYPE можно установить тип_данных , соответствующий типу данных какой- либо переменной ( имя_переменной ) или столбца ( имя_таблицы.имя_столбца ). Атрибут %ROWTYPE позволяет определить поле как запись, соответствующую описанию столбцов какой-либо базовой таблицы. Например, если необходимо создать запись rec_menu , содержащую поля трапеза , вид , блюдо и дата из таблиц меню , блюда , виды_блюд , трапезы , то для нее можно определить тип: TYPE rec_menu_type IS RECORD ( trapeza трапезы.трапеза%TYPE, Часть VI. Создание приложений на SQL 288 vid виды_блюд.вид%TYPE, bludo блюда.блюдо%TYPE, date_menu меню.дата%TYPE ); Теперь можно определить запись: rec_menu rec_menu_type; Для ее создания можно использовать следующий анонимный блок: DECLARE TYPE rec_menu_type IS RECORD (trapeza трапезы.трапеза%TYPE, vid виды_блюд.вид%TYPE, bludo блюда.блюдо%TYPE, date_menu меню.дата%TYPE ); rec_menu rec_menu_type; BEGIN NULL; -- пустой оператор (см. разд. 17.4.4) END; / При объявлении типа записи можно присвоить ее полям некоторые значения. Если же для поля вводится ограничение NOT NULL (для предотвращения на- значения пустых значений), то этому полю надо обязательно присвоить зна- чение. Например: TYPE BludoRecTyp IS RECORD (код_блюда NUMBER(2) NOT NULL := 11, блюдо VARCHAR2(16), код_вида NUMBER(1), основа VARCHAR2(6), выход Блюда.выход%TYPE, труд NUMBER(3)); или TYPE BludoRecTyp IS RECORD (Блюда%ROWTYPE); Объявление создаваемой записи (например, имя_plsql_записи ) производится в разделе DECLARE и имеет вид: имя_plsql_записи имя_типа_записи; Ссылки на отдельные поля записи осуществляются так: имя_plsql_записи.имя_поля; Для присвоения значения конкретному полю записи используется синтаксис: имя_plsql_записи.имя_поля := выражение; Примеры использования записей в программах PL/SQL приведены в разд. 17.7. Глава 17. Процедурные расширения SQL 289 17.4. Команды управления ходом выполнения программы 17.4.1. Команды условного перехода ( IF...) Существует три модификации оператора условного перехода: IF-THEN (если-то): IF условие THEN последовательность_команд; END IF; IF-THEN-ELSE (если-то-иначе): IF условие THEN 1_последовательность_команд; ELSE 2_последовательность_команд; END IF IF-THEN-ELSIF (если-то-иначе-если): IF условие1 THEN 1_последовательность_команд; ELSIF условие2 THEN 2_последовательность_команд; ELSIF условиеN THEN N_последовательность_команд; [ ELSE N+1_последовательность_команд;] END IF Во всех модификациях, если условие или условие1 истинно, то выполняется последовательность_команд или 1_последовательность_команд и управле- ние передается на первый оператор после END IF . Если же оно ложно, то: в модификации IF-THEN управление передается на первый оператор после END IF ; в модификации IF-THEN-ELSE выполняется 2_последовательность_команд и управление передается на первый оператор после END IF ; в модификации IF-THEN-ELSIF проверяется условие2 ; если оно истинно, то выполняется 2_последовательность_команд и управление переда- ется на первый оператор после END IF ; если условие1 и условие2 ложны, Часть VI. Создание приложений на SQL 290 а условие3 истинно, то выполняется 3_последовательность_команд и управление передается на первый оператор после END IF ; наконец, если условия 1, 2, ..., N ложны, то выполняется N+1 последовательность команд и управление передается на первый оператор после END IF Все это справедливо, если внутри последовательности команд нет операто- ров, осуществляющих переход за пределы этой последовательности. 17.4.2. Метки и оператор безусловного перехода ( GOTO) В любом месте программы может быть поставлена метка, имеющая синтаксис: <<имя_метки>> Оператор GOTO позволяет осуществить безусловный переход к метке, имя ко- торой должно быть уникальным внутри программы или блока PL/SQL. На- пример, управление передается вниз к помеченному оператору: BEGIN GOTO insert_row; < INSERT INTO Блюдо VALUES ... END; В следующем примере управление передается вверх к помеченной последо- вательности операторов: BEGIN < BEGIN UPDATE Блюдо SET ... END; GOTO update_row; END; Следует отметить, что использование GOTO (особенно в тех случаях, когда метка предшествует оператору GOTO ) может привести к сложным, нерас- познаваемым кодам ошибок, которые трудно обрабатывать. Поэтому реже Глава 17. Процедурные расширения SQL 291 используйте GOTO , тем более что этот оператор нельзя использовать для вы- полнения перехода: в IF -блок, LOOP -блок или в другой блок, не включающий текущий; из одного предложения IF -оператора к другому; из внешнего блока во внутренний блок; из обработчика особых ситуаций в текущий блок. 17.4.3. Операторы цикла (LOOP, WHILE...LOOP и FOR...LOOP) Циклы служат для повторяемого выполнения последовательности команд. В PL/SQL используются три модификации операторов цикла: LOOP , WHILE...LOOP и FOR...LOOP Цикл LOOP имеет следующий синтаксис: LOOP последовательность_команд; END LOOP; и приводит к бесконечному повторению последовательности_команд , если внутри нее нет команд EXIT (выход из цикла), RAISE (вызов обработчика ис- ключительных ситуаций) или GOTO (безусловный переход). Например, LOOP последовательность_команд; IF условие THEN EXIT; END IF; END LOOP; приведет к выходу из цикла после выполнения последовательности_команд , как только условие станет истинным. Цикл WHILE предназначен для повторения последовательности_команд , пока условие остается истинным: WHILE условие LOOP последовательность_команд; END LOOP; Цикл FOR является наиболее распространенной модификацией цикла и имеет следующий синтаксис: FOR индекс IN [REVERSE] нижняя_граница..верхняя_граница LOOP последовательность_команд; END LOOP; Часть VI. Создание приложений на SQL 292 Здесь индекс (счетчик циклов) изменяется от нижней до верхней границы с шагом 1, а при использовании REVERSE — от верхней до нижней границы с шагом –1. Например, FOR i IN 1..3 LOOP -- для i = 1, 2, 3 последовательность_команд; -- цикл выполняется 3 раза END LOOP; FOR i IN REVERSE 1..3 LOOP -- для i = 3, 2, 1 последовательность_команд; -- цикл выполняется 3 раза END LOOP; Если нижняя граница равна верхней, последовательность выполняется один раз. Если нижняя граница больше верхней, последовательность не выполня- ется, и управление переходит к следующему за циклом оператору. Пределы диапазона цикла могут быть литералами, переменными или выра- жениями, но должны быть целыми числами. Например, допустимы следую- щие диапазоны: j IN -5..5 k IN REVERSE first..last step IN 0..TRUNC(high/low) * 2 code IN ASCII('A')..ASCII('J') Объявлять индекс не нужно — он объявлен неявно как локальная переменная типа integer PL/SQL позволяет определять диапазон цикла динамически во время выпол- нения. Например: SELECT COUNT(код_блюда) INTO bluda_count FROM Блюда; FOR i IN 1..bluda_count LOOP END LOOP; Значение bluda_count не известно при компиляции; предложение SELECT оп- ределяет это значение во время выполнения (синтаксис SELECT…INTO рас- смотрен в разд. 17.5.1). Индекс может использоваться в выражениях внутри цикла, но он не может изменяться. Индекс определен только внутри цикла, и на него нельзя ссылаться снаружи цикла. После выполнения цикла индекс не определен. Глава 17. Процедурные расширения SQL 293 Подобно PL/SQL блокам, циклы могут быть помечены. Метка устанавлива- ется перед оператором LOOP , и ее имя может быть указано после соответст- вующего END LOOP : <<имя_метки>> LOOP последовательность_команд; END LOOP [имя_метки]; Помеченные циклы используются для улучшения чтения программы (раз- борчивости). С любой формой утверждения EXIT можно завершать не только текущий цикл, но и любой внешний цикл. Для этого маркируйте внешний цикл, который надо завершить, и используйте метку в утверждении EXIT следующим образом: < LOOP LOOP EXIT outer WHEN ... -- завершаются оба цикла END LOOP; END LOOP outer; Если требуется преждевременно выйти из вложенного цикла FOR , маркируйте цикл и используйте метку в утверждении EXIT . Например: < FOR i IN 1..5 LOOP FOR j IN 1..10 LOOP FETCH s1 INTO ShRec; EXIT outer WHEN s1%NOTFOUND; -- завершаются оба цикла END LOOP; END LOOP outer; -- управление передается сюда |