Курсорная переменная ссылается на курсор. В отличие от явного курсора, имя которого в PL/SQL используется как идентификатор рабочей области результирующего набора строк, курсорная переменная содержит ссылку на эту рабочую область. Явные и неявные курсоры имеют статическую природу, поскольку они жестко привязаны к конкретным запросам. С помощью же курсорной переменной можно выполнить любой запрос и даже несколько разных запросов в одной программе.
Важнейшим преимуществом курсорных переменных является то, что они предоставляют механизм передачи результатов запроса (выбранных из строк курсора) между разными программами PL/SQL, в том числе между клиентскими и серверными программами. До выхода PL/SQL Release 2.3 приходилось выбирать из курсора все данные, сохранять их в переменных PL/SQL (например, в коллекции) и передавать эти данные в аргументах. А курсорная переменная позволяет передать другой программе ссылку на объект курсора, чтобы та могла работать с его данными. Это упрощает программный код и повышает его эффективность.
Кроме того, курсоры могут совместно использоваться несколькими программами. Например, в архитектуре «клиент-сервер» клиентская программа может открыть курсор и начать выборку из него данных, а затем передать указывающую на него переменную в качестве аргумента хранимой процедуре на сервере. Эта процедура продолжит выборку, а некоторое время спустя снова передаст управление клиентской программе, которая закроет курсор. Так же могут взаимодействовать и две разные хранимые программы из одного или разных экземпляров Oracle.
Описанный процесс схематически показан на рис. 1. Он демонстрирует интересные новые возможности программ PL/SQL — совместное использование данных и управление курсорами.
Рис. 1. Передача ссылки на курсор между программами
Когда используются курсорные переменные?
Ниже перечислены основные области применения курсорных переменных.
- Курсорные переменные могут связываться с разными запросами в разные моменты выполнения вашей программы. Иначе говоря, одна курсорная переменная может использоваться для выборки данных из разных результирующих наборов.
- Курсорные переменные могут передаваться в аргументах процедур или функций. По сути, передавая ссылку на результирующий набор курсора, вы организуете совместное использование его результатов.
- Полноценное использование функциональности статических курсоров
PL/SQL
. Вы можете применятьOPEN, CLOSE и FETCH
к своим курсорным переменным в программахPL/SQL
, а также обращаться к стандартным атрибутам курсоров —%IS0PEN, %FOUND, %NOTFOUND и %ROWCOUNT.
- Присваивание содержимого одного курсора (и его результирующего набора) другой курсорной переменной. Курсорная переменная, как и любая другая переменная, может использоваться в операциях присваивания. Однако, как будет показано позднее в этом блоге, на применение таких переменных устанавливаются определенные ограничения.
Сходство со статическими курсорами
При проектировании курсорных переменных одно из важнейших требований заключалось в том, что семантика управления объектами курсоров по возможности должна совпадать с семантикой управления статическими курсорами. Хотя объявления курсорных переменных и синтаксис их открытия были усовершенствованы, следующие операции с курсорными переменными не отличаются от операций со статическими курсорами:
- Команда
CLOSE
В следующем примере объявляется типREF CURSOR
и курсорная переменная для этого типа. Затем курсорная переменная закрывается с использованием того же синтаксиса, что и для статических курсоров:
DECLARE
TYPE var_cur_type IS REF CURSOR; var_cur var_cur_type;
BEGIN
OPEN var_cur FOR
...
CLOSE var_cur;
END;
- Атрибуты курсоров Разработчик может использовать любые из четырех атрибутов курсоров с точно таким же синтаксисом, как и у статических курсоров. По правилам, управляющим их использованием, и значениям, возвращаемым атрибутами, они ничем не отличаются от явных курсоров. Так, для объявления курсорной переменной из предыдущего примера могут использоваться следующие атрибуты:
var_cur%ISOPEN
var_cur%FOUND
var_cur%NOTFOUND
var_cur%ROWCOUNT
- Выборка данных из курсорной переменной При выборке данных из курсорной переменной в локальные структуры данных
PL/ SQL
может использоваться уже знакомый синтаксисFETCH
. ОднакоPL/SQL
устанавливает дополнительные правила, которые гарантируют, что структуры данных строки курсорной переменной (набор значений, возвращаемых объектом курсора) соответствуют структурам данных справа от ключевого словаINTO
. Эти правила рассматриваются в разделе «Правила использования курсорных переменных» этого блога. Так как синтаксис этих аспектов курсорных переменных не отличается от синтаксиса уже знакомых нам явных курсоров, в следующих разделах основное внимание будет уделяться уникальным особенностям курсорных переменных.
Объявление типов REF CURSOR
По аналогии с таблицами PL/SQL
или записями типа, определяемого программистом, курсорная переменная объявляется в два этапа.
- Определение типа курсора.
- Объявление фактической переменной на базе этого типа.
Синтаксис объявления типа курсора:
TYPE имя_типа_курсора IS REF CURSOR [ RETURN возвращаемый_тип];
Здесь имя курсора — имя типа курсора, а возвращаемый тип — спецификация возвращаемых данных курсора. Это может быть любая структура данных, использование которой допускается в секции RETURN
, определенная с помощью атрибута %ROWTYPE
или путем ссылки на ранее объявленный тип записи.
Обратите внимание, что секция RETURN
в объявлениях типа REF CURSOR
не обязательна, поэтому допустимы оба следующих объявления:
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;
TYPE generic_curtype IS REF CURSOR;
Первая форма REF CURSOR
называется сильнотипизированной, поскольку тип структуры, возвращаемой курсорной переменной, задается в момент объявления (непосредственно или путем привязки к типу строки таблицы). Курсорная переменная, объявленная на основе данного типа, может использоваться только для выборки в структуры данных, соответствующие типу записи. Преимущество сильной типизации заключается в том, что компилятор может сразу определить, соответствует ли тип заданной в нем структуры данных спецификации RETURN
в определении REF CURSOR
.
Вторая форма (без предложения RETURN
) называется слаботипизированной. Тип возвращаемой структуры данных для нее не задается. Курсорная переменная, объявленная на основе такого типа, обладает большей гибкостью, поскольку для нее можно задавать любые запросы с любой структурой возвращаемых данных, причем с помощью одной и той же переменной можно поочередно выполнить несколько разных запросов с результатами разных типов.
В Oracle9i появился предопределенный слабый тип REF CURSOR
с именем SYS_REFCURSOR
. Теперь программисту не нужно определять собственный слабый тип — достаточно использовать стандартный тип Oracle:
DECLARE
my_cursor SYS_REFCURSOR;
Объявление курсорной переменной
Синтаксис объявления курсорной переменной таков:
имя_курсорной_переменной имя_типа_курсора
Здесь имя курсорной переменной — имя объявляемой переменной, а имя_типа_курсора — имя типа курсора, объявленного ранее с помощью команды TYPE
.
Пример создания курсорной переменной:
DECLARE
/* Создание типа курсора для информации о спортивных автомобилях. */
TYPE sports_car_cur_type IS REF CURSOR RETURN car%ROWTYPE;
/* Create a cursor variable for sports cars. */
sports_car_cur sports_car_cur_type;
BEGIN
...
END;
Рис. 2. Курсоры и курсорные переменные
Важно понимать различие между операцией объявления курсорной переменной и операцией создания реального объекта курсора, то есть результирующего набора строк, определяемого командой SQL курсора. Как известно, константа является значением, а переменная — указателем на значение; аналогичным образом статический набор является набором данных, а курсорная переменная — указателем на этот набор. Различие продемонстрировано на рис. 2. Обратите внимание на то, что две разные переменные в разных программах ссылаются на один и тот же объект курсора.
При объявлении курсорной переменной объект курсора не создается. Для создания такового, а также для помещения ссылки на него в переменную нужно выполнить команду OPEN FOR
.
Открытие курсорной переменной
Значение (объект курсора) присваивается курсорной переменной при открытии курсора. Таким образом, синтаксис традиционной команды OPEN
позволяет использовать после секции FOR
команду SELECT
:
OPEN имя_курсора FOR команда_select;
Здесь имя курсора — имя курсора или курсорной переменной, а комanda select — команда SQL SELECT
, используемая для формирования набора строк курсора.
Для сильнотипизированной курсорной переменной количество и типы данных столбцов, указанных в команде SELECT
, должны быть совместимыми со структурой, заданной в секции RETURN
команды TYPE...REF CURSOR
. Пример совместимых типов показан на рис. 3. Подробнее о совместимости возвращаемых курсорами структур рассказывается далее, в разделе «Правила использования курсорных переменных».
Рис. 3. Совместимость типа данных REF CURSOR и типа данных значений, возвращаемых командой SELECT
Если курсорная переменная объявлена со слабой формой REF CURSOR
, ее можно открывать с любым запросом, независимо от структуры возвращаемых им данных. В следующем примере курсорная переменная трижды открывается с тремя разными запросами (а последняя команда OPEN
вообще никак не связана с таблицей employee
!):
DECLARE
TYPE emp_curtype IS REF CURSOR; emp_curvar emp_curtype;
BEGIN
OPEN emp_curvar FOR SELECT * FROM employees;
OPEN emp_curvar FOR SELECT employee_id FROM employees;
OPEN emp_curvar FOR SELECT company_id, name FROM company;
END;
Если курсорной переменной еще не присвоен никакой объект курсора, команда OPEN FOR
неявно создает его. Если же переменная уже указывает на объект курсора, команда OPEN FOR
не создает новый курсор, а ассоциирует с существующим новый запрос. Таким образом, объект курсора является структурой, отдельной и от курсорной переменной, и от запроса.
Если с курсорной переменной, которая ранее использовалась с командой OPEN FOR
, связывается новый результирующий набор и эта курсорная переменная не была явно закрыта, курсор остается открытым. Всегда явно закрывайте курсорные переменные перед тем, как связывать их с другим результирующим набором.
Выборка данных из курсорной переменной
Как уже упоминалось, синтаксис команды FETCH
для курсорной переменной не отличается от синтаксиса статического курсора:
FETCH имя_курсорной_переменной INTO имя_записи;
FETCH имя_курсорной_переменной INTO имя_переменной, имя_переменной ...;
Если курсорная переменная объявляется в сильнотипизированной форме, компилятор PL/SQL проверяет совместимость типов структур в предложении INTO со структурой запроса, связанного с курсорной переменной.
Если курсорная переменная объявляется в слаботипизированной форме, компилятор не сможет выполнить подобную проверку. Данные из такой курсорной переменной могут извлекаться в любые структуры данных, поскольку компилятор не знает, какой объект курсора (и какая команда SQL) будет ей присвоен на момент выборки.
Поэтому проверка совместимости производится во время выполнения программы, непосредственно в момент выборки данных. И если окажется, что возвращенные запросом данные не соответствуют структурам, указанным в условии INTO
, исполняющее ядро PL/SQL
сгенерирует исключение R0WTYPE_MISMATCH
. Учтите, что при необходимости и наличии возможности PL/SQL
выполняет неявное преобразование данных.
Обработка исключения ROWTYPE_MISMATCH
Вы можете перехватить исключение R0WTYPE_MISMATCH
, а затем попытаться выполнить выборку из курсорной переменной с другой секцией INTO
. Но несмотря на выполнение второй команды FETCH
в программе, вы все равно получите первую строку результирующего набора запроса объекта курсора. Эта функциональность удобна для слабых типов REF CURSOR
, которые легко определяются с использованием предопределенного типа SYS_REFCURSOR
.
В следующем примере в таблицах централизованной базы данных хранится информация о разных видах недвижимости: одна таблица для домов, другая для коммерческих строений и т. д. Также существует одна центральная таблица с адресами и типами зданий (жилой дом, коммерческое сооружение и т. д.). Я использую одну процедуру для открытия слабой переменной REF CURSOR
для таблицы, соответствующей адресу объекта. Затем каждое бюро недвижимости вызывает эту процедуру для перебора соответствующих строений. Общая последовательность действий выглядит так:
- Создание процедуры. Обратите внимание: параметр курсорной переменной определяется в режиме in out:
/* Файл в Сети: rowtype_mismatch.sql */
PROCEDURE open_site_list
(address_in IN VARCHAR2,
site_cur_inout IN OUT SYS_REFCURSOR)
IS
home_type CONSTANT PLS_INTEGER := 1;
commercial_type CONSTANT PLS_INTEGER := 2;
/* Статический курсор для получения типа строения. */
CURSOR site_type_cur IS
SELECT site_type FROM property_master
WHERE address = address_in;
site_type_rec site_type_cur%ROWTYPE;
BEGIN
/* Получение типа строения для данного адреса. */
OPEN site_type_cur;
FETCH site_type_cur INTO site_type_rec;
CLOSE site_type_cur;
/* Использование типа для выборки из правильной таблицы.*/
IF site_type_rec.site_type = home_type
THEN
/* использование таблицы жилых домов. */
OPEN site_cur_inout FOR
SELECT * FROM home_properties WHERE address
LIKE '%' || address_in || '%';
ELSIF site_type_rec.site_type = commercial_type
THEN
/* Использование таблицы коммерческих объектов. */
OPEN site_cur_inout FOR
SELECT * FROM commercial_properties WHERE
address LIKE '%' || address_in || '%';
END IF;
END open_site_list;
2. Созданная процедура используется для перебора строений.
В следующем примере я передаю адрес, а затем пытаюсь выполнить выборку из курсора в предположении, что адрес относится к жилому дому. Если адрес в действительности относится к коммерческой недвижимости, PL/SQL
инициирует исключение ROWTYPE_MISMATCH
из-за несовместимости структур записей. Затем раздел исключений снова осуществляет выборку, на этот раз в запись коммерческого строения, и перебор завершается:
/* Файл в Сети: rowtype_mismatch.sql */
DECLARE
/* Объявление курсорной переменной. */
building_curvar sys_refcursor;
address_string property_master.address%TYPE;
/* Определение структур записей для двух разных таблиц.*/
home_rec home_properties%ROWTYPE;
commercial_rec commercial_properties%ROWTYPE;
BEGIN
/* Получение адреса из cookie или другого источника.*/
address_string := current_address ();
/* Присваивание запроса курсорной переменной на основании адреса. */
open_site_list (address_string, building_curvar);
/* Попытка выборки строки в запись жилого дома. */
FETCH building_curvar
INTO home_rec;
/* Если управление передано в эту точку, адрес относится к жилому дому; выводим его. */
show_home_site
(home_rec);
EXCEPTION
/* Если первая запись не относилась к жилому дому... */
WHEN ROWTYPE_MISMATCH
THEN
/* Выборка той же первой строки в запись коммерческого строения. */
FETCH building_curvar
INTO commercial_rec;
/* Вывод информации о коммерческом строении.*/
show_commercial_site (commercial_rec);
END;
Правила использования курсорных переменных
В этом разделе более подробно рассматриваются правила и вопросы, связанные с использованием курсорных переменных в программах. Речь пойдет о правилах соответствия типов данных строк, псевдонимах курсорных переменных и области их действия. Прежде всего помните, что курсорная переменная — это ссылка на объект курсора, представляющий данные, выбранные из базы данных с помощью содержащегося в нем запроса. Это не сам объект курсора. Курсорная переменная может быть связана с определенным запросом при выполнении одного из следующих условий:
- при выполнении команды
OPEN FOR
, назначающий курсорной переменной этот запрос; - если курсорной переменной присваивается значение другой курсорной переменной, указывающей на этот запрос.
С курсорными переменными может использоваться операция присваивания, и эти переменные могут передаваться в качестве аргументов процедурам и функциям. Чтобы иметь возможность выполнять такие операции, переменные должны удовлетворять правилам соответствия типов строк, проверяемым во время компиляции или во время выполнения, в зависимости от типа курсорных переменных.
Правила соответствия типов строк, проверяемые во время компиляции
В процессе компиляции программы PL/SQL проверяет соблюдение следующих правил:
- Две курсорные переменные (или два параметра) совместимы по присваиванию и передаче аргументов в любом из следующих случаев.
- обе переменные (или параметра) определены на основе сильнотипизированных форм
REF CURSOR
с одним и тем же именем типа возвращаемой записи; - обе переменные (или параметра) определены на основе слаботипизированных форм
REF CURSOR
независимо от типа возвращаемой записи; - одна переменная (или параметр) определена на основе сильнотипизированной формы
REF CURSOR
, а другая — на основе слаботипизированной формы.
- Курсорная переменная (или параметр), определенная на основе сильнотипизированной формы REF CURSOR, может открыть запрос, который возвращает строку типа, структурно эквивалентного возвращаемому типу из определения типа курсорной переменной.
- Курсорная переменная (или параметр), определенная на основе слаботипизированной формы REF CURSOR, может открыть любой запрос. Из этой переменной можно выбирать данные в любой список переменных или в запись любого типа.
Если одна из курсорных переменных определена на основе слаботипизированной формы REF CURSOR
, компилятор PL/SQL
не может проверить совместимость типов возвращаемых строк. Такая проверка будет произведена во время выполнения программы с учетом правил, описанных в следующем разделе.
Правила соответствия типов строк, проверяемые во время выполнения
При выполнении программы PL/SQL
проверяет, соблюдаются ли следующие правила:
- Курсорной переменной (или параметру), определенной на основе слаботипизированной формы
REF CURSOR
, можно назначить любой запрос независимо от типа возвращаемых им строк. - Курсорной переменной (или параметру), определенной на основе сильнотипизированной формы REF CURSOR, можно назначить только запрос, структурно соответствующий типу записи, указанному в секции
RETURN
определения типа курсорной переменной. - Две записи (или два списка переменных) считаются структурно совместимыми при выполнении двух условий:
- количество полей в обеих записях (или количество переменных в обоих списках) одинаково;
- для каждого поля одной записи (или переменной одного списка) соответствующее поле (или переменная из другого списка) имеет тот же тип данных или может быть неявно преобразовано к тому же типу данных.
- Список переменных и запись в секции
INTO
командыFETCH
должны структурно соответствовать связанному с курсорной переменной запросу. Это правило касается и статических курсоров.
Псевдоним объекта курсора
Если одна курсорная переменная присваивается другой курсорной переменной, то обе они становятся псевдонимами одного и того же объекта курсора. В данном случае в результате присваивания в принимающую переменную копируется только ссылка на объект курсора. Результаты любой операции с этим курсором, выполняемые через одну из переменных, сразу же становятся доступными для другой переменной.
Следующий анонимный блок демонстрирует принцип действия псевдонимов курсоров:
DECLARE
TYPE curvar_type IS REF CURSOR;
curvar1 curvar_type;
curvar2 curvar_type;
story fairy_tales%ROWTYPE;
BEGIN
OPEN curvar1 FOR SELECT * FROM fairy_tales;
curvar2 := curvar1;
FETCH curvar1 INTO story;
FETCH curvar2 INTO story;
CLOSE curvar2;
FETCH curvar1 INTO story;
END;
Основные действия, выполняемые этим кодом, описаны в следующей таблице.
Любое изменения состояния объекта курсора отражается во всех ссылающихся на него переменных.
Область действия объекта курсора
Курсорные переменные обладают такой же областью действия, как и статические курсоры: это блок PL/SQL
, в котором объявлена переменная. Однако с областью действия объекта курсора, которому присваивается курсорная переменная, дело обстоит иначе. Как только команда OPEN FOR
создаст объект курсора, этот объект остается доступным, пока на него ссылается хотя бы одна активная курсорная переменная. Это означает, что объект курсора можно создать в одной области действия (блок PL/SQL
) и присвоить его курсорной переменной. Присваивая эту курсорную переменную другой курсорной переменной с другой областью действия, вы обеспечиваете доступность объекта курсора, даже если исходная курсорная переменная выходит из области действия.
В следующем примере я использую вложенные блоки для демонстрации того, как объект курсора продолжает существовать вне области действия, в которой он был изначально создан:
DECLARE
curvarl SYS_REFCURSOR;
do_you_get_it VARCHAR2(100);
BEGIN
/*
|| Вложенный блок создает объект курсора и присваивает его || курсорной переменной curvar1.
*/
DECLARE
curvar2 SYS_REFCURSOR;
BEGIN
OPEN curvar2 FOR SELECT punch_line FROM Joke; curvar1 := curvar2;
END;
/*
|| Курсорная переменная curvar2 не активна, но "эстафета"
|| была передана переменной curvar1, существующей
|| во внешнем блоке. Соответственно, мы можем выполнить выборку
|| из объекта курсора через другую переменную.
*/
FETCH curvar1 INTO do_you_get_it;
CLOSE curvar1;
END;
Передача курсорных переменных в аргументах
Курсорную переменную можно передать в качестве аргумента процедуре или функции. В списке параметров такой процедуры (или функции) должен быть задан режим использования и тип REF CURSOR
.
идентификация типа REF CURSOR
В заголовке программы необходимо идентифицировать тип параметра курсорной переменной. Для этого он должен быть заранее объявлен.
Если вы создаете локальный модуль внутри другой программы, тип курсора можно определить в той же программе. Пример:
DECLARE
/* Определение типа REF CURSOR. */
TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;
/* Тип указывается в списке параметров. */
PROCEDURE open_query (curvar_out OUT curvar_type)
IS
local_cur curvar_type;
BEGIN
OPEN local_cur FOR SELECT * FROM
company; curvar_out := local_cur;
END;
BEGIN
...
END;
Если вы создаете отдельную процедуру или функцию, сослаться на существующий тип REF CURSOR
можно только одним способом: разместив команду TYPE в пакете. Все переменные, объявленные в спецификации пакета, становятся глобальными в рамках сеанса, и для ссылок на этот тип курсора может использоваться точечный синтаксис. Последовательность действий в этом случае должна быть такой:
- Создаем пакет с объявлением типа
REF CURSOR
:
PACKAGE company
IS
/* Определение типа REF CURSOR. */
TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;
END package;
- В отдельной процедуре ссылаемся на тип
REF CURSOR
, указывая перед именем типа имя пакета:
PROCEDURE open_company (curvar_out OUT company.curvar_type) IS
BEGIN
...
END;
Назначение режима параметра
Курсорные переменные, как и любые параметры, могут работать в одном из трех режимов:
- IN — разрешается только чтение данных,
- OUT — разрешается только запись данных,
- IN OUT — разрешается как чтение, так и запись данных.
Помните, что значение курсорной переменной представляет собой ссылку на объект курсора, а не состояние этого объекта. Иначе говоря, значение курсорной переменной не изменяется после выборки данных или закрытия курсора.
Значение курсорной переменной (то есть объект курсора, на который указывает переменная) может измениться только в результате выполнения двух операций:
- Присваивание курсорной переменной.
- Выполнение команды OPEN FOR.
Если курсорная переменная уже указывает на объект курсора, OPEN FOR
не изменяет ссылку; изменяется только запрос, связанный с объектом.
Операции FETCH
и CLOSE
изменяют состояние объекта курсора, но не ссылку на объект, которая является значением курсорной переменной.
Пример программы, использующей курсорные переменные в параметрах:
PROCEDURE assign_curvar (old_curvar_in IN company.curvar_type, new_curvar_out OUT company.curvar_type)
IS
BEGIN
new_curvar_out := old_curvar_in;
END;
Процедура копирует старую курсорную переменную в новую переменную. Первый параметр объявлен с режимом IN, потому что он используется только в правой части присваивания. Второй параметр должен быть объявлен в режиме OUT (или IN OUT), потому что его значение изменяется внутри процедуры. Обратите внимание: тип curvar_type
определяется в пакете company.
Ограничения на использование курсорных переменных
Использование курсорных переменных подчиняется следующим ограничениям (возможно, компания Oracle снимет некоторые из них в будущих версиях):
- Курсорные переменные не могу объявляться в пакетах, потому что они не обладают долгосрочным состоянием.
- Не допускается использование вызовов
RPC
(Remote Procedure Call) для передачи курсорных переменных между серверами. - Если курсорная переменная передается в качестве переменной привязки или хостпеременной в PL/SQL, вы не сможете выполнить выборку из нее с сервера, если только она не открывается в вызове с того же сервера.
- Запрос, связанный с курсорной переменной в команде
OPEN FOR
, не может использовать секциюFOR UPDATE
в Oracle8i и более ранних версиях. - Курсорные переменные не могут проверяться на равенство, неравенство или неопределенность с использованием операторов сравнения.
- Курсорной переменной нельзя присвоить
NULL
; Oracle выдает сообщение об ошибкеPLS-00382
:Expression is of wrong type
. - Значения курсорных переменных не могут храниться в столбцах базы данных. Тип
REF CURSOR
не может использоваться для определения типа столбцов в командахCREATE TABLE
. - Значения курсорных переменных не могут храниться в элементах вложенных таблиц, ассоциативных массивов или
VARRAY
. ТипREF CURSOR
не может использоваться для задания типа элементов коллекций.