Re: sql%notfound and NO_DATA_FOUND exceptions.

From: Thodla Subrahmanyam <tsubrahm_at_PROBLEM_WITH_INEWS_GATEWAY_FILE>
Date: 1996/11/25
Message-ID: <57cb5g$sa6_at_eccdb1.pms.ford.com>#1/1

I think it works this way:

DECLARE
a table1.column1%TYPE;
b table1.column2%TYPE;
BEGIN
  SELECT column1,column2
    INTO a,b
    FROM table1
   WHERE x=y;

  code1; /* this will be executed only if the

             select returns only one record.
               If it returns more than one row -- goes to too_many_rows
	       If it does not return any rows  -- goes to no_data_found */

  DELETE FROM table1
        WHERE x=y;



  -- Normally,  you can use implicit cursor attributes 

  --            for the DML statements (Delete,Insert,Update

  --            Cursor Attributes : sql%found / sql%notfound

  --                                sql%isopen (always evaluates to false)


  --                                sql%rowcount (No. of rows affected)

  IF sql%found /* Atleast one row has been deleted */   THEN
    dosomething;
  ELSE
    dosomeotherthing; /* Could not find any rows to delete .

                         In this example,  this condition will never
                         be satisfied */

  END IF;   EXCEPTION
  WHEN no_data_found THEN

       code2;  /* this will be executed if the select does 
                  not return any data */

  WHEN OTHERS THEN NULL; /* This will be executed for all other exceptions */ END; Received on Mon Nov 25 1996 - 00:00:00 CET