WWW.DISSERS.RU

БЕСПЛАТНАЯ ЭЛЕКТРОННАЯ БИБЛИОТЕКА

   Добро пожаловать!

Pages:     | 1 | 2 || 4 | 5 |   ...   | 7 |

«ОФИЦИАЛЬНОЕ АВТОРИЗОВАННОЕ ИЗДАНИЕ ORACLE PRESS™ ЭКСКЛЮЗИВНЫЕ ПРАВА ПРИНАДЛЕЖАТ ИЗДАТЕЛЬСТВУ OSBORNE Программирование на языке PL/SQL Разработка эффективных приложений )мощью PL/SQL OFFICIAL • Oracle ...»

-- [ Страница 3 ] --

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

Процедурные и SQL-операторы.

Возвращает строку с удаленными правыми символами, присутствующими в строке По умолчанию является одиночным пробелом. База данных будет просматривать string/, начиная с самой правой позиции. Дойдя до первого символа, не встречающегося в строке она вернет результат. RTRIM ведет себя аналогично LTRIM за исключением что удаляет символы справа, а не слева.

Процедурные и SQL-операторы.

Возвращает фонетическое представление строки string (см. ниже).

a Процедурные и SQL-операторы.

Возвращает часть строки string;

величины а и b измеряются в символах (см. ниже).

Процедурные и SQL-операторы.

Возвращает часть строки величины а и b измеряются в байтах (см. ниже).

Процедурные и SQL-операторы.

Возвращает часть строки величины а и b измеряются в полных символах Unicode (см. ниже).

Процедурные и SQL-операторы.

Возвращает часть строки string;

величины а и b измеряются в кодовых пунктах UCS2 (см. ниже).

Встроенные функции SQL _ Функция Доступна в версии Где применима SUBSTR4(string, Oracle9i Процедурные и SQL-операторы.

Возвращает часть строки string;

величины а и b измеряются в кодовых пунктах UCS4 (см. ниже).

Процедурные и SQL-операторы.

Возвращает строку sfring, где вхождение каждого символа из строки заменяется соответствующим символом из строки TRANSLATE реализует супермножество функциональности, предоставляемой REPLACE. Если fromjtr длиннее то все символы из отсутствующие в fo_sfr, удаляются из sfring, так как они не имеют соответствующих символов. Строка fo_sfr не может быть пустой. Oracle интерпретирует пустую строку как NULL, и если какой-либо аргумент TRANSLATE будет NULL, то результат также будет NULL.

TRIM([{{LEADING|TRAILING Процедурные и SQL-операторы.

| FROM] sfring) Возвращает строку sfring с удаленными символами в начале и/или в конце.

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

Процедурные и SQL-операторы.

Возвращает строку, где все символы переведены в верхний регистр. Если строка имеет тип CHAR, возвращаемое значение также будет CHAR. Если строка имеет тип VARCHAR2, возвращаемое значение будет VARCHAR2. Символы, не являющиеся буквами, остаются без изменения в возвращаемом значении.

SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2 и SUBSTR Синтаксис а [,, а Назначение Возвращает часть строки символов string, начинающуюся с символа с номером а и имеющую длину b символов. Если а = 0, это равно сильно тому, что а= 1 (начало строки). Если положительно, возвращае мые символы считаются слева направо. Если а отрицательно, символы возвращаются, начиная с конца строки, и считаются справа налево. Если b отсутствует, то по умолчанию возвращаются все символы до конца стро ки. Если b меньше возвращается NULL-значение. Если в качестве а или b указано число с плавающей точкой, его дробная часть отбрасывается.

Возвращаемый тип всегда тот же самый, что и у string.

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

168 ГЛАВА Функция для а и b SUBSTR Символы из входного набора символов базы данных SUBSTRB Байты SUBSTRC Полные символы UNICODE SUBSTR2 Кодовые пункты SUBSTR4 Кодовые пункты USC Пример, 4, 4) "First" FROM dual;

First 123d SELECT -4, 4) "Second" FROM dual;

Second 3def SELECT 5) "Third" FROM dual;

Third 23def Синтаксис Назначение Возвращает фонетическое представление строки string. Это полезно для сравнения слов, правописание которых различно, но произ ношение одинаково. Фонетическое представление слов определено в книге Дональда Е. Кнута "The Art of Computer Programming, Volume 3:

Sorting and Searching". Алгоритм построения фонетического разбора слов следующий:

• Сохраняем первую букву строки и удаляем все вхождения а, е, h, i, о, т Назначаем оставшимся буквам номера:

1.

2. с, q, s, x, z 3. d,t 4.

Встроенные SQL 5. т, п 6.

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

• Возвращаем первые 4 байта, дополненные нулями.

Пример SQL> SELECT 2 FROM students;

FIRST NAME Scott S Margaret M Joanne J Manish M Patrick P Timothy T Barbara B David D Ester E Rose R Rita R Shay SOOO SELECT FROM students WHERE = );

FIRST NAME Scott Символьные возвращающие числовые значения Эти функции принимают символьные аргументы и возвращают числовые результаты. Аргументы могут быть либо либо Хотя обычно в результате получается целое значение, возвращаемое значение является NUMBER без определения точности или масштаба. Эти функ ции описываются в следующей таблице:

Функция Доступна в версии Где применима Процедурные и SQL-операторы Возвращает десятичное представление первого байта строки из набора символов базы данных. Обратите внимание, что эта функция по-прежнему называется ASCII, хотя набор символов может и не быть 7-битовым ASCII. Функции CHR и ASCII являются обратными по отношению друг к другу. CHR возвращает символ для кода символа, a ASCII — код символа для символа.

Процедурные и SQL-операторы Возвращает позицию вхождения строки в строку величины а и b измеряются в символах (см. ниже).

Процедурные и SQL-операторы Возвращает позицию вхождения строки в строку величины а и b измеряются в байтах (см. ниже).

Процедурные и SQL-операторы Возвращает позицию вхождения строки в строку величины а и b измеряются в полных символах Unicode (см. ниже).

Процедурные и SQL-операторы Возвращает позицию вхождения строки в строку величины а и b измеряются в кодовых пунктах UCS2 (см. ниже).

Процедурные и SQL-операторы Возвращает позицию вхождения строки string? в строку величины а и b измеряются в кодовых пунктах UCS4 (см. ниже).

Процедурные и SQL-операторы Возвращает длину строки, измеренную в символах (см. ниже).

Процедурные и SQL-операторы Возвращает длину строки, измеренную в байтах (см. ниже).

Процедурные и SQL-операторы Возвращает длину строки, измеренную в полных символах Unicode (см. ниже).

Процедурные и SQL-операторы Возвращает длину строки, измеренную в кодовых пунктах UCS2 (см. ниже).

Процедурные и SQL-операторы Возвращает длину строки, измеренную в кодовых пунктах UCS4 (см. ниже).

INSTRB, INSTRC, INSTR2 и INSTR Синтаксис l, [,a] Встроенные функции SQL Назначение Возвращает позицию вхождения строки в строку Строка просматривается слева, начиная с позиции а. Если а отрицательно, то stringl просматривается справа. Возвращается позиция вхождения. По умолчанию а и b присваиваются значения что будет возвращать позицию первого вхождения строки в строку stringl.

Если не найдена по указанным а и Ъ, возвращается 0. Позиции из меряются относительно начала stringl независимо от значений а и Ъ.

Разные версии INSTR придают различный смысл а и Ь, как показано в следующей таблице.

Функция для а и b INSTR Символы входного набора символов базы данных INSTRB INSTRC Полные символы Unicode INSTR2 Кодовые пункты UCS INSTR4 Кодовые пункты Пример SELECT 1, 2) "First" FROM dual;

First INSTRC -1, 2) "Second" FROM dual;

Second SELECT INSTRC Scotfs 5) "Third" FROM dual;

Third SELECT INSTRC "s 12) "Fourth" FROM dual;

Fourth и LENGTH Синтаксис 172 Назначение Возвращает длину строки string. Значения CHAR дополня ются пробелами, поэтому если строка имеет тип CHAR, конечные пробе лы включаются в длину. Если string будет NULL, функция вернет NULL.

Различные версии LENGTH по-разному интерпретируют возвращаемое как показывает следующая таблица.

Функция Единицы измерения возвращаемого значения LENGTH Символы входного набора символов базы данных LENGTHC Полные символы Unicode LENGTH2 Кодовые пункты UCS LENGTH4 Кодовые пункты UCS Пример SELECT had a little "Length" FROM dual;

Length Функции NLS За исключением NCHR, эти функции получают символьные аргументы и возвращают символьные значения. Все функции этого вида созданы для работы с данными из различных наборов символов либо с параметрами NLS, отличными от используемых по умолчанию. Дополнительная ин формация о (в носит название Globalization) представлена в документации Oracle.

Функция Доступна в версии Где применима Процедурные и SQL-операторы Преобразует символы входной строки string в символы из указанного набора Аргумент задает набор символов входного значения. Если он не определен, используется набор символов базы данных по умолчанию. Входные данные могут быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB. Возвращаемое значение будет VARCHAR2.

Если символ входной строки не присутствует в будет применен символ замены (как определено в NCHR(x) Процедурные и SQL-операторы Возвращает символ, который имеет значение, эквивалентное х в национальном наборе символов базы данных. NCHR(x) эквивалентна CHR(x USING NCHAR_CS).

Встроенные функции SQL Функция Доступна в версии Где применима Процедурные и SQL-операторы Возвращает объявленную длину (в символах) значения NCHAR. Аргумент указывает длину значения в байтах, a charset является идентификатором набора символов, используемым для значения.

Процедурные и SQL-операторы Возвращает числовой идентификатор указанного набора символов Задание для будет возвращать идентификатор набора символов базы данных, a вернет идентификатор национального набора символов базы данных. Если charsetjiame не является допустимым именем набора символов, возвращается NULL.

и обратные функции.

Процедурные и SQL-операторы Возвращает имя набора символов по указанному идентификатору Если является недопустимым идентификатором набора символов, возвращается NULL.

и - взаимно обратные функции.

Процедурные и SQL-операторы Возвращает строку, в которой первый символ каждого слова является прописным, а остальные символы строчные. Аргумент nlsparams определяет для сеанса последовательность сортировки, отличную от используемой по умолчанию. Если этот аргумент не указан, ведет себя так же, как Аргумент nlsparams задается в виде где sort определяет последовательность лингвистической сортировки. Дополнительную информацию о параметрах NLS и о том, как они используются, можно найти в "Server SQL Reference".

Процедурные и SQL-операторы Возвращает строку, в которой все символы строчные. Символы, отличные от букв, не изменяются. Аргумент nlsparams действует так же, как и в NLSJNITCAP. Если nlsparams отсутствует, NLS_LOWER ведет себя аналогично LOWER.

Процедурные и SQL-операторы Возвращает строку, в которой все символы прописные. Символы, отличные от букв, не изменяются. Аргумент nlsparams действует так же, как и в NLSJNITCAP. Если nlsparams отсутствует, ведет себя аналогично UPPER.

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

Аргумент nlsparams ведет себя так же, как в NLSJNITCAP. Если nlsparams не указан, для сеанса используется последовательность сортировки по умолчанию. Дополнительную информацию о последовательностях сортировки можно найти в разделе "Поддержка национальных языков" в "Server SQL Reference".

USING Процедурные и SQL-операторы TRANSLATE... USING преобразует входной строковый аргумент string либо в набор символов базы данных (если указано CHAR CS), либо в национальный набор символов базы данных (если указано Аргумент string может быть CHAR, VARCHAR2, NCHAR или NVARCHAR2.

Если задано CHAR_CS, возвращаемый тип будет VARCHAR2, а в случае NCHAR_C5 возвращается тип NVARCHAR2. USING предоставляет подмножество функциональности CONVERT.

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

174 Функция Доступна в версии Где применима UNISTR(s) Процедурные и SQL-операторы Возвращает строку s, транслированную в набор символов Unicode базы данных. Аргумент s может содержать escape-символы кодовых пунктов UCS2, состоящие из обратного слэша (\), за которым следует код. Таким образом, чтобы включить обратный слэш в строку, необходимо использовать двойной обратный слэш UNISTR аналогична за исключением того только, что транслирует в Unicode и принимает escape-символы.

Числовые функции Эти функции принимают аргументы типа NUMBER и возвращают значе ния типа NUMBER. Возвращаемые значения и триго нометрических функций имеют точность 36 десятичных знаков. ACOS, ATAN и вычисляются с точностью до 30 десятичных зна ков. Числовые функции описываются в следующей таблице:

Функция Доступна в версии Где применима ABS(x) Процедурные и SQL-операторы Возвращает абсолютное значение х.

Процедурные и SQL-операторы Возвращает арккосинус х Область определения х: от -1 до 1. А область значений: от 0 до к, выраженная в радианах.

ASIN(x) Процедурные и SQL-операторы Возвращает арксинус х. Область определения х: от -1 до 1. А область значений: от -к/2 до тс/2, выраженная в радианах.

ATAN(x) Процедурные и SQL-операторы Возвращает арктангенс х. Область значений: от -л/2 до я/2, выраженная в радианах.

ATAN2(x, у) Процедурные и SQL-операторы Возвращает арктангенс х и у, Область значений: от -тс до я. Область значений зависит от знаков х и у и выражается в радианах. ATAN2(x, у) — это то же самое, что и у) Процедурные и SQL-операторы Возвращает результат выполнения побитовой операции AND над аргументами х и у, каждый из которых должен быть неотрицательным целым значением. Эта функция эквивалентна оператору && в С. Отметим, что не существует функции BITOR, но модуль предоставляет допол нительные побитовые операторы, которые работают со значениями типа RAW.

CEIL(x) Процедурные и SQL-операторы Возвращает наименьшее целое число, большее или равное х.

COS(x) Процедурные и SQL-операторы Возвращает косинус угла в х радиан.

COSH(x) Процедурные и SQL-операторы Возвращает гиперболический косинус х.

Встроенные функции SQL Функция Доступна в версии Где применима ЕХР(х) Процедурные и SQL-операторы Возвращает е в степени х (е = Процедурные и SQL-операторы Возвращает наибольшее целое число, меньшее или равное х.

LN(x) Процедурные и SQL-операторы Возвращает натуральный логарифм х. Аргумент х должен быть больше 0.

у) Процедурные и SQL-операторы Возвращает логарифм у по основанию х. Основание должно быть положительным числом, отличным от 0 и а у может быть любым положительным числом.

у) Процедурные и SQL-операторы Возвращает остаток от деления х на у. Если у равен 0, то возвращается х.

