OraclePLSQL--KINDS OF CURSOR AND EXPLANATION OF EXPLICIT CURSOR

KINDS OF CURSOR

AND

EXPLANATION OF EXPLICIT CURSOR

Anyone who has ever written a PL/SQL function or procedure that performs any looping logic knows the pain of choosing just the right type of cursor—or the pain of choosing the wrong type of cursor. Choosing the wrong type of cursor may result in your users, your peers, or your managers (or all of them) losing faith in your ability to serve the technical needs of the business requirements. Choosing the wrong type of cursor may also lead to a great amount of time debugging system slowdowns in production and, as what you may deem a worst-case scenario, a diminished paycheck. Given the potential pitfalls and consequences, every PL/SQL programmer should strive to choose a type of cursor that works best for each individual technical problem he must solve.

. The four types of cursors chapter are

·         Explicit

·         Implicit

·         Static REF Cursors

·         Dynamic REF Cursors

Your goal in writing any PL/SQL program that obtains sets of records for processing programmatically (either individually or in bulk) is to choose a type of cursor that allows you and the Oracle database to obtain the correct answer with the least amount of work. It is really just that simple. Of course, there are many types of cursors you can use in many situations. But should you? This is the question you need to ask yourself each time you write a PL/SQL program. Choose knowledgeably. Know first what business question you are trying to answer, and then choose the best programmatic tool to answer it quickly and correctly given each individual situation.

Explicit cursors

Explicit cursors are named pointers to rows and columns within the database. These structures are used whenever sequential processing of a portion of the database is required.

The Anatomy of an Explicit Cursor

1. OPEN: This step initializes the cursor and identifies the result set. Note that it does not actually have to assemble the result set. It just sets the point in time the result set will be "as of."

2.FETCH: This step executes repeatedly until all rows have been retrieved (unless you are using BULK COLLECT  which fetches all of the records at once).

3.CLOSE: This step releases the cursor once the last row has been processed.

Example:

·      Within the DECLARE clause a cursor declaration must be made. Any valid SQL statement is permitted. Cursor declarations may include joins of multiple tables, the ORDER BY clause to order the rows presented from the cursor, and so on. In the example, this cursor declaration is very simple and accesses all data within the EMPLOYEE table in random order.

·      Also within the DECLARE clause one must declare a structure with which to hold the data available from within the cursor. Any of the available declaration techniques are permitted so long as the types are compatible with the cursor data. In the example shown we use the %ROWTYPE declaration since this is compatible with the cursor data.

DECLARE

CURSOR Employees IS

SELECT *

FROM employee;

EmpRecord         employee%ROWTYPE;

BEGIN

OPEN Employees;

LOOP

FETCH Employees INTO EmpRecord;

EXIT WHEN Employees%NOTFOUND;

/*

Record-by-record processing occurs here. For the sake of simplicity, the

processing logic in this case is very limited.

*/

  dbms_output.put_line ('Employee ' || EmpRecord.LName || ' ' || EmpRecord.FName ||  ' ' ||

                        'earns ' || EmpRecord.Salary);

END LOOP;

  CLOSE Employees;

END;

anonymous block completed

Employee Bhutta Ali earns 55000

Employee Khan Sana earns 25000

Employee Shah Faz earns 25000

Employee Waseem Akram earns 38000

Employee Waqar Ahmed earns 30000

Employee Hiraa Hira earns 43000

Employee Saaad Saad earns 40000

Employee Wardhaa Alicia earns 25000