Курсор Oracle PL/SQL: неявный, явный, цикл For с примером

Что такое КУРСОР в PL/SQL?

Курсор является указателем на эту контекстную область. Oracle создает контекстную область для обработки оператора SQL, которая содержит всю информацию об этом операторе.

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

Курсор бывает двух типов.

  • Неявный курсор
  • Явный курсор

Неявный курсор

Всякий раз, когда в базе данных происходят какие-либо операции DML, создается неявный курсор, который содержит строки, затронутые в этой конкретной операции. Этим курсорам нельзя дать имя, и, следовательно, ими нельзя управлять или обращаться к ним из другого места кода. Мы можем ссылаться только на самый последний курсор через атрибуты курсора.

Явный курсор

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

Ниже приведены шаги по работе с явными курсорами.

  • Объявление курсора Объявление курсора просто означает создание одной именованной области контекста для оператора SELECT, которая определена в части объявления. Имя этой контекстной области совпадает с именем курсора.
  • Открывающий курсорОткрытие курсора даст указание PL/SQL чтобы выделить память для этого курсора. Это подготовит курсор к выборке записей.
  • Получение данных из курсораВ этом процессе выполняется оператор SELECT, и выбранные строки сохраняются в выделенной памяти. Теперь они называются активными наборами. Извлечение данных из курсора — это действие на уровне записи, что означает, что мы можем получить доступ к данным по записи. Каждый оператор выборки извлекает один активный набор и содержит информацию об этой конкретной записи. Этот оператор аналогичен оператору «SELECT», который извлекает запись и присваивает ее переменной в предложении «INTO», но он не генерирует никаких исключений.
  • Закрытие курсораКак только вся запись будет получена, нам нужно закрыть курсор, чтобы освободить память, выделенную для этой контекстной области.

Синтаксис

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;
  • В приведенном выше синтаксисе часть объявления содержит объявление курсора и переменную курсора, в которой будут назначены извлеченные данные.
  • Курсор создается для оператора SELECT, указанного в объявлении курсора.
  • На этапе выполнения объявленный курсор открывается, извлекается и закрывается.

Атрибуты курсора

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

Атрибут курсора Описание
%НАЙДЕННЫЙ Он возвращает логический результат «ИСТИНА», если последняя операция выборки успешно извлекла запись, в противном случае он вернет ЛОЖЬ.
%НЕ НАЙДЕНО Это работает противоположно %FOUND: он вернет «ИСТИНА», если последняя операция выборки не смогла получить какую-либо запись.
%ОТКРЫТ Он возвращает логический результат «ИСТИНА», если данный курсор уже открыт, в противном случае он возвращает «ЛОЖЬ».
% ROWCOUNT Он возвращает числовое значение. Он дает фактическое количество записей, на которые повлияла активность DML.

Пример явного курсора:

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

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

Курсор Oracle PL/SQL

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
lv_emp_name emp.emp_name%type;

BEGIN
OPEN guru99_det;

LOOP
FETCH guru99_det INTO lv_emp_name;
IF guru99_det%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name);
END LOOP;
Dbms_output.put_line(‘Total rows fetched is‘||guru99_det%R0WCOUNT);
CLOSE guru99_det;
END:
/

Результат

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY 
Total rows fetched is 3

Код Пояснение

  • Строка кода 2: объявление курсора guru99_det для оператора «SELECT emp_name FROM emp».
  • Строка кода 3: объявление переменной lv_emp_name.
  • Строка кода 5: Открытие курсора guru99_det.
  • Строка кода 6: Настройка оператора базового цикла для извлечения всех записей в таблице emp.
  • Строка кода 7: Извлекает данные guru99_det и присваивает значение lv_emp_name.
  • Строка кода 9: Использование атрибута курсора «%NOTFOUND», чтобы определить, выбрана ли вся запись в курсоре. Если оно получено, оно вернет «ИСТИНА», и элемент управления выйдет из цикла, в противном случае элемент управления продолжит извлекать данные из курсора и печатать данные.
  • Строка кода 11: Условие EXIT для оператора цикла.
  • Строка кода 12: Распечатайте полученное имя сотрудника.
  • Строка кода 14: Использование атрибута курсора «%ROWCOUNT» для определения общего количества записей, которые были затронуты/выбраны в курсоре.
  • Строка кода 15: После выхода из цикла курсор закрывается и выделенная память освобождается.

Оператор циклического курсора FOR

Оператор FOR LOOP можно использовать для работы с курсорами. Мы можем указать имя курсора вместо ограничения диапазона в операторе цикла FOR, чтобы цикл работал от первой записи курсора до последней записи курсора. Переменная курсора, открытие курсора, выборка и закрытие курсора будут выполняться неявно циклом FOR.

Синтаксис

DECLARE
CURSOR <cursor_name> IS <SELECT statement>;
BEGIN
  FOR I IN <cursor_name>
  LOOP
  .
  .
  END LOOP;
END;
  • В приведенном выше синтаксисе часть объявления содержит объявление курсора.
  • Курсор создается для оператора SELECT, указанного в объявлении курсора.
  • На этапе выполнения объявленный курсор устанавливается в цикле FOR, и в этом случае переменная цикла «I» будет вести себя как переменная курсора.

Курсор Oracle для примера цикла:
В этом примере мы проецируем все имена сотрудников из таблицы emp, используя цикл курсора-FOR.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp; 
BEGIN
FOR lv_emp_name IN guru99_det
LOOP
Dbms_output.put_line(‘Employee Fetched:‘||lv_emp_name.emp_name);
END LOOP;
END;
/

Результат

Employee Fetched:BBB 
Employee Fetched:XXX
Employee Fetched:YYY

Код Пояснение

  • Строка кода 2: объявление курсора guru99_det для оператора «SELECT emp_name FROM emp».
  • Строка кода 4: Создание цикла FOR для курсора с переменной цикла lv_emp_name.
  • Строка кода 5: Печать имени сотрудника на каждой итерации цикла.
  • Строка кода 8: Выйти из цикла

Примечание: В цикле Cursor-FOR атрибуты курсора использовать нельзя, поскольку открытие, выборка и закрытие курсора выполняются неявно с помощью Цикл FOR.