POWER(x, у) Процедурные и SQL-операторы Возвращает х в степени у. Основание х и порядок у не должны быть обязательно положительными целыми числами, но если число, то у должен быть целым числом.

Процедурные и SQL-операторы Возвращает х, округленный до у разрядов справа от десятичной точки. Значением по умолчанию для у является 0;

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

SIGN(x) Процедурные и SQL-операторы Если х < 0, возвращает Если х = 0, возвращает 0. Если х > 0, возвращает SIN(x) Процедурные и SQL-операторы Возвращает синус угла в х радиан.

SINH(x) Процедурные и SQL-операторы Возвращает гиперболический синус угла в х радиан.

SQRT(x) Процедурные и SQL-операторы Возвращает квадратный корень х. Аргумент х не может быть отрицательным.

TAN(x) Процедурные и SQL-операторы Возвращает тангенс угла в х радиан.

TANH(x) Процедурные и SQL-операторы Возвращает гиперболический тангенс х.

TRUNC(x Процедурные и SQL-операторы Возвращает х, усеченный (не округленный) до у десятичных разрядов. Значением по умолчанию для у является 0;

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

тот, max, Только операторы Описывается в следующем разделе.

176 ГЛАВА WIDTH_BUCKET Синтаксис mm, max, Назначение позволяет создавать гистограммы одина ковой длины на основе входных параметров. Диапазон делится на отрезков одинаковой длины. Возвращается отрезок, в кото рый попадает х. Если х меньше тгп, возвращается 0. Если х больше или ра вен max, возвращается Ни тгп, ни не могут быть NULL, a должен быть положительным целым числом. Если х содер жит NULL, то возвращается NULL.

Пример Следующий пример определяет 20 отрезков, каждый размером (1000/20):

SELECT 1, 1000, 20) Bucket FROM rooms;

NUMBER SEATS BUCKETS 1000 500 50 50 50 10 1000. 75 Функции даты и времени Аргументы функций даты и времени имеют тип DATE. За исключением функции которая возвращает значение типа NUMBER, все функции возвращают значения типа DATE. В этом разделе рассматриваются также арифметические операции над датами. Функции даты и времени описываются в следующей таблице.

Функция Доступна в версии Где применима x) Процедурные и SQL-операторы Возвращает дату плюс х месяцев. Аргумент х может быть любым целым числом. Если в получаемом месяце число дней меньше, чем в месяце d, то возвращается последний день месяца-результата. Если число дней не меньше, то день месяца-результата и день месяца d совпадают. Временные компоненты даты d и результата одинаковы.

CURRENT_DATE Процедурные и SQL-операторы Встроенные функции SQL Функция Доступна в версии Где применима Возвращает текущую дату во временной зоне сеанса как значение типа DATE. Эта функция аналогична SYSDATE за исключением того, что SYSDATE не зависит от временной зоны текущего сеанса.

Процедурные и SQL-операторы Возвращает текущую дату во временной зоне сеанса как значение WITH Если задан параметр precision (точность), он представляет десятичную точность возвращаемого числа секунд;

по умолчанию используется 6.

DBTIMEZONE Процедурные и SQL-операторы Возвращает временную зону базы данных. Используется такой же формат, как и в операторе CREATE DATABASE или в самом последнем операторе ALTER DATABASE.

MONTH, DAY, В только поля DATE, Процедурные и SQL-операторы HOUR, MINUTE, SECOND, в все поля HOUR, TIMEZONE REGION, FROM Возвращает выбранные данные из параметра который должен быть выра жением типа DATE, или INTERVAL Запрашиваемое поле должно быть допустимым — например, нельзя извлечь TIMEZONE из значения DATE. Если поле недопустимо, будет порождать ошибку invalid extract field for extract source".

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

Процедурные и SQL-операторы Возвращает текущую дату во временной зоне сеанса как значение типа TIMESTAMP. Если определен параметр precision, он представляет десятичную точность числа возвращаемых секунд;

по умолчанию используется 6. LOCALTIMESTAMP возвращает значение TIMESTAMP, в то время как CURRENTJIMESTAMP возвращает значение TIMESTAMP WITH TIMEZONE.

Процедурные и SQL-операторы date2) Возвращает число месяцев между датой и датой date2. Если дни в и date2 совпада ют или если обе даты являются последними днями своих месяцев, то результат представляет собой целое число. В противном случае будет содержать дробную часть (в отноше нии к месяцу).

NEW_TIME(d, zone/, Процедурные и SQL-операторы Возвращает дату и время часового пояса zone2 для даты и времени часового пояса заданных значением d. и zone2 строки символов, их возможные значения приведены в таблице В можно определить дополнительные имена временных зон с помощью представления string) Процедурные и SQL-операторы 178 ГЛАВА Функция Доступна в версии Где применима Возвращает в string дату первого дня, наступающего после даты d. Строка string указывает день недели на языке текущего сеанса. Временной компонент возвращаемого значения тот же, что и временной компонент Регистр символов строки значения не имеет.

Процедурные и SQL-операторы Округляет дату d до единицы измерения, указанной в format. Форматы, применяемые в функциях ROUND и TRUNC, описаны в таблице 5.2. Если формат не указан, принимается формат по умолчанию который округляет d до ближайшего дня.

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

Формат (если указан) тот же самый, что применяется в последнем операторе ALTER SESSION.

Процедурные и SQL-операторы Возвращает время в UTC (Универсальное координированное время, ранее — Среднее время по Гринвичу) из параметра datetime, который должен включать в себя часовой пояс.

SYSDATE Процедурные и SQL-операторы Возвращает текущие дату и время в формате типа DATE. Аргументов нет. При использовании в распределенных SQL-операторах функция SYSDATE возвращает дату и время локальной базы данных.

Процедурные и SQL-операторы Возвращает текущие дату и время в формате типа TIMESTAMP WITH TIMEZONE.

При использовании в распределенных SQL-операторах SYSTIMESTAMP возвращает дату и время локальной базы данных.

