ГЛАВА 4
----------------------------------------------------------------
ВЗАИМОДЕЙСТВИЕ С ORACLE
Есть два сорта знания. Мы знаем предмет сами,
или мы знаем, где найти информацию о нем.
Сэмюел Джонсон
Эта глава помогает вам использовать мощь ORACLE. Вы узнаете,
как PL/SQL поддерживает команды, функции и операторы SQL, с
помощью которых ваша программа манипулирует данными ORACLE. Вы
узнаете также, как обращаться к удаленным базам данных,
управлять курсорами, использовать советы оптимизатору,
обрабатывать транзакции и поддерживать целостность вашей базы
данных.
Взаимодействие с ORACLE 4-1
----------------
Поддержка SQL
За счет расширения языка SQL, PL/SQL предлагает уникальную
комбинацию мощи и простоты использования. Вы можете гибко и
безопасно манипулировать данными ORACLE, потому что PL/SQL
поддерживает все команды манипулирования данными (за исключением
EXPLAIN PLAN), команды управления транзакциями, функции,
псевдостолбцы и операторы SQL. Однако PL/SQL НЕ поддерживает
команд определения данных, таких как CREATE, команд управления
сессией, таких как SET ROLE, и команду управления системой ALTER
SYSTEM.
Манипулирование данными
-----------------------
Для манипулирования данными ORACLE вы используете команды
INSERT, UPDATE, DELETE, SELECT и LOCK TABLE. INSERT вставляет
новые строки данных в таблицы базы данных; UPDATE модифицирует
строки; DELETE удаляет ненужные строки; SELECT выбирает строки,
удовлетворяющие вашим поисковым условиям; LOCK TABLE временно
ограничивает доступ к таблице.
Управление транзакциями
-----------------------
ORACLE ориентирован на транзакции; иными словами, ORACLE
использует транзакции, чтобы обеспечивать целостностьь данных.
ТРАНЗАКЦИЯ - это ряд предложений манипулирования данными SQL,
которые выполняют логическую единицу работы. Например, два
предложения UPDATE могли бы кредитовать один банковский счет и
дебитовать другой. В один момент времени ORACLE либо делает
постоянными, либо отменяет все изменения в базе данных,
осуществленные транзакцией. Если ваша программа сбивается в
середине транзакции, ORACLE обнаруживает ошибку и выполняет
отмену (откат) транзакции. Следовательно, база данных
автоматически возвращается в свое прошлое состояние.
Для управления транзакциями используются команды COMMIT,
ROLLBACK, SAVEPOINT и SET TRANSACTION. COMMIT делает
постоянными все изменения в базе данных, сделанные в течение
текущей транзакции ("подтверждает" транзакцию). До тех пор,
пока вы не подтвердите свои изменения, другие пользователи не
могут их увидеть. ROLLBACK заканчивает текущую транзакцию и
отменяет все изменения, сделанные с момента ее начала.
SAVEPOINT отмечает текущую точку в обработке транзакции.
Совместно с ROLLBACK, команда SAVEPOINT позволяет отменить часть
транзакции. SET TRANSACTION устанавливает режим транзакции
"только чтение". Для дополнительной информации смотрите раздел
"Обработка транзакций" ниже в этой главе.
4-2 Руководство пользователя и справочник по PL/SQL
Функции SQL
-----------
PL/SQL позволяет вам использовать все функции SQL, за
исключением следующих групповых функций, которые суммируют целые
столбцы данных ORACLE:
* AVG
* COUNT
* MAX
* MIN
* STDDEV
* SUM
* VARIANCE
Еще две групповых функции, GLB и LUB, доступны лишь в Trusted
ORACLE. Эти функции возвращают соответственно наибольшую нижнюю
границу и наименьшую верхнюю границу меток операционной системы.
Для дополнительной информации обратитесь к документу Trusted
ORACLE7 Server Administrator's Guide.
Вы можете использовать групповые функции в предложениях SQL, но
НЕ в процедурных предложениях. Групповые функции оперируют на
целых столбцах, если вы не используете предложение SELECT GROUP
BY, чтобы отсортировать возвращаемые строки по подгруппам. Если
вы опускаете фразу GROUP BY, групповая функция трактует все
возвращаемые строки как единственную группу.
Вы вызываете групповую функцию, используя синтаксис
имя_функции([ALL | DISTINCT] выражение)
где выражение возвращает один или несколько столбцов базы
данных. Если вы специфицируете опцию ALL (умолчание), то
групповая функция учитывает все значения столбца, включая
повторяющиеся значения. Например, следующее предложение
возвращает стандартное отклонение по всем значениям столбца
comm:
SELECT STDDEV(comm) INTO comm_sigma FROM emp;
Если вы специфицируете опцию DISTINCT, то групповая функция
учитывает только различающиеся значения. Например, следующее
предложение возвращает число различных наименований работ в
таблице emp:
SELECT COUNT(DISTINCT job) INTO job_count FROM emp;
Функция COUNT позволяет специфицровать звездочку (*), что
возвращает число строк в таблице. Например, следующее
предложение возвращает число сотрудников в таблице emp:
SELECT COUNT(*) INTO emp_count FROM emp;
За исключением COUNT(*), все групповые функции игнорируют пустые
значения.
Взаимодействие с ORACLE 4-3
Псевдостолбцы SQL
-----------------
SQL распознает следующие псевдостолбцы SQL, которые возвращают
специфические элементы данных:
* CURRVAL
* LEVEL
* NEXTVAL
* ROWID
* ROWNUM
Например, NEXTVAL возвращает очередное значение
последовательности базы данных. Поскольку эти столбцы не
являются действительными столбцами в какой-нибудь таблице, они
называюся ПСЕВДОСТОЛБЦАМИ. Однако псевдостолбцы должны
извлекаться из таблицы, ибо они трактуются как столбцы.
Например, вы можете выбирать значение из псевдостолбца. Однако
нельзя применять операции INSERT, UPDATE и DELETE по отношению к
псевдостолбцам.
Вы можете использовать псевдостолбцы в предложениях SQL, но НЕ в
процедурных предложениях. В следующем примере
последовательность базы данных empno_seq и псевдостолбец NEXTVAL
используются для вставки нового номера сотрудника в таблицу emp:
INSERT INTO emp VALUES (empno_seq.NEXTVAL, new_ename, ...);
Иногда бывает удобно извлекать псевдостолбцы из фиктивной
таблицы, содержащей одну строку и один столбец, например:
DECLARE
new_empno NUMBER(4);
new_ename CHAR(10);
...
BEGIN
...
SELECT empno_seq.NEXTVAL INTO new_empno FROM dual;
INSERT INTO emp VALUES (new_empno, new_ename, ...);
END;
Ниже приведены краткие описания псевдостолбцов. Для
дополнительной информации обратитесь к документу ORACLE7 Server
SQL Language Reference Manual.
4-4 Руководство пользователя и справочник по PL/SQL
CURRVAL и NEXTVAL
ПОСЛЕДОВАТЕЛЬНОСТЬ (sequence) - это объект базы данных, который
генерирует порядковые номера. При создании последовательности
вы можете специфицировать ее начальное значение и приращение.
CURRVAL возвращает текущее значение указанной
последовательности. Прежде чем обратиться к CURRVAL в данной
сессии, вы должны хотя бы раз обратиться к NEXTVAL, чтобы
сгенерировать номер. Обращение к NEXTVAL записывает текущий
порядковый номер в CURRVAL. NEXTVAL выполняет приращение и
возвращает очередное значение. Чтобы получить текущее или
очередное значение последовательности, вы должны использовать
квалифицированную ссылку:
имя_последовательности.CURRVAL
имя_последовательности.NEXTVAL
После создания последовательности вы можете использовать ее для
генерации уникальных порядковых номеров для обработки ваших
транзакций. Однако вы можете использовать CURRVAL и NEXTVAL
только в списке SELECT, фразе VALUES и фразе SET. В следующем
примере последовательность используется для того, чтобы вставить
один и тот же номер сотрудника в две таблицы:
INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);
INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);
При использовании в транзакции, приращение последовательности
выполняется немедленно, независимо от того, выполнится ли
транзакция успешно или будет отменена.
LEVEL
Вы используете LEVEL в предложении SELECT CONNECT BY, чтобы
организовать строки таблицы базы данных в иерархическую
структуру. LEVEL возвращает номер уровня узла в этой структуре.
Корень дерева имеет уровень 1, его дети - уровень 2, и т.д.
Вы указываете направление обхода дерева (вниз от корня или вверх
от ветвей) посредством оператора PRIOR. В фразе START WITH
специфицируется условие, которое идентифицирует корень дерева.
ROWID
ROWID возвращает идентификатор строки (двоичный адрес строки) в
таблице базы данных. Вспомните, что PL/SQL предоставляет тип
данных с таким же названием ROWID. Вы можете использовать
переменные типа ROWID для хранения идентификаторов строк в
читабельном формате. Например, вы могли бы объявить для этой
цели переменную с именем row_id:
DECLARE
row_id ROWID;
...
Взаимодействие с ORACLE 4-5
Выбирая или извлекая идентификатор строки в переменную типа
ROWID, вы можете использовать функцию ROWIDTOCHAR, которая
преобразует двоичное значение в 18-байтовую символьную строку.
Затем, вы можете сравнивать переменную ROWID с псевдостолбцом
ROWID в фразе WHERE предложения UPDATE или DELETE, чтобы
идентифицировать последнюю строку, извлеченную из курсора.
Более того, вы можете использовать атрибут %ROWTYPE с курсорами,
ссылающимися на псевдостолбец ROWID. Для примеров обратитесь к
разделу "Извлечения между commit'ами" ниже в этой главе.
ROWNUM
ROWNUM возвращает порядковый номер строки согласно тому порядку,
в котором строки выбираются из таблицы. Первая выбранная строка
имеет ROWNUM 1, вторая - 2, и т.д. Если строки выбираются
предложением SELECT, содержащим фразу ORDER BY, то значения
ROWNUM назначаются строкам ДО ТОГО, как они будут отсортированы.
Вы можете использовать ROWNUM в предложении UPDATE, чтобы
присваивать уникальное значение каждой строке в таблице. Вы
также можете использовать ROWNUM в фразе WHERE предложения
SELECT, чтобы лимитировать число извлекаемых строк, например:
DECLARE
CURSOR c1 IS SELECT empno, sal FROM emp
WHERE sal > 2000 AND ROWNUM < 10; -- возвратит 10 строк
...
Значение ROWNUM возрастает лишь при извлечении строки, так что
единственное осмысленное использование ROWNUM в фразе WHERE -
это условие вида
... WHERE ROWNUM < константа;
Например, следующее условие не может быть удовлетворено, так как
первые девять строк никогда не будут извлечены:
... WHERE ROWNUM = 10;
Столбец ROWLABEL
----------------
PL/SQL также распознает специальный столбец ROWLABEL, который
Trusted ORACLE создает для каждой таблицы базы данных. Как и к
прочим столбцам, к столбцу ROWLABEL можно обращаться в
предложениях SQL. Однако в стандартном ORACLE этот столбец
всегда возвращает NULL. В Trusted ORACLE столбец ROWLABEL
возвращает метку операционной системы для строки.
Типичное использование ROWLABEL - это фильтрация результатов
запроса. Например, следующее предложение подсчитывает лишь те
строки, у которых уровень секретности выше, чем "unclassified":
SELECT COUNT(*) INTO head_count FROM emp
WHERE ROWLABEL > 'UNCLASSIFIED';
4-6 Руководство пользователя и справочник по PL/SQL
Операторы SQL
-------------
PL/SQL позволяет вам использовать в предложениях SQL все
операторы SQL сравнений, множеств и строк. В этом подразделе
кратко описаны некоторые из таких операторов. Для
дополнительной информации обратитесь к документу ORACLE7 Server
SQL Language Reference Manual.
Операторы сравнения
Как правило, вы используете операторы сравнения в фразе WHERE
предложения манипулирования данными для формирования ПРЕДИКАТОВ,
которые сравнивают одно выражение с другим и всегда возвращают
TRUE, FALSE или NULL. Для формирования предикатов можно
использовать все операторы сравнения, перечисленные ниже. Более
того, вы можете комбинировать предикаты, используя логические
операторы AND, OR и NOT.
ALL Сравнивает значение с каждым значением в списке
или в множестве, возвращаемом подзапросом, и
возвращает TRUE, если все индивидуальные
сравнения дают результат TRUE.
ANY,SOME Сравнивает значение с каждым значением в списке
или в множестве, возвращаемом подзапросом, и
возвращает TRUE, если хотя бы одно из
индивидуальных сравнений дает результат TRUE.
BETWEEN Проверяет, лежит ли значение в интервале.
EXISTS Возвращает TRUE, если подзапрос возвращает хотя
бы одну строку.
IN Проверяет на членство в множестве.
IS NULL Проверяет на пустоту.
LIKE Проверяет, удовлетворяет ли символьная строка
заданному образцу, который может содержать
поисковые символы.
Операторы множеств
Операторы множеств комбинируют результаты двух запросов в один
результат. Вы можете использовать все операторы множеств,
включая INTERSECT, MINUS, UNION и UNION ALL.
INTERSECT возвращает все различающиеся строки, возвращенные
обоими запросами. MINUS возвращает все различающиеся строки,
возвращенные первым, но не вторым запросом. UNION возвращает
все различающиеся строки, возвращенные любым из запросов. UNION
ALL возвращает все строки, возвращенные любым из запросов, в том
числе все повторяющиеся строки.
Операторы строк
Операторы строк возвращают конкретные строки или обращаются к
ним. Вы можете использовать все операторы строк, включая ALL,
DISTINCT и PRIOR.
ALL возвращает повторяющиеся строки в результате запроса или в
агрегатном выражении. DISTINCT устраняет повторяющиеся строки в
результате запроса или в агрегатном выражении. PRIOR ссылается
на родительскую строку текущей строки, возвращенной
иерархическим запросом. Вы должны использовать этот оператор в
фразе CONNECT BY такого запроса, чтобы определить отношение
подчиненности в дереве.
Взаимодействие с ORACLE 4-7
Советы оптимизатору
-------------------
Для каждого предложения SQL оптимизатор ORACLE генерирует ПЛАН
ИСПОЛНЕНИЯ, представляющий собой ряд шагов, которые ORACLE
предпринимает для выполнения этого предложения. В некоторых
случаях вы можете предложить ORACLE нужный способ оптимизации
предложения SQL. Эти рекомендации, называемые СОВЕТАМИ (hints),
позволяют вам влиять на решения, принимаемые оптимизатором.
Советы не являются директивами, они просто помогают оптимизатору
делать его работу. Некоторые советы ограничивают сферу
информации, которая должна использоваться для оптимизации
предложения SQL, тогда как другие советы предлагают полные
стратегии.
Вы можете использовать советы, чтобы специфицировать
* подход к оптимизации предложения SQL
* путь доступа для каждой адресуемой таблицы
* порядок соединения для соединений
* метод, используемый для соединения таблиц
Соответственно, советы распадаются на следующие категории:
* Подход к оптимизации
* Путь доступа
* Порядок соединения
* Способ соединения
Например, два возможных совета "подход к оптимизации", COST и
NOCOST, вызывают соответственно стоимостной оптимизатор и
регулярный оптимизатор.
Вы даете советы оптимизатору, помещая их в форме комментария
непосредственно вслед за глаголом предложения SELECT, UPDATE или
DELETE. Например, в следующем предложении рекомендуется
использовать стоимостной подход к оптимизации:
SELECT /*+ COST */ ename, job, sal INTO ...
Для дополнительной информации о советах оптимизатору обратитесь
к документу ORACLE7 Server Applicaton Developer's Guide.
4-8 Руководство пользователя и справочник по PL/SQL
Поддержка национальных языков
-----------------------------
Хотя широко используемые 7- или 8-битовые наборы символов ASCII
и EBCDIC достаточны для представления латинского алфавита,
некоторые азиатские языки, такие как японский, содержат тысячи
символов. Эти языки требуют 16 бит (два байта) для
представления каждого символа. Как ORACLE справляется с такими
нетипичными языками?
ORACLE предоставляет поддержку национальных языков (NLS),
которая позволяет вам обрабатывать данные в однобайтовых и
мультибайтовых символах, а также преобразовывать данные из
одного набора символов в другой. NLS также позволяет вашим
приложениям выполняться в различных языковых окружениях.
Благодаря NLS форматы чисел и дат автоматически адаптируются к
языковым соглашениям, специфицированным для сессии пользователя.
Таким образом, NLS позволяет пользователям во всем мире
взаимодействовать с ORACLE на их родных языках.
Вы управляете действием средств, зависящих от языка,
специфицируя разнообразных параметров NLS. Умалчиваемые значения
этих параметров могут быть установлены в файле инициализации
ORACLE. Следующая таблица показывает назначение каждого
параметра NLS.
Параметр NLS Специфицирует...
----------------------------------------------------------------
NLS_LANGUAGE соглашения, зависящие от языка
NLS_TERRITORY соглашения, зависящие от территории
NLS_DATE_FORMAT формат даты
NLS_DATE_LANGUAGE язык для названий дней и месяцев
NLS_NUMERIC_CHARACTERS десятичный и групповой разделители
NLS_CURRENSY локальное обозначение валюты
NLS_ISO_CURRENCY обозначение валюты в стандарте ISO
NLS_SORT последовательность сортировки
Основными параметрами являются NLS_LANGUAGE и NLS_TERRITORY.
NLS_LANGUAGE специфицирует умалчиваемые значения для свойств,
зависящих от языка, к которым относятся
* язык для сообщений сервера ORACLE
* язык для названий дней и месяцев
* последовательность сортировки
Взаимодействие с ORACLE 4-9
NLS_LANGUAGE специфицирует умалчиваемые значения для свойств,
зависящих от территории, к которым относятся
* формат даты
* десятичный разделитель
* групповой разделитель
* местный символ валюты
* символ валюты в стандарте ISO
Вы можете управлять выбором зависящих от языка свойств NLS для
сессии пользователя, специфицируя параметр NLS_LANG:
NLS_LANG = <язык>_<территория>.<набор символов>
где "язык" специфицирует значение NLS_LANGUAGE для сессии
пользователя, "территория" специфицирует значение NLS_TERRITORY,
а "набор символов" задает кодовое множество, используемое данным
терминалом. Кодовое множество (или набор символов, или кодовая
страница) - это совокупность числовых кодов, соответствующих
множеству символов, которые может отображать терминал. В это
множество входят также символы для управления терминалом.
Вы определяете параметр NLS_LANG через переменную окружения (или
ее эквивалент на вашей системе). Например, в системе UNIX с
интерпретатором команд C shell, вы могли бы определить NLS_LANG
следующим образом:
setenv NLS_LANG French_France.WE8ISO8859P1
PL/SQL полностью поддерживает все средства NLS, что позволяет
вашим приложениям обрабатывать многоязычные данные, хранящиеся в
вашей базе данных ORACLE. Например, вы можете объявлять
символьные переменные для иностранных языков и передавать их
строковым функциям, таким как INSTRB, LENGTHB и SUBSTRB. Эти
функции имеют тот же самый синтаксис, что и функции INSTR,
LENGTH и SUBSTR соответственно, но оперируют побайтно, а не
посимвольно.
Вы можете использовать функции NLS_INITCAP, NLS_LOWER и
NLS_UPPER, чтобы обрабатывать специальные случаи преобразования
регистра букв. Кроме того, вы можете использовать функцию
NLSSORT, чтобы специфицировать в фразе WHERE сравнения,
основывающиеся на лингвистическом, а не на двоичном,
упорядочении. Вы можете даже передавать параметры NLS функциям
TO_CHAR, TO_DATE и TO_NUMBER. Для дополнительной информации о
NLS обратитесь к документу ORACLE7 Server Application
Developer's Guide.
4-10 Руководство пользователя и справочник по PL/SQL
----------------
Удаленный доступ
PL/SQL позволяет вам обращаться к удаленным базам данных. В
следующем примере вы выполняете запрос через SQL*Net по таблице,
находящейся в удаленной базе данных newyork. Этот запрос
предоставляется вашему локальному серверу ORACLE, но
"проталкивается" для выполнения в удаленную базу данных.
BEGIN
SELECT ename, job INTO my_ename, my_job
FROM emp@newyork
WHERE empno = my_empno;
...
Для более подробной информации о доступе к удаленным базам
данных обратитесь к документу ORACLE7 Server Application
Developer's Guide.
Вы можете использовать атрибут %TYPE для получения типа данных
столбца в удаленной базе данных. Аналогично, можно использовать
атрибут %ROWTYPE для объявления записи, которая будет
представлять строку в удаленной таблице. Например:
DECLARE
emp_id emp.empno@newyork%TYPE;
dept_rec dept@newyork%ROWTYPE;
...
Вы даже можете вызывать как независимые, так и пакетированные
подпрограммы, хранящиеся в удаленной базе данных ORACLE. Для
дополнительной информации см. раздел "Удаленный доступ" в главе
6.
Прозрачность местоположения
---------------------------
Чтобы обеспечить прозрачность адресации объектов удаленных баз
данных, таких как таблицы и обзоры, вы можете создавать
синонимы, как показывает следующий пример:
CREATE SYNONYM emp2 FOR emp@newyork;
Затем, как показано в следующем примере, вы можете ссылаться на
удаленный объект через его синоним:
BEGIN
SELECT ename, job INTO my_ename, my_job
FROM emp2 -- синоним для emp@newyork
WHERE empno = my_empno;
...
Поскольку пакеты и независимые подпрограммы являются объектами
базы данных, для них также могут быть созданы синонимы. Нельзя,
однако, создавать синонимы для объектов, объявленных внутри
пакетов или подпрограмм. Например, вы не можете создать синоним
для пакетированной процедуры.
Взаимодействие с ORACLE 4-11
Глобальные имена
----------------
РАСПРЕДЕЛЕННАЯ БАЗА ДАННЫХ - это единая логическая база данных,
состоящая из нескольких физических баз данных, расположенных на
разных узлах. В системе распределенной базы данных каждая из
баз данных уникально идентифицируется ГЛОБАЛЬНЫМ ИМЕНЕМ,
состоящим из двух компонент. Первая компонента - это имя базы
данных длиной не более 8 символов (например, sales); вторая
компонента - это сетевое имя домена. Рис.4-1 изображает
воображаемый сетевой домен, удовлетворяющий соглашениям
Internet. Internet - это всемирная система компьютерных сетей,
используемых компаниями и учебными заведениями для обмена
информацией.
Рис.4-1
Сетевой домен
COM
¦
------------------+-----------------¬
¦
PRIMUS
¦
--------------------+------------------------------¬
¦ ¦ ¦
ASIA AMERICAS EUROPE
¦ ¦ ¦
--------+-¬ ----+--------------¬ -----+-----¬
¦ ¦ ¦ ¦ ¦
JAPAN US MEXICO FRANCE GERMANY
¦ ¦ ¦ ¦ ¦
¦ ---+-------¬ ¦ ¦ ¦
¦ ¦ ¦ ¦ ¦ ¦
-----+---¬ -----+---¬ -----+---¬ -----+---¬ -----+---¬ -----+---¬
¦ MFG ¦ ¦ SALES ¦ ¦ MFG ¦ ¦ SALES ¦ ¦ SALES ¦ ¦ SALES ¦
+--------+ +--------+ +--------+ +--------+ +--------+ +--------+
¦ База ¦ ¦ База ¦ ¦ База ¦ ¦ База ¦ ¦ База ¦ ¦ База ¦
¦ данных ¦ ¦ данных ¦ ¦ данных ¦ ¦ данных ¦ ¦ данных ¦ ¦ данных ¦
¦ ORACLE ¦ ¦ ORACLE ¦ ¦ ORACLE ¦ ¦ ORACLE ¦ ¦ ORACLE ¦ ¦ ORACLE ¦
L--------- L--------- L--------- L--------- L--------- L---------
Для формирования сетевых имен домена вы используете
последовательное квалифицирование и продвигаетесь по иерархии от
листа к корню дерева. Имя в домене сообщает вам название или
местоположение сетевого хоста и тип его организации. В
следующем примере хвостовой домен com говорит вам, что Primus -
это компания или иная коммерческая организация:
sales.mexico.americas.primus.com
Каждый объект базы данных уникально идентифицируется своим
ГЛОБАЛЬНЫМ ИМЕНЕМ ОБЪЕКТА. В следующем примере вы осуществляете
запрос по удаленной таблице emp:
BEGIN
SELECT ename, job INTO my_ename, my_job
FROM emp@sales.france.europe.primus.com
WHERE empno = my_empno;
...
4-12 Руководство пользователя и справочник по PL/SQL
----------------
Управление курсорами
Вспомните из главы 1, что PL/SQL использует два типа курсоров:
неявные и явные. PL/SQL неявно объявляет курсор для каждого
предложения манипулирования данными SQL, в том числе для
запросов, возвращающих ровно одну строку. Однако, для запросов,
возвращающих больше одной строки, вы должны объявить явный
курсор или использовать курсорный цикл FOR.
Явные курсоры
-------------
Множество строк, возвращаемых запросом (активное множество),
может состоять из нуля, одной или нескольких строк, в
зависимости от того, сколько строк удовлетворяют вашим поисковым
условиям. Когда запрос возвращает несколько строк, вы можете
явно определить курсор для обработки этих строк.
Вы определяете курсор в декларативной части блока PL/SQL,
подпрограммы или пакета путем задания его имени и
специфицирования запроса. После этого вы манипулируете курсором
при помощи трех команд: OPEN, FETCH и CLOSE.
Прежде всего вы инициализируете курсор предложением OPEN,
которое идентифицирует активное множество. Затем с помощью
предложения FETCH вы извлекаете первую строку. Вы можете
повторять FETCH неоднократно, пока не будут извлечены все
строки. После обработки последней строки вы освобождаете курсор
предложением CLOSE.
Вы можете обрабатывать параллельно несколько запросов, объявив и
открыв несколько курсоров. Теперь рассмотрим описанные шаги
более внимательно.
Объявление курсора
Ссылки вперед недопустимы в PL/SQL. Поэтому вы должны объявить
курсор, прежде чем сможете ссылаться на него в других
предложениях. Объявляя курсор, вы даете ему имя и ассоциируете
его с конкретным запросом. В следующем примере объявляется
курсор с именем c1:
DECLARE
CURSOR c1 IS SELECT ename, deptno FROM emp
WHERE sal > 2000;
...
BEGIN
...
Имя курсора - это необъявленный ранее идентификатор, а не
переменная PL/SQL; его можно использовать только для обращения к
запросу. Вы не можете присваивать значений имени курсора или
использовать его в выражениях. Однако, имена курсоров
подчиняются тем же правилам сферы, что и имена переменных.
Взаимодействие с ORACLE 4-13
Параметризованные курсоры
Курсоры могут принимать параметры, как показывает следующий
пример. Параметр курсора может появляться в запросе всюду, где
допускается появление константы.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHER sal > median;
Для объявления формальных параметров курсора используется
синтаксис
CURSOR имя [ (параметр [, параметр, ...]) ] IS
SELECT ...
где "параметр", в свою очередь, имеет следующий синтаксис:
имя_переменной [IN] тип_данных [{:= | DEFAULT} значение]
Формальные параметры курсора должны иметь моду IN. Для
обсуждения мод параметров обратитесь к разделу "Моды параметров"
в главе 6.
Как показывает следующий пример, вы можете инициализировать
параметры курсора умалчиваемыми значениями. Таким способом вы
можете передавать курсору различное число фактических
параметров, принимая или перекрывая умалчиваемые значения по
своему желанию. Более того, вы можете добавлять в курсор новые
формальные параметры без необходимости отыскивать и исправлять
все обращения к курсору в тексте программы.
DECLARE
CURSOR c1
(low INTEGER DEFAULT 0,
high INTEGER DEFAULT 99) IS SELECT ...
...
Сфера параметров курсора локальна в этом курсоре, что означает,
что к этим параметрам можно обращаться лишь в запросе, который
участвует в объявлении курсора. Значения параметров курсора
используются ассоциированным запросом в момент открытия курсора.
4-14 Руководство пользователя и справочник по PL/SQL
Открытие курсора
Открытие курсора предложением OPEN исполняет предложение SELECT
и идентифицирует АКТИВНОЕ МНОЖЕСТВО, т.е. все строки,
удовлетворяющие поисковым условиям запроса. Для курсоров,
объявленных с фразой FOR UPDATE, предложение OPEN также
осуществляет блокировку этих строк. Пример предложения OPEN:
OPEN c1;
Предложение OPEN не извлекает строк активного множества. Для
этого используется предложение FETCH.
Передача параметров
Курсору могут быть переданы параметры при открытии. Например,
при объявлении курсора
CURSOR c1 (my_ename CHAR, my_comm NUMBER) IS SELECT ...
любое из следующих предложений открывает этот курсор:
OPEN c1('ATTLEY', 300);
OPEN c1(employee_name, 150);
OPEN c1('THURSTON', my_comm);
В последнем примере переменная, специфицированная в предложении
OPEN, имеет такое же имя, что и параметр в объявлении курсора.
Когда идентификатор my_comm используется в объявлении курсора,
он обозначает формальный параметр курсора. Когда этот же
идентификатор используется вне объявления курсора, он обозначает
переменную PL/SQL с этим именем. Однако, для ясности,
рекомендуется использовать уникальные идентификаторы.
Если вы не хотите принять умалчиваемые значения, каждому
формальному параметру в объявлении курсора следует сопоставить
соответствующий фактический параметр в предложении OPEN.
Формальные параметры, объявленные с умалчиваемым значением,
могут и не иметь соответствующих им фактических параметров. В
этом случае они просто принимают свое умалчиваемое значение во
время выполнения OPEN. Не забывайте, что формальные параметры
курсора должны быть параметрами IN, так что они не могут
возвращать значений фактическим параметрам.
Чтобы сопоставить фактические параметры в предложении OPEN
формальным параметрам в объявлении курсора, вы можете
использовать позиционную или именную нотацию. (См. раздел
"Позиционная и именная нотация" в главе 6.) Каждый фактический
параметр должен иметь тип данных, совместимый с типом данных
соответствующего формального параметра.
Взаимодействие с ORACLE 4-15
Извлечение данных из курсора
Предложение FETCH извлекает очередную строку из активного
множества. При каждом выполнении FETCH курсор продвигается к
следующей строке в активном множестве. Пример предложения
FETCH:
FETCH c1 INTO my_empno, my_ename, my_deptno;
Для каждого значения столбца, извлекаемого запросом,
ассоциированного с курсором, в списке INTO должна быть
соответствующая переменная, имеющая совместимый с этим столбцом
тип данных. Обычно вы используете не индивидуальные переменные,
а запись:
...
OPEN c1;
LOOP
FETCH c1 INTO my_record;
EXIT WHEN c1%NOTFOUND;
-- обработать извлеченные данные
END LOOP;
Любые переменные в фразе WHERE запроса, ассоциированного с
курсором, вычисляются лишь в момент открытия курсора. Как
показывает следующий пример, запрос может обращаться к
переменным PL/SQL внутри своей сферы:
DECLARE
my_sal emp.sal%TYPE;
my_job emp.job%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT factor*sal FROM emp WHERE job = my_job;
BEGIN
...
OPEN c1; -- здесь factor равен 2
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
...
factor := factor + 1; -- не окажет влияния на FETCH
END LOOP;
END;
В этом примере каждое извлекаемое значение sal будет умножаться
на 2, несмотря на то, что значение factor увеличивается после
каждой операции FETCH. Чтобы изменить активное множество или
значения переменных в запросе, вы должны закрыть и заново
открыть курсор с новыми значениями входных параметров.
4-16 Руководство пользователя и справочник по PL/SQL
Однако для каждой операции FETCH на одном и том же курсоре вы
можете использовать собственный список INTO. Каждая FETCH
извлекает строку и присваивает значения своим переменным INTO,
как показывает следующий пример:
DECLARE
CURSOR c1 IS SELECT ename FROM emp;
name1 emp.ename%TYPE;
name2 emp.ename%TYPE;
name3 emp.ename%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- извлекает первую строку
FETCH c1 INTO name2; -- извлекает вторую строку
FETCH c1 INTO name3; -- извлекает третью строку
...
CLOSE c1;
END;
Если вы выдаете FETCH, но в активном множестве больше нет строк,
то значения переменных в списке INTO не определены.
Закрытие курсора
Предложение CLOSE деактивирует курсор, и активное множество
становится неопределенным. Пример предложения CLOSE:
CLOSE c1;
После того, как курсор закрыт, вы можете снова открыть его.
Любая иная операция на закрытом курсоре возбуждает
предопределенное исключение INVALID_CURSOR, которое
соответствует ошибке ORACLE с кодом ORA-01001.
Взаимодействие с ORACLE 4-17
Атрибуты явного курсора
-----------------------
Каждый курсор, явно объявленный вами, имеет четыре атрибута:
%NOTFOUND, %FOUND, %ROWCOUNT и %ISOPEN. Атрибуты позволяют вам
получать полезную информацию о выполнении многострочного
запроса. Для обращения к атрибуту просто присоедините его имя к
имени курсора. Атрибуты явного курсора можно использовать в
процедурных предложениях, но не в предложениях SQL.
Использование %NOTFOUND
Когда курсор открыт, строки, удовлетворяющие ассоциированному
запросу, идентифицированы и образуют активное множество. Эти
строки извлекаются операцией FETCH по одной за раз. Если
последняя операция FETCH вернула строку, %NOTFOUND дает FALSE.
Если последняя операция FETCH не смогла вернуть строку (так как
активное множество исчерпано), %NOTFOUND дает TRUE. Операция
FETCH должна в конце концов исчерпать активное множество, так
что, когда это происходит, никакого исключения не возбуждается.
В следующем примере вы используете %NOTFOUND, чтобы выйти из
цикла, когда FETCH не сможет вернуть строку:
LOOP
FETCH c1 INTO my_ename, my_deptno;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
Перед первой операцией FETCH атрибут %NOTFOUND дает NULL.
Поэтому, если FETCH ни разу не выполнится успешно, вы никогда не
выйдете из этого цикла. Причина в том, что предложение EXIT
WHEN выполняется только в том случае, когда условие WHEN дает
TRUE. Поэтому для безопасности вы можете предпочесть такой
вариант предложения EXIT:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
Вы можете открыть несколько курсоров, а затем использовать
%NOTFOUND, чтобы проверять, в каких курсорах еще есть строки.
Если курсор не открыт, обращение к нему через %NOTFOUND
возбуждает предопределенное исключение INVALID_CURSOR.
4-18 Руководство пользователя и справочник по PL/SQL
Использование %FOUND
%FOUND логически противоположен атрибуту %NOTFOUND. После
открытия явного курсора, но до первой операции FETCH, %FOUND
дает NULL. Впоследствии он дает TRUE, если последняя операция
FETCH вернула строку, или FALSE, если последняя операция FETCH
не смогла извлечь строку, так как больше нет доступных строк.
Следующий пример использует %FOUND, чтобы выбрать одно из двух
альтернативных действий:
LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%FOUND THEN -- извлечение успешно
INSERT INTO temp VALUES (...);
ELSE
EXIT;
...
END LOOP;
Вы можете открыть несколько курсоров, а затем использовать
%FOUND, чтобы проверять, в каких курсорах еще есть строки. Если
курсор не открыт, обращение к нему через %FOUND возбуждает
предопределенное исключение INVALID_CURSOR.
Использование %ROWCOUNT
Когда вы открываете курсор, его атрибут %ROWCOUNT обнуляется.
Перед первой операцией FETCH %ROWCOUNT возвращает 0.
Впоследствии, %ROWCOUNT возвращает число строк, извлеченных
операциями FETCH из активного множества на данный момент. Это
число увеличивается, если последняя FETCH вернула строку.
Следующий пример использует %ROWCOUNT, чтобы предпринять
определенные действия, если выбрано более 10 строк:
LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
-- выбрано больше 10 строк
...
END IF;
END LOOP;
Вы можете открыть несколько курсоров, а затем использовать
%ROWCOUNT, чтобы проверять, сколько строк извлечено из каждого
курсора. Если курсор не открыт, обращение к нему через
%ROWCOUNT возбуждает предопределенное исключение INVALID_CURSOR.
Использование %ISOPEN
%ISOPEN дает TRUE, если явный курсор открыт, и FALSE в противном
случае. Следующий пример использует %ISOPEN для выбора
действия:
IF с1%ISOPEN THEN -- курсор открыт
...
ELSE -- курсор закрыт, открыть его
OPEN c1;
END IF;
Взаимодействие с ORACLE 4-19
Таблица 4-1 показывает значения, возвращаемые атрибутами явного
курсора перед и после выполнения операций OPEN, FETCH и CLOSE.
Обратите внимание, что перед первой операцией FETCH атрибуты
%NOTFOUND и %FOUND возвращают NULL на открытом курсоре.
Табл.4-1
Значения атрибутов курсора
------------T--------T-----------T----------¬
¦ %NOTFOUND ¦ %FOUND ¦ %ROWCOUNT ¦ %ISOPEN ¦
-----------T-------+-----------+--------+-----------+----------+
¦ ¦ Перед ¦ * ¦ * ¦ * ¦ FALSE ¦
¦ OPEN +-------+-----------+--------+-----------+----------+
¦ ¦ После ¦ NULL ¦ NULL ¦ 0 ¦ TRUE ¦
+----------+-------+-----------+--------+-----------+----------+
¦ Первая ¦ Перед ¦ NULL ¦ NULL ¦ 0 ¦ TRUE ¦
¦ FETCH +-------+-----------+--------+-----------+----------+
¦ ¦ После ¦ FALSE ¦ TRUE ¦ 1 ¦ TRUE ¦
+----------+-------+-----------+--------+-----------+----------+
¦ Промежу- ¦ Перед ¦ FALSE ¦ TRUE ¦ 1 ¦ TRUE ¦
¦ точные +-------+-----------+--------+-----------+----------+
¦ FETCH ¦ После ¦ FALSE ¦ TRUE ¦ ** ¦ TRUE ¦
+----------+-------+-----------+--------+-----------+----------+
¦ Последняя¦ Перед ¦ FALSE ¦ TRUE ¦ ** ¦ TRUE ¦
¦ FETCH +-------+-----------+--------+-----------+----------+
¦ ¦ После ¦ TRUE ¦ FALSE ¦ ** ¦ TRUE ¦
+----------+-------+-----------+--------+-----------+----------+
¦ ¦ Перед ¦ TRUE ¦ FALSE ¦ ** ¦ TRUE ¦
¦ CLOSE +-------+-----------+--------+-----------+----------+
¦ ¦ После ¦ * ¦ * ¦ * ¦ FALSE ¦
L----------+-------+-----------+--------+-----------+-----------
* Возбуждает предопределенное исключение INVALID_CURSOR
** Зависит от данных
4-20 Руководство пользователя и справочник по PL/SQL
Несколько примеров
------------------
Предположим, что в таблице data_table хранятся данные (числа),
собранные в ходе лабораторных экспериментов, и вы хотите
проанализировать данные из эксперимента 1. В следующем примере
вы вычисляете результаты и помещаете их в таблицу базы данных с
именем temp:
-- доступен на диске в файле EXAMP5
DECLARE
num1 data_table.n1%TYPE; -- Объявить переменные
num2 data_table.n2%TYPE; -- с теми же типами,
num3 data_table.n3%TYPE; -- что и столбцы таблицы
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table
WHERE exper_num = 1;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO num1, num2, num3;
EXIT WHEN c1%NOTFOUND;
-- условие c1%NOTFOUND будет равно TRUE,
-- когда FETCH не обнаружит больше строк
/* вычислить и сохранить результаты */
result := num2/(num1 + num3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
CLOSE c1;
COMMIT;
END;
Взаимодействие с ORACLE 4-21
В следующем примере вы проверяете все сталлажи, содержащие
деталь с номером 5469, и забираете их содержимое, пока не
наберете 1000 единиц:
-- доступен на диске в файле EXAMP6
DECLARE
CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin
FROM bins
WHERE part_num = part_number AND
amt_in_bin > 0
ORDER BY bin_num
FOR UPDATE OF amt_in_bin;
bin_amt bins.amt_in_bin%TYPE;
total_so_far NUMBER(5) := 0;
amount_needed CONSTANT NUMBER(5) := 1000;
bins_looked_at NUMBER(3) := 0;
BEGIN
OPEN bin_cur(5469);
WHILE total_so_far < amount_needed LOOP
FETCH bin_cur INTO bin_amt;
EXIT WHEN bin_cur%NOTFOUND;
/* If we exit, there's not enough to *
* satisfy the order. */
bins_looked_at := bins_looked_at + 1;
IF total_so_far + bin_amt < amount_needed THEN
UPDATE bins SET amt_in_bin = 0
WHERE CURRENT OF bin_cur;
-- take everything in the bin
total_so_far := total_so_far + bin_amt;
ELSE -- we finally have enough
UPDATE bins SET amt_in_bin = amt_in_bin
- (amount_needed - total_so_far)
WHERE CURRENT OF bin_cur;
total_so_far := amount_needed;
END IF;
END LOOP;
CLOSE bin_cur;
INSERT INTO temp VALUES (null, bins_looked_at, '<- bins
looked at');
COMMIT;
END;
4-22 Руководство пользователя и справочник по PL/SQL
Неявные курсоры
---------------
ORACLE неявно открывает курсор для обработки каждого предложения
SQL, не ассоциированного с явно объявленным курсором. PL/SQL
позволяет вам обращаться к последнему открытому неявному курсору
через имя "SQL". Поэтому, хотя вы не можете манипулировать
неявным курсором посредством предложений OPEN, FETCH и CLOSE, вы
можете опрашивать атрибуты этого курсора, чтобы получить
полезную информацию о последней выполненной операции SQL.
Атрибуты неявного курсора
-------------------------
Курсор SQL имеет четыре атрибута: %NOTFOUND, %FOUND, %ROWCOUNT и
%ISOPEN. Эти атрибуты, присоединяемые к имени курсора (SQL),
дают вам доступ к информации о выполнении предложений INSERT,
UPDATE, DELETE и однострочных предложений SELECT INTO. Вы
можете использовать атрибуты неявного курсора в процедурных
предложениях, но не в предложениях SQL.
Значения атрибутов неявного курсора всегда относятся к последней
выполненной операции SQL, где бы она ни появилась.
Соответствующее предложение SQL может даже находиться в другой
сфере (например, в подблоке). Поэтому, если вы хотите сохранить
значение такого атрибута, немедленно присвойте его булевской
переменной. Следующий пример показывает, как несоблюдение этого
правила может привести к логической ошибке:
UPDATE parts SET qty = qty - 1 WHERE partno = part_id;
check_parts; -- вызов процедуры
IF SQL%NOTFOUND THEN -- рискованно!
...
END IF;
В этом примере рискованно полагаться на условие IF, потому что
процедура ckeck_parts, возможно, изменила значение %NOTFOUND.
Вы можете исправить этот код следующим образом, объявив в
декларативной части булевскую переменную sql_notfound:
UPDATE parts SET qty = qty - 1 WHERE partno = part_id;
sql_notfound := SQL%NOTFOUND;
check_parts;
IF sql_notfound THEN
...
END IF;
Пока ORACLE автоматически не открыл курсор SQL, атрибуты
неявного курсора возвращают NULL.
Взаимодействие с ORACLE 4-23
Использование %NOTFOUND
%NOTFOUND дает TRUE, если INSERT, UPDATE или DELETE не
обработала ни одной строки, или если операция SELECT INTO не
возвратила ни одной строки. В противном случае %NOTFOUND дает
FALSE. В следующем примере вы используете %NOTFOUND, чтобы
вставить новую строку, если операция обновления не нашла строки:
UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;
IF SQL%NOTFOUND THEN -- обновление не прошло
INSERT INTO temp VALUES (...);
END IF;
Если предложение SELECT INTO не возвращает ни одной строки,
возбуждается предопределенное исключение NO_DATA_FOUND,
независимо от того, проверяете ли вы %NOTFOUND в следующей
строке или нет. Рассмотрим следующий пример:
DECLARE
my_sal NUMBER(7,2);
my_empno NUMBER(4);
BEGIN
...
SELECT sal INTO my_sal FROM emp WHERE empno = my_empno;
-- может возбудить исключение NO_DATA_FOUND
IF SQL%NOTFOUND THEN -- получит управление лишь при FALSE
... -- эти действия никогда не будут выполнены
END IF;
EXCEPTION
...
END;
Здесь проверка бессмысленна, потому что условие IF будет
проверяться лишь тогда, когда %NOTFOUND даст FALSE. Когда
возбуждается исключение NO_DATA_FOUND, нормальное выполнение
останавливается, и управление передается на часть обработки
исключений блока. В такой ситуации атрибут %NOTFOUND полезен в
обработчике исключений OTHERS, как показывает следующий пример.
Вместо написания обработчика исключения NO_DATA_FOUND вы
узнаете, что это исключение было возбуждено, проверяя атрибут
%NOTFOUND.
DECLARE
my_sal NUMBER(7,2);
my_empno NUMBER(4);
BEGIN
...
SELECT sal INTO my_sal FROM emp WHERE empno = my_empno;
-- может возбудить исключение NO_DATA_FOUND
EXCEPTION
WHEN OTHERS THEN
IF SQL%NOTFOUND THEN -- проверка на 'no data found'
...
END IF;
...
END;
4-24 Руководство пользователя и справочник по PL/SQL
Однако, предложение SELECT INTO, использующее вызов групповой
функции SQL, никогда не возбуждает исключение NO_DATA_FOUND.
Дело в том, что групповые функции SQL, такие как AVG, MAX или
SUM, ВСЕГДА возвращают значение, даже если это значение есть
NULL. В таких случаях %NOTFOUND всегда дает FALSE. Рассмотрим
следующий пример:
DECLARE
my_sal NUMBER(7,2);
my_deptno NUMBER(4);
BEGIN
...
SELECT MAX(sal) INTO my_sal FROM emp WHERE empno = my_deptno;
-- никогда не возбудит NO_DATA_FOUND
IF SQL%NOTFOUND THEN -- всегда равен FALSE
... -- это действие никогда не будут выполнено
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN -- никогда не вызывается
...
END;
Использование %FOUND
%FOUND логически противоположен атрибуту %NOTFOUND. Перед
выполнением предложения манипулирования данными SQL SQL%FOUND
дает NULL. Впоследствии, этот атрибут дает TRUE, если операция
INSERT, UPDATE или DELETE затронула хотя бы одну строку, или
если операция SELECT INTO вернула хотя бы одну строку. В
противном случае SQL%FOUND дает FALSE. В следующем примере вы
используете %FOUND, чтобы вставить строку при успехе операции
удаления:
DELETE FROM temp_emp WHER empno = my_empno;
IF SQL%FOUND THEN -- удаление успешно
INSERT INTO emp VALUES (my_empno, my_empname, ...);
END IF;
Использование %ROWCOUNT
SQLROWCOUNT возвращает число строк, на которое воздействовала
операция INSERT, UPDATE или DELETE, или число строк,
возвращенных операцией SELECT INTO. %ROWCOUNT возвращает 0, если
операция INSERT, UPDATE или DELETE не затронула ни одной строки,
или если операция SELECT INTO не возвратила ни одной строки.
Следующий пример использует %ROWCOUNT, чтобы предпринять
определенные действия, если было удалено более 10 строк:
DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN
-- удалено больше 10 строк
...
END IF;
Взаимодействие с ORACLE 4-25
Если операция SELECT INTO возвращает более одной строки, то
возбуждается предопределенное исключение TOO_MANY_ROWS, а
атрибут %ROWCOUNT устанавливается в 1, а НЕ в действительное
число строк, удовлетворяющих запросу. В такой ситуации
%ROWCOUNT полезен в обработчике исключений OTHERS, как
показывает следующий пример. Вместо написания обработчика
исключения TOO_MANY_ROWS вы узнаете, что это исключение было
возбуждено, проверяя атрибут %ROWCOUNT.
DECLARE
my_sal NUMBER(7,2);
my_ename CHAR(10);
BEGIN
...
SELECT sal INTO my_sal FROM emp WHERE ename = my_ename;
-- может возбудить исключение TOO_MANY_ROWS
...
EXCEPTION
WHEN OTHERS THEN
IF SQL%ROWCOUNT > 0 THEN -- проверка на 'too many rows'
...
END IF;
...
END;
Использование %ISOPEN
ORACLE автоматически закрывает неявный курсор после выполнения
связанной с ним операции SQL. Поэтому атрибут SQL%ISOPEN всегда
возввращает FALSE.
Пакетированные курсоры
----------------------
При помещении курсора в пакет вы можете отделить спецификацию
курсора от его тела, используя фразу RETURN, как показывает
следующий пример:
CREATE PACKAGE emp_actions AS
/* Объявить спецификацию курсора. */
CURSOR c1 RETURN emp%ROWTYPE;
...
END emp_actions;
CREATE PACKAGE BODY emp_actions AS
/* Определить тело курсора. */
CURSOR c1 RETURN emp%ROWTYPE
SELECT * FROM emp WHERE sal > 3000;
...
END emp_actions;
4-26 Руководство пользователя и справочник по PL/SQL
Это позволит вам изменять тело курсора, не затрагивая его
спецификацию. Так, в последнем примере можно было бы изменить
фразу WHERE:
CURSOR c1 RETURN emp%ROWTYPE
SELECT * FROM emp WHERE deptno = 20;
В спецификации курсора отсутствует предложение SELECT, потому
что фразы RETURN достаточно, чтобы определить тип данных
результирующего значения. Вы можете использовать в фразе RETURN
атрибут %ROWTYPE, чтобы указать тип записи, которая будет
представлять строку в таблице базы данных. Вы также можете
использовать в фразе RETURN атрибут %TYPE, чтобы указать тип
данных переменной, константы или столбца базы данных.
Тело курсора должно содержать предложение SELECT, а также точно
такую же фразу RETURN, как и в спецификации курсора. Более
того, количество и типы данных элементов списка в предложении
SELECT должны соответствовать фразе RETURN.
Курсорные циклы FOR
-------------------
Как уже говорилось в главе 1, в большинстве ситуаций, требующих
курсора, вы можете использовать курсорный цикл FOR, чтобы
упростить кодирование. Курсорный цикл FOR неявно объявляет свой
индекс цикла как запись типа %ROWTYPE, открывает курсор, в цикле
извлекает строки из активного множества в поля записи, и
закрывает курсор, когда все строки обработаны или когда вы
выходите из цикла.
Рассмотрим следующий блок PL/SQL, который анализирует данные,
собранные в ходе лабораторных экспериментов, и помещает
результаты во временную таблицу. Переменная c1rec, используемая
как индекс в курсорном цикле FOR, неявно объявляется как запись,
хранящая все элементы данных, возвращаемые одной операцией FETCH
для курсора c1. Вы обращаетесь к элементам данных, хранящимся в
полях записи, используя квалифицированные ссылки.
-- доступен на диске в файле EXAMP7
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table
WHERE exper_num = 1;
BEGIN
FOR c1rec IN c1 LOOP
/* вычислить и сохранить результаты */
result := c1rec.n2 / (c1rec.n1 + c1rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END;
Взаимодействие с ORACLE 4-27
Перед каждой итерацией курсорного цикла FOR, PL/SQL извлекает
данные в неявно объявленную запись, которая эквивалентна
следующей явно объявленной записи:
c1rec c1%ROWTYPE;
Эта запись определена только внутри цикла. Вы не можете
обращаться к ее полям вне цикла. Например, следующая ссылка
незаконна:
BEGIN
...
FOR c1rec IN c1 LOOP
...
END LOOP;
result := c1rec.n2 + 3; -- незаконно
END;
Последовательность предложений внутри цикла выполняется один раз
для каждой строки, которая удовлетворяет запросу,
ассоциированному с курсором. Когда вы выходите из цикла, курсор
закрывается автоматически. Это справедливо даже тогда, когда вы
выходите из цикла принудительно, с помощью EXIT или GOTO, или
когда внутри цикла возбуждается исключение.
Использование алиасов
Поля в неявно объявленной записи курсорного цикла FOR содержат
значения столбцов из последней извлеченной строки. Эти поля
имеют такие же имена, что и соответствующие столбцы в списке
SELECT запроса. Однако что, если элемент списка SELECT является
выражением? Рассмотрим следующий пример:
CURSOR c1 IS
SELECT empno, sal+NVL(comm,0), job FROM ...
В таких случаях вы должны предоставлять алиас для элемента
списка SELECT. В следующем примере для вычисляемого элемента
sal+NVL(comm,0) используется алиас wages:
CURSOR c1 IS
SELECT empno, sal+NVL(comm,0) wages, job FROM ...
При обращениях к соответствующему полю вы используете алиас в
квалифицированной ссылке, например:
IF emp_rec.wages < 1000 THEN ...
4-28 Руководство пользователя и справочник по PL/SQL
Передача параметров
Вы можете передавать параметры курсору, используемому в
курсорном цикле FOR. В следующем примере передается номер
отдела. Здесь вычисляется общий заработок (жалованье плюс
комиссионные) для сотрудников указанного отдела. Кроме того,
подсчитывается, сколько сотрудников имеют жалованье выше $2000,
и сколько имеют комиссионные, превышающие жалованье.
-- доступен на диске в файле EXAMP8
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* Число итераций будет равно числу строк, *
* возвращенных курсором emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
'Total Wages: ' || TO_CHAR(total_wages));
COMMIT;
END;
----------------
Обработка транзакций
Первое предложение SQL в вашей программе начинает транзакцию.
Когда одна транзакция заканчивается, очередное предложение SQL
автоматически начинает следующую транзакцию. Таким образом,
каждое предложение SQL является частью некоторой транзакции.
Предложения COMMIT и ROLLBACK гарантируют, что все изменения в
базе данных, осуществленные операциями SQL, либо становятся
постоянными, либо отменяются единовременно. Все предложения
SQL, выполненные после последней операции COMMIT или ROLLBACK,
составляют текущую транзакцию. Предложение SAVEPOINT отмечает и
именует текущую точку в обработке транзакции.
Взаимодействие с ORACLE 4-29
Распределенные транзакции
-------------------------
РАСПРЕДЕЛЕННАЯ ТРАНЗАКЦИЯ включает по меньшей мере одно
предложение SQL, изменяющее данные в нескольких узлах
распределенной базы данных. Если обновление затрагивает лишь
один узел, транзакция удаленная, но не распределенная.
Если часть распределенной транзакции сбивается, вы должны
выполнить откат всей транзакции или откат к точке сохранения.
ORACLE в такой ситуации выдает сообщение об ошибке. Поэтому вы
должны включать проверки на ошибки в каждое приложение,
выполняющее распределенные транзакции. Для дополнительной
информации обратитесь к документу ORACLE7 Server Application
Developer's Guide.
Использование COMMIT
--------------------
Предложение COMMIT завершает текущую транзакцию и делает
постоянными все изменения, осуществленные в течение этой
транзакции. До этого момента другие пользователи не могут
видеть измененных данных; они видят данные в том состоянии,
каким оно было к моменту начала транзакции.
Рассмотрим простую транзакцию, которая осуществляет перевод
денег с одного банковского счета на другой. Эта транзакция
требует двух операций обновления (UPDATE), потому что она должна
дебитовать один счет и кредитовать другой. После кредитования
второго счета вы выдаете команду COMMIT, делая изменения
постоянными. Лишь после этого новое состояние счетов становится
видимым другим пользователям.
BEGIN
...
UPDATE accts SET bal = my_bal - debit
WHERE acctno = 7715;
...
UPDATE accts SET bal = my_bal + credit
WHERE acctno = 7720;
COMMIT WORK;
END;
Необязательное ключевое слово WORK не имеет никакого эффекта,
помимо улучшения читабельности.
Предложение COMMIT освобождает все блокировки таблиц и строк.
Оно также стирает все точки сохранения (обсуждаемые ниже),
отмеченные после последней операции COMMIT или ROLLBACK.
Фраза COMMENT позволяет вам специфицировать комментарий, который
ассоциируется с распределенной транзакцией. Когда вы выдаете
COMMIT, постоянными становятся изменения во всех базах данных,
затронутых распределенной транзакцией. Однако, если во время
выполнения COMMIT произойдет сбой сети или машины, состояние
распределенной транзакции может оказаться неизвестным или
СОМНИТЕЛЬНЫМ (in doubt). В таком случае ORACLE сохраняет в
словаре данных текст, специфицированный в фразе COMMENT, вместе
с идентификатором транзакции. Текст должен быть литералом в
апострофах длиной не более 50 символов, например:
COMMIT COMMENT 'Сомнительная транзакция; уведомите отдел D.E.');
4-30 Руководство пользователя и справочник по PL/SQL
Использование ROLLBACK
----------------------
Предложение ROLLBACK противоположно COMMIT. Оно заканчивает
текущую транзакцию и отменяет все изменения, осуществленные за
время этой транзакции. Предложение ROLLBACK полезно по двум
причинам. Во-первых, если вы сделали ошибку, например, удалили
не ту строку из базы данных, вы можете использовать ROLLBACK для
восстановления первоначальных данных. Вариант ROLLBACK TO
позволяет вам отменить изменения до промежуточной точки в
текущей транзакции, так что вы не обязаны стирать все ваши
изменения.
Во-вторых, предложение ROLLBACK полезно, когда вы начали
транзакцию, которую не в состоянии завершить, например, при
возникновении исключения или ошибки в предложении SQL. В таких
случаях ROLLBACK позволяет вам вернуться к стартовой точке, так
что вы можете предпринять корректирующие действия и попытаться
снова повторить транзакцию.
Рассмотрим следующий пример, в котором вы вставляете информацию
о сотруднике в три различных таблицы базы данных. Все три
таблицы имеют столбец, содержащий номер сотрудника и
ограничиваемый уникальным индексом. Если предложение INSERT
пытается вставить повторяющийся номер сотрудника, возбуждается
предопределенное исключение DUP_VAL_ON_INDEX. В этом случае вам
необходимо отменить все изменения. Поэтому вы выдаете ROLLBACK
в обработчике исключений.
DECLARE
emp_id INTEGER;
...
BEGIN
SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ...
...
INSERT INTO emp VALUES (emp_id, ...);
INSERT INTO tax VALUES (emp_id, ...);
INSERT INTO pay VALUES (emp_id, ...);
...
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
...
END;
Фраза COMMENT позволяет вам специфицировать комментарий, который
ассоциируется с распределенной транзакцией. Когда вы выдаете
ROLLBACK, отменяются изменения во всех базах данных, затронутых
распределенной транзакцией. Однако, если во время выполнения
ROLLBACK произойдет сбой сети или машины, состояние
распределенной транзакции может оказаться неизвестным или
сомнительным. В таком случае ORACLE сохраняет в словаре данных
текст, специфицированный в фразе COMMENT, вместе с
идентификатором транзакции. Текст должен быть литералом в
апострофах длиной не более 50 символов, например:
ROLLBACK COMMENT 'In-doubt sales transaction; notify Sales Dept';
('Сомнительная транзакция; уведомите отдел продаж')
Взаимодействие с ORACLE 4-31
Откаты на уровне предложений
Прежде чем исполнять предложение SQL, ORACLE выдает неявную
точку сохранения. Затем, если это предложение сбивается, ORACLE
автоматически выполняет его откат. Например, если предложение
INSERT пытается вставить повторяющееся значение в уникальный
индекс, оно откатывается. При этом теряется лишь работа,
начатая сбившимся предложением SQL; вся работа, проделанная в
текущей транзакции до этого момента, не затрагивается.
ORACLE может также предпринимать откат одиночных предложений SQL
с целью предотвратить мертвые захваты (взаимоблокировки). В
таких случаях ORACLE сигнализирует об ошибке одной из транзакций
и выполняет откат текущего предложения SQL в этой транзакции.
Прежде чем исполнять предложение SQL, ORACLE должен выполнить
его РАЗБОР (parse), т.е. исследовать его, чтобы убедиться, что
оно синтаксически корректно и ссылается на действительные
объекты базы данных. Ошибки, обнаруженные во время разбора
предложения (в отличие ошибок во время выполнения) не приводят к
откату.
Использование SAVEPOINT
SAVEPOINT отмечает и именует текущую точку (точку сохранения) в
процессе транзакции. Такая точка, используемая в предложении
ROLLBACK TO, позволяет отменить часть транзакции. В следующем
примере вы отмечаете точку сохранения перед тем, как выполнять
вставку строки. Если предложение INSERT попытается вставить
повторяющееся значение в столбец empno, возникнет
предопределенное исключение DUP_VAL_ON_INDEX. В этом случае вы
откатитесь к точке сохранения, отменив лишь вставку.
DECLARE
emp_id emp.empno%TYPE;
BEGIN
...
UPDATE emp SET ... WHERE empno = emp_id;
DELETE FROM emp WHERE ...
...
SAVEPOINT do_insert;
INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO do_insert;
END;
При выполнении ROLLBACK TO все точки сохранения, отмеченные
после указанной, стираются, а все изменения, сделанные после
этой точки, отменяются. Однако сама точка сохранения, к которой
вы возвращаетесь, не удаляется. Например, если вы
последовательно отметите точки сохранения A, B C и D, а затем
выполните ROLLBACK TO к точке B, то будут стерты лишь C и D.
ROLLBACK без аргументов, как и COMMIT, стирает все точки
сохранения.
Если вы отмечаете точку сохранения в рекурсивной подпрограмме,
то на каждом уровне рекурсивного спуска предложение SAVEPOINT
будет создавать новые экземпляры точек сохранения. Однако
ROLLBACK TO вернет вас лишь к самой последней из точек
сохранения с данным именем.
4-32 Руководство пользователя и справочник по PL/SQL
Имена точек сохранения - это необъявляемые идентификаторы. Их
можно повторно использовать внутри транзакции. При этом точка
сохранения сдвигается со своей старой позиции в текущую точку в
транзакции. Таким образом, откат к точке сохранения
воздействует лишь на текущую часть вашей транзакции. Рассмотрим
следующий пример:
...
BEGIN
...
SAVEPOINT my_point;
UPDATE emp SET ... WHERE empno = emp_id;
...
SAVEPOINT my_point; -- перемещает my_point в текущую точку
INSERT INTO emp BALUES (emp_id, ...);
...
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO my_point;
END;
По умолчанию число активных точек сохранения на сессию не может
быть больше 5. АКТИВНАЯ ТОЧКА СОХРАНЕНИЯ - это точка,
отмеченная после последней операции COMMIT или ROLLBACK. Вы или
ваш АБД можете поднять этот лимит (вплоть до 255), увеличив
значение параметра инициализации ORACLE с именем SAVEPOINTS.
Неявные точки сохранения
------------------------
Перед выполнением каждого предложения INSERT, UPDATE и DELETE
ORACLE создает неявную точку сохранения (недоступную вам). Если
предложение сбивается, то выполняется откат к этой неявной
точке. Обычно отменяется лишь сбившееся предложение SQL, а не
вся транзакция. Однако, если это предложение возбудило
необрабатываемое исключение, то хост-окружение определяет, что
следует отменить.
Если хранимая подпрограмма сбилась в результате
необрабатываемого исключения, то ORACLE выполняет неявный откат
всех изменений, сделанных этой подпрограммой. Однако, если
подпрограмма выдала COMMIT до возникновения необрабатываемого
исключения, то отменяется лишь неподтвержденная часть работы.
Взаимодействие с ORACLE 4-33
Завершение транзакций
---------------------
Хорошей практикой программирования является явное подтверждение
или явный откат каждой транзакции. Выдаете ли вы COMMIT и
ROLLBACK в вашей программе PL/SQL или в хост-окружении - зависит
от логического потока вашего приложения. Если вы пренебрегаете
явными операциями COMMIT или ROLLBACK, то окончательное
состояние транзакции определяет хост-окружение.
Например, в среде SQL*Plus, если ваш блок PL/SQL не содержит
предложения COMMIT или ROLLBACK, окончательное состояние вашей
транзакции зависит от того, что вы делаете после выполнения
этого блока. Если вы выполняете предложение определения данных,
предложение управления данными или предложение COMMIT, либо если
вы выдаете команду EXIT, DISCONNECT или QUIT, то ORACLE неявно
подтверждает вашу транзакцию. Если вы выдаете предложение
ROLLBACK или аварийно снимаете сессию SQL*Plus, то ORACLE
выполняет откат транзакции.
В среде прекомпилятора ORACLE, если ваша программа не
завершается нормально, ORACLE выполняет откат транзакции.
Программа завершается нормально, если она явно выдает COMMIT или
ROLLBACK и отсоединяется от ORACLE с помощью параметра RELEASE,
например:
EXEC SQL COMMIT WORK RELEASE;
В среде интерфейса вызовов ORACLE (OCI), если вы выдаете вызов
OLOGOFF, ORACLE автоматически подтверждает вашу транзакцию. В
противном случае ORACLE выполняет откат транзакции.
4-34 Руководство пользователя и справочник по PL/SQL
Использование SET TRANSACTION
-----------------------------
Умалчиваемым режимом для всех транзакций является
согласованность данных по чтению НА УРОВНЕ ПРЕДЛОЖЕНИЯ. Это
гарантирует, что запрос видит лишь то состояние данных, которое
было подтверждено перед началом его выполнения, плюс все
изменения, которые внесены предыдущими предложениями в текущей
транзакции. Если во время запроса другие пользователи вносят
изменения в эти же таблицы базы данных, то эти изменения будут
видны лишь последующим, но не текущему, запросу.
Однако вы можете, выдав предложение SET TRANSACTION, установить
режим согласованности данных по чтению НА УРОВНЕ ТРАНЗАКЦИИ.
Это гарантирует, что запрос видит лишь то состояние данных,
которое было подтверждено перед началом всей транзакции; однако
при этом транзакция не должна вносить изменений в базу данных.
Предложение SET TRANSACTION READ ONLY не принимает
дополнительных параметров и имеет вид:
SET TRANSACTION READ ONLY;
Предложение SET TRANSACTION должно быть первым предложением SQL
в транзакции и может появиться лишь один раз на транзакцию. Как
уже сказано, в таком режиме транзакции все запросы, выдаваемые в
ней, видят то состояние данных, которое было подтверждено перед
началом всей транзакции. Режим READ ONLY не влияет на других
пользователей или другие транзакции.
В транзакции READ ONLY допускаются лишь предложения SELECT,
COMMIT и ROLLBACK. Другие предложения, например, INSERT или
DELETE, приводят к возбуждению исключения.
В течение транзакции READ ONLY все ее запросы обращаются к
одному и тому же снимку базы данных, что обеспечивает
многотабличное, многозапросное, согласованное по чтению
представление данных для транзакции. Другие пользователи могут
продолжать опрашивать или обновлять данные в обычном режиме.
Транзакция READ ONLY завершается выдачей COMMIT или ROLLBACK. В
следующем примере вы, как управляющий складом, используете
транзакцию READ ONLY, чтобы собрать цифры по продажам за день,
прошедшую неделю и прошедший месяц. На эти цифры не могут
повлиять другие пользователи, обновляющие базу данных во время
транзакции.
DECLARE
daily_sales REAL;
weekly_sales REAL;
monthly_sales REAL;
BEGIN
SET TRANSACTION READ ONLY;
SELECT SUM(amt) INTO daily_sales FROM sales
WHERE dte = SYSDATE;
SELECT SUM(amt) INTO weekly_sales FROM sales
WHERE dte > SYSDATE - 7;
SELECT SUM(amt) INTO monthly_sales FROM sales
WHERE dte > SYSDATE - 30;
COMMIT; -- это просто сигнал об окончании транзакции,
-- так как никаких изменений она не делает
...
END;
Взаимодействие с ORACLE 4-35
Переопределение умалчиваемой блокировки
---------------------------------------
По умолчанию ORACLE автоматически блокирует для вас структуры
данных. Однако вы можете запросить специфические блокировки по
строкам или таблицам, если вам почему-либо выгодно изменить
умалчиваемый режим блокировки. Явная блокировка позволяет вам
разрешать или запрещать совместный доступ к таблице на время
транзакции.
Использование FOR UPDATE
При объявлении курсора, к которому будет обращаться фраза WHERE
CURRENT OF предложения UPDATE или DELETE, вы должны использовать
фразу FOR UPDATE, чтобы затребовать для этого курсора
монопольные блокировки строк. Фраза FOR UPDATE, когда она
присутствует, должна появляться в конце объявления курсора, как
показывает следующий пример:
DECLARE
CURSOR c1 IS SELECT empno, sal FROM emp
WHERE job = 'SALESMAN' AND comm > sal FOR UPDATE;
Фраза FOR UPDATE указывает, что строки, выбираемые запросом,
будут обновляться или удаляться, и блокирует все строки в
активном множестве курсора. Это полезно, когда вы хотите, чтобы
обновление базировалось на существующих значениях строк. В этом
случае вам нужна гарантия, что строка не будет изменена другим
пользователем, прежде чем вы обновите ее. Все строки в активном
множестве блокируются в момент открытия курсора, и
разблокируются при выполнении COMMIT. Поэтому после COMMIT вы
не можете извлекать строк из курсора, объявленного FOR UPDATE.
При запросах по нескольким таблицам вы можете использовать фразу
FOR UPDATE OF, чтобы ограничить блокировку строк конкретными
таблицами. Строки будут блокироваться лишь в тех таблицах,
которые содержат столбец, указанный в фразе FOR UPDATE OF.
Например, следующий запрос блокирует строки в таблице emp, но не
в таблице dept:
DECLARE
CURSOR c1 IS SELECT ename, dname FROM emp, dept
WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
FOR UPDATE OF sal;
Чтобы обратиться к последней строке, извлеченной курсором, вы
используете фразу WHERE CURRENT OF предложения UPDATE или
DELETE, как показывает следующий пример:
DECLARE
CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE;
...
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO ...
...
UPDATE emp SET sal = new_sal WHERE CURRENT OF c1;
END LOOP;
...
4-36 Руководство пользователя и справочник по PL/SQL
Использование LOCK TABLE
Предложение LOCK TABLE позволяет вам заблокировать одну или
несколько таблиц в указанном режиме, так что вы можете
регулировать одновременный доступ к таблицам, поддерживая их
целостность. Например, предложение, приведенное ниже, блокирует
таблицу emp в режиме row share. Такой режим разрешает
одновременный доступ к таблице, но запрещает другим
пользователям блокировать всю таблицу для монопольного
использования. Блокировка таблицы освобождается, когда ваша
транзакция выдает COMMIT или ROLLBACK.
LOCK TABLE emp IN ROW SHARE MODE NOWAIT;
Режим блокировки определяет, какие другие блокировки могут быть
применены к таблице. Например, несколько пользователей могут
одновременно затребовать блокировки row share для одной и той же
таблицы, но лишь один пользователь за раз может затребовать
МОНОПОЛЬНУЮ (exclusive) блокировку. Пока один пользователь
имеет монопольную блокировку таблицы, другие пользователи не
могут изменять (INSERT, UPDATE или DELETE) строк в этой таблице.
Необязательное ключевое слово NOWAIT указывает, что, если запрос
LOCK TABLE не может быть удовлетворен (возможно, потому, что
таблица уже заблокирована другим пользователем), то LOCK TABLE
вернет управление пользователю, вместо того, чтобы ждать
удовлетворения запроса. Если вы опустите ключевое слово NOWAIT,
то ORACLE будет ждать освобождения таблицы; это ожидание не
имеет устанавливаемого предела.
Блокировка таблицы никогда не препятствует другим пользователям
выдавать запросы по этой таблице; с другой стороны, запрос
никогда не требует блокировки таблицы. Одной транзакции
придется ждать завершения другой лишь в том случае, если эти
транзакции пытаются модифицировать одну и ту же строку.
Извлечения между COMMIT'ами
Как говорилось выше, фраза FOR UPDATE запрашивает монопольную
блокировку строк. Все строки в активном множестве блокируются
во время открытия курсора, а не во время их извлечения. Все
строки освобождаются при завершении транзакции (COMMIT или
ROLLBACK). Поэтому вы не можете извлекать строки из курсора,
объявленного FOR UPDATE, после COMMIT. Если вы попытаетесь
сделать это, возникнет исключение. Рассмотрим следующий цикл
FOR, который собъется после десятой вставки:
DECLARE
CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal;
ctr NUMBER := 0;
BEGIN
FOR emp_rec IN c1 LOOP -- неявные операции FETCH
...
ctr := ctr + 1;
INSERT INTO temp VALUES (ctr, 'еще работает');
IF ctr >= 10 THEN
COMMIT; -- освобождает блокировки
END IF;
END LOOP;
END;
Взаимодействие с ORACLE 4-37
Если вы собираетесь выполнять COMMIT, не закончив всех
извлечений, то не используйте фразу FOR UPDATE или FOR UPDATE
OF. Вместо этого, чтобы имитировать поведение фразы WHERE
CURRENT OF, выбирайте (SELECT) ROWID для каждой строки, а затем
используйте полученное значение в последующих операциях UPDATE
или DELETE, как показывает следующий пример:
DECLARE
CURSOR c1 IS SELECT ename, job, rowid FROM emp;
my_ename emp.ename%TYPE;
my_job emp.job%TYPE;
my_rowid ROWID;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_job, my_rowid;
EXIT WHEN c1%NOTFOUND;
UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid;
-- это имитирует WHERE CURRENT OF c1
COMMIT;
END LOOP;
CLOSE c1;
END;
Заметьте, однако, что извлекаемые строки НЕ БЛОКИРОВАНЫ, ибо
фраза FOR UPDATE OF не использовалась. Поэтому вы можете
получить несогласованные результаты, если другой пользователь
изменит строку после того, как вы прочитали ее, но до того, как
вы модифицировали ее. Кроме того, курсор должен иметь
согласованное по чтению представление данных, и потому сегменты
отката, используемые для ваших обновлений, не будут освобождены,
пока курсор не будет закрыт. Это может замедлить обработку,
если обновляется много строк.
Следующий пример показывает, что вы можете использовать атрибут
%ROWTYPE с курсорами, обращающимися к псевдостолбцу ROWID:
DECLARE
CURSOR c1 IS SELECT ename, sal, rowid FROM emp;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
...
IF ... THEN
DELETE FROM emp WHERE rowid = emp_rec.rowid;
END IF;
END LOOP;
CLOSE c1;
END;
4-38 Руководство пользователя и справочник по PL/SQL
Как справляться с ограничениями на размер программы
---------------------------------------------------
PL/SQL был спроектирован прежде всего для обработки транзакций.
Одним из следствий такого проекта является то, что компилятор
PL/SQL накладывает ограничение на размер программы. Этот лимит
зависит от смеси предложений в вашей программе PL/SQL.
Программы, превышающие этот лимит, вызывают сообщение об ошибке
"программа слишком велика".
Наилучшим решением этой проблемы является модуляризация вашей
программы путем определения подпрограмм, которые можно сохранить
в базе данных ORACLE. Для более подробной информации обратитесь
к главе 6.
Другие решение - разбить вашу программу на два меньших блока.
Прежде чем завершиться, первый блок должен вставить все данные,
необходимые второму блоку, в таблицу базы данных (скажем, temp).
Начав выполнение, второй блок должен выбрать все данные из
таблицы temp. Это напоминает передачу параметров от одной
процедуры другой. Следующий пример показывает два
взаимодействующих через "параметры" блока PL/SQL в одном скрипте
SQL*Plus:
...
DECLARE
mode NUMBER;
median NUMBER;
...
BEGIN
...
INSERT INTO temp (col1, col2, col3)
VALUES (mode, median, 'blockA');
END;
/
...
DECLARE
mode NUMBER;
median NUMBER;
...
BEGIN
...
SELECT col1, col2 INTO mode, median FROM temp
WHERE col3 = 'blockA';
END;
/
...
Взаимодействие с ORACLE 4-39
Этот метод будет работать, если вам не потребуется снова
исполнять первый блок, когда второй блок еще работает (например,
если несколько пользователей не будут исполнять этот файл
одновременно).
Другое решение избегает этих ограничений. Если вы встроите
PL/SQL в хост-язык третьего поколения, такой как C, COBOL или
FORTRAN, то вы сможете повторно исполнять первый блок при помощи
команд передачи управления. Кроме того, вместо использования
временной таблицы в базе данных, вы можете передавать данные
через глобальные хост-переменные, как показывает следующий
пример на Pro*C:
EXEC SQL BEGIN DECLARE SECTION;
int my_empno;
float my_sal, my_comm;
short comm_ind;
...
EXEC SQL END DECLARE SECTION;
...
EXEC SQL EXECUTE
BEGIN
...
SELECT sal, comm INTO :my_sal, :my_comm:comm_ind FROM emp
WHERE empno = :my_empno;
IF :my_comm:comm_ind IS NULL THEN
...
END IF;
END;
END-EXEC;
...
EXEC SQL EXECUTE
BEGIN
...
IF :my_comm:com_ind > 1000 THEN
:my_sal := :my_sal * 1.10;
UPDATE emp SET sal = :my_sal WHERE empno = :my_empno;
END IF;
END;
END-EXEC;
...
4-40 Руководство пользователя и справочник по PL/SQL
----------------
Триггеры базы данных
ТРИГГЕР БАЗЫ ДАННЫХ - это хранимая программная единица PL/SQL,
ассоциированная с конкретной таблицей базы данных. ORACLE
исполняет (возбуждает) триггер бады данных автоматически каждый
раз, когда данная операция SQL воздействует на эту таблицу.
Итак, в отличие от подпрограмм, которые должны вызываться явно,
триггер базы данных вызывается неявно. Среди прочего, вы можете
использовать триггеры базы данных для
* аудита (отслеживания) модификаций данных
* прозрачной журнализации (регистрации) событий
* реализации комплексных организационных правил
* автоматического вычисления значений столбцов
* осуществления сложных процедур защиты
* поддержки дублированных таблиц
С каждой таблицей можно ассоциировать до 12 триггеров базы
данных. Для создания триггера базы данных вы должны иметь
привилегии CREATE TRIGGER, а также либо владеть ассоциированной
таблицей, либо иметь привилегии ALTER для ассоциированной
таблицы, либо иметь привилегии ALTER ANY TABLE.
Триггер базы данных состоит из трех частей: события триггера,
необязательного ограничения триггера и действия триггера. Когда
происходит событие триггера, триггер базы данных возбуждается, и
анонимный блок PL/SQL выполняет предписанное действие. Триггеры
базы данных возбуждаются с привилегиями владельца, а не текущего
пользователя. Поэтому владелец должен иметь должный доступ ко
всем объектам, вовлекаемым в действие триггера.
Взаимодействие с ORACLE 4-41
Следующий пример иллюстрирует прозрачную журнализацию событий.
Триггер базы данных с именем reorder обеспечивает, что товар
заказывается заново каждый раз, когда его имеющееся на складе
количество (qty_on_hand) падает ниже пороговой точки.
CREATE TRIGGER reorder
/* событие триггера */
AFTER UPDATE OF qty_on_hand ON inventory -- таблица
FOR EACH ROW
/* ограничение триггера */
WHEN (new.reorderable = 'T')
BEGIN
/* действие триггера */
IF :new.qty_on_hand < :new.reorder_point THEN
INSERT INTO pending_orders
VALUES (:new.part_no, :new.reorder_qty, SYSDATE);
END IF;
END;
Имя в фразе ON идентифицирует таблицу базы данных,
ассоциированную с триггером базы данных. Событие триггера
специфицирует предложение манипулирования данными SQL, которое
воздействует на таблицу. В данном случае это предложение
UPDATE. Если предложение триггера сбивается, оно откатывается.
Ключевое слово AFTER указывает, что триггер базы данных
возбуждается после того, как обновление выполнено.
По умолчанию, триггер базы данных возбуждается один раз на всю
таблицу. Необязательная фраза FOR EACH ROW указывает, что
триггер должен возбуждаться один раз на каждую строку. Для
того, чтобы триггер возбудился, однако, требуется, чтобы
булевское выражение в фразе WHEN давало значение TRUE.
Префикс :new представляет собой КОРРЕЛИРУЮЩЕЕ ИМЯ, которое
отсылает к вновь измененному значению столбца. Внутри триггера
базы данных вы можете обращаться как к новому, так и к старому
(:old) значениям столбцов в измененных строках. Заметьте, что в
фразе WHEN двоеточие не используется. Вы можете использовать
фразу REFERENCING (здесь не показана), чтобы заменить :new и
:old другими коррелирующими именами.
За исключением предложений управления транзакциями, таких как
COMMIT и ROLLBACK, в блоке BEGIN..END может появляться любое
предложение SQL или процедурное предложение, включая вызовы
подпрограмм. Триггер базы данных может также иметь секции
DECLARE и EXCEPTION.
4-42 Руководство пользователя и справочник по PL/SQL
Как показывает следующий пример, действие триггера может
включать вызовы встроенной процедуры ORACLE с именем
raise_application_error, которая позволяет выдавать определенные
пользователем сообщения об ошибках:
CREATE TRIGGER check_salary
BEFORE INSERT OR UPDATE OF sal, job ON emp
FOR EACH ROW
WHEN (new.job != 'PRESIDENT')
DECLARE
minsal NUMBER;
maxsal NUMBER;
BEGIN
/* Дать интервал окладов для данной должности из справочника */
SELECT losal, hisal INTO minsal, maxsal FROM sals
WHERE job = :new.job;
/* Если оклад вне диапазона, прибавка отрицательна, *
/* или прибавка выше 10%, возбудить исключение. */
IF (:new.sal < minsal OR :new.sal > maxsal) THEN
raise_application_error(-20225, 'Salary out of range');
ELSIF (:new.sal < :old.sal) THEN
raise_application_error(-20230, 'Negative increase');
ELSIF (:new.sal > 1.1 * :old.sal) THEN
raise_application_error(-20235, 'Increase exceeds 10%');
END IF;
END;
Подробнее о процедуре raise_application_error см. в разделе
"Пакеты, специфичные для продуктов" в главе 7. Для полного
обсуждения триггеров баз данных обратитесь к документу ORACLE7
Server Application Developer's Guide.