Учебники

17) Oracle PL / SQL Cursor

Март 11, 2020

Что такое CURSOR в PL / SQL?

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

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

В этом уроке вы узнаете

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

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

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

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

Явный курсор

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

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

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

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

  • Открытие курсора

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

  • Извлечение данных из курсора

    In this process, the ‘SELECT’ statement is executed and the rows fetched is stored in the allocated memory. These are now called as active sets. Fetching data from the cursor is a record-level activity that means we can access the data in a record-by-record way.

    Each fetch statement will fetch one active set and holds the information of that particular record. This statement is same as ‘SELECT’ statement that fetches the record and assigns to the variable in the ‘INTO’ clause, but it will not throw any exceptions.

  • Closing the Cursor

    Once all the record is fetched now, we need to close the cursor so that the memory allocated to this context area will be released.

Syntax:

DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;
  • In the above syntax, the declaration part contains the declaration of the cursor and the cursor variable in which the fetched data will be assigned.
  • The cursor is created for the ‘SELECT’ statement that is given in the cursor declaration.
  • In execution part, the declared cursor is opened, fetched and closed.

Cursor Attributes

Both Implicit cursor and the explicit cursor has certain attributes that can be accessed. These attributes give more information about the cursor operations. Below are the different cursor attributes and their usage.

Cursor Attribute Description
%FOUND It returns the Boolean result ‘TRUE’ if the most recent fetch operation fetched a record successfully, else it will return FALSE.
%NOTFOUND This works oppositely to %FOUND it will return ‘TRUE’ if the most recent fetch operation could not able to fetch any record.
%ISOPEN It returns Boolean result ‘TRUE’ if the given cursor is already opened, else it returns ‘FALSE’
%ROWCOUNT It returns the numerical value. It gives the actual count of records that got affected by the DML activity.

Example 1: In this example, we are going to see how to declare, open, fetch and close the explicit cursor.

We will project all the employee’s name from emp table using a cursor. We will also use cursor attribute to set the loop to fetch all the record from the cursor.

SQL в 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: установка оператора цикла Basic для извлечения всех записей в таблице «emp».
  • Строка кода 7: извлекает данные guru99_det и присваивает значение lv_emp_name.
  • Строка кода 9: Использование атрибута курсора «% NOTFOUND», чтобы определить, выбраны ли все записи в курсоре. В случае выборки он вернет «ИСТИНА» и элемент управления выйдет из цикла, иначе элемент управления продолжит извлекать данные из курсора и печатать данные.
  • Строка кода 11: условие EXIT для оператора цикла.
  • Строка кода 12: распечатайте выбранное имя сотрудника.
  • Строка кода 14: Использование атрибута курсора «% ROWCOUNT», чтобы найти общее количество записей, которые были затронуты / извлечены в курсоре.
  • Строка кода 15: после выхода из цикла курсор закрывается и выделяемая память освобождается.

Оператор FOR Loop Cursor

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

Синтаксис: 

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

Example 1: In this example, we will project all the employee name from emp table using a cursor-FOR loop. 

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;
/

Output 

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

Code Explanation:

  • Code line 2: Declaring the cursor guru99_det for statement ‘SELECT emp_name FROM emp’.
  • Code line 4: Constructing the ‘FOR’ loop for the cursor with the loop variable lv_emp_name.
  • Code line 5: Printing the employee name in each iteration of the loop.
  • Code line 8: Exit the loop

Note: In Cursor-FOR loop, cursor attributes cannot be used since opening, fetching and closing of the cursor is done implicitly by FOR loop.

 

48
Share: