Open Cursor and fetch Records in PL/SQL

After defining a cursor, you must open it before fetching the records. The records can be fetched into a record or a variable list. The record or variable list can be used for further processing of the data.

To open a cursor in PL/SQL and fetch data from it, you generally follow a sequence of steps: declaration, opening, fetching, and closing. Here's a breakdown of the syntax for opening a cursor and using variables to fetch rows:

1. Declaration: First, declare the cursor with the specific SQL query you want to use.
   

   CURSOR cursor_name IS select_statement;
   

2. Opening: Open the cursor to establish the result set.
   

   OPEN cursor_name;
   

3. Fetching: After opening the cursor, fetch rows from the cursor into PL/SQL variables or records. You need to declare variables or a record that matches the structure of the cursor's select list.
   For variables:

   FETCH cursor_name INTO variable1, variable2, ...;
   

   
   For a record (where the record's structure matches the cursor's select list):
   

   FETCH cursor_name INTO record_name;
   

4. Closing: Once done with fetching data, close the cursor to release resources.
   

   CLOSE cursor_name;
   

Example: Assuming you have a table named `employees` with columns `employee_id` and `employee_name`, here's an example that demonstrates the steps:

1. Declaration:
   

   CURSOR emp_cursor IS SELECT employee_id, employee_name FROM employees;
   

2. Variable Declaration (matching the cursor's select list):
   

   DECLARE
   	v_employee_id employees.employee_id%TYPE;
   	v_employee_name employees.employee_name%TYPE;
   

3. Opening, Fetching, and Closing:
   

   BEGIN
   	OPEN emp_cursor;  -- Open the cursor
   
   	LOOP
   		FETCH emp_cursor INTO v_employee_id, v_employee_name;  -- Fetch data into variables
   
   		EXIT WHEN emp_cursor%NOTFOUND;  -- Exit loop when no more rows are available
   
   		-- Process the fetched row, for example, display the employee's details
   		DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_employee_name);
   	END LOOP;
   
   	CLOSE emp_cursor;  -- Close the cursor
   END;
   

In this example, the `emp_cursor` is declared with a `SELECT` statement to fetch `employee_id` and `employee_name` from the `employees` table. Variables `v_employee_id` and `v_employee_name` are declared to match the structure of the fetched data. The cursor is then opened, and a loop is used to fetch each row into the variables. The fetched data is processed inside the loop (in this case, simply outputting the employee details), and the loop exits when there are no more rows to fetch. Finally, the cursor is closed to free up resources.

The following diagram explains the syntax for opening a cursor and the variables in the syntax. It also shows an example of using the syntax to open a cursor. In this example, no arguments are specified.
Opening Cursor in PL/SQL

cursor_name
Name of the cursor
argument_1,…,argument_n
A series of arguments. The arguments specified are optional.
OPEN pets;
Name of the cursor

After declaring a cursor variable, you can open it with the OPEN FOR statement, which does the following:

1. Associates the cursor variable with a query (typically, the query returns multiple rows)
   The query can include placeholders for bind variables, whose values you specify in the USING clause of the OPEN FOR statement.
2. Allocates database resources to process the query
3. Processes the query; that is:
   1. Identifies the result set If the query references variables, their values affect the result set.
   2. If the query has a FOR UPDATE clause, locks the rows of the result set
4. Positions the cursor before the first row of the result set

You need not close a cursor variable before reopening it (that is, using it in another OPEN FOR statement). After you reopen a cursor variable, the query previously associated with it is lost. When you no longer need a cursor variable, close it with the CLOSE statement, thereby allowing its resources to be reused. After closing a cursor variable, you cannot fetch records from its result set or reference its attributes. If you try, PL/SQL raises the predefined exception INVALID_CURSOR. You can reopen a closed cursor variable.

When opening a cursor, the arguments specified are optional. When you open a cursor, PL/SQL executes the query defined within the cursor and identifies the result set pertaining to the SELECT statement, that is, the records from all the tables that meet the criteria defined within the WHERE clause. The OPEN statement does not actually retrieve the records. This action is done by the FETCH statement.

The following diagram explains the syntax for fetching records and the variables in the syntax. It also shows an example of using the syntax for fetching records. In this example, the records are fetched into a variable list (pet_name1, pet_count1).

DECLARE
CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name;
name1 employees.last_name%TYPE;
name2 employees.last_name%TYPE;
name3 employees.last_name%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO name1; -- this fetches first row
FETCH c1 INTO name2; -- this fetches second row
FETCH c1 INTO name3; -- this fetches third row
CLOSE c1;
END;

If you fetch past the last row in the result set, the values of the target variables are undefined. Eventually, the FETCH statement fails to return a row. When that happens, no exception is raised. To detect the failure, use the cursor attribute %FOUND or %NOTFOUND.

Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter, %FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows, or a SELECT INTO statement returned one or more rows. Otherwise, %FOUND yields FALSE.
In Example 6–6, a developer uses %FOUND to insert a row if a delete succeeds.
Example 6–6 Using SQL%FOUND

CREATE TABLE dept_temp AS SELECT * FROM departments;
DECLARE
dept_no NUMBER(4) := 270;
BEGIN
DELETE FROM dept_temp WHERE department_id = dept_no;
IF SQL%FOUND THEN -- delete succeeded
INSERT INTO dept_temp VALUES (270, 'Personnel', 200, 1700);
END IF;
END;
/

The number of columns within the cursor definition should match the columns within the FETCH statement. In a cursor block without a loop, the number of variables defined should be equal to the number of records fetched within the cursor. In the next lesson, you will learn how to close a cursor.