4. Зарезервированные слова. 2

1. Модель клиент/сервер. 1

2. Лексические единицы.. 2

3. Идентификаторы.. 2

4. Зарезервированные слова. 2

5. Ограничители. 3

6. Литералы.. 3

7. Комментарии. 4

8. Блок PL/SQL.. 4

9. Объявление переменных. 5

10. Типы PL/SQL.. 5

10.1. Числовые. 5

10.2. Символьные. 6

10.3. Семейство типов без обработки. 6

10.4. Семейство типов даты. 7

10.5. Семейство типов ROWID.. 7

10.6. Семейство логических типов. 7

10.7. Семейство типов Trusted. 7

10.8. Ссылочные типы. 8

10.9. Типы LOB.. 8

10.10. Записи. 8

10.11. Таблицы.. 8

10.12. Использование %TYPE, %ROWTYPE.. 9

10.13. Подтипы, определяемые пользователями. 10

10.14. Преобразование типов данных. 10

11. Выражения и операции. 10

11.1. Присваивание. 10

11.2. Логические выражения. 10

12. Управляющие структуры PL/SQL.. 11

12.1. IF-THEN-ELSE.. 11

12.2. Циклы.. 12

12.3. Операторы GOTO и метки.. 13

13. Использование SQL в PL/SQL.. 13

13.1. Select 13

13.2. Связи баз данных. 14

13.3. Ссылки на таблицы.. 14

14. Курсоры и курсорные переменные. 14

14.1. Курсорные атрибуты.. 16

14.2. Циклы выборки. 16

1. Модель клиент/сервер

Многие приложения для работы с базами данных создаются с использованием модели клиент/сервер. Сама программа размещается на компьютере клиента и посылает запросы на получение информации серверу базы данных. Запросы инициируются при помощи SQL, что, как правило, приводит к наличию в сети большого числа посылок — по одной на каждый SQL-оператор. Эта ситуация иллюстрируется в левой половине рис. 1.1. Теперь обратимся к его правой половине. Несколько SQL-операторов могут быть объединены в единый блок PL/SQL и посланы серверу как единое целое. В результате сетевой трафик снижается, а приложение функционирует намного быстрее.

2. Лексические единицы

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

■ Буквы верхнего и нижнего регистров: A-Z и a-z

■ Цифры: 0-9

■ Разделители: символы табуляции, пробелы и символы возврата каретки

■ Математические символы: + - * / < > =

■ Символы пунктуации: () { } [ ]?! ~;:. ' " @ # % $ ^ & _ |

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

3. Идентификаторы

