Cursors : Implicit Cursors

Cursors:


When we execute a SQL statement from PL/SQL, the Oracle assigns a private work area for that statement and also manages the data specified by the SQL statement in the SGA.
The private work area contains information about the SQL statement and the set of data returned or affected by that statement.


Implicit Cursors:

PL/SQL declares and manages an implicit cursor every time we execute an DML (SELECT INTO,INSERT,UPDATE,DELETE).

SYNTAX:

SELECT column_list
      [BULK COLLECT] INTO PL/SQL variable list
      ...SQL SELECT statement...


A common use of implicit cursors is to perform a lookup based on a primary key.(returns only one row)

If we need to fetch multiple rows

1. Go for Explicit cursors.
2. BULK COLLECT INTO 


Implicit cursors can't be controlled.


Common Exceptions:

1. NO_DATA_FOUND
2. TO_MANY_ROWS


When ever these exceptions occur,execution of the current block terminates and control is passed to the execution section.

Best practice:

Include the implicit cursor as a separate PL/SQL BLOCK.



DECLARE
-------
-------
Begin
-------
-------
     Begin
        Select xxxx INTO xxxx from table where <conditions>; --<Implicit Cursor>
     Exception
        When NO_DATA_FOUND then
         xxxxxx;
        When TO_MANY_ROWS then
        xxxxxxx;
     END;
--------
--------
Exception
---------
---------
END;
/



EXAMPLE:

Create new email account in email_account TABLE

LOGIC FLOW: check if the entered email exists,
                          if exists return 'NOT AVAILABLE'
                          else 'AVAILABLE'


CREATE Function Check_email_availability
(entered_email in varchar2)
return varchar2

begin

   select email_address into return_value from email_account
   where email=entered_email;

   Return return_value;

Exception
 When NO_DATA_FOUND 
 then
      Return NULL;
 When TO_MANY_ROWS
 then
      DBMS_OUTPUT.PUT_LINE('DATA INTEGRITY');
      RAISE;
END;

-- CALLING PL/SQL BLOCK --

Declare
-------
-------
-------
Begin

  IF (check_email_availability) IS NULL

      'Email Available'
  else
  
      'Email is not Available'
  END IF;

-------
-------
-------
EXCEPTION
-------
-------
END;


IF      select email_address into return_value from email_account
        where email=entered_email;
doesn't return any row means the entered email is not used by anyone and can be used, but the implicit cursor raises NO_DATA_FOUND exception and we handle it and return NULL.


IF       select email_address into return_value from email_account
         where email=entered_email;
returns more than one, it means something wrong with the table design.



CURSOR ATTRIBUTES:

1. SQL%FOUND    : Returns TRUE if one row was fetched.
2. SQL%NOTFOUND : Returns FALSE if one row was fetched.
3. SQL%ROWCOUNT : Returns the number of rows fetched from the cursor.(SELECT INTO : 1)
4. SQL%ISOPEN   : Alway FALSE

All the implicit cursor attributes return NULL if no implicit cursors have yet been executed in the session.
Otherwise, the values of the attributes always refer to the most recently executed SQL statement, regardless
of the block or program from which the SQL statement was executed.