Try it here
Subscribe
PL/SQL Implicit and Explicit Cursor

PL/SQL Cursor

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.

Attribute Description
%FOUNDIts return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at least one row or more rows or a SELECT INTO statement returned one or more rows. Otherwise it returns FALSE.
%NOTFOUNDIts return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise it returns FALSE. It is a just opposite of %FOUND.
%ISOPENIt always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements.
%ROWCOUNTIt returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.
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;

Writer profile pic

Uk01 on Nov 19, 2015 at 12:11 am


If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.



Post Comment

Comments( 0)

×

Forgot Password

Please enter your email address below and we will send you information to change your password.