Работа с явными курсорами в PL/SQL

Явный курсор представляет собой команду SELECT, явно определенную в разделе объяв­лений программы. При объявлении явного курсора ему присваивается имя. Для команд INSERT, UPDATE, MERGE и DELETE явные курсоры определяться не могут.

Определив команду SELECT как явный курсор, программист получает контроль над основными стадиями выборки информации из базы данных Oracle. Он определяет, когда открыть курсор (OPEN), когда выбрать из него строки (FETCH), сколько выбрать строк и когда закрыть курсор с помощью команды CLOSE. Информация о текущем состоянии курсора доступна через его атрибуты. Именно высокая детализация контроля делает явные курсоры бесценным инструментом для программиста.

Рассмотрим пример:

1	FUNCTION	jealousy_level (
2	   NAME_IN IN	friends.NAME%TYPE)	RETURN NUMBER
3	AS
4	   CURSOR jealousy_cur
5   	IS
6	      SELECT location FROM friends
7	      WHERE NAME = UPPER (NAME_IN);
8
8	   jealousy_rec	jealousy_cur%ROWTYPE;
9	   retval	NUMBER;
10	BEGIN
11	   OPEN jealousy_cur;
13
12	   FETCH jealousy_cur INTO jealousy_rec;
15
13	   IF jealousy_cur%FOUND
14	   THEN
15	      IF	jealousy_rec.location = 'PUERTO RICO'
16	         THEN retval := 10;
17	      ELSIF jealousy_rec.location = 'CHICAGO'
18	         THEN retval := 1;
19	      END IF;
20	   END IF;
24
21	   CLOSE jealousy_cur;
26
22	   RETURN retval;
23	EXCEPTION
24	   WHEN OTHERS THEN
25	      IF jealousy_cur%ISOPEN	THEN
26	         CLOSE jealousy_cur;
27	      END IF;
28	END;

В этом блоке PL/SQL выполняются следующие операции с курсором.

В нескольких ближайших разделах подробно рассматривается каждая из перечисленных операций. Термин «курсор» в них относится к явным курсорам, если только в тексте явно не указано обратное.

Объявление явного курсора

Чтобы получить возможность использовать явный курсор, его необходимо объявить в разделе объявлений блока PL/SQL или пакета:

CURSOR имя_курсора [ ( [ параметр [, параметр ...] ) ]
   [ RETURN спецификация_геЕигп ]
   IS команда_SELECT
      [FOR UPDATE [OF [список_столбцов]];

Здесь имя курсора — имя объявляемого курсора; спеиифишция_те?ит — необязательная секция RETURN; KOMaHdaSELECT — любая допустимая SQL-команда SELECT. Курсору также могут передаваться параметры (см. далее раздел «Параметры курсора»). Нако­нец, после команды SELECT...FOR UPDATE можно задать список столбцов для обновления (также см. далее). После объявления курсор открывается командой OPEN, а выборка строк из него осуществляется командой FETCH.

Несколько примеров объявлений явных курсоров.

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

CURSOR company_cur IS
   SELECT company_id FROM company;

• Курсор с параметрами. Результирующий набор строк этого курсора содержит единственную строку с именем компании, соответствующим значению переданного параметра:

CURSOR name_cur (company_id_in IN NUMBER)
IS
   SELECT name FROM company
    WHERE company_id = company_id_in;

• Курсор с предложением RETURN. Результирующий набор строк этого курсора содержит все данные таблицы employee для подразделения с идентификатором 10:

CURSOR emp_cur
    RETURN employees%ROWTYPE IS
    SELECT * FROM employees WHERE
 department_id = 10;

Имя курсора

Имя явного курсора должно иметь длину до 30 символов и соответствовать тем же правилам, что и остальные идентификаторы PL/SQL. Имя курсора не является пере­менной — это идентификатор указателя на запрос. Имени курсора не присваивается значение, его нельзя применять в выражениях. Курсор используется только в командах OPEN, CLOSE и FETCH, а также для уточнения атрибута курсора.

Объявление курсора в пакете

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

PACKAGE book_info
IS
   CURSOR titles_cur 
   IS
      SELECT title
       FROM books;
   CURSOR books_cur (title_filter_in IN books.title%TYPE)
      RETURN books%ROWTYPE 
   IS
      SELECT *
      FROM books
      WHERE title LIKE title_filter_in;
END;

Первый курсор titles_cur возвращает только названия книг. Второй, books_cur, воз­вращает все строки таблицы books, в которых названия книг соответствуют шаблону, заданному в качестве параметра курсора (например, «Все книги, содержащие строку 'PL/SQL'»). Обратите внимание: во втором курсоре используется секция RETURN, которая объявляет структуру данных, возвращаемую командой FETCH.

В секции RETURN могут быть указаны любые из следующих структур данных:

•  Запись, определяемая на основе строки таблицы данных с помощью атрибута %ROWTYPE.
•  Запись, определяемая на основе другого, ранее объявленного курсора, также с по­мощью атрибута %rowtype.
•  Запись, определенная программистом.

Количество выражений в списке выборки курсора должно соответствовать количеству столбцов записи имя_таблицы%ROWTYPE, Kypcop%ROWTYPE или тип записи. Типы данных элементов тоже должны быть совместимы. Например, если второй элемент списка вы­борки имеет тип NUMBER, то второй столбец записи в секции RETURN не может иметь тип VARCHAR2 или BOOLEAN.

Прежде чем переходить к подробному рассмотрению секции RETURN и ее преимуществ, давайте сначала разберемся, для чего вообще может понадобиться объявление курсоров в пакете? Почему не объявить явный курсор в той программе, в которой он использу­ется — в процедуре, функции или анонимном блоке?

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

Также стоит рассмотреть возможность создания функции, возвращающей курсорную переменную на базе REF CURSOR. Вызывающая программа осуществляет выборку строк через курсорную переменную. За дополнительной информацией обращайтесь к разделу «Курсорные переменные и REF CURSOR».

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

А теперь давайте разберемся с секцией RETURN. У объявления курсора в пакете имеется одна интересная особенность: заголовок курсора может быть отделен от его тела. Такой заголовок, больше напоминающий заголовок функции, содержит информацию, которая необходима программисту для работы: имя курсора, его параметры и тип возвращаемых данных. Телом курсора служит команда SELECT. Этот прием продемонстрирован в новой версии объявления курсора books_cur в пакете book_info:

PACKAGE book_info
IS
   CURSOR books_cur (title_filter_in IN books.title%TYPE)
      RETURN books%ROWTYPE;
END;

PACKAGE BODY book_info
IS
   CURSOR books_cur (title_filter_in IN books.title%TYPE)
      RETURN books%ROWTYPE
   IS
      SELECT *
      FROM books
      WHERE title LIKE title_filter_in;
END;

Все символы до ключевого слова IS образуют спецификацию, а после IS следует тело курсора. Разделение объявления курсора может служить двум целям.

•  Сокрытие информации. Курсор в пакете представляет собой «черный ящик». Это удобно для программистов, потому что им не нужно ни писать, ни даже видеть команду SELECT. Достаточно знать, какие записи возвращает этот курсор, в каком порядке и какие столбцы они содержат. Программист, работающий с пакетом, ис­пользует курсор как любой другой готовый элемент.
•  Минимум перекомпиляции. Если скрыть определение запроса в теле пакета, то изменения в команду SELECT можно будет вносить, не меняя заголовок курсора в спецификации пакета. Это позволяет совершенствовать, исправлять и повторно компилировать код без перекомпиляции спецификации пакета, благодаря чему за­висящие от этого пакета программы не будут помечены как недействительные и их также не нужно будет перекомпилировать.

Открытие явного курсора

Использование курсора начинается с его определения в разделе объявлений. Далее объявленный курсор необходимо открыть. Синтаксис оператора OPEN очень прост:

OPEN имя_курсора [ ( аргумент [, аргумент ...] ) ];

Здесь имякурсора — это имя объявленного ранее курсора, а аргумент — значение, передаваемое курсору, если он объявлен со списком параметров.

Oracle также поддерживает синтаксис FOR при открытии курсора, который ис­пользуется как для курсорных переменных (см. раздел «Курсорные переменные и REF CURSOR»), так и для встроенного динамического SQL.

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

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

Более того, если команда SELECT содержит секцию FOR UPDATE, все идентифицируемые курсором строки блокируются при его открытии.

При попытке открыть уже открытый курсор PL/SQL выдаст следующее сообщение об ошибке:

ORA-06511: PL/SQL: cursor already open

Поэтому перед открытием курсора следует проверить его состояние по значению атри­бута %isopen:

IF NOT company_cur%ISOPEN
THEN
   OPEN company_cur;
END IF;

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

Если в программе выполняется цикл FOR с использованием курсора, этот курсор не нуждается в явном открытии (выборке данных, закрытии). Ядро PL/SQL делает это автоматически.

Выборка данных из явного курсора

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

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

Здесь имя курсора — имя курсора, из которого выбирается запись, а запись или список переменных — структуры данных PL/SQL, в которые копируется следующая строка активного набора записей. Данные могут помещаться в запись PL/SQL (объявленную с атрибутом %ROWTYPE или объявлением TYPE) или в переменные (переменные PL/SQL или переменные привязки — как, например, в элементы Oracle Forms).

Примеры явных курсоров

Следующие примеры демонстрируют разные способы выборки данных.

•  Выборка данных из курсора в запись PL/SQL:

DECLARE
   CURSOR company_cur is SELECT ...; 
   company_rec company_cur%ROWTYPE;
BEGIN
   OPEN company_cur;
   FETCH company_cur INTO company_rec;

•  Выборка данных из курсора в переменную:

FETCH new_balance_cur INTO new_balance_dollars;

•  Выборка данных из курсора в строку таблицы PL/SQL, переменную и переменную привязки Oracle Forms:

FETCH emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary;

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

Выборка после обработки последней строки

Открыв курсор, вы по очереди выбираете из него строки, пока они все не будут исчер­паны. Однако и после этого можно выполнять команду FETCH.

Как ни странно, в этом случае PL/SQL не инициирует исключение. Он просто ничего не делает. Поскольку выбирать больше нечего, значения переменных в секции INTO команды FETCH не изменяются. Иначе говоря, команда FETCH не устанавливает значения этих переменных равными NULL.

Следовательно, если вам понадобится узнать, успешно ли прошла выборка очередной строки, проверка переменных из списка INTO ничего не даст. Вместо этого следует про­верить атрибут %FOUND или %NOTFOUND (см. далее раздел «Атрибуты явных курсоров»).

Псевдонимы столбцов явного курсора

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

Псевдоним (alias) столбца представляет собой альтернативное имя, указанное в ко­манде SELECT для столбца или выражения. Задав подходящие псевдонимы в SQL*Plus, можно вывести результаты произвольного запроса в удобочитаемом виде. В подоб­ных ситуациях псевдонимы не являются обязательными. С другой стороны, при использовании явных курсоров псевдонимы вычисляемых столбцов необходимы в следующих случаях:

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

Рассмотрим следующий запрос. Команда SELECT выбирает названия всех компаний, заказывавших товары в течение 2001 года, а также общую сумму заказов (предпола­гается, что для текущего экземпляра базы данных по умолчанию используется маска форматирования DD-MON-YYYY):

SELECT company_name, SUM (inv_amt)
      FROM company c, invoice i 
   WHERE c.company_id = i.company_id
      AND i.invoice_date BETWEEN '01-JAN-2001' AND '31-DEC-2001';

При выполнении этой команды в SQL*Plus будет получен следующий результат:

Как видите, заголовок столбца SUM (INV_AMT) плохо подходит для отчета, но для простого просмотра данных он вполне годится. Теперь выполним тот же запрос в программе PL/ SQL с использованием явного курсора и добавим псевдоним столбца:

DECLARE
   CURSOR comp_cur IS
      SELECT c.name, SUM (inv_amt) total_sales 
         FROM company C, invoice I 
       WHERE C.company_id = I.company_id
      AND I.invoice_date BETWEEN '01-JAN-2001' AND '31-DEC-2001'; 
      comp_rec comp_cur%ROWTYPE;
BEGIN
   OPEN comp_cur;
   FETCH comp_cur INTO comp_rec;
END;

Без псевдонима я не смогу сослаться на столбец в структуре записи comp_rec. При на­личии псевдонима с вычисляемым столбцом можно работать точно так же, как с любым другим столбцом запроса:

IF comp_rec.total_sales > 5000
THEN
      DBMS_OUTPUT.PUT_LINE
         (' You have exceeded your credit limit of $5000 by ' ||
         TO_CHAR (comp_rec.total_sales - 5000, '$9999'));
END IF;

При выборке строки в запись, объявленную с атрибутом %ROWTYPE, доступ к вычис­ляемому столбцу можно будет получить только по имени — ведь структура записи определяется структурой самого курсора.

Закрытие явного курсора

Когда-то в детстве нас учили прибирать за собой, и эта привычка осталась у нас (хотя и не у всех) на всю жизнь. Оказывается, это правило играет исключительно важную роль и в программировании, и особенно когда дело доходит до управления курсорами. Никогда не забывайте закрыть курсор, если он вам больше не нужен!

Синтаксис команды CLOSE:

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

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

•  Если курсор объявлен и открыт в процедуре, не забудьте его закрыть после завер­шения работы с ним; в противном случае в вашем коде возникнет утечка памяти. Теоретически курсор (как и любая структура данных) должен автоматически закры­ваться и уничтожаться при выходе из области действия. Как правило, при выходе из процедуры, функции или анонимного блока PL/SQL действительно закрывает все от­крытые в нем курсоры. Но этот процесс связан с определенными затратами ресурсов, поэтому по соображениям эффективности PL/SQL иногда откладывает выявление и закрытие открытых курсоров. Курсоры типа REF CURSOR по определению не могут быть закрыты неявно. Единственное, в чем можно быть уверенным, так это в том, что по завершении работы «самого внешнего» блока PL/SQL, когда управление будет возвращено SQL или другой вызывающей программе, PL/SQL неявно закроет все открытые этим блоком или вложенными блоками курсоры, кроме REF CURSOR. В статье «Cursor reuse in PL/SQL static SQL» из Oracle Technology Network приво­дится подробный анализ того, как и когда PL/SQL закрывает курсоры. Вложенные анонимные блоки — пример ситуации, в которой PL/SQL не осуществляет неяв­ное закрытие курсоров. Интересная информация по этой теме приведена в статье Джонатана Генника «Does PL/SQL Implicitly Close Cursors?».
•  Если курсор объявлен в пакете на уровне пакета и открыт в некотором блоке или программе, он останется открытым до тех пор, пока вы его явно не закроете, или до завершения сеанса. Поэтому, завершив работу с курсором пакетного уровня, его следует немедленно закрыть командой CLOSE (и кстати, то же самое следует делать в разделе исключений):

BEGIN
   OPEN my_package.my_cursor;
   ... Работаем с курсором
   CLOSE my_package.my_cursor;
EXCEPTION
   WHEN OTHERS
   THEN
      IF mypackage.my_cursor%ISOPEN
      THEN CLOSE my_package.my_cursor;
   END IF;
END;

•  Курсор можно закрывать только в том случае, если ранее он был открыт; в про­тивном случае будет инициировано исключение INVALID_CURS0R. Состояние курсора проверяется с помощью атрибута %ISOPEN:

IF company_cur%ISOPEN
THEN
   CLOSE company_cur;
END IF;

•  Если в программе останется слишком много открытых курсоров, их количество может превысить значение параметра базы данных OPEN_CURSORS. Получив сообще­ние об ошибке, прежде всего убедитесь в том, что объявленные в пакетах курсоры закрываются после того, как надобность в них отпадет.

Атрибуты явных курсоров

Oracle поддерживает четыре атрибута (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) для полу­чения информации о состоянии явного курсора. Ссылка на атрибут имеет следующий синтаксис: курсор%атрибут

Здесь курсор — имя объявленного курсора.

Значения, возвращаемые атрибутами явных курсоров, приведены в табл. 1.

Таблица 1. Атрибуты явных курсоров

Значения атрибутов курсоров до и после выполнения различных операций с ними указаны в табл. 2.

Работая с атрибутами явных курсоров, необходимо учитывать следующее:

•  При попытке обратиться к атрибуту %FOUND, %NOTFOUND или %ROWCOUNT до открытия курсора или после его закрытия Oracle инициирует исключение INVALID CURSOR (ORA-01001).
•  Если после первого выполнения команды FETCH результирующий набор строк ока­жется пустым, атрибуты курсора возвращают следующие значения: %FOUND = FALSE, %NOTFOUND = TRUE и %ROWCOUNT = 0.
•  При использовании BULK COLLECT атрибут %ROWCOUNT возвращает количество строк, извлеченных в заданные коллекции.

 Таблица 2. Значения атрибутов курсоров

Использование всех этих атрибутов продемонстрировано в следующем примере:

PACKAGE bookinfo_pkg 
IS
   CURSOR bard_cur
      IS SELECT title, date_published 
      FROM books
      WHERE UPPER(author) LIKE 'SHAKESPEARE%';
END bookinfo_pkg;

DECLARE
   bard_rec bookinfo_pkg.bard_cur%ROWTYPE;
BEGIN
/* Проверяем, не открыт ли уже курсор.
Это возможно, поскольку курсор определен в пакете.
Если курсор открыт, закрываем его и открываем повторно, чтобы получить "свежий" результирующий набор.
*/
IF bookinfo_pkg.bard_cur%ISOPEN THEN
   CLOSE bookinfo_pkg.bard_cur;
END IF;

OPEN bookinfo_pkg.bard_cur;
-- По очереди выбираем строки. Перебор останавливается 
-- после вывода первых пяти произведений Шекспира 
-- или когда будут исчерпаны все строки.

LOOP
   FETCH bookinfo_pkg.bard_cur INTO bard_rec;
   EXIT WHEN bookinfo_pkg.bard_cur%NOTFOUND
      OR bookinfo_pkg.bard_cur%ROWCOUNT > 5;
   DBMS_OUTPUT.put_line (
      bookinfo_pkg.bard_cur%ROWCOUNT
      II ') '
      || bard_rec.title || ', издана в '
      || TO_CHAR (bard_rec.date_published, 'YYYY')
   );
END LOOP;
CLOSE bookinfo_pkg.bard_cur;

END;

Параметры курсора

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

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

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

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

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

CURSOR joke_cur IS
   SELECT name, category, last_used_date
       FROM Jokes;

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

CURSOR joke_cur IS
   SELECT name, category, last_used_date
       FROM jokes
   WHERE category = 'HUSBAND';

Для выполнения этой задачи мы не стали использовать параметры, да они и не нужны. В данном случае курсор возвращает все строки, относящиеся к конкретной категории. Но как быть, если при каждом обращении к этому курсору категория изменяется?

Курсоры с параметрами

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

PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE)
IS
   /*
   || Курсор со списком параметров, состоящим || из единственного строкового параметра.
   */
   CURSOR joke_cur (category_in IN VARCHAR2)
   IS
      SELECT name, category,
          last_used_date FROM Joke
      WHERE category = UPPER ( category_in); joke_rec
    joke_cur%ROWTYPE;
    
BEGIN
   /* Теперь при открытии курсора ему передается аргумент */
   OPEN joke_cur (main_category_in);
   FETCH joke_cur INTO joke_rec;

Между именем курсора и ключевым словом IS теперь содержится список параметров. Жестко закодированное значение HUSBAND в предложении WHERE заменено ссылкой на параметр UPPER (category_in). При открытии курсора можно будет задать значение HUSBAND, husband или HuSbAnD — курсор все равно будет работать. Название категории, для которой курсор должен вернуть строки таблицы joke, задается в операторе OPEN (в скобках) в виде литерала, константы или выражения. В момент открытия курсора производится разбор команды SELECT, а параметр связывается со значением. Затем определяется результирующий набор строк — и курсор готов к выборке.

Открытие курсора с параметрами

Новый курсор можно открывать с указанием любой категории:

OPEN joke_cur ( Jokes_pkg.category);
OPEN joke_cur ('husband');
OPEN joke_cur ('politician');
OPEN joke_cur ( Jokes_pkg.relation || '-IN-LAW');

Параметры курсора чаще всего используются в условии WHERE, но ссылаться на них можно и в других местах команды SELECT:

DECLARE
   CURSOR joke_cur (category_in IN ARCHAR2)
   IS
      SELECT name, category_in, last_used_date FROM joke
      WHERE category = UPPER (category_in);

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

Область действия параметра курсора

Область действия параметра курсора ограничивается этим курсором. На параметр курсо­ра нельзя ссылаться за пределами команды SELECT, связанной с курсором. Приведенный ниже фрагмент PL/SQL не компилируется, потому что идентификатор program_name не является локальной переменной в блоке. Это формальный параметр курсора, который определен только внутри курсора:

DECLARE
   CURSOR scariness_cur (program_name VARCHAR2)
   IS
      SELECT SUM (scary_level) total_scary_level 
      FROM tales_from_the_crypt 
      WHERE prog_name = program_name;
      
BEGIN
   program_name := 'THE BREATHING MUMMY'; /* Недопустимая ссылка */
   OPEN scariness_cur (program_name);
   ....
   CLOSE scariness_cur;
END;

Режимы параметра курсора

Синтаксис параметров курсоров очень похож на синтаксис процедур и функций — за исключением того, что параметры курсоров могут быть только параметрами IN. Для параметров курсоров нельзя задавать режимы OUT или IN OUT. Эти режимы позволяют передавать и возвращать значения из процедур, что не имеет смысла для курсора. Существует только один способ получения информации от курсора: выборка записи и копирование значений из списка столбцов в секции INTO

Значения параметров по умолчанию

Параметрам курсоров могут присваиваться значения по умолчанию. Пример курсора со значением параметра по умолчанию:

CURSOR emp_cur (emp_id_in NUMBER := 0)
IS
   SELECT employee_id, emp_name 
   FROM employee
   WHERE employee_id = emp_id_in;

Поскольку для параметра emp_id_in определено значение по умолчанию, в команде FETCH его значение можно не указывать. В этом случае курсор вернет информацию о сотруднике с кодом 0.

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL... 4655 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48

Встроенные методы коллекций PL... 14860 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Работа с числами в PL/SQL на п... 45352 просмотров Antoniy Mon, 28 May 2018, 16:45:11

Основы языка PL/SQL: использов... 4702 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03