PL/SQL Cursor

A cursor a temporary memory which holds the records of a sql statement.
A cursor is a pointer that points to a result of a query.

A cursor is used to referred to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors:

• Implicit Cursors
• Explicit Cursors

PL/SQL Implicit Cursors

The implicit cursors are automatically generated by Oracle while an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE is executed, if you don't use an explicit cursor for the statement.

Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor's information and statuses such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.

The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.

Example :

When you execute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement. It will return an error if there no data is selected.

DECLARE   
   total_records NUMBER(2);  
BEGIN  
   UPDATE emp  
   SET sal = sal + 1000;  
   IF SQL%NOTFOUND THEN  
      DBMS_OUTPUT.PUT_LINE('No employee updated');  
   ELSIF SQL%FOUND THEN  
      total_records := SQL%ROWCOUNT;  
      DBMS_OUTPUT.PUT_LINE( total_records || ' employees updated ');  
   END IF;   
END;

PL/SQL Explicit Cursors

An explicit cursor is an SELECT statement declared explicitly in the declaration section of the current block or a package specification. For an explicit cursor, you have the control over its execution cycle from OPEN, FETCH, and CLOSE.

Steps:

1. Declare the cursor to initialize in the memory.
2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.

Declare a cursor

CURSOR cursor_name IS query;

Open a cursor

OPEN cursor_name;

When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement. Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.

Fetch from a cursor

The FETCH statement places the contents of current row into variables. The syntax of FETCH statement is as follows:

FETCH cursor_name INTO variable_list;

Closing a cursor

CLOSE cursor_name;

Closing a cursor instructs Oracle to release allocated memory at an appropriate time.

If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of the block, procedure, and function ends.

However, you must explicitly close package-based cursors. If you close a cursor that is not opened, Oracle will raise an INVALID_CURSOR exception.

Example

DECLARE 
e_empno emp.empno%TYPE;
e_name emp.ename%TYPE;
e_hiredt emp.hiredate%TYPE;
CURSOR c_emp_dtl IS
SELECT empno, ename, hiredate FROM emp;
BEGIN
	OPEN c_emp_dtl;
	LOOP
		
	     FETCH c_emp_dtl INTO e_empno,e_name,e_hiredt;
	     EXIT WHEN c_emp_dtl%NOTFOUND;
	     dbms_output.put_line('ename :' || e_empno||' , empid :' || e_empno || ' , hiredate :'|| e_hiredt);
	END LOOP;
	CLOSE c_emp_dtl;
END;