TRUNC(d[, Процедурные и SQL-операторы Возвращает дату d, усеченную до единицы измерения, указанной с помощью format.

Применяются те же форматы, что и в функции ROUND (см. таблицу 5.2). Если format опущен, принимается формат по умолчанию который усекает d до ближайшего дня.

Процедурные и SQL-операторы Возвращает в виде строки символов смещение между заданным часовым поясом и UTC. Аргумент timezone можно определить либо как название часового пояса (см. таблицу или представление либо как смещение в формате Также могут использоваться функции SESSIONTIMEZONE и DBTIMEZONE. Смещение возвращается в формате Таблица 5.1. Строки формата TIME ZONE Строка Часовой пояс AST Atlantic Standard Time (атлантическое (нью-йоркское) поясное время) ADT Atlantic Daylight (атлантическое (нью-йоркское) летнее поясное время) BST Bering Standard Time (поясное время Берингова пролива) Bering Daylight Time (летнее поясное время Берингова пролива) CST Central Standard Time (центральное поясное время) Встроенные функции SQL Таблица 5.1. Строки формата TIME ZONE (продолжение) Строка Часовой пояс CDT Central Daylight Time (центральное летнее поясное время) EST Eastern Standard Time (восточное поясное время) Eastern Daylight Time (восточное летнее поясное время) GMT Greenwich Mean Time (среднее время по гринвичскому меридиану) HST Alaska-Hawaii Standard Time (поясное время Аляски и Гавайских островов) Alaska-Hawaii Daylight Time (летнее поясное время Аляски и Гавайских островов) Mountain Standard Time (поясное время Среднего Запада США) MDT Mountain Daylight Time (летнее поясное время Среднего Запада США) NST Standard Time (поясное время Ньюфаундленда) PST Pacific Standard Time (тихоокеанское поясное время) Pacific Daylight Time (тихоокеанское летнее поясное время) YST Yukon Standard Time (поясное время Юкона) YDT Yukon Daylight Time (летнее поясное время Юкона) Таблица 5.2. Форматы дат функций ROUND и TRUNC Формат Единица округления или усечения СС, SCC Век SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Год (округляется до 1 июля) IYYY, IYY, IY, I Год ISO Q Квартал (округляется до шестнадцатого дня второго месяца квартала) MONTH, MON, MM, RM Месяц (округляется до шестнадцатого дня) Тот же день недели, что и первый день года Тот же день недели, что и первый день года ISO Тот же день недели, что и первый день месяца DD, J День Day, DY, D Первый день недели НН, НН12, НН24 Час Ml Минута Арифметические операции с датами и временем Выполнение арифметических операций над датами датами и време нем (dt), интервалами (г) и числами (га) описывается в таблице 5.3. Обра тите внимание, что при вычитании одной даты из другой результатом является число.

Таблица 5.3. Семантика операций с датами Операция Тип Результат возвращаемого значения NUMBER разница в днях между d1 и d2.

Это значение является действительным числом, где дробная часть означает неполный день.

dt1 - dt2 INTERVAL Возвращает интервал между df 1 и dt2.

П INTERVAL Возвращает разницу между и He определен Запрещенная операция. Допустимо только вычитание одной даты из другой.

dt1 + dt2 He определен Запрещенная операция. Допустимо только вычитание одной из другой.

П INTERVAL Возвращает сумму и DATE К d1 добавляется п дней, и возвращается результат, имеющий тип DATE. Операнд п может быть вещественным числом, содержащим неполный день, DATE Из вычитается л дней, и возвращается результат, имеющий тип DATE. Операнд л может быть вещественным числом, содержащим неполный день.

dt1 + DATETIME Возвращает сумму dt1 и - DATETIME Возвращает разность df/ и л INTERVAL Возвращает умноженное на п.

INTERVAL Возвращает значение деленное на п.

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

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

Функция Доступна в версии Где применима Процедурные и SQL-операторы Возвращает строку, содержащую только допустимые символы SQL плюс слэш (/). Все символы string (они могут принадлежать любому набору символов), не являющиеся допустимыми, преобразуются в числовой эквивалент, перед которым указывается слэш.

Только операторы SQL Встроенные SQL Функция Доступна в версии Где применима Преобразует битовый вектор в эквивалентное число. Аргументом для является список разделенных запятыми каждое из которых может быть только 0 или возвращает 11, так как двоичным представлением 11 является 1011.

полезна при использовании группирования и предложения GROUP BY.

Процедурные и SQL-операторы Преобразует значение типа CHAR или VARCHAR2, содержащее идентификатор строки (ROWID) во внешнем формате, во внутренний двоичный формат. Аргумент string должен быть символьной строкой, содержащей идентификатор строки который представлен во внешнем формате (см. главу 2). Функция является обратной по отношению к функции Только операторы SQL Возвращает строку string (которая может содержать символы любого набора) в полностью нормализованной форме Unicode в том же наборе символов. Подробности о Unicode и форме представлены в документации Oracle. Аргумент string может быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.

Только операторы SQL Возвращает строку Unicode, которая является канонической декомпозицией string (может быть представлена в любом наборе символов). Подробности о Unicode и канонической декомпозиции приведены в документации Oracle. Аргумент string может быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.

Процедурные и SQL-операторы Возвращает значение WITH TIMEZONE, которое является комбинацией не содержащего информации о временном поясе, и предоставленного timezone.

Процедурные и SQL-операторы Преобразует двоичное значение, представленное строкой в значение типа RAW. Строка string должна содержать значения. Каждые два символа строки представляют один байт результирующего значения RAW. Функции и являются обратными по отношению друг к другу.

unit) Процедурные и SQL-операторы Преобразует х, которое должно быть числом, в значение INTERVAL DAY TO SECOND. Аргумент unit является строкой символов (типа CHAR, VARCHAR2, NCHAR или NVARCHAR2) и может принимать одно из следующих значений: Аргумент unit не зависит от регистра символов. По умолчанию возвращаемое значение имеет точность 9.

unit) Процедурные и SQL-операторы Преобразует х, которое должно быть числом, в значение INTERVAL YEAR TO MONTH. Аргумент unit является строкой символов (типа CHAR, VARCHAR2, NCHAR или NVARCHAR2) и может принимать одно из следующих значений: Аргумент unit не зависит от регистра символов. По умолчанию возвращаемое значение имеет точность 9.

Процедурные и SQL-операторы Возвращает представление значения REF.

Процедурные и SQL-операторы Преобразует значение типа RAW в строку содержащую шестнадцатеричное представление. Каждый байт преобразуется в строку. Функции RAWTOHEX и HEXTORAW являются обратными по отношению друг к другу.

Процедурные и SQL-операторы 182 ГЛАВА Функция Доступна в версии Где применима Преобразует значение типа RAW в строку символов, содержащую представление. Каждый байт преобразуется в строку.

ведет себя аналогично за исключением того, что возвращаемое значение будет иметь тип NVARCHAR2, а не VARCHAR2.

Процедурные и SQL-операторы Преобразует значение типа ROWID (идентификатор строки) в его внешнее представление в виде строки символов (которое может иметь различную форму в зависимости от исходного значения Функции ROWIDTOCHAR и являются обратными по отношению к другу.

Процедурные и SQL-операторы Преобразует значение типа ROWID (идентификатор строки) в его внешнее представление в виде строки символов (которое может иметь различную форму в зависимости от исходного значения rowid). Возвращаемым типом является NCHAR, а не CHAR.

TO_CHAR Процедурные и SQL-операторы Преобразует свои аргументы в символьный тип (см. ниже).

Процедурные и SQL-операторы Преобразует строку в тип CLOB. Аргумент string может быть литералом или другим столбцом Если аргумент содержит данные NCHAR, он преобразуется в набор символов базы данных.

TO_DATE Процедурные и SQL-операторы Преобразует свои аргументы в тип DATE (см. ниже).

Процедурные и SQL-операторы Преобразует строку (которая может быть CHAR, VARCHAR2, NCHAR или NVARCHAR2) в тип INTERVAL DAY TO SECOND. Необязательный аргумент может содержать только спецификацию NLS_NUMERIC_CHARARCTERS десятичных и групповых символов.

Только операторы SELECT Преобразует в LOB. Эта функция используется для преобразования данных LONG или LONG RAW в CLOB или BLOB соответственно.

Процедурные и SQL-операторы Возвращает строку, в которой все однобайтовые символы заменены эквивалентными многобайтовыми Эта функция применяется, если набор символов базы данных состоит как из однобайтовых, так и из многобайтовых символов. В противном случае string не изменяется. Функции и являются обратными по отношению друг к другу.

TO_NCHAR Процедурные и SQL-операторы TO_NCHAR ведет себя так же, как TO_CHAR, за исключением того, что результат будет представлен в национальном наборе символов, а не в наборе символов базы данных (см. ниже).

Процедурные и SQL-операторы Преобразует строку string в NCLOB. Аргумент может быть литералом или другим столбцом LOB. Данные из если необходимо, в национальный набор символов базы данных.

Процедурные и SQL-операторы Встроенные функции SQL Функция Доступна в версии Где применима Преобразует свои аргументы в числовой тип (см. ниже).

Процедурные и SQL-операторы Преобразует все многобайтовые символы строки string в эквивалентные однобайтовые символы.

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

Процедурные и SQL-операторы Преобразует свои аргументы в тип (см. ниже).

Процедурные и SQL-операторы Преобразует свои аргументы в тип TIMESTAMP WITH (см. ниже).

Процедурные и SQL-операторы Преобразует строку string (которая может быть CHAR, VARCHAR2, NCHAR или NVARCHAR2) в тип INTERVAL YEAR TO MONTH. аналогична функции за исключением того, что не получает параметров NLS в качестве аргумента и преобразует в интервал YEAR TO MONTH, а не в DAY TO SECOND.

TO_CHAR (дата и дата-время) Синтаксис Назначение Преобразует дату или отметку времени d в строку символов Если задан format (формат), он используется для управления структурой результата. Строка формата состоит из элементов формата.

Каждый элемент возвращает часть значения даты или отметки времени, на пример месяц. Элементы формата даты, которые допустимы в описаны в таблице 5.4. Если format не указан, используется формат по умол чанию для отметки времени или отметки времени с часовым поясом.

Если задан он управляет языком для компонентов месяца и дня в возвращаемой строке. Формат nlsparams следующий:

где language — требуемый язык. Дополнительная информация о и элементах формата даты содержится в "Server SQL Reference".

Пример SELECT "Right Now" FROM dual;

Right Now 10-AUG-01 15:44: 184 Таблица 5.4. Допустимые элементы формата даты и даты-времени Элемент формата даты Описание Пунктуация Все символы пунктуации воспроизводятся в результирующей строке "текст" Текст, содержащийся в двойных кавычках, воспроизводится без изменений AD, A.D. Указатель AD с точками или без точек AM, Указатель Ante Meridiem с точками или без точек ВС, B.C. Указатель ВС с точками или без точек СС, SCC Век;

SCC возвращает даты ВС как отрицательные значения D День недели Название дня, дополненное пробелами до длины в девять символов DD, День месяца DDD Сокращенное название дня Е Сокращенное название эры (только календари Japanese Imperial, ROC и Thai Buddha) Полное название эры (только календари Japanese Imperial, ROC Official и Thai Buddha) FF Доли секунд вне зависимости от системы счисления НН, НН12 дня НН Неделя года (1 — 52, 1 — 53) на основе стандарта ISO IYY, I Последние три, две или одна цифра года ISO IYYY Четырехзначный год на основе стандарта ISO J Юлианский день. Число дней с 1 января 4712 ВС. Соответствующий результат будет целым значением.

Ml Минуты (0 - 59) MM = = Сокращенное название месяца Название месяца, дополненное пробелами до девяти символов РМ, P.M. Указатель Post Meridiem с точками или без точек Q Квартал года - 4);

JAN - MAR = RM Месяц римскими цифрами (I — XII);

JAN = I, DEC = XII RR Последние две цифры года для годов других веков RRRR Круглый год;

аналогичен RR за исключением того, что может чать четыре цифры.

SS Секунды SSSSS Секунды с полуночи (0 — 86 399). Формат всегда будет выдавать числовое значение.

Информация о летнем времени Час часового пояса Минута часового пояса Встроенные функции SQL Таблица 5.4. Допустимые элементы формата даты и даты-времени (продолжение) Элемент формата даты Описание Регион часового пояса (в виде Неделя года — 53). Неделя 1 начинается в первый день года и продолжается семь дней. Таким неделя не обязательно начинается в воскресенье.

W Неделя месяца (1 — Недели определяются так же, как и для элемента Символ локальной системы счисления Год с запятой в указанной позиции YEAR, Год. SYEAR возвращает даты ВС как отрицательные числа.

YYYY, SYYYY Четырехзначный SYYYY возвращает даты ВС как отрицательные числа.

YYY, YY, Y Последние три, две или одна цифра года Эти элементы зависят от регистра символов. Например, будет возвращать а будет возвращать :

Эти элементы являются новыми в Oracle9i.

TO_CHAR (число) Синтаксис Назначение Преобразует аргумент типа NUMBER в VARCHAR2.

Необязательный аргумент format управляет преобразованием. Доступные числовые форматы описаны в таблице 5.5. format задан, результи рующая строка будет иметь точно столько символов, сколько необходимо для сохранения значащих цифр num. Параметр используется для определения десятичных и групповых разделителей, а также символа валюты. Он может иметь формат:

rfg" где к g — десятичный и групповой разделители соответственно. Строка siring представляет символ валюты. Например, в США десятичным разде лителем обычно является точка групповым разделителем — запятая (,), а символом валюты — доллар ($). Поддержка национального языка по дробно описана в "Oracle Пример "Result" FROM dual;

Result 12,34, 186 SELECT =,.

= "Money" "Result 2" FROM dual;

Result Таблица 5.5. Элементы числового формата Элемент Пример Описание формата строки формата $ $999 Возвращает значение с ведущим знаком доллара независимо от символа валюты. Это можно использовать в дополнение к ведущим или завершающим нулям.

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

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

S S9999 Возвращает ведущий для положительных чисел и '-' для отрицательных чисел.

S 9999S Возвращает завершающий знак '+' для положительных чисел и '-' для отрицательных чисел.

PR 99PR Возвращает отрицательное значение в скобкахХ Положительное значение будет иметь ведущий или завершающий пробел.

D 99D9 Возвращает разделитель десятичной точки в указанной позиции. Число девяток на каждой стороне определяет максимальное число цифр.

G 9G999 Возвращает групповой разделитель в указанной позиции. G может присутствовать более одного раза в строке формата.

С С99 Возвращает ISO-символ валюты в указанной позиции. С может присутствовать более одного раза в строке формата.

L L999 Возвращает локальный символ валюты в указанной позиции.

, 999,999 Возвращает запятую в указанной позиции вне зависимости от группового разделителя.

99.99 Возвращает десятичную точку в указанной позиции вне зависимости от десятичного разделителя.

V 99V999 Возвращает значение, умноженное на где числу девяток после V. В случае необходимости это значение округляется.

ЕЕЕЕ Возвращает значение в экспоненциальной форме записи.

RM Возвращает значение, записанное римскими цифрами в верхнем регистре.

Встроенные функции SQL Таблица 5.5. Элементы числового формата (продолжение) Элемент Пример Описание формата строки формата rm Возвращает значение, записанное римскими цифрами в нижнем регистре.

Возвращает значение без ведущих или завершающих пробелов.

0 0999 Возвращает ведущие нули, а не пробелы.

0 9990 Возвращает завершающие нули, а не пробелы.

9 99 Каждая девятка представляет значащую цифру ъ результате.

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

TO_DATE Синтаксис Назначение Преобразует строку string типа CHAR или в тип DATE. format является строкой формата даты (см. таблицу 5.4). Если format не задан, используется формат по умолчанию для сеанса. Аргумент используется в таким же образом, как и в и являются взаимно обратными функциями.

Пример Г) DECLARE DATE;

BEGIN 7, DD, END;

Синтаксис TO_NUMBER( string [ nlsparams] Назначение Преобразует строку string CHAR или в зна чение типа NUMBER. Если задан format, строка должна соответствовать числовому формату. Параметр nlsparams ведет себя таким же образом, как и в TO_NUMBER и являются взаимно обратными функциями.

Пример Г] DECLARE v NUMBER;

188 BEGIN := END;

и TO_TIMESTAMP_TZ и выше Синтаксис Назначение Преобразует строку string типа CHAR или в тип TIMESTAMP или TIMESTAMP WITH TIMEZONE. format являет ся строкой формата даты (см. таблицу 5.4). задан, ется формат по умолчанию для сеанса. Аргумент используется в и таким же образом, и в Где применимы Процедурные и SQL-операторы Пример DECLARE TIMESTAMP;

BEGIN := 7, DD, END;

Групповые и аналитические функции Групповые функции обрабатывают по несколько строк, но возвращают один результат. Б этом их отличие от однострочных функций, которые вы дают результат для каждой строки базы данных. Например, групповая фун кция COUNT возвращает число строк, удовлетворяющих критерию, задаваемому предложением WHERE оператора SELECT. Эти функции мож но применять только в списках выбора запросов и в конструкциях GROUP BY и HAVING.

В большинстве этих функций допускается использование ров (уточнителей) аргументов: DISTINCT (отличные от других) и ALL (все). Если указывается DISTINCT, рассматриваются только те возвращае мые запросом значения, которые отличны от других. Когда применяется ALL, функция рассматривает все значения, возвращаемые запросом. Если не указано другое условие, ALL принимается как параметр, заданный по умолчанию.

i вводит предложение OVER, которое может применяться с лю и выше бой функцией, рассматриваемой в этом разделе. В случае использования предложения OVER функция называется аналитической, а не группо вой. Аналитические функции могут возвращать несколько строк для каждой группы, а не единственное значение, как делают групповые фун кции. Предложение OVER определяет скользящее окно в группе. Допол нительную информацию можно найти в "SQL Reference".

Встроенные функции SQL Функции описываются в следующей таблице:

Функция Доступна в версии AVG([DISTINCT|ALL] Возвращает среднее значение для значений столбца.

x2) Oracle» Возвращает коэффициент корреляции множества составленных из числовых выражений х и х2. После удаление всех строк, в которых или содержит NULL, результат определяется выражением Возвращает число строк в запросе. Если указан символ *, возвращает общее число Если передается элемент списка выбора, то подсчитываются значения, отличные от NULL.

Oracle» Возвращает ковариацию совокупности множества составленных из числовых выражений х и х2. Результат определяется выражением где п является числом пар с записями, отличными от NULL х2) Oracle» Возвращает ковариацию подобия множества пар, составленных из числовых выражений х1 и х2.

После удаления всех строк, х1 или х2 содержит результат определяется выражением п число пар с записями, отличными от NULL Oracle» Возвращает кумулятивное распределение значения в группе значений (см. "SQL Reference").

DENSE_RANK Oracle» Возвращает ранг строки в упорядоченной группе строк, где ранги являются последовательными целыми числами, начинающимися с 1 (см. "SQL Reference").

Возвращает уникальное числовое значение, используемое для различения групп в предложении GROUP BY.

GROUPING Отличает суперсборные строки от обычных групповых строк (см. "SQL Reference").

Oracle» Возвращает число, соответствующее битовому вектору GROUPING для строки (см. "SQL Reference").

| All] col) Возвращает максимальное значение для элемента списка выбора. Заметим, что DISTINCT и ALL не оказывают на функцию никакого влияния, поскольку в любом случае максимальное значение будет одним и тем же.

| ALL] col) Возвращает минимальное значение для элемента списка выбора. Заметим, что квалификаторы DISTINCT и ALL не оказывают на функцию никакого влияния, поскольку в любом случае минимальное значение будет одним и тем же.

Oracle» Эта функция является обратной функцией распределения, которая предполагает применение непрерывной модели распределения (см. "SQL Reference").

ГЛАВА Функция Доступна в версии PERCENTILE_DISC Эта функция является обратной функцией распределения, которая предполагает применение дискретной модели распределения (см. "SQL Reference").

Возвращает ранг заданной строки как значение между 0 и 1 (см. "SQL Reference").

RANK Возвращает ранг заданной строки. Ранги не обязательно должны быть последовательными, так как равные строки получают одинаковый ранг (см. "SQL Функции REGR Эти функции REGR_R2, REGR_AVGX, REGR_SYY и REGR_SXY) создают линию обыкновенной регрессии наименьших квадратов для множества "SQL Reference").

| ALL] col) Возвращает стандартное отклонение для элемента списка выбора. Это значение определяется как квадратный корень из дисперсии.

Вычисляет стандартное отклонение совокупности и возвращает корень квадратный из дисперсии совокупности.

Вычисляет кумулятивное стандартное отклонение и возвращает корень квадратный из дисперсии выборки.

| ALL] Возвращает сумму значений для элемента списка выбора.

VAR_POP(x) Возвращает дисперсию совокупности множества чисел после отбрасывания значений NULL.

Результат определяется выражением Возвращает дисперсию выборки множества чисел после отбрасывания значений NULL.

Результат определяется выражением - VARIANCE([DISTINCT | ALL] col) Возвращает дисперсию col.

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

Функция Доступна в версии Где применима Процедурные и SQL-операторы Возвращает локатор BFILE, соответствующий имени физического файла fi/ejiame операционной системы. Каталог directory должен быть объектом DIRECTORY в словаре данных.