Идентификаторы используются для именования объектов PL/SQL, таких как переменные, курсоры, типы и подпрограммы. Идентификатор начинается с буквы, за которой может следовать любая последо­вательность символов, состоящая ил букв, цифр, знаков доллара ($), знаков подчеркивания и знаков фунта (#). Другие символы запрещены. Максимальная длина идентификатора — 30 символов, причем все они явля­ются значащими. PL/SQL не учитывает регистр символов. Если нужно сделать идентификатор чувствительным к регистру символов, включить в его состав такие символы, как пробелы, или воспользоваться зарезервированными словами, можно заключить такой идентификатор в двойные кавычки. Максимальная длина идентификаторов в кавычках также равна 30 символам (без учета двойных ка­вычек). В состав идентификатора в кавычках может входить любой печатный символ, за исключением двойных кавычек.

4. Зарезервированные слова

Многие идентификаторы, называемые зарезервированными (или ключевыми) словами, имеют в PL/SQL особое значение. Использовать эти слова для именования собственных идентификаторов нельзя. (Напр. Begin) В состав же других идентификато­ров они могут быть включены. (Напр. v_ Begin Date)

5. Ограничители

Ограничители — это символы (одни символ или их последовательность), которые имеют специальное значение в PI./SQL. Они применяются для отделения идентификаторов друг от друга. Список ограни­чителей PL/SQL, приведен в таблице 2.1.

6. Литералы

Литерал — это символьное, числовое или логическое значение, которое не является идентификатором. Например, как -23.450, так и NULL — литералы. Символьные литералы (называемые также строковыми) состоят из одного или нескольких символов, ограниченных одиночными кавычками. Символьные литералы могут быть присвоены переменным, име­ющим тины CHAR и VARCHAR2. без преобразования. Считается, что все строковые литералы имеют тип данных CHAR. Частью литерала может быть лю­бой печатный символ из набора символов PL/SQL, в том числе и еще одна одиночная кавычка. Чтобы включить одиночную кавычку в строковый литерал, нужно расположить две одиночные кавычки рядом друг с другом. Например, поместить строку "Mike's string" в литерал следует так: . Строка ' '

считается = NULL.

Числовой литерал может представлять как целое, так и действительное значение. Числовые литералы могут быть присвоены без преобразования переменным, имеющим тип NUMBER. Это единственный вид литералов, которые разрешено использовать в арифметических выражениях. Целые литералы со­стоят из цифр, перед которыми может присутствовать знак литерала (+ или -). В таких литералах за­прещается указывать десятичную точку. Действительные литералы состоят из цифр (с указанием десятичной точки), перед которыми может стоять знак литерала. При желании действительные литералы могут быть записаны при помощи экспоненциального представления. Приведенные ниже действительные литералы также вполне корректны:

1.345Е7 9.87Е-3 -7.12е+12

После Е или е можно укалывать только целый литерал. Е означает "экспонента", т. е. умножить на 10 в степени …

Существует три логических литерала: TRUE (истина), FALSE (ложь) и NULL. Эти значения мо­гут быть присвоены лишь логическим (булевым) переменным. Логические литералы обозначают истин­ность или ошибочность некоторых условий и используются в операторах IF и LOOP.

7. Комментарии

Комментарии повышают удобочитаемость программ и делают их более понятными. Компилятор PL/SQL игнорирует комментарии. Существуют комментарии двух видов: однострочные и многостроч­ные. Однострочный комментарий начинается с двух символов тире и продолжается до конца строки (огра­ниченной символом возврата каретки). Многострочные комментарии начинаются с ограничителя /* и заканчиваются ограничителем */, как это делается в языке программирования С.

8. Блок PL/SQL

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

■ Анонимные блоки (anonymous blocks) создаются, как правило, динамически и выполняются только один раз.

■ Именованные блоки (named blocks) — это анонимные блоки с метками, дающими блокам имена. Они также создаются, как правило, динамически и выполняются только один раз. Чтобы дать блоку имя, нужно указать перед ключевым словом DECIARE метку, как показано в следу­ющем примере. Можно разместить метку и после ключевого слова END.

≪l_InsertIntoTemp≫

DECLARE

/* Раздел объявлений — переменные, типы, курсоры и логические подпрограммы PL/SQL. * /

BEGIN

/* Выполняемый раздел — процедурные и SQL-операторы, Это

основной раздел блока и единственный, являющийся обязательным,

EXCEPTION

/* Раздел исключительных ситуаций - операторы обработки ошибок. */

END l_InsertIntoTemp;

■ Подпрограммы (subprograms) — это процедуры, модули и функции, хранимые в базе данных. Эти блоки, как правило, не изменяются после своего создания и выполняются многократно явным образом посредством вызова процедуры, модуля или функции.

■ Триггеры (triggers) - это именованные блоки, которые также хранятся в базе данных. Они тоже, как правило, не изменяются после своего создания и выполняются многократно неявным образом при наступлении соответствующих событий. Событием, вызывающим активизацию триггера, является оператор языка манипулирования данными (DML — data manipulation language), выполняемый над некоторой таблицей базы данных. Операторы DML — это INSERT (ввести), UPDATE (обновить) и DELETE (удалить).

9. Объявление переменных

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

имя_переменной тип [CONSTANT] [NOT NULL] [:= значение];

где значение — начальное значение переменной. Вместо:= можно воспользоваться ключевым словом DEFAULT (по умолчанию). В разделе объявлений в одной строке может быть описана только одна переменная. В качестве имени переменной может быть использован любой разрешенный идентификатор. В неинициализированной переменной содержится NULL-значение. Если в объявлении указано NOT NULL, переменная должна быть инициализирована. Более того, запрещается присваивать NULL-значение пе­ременной, которая ограничена как NOT NULL в выполняемом разделе или в разделе исключительных ситуаций блока. Если в объявлении переменной указано CONSTANT, то она должна быть инициализирована и се на­чальное знамение не может быть изменено.

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

Область видимости (visibility) переменной — это фрагмент программы, в котором возможно обраще­ние к этой переменной без использования ее квалифицированного имени. Область видимости всегда ле­жит в пределах области действия; если переменная находится вне области своего действия, они невидима.

Квалификационное имя переменной – имя переменно, содержащее путь к ней в виде имя_блока1. имя_блока2. итд. имя_переменной, где имя_блокаN – имена блоков, вложенных друг в друга.

10. Типы PL/SQL

Имеются следующие категории типов: скалярные, составные, ссылочные, LOB (large object). Внутри скалярных типов не содержится никаких компонентов, в то время как в составных типах они присутствуют. Ссылочный тип является указателем на другой тип. Допустимые скалярные типы включают типы, аналогичные тем, которые применяются для определения столбцов таблиц базы данных, и ряд дополнительных типов. Скалярные типы можно разделить на семь семейств: числовые типы, символьные типы, типы без обработки, типы даты, типы ROWID, логические типы и типы Trusted.

10.1. Числовые

При помощи семейства числовых типов хранятся целые и действительные значения. Существует три базо­вых типа: NUMBER, PLS_INTEGER и BINARY_INTEGER. В переменных, имеющих тип NUMBER, мож­но сохранять как целые, так и действительные величины, а в переменных типа BINARY_INTEGER или PLS_INTEGER — только целые числа.

Синтаксис объявления некоторо­го числа NUMBER таков:

NUMBER (P,S); где Р— точность (precision), a S — масштаб (scale). Точность — это количество цифр в значении, а масш­таб — количество цифр справа от десятичной точки. Использование как точности, так и масштаба нео­бязательно, однако при указании масштаба необходимо указывать и точность. Максимальной точностью является 38 цифр, а масштаб может быть числом в диапазоне от до 127. Тип NUMBER хранится в десятичном представлении, и по этом удобен для хранения в БД, но для использования в вычислениях он ДБ преобразован в двоичное представление. BINARY_INTEGER изначально двоичный, поэтому, если переменная должны быть использована только для вычислений (напр. счетчик цикла), то лучше объявить ее этим типом. Диапазон от до . Тип PLS_INTEGER то же что и BINARY_INTEGER, с тем отличием, что в случае переполнения возникает ошибка, в то время как BINARY_INTEGER просто приводится к NUMBER, диапазон которого дольше.

10.2. Символьные

Переменные символьных типов используются для хранения строковых, или символьных, данных. В это семей­ство входят типы VARCHAR2, CHAR, LONG, NCHAR и NVARCHAR2.

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

VARCHAR2(L);

где L — максимальная длина (length) переменной в байтах. Если используются многобайтовые символы, то длина строки будет в m раз меньше (m - количество байт на символ). Указание длины обязательно — значения по умолча­нию не существует. Максимальная длина переменной типа VARCHAR2 составляет 32 767 байтов. Обра­тите внимание, что в поле столбца базы данных, имеющем тип VARCHAR2, можно хранить только 4000 байтов (В версии младше Oracle8 эта цифра 2000). Если длина переменной типа VARCHAR2 PL/SQL превышает 4000 байтов, ее можно ввес­ти лишь в столбец базы данных, имеющий тип LONG, максимальный размер которого составляет 2 ГБ. Аналогично, данные LONG нельзя выбрать в переменную VARCHAR2, если их размер превы­шает 32767 байтов.

Переменные типа CHAR представляют собой строки символов фиксированной длины. Синтак­сис объявления переменной CHAR таков:

CHAR(L);

где L — максимальная длина в байтах (с многобайтовыми символами ситуация как у VARCHAR2). Однако, в отличие от типа VARCHAR2 указание длины необязательно. Если она не указана, принимается значение по умолчанию 1, причем круглые скобки нс нужны. Переменные типа CHAR имеют фиксированную длину, поэтому при необходимости они заполняются до максимальной длины пробелами. Ограничение на длину в БД - 255 байтов до Oracle8 и 2000 байтов после включительно.

Тип LONG аналогичен VARCHAR2 и его максимальная длина 32760 байт. Но БД максимальная длина 2 ГБ, и как следствие в БД можно писать без проблем, а обратно возможны ошибки.

Переменные типов NCHAR и NVARCHAR2 описываются точно так же, как и переменные типов CHAR и VARCHAR2. Однако длина может меняться в зависимости от применяемого национального на­бора символов. Если в таком наборе размер символов фиксирован, длина указывается в символах. Если же их размер непостоянен, длина указывается в байтах.

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

10.3. Семейство типов без обработки

Синтаксис описания переменной RAW (LONG RAW) таков:

RAW(L);

где L — длина переменной в байтах. Тип RAW (LONG RAW) используется для хранения двоичных данных фиксиро­ванной длины. Максимальная длина переменной RAW LONG RAW равна () байтам. Максимальная же длина поля RAW базы данных составляет 255 байтов, поэ­тому, если размер данных превышает 255 байтов, они не могут быть введены в столбец RAW базы дан­ных. Однако они могут быть введены в столбец базы данных, имеющий тип LONG RAW, максимальная длина которого составляет 2 ГБ. Аналогично, если длина данных в поле LONG RAW превышает байтов, выбрать их в переменную PL/SQL RAW (LONG RAW) нельзя.

10.4. Семейство типов даты.

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

Значения переменным DATE обычно присваиваются посредством встроенной функции TO_DATE. Это позволяет легко преобразовывать символьные переменные в переменные DATE. Подобно этому при помощи функции TO_CHAR можно преобразовывать переменные DATE в символьные переменные.

10.5. Семейство типов ROWID

В этом семействе только один тип — ROWID, который абсолютно аналогичен типу, используемому для работы с псевдостолбцами ROWID базы данных. Он дает возможность сохранять идентификаторы строк (rowids). которые можно рассматривать в качестве ключей, однозначно определяющих каждую строку базы данных. Идентификаторы строк хранятся внутри базы данных в виде двоичных значении фиксированной длины, размер которых зависит от применяемой операционной системы. Для работы с идентификаторами строк их можно преобразовать в последовательности символов при помощи встро­енной функции ROWIDTOCHAR. Результатом работы этой функции является 18-символьпая последова­тельность, имеющая формат:

ВВВВВВВВ.RRRR.FFFF

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

0000001E.00FF.0001

указывает на 30-й блок, 255-ую строку в этом блоке, который расположен в файле 1. Идентификато­ры строк обычно не создаются программами PL/SQL; они выбираются в псевдостолбце ROWID табли­цы. Затем выбранное значение может быть использовано в задаваемом условии оператора UPDATE или DELETE.

10.6. Семейство логических типов

Единственным типом данных этого семейства является тип BOOLEAN. Логические переменные исполь­зуются в управляющих структурах PL/SQL, таких как операторы IF-THEN-ELSE и LOOP. Значение типа BOOLEAN может быть только TRUE, FALSE и NULL. (Но не 0 или 1 как в C\C++)

10.7. Семейство типов Trusted

Единственным типом данных этого семейства является тип MLSLABEL, используемый в Trusted Oracle для хранения двоичных меток переменной длины. В стандартной системе Oracle в переменных и столб­цах, имеющих тип MISLABEL, могут содержаться только NULL-значения. Внутренняя длина перемен­ных типа MLSLABEL составляет от 2 до 5 байтов, однако их можно автоматически преобразовывать в символьные переменные, а также выполнять обратное преобразование. Максимальная длина символьно­го представления переменной MLSLABEL составляет 255 байтов.

10.8. Ссылочные типы.

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

REF тип

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

10.9. Типы LOB

Используются для хранения больших объектов. Большой объект (large object) может быть либо двоичным, либо символьным значением размером до 4-х Гбайтов. В бо­льших объектах могут содержатся неструктурированные данные, доступ к которым осу­ществляется эффективнее, чем к данным типа LONG или LONG RAW, с меньшим числом ограничений. Типы LOB управляются с помощью модуля DBMS_LOB.

10.10. Записи.

Служат для объединения несколько других типов в один составной. Общий синтаксис описания типа записи таков:

TYPE тип_записи IS RECORD (

поле1 тип1 [NOT NULL] [:= выражение1],

поле2 тип2 [NOT NULL] [:= выражение2],

…

полеΝ типΝ [NOT NULL] [:= выражениеΝ]);

Здесь тип_записи — это имя нового типа, поле1... полеΝ — имена полей записи, a mun1... munΝ — типы соответствующих полей. Нет ограничений на N. В остальном всё аналогично объявлению переменных. Для ссылки на поле записи используется запись через точку. Ее синтаксис таков: имя_записи.имя_поля

Чтобы присвоить одной записи значение другой, они должны быть одного типа. Присвоить записи значение можно с помощью оператора SELECT. Поля записи должны соответствовать (имя и тип) по­лям, указываемым в списке выбора запроса.

10.11. Таблицы

Таблицы аналогичны массивам языка. Это тоже тип данных. Общий синтаксис описания таблицы таков:

TYPE тип_таблицы IS TABLE OF тип INDEX BY BINARY_INTEGER;

где тип_таблицы— имя нового типа, а тип— ранее определенный скалярный тип или ссылка на скаляр­ный тип посредством %TYPE. Для обращения к строке переменной типа таблица используется синтаксис:

имя_таблицы(индекс)

Для обращения к полю:

таблица(индекс).поле

где имя_таблицы — это имя переменной, а индекс— либо переменная, имеющая тип BINARY_INTEGER_, либо переменная или выражение, которые могут быть преобразованы к типу BINARY_INTEGER.

Таблица PL/SQL содержит два столбца — KEY (ключ) и VALUE (значение). Тип ключа — BINARY_INTEGER, а тип значения — это тип, указанный в описании (тип в синтаксисе описания таблицы, приведенном выше). Например:

Особенности:

■ Число строк таблицы ничем не ограничено. Единственное ограничение — это значения, которые могут быть представлены типом BINARY_INTEGER.

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

■ Ключи, используемые в таблице PL/SQL, необязательно должны быть последовательными. В качестве индекса таблицы может быть использовано любое значение или выражение, имеющее тип BINARY_INTEGER.

Таблицы имеют атрибуты. Для обращения к ним используется синтаксис имя_табоицы.атрибут. Существуют следующие атрибуты:

Атрибут COUNT (количество) возвращает текущее количество строк таблицы.

Атрибут DELETE (удалить) удаляет строки таблицы. Он используется следующим образом:

■ таблица. DELETE — удаляются все строки таблицы

■ таблица. DELETE(i)— удаляется строка таблицы с индексом i

■ таблица. DELETE (i,j) — удаляются все строки таблицы с индексами от i до j

Атрибут EXISTS (существует) в виде таблица. EXISTS (i) возвращает истинное значение, когда индекс i существует в таблице, а ложное — когда не существует.

FIRST и LAST возвращают индексы соответственно первой (first) и последней (last) строк таблицы.

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

10.12. Использование %TYPE, %ROWTYPE

%TYPE возвращает тип переменной или столбца таблицы после которой он написан. При этом ограничение NOT NULL не распространяется.

DECLARE

v_TempVar NUMBER(7,3) NOT NULL: 12.3;

v_AnotherVar v_TempVar%TYPE; — возвращает NUMBER (7,3)

Для объявление записей с теми же типами, которые имеют строки базы данных используется %ROWTYPE. Например:

DECLARE

v_RoomRecord rooms%ROWTYPE

10.13. Подтипы, определяемые пользователями

Подтип (subtype) — это альтернативное имя для некоторого типа, с помощью которого можно огра­ничить множество значений, допустимых для переменных, имеющих данный подтип. С по­мощью подтипа можно дать типу альтернативное имя, которое более точно описывает назначение этого типа. Для объявления подтипа используется синтаксис:

SUBTYPE новый_тип IS исходный_тип;

где новьш_тип — имя нового подтипа, а исходный_тип указывает базовый тип. Базовый тип может быть либо заранее определенным типом, либо подтипом, либо ссылкой %TYPE.

10.14. Преобразование типов данных

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

Например, переменную типа CHAR(10) нельзя преобразовать в переменную типа VARCHAR2(1), так как не будет хватать места для хранения значения этой переменной.

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

11. Выражения и операции

Посредством выражений и операций осуществляется связывание переменных PL/SQL. С помощью опе­раций определяются способы присваивания переменным конкретных значений и способы работы с эти­ми значениями. Выражение (expression) — это некоторая последовательность переменных и литералов, разделенных знаками операций, или операторами (operators). Значение выражения определяется значе­ниями переменных и литералов, составляющих это выражение, а также описанием используемых опера­ций.

11.1. Присваивание

Ее синтаксис таков: переменная:= выражение,

Операнд — это аргумент операции. В операциях PL/SQL используется либо один аргумент (унарная, или одноместная, операция), либо два аргумента (бинарная, или двухместная, операция).

Для символьных типов существует лишь одна операция — это операция конкатенации, или сцепления (| |). При по­мощи данной операции соединяются две строки символов (или аргументы, которые могут быть неявно преобразованы в строки символов).

=

Если все операнды в выражении конкатенации имеют тип CHAR, то и оно само имеет тип CHAR. Если же хотя бы один операнд имеет тип VARCHAR2, выражение имеет тип VARCHAR2. Считается, что строковые литералы имеют тип CHAR.

11.2. Логические выражения

Но всех управляющих структурах PL/SQL (за исключением GOTO) используются логические выраже­ния, называемые также условиями. Логическое, или булево, выражение — это любое выражение, которое даст в результате логическое значение (TRUE (истина), FALSE (ложь) или NULL).

Таблица истинности логических операторов:

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

Операция Описание

= Равно (равенство)

!= Не равно (неравенство)

< Меньше

> Больше

<= Меньше или равно

>= Больше или равно

В операции IS NULL значение TRUE возвращается только тогда, когда операндом является NULL. NULL-зиачения не могут быть проверены на истинность при помощи операций отношения, так как лю­бое выражение отношения с NULL в качестве операнда возвращает NULL.

Операция LIKE (подобие) применяется для сопоставления с некоторым образцом строк символов, подобно тому, как это делается в регулярных выражениях системы Unix. Знак подчеркивания (_) соот­ветствует только одному символу, а знак процента (%) — нулю и более символов. Выражения ниже возвращают TRUE:

В операции BETWEEN (между) операции <= и >= объединяются в одном выражении. Например, при­веденное ниже выражение возвращает ложное значение (FALSE):

100 BETWEEN 110 AND 120

а это выражение возвращает TRUE:

100 BETWEEN 90 AND 110

Результатом операции IN (в) будет истинное значение, когда первый операнд содержится в наборе, определяемом вторым операндом. Например, результат этого выражения — FALSE:

'Scott' IN ('Mike', 'Pamela', 'Fred')

Если в наборе содержатся NUI.L-значения, они игнорируются, так как при сравнении некоторого значения с NULL-значением всегда будет возвращаться NULL.

12. Управляющие структуры PL/SQL

12.1. IF-THEN-ELSE

Синтаксис оператора IF-THEN-ELSE (если-то-иначе):

где логическое_выражепие — любое выражение, результатом которого является логическое значение. Условия ELSIF и ELSE необязательны, причем условий ELSIF может быть сколь угодно много. Функционирование блока происходит в точности так, как это указано с помощью клю­чевых слов. Если первое условие истинно (FALSE или NULL считается ложным), то выполняется первая последовательность операторов, иначе проверяется следующее условие (после следующего ELSIF) или выполняются операторы из блока ELSE (если дошло до конца).

12.2. Циклы

В PL/SQL имеется возможность повторения операторов посредством циклов (loops). Циклы подразделя­ются на четыре категории. Простые циклы, циклы WHILE и циклы FOR, курсорные циклы FOR.

Простые циклы

Синтаксис простых циклов (основных циклов языка) таков:

LOOP

последовательность_операторов;

EXIT [WHEN условие ]; /*выход при условии */

IF условие THEN /*эквивалентный способ выхода*/

EXIT;

END IF;

END LOOP;

Последовательноетъ_опероторов будет выполняться бесконечно долго пока не выполнится хотя бы одно условие выхода или не встретит безусловный оператор ΕΧΙΤ.

Циклы WHILE

Синтаксис цикла WHILE (цикла с условием продолжения) таков:

WHILE условие LOOP

последовательность_опера торов;

END LOOP;

Проверка условия происходит перед каждой итерацией (шагом) цикла. Если условие истинно, выпол­няется последоватеяъность_операторов. Если же проверка условия дает ложное или NULL-значение, цикл за­вершается и управление программой передается оператору, следующему за оператором END LOOP. Для выхода можно использовать EXIT [THEN условие];

Числовые циклы FOR

В числовых же циклах FOR число итераций заранее определено. Синтаксис цикла FOR такой:

FOR счетчик_цикла IN [REVERSE] нижняя_граница.. верхняя_граница LOOP последовательность_операторов

END LOOP;

где счетчик_цикла — неявно создаваемая индексная переменная, нижняя_граница и верхняя_граница, а последовотельиость_оперпторов является содержимым цикла.

Границы цикла указываются один раз и определяют общее число итераций, проходимых счетчком_цикла от нижней_границы до верхней_граници. При этом счетчик каждый раз увеличивается на 1 до тех пор, пока цикл не завершится. Счетчик (индекс) цикла FOR неявно объявляется с типом BINARY_INTEGER. Объявлять его перед циклом необязательно. Если он все же объявлен, цикл скрывает это внешнее объявление так же, как объявление переменной во внутреннем блоке скрывает ее объявление во внешнем блоке. Если в цикле FOR указывается ключевое слово REVERSE (обратный поря­док), индекс цикля будет изменяться от верхней границы до нижней. Обратите внимание, что в этом случае синтаксис остался прежним — нижняя граница по-прежнему указывается первой. Верхняя и нижняя границы могут быть любыми выражениями, для которых возможно преобразование в числовые значе­ния.

Если никаких действий выполнять не нужно, можно использовать оператор NULL;.

12.3. Операторы GOTO и метки

Применяются операторы GOTO (перейти к). Синтаксис оператора GOTO таков:

GOTO метка

где мешка — это метка, определяемая в блоке PL/SQL. Метки заключаются в двойные угловые скобки.

При выполнении оператора GOTO управление программой сразу же передается оператору, на который указывает метка.

Нельзя переда­вать управление программой во внутренний блок, цикл или оператор IF. Кроме того, запрещается передавать управление программой из одной последовательности операто­ров условного оператора IF в другую. Нельзя передавать управление из обработчика исключительной ситуации обратно в теку­щий блок.

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

EXIT l_Outer;

Если цикл помечен, имя метки можно указывать после оператора END LOOP.

Прагмы (pragmas) — это директивы компилятора, которые служат в качестве инструкций компилятор, который выполняет прагму во время компи­ляции блока.

RESTRICT_RFFERENCES (ограничить ссылки) устанавливает ограниче­ния на способ использования SQL-операторов в некоторой функции.

13. Использование SQL в PL/SQL

Некоторые виды операторов.

■ С помощью операторов языка манипулирования данными (DML — data manipulation language) можно модифицировать и запрашивать информацию, содержащуюся в таблицах, однако нельзя изменять структуру таблиц и других объектов.

■ С помощью операторов управления транзакциями обеспечивается согласованность данных. Это достигается объединением SQL-операторов в логические транзакции, которые выполняются либо успешно, ибо неуспешно как единое целое.

Из всех SQL-операторов в программах PL/SQL можно использовать лишь операторы DML и операторы управления транзакциями. Оператор EXPLAIN PIAN (объяснить план) — хотя он и относится к категории DML — применять также не разрешается.

13.1. Select

С помощью оператора SELECT данные выбираются в базе данных и записываются в переменные PL/SQL.

Основная форма инструкция SELECT выглядит так:

SELECT список_столбцов

FROM слисок_таблиц

WHERE условия

GROUP BY список_столбцов

HAVING условия

ORDER BY слисок_столбцов;

Этот синтаксис требует, чтобы элементы в списках разделялись запятыми.

По умолчанию применяется сортировка (ORDER BY) по воз­растанию. Вы можете отсортировать результаты в убывающем порядке, используя ключевое слово DESC.

13.2. Связи баз данных

Если в системе установлено программное средство SQL*Net, то можно применять связи баз данных. Связь баз данных (database link) — это ссылка на удаленную базу данных, которая может быть размещена в системе, не имеющей ничего общего с локальной базой данных. Связь баз данных создается с помо­щью следующего оператора DDL:

CREATE DATABASELINK имя_связи

CONNECTTO имя_пользователя IDENTIFIED BY пароль

USING sqlnet_строка;

Синтаксис для имени связи баз данных — имя_сеязи — подчиняется обычным правилам, установлен­ным для идентификаторов баз данных. Элементы имя_пользователя и пароль идентифицируют схему уда­ленной базы данных, a sqlnet_cmpокa является корректной последовательностью символов, применяющейся для соединения с удаленной базой данных.

13.3. Ссылки на таблицы

Во всех операциях DML производятся ссылки на таблицы. Общий вид ссылки на таблицу можно пред­ставить следующим образом:

[схема. ]таблица[@связь_баз_данных]

где схема идентифицирует владельца таблицы, а связь__баз_данных — таблицу удаленной базы данных. Схему указывать не обязательно если вы работаете со своей таблицей.

14. Курсоры и курсорные переменные

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

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

Курсоры бывают явные (explicit) (Имя курсора явно при­своено оператору SELECT при помощи оператора CURSOR … IS). Для всех других SQL-операторов применя­ются неявные (implicit) курсоры. Обработка явного курсора выполняется в четыре этапа. Обработка неявного курсора осуществляется в PL/SQL автоматически. Каждый оператор SELECT выполняется в пределах контекстной области и поэтому имеет курсор, указывающий на конкретную контекстную об­ласть. Такой курсор называется SQL-курсором. PL/SQL неявно открывает SQL-курсор, обрабатывает SQL-оператор и впоследствии закрывает этот курсор. Неявные курсоры используются для обработки операторов INSERT, UPDATE, DELETE, а также од­нострочных операторов SELECT..INTO. SQL-курсор открывается и закрывается PL/SQL, поэтому команды OPEN, FETCH и CLOSE не нужны. Однако для SQL-курсоров можно применять курсорные ат­рибуты, например: SQL%ROWCOUNT вернет 1 если последним был оператор UPDATE, который успешно изменил значение таблицы.

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

TYPE имя_типа IS REF CURSOR [RETURN возвращаемый_тип];

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

Типом, возвращаемым курсорной переменной, должен быть тип записи. Запись может быть объявлена явно как запись, определяемая пользователем, или неявно, при помощи %ROWTYPE.

Затем можно объявить переменную нового типа. Если RETURN не указано, то переменная не имеет ограничение по типу, возвращаемых запросом записей (неограниченная), иначе ее называют ограниченной. Курсорные переменные используются аналогично курсорам, с тем отличием, что используется имя конкретной переменной в том месте, где д.б. использовано имя курсора.

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

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

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

CURSOR имя_курсора IS оператор_SELECT

… [FOR UPDATE [OF имя_столбца]] [NOT WAIT]

где имя_курсора — это имя курсора (идентификатор), a onepamop__SELECT — запрос, который будет обрабатываться. Если указано FOR UPDATE, то курсор блокирует столбцы для изменения в таблице, что дает возможность их менять присваиванием. Столбцы для изменения можно перечислить либо в конструкции OF имя_столбца через запятую, либо после SELECT, как это обычно делается при выборе столбцов. Если указанные столбцы заняты, то при открытии курсор будет ждать бесконечно долго их закрытия, но можно указать NOT WAIT тогда произойдет исключение. Если курсор объявлен с конструкцией FOR UPDATE, в операторе UPDATE или SELECT может быть ука­зана конструкция WHERE CURRENT OF. Синтаксис конструкции WHERECURRENT OF таков:

WHERE CURRENT OF курсор

где курсор — это имя курсора, объявленного с конструкцией FOR UPDATE. Конструкция WHERE CURRENT OF определяет строку, только что считанную курсором, тем самым предоставляя удобный способ выбора именно той строкой, которая, например, обрабатывается на данном проходе цикла.

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

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

Оператор COMMIT снимает все блокировки, удерживаемые сеансом, и заносит изменения в БД. Когда это происходит, действие курсора FOR UPDATE прекращается и любая последующая попытка считать строки приводит к ошибке. Если требуется применять изменения сразу, то следует использовать обычный курсор (т.к. на обычный курсоры COMMIT не влияет), но вместо WHERE CURRENT OF придется определять нужную строку по каким-либо другим признакам.

Запрос, связанный с курсорной переменной в операторе OPEN..FOR, не может иметь тип FOR UPDATE.

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

Курсор может быть параметризованным (parameterized) т.е., подобно процедурам, принимать определенные аргументы. Пример:

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

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

OPEN имя_курсора;

Для параметризованного курсора:

OPEN имя_курсора (значения_аргументов_через_запятую);

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

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

■ На основе значений переменных привязки определяется активный набор.

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

Можно открыть уже открытый курсор. Перед вторым открытием PL/SQL неявно выполняет опера­тор CLOSE. Кроме того, в любой момент может быть открыто несколько курсоров.

Для курсорных переменных используется следующий синтаксис открытия:

OPEN курсорная_переменная FOR оператор_выбора;

где курсорная_переменная — это ранее объявленная курсорная переменная, a оператор_выбора — требуемый запрос. Если курсорная переменная ограничена, список выбора должен соответствовать типу, возвраща­емом)у курсором. Память под курсорную переменную выделяется автоматически.

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

