Cursor in Database Management System(DBMS).

CURSORS:

Cursor are defined as database object use to iterate over the rows of the table. When a cursor is created , its starts with first row and move towards last row i.e any instance of time cursor access one and only one row.

Cursor are of following types:

1. Implicit cursor

2. Explicit cursor

1. Implicit cursor: Such cursor are created and destroyed by the database itself. Implicit cursor

are created for every update that is perform in the database  i.e for every statement such as insertion ,

deletion , updation. An Implicit cursor created and assign a name as SQL.

SQL cursor has following variable associated with its, which can be used to track the rows affected in the table:

a. % FOUND

b. % NOTFOUND

c. % ISOPEN

d. % ROWCOUNT

%FOUND: is used to track whether any row is found in the table. If any row is found it return true , otherwise false. It can be used as SQL % found.

%NOTFOUND: It return true if no row is found in the table otherwise false. It can be used as SQL%not found.

%OPEN: It can be used to check whether the cursor is open or close. It return true if cursor is open otherwise false. It can be used as SQL%open. Such a variable place a role in case of explicit cursor.

%ROWCOUNT: It is used to count total number of row updated by an updates statement.

It can be used as SQL%ROW COUNT;

Example:

Write a block to increase quota by 50% of their sales for  the employee having sales more than quota and display total number of row updated if any row is found.

Syntax:

SET SERVEROUTPUT ON

DECLARE 

n integer;

BEGIN

UPDATE salesrep set

quota = quota + 0.5 * quota

WHERE sales > quota;

IF (SQL % FOUND) THEN

n=SQL%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE(N||'ROW UPDATED');

ELSE

DBMS_OUTPUT.PUT_LINE('NO ROW UPDATED');

END IF;

END;

/

2. Explicit Cursor:

Explicit cursor are created by programmer by writing a select query in declaration sections. Since a cursor iterates over rows of the table select query   will have always a query select start along with where clause and other clauses.

One cursor is created in declare section than it can be used in begin section by opening the cursor and after it has been used , it must be closed.

its syntax:

DECLARE 

CURSOR cursor_name IS 

SELECT * 

FROM  table_name

recordname tablename % ROWTYPE;

BEGIN

OPEN cursor_name

FETCH cursor_name INTO recordname 

-

-

-

CLOSE cursor_name;

END;

After the cursor is open it must be fetch into a record_name of %ROWTYPE variable , the record_name can be used to do manipulation over data as per requirement.

The variables available in Implicit cursor. Such as %FOUND ,%NOTFOUND , %ISOPEN and %ROWCOUNT are available with implicit cursor also with similar meaning.

Example:

Write a block to create a cursor which select all employee with sales > quota and display an output as employee with sales > quota are given as follow:

SET SERVEROUTPUT ON

DECLARE 

CURSOR empcur IS 

SELECT * 

FROM salesrep 

WHERE sales > quota ;

emprec salesrep % ROWTYPE;

BEGIN

OPEN empcur 

DBMS_OUTPUT.PUT_LINE('Employee name with sales above quota is as follow');

LOOP

FETCH empcur INTO emprec;

DBMS_OUTPUT.PUT_LINE(emprec.name);

EXIT WHEN (empcur % NOTFOUND);

END LOOP;

END;