Встроенные функции SQL Функция Доступна в версии Где применима COALESCE(expr7,..., Процедурные и SQL-операторы Возвращает первое выражение, не являющееся NULL, из списка аргументов. Если все выражения будут NULL, COALESCE вернет NULL. Это обобщение функции DECODE(base_expr, Только операторы default) Функция DECODE аналогична последовательности вложенных операторов IF-THEN-ELSE.

Базовое выражение последовательно сравнивается с выражениями compare/, и т.д. Если базовое выражение соответствует /'-му пункту сравнения, возвращается значение Если базовое выражение не соответствует ни одному пункту, возвращается значение по умолчанию Выражения сравнения рассматриваются по очереди. Если найдено соответствие, оставшиеся пункты сравнения не рассматриваются. Если базовое выражение является NULL-значением, оно считается эквивалентным выражению сравнения типа NULL. Каждое значение выражения сравнения преобразуется, если необходимо, в тип данных значения первого выражения сравнения. Этот тип данных является также типом возвращаемого значения.

DUMP Только операторы SQL Описание дается ниже.

Процедурные и SQL-операторы Возвращают пустые локаторы LOB. возвращает символьный локатор, а локатор.

Только операторы SQL Определяет, возвращаются ли какие-либо узлы при просмотре документа идентифицированного посредством имени и пути доступа Возвращается значение типа NUMBER, которое равно 0, если узлов нет, и больше 0 при наличии узлов. Информация об использовании типов представлена в документации Oracle.

Только операторы SQL Возвращает часть документа XML, идентифицированного посредством имени XMLTypejnstance и пути Xpathjtring. Информация об использовании типов представлена в документации Oracle.

[, Процедурные и SQL-операторы Возвращает наибольшее выражение среди своих аргументов. Перед сравнением каждое выражение неявно преобразуется к типу выражения Если имеет символьный тип, то сравнение выполняется без дополнения пробелами, причем результат имеет тип VARCHAR2, LEAST(expr7 [, Процедурные и SQL-операторы Возвращает наименьшее значение из списка выражений. Функция LEAST похожа на функцию GREATEST тем, что все выражения неявно преобразуются к типу данных первого выражения.

Операции сравнения символов выполняются методом сравнения без дополнения пробелами.

Ь) Только операторы SQL Возвращает NULL, если а равно и а, если аргументы не равны.

192 Функция Доступна в версии Где применима Процедурные и SQL-операторы Возвращает если содержит в противном случае возвращает Если ехрг1 не является строкой символов, то возвращаемое значение имеет тот же тип данных, что и Иначе возвращаемое значение имеет тип данных VARCHAR2. Эта функция обеспечивает отсутствие в активном наборе запроса.

Процедурные и SQL-операторы Если содержит NULL, возвращается иначе возвращается ехргЗ. Возвращаемое значение имеет тип ехрг2, если не является символьным;

в противном случае возвращается VARCHAR2.

SYS_CONNECT_BY_PATH Только операторы SELECT SYS_CONNECT_BY_PATH возвращает для значения столбца путь от корня до узла. Применяется только в иерархических запросах (см. "SQL Reference").

Процедурные и SQL-операторы Возвращает значение parameter, связанное с контекстом пространства имен namespace.

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

SYS_DBURIGEN Только операторы SQL Создает URL для извлечения документа из базы данных. Информацию об использовании типов данных можно найти в документации Oracle.

SYS_GUID Процедурные и SQL-операторы Возвращает глобально уникальный идентификатор как значение типа RAW.

Процедурные и SQL-операторы Возвращает тип ID самого общего типа объекта Процедурные и SQL-операторы Объединяет несколько документов или фрагментов документов в один документ.

Информацию об использовании типов данных XML можно найти в документации Oracle.

Oracle9i Процедурные и SQL-операторы Возвращает фрагмент документа на основе данных в базе данных. Информацию об использовании типов XML можно найти в документации Oracle.

TREAT(expr Oracle9i Только операторы SQL TREAT используется для изменения объявленного типа выражения. Можно изменить объявленный тип только на подтип или супертип заданного выражения. Возвращает как если бы оно имело тип или являлось ссылкой на него (в зависимости от того, что указано).

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

Функция не имеет аргументов.

USER Процедурные и SQL-операторы Возвращает значение типа VARCHAR2, содержащее имя текущего пользователя Oracle. Функция USER не имеет аргументов.

Встроенные функции SQL Функция Доступна в версии Где применима Процедурные и SQL-операторы Описание дается ниже.

VSIZE(x) Процедурные и SQL-операторы Возвращает число байтов во внутреннем представлении х.

DUMP Синтаксис Назначение Возвращает значение которое содержит инфор мацию о внутреннем представлении Аргумент определяет базу возвращаемых значений согласно следующей таблице:

number format Результат возвращается в виде 8 Восьмеричной записи 10 Десятичной записи 16 записи 17 Одиночных символов Если не указан, результат возвращается в десятичной за писи. Если заданы и length, возвращается length байтов, начиная с позиции По умолчанию выдается все представление. Тип данных возвращается в виде числа, соответствующего внутренним типам данных согласно следующей таблице:

Код Тип данных Доступен в версии 1 VARCHAR 2 NUMBER 8 LONG 12 DATE 23 RAW 24 LONG RAW 96 CHAR. BLOB 181 TIMESTAMP WITH TIMEZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND 208 UROWID 231 TIMESTAMP WITH LOCAL TIMEZONE Пример SELECT "Dump" FROM students FIRST_NAME Dump Scott Len=5: 83, 111, 116, Typ=1 77, 97, 114, 103, 97, 114, 101, Joanne Typ=1 Len=6: 74 97, 110, 110,, 111, Manish Typ=1 Len=6: 77, 97, 110, 105, 115, Patrick Typ=1 Len=7: 80 116, 114, 105, 99,, 97, Timothy Typ=1 Len=7: 84, 105, 109, 111, 116, 104, SELECT, 17) FROM students FIRST_NAME Dump Scott Typ=1 Len=5: S, C, 0, t, t Margaret Typ=1 M, a, r,, r, e, t g, a n, n, e Joanne Typ=1 Len=6: J, o, a, Manish Len=6: M, a, n, i, s, Patrick Typ=1 Len=7: P, a, t, r, i, c, k Timothy Typ=1 T, i, 0, t, h, у SELECT first_name,, 17, 2, 4) "Dump" FROM students;

FIRST_NAME Dump Scott Len=5: 0, t, t Typ= Margaret r, Typ= Joanne Typ=1 0, a, n, Manish Typ=1 Len=6: a. n, i, s Patrick Len=7: t, r, Typ= ISERENV t Синтаксис Назначение Возвращает значение содержащее информацию о текущем сеансе, на основе параметра option. Поведение описывается в следующей таблице:

Значение параметра Поведение USERENV(option) Если в текущем сеансе инициирована роль OSDBA, возвращает иначе возвращает Отметим, что возвращаемое значение будет иметь тип VARCHAR2, а не BOOLEAN.

Допустимо только в Trusted Oracle. Возвращает метку текущего сеанса. Дополнительную информацию можно найти в "Trusted Server Administrator's Guide".

Встроенные функции SQL Значение параметра Поведение USERENV(option) Возвращает язык и используемые в данный момент сеансом, а также набор символов базы данных. Они являются параметрами NLS. Возвращаемое значение имеет форму Возвращает зависящий от операционной системы идентификатор терминала текущего сеанса. Для распределенных операторов SQL возвращается идентификатор локального сеанса.

'SESSIONID' Возвращает идентификатор сеанса аудита, если параметр задан как TRUE. USERENV('SESSIONID') не допустим в распределенных операторах SQL.

'ENTRYID' Возвращает доступный идентификатор записи аудита, если инициализационный параметр AUDITJRAIL задан как TRUE.

USERENV('ENTRYID') не допустим в распределенных операторах SQL.

Возвращает сокращение ISO для названия языка. Это сокращенный формат в и выше.

Пример SELECT FROM dual;

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

ГЛАВА Курсоры 198 главах 4 и 5 уже говорилось о применении в PL/SQL.

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

Определение курсора Для обработки SQL-оператора Oracle выделяет область памяти, называе мую контекстной областью (context area). Она содержит информацию, необходимую для завершения обработки, включая: число строк, обраба тываемых оператором, указатель на представление этого оператора по сле проведения синтаксического анализа и активный набор (active т.е.

набор строк, возвращаемых запросом.

Курсор (cursor) — это указатель на контекстную область. С его помощью программа PL/SQL может управлять контекстной областью и ее состоя нием во время обработки оператора. Ниже приводится блок PL/SQL, в котором выполняется цикл выборки курсора. Здесь запрос возвращает несколько строк данных.

— Этот пример содержится в файле DECLARE /* Выходные переменные для хранения результатов запроса */ /* Переменная привязки, используемая в запросе */ v_Major := /* Объявление курсора */ CURSOR c_Students IS SELECT id, FROM students WHERE major = BEGIN /* Идентифицируем строки активного набора и подготовимся к дальнейшей обработке данных */ OPEN c_Students;

LOOP /* Извлечем каждую строку активного набора в переменные PL/SQL */ FETCH c_Students INTO v_StudentID, /* Если строки, которые нужно извлечь, закончились, выходим из цикла */ EXIT WHEN c_Students%NOTFOUND;

END LOOP;

Курсоры /* Освободим ресурсы, используемые запросом */ CLOSE END;

В этом примере используется явный (explicit) курсор. Имя курсора явно присваивается оператору SELECT при помощи оператора Для всех других SQL-операторов применяются неявные (implicit) курсоры.

Обработка явного курсора выполняется в четыре этапа (см. ниже). Обра ботка неявного курсора осуществляется в PL/SQL автоматически.

Обработка явных курсоров Для обработки явного курсора в PL/SQL необходимо выполнить четыре действия:

Объявить курсор.

2. Открыть курсор для запроса.

3. Выбрать результаты в переменные PL/SQL.

4. Закрыть курсор.

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

Объявление курсора При объявлении курсора ему назначается имя и ставится в соответствие некоторый оператор SELECT. Синтаксис объявления курсора:

CURSOR IS где — это имя курсора, a — запрос, который будет обрабатываться. Что касается области действия и области видимо сти, то курсоры отвечают стандартным правилам, установленным для идентификаторов PL/SQL (см. главу 3). Так как имя курсора является идентификатором PL/SQL, оно должно быть объявлено до того, как на него будет произведена ссылка. Можно использовать любые операторы SELECT, в том числе соединения (join) и операторы, в состав которых входят конструкции UNION (объединение) и MINUS Внимание Конструкция INTO не входит в состав оператора SELECT, а является частью оператора FETCH.

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

ниже приводится правильный раздел объявлений:

200 DECLARE CURSOR c_Classes IS SELECT * FROM classes WHERE department = AND course = v_Course;

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

Г) DECLARE CURSOR c_Classes IS SELECT * FROM classes WHERE department = AND course = Для гарантии что все переменные, на которые производятся ссылки при объявлении курсора, уже объявлены, рекомендуется создавать все курсоры в конце раздела объявлений. В данной книге соблюдается это соглашение. Единственным исключением может быть ситуация, когда имя курсора указывается в ссылке — например, в атрибуте (при со здании записи с полями, соответствующими списку выбора курсора). В этом случае курсор должен быть объявлен до того, как на него будет произ ведена ссылка.

Открытие курсора Курсор открывается следующим образом:

OPEN где идентифицирует предварительно объявленный курсор.

Когда курсор открывается, происходит следующее:

• Анализируются значения переменных привязки.

• На основе значений переменных привязки и содержимого таблиц, к которым обращается запрос, определяется активный набор.

• Указатель активного набора устанавливается на первую строку.

Переменные привязки анализируются только во время открытия кур сора. Для примера рассмотрим следующий блок PL/SQL:

DECLARE v_Building rooms.

classes.

CURSOR IS SELECT building FROM rooms, classes WHERE = classes.

and department = v_Department and course = v_Course;

BEGIN - Перед открытием курсора присваиваем переменным привязки - определенные значения.

- Открываем курсор.

OPEN - Присваиваем переменным привязки новые значения. Это никак не - влияет на программу, так как курсор уже открыт.

v_Course END;

При открытии курсора c_Building в переменных и содержатся соответственно и Эти значения используются в за просе. Затем значения переменных и изменяются, но это никак не влияет на активный набор запроса. Чтобы применялись но вые значения, курсор необходимо закрыть и открыть заново. В соответст вии с транзакционной моделью (см. главу 4) запрос видит изменения, внесенные в базу данных до выполнения оператора OPEN, и изменения сделанные в базе данных текущей транзакцией. Если другой сеанс изменил данные, но еще не зафиксировал эти изменения, они видны не будут.

Активный набор, т.е. набор строк, удовлетворяющих условию запроса, определяется во время открытия курсора. К примеру, в предыдущем запро се возвращается одна строка Для таблицы (таблиц), ука занной в конструкции FROM запроса, проверяется условие WHERE, и все строки, для которых условие истинно, добавляются к активному набору.

Указатель на этот набор также создается во время открытия курсора. Он сообщает, какая строка должна быть считана курсором в следующий раз.

Открытый курсор нельзя открыть еще раз. Если команда OPEN приме няется к уже открытому курсору, Oracle генерирует ошибку например:

— Содержится в файле SQL> DECLARE 4 CURSOR IS 5 SELECT ID FROM students;

6 BEGIN 7 OPEN 9 - курсор еще раз. Будет сгенерирована ошибка 10 OPEN 11 END;

12 / 202 DECLARE ERROR at line PL/SQL: cursor already open at line at line Считывание строи из курсора Предложение INTO запроса является частью оператора FETCH. Опера тор FETCH имеет две формы:

FETCH INTO FETCH INTO где обозначает предварительно объявленный и открытый сор, представляет собой список предварительно объяв ленных переменных PL/SQL, разделенных запятыми, a — это предварительно объявленная запись PL/SQL. В любом случае пере менная (переменные) в конструкции INTO должна иметь тип, совмести мый со списком выбора запроса. Если воспользоваться созданным курсором то следующий оператор FETCH будет правильным:

Г] FETCH INTO В примере, приведенном ниже, демонстрируются правильный и не правильный операторы FETCH:

— Этот содержится в файле DECLARE v_Course CURSOR IS SELECT * FROM classes;

v_ClassesRecord BEGIN OPEN - Это допустимый оператор FETCH, помещающий первую строку в запись - PL/SQL, которая соответствует списку выбора запроса.

FETCH INTO v_ClassesRecord;

- Этот оператор FETCH неверен, так как список выбора запроса - возвращает 7 столбцов таблицы classes, но считывание - происходит только в 2 переменные.

- Это приведет к ошибке wrong number - of values in the INTO list of a FETCH statement" - ("неверное число значений в списке INTO оператора FETCH") FETCH INTO v_Department, v_Course;

END;

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

Для определения момента считывания всего набора используется ат рибут %NOTFOUND. При выполнении последнего считывания FETCH выходным переменным не будут присваиваться новые значения, т.е. они будут содержать прежние значения.

Внимание Операторы FETCH такого вида извлекают по одной строке за раз. В и выше можно извлекать более одной за раз, используя сборную конструкцию и предложение BULK COLLECT (см. главу 12).

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

CLOSE где обозначает ранее открытый курсор. После закрытия кур сора считывать из него строки нельзя. Если попытаться сделать это, Oracle выдаст сообщение об ошибке:

Invalid Cursor (неверный курсор) или Fetch out of Sequence (непоследовательное считывание) Попытка закрыть уже закрытый курсор также приведет к ошибке ORA-1001.

курсора В PL/SQL существуют четыре атрибута, которые могут быть применены к курсорам. Атрибуты курсора добавляются к имени курсора в блоке PL/SQL, подобно атрибутам и Однако курсорные рибуты возвращают не тип, а значения, которые могут быть использованы в выражениях. К атрибутам курсора относятся %FOUND, и При их описании в качестве примера исполь зуется программа, приведенная на рис. 6.1. добавляет новый ат рибут применяемый для извлечения массивов.

См. главу 12.) В этом примере предполагается, что в таблице temp_table содержатся две строки со следующими данными:

ГЛАВА %FOUND Это логический атрибут. Он возвращает TRUE, если при вующем считывании FETCH была извлечена строка, и FALSE — в противном случае. Если курсор не открыт, то при проверке выдается ошибка (неверный курсор). Поведение атрибута описывает ся в таблице;

здесь цифры соответствуют точкам, помеченным на рис.

Точка Значение Пояснение Ошибка: ORA-1001 Курсор еще не открыт, и активного набора для него не существует.

? NULL Курсор открыт, но еще не было произведено ни одного считывания строк. Значение атрибута не может быть определено.

TRUE С помощью предшествующего оператора FETCH выбрана первая строка таблицы TRUE С помощью предшествующего оператора FETCH выбрана вторая строка таблицы FALSE Предшествующий оператор FETCH не вернул никаких данных, так как все строки активного набора извлечены.

Ошибка: ORA-1001 Курсор и вся хранившаяся информация об активном наборе удалена.

DECLARE - Объявление курсора CURSOR cJempData IS SELECT * from -- Запись для хранения извлекаемых данных OPEN c_TempData;

- Открываем курсор INTO v_TempRecord;

- Извлекаем первую строку INTO v_TempRecord;

- Извлекаем вторую строку FETCH INTO v_TempRecord;

- Извлекаем третью строку CLOSE END;

Рис. 6.1. Фрагмент программы, в которой используется курсорный атрибут Ведет себя противоположно если предшеству ющее считывание возвращает строку — значение ложно.

Атрибут возвращает только если во время предше ствующего считывания строка извлечена не была. Этот атрибут часто ис пользуется в качестве условия выхода из цикла выборки. Поведение %NOTFOUND описывается в таблице (см. пример на рис. 6.1).

Точка Значение Пояснение Ошибка: Курсор еще не открыт, и активного набора для него не существует.

2 NULL Курсор открыт, но еще не было произведено ни одного считывания строк. Значение атрибута не может быть определено.

FALSE С помощью предшествующего оператора FETCH выбрана первая строка таблицы FALSE С помощью предшествующего оператора FETCH выбрана вторая строка таблицы TRUE Предшествующий оператор FETCH не вернул никаких данных, так как все строки активного набора извлечены.

Ошибка: ORA-1001 Курсор закрыт, и вся хранившаяся информация об активном наборе удалена.

%ISOPEN Этот логический атрибут используется для от крыт или нет соответствующий курсор. Если курсор открыт, %ISOPEN возвращает TRUE, а если не открыт — FALSE. Поведение опи сывается в следующей таблице:

Точка Значение Пояснение FALSE Курсор еще не открыт.

2 TRUE Курсор был открыт.

3 TRUE Курсор еще открыт.

4 TRUE Курсор еще открыт.

5 TRUE Курсор c_TempData еще открыт.

6 FALSE Курсор закрыт.

Этот числовой атрибут возвращает количество строк, счи танных курсором на данный момент. Если используется, когда соответствующий курсор еще не открыт, то возвращается ошибка ORA-1001. Поведение атрибута описывается в следующей таблице:

206 Точка Значение Пояснение 1 Ошибка Курсор еще не открыт, и активного набора для него не существует.

2 0 Курсор открыт, но еще не было произведено ни одного считывания строк.

3 1 Считана первая строка таблицы temp_table.

4 2 Считана вторая строка таблицы 5 2 К данному моменту считаны две строки таблицы 6 Ошибка Курсор закрыт, и вся хранившаяся информация об активном наборе удалена.

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

Таблица Значения, возвращаемые атрибутами курсора Точка FOUND NOTFOUND ISOPEN 1 ORA-1001 FALSE ORA- 2 NULL NULL TRUE 3 TRUE FALSE TRUE 4 TRUE FALSE TRUE 5 FALSE TRUE TRUE 6 ORA-1001 ORA-1001 FALSE ORA- Параметризованные курсоры Существует еще один способ использования переменных привязки в кур соре. Параметризованные (parameterized) курсоры, подобно процедурам (см. главы 9 и 10), принимают определенные аргументы. Рассмотрим кур сор c_Classes:

••- Этот пример содержится в файле DECLARE v_Department classes.

CURSOR c_Classes IS SELECT « FROM classes WHERE department = v_Department AND = Курсор содержит две переменные привязки — и v_Course. Можно превратить c_Classes в эквивалентный параметризован ный курсор:

Курсоры Этот пример содержится в файле DECLARE CURSOR c_Classes (p_Department p_Course classes. IS SELECT * FROM classes WHERE department = AND = p_Course;

При работе с параметризованным курсором фактические значения указываются в операторе например:

-- Этот пример содержится в файле OPEN 101);

В этом случае передается как значение для p_Department, a 101 для Кроме того, параметры можно передавать при помощи по зиционной или именной формы записи. Полная информация о передаче параметров приводится в главе 9.

Обработка неявных курсоров Как говорилось в предыдущем разделе, явные курсоры используются для обработки тех операторов SELECT, которые возвращают более одной строки. Однако каждый оператор SQL выполняется в пределах контекст ной области и поэтому имеет курсор, указывающий на конкретную кон текстную область. Такой курсор называется SQL- курсором. В отличие от явных курсоров, SQL-курсор не открывается и не закрывается програм мой. PL/SQL неявно открывает SQL-курсор, обрабатывает SQL-оператор и затем закрывает этот курсор.

Неявные курсоры используются для обработки операторов INSERT, UPDATE, DELETE, а также однострочных операторов SQL-курсор открывается и закрывается PL/SQL, поэтому команды OPEN, FETCH и CLOSE не нужны. Однако для SQL-курсоров можно при менять курсорные атрибуты. Например, ниже приводится блок, в кото ром оператор INSERT выполняется в том случае, если оператору UPDATE не соответствует ни одной строки.

— Этот пример содержится в файле BEGIN UPDATE rooms SET = WHERE = 99980;

- Если оператор UPDATE не выбирает ни одной — строки, вводим новую строку в таблицу rooms.

IF THEN INSERT INTO rooms (room_id, VALUES (99980, 100);

END IF;

END;

208 Ту же задачу можно выполнить при помощи атрибута — Этот пример содержится в файле BEGIN UPDATE rooms SET = WHERE = 99980;

- Если оператор UPDATE не выбирает ни одной - строки, вводим новую строку в таблицу rooms.

IF = О THEN INSERT INTO rooms (room_id, VALUES (99980, 100);

END IF;

END;

Атрибут можно использовать в операторах но он мало подходит для этого. Дело в том, что если опе ратор не выбирает ни одной строки, Oracle выдает сооб щение об ошибке:

no data found (данные не найдены) В результате управление блоком сразу же передается разделу обработки исключительных ситуаций, и атрибут не проверяется.

Проиллюстрируем вышесказанное на примере сеанса ••- Этот пример содержится в файле SQL> DECLARE 2 - Запись для хранения информации об аудиториях.

4 BEGIN - Считаем информацию об аудитории с идентификатором 6 SELECT * 7 INTO 8. FROM rooms 9 WHERE = -1;

11 - Следующий оператор не может быть выполнен, так как управление 12 - программой передается обработчику исключительной ситуации.

13 IF THEN 14 is true!

15 END IF;

16 EXCEPTION 17 WHEN THEN 19 END;

20 / NO DATA FOUND raised!

PL/SQL procedure successfully completed.

Курсоры Обработка исключительных ситуаций детально обсуждается в главе 7.

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

Можно воспользоваться также атрибутом однако он всегда будет возвращать FALSE, поскольку неявный курсор автоматиче ски закрывается после обработки его оператора.

Циклы выборки курсора Чаще всего курсоры используются для считывания всех строк активного набора с помощью цикла выборки. Цикл выборки (fetch loop) — это обыч ный цикл, в котором строки активного набора обрабатываются по поряд ку, одна за другой. Ниже рассматривается несколько различных типов циклов выборки курсоров и их применение.

Простые циклы В этих циклах для обработки курсора используется синтаксис простого цикла LOOP). Количество итераций задается с помощью атрибутов явного курсора. Рассмотрим пример:

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

students.

- Курсор для считывания информации о студентах-историках CURSOR c_HistoryStudents IS SELECT id, first_name, FROM students WHERE major = BEGIN - Откроем курсор и инициализируем активный набор.

OPEN LOOP - Считаем информацию о следующем c_HistoryStudents INTO v_StudentID, -- Выйдем из цикла, если строк для выбора больше нет.

EXIT WHEN c_HistoryStudentsXNOTFOUND;

Обработаем считанные строки. В нашем случае запишем всех - студентов в группу History введя информацию о них в - таблицу registered_students. Кроме запишем имена и - фамилии в таблицу temp_table.

INSERT INTO registered_students (student_id, department, course) 210 ГЛАВА VALUES 301);

.

INSERT INTO char_col) VALUES (v_StudentID, || ' ' II END LOOP;

- Освободим ресурсы, используемые курсором.

CLOSE END;

Обратите внимание, что оператор EXIT WHEN расположен сразу после оператора FETCH. После извлечения последней строки %NOTFOUND становится истинным и осуществляется выход из цикла. Кроме того, оператор EXIT WHEN указывается перед фрагментом обработки дан ных. Это делается для того, чтобы не обрабатывались строки NULL.

А теперь посмотрим, что произойдет, если перенести оператор EXIT WHEN в конец цикла:

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

v_FirstName - Курсор для считывания информации о студентах-историках CURSOR c_HistoryStudents IS SELECT id, FROM students WHERE major = BEGIN - Откроем курсор и инициализируем активный набор.

OPEN c_HistoryStudents;

LOOP - Считаем информацию о следующем студенте.

FETCH c_HistoryStudents INTO v_StudentID, считанные строки. В нашем случае запишем всех - студентов в группу History введя информацию о них в - таблицу registered_students. Кроме того, запишем имена и - фамилии в таблицу INSERT INTO registered_students department, course) VALUES 301);

INSERT INTO char_col) VALUES (v_StudentID, || ' ' II - Выйдем из цикла, если строк для выбора больше нет.

EXIT WHEN END LOOP;

Курсоры - Освободим ресурсы, используемые курсором.

CLOSE END;

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

Циклы WHILE Циклы выборки курсоров можно построить также при помощи синтакси са Рассмотрим пример:

•- Этот пример содержится в файле DECLARE - Курсор для считывания информации о студентах-историках CURSOR IS SELECT id, FROM students WHERE major - Объявим запись для хранения считанной информации.

v_StudentData BEGIN - Откроем курсор и инициализируем активный набор.

OPEN - Считаем первую строку, чтобы установить цикл WHILE.

FETCH c_HistoryStudents INTO - Выполняем пока есть строки для WHILE LOOP •- Обработаем считанные строки. В нашем случае запишем всех - студентов в группу History 301, введя информацию о них в - таблицу Кроме того, запишем имена и •- фамилии в таблицу INSERT INTO (student_id, department, course) VALUES ID, 301);

INSERT INTO temp_table char_col) VALUES ' ' - Считаем следующую строку. Условие %FOUND будет проверяться - перед тем, как цикл будет продолжен.

FETCH INTO v_StudentData;

END LOOP;

•- Освободим ресурсы, используемые курсором.

CLOSE END;

Этот цикл выборки действует точно так же, как и цикл LOOP (см. выше). Обратите внимание, что оператор FETCH появляется дважды — один раз перед циклом и один раз после обработки данных.

Условие следует проверять для каждой итерации цикла.

Курсорные циклы FOR В обоих циклах выборки, описанных выше, необходимо обрабатывать курсоры явным образом с помощью операторов OPEN, FETCH и CLOSE.

В PL/SQL имеется упрощенный вид цикла — курсорный цикл FOR, в ко тором управление обработкой курсора осуществляется неявно. Рассмот рим пример:

— Этот пример содержится в файле DECLARE - Курсор для считывания информации о студентах-историках.

CURSOR c_HistoryStudents IS SELECT id, FROM students WHERE major = BEGIN - Начнем цикл. Здесь производится неявное открытие курсора - c_HistoryStudents.

FOR v_StudentData IN c_HistoryStudents LOOP Здесь осуществляется неявное считывание.

•- Обработаем считанные строки. В нашем случае запишем всех - студентов в группу History введя о них в - таблицу Кроме того, запишем имена и - фамилии в таблицу INSERT INTO registered_students department, course) VALUES 301);

INSERT INTO (num_col, char_col) VALUES | ' ' II END LOOP;

- После окончания цикла выполняется неявное закрытие - c_HistoryStudents.

END;

Необходимо отметить два важных аспекта, касающихся этого приме ра. Во-первых, запись v_StudentOata не объявляется в разделе объявлений блока. Эта переменная неявно объявляется компилятором PL/SQL, подоб но индексу цикла в числовых циклах FOR. Она имеет тип c_HistoryStu а ее область действия — цикл FOR. Неявное объявление Курсоры индекса цикла и область действия этого объявления такие же, как и в чис ловом цикле FOR (см. главу 3). Поэтому нельзя присваивать какое-либо значение переменной цикла внутри курсорного цикла FOR.

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

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

Неявные циклы FOR Синтаксис цикла FOR можно сократить еще больше. Можно неявно объя вить сам курсор, как показывает следующий пример:

Г] •- Этот пример содержится в файле BEGIN Начало цикла. Здесь неявно OPEN.

FOR IN (SELECT FROM students WHERE = LOOP •- Здесь выполняется неявно FETCH и проверяется - Обработаем извлеченные строки. В нашем случае запишем всех студентов - в группу History введя информацию о них в таблицу - Кроме того, запишем имена и фамилии в таблицу INSERT INTO department, course) VALUES 301);

INSERT INTO char_col) VALUES ' ' END LOOP;

- Теперь, когда цикл закончен, неявно выполняется CLOSE.

END;

Запрос содержится в скобках внутри самого оператора В данном случае запись v_StudentData и курсор объявляются неявно. Однако курсор не имеет имени.

NO_DATA_FOUND и Исключительная ситуация NO_DATA_FOUND (данные не найдены) уста навливается только для операторов когда условию WHERE запроса не соответствует ни одна из строк. В этом случае атрибут принимает значение TRUE. Если нет соответствия строкам в условии WHERE оператора UPDATE или DELETE, также принимает значение TRUE. Поэтому во всех циклах выборки, рас смотренных выше, для определения условия выхода из цикла используется не исключительная ситуация а атрибут или 214 ГЛАВА Курсоры SELECT FOR Очень часто при обработке данных в цикле выборки модифицируются строки, которые были считаны курсором. В PL/SQL для этого предлагает ся удобное средство, состоящее из двух частей: конструкции FOR UPDATE (для обновления), указываемой в объявлении курсора, и конструкции WHERE CURRENT OF (где текущая строка...) в операторе UPDATE или DELETE.