Частью оператора FETCH является список INTO. Оператор FETCH имеет две формы:

FETCH имя_курсора INTO список_первменных;

FETCH имя_курсора INTO запись_PL/SQL;

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

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

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

Синтаксис закрытиякурсора таков: CLOSE имя_курсора;, а закрытия курсорной переменной CLOSE имя_переменной.

14.1. Курсорные атрибуты

В PL/SQL существуют четыре атрибута, которые могут быть применены к курсорам. Курсорные атрибу­ты добавляются к имени курсора в блоке PL/SQL. Эти атрибуты: %FOUND, %NOTFOUND, %ISOPEN и %ROWCOUNT.

%FOUND- это логический атрибут. Он возвращает TRUE, если при предшествующем считывании была выбранастрока, и FALSE — если строка выбрана не была.

%NOTFOUNDведет себя противоположно %FOUND: если предшествующее считывание возвращает строку — значение %NOTFOUND ложно. Атрибут %NOTFOUND возвращает TRUE, только если во вре­мя предшествующего считывания строка выбрана не была. Этот атрибут часто используется в качестве условия выхода из цикла выборки.

%ISOPEN Этот логический атрибут используется для курсор. Если открыт, то %FSOPEN возвращает TRUE.

%ROWCOUNT Этот числовой атрибут возвращает число строк, считанных курсором на данный мо­мент, а не общее количество строк которые выбрал SELECT.

14.2. Циклы выборки

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

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

FOR итератор IN курсор LOOP

/*операторы*/

END LOOP;

Здесь итератор играет роль счетчика цикла и одновременно содержит в себе строчку, считанную курсором на данном раунде. Считывание новой строчки происходит автоматически перед началом каждого раунда. Выход так же автоматический, как только %NOTFOUND окажется равным TRUE (неявно) перед очередным считыванием.

Дата добавления: 2015-08-29