Cursors

Cursor : Cursor is a mechanism by which you can assign a name to a select statement and manipulate the information with in the SQL statement.A cursor is a reference to the system memory when an SQL statement is executed.

Cursor attributes are: 

• %FOUND : Returns true if a DML or SELECT statement affects at least one row.
• %NOTFOUND: Returns true if a DML or SELECT statement does not affect at least one row.
• %ROWCOUNT: Returns the number of rows affected by the DML or SELECT statement.
• %ISOPEN: Returns true if a cursor is in open state.
• %BULK_ROWCOUNT: Similar to %ROWCOUNT, except it is used in bulk operations.

--------------------------------------------------------------------------------------------------------

Implicit cursors : Implicit cursors are the one created automatically by oracle when you execute a query.

• Easy to code
• Vulnerable to data error
• Cannot control implicit cursor and the information in it.
• Whenever a DML statement (insert,update and delete) is issued an implicit cursor is associated with the statement.
• We can refer to the most recent implicit cursor as the SQL cursor.
• SQL%FOUND , SQL%NOTFOUND, SQL%ISOPEN, SQL%ROWCOUNT,

e.g

BEGIN

UPDATE cust set sal = 100000 where cutno=123;

IF SQL%NOTFOUND THEN 

           dbms_output.put_line('cust not found');

ELSE IF SQL%FOUND THEN

          dbms_output.put_line('cust updated');

END IF;

END;

-----------------------------------------------------------------------------------------------------------

Explicit cursors : Programmer defined cursors for gaining more control over the context area. Context area is the memory area for processing the SQL statements.

• Takes more code
• Gives more control

1) Select INTO.
2) Fetching from explicit cursors.
3) Using cursor FOR loop.
4) Execute immediate for dynamic queries.
5) Cursor variables.