FOR Конструкция FOR UPDATE является частью оператора SELECT. Ее разре шается использовать в качестве последней конструкции оператора, после ORDER BY (если SELECT... FROM... FOR UPDATE [OF где — это столбец таблицы, для которой выполняется запрос. Можно также воспользоваться списком столбцов. Например, ниже приводится раздел объявлений, в котором описываются два курсо ра. Это правильные формы курсора UPDATE.

DECLARE - В этом курсоре в конструкции FOR UPDATE указываются два столбца.

CURSOR IS * FROM students FOR UPDATE OF - В этом курсоре столбцы не указаны.

CURSOR c_LargeClasses IS SELECT course FROM classes WHERE max_students > FOR UPDATE;

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

Однако при использовании конструкции FOR UPDATE перед открытием курсора устанавливаются исключающие блокировки строк активного набо ра. Эти блокировки предотвращают изменение строк активного набора другими сеансами до завершения транзакции.

Курсоры Если другой сеанс уже блокировал строки активного набора, операция UPDATE будет ждать снятия этих блокировок неограни ченное время. Можно воспользоваться параметром NOWAIT (без ожида ния), тогда оператор OPEN сразу же вернет сообщение об ошибке, если строки блокированы другим сеансом:

ORA-54: resource busy and acquire with NOWAIT specified (ресурс занят и запрошен с указанием NOWAIT) В этом случае можно попытаться открыть курсор позже или изменить активный набор так, чтобы считывать неблокированные строки.

В использовать синтаксис [OF [WAIT где п — число секунд ожидания. Если строки не разблокируются в течение п секунд, возвращается ошибка WHERE CURRENT OF Если курсор объявлен с помощью FOR UPDATE, в операторе UPDATE или SELECT может использоваться конструкция WHERE CURRENT OF.

Синтаксис этой конструкции:

WHERE CURRENT OF курсор где курсор — это имя курсора, объявленного с применением FOR UPDATE.

Конструкция WHERE CURRENT OF определяет строку, только что счи танную курсором. Ниже приводится блок, который обновляет текущие зачеты всех студентов, зарегистрированных в HIS -- Этот пример содержится в файле DECLARE - Число зачетов, которое нужно добавить к общему числу зачетов - каждого - Этот курсор будет выбирать только тех студентов, которые - в настоящий момент зарегистрированы в HIS CURSOR c_RegisteredStudents IS SELECT * FROM students WHERE id IN (SELECT FROM registered_students WHERE AND course = 101) FOR UPDATE OF BEGIN - Установим цикл выборки для курсора.

FOR IN c_RegisteredStudents LOOP •- Определим число зачетов для HIS SELECT credits 216 INTO FROM classes WHERE department =.

AND course = 101;

- Обновим строку, только что считанную из курсора.

UPDATE students SET current_credits current_credits + WHERE CURRENT OF END LOOP;

- Завершим работу и снимем блокировку.

COMMIT;

END;

Оператор UPDATE обновляет только тот столбец, который указан в конструкции FOR UPDATE при объявлении курсора. Если не указан ни один столбец, можно обновлять любые столбцы.

Разрешается выполнять запрос с конструкцией FOR UPDATE, не ссы лаясь при этом на строки, извлекаемые посредством WHERE CURRENT OF. В этом случае строки остаются блокированными и поэтому могут быть модифицированы только текущим сеансом (удерживающим блоки ровку). Операторы UPDATE и DELETE, изменяющие эти строки, не будут блокировать их, если выполняются сеансом, удерживающим блокировку.

Однако не разрешается использовать WHERE CURRENT с курсором, ко торый не был объявлен FOR UPDATE.

Использование оператора COMMIT при считывании строк Обратите внимание, что в предыдущем примере оператор COMMIT вы полняется после окончания цикла выборки. Дело в том, что этот опера тор снимает все блокировки, удерживаемые сеансом. Конструкция FOR UPDATE устанавливает блокировки, а оператор COMMIT их снимает.

Когда это происходит, действие курсора прекращается и любая последу ющая попытка считать строки приводит к ошибке Oracle:

fetch out of sequence (непоследовательное считывание) Рассмотрим пример, в результате выполнения которого возвращается эта ошибка.

— Этот пример содержится в файле DECLARE, - Курсор для считывания информации обо всех студентах, а также — для блокирования строк CURSOR IS SELECT * FROM students FOR UPDATE;

- Переменная для выбранных данных Курсоры BEGIN - Откроем курсор. При этом будут установлены блокировки.

OPEN - Считаем первую запись.

FETCH INTO - Выполним оператор COMMIT. При этом блокировки будут сняты - и действие курсора прекращено.

COMMIT;

- Этот оператор FETCH приведет к ошибке ORA-1002.

FETCH INTO v_StudentInfo;

END;

Таким образом, если оператор COMMIT расположен внутри цикла вы борки SELECT...FOR UPDATE, считывание строк после COMMIT будет завершаться ошибкой. Поэтому не рекомендуется размещать COMMIT внутри цикла. Если курсор не определен как UPDATE, ни каких проблем не возникает.

Совет Даже если не используется UPDATE, применение оператора COMMIT внутри цикла выборки нежелательно.

Например, если запрос воздействует на большое число строк, то можно получить ошибку snapshot too old" (мгновенный снимок слишком старый).

Как поступить, если требуется обновить строку, только что считанную из курсора, и при этом применить оператор COMMIT внутри цикла? Кон струкцией WHERE CURRENT OF воспользоваться нельзя, так как курсор не может быть определен как FOR UPDATE. В этом случае можно исполь зовать первичный ключ таблицы в условии WHERE оператора UPDATE, как показано в следующем примере:

— Этот пример содержится в файле DECLARE - Число зачетов, которое нужно добавить к общему числу зачетов - каждого студента.

- Этот курсор будет выбирать только тех студентов, которые - в настоящий момент зарегистрированы в HIS CURSOR IS SELECT * FROM students WHERE id IN (SELECT student_id FROM registered_students WHERE AND course = 101);

218 ГЛАВА BEGIN - Установим цикл выборки для курсора.

FOR IN LOOP - Определим число зачетов для HIS SELECT INTO FROM classes WHERE department = AND course = 101;

- Обновим строку, только что считанную из курсора.

UPDATE students SET current_credits = current_credits + WHERE id = - Можно завершить работу внутри цикла, так как курсор не - объявлен как FOR UPDATE.

COMMIT;

END LOOP;

END;

В этом примере, по существу, имитируется конструкция WHERE CURRENT OF, однако строки активного набора не блокируются. Как следствие, этот блок может выполняться не так, как требуется, если дру гие сеансы будут параллельно обращаться к данным.

Совет Если используемая в запросе таблица не имеет первичного ключа, вместо него можно применить псевдостолбец ROWID.

Можно извлечь ROWID каждого столбца в переменную PL/SQL (типа ROWID или UROWID) и использовать его в предложении WHERE в виде = Курсорные переменные Во всех приведенных выше примерах явных курсоров рассматривались статические курсоры (static cursors), связанные с одним SQL-оператором, который был известен при компиляции блока. Курсорная же переменная (cursor variable) может быть связана с различными операторами во время выполнения программы. Курсорные переменные аналогичны перемен ным PL/SQL, в которых могут содержаться различные значения. Стати ческие же курсоры аналогичны константам PL/SQL, так как они могут быть связаны только с одним запросом на этапе выполнения программы.

Внимание Динамический SQL также позволяет связывать различные операторы с переменной (см. главу Курсоры Чтобы воспользоваться курсорной переменной, ее необходимо предва рительно объявить. Во время выполнения программы должна быть выделе на память для хранения этой переменной, так как курсорные переменные имеют тип Затем ее можно открывать, считывать и закрывать так же, как статический курсор. Обычно курсорные переменные используются внутри хранимой процедуры, которая возвращает переменную клиентской программе. Эта техника позволяет хранимой процедуре открывать запрос и возвращать результирующий набор клиенту для обработки. Хранимые про цедуры рассматриваются в главах 9 и 10.

Объявление курсорной переменной Курсорные переменные имеют ссылочный тип. До это был единст венный доступный ссылочный тип. и выше допускают REF объек тных типов;

см. главу 12.) Как говорилось в главе 3, ссылочный тип — это то же самое, что и указатель в языке программирования С. С помощью та кого типа можно именовать области хранения данных во время выполне ния программы. Чтобы воспользоваться ссылочным типом, необходимо сначала объявить переменную, а затем выделить область памяти. Ссылоч ные типы PL/SQL объявляются следующим образом:

REF тип где тип — это предварительно определенный тип. Ключевое слово REF означает, что новый тип будет указателем на ранее определенный тип.

Таким образом, тип курсорной переменной — REF CURSOR. Полный син таксис описания типа курсорной переменной таков:

TYPE IS REF CURSOR [RETURN где — это имя нового ссылочного типа, а — тип записи, указывающий типы списка выбора, которые в итоге будут возвращаться курсорной переменной.

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

— Этот пример содержится в файле DECLARE - Описание при помощи %ROWTYPE TYPE CURSOR RETURN - Определяем новый тип записи, TYPE IS RECORD ( students.

- переменную этого типа 220 ГЛАВА -- и курсорную переменную, использующую этот тип записи.

TYPE IS REF CURSOR RETURN - При помощи %TYPE можно объявить еще один тип.

TYPE IS REF CURSOR RETURN - курсорные переменные, использующие созданные выше типы.

Ограниченные и неограниченные курсорные переменные Курсорные переменные, рассмотренные в предыдущем разделе, являют ся ограниченными (constrained): они объявляются только для конкретного возвращаемого типа. Переменная должна открываться для такого запро са, список выбора которого соответствует типу, возвращаемому курсо ром. В противном случае возникает предопределенная исключительная ситуация (несоответствие типов строк).

Однако в PL/SQL разрешается объявлять неограниченные (unconstrained) курсорные для которых предложение RETURN отсутствует.

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

Этот пример содержится в файле DECLARE - Определим неограниченный ссылочный тип TYPE IS REF CURSOR;

- и переменную этого типа.

t._FlexibleRef;

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

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

В обязанности клиентской программы входит выделение памяти для пе ременной. В зависимости от языка, на котором написана программа кли ентской стороны, курсорная переменная размещается по-разному. В этом разделе приводятся примеры размещения для клиента Курсорные переменные могут использоваться также в клиентских программах и JDBC. Дополнительная информация о применении курсорных перемен ных в этих средах содержится в документации Oracle.

Курсоры Выделение памяти в Рго*С Чтобы выделить память при использовании прекомпилятора необходимо объявить переменную типа Затем она размещается с помощью команды EXEC SQL ALLOCATE. Например, следующий фрагмент объявляет и разме щает курсорную переменную:

EXEC SQL ALLOCATE :

Базовая курсорная переменная является неограниченной, так как не имеет связанного возвращаемого типа.

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

Открытие курсорной переменной для запроса Для связи курсорной переменной с определенным оператором SELECT используется расширенный синтаксис оператора OPEN, позволяющий указать требуемый запрос:

OPEN FOR где — это ранее объявленная курсорная переменная, a — требуемый запрос. Если курсорная ограни чена, список выбора должен соответствовать типу, возвращаемому курсо ром. Иначе выдается сообщение об ошибке:

ORA-6504: PL/SQL: return types of result set variables or query do not match (PL/SQL: не установлено соответствие между возвращаемыми типами переменных результирующего набора и запросом) Более детальная информация об ошибках PL/SQL и том, что делать в случае их возникновения, приводится в главе 7. В качестве примера рас смотрим курсорную переменную:

Г) DECLARE TYPE t_ClassesRef IS REF CURSOR RETURN v_ClassesCV Теперь откроем v_ClassesCV:

OPEN v_ClassesCV FOR SELECT * classes;

Если же попытаться открыть v_ClassesCV так:

Г] OPEN FOR 222 SELECT department, course FROM classes;

то будет сообщение об ошибке так как список выбо ра запроса не соответствует типу, возвращаемому этой курсорной переменной.

Оператор в принципе, аналогичен оператору OPEN: ана лизируются переменные привязки и определяется активный набор. После выполнения можно считывать информацию из курсорной переменной. Считывание может производиться либо на сервере с помощью оператора FETCH (так же, как для обычного либо на клиенте.

\ Внимание используемый в OPEN FOR, должен быть жестко закодирован в программе — он не может содержаться в снимается в посредством встроенного динамического SQL (см. главу 12.) Закрытие курсорных переменных На сервере курсорные переменные закрываются точно так же, как стати ческие курсоры, — при помощи оператора CLOSE. При этом освобожда ются ресурсы, используемые запросом, однако память, отведенная для хранения самой курсорной переменной, освобождается не всегда, а толь ко когда переменная выходит из области своего действия. Запрещается повторно закрывать ранее закрытые курсоры и курсорные переменные.

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

Пример использования курсорных переменных Ниже приводится полная программа демонстрирующая использо вание курсорных переменных. В ней имеется встроенный блок PL/SQL, предназначенный для выбора данных либо из таблицы classes, либо из таблицы rooms, в зависимости от ввода пользователя. Текст программы сопровождается подробными комментариями.

Г] — Этот пример содержится в файле /* Подключим файлы заголовков С и SQL. */ tfinclude EXEC SQL INCLUDE SQLCA;

/* Строка символов для хранения имени и пароля пользователя */ char = /* Курсорная переменная SQL. */ SQL_CURSOR /* Целочисленная переменная для управления выбором таблицы */ int v_Table;

/* Выходные переменные для таблицы */ Курсоры int VARCHAR /* Выходные переменные для таблицы classes */ VARCHAR int /* Подпрограмма обработки ошибок - печать сообщения об ошибке и выход */ void { Error EXEC SQL ROLLBACK WORK RELEASE;

exit(1);

int { /* Строка символов для хранения ввода пользователя */ char /* Установим обработчик ошибок. Всякий раз, когда происходит ошибка SQL, будет вызываться подпрограмма */ EXEC SOL WHENEVER SQLERROR DO Установим соединение с базой данных. */ EXEC SQL CONNECT :

to /* Выделим память для курсорной переменной. */ EXEC SQL ALLOCATE /* Напечатаем сообщение, запрашивающее ввод пользователя, и запишем введенное значение в переменную */ from (C)lasses or (R)ooms. Enter с or r:

/* Определим нужную таблицу. */ if (v_Choice[0] == v_Table = else v_Table = 2;

/* Откроем курсорную переменную с помощью встроенного блока PL/SQL. */ EXEC SQL EXECUTE BEGIN IF = 1 THEN /* Откроем переменную для таблицы classes. */ OPEN FOR SELECT department, course FROM classes;

ELSE /* Откроем переменную для таблицы rooms.

OPEN :v_CursorVar FOR SELECT description FROM rooms;

224 END IF;

END;

/* По окончании считывания данных выйдем из цикла. */ EXEC SQL WHENEVER NOT FOUND DO BREAK;

/* Начнем цикл выборки. */ for (;

;

) { if (v_Table == 1) { /* Считаем информацию о группе. */ EXEC SQL FETCH INTO :

/* Отобразим полученную информацию на экране.

имеет тип VARCHAR, поэтому воспользуемся полем для фактической длины и полем для данных. */ *s } else { /* Считаем информацию об аудитории. */ EXEC SQL FETCH :v_CursorVar INTO /* Отобразим полученную информацию на экране. v_Description имеет тип VARCHAR, поэтому воспользуемся полем для фактической длины и полем для данных. */ /* Закроем курсор. «/ EXEC SQL CLOSE : v_CursorVar;

/* Закроем соединение с базой данных. */ EXEC SQL COMMIT WORK RELEASE;

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

Второй пример курсорной переменной Следующий пример похож на предыдущий, однако он написан целиком на PL/SQL. Это хранимая процедура, с помощью которой выбирается информация из таблицы classes или из таблицы rooms в зависимости от входных данных. Более подробно о процедурах говорится в главах 9 и 10.

Курсоры Г] — Этот пример содержится в файле CREATE OR REPLACE PROCEDURE /* Демонстрируется использование курсорной переменной на сервере.

Если -- это то в таблицу вводится информация из таблицы classes. Если p_Table то вводится информация из таблицы rooms. */ IN VARCHAR2) AS /* Определим тип курсорной переменной */ TYPE IS REF CURSOR;

/* и собственно переменной. */ /* Переменные для хранения результатов */ v_Course v_RoomID v_Description rooms. • BEGIN - На основании входного параметра откроем курсорную переменную.

IF p_Table = THEN OPEN FOR SELECT department, course FROM classes;

p_table = THEN OPEN FOR SELECT description FROM ELSE /* При вводе неверного значения возникает ошибка. */ RAISE_APPLICATION_ERROR must be or END IF;

/* Цикл выборки. Обратите внимание, что оператор EXIT WHEN расположен после оператора FETCH. */ LOOP IF = THEN FETCH v_CursorVar INTO v_Course;

EXIT WHEN INSERT INTO char_col) VALUES (v_Course, ELSE FETCH INTO v_Description;

EXIT WHEN INSERT INTO (num_col, char_col) VALUES (v_RoomID, SUBSTR(v_Description, 1, 226 ГЛАВА END IF;

END LOOP;

/* Закроем курсор. */ CLOSE v_CursorVar;

COMMIT;

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

• Курсорные переменные нельзя объявлять в модуле. Сам тип можно, но переменную нельзя.

• Удаленные подпрограммы не могут возвращать значение курсорной переменной. Курсорные переменные могут передаваться между клиен тской и серверной стороной PL/SQL (например, из клиента Oracle но не между двумя серверами.

• Сборные конструкции PL/SQL (индексные таблицы, вложенные таблицы и изменяемые массивы) не могут хранить курсорные пере менные. Аналогично, таблицы и представления базы данных не мо гут хранить столбцы REF CURSOR. Можно, однако, иметь массив клиентских курсорных переменных (такой, ResultSets).

• Нельзя использовать курсорные переменные с динамическим SQL в • Запрос, связанный с курсорной переменной в операторе не может быть FOR UPDATE. Это ограничение снято в и выше.

Итоги В этой главе были рассмотрены операции, которые выполняются при об работке курсоров и позволяют явным образом управлять исполнением SQL-операторов. В Случае явных курсоров производятся операции объяв ления, открытия, считывания и закрытия курсора. Курсорные атрибуты используются для определения текущего состояния курсора, т.е. для управления его функционированием. Кроме того, были изучены различ ные виды циклов выборки и приведены примеры применения курсорных переменных. В следующей главе рассматривается обработка ошибок в PL/SQL.

ГЛАВА Обработка ошибок 228 ГЛАВА любой хорошо написанной программе должны существовать средства обработки, а при возможности и устранения ошибок. В PL/SQL обработка ошибок реализуется с помощью исключительных ситуаций (exception) и обработчиков исключительных ситуаций (exception handler). Исключите льные ситуации могут быть связаны с ошибками Oracle или с ошибками, определяемыми пользователем. В этой главе описывается синтаксис иск лючительных ситуаций и их обработчиков, а также приводятся правила их распространения. В конце главы даются рекомендации по использованию исключительных ситуаций.

Понятие исключительной ситуации В основе PL/SQL лежит язык программирования Ada, одним из свойств которого является механизм исключительных ситуаций. При использова нии этого механизма написанные на PL/SQL программы становятся го раздо надежнее, и во время их выполнения предоставляется возможность обработки как запланированных, так и незапланированных ошибок. Иск лючительные ситуации в PL/SQL аналогичны также исключительным си туациям в Java. Например, исключительные ситуации Java порождаются и перехватываются способом, подобным PL/SQL. Однако, в отличие от Java, исключительные ситуации PL/SQL не являются объектами и не име ют методов.

Классификация ошибок, возникающих в программах PL/SQL, приве дена в таблице Таблица Типы ошибок PL/SQL Тип ошибки Источник сообщения Обработка Ошибка компиляции Компилятор PL/SQL Интерактивная: компилятор сообщает об ошибках, а пользователь их исправляет.

Ошибка времени Система поддержки PL/SQL Программная: исключительные ситуации выполнения инициируются и перехватываются обработчиками исключительных ситуаций.

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

Г) identifier must be declared (идентификатор должен быть объявлен) так как в операторе SELECT неверно записан идентификатор students:

Обработка ошибок DECLARE NUMBER;

BEGIN SELECT INTO FROM END;

Исключительные ситуации и их обработчики — это при помощи которого программа реагирует на ошибки времени выполнения и устраня ет их. В число ошибок выполнения входят ошибки SQL, например:

unique constraint violated (нарушение ограничения уникальности) и процедурные например:

Г] PL/SQL: numeric or value error (ошибочное число или значение) Внимание PL/SQL имеет средство, называемое динамическим SQL Оно позволяет во время выполнения создавать и исполнять произвольные операторы SQL и блоки PL/SQL Если выполнить динамически блок PL/SQL, который содержит ошибку компиляции, то эта ошибка будет инициироваться во время выполнения и может перехватываться обработчиком исключительных ситуаций. Дополнительная информация о динамическом SQL содержится в главе 12.

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

Для обеспечения обработки ошибок в тех языках программирования, в которых поддерживается модель исключительных ситуаций (таких, как язык программы должны содержать операторы, реализующие эту обработку. Например:

Q int х = 1, у = 2, = 3;

/* Вызов функции;

х передается в качестве аргумента. */ if handle_error У = 1 / z;

if <ошибка> z = х + у;

if <ошибка> 230 Обратите внимание: проверка ошибок должна выполняться после каж дого оператора программы. Если не внести в программу такую проверку, ошибки не будут обработаны надлежащим образом. Кроме того, операторы, реализующие обработку ошибок, засоряют программу и затрудняют пони мание ее логической структуры. Сравним программу, приведенную в пре дыдущем примере, с аналогичной программой, написанной на PL/SQL:

DECLARE х NUMBER := 1;

у NUMBER := 2;

NUMBER := 3;

BEGIN f(x);

У := 1 / z = x + y;

EXCEPTION WHEN OTHERS THEN Обработчик всех ошибок */ END;

что раздел обработки ошибок отделен от логической схемы программы. В этом случае:

• Логика программы нагляднее и легче для понимания.

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

Объявление исключительных ситуаций Исключительные ситуации описываются в разделе объявлений блока, инициируются в выполняемом разделе, а обрабатываются в разделе иск лючительных ситуаций. Существуют два вида исключительных ситуаций:

определяемые пользователем и стандартные (предопределенные).

Исключительные определяемые пользователем Исключительная ситуация, определяемая пользователем, обозначает та кую ошибку, которая описывается программистом, причем совсем не обя зательно, чтобы эта ошибка была Oracle, — она может быть, например, ошибкой данных. Стандартные же исключительные ситуации соответствуют типичным ошибкам SQL и PL/SQL.

Исключительные ситуации, определяемые пользователем, описыва ются в разделе объявлений блока PL/SQL. Как и переменные, исключи тельные ситуации имеют собственный тип (EXCEPTION) и область действия. Например:

Обработка ошибок DECLARE I EXCEPTION;

— это идентификатор, который виден во всем блоке.

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

Стандартные ситуации В Oracle существует ряд исключительных ситуаций, которые соответству ют типичным ошибкам Oracle. Как и стандартные типы данных (NUMBER, и идентификаторы таких исключительных ситуаций описаны в модуле STANDARD (см. главу 3). Эти идентификато ры доступны программе, и их не надо описывать в разделе объявлений, в отличие от исключительных ситуаций, определяемых пользователем.

Стандартные исключительные ситуации приведены в таблице 7.2.

Внимание Можно также связать определяемые пользователем исключительные ситуации с ошибками Oracle (см. ниже раздел "Прагма Таблица 7.2. Стандартные исключительные ситуации Ошибка Oracle Соответствующая Описание исключительная ситуация DUP_VAL_ON_INDEX Нарушено ограничение уникальности TIMEOUT_ON_RESOURCE Истекло время при ожидании ресурса Произведен откат транзакции в связи с взаимоблокировкой транзакций INVALID_CURSOR Запрещенная операция с курсором NOT_LOGGED_ON Отсутствует соединение с Oracle LOGIN_DENIED Неверные имя/пароль пользователя NO_DATA_FOUND Данные не найдены Не удалось осуществить преобразование в универсальный rowid Оператор возвращает более одной строки ZERO_DIVIDE Деление на нуль Неудачная попытка преобразования к типу NUMBER;

например является недо значением ORA-6500 STORAGE ERROR Внутренняя ошибка PL/SQL;

инициируется, если PL/SQL недостаточно памяти 232 ГЛАВА Таблица 7.2. Стандартные исключительные ситуации (продолжение) Ошибка Oracle Соответствующая Описание исключительная ситуация ORA-6501 ошибка PL/SQL ORA-6502 Ошибка усечения, арифметическая ошибка или ошибка преобразования Базовая курсорная переменная и курсорная переменная PL/SQL имеют несовместимые типы строк ORA-6511 CURSOR_ALREADY_OPEN Попытка открыть курсор, который уже открыт ORA-6530 Попытка присвоить значение атрибуту ORA-6531 COLLECTION IS Попытка применить к таблице или изменяемому массиву содержащему NULL, метод сборных конструкций, отличный от EXISTS ORA-6532 Ссылка на индекс вложенной таблицы или изменяемого массива, лежащий вне объявленного диапазона (например, -1) ORA-6533 Ссылка на индекс таблицы или изменяемого массива, больший, чем число элементов данной сборной конструкции ORA-6592 Не найдено соответствующее предложение WHEN в операторе CASE ORA-30625 SELF IS Попытка вызвать метод экземпляра NULL-объекта Эта исключительная ситуация определена в и выше.

Эта исключительная ситуация определена в и выше.

Эта исключительная ситуация определена в Oracle9i и выше.

Краткое описание некоторых стандартных исключительных ситуаций приводится ниже. Более подробно об этих ошибках рассказывается в ру ководстве "PL/SQL Guide".

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

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

Обработка ошибок — Этот пример содержится в файле DECLARE 2 TYPE IS TABLE OF NUMBER 3 INDEX BY BINARY_INTEGER;

5 NUMBER;

6 BEGIN 7 := 8 END;

9 / DECLARE ERROR at line no data found at line Дополнительная информация об индексных таблицах PL/SQL содер жится в главе 8.

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

Г] INSERT INTO students VALUES STORAGE_ERROR и Это внутренние исключитель ные ситуации. Причиной их возникновения является либо нехватка памя ти либо внутренняя ошибка PL/SQL ERROR). Чаще всего внутренние ошибки вызываются сбоями в работе системы PL/SQL, и о них следует сообщать в службу технической жки корпорации Oracle (Oracle Technical Support).

VALUE_ERROR Эта исключительная ситуация устанавливается при по явлении арифметической ошибки, ошибки усечения или ограничения в процедурном операторе. Если же ошибка возникает в SQL-операторе, то генерируется исключительная ситуация INVALID_ NUMBER. Причиной ошибки может быть операция при сваивания, оператор SELECT...INTO, параметры RETURNING INTO опе ратора SQL или параметры подпрограммы. Все эти ситуации являются результатом присвоения значения переменной PL/SQL. Если возникает проблема с этим присваиванием, устанавливается До г текстом сообщения об ошибке было "Numeric or Value Error" (ошибочное число или Однако в г щение об ошибке будет указывать причину ошибки. Приведем примеры:

— Этот пример содержится в файле SQL> DECLARE 2 VARCHAR2(3);

234 ГЛАВА 3 BEGIN 5 END;

6 / DECLARE ERROR at line PL/SQL: numeric or value error: character string buffer too small at line DECLARE 2 v_TempVar 3 BEGIN 4 SELECT id INTO 6 FROM students 7 WHERE = 8 END;

9 / DECLARE * ERROR at line ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line Эта исключительная ситуация устанавливается при несоответствии типов базовой курсорной переменной и курсорной переменной PL/SQL. Например, возникает при несоответствии фактического и формального типов, которые возвраща ются процедурой, использующей в качестве аргумента курсорную пере менную. О курсорных переменных подробно рассказывается в главе 6, там же приводится пример этой исключительной ситуации.

Инициирование исключительных ситуаций Когда возникает ошибка, связанная с некоторой исключительной ситуа цией, инициируется (устанавливается) эта исключительная ситуация. Иск лючительные ситуации, определяемые пользователем, устанавливаются явно при помощи оператора RAISE, в то время как стандартные исключите ситуации (или определенные пользователем исключительные ситуа ции, связанные с ошибкой Oracle посредством прагмы инициируются неявно при возникновении соответствующих ошибок Oracle. Если возникает ошибка Oracle, не связанная с исключительной ситуацией, тоже инициируется исключительная ситуация. Ее можно пе рехватывать с помощью обработчика OTHERS (см. ниже раздел "Обра ботчик исключительных ситуаций OTHERS"). Стандартные исключитель ные ситуации при желании можно устанавливать также с помощью оператора RAISE. Усложним пример, приведенный в разделе "Исключи тельные ситуации, определяемые Обработка ошибок -- Этот пример содержится в файле DECLARE - Исключительная ситуация для указания условия ошибки EXCEPTION;

- Текущее число студентов, зарегистрированных в - Максимальное число студентов, допустимое в HIS- v_MaxStudents BEGIN /* Определим текущее число зарегистрированных студентов и максимальное число студентов. */ SELECT INTO FROM classes WHERE department = AND course = 101;

Сравним полученные значения. «/ IF > THEN /* Зарегистрировано слишком много студентов -- установим исключительную ситуацию. */ RAISE e_TooManyStudents;

ENF IF;

END;

При установлении исключительной ситуации управление программой сразу же передается разделу исключительных ситуаций блока. Если такого раздела нет, исключительная ситуация передается блоку (распространяется на в который входит данный блок (см. ниже раздел "Распространение исключительных ситуаций"). После передачи управления обработчику невозможно вернуться в выполняемый раздел блока (см. рис. 7.1).

Стандартные ситуации инициируются автоматически при возникнове нии соответствующей ошибки Oracle. Например, в этом блоке PL/SQL устанавливается исключительная ситуация Г] ••- Этот пример содержится в файле BEGIN INSERT INTO students (id, VALUES (20000, INSERT INTO students (id, VALUES (20000, END;

Исключительная ситуация устанавливается вследствие что стол бец id таблицы students является первичным ключом, и поэтому он дол жен быть уникальным. При попытке второго оператора INSERT ввести значение 20000 в этот столбец происходит ошибка:

unique constraint (

Объявление исключительной ситуации А.

DECLARE A Инициация исключительной BEGIN ситуации А.

Находящийся здесь программный код не выполняется.

EXCEPTION WHEN A THEN Управление передается обработчику исключительной ситуации.

Этот код выполняется.

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

EXCEPTION WHEN THEN WHEN THEN [ WHEN OTHERS THEN ] END;

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

Г) — Этот пример содержится в файле DECLARE Обработка ошибок - Исключительная ситуация для указания условия ошибки EXCEPTION;

- Текущее число студентов, зарегистрированных в HIS- NUMBER(3);

- Максимально допустимое число студентов в HIS- NUMBER(3);

BEGIN /* Определим текущее число зарегистрированных студентов и максимальное число студентов. */ SELECT INTO FROM classes WHERE department = AND course = 101;

/* Сравним полученные значения. */ IF > v_MaxStudents THEN /* Зарегистрировано слишком много студентов -- установим исключительную ситуацию. */ RAISE e_TooManyStudents;

END IF;

EXCEPTION WHEN e_TooManyStudents THEN /* Обработчик, в том случае, если в HIS- зарегистрировано слишком много студентов. Введем сообщение, поясняющее сложившуюся ситуацию. */ INSERT INTO (info) VALUES 101 | max allowed END;

Один обработчик может обслуживать несколько исключительных си туаций, для чего нужно перечислить их имена в условии WHEN, отделив одно от другого ключевым словом OR EXCEPTION WHEN OR THEN INSERT INTO log_table (info) VALUES select occu END;

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

В настоящее время компилятор PL/SOL не проверяет раздел выполнения в этом отношении.

238 ГЛАВА Данная исключительная ситуация может обрабатываться максимум толь ко одним обработчиком в разделе обработки исключений. Если для иск лючительной ситуации имеется более одного обработчика, компилятор PL/SQL будет инициировать ошибку как показывает следующий сеанс — Этот пример содержится в файле SQL> DECLARE 2 - Объявим два определенных пользователем исключения 3 e_Exception1 EXCEPTION;

4 e_Exception2 EXCEPTION;

5 BEGIN 6 - Инициируем только исключение 7 RAISE 8 EXCEPTION 9 WHEN THEN 10 INSERT INTO log_table 11 VALUES 12 WHEN e_Exception1 THEN 13 INSERT INTO log_table (info) 14 VALUES 15 WHEN e_Exception1 OR e_Exception2 THEN 16 INSERT INTO 17 VALUES 19 / WHEN e_Exception1 OR e_Exception2 THEN ERROR at line ORA-06550: line 15, column 3:

exception may appear in at most one exception handler in this block ORA-06550: 0, column 0:

PL/SQL: Compilation unit analysis terminated Обработчик исключений OTHERS PL/SQL определяет специальный обработчик исключительных ситуа ций — WHEN OTHERS. Обработчик OTHERS (другие) выполняется для всех инициированных исключительных ситуаций, которые не обработа ны другими предложениями WHEN, определенными в текущем разделе исключений (аналогично базовому классу Exception в Java). Он всегда должен быть последним обработчиком в блоке, чтобы все предыдущие (и более специальные) обработчики были перед этим просмотрены. WHEN OTHERS будет перехватывать все исключения, предварительно опреде ленные и определяемые пользователем. Обработчик OTHERS рекоменду ется указывать на самом высоком уровне программы (в самом внешнем блоке) для обеспечения распознавания всех возможных ошибок. Иначе ошибка будет распространяться в вызывающую среду (см. ниже раздел "Распространение исключительных ситуаций"). Это может привести к не желательным последствиям, таким как откат текущей транзакции.

Обработка ошибок Добавим в рассматриваемый пример обработчик OTHERS:

Г] -- Этот пример содержится в файле DECLARE - Исключительная ситуация для указания условия ошибки e_TooManyStudents EXCEPTION;

- Текущее число студентов, зарегистрированных в HIS- - Максимально допустимое число студентов в HIS- BEGIN /* Определим текущее число зарегистрированных студентов и максимальное число студентов, */ SELECT current_students, max_students INTO v_MaxStudents FROM classes WHERE department = AND course = 101;

/* Сравним полученные значения. */ IF > THEN /* Зарегистрировано слишком много студентов -- установим исключительную ситуацию. */ RAISE END IF;

EXCEPTION WHEN e_TooManyStudents THEN /* Обработчик, выполняющийся в том случае, если в HIS- зарегистрировано слишком много студентов. Введем сообщение, поясняющее сложившуюся ситуацию. */ INSERT INTO log_table (info) VALUES max allowed || WHEN OTHERS THEN /* Обработчик, выполняющийся для всех других ошибок. */ INSERT INTO log_table (info) VALUES error END;

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

240 * Совет Не используйте в коде обработчик исключительных ситуаций OTHERS THEN NULL;

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

SQLCODE и SQLERRM При использовании обработчика OTHERS быва ет полезно знать, какая ошибка Oracle установила исключительную ситуа цию. Можно регистрировать не только факт возникновения ошибки, но и ее тип, если это необходимо для выполнения конкретных действий. В PL/SQL такие сведения получают при помощи двух встроенных функ ций: SQLCODE и SQLERRM. SQLCODE возвращает код текущей ошибки, a SQLERRM — текст сообщения об ошибке. Для исключений, определяе мых пользователем, SQLCODE возвращает 1, a SQLERRM возвращает "User-defined Exception" (определенное пользователем Внимание возвращает код текущей ошибки и может быть использована в дополнение к функции SQLERRM (см. приложение А).

Ниже приводится полный текст блока PL/SQL, в котором используется обработчик исключительных ситуаций OTHERS.

Этот пример содержится в файле DECLARE - Исключительная ситуация для указания условия ошибки e_TooManyStudents EXCEPTION;

- Текущее число студентов, зарегистрированных в HIS- - Максимально допустимое число студентов в HIS- NUMBER(3);

- Переменная для хранения кода ошибки v_ErrorCode NUMBER;

- Переменная для хранения текста сообщения об ошибке v_ErrorText BEGIN /* Определим текущее число зарегистрированных студентов и максимально допустимое число студентов. */ SELECT INTO v_MaxStudents FROM classes WHERE department = AND course = /* Сравним полученные значения. */ IF > v MaxStudents THEN Обработка ошибок /* Зарегистрировано слишком много студентов -- установим исключительную ситуацию. */ RAISE END IF;

EXCEPTION WHEN THEN /* Обработчик, выполняющийся в случае, если в зарегистрировано слишком много студентов. Введем сообщение, поясняющее сложившуюся ситуацию.

INSERT INTO log_table VALUES 101 | || max allowed || WHEN OTHERS THEN /* Обработчик, выполняющийся для всех других ошибок */ v_ErrorCode SQLCODE;

- Обратите внимание на использование v_ErrorText := 1, 200);

INSERT INTO log_table (code, message, info) VALUES v_ErrorText, END;

\ Максимальная длина сообщения об ошибке Oracle составляет 512 сим волов. В примере переменная ограничена 200 символами (для соответствия полю code таблицы Если текст сообщения об ошибке превышает 200 операция := сама вызывает стандартную исключительную ситуацию Во избежание этого используется встроенная функция SUBSTR, обеспе чивающая присваивание переменной v_ErrorText не более чем 200 симво лов. О SUBSTR и о других встроенных функциях PL/SQL рассказывается в главе 5.

Заметим, что значения функций SQLCODE и SQLERRM сначала при сваиваются локальным переменным, и только потом эти переменные ука зываются в Данные функции являются процедурными, поэтому их нельзя использовать непосредственно в SQL-операторе.

Функция SQLERRM может принимать числовой аргумент. В этом слу чае она возвращает текст сообщения об ошибке, код которой равен за данному числу. Аргумент должен всегда быть отрицательным. Если аргумент SQLERRM равен нулю, возвращается сообщение Г] ORA-0000: normal, successful completion Если в SQLERRM передается положительное значение, отличное от +100, выдается сообщение Exception возвращает no data found 242 ГЛАВА При вызове в обработчике функция SQLCODE возвращает отрицате льное число, обозначающее ошибку Oracle. Единственным исключением является ошибка no data found", когда SQLCODE возвращает +100.

Если SQLERRM без аргументов вызывается из выполняемого раздела блока, она всегда возвращает сообщение ORA-0000: normal, successful completion A SQLCODE возвращает 0. Все эти ситуации показаны в следующем примере:

-- Этот пример содержится в файле SQL> BEGIN 3 | 4 | | 5 || SQLERRM);

6 | 7 || 8 END 9 / SQLERRM(O): ORA-0000: normal, successful completion data found -10: non-ORACLE exception SQLERRM: ORA-0000: normal, successful completion unique constraint violated ORA-00054: resource busy and acquire with specified PL/SQL procedure successfully completed.

Совет Обычно лучше использовать SQLERRM без параметров (а не передавать такой параметр, как SQLCODE). Версия без параметров вернет полное сообщение об ошибке с любой строкой подстановки, например, с именем ограничения в случае ошибки (см. предыдущий пример).

Прагма Можно связывать именованные исключительные ситуации с конкретны ми ошибками Oracle, что позволяет обнаруживать эти ошибки непосред ственно, а не с помощью обработчика OTHERS. Для этого служит прагма EXCEPTIONJNIT (см. главу 3). Прагма EXCEPTIONJNIT используется следующим образом:

PRAGMA EXCEPTIONJNIT где — это имя исключительной ситуации, объявленной перед прагмой, а — код ошибки, кото рую нужно связать с этой именованной исключительной ситуацией.

Прагма должна быть указана в разделе объявлений. Ниже приводится Обработка ошибок пример, в котором исключительная ситуация определяется пользователем и устанавливается, если во время выполнения программы происходит ошибка mandatory NOT NULL column missing or NULL during insert" (при вводе данных в столбец NOT NULL пропущено значение или указано Этот пример содержится в файле DECLARE EXCEPTION;

PRAGMA -1400);

BEGIN INSERT INTO students VALUES (NULL);

EXCEPTION WHEN then INSERT INTO log_table (info) VALUES END;

В одном предложении PRAGMA можно связать с ошибкой Oracle только одну исключительную ситуацию, определяемую пользователем. В обработчике этой исключительной ситуации функции и будут возвращать код и сообщение, соответствую щие ошибке Oracle, а не сообщение "User- Defined Exception" (определен ное пользователем исключение).

Использование функции RAISE_APPLICATION Для создания собственных сообщений об ошибках, более содержатель ных, чем именованные исключительные ситуации, пользователи могут применять встроенную функцию Сообще ния об ошибках, определяемых пользователем, передаются из блока в вы зывающую среду так же, как и сообщения об ошибках Oracle. Синтаксис функции следующий:

где — это параметр, лежащий в диапазоне от -20 000 до - 999, — текст, соответствующий данной ошибке, а сохра — логическое значение. Длина параметра не должна превышать 512 символов. Логический параметр необязателен. Если он установлен в TRUE, новая ошибка пополнит спи сок ранее ошибок (при наличии этого в случае же FALSE новая ошибка заместит текущий список ошибок.

Для примера рассмотрим процедуру, которая перед регистрацией но вого студента имеется ли для свободное место в учебной группе. Более подробно процедуры рассматриваются в главе 9:

••- Этот пример содержится в файле /* Регистрирует студента, указанного параметром p_StudentID, в группе, идентифицируемой параметрами и p_Course. */ CREATE OR REPLACE PROCEDURE Register ( 244 ГЛАВА IN students.

IN IN AS classes.

v_Count NUMBER;

BEGIN /* Определим текущее число зарегистрированных студентов и максимально допустимое число студентов в группе. */ BEGIN SELECT INTO FROM classes WHERE course = AND department = /* Выясним, есть ли в группе свободное место для нового студента. */ IF v_CurrentStudents + 1 > v_MaxStudents THEN RAISE_APPLICATION_ERROR(-20000, add students to | I || ' ' || END IF;

EXCEPTION WHEN THEN /* Указанная группа не существует */ p_Department || II p_Course || ' END;

/* Проверим, что студент еще не зарегистрирован */ INTO FROM registered_students WHERE = p_StudentID AND department = AND course = p_Course;

IF v_count = 1 THEN RAISE APPLICATION ERROR (-20002, ' p_StudentID ' is registered for ' II ' ' II p_Course);

END IF;

/* В группе есть место, и студент еще в нее не записан. Обновим необходимые таблицы. */ INSERT INTO registered_students (student_id, department, course) VALUES (p_StudentID, p_Department, p_Course);

UPDATE students SET current_credits = current_credits + WHERE ID = p_StudentID;

UPDATE classes Обработка ошибок SET = + WHERE course = AND department = END Register;

Pages:     | 1 | 2 || 4 | 5 |   ...   | 7 |



© 2011 www.dissers.ru - «Бесплатная электронная библиотека»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.