RE: Branching logic in PL/SQL, positive instead of exception

[Alec Macdonell]

Title: Branching logic in PL/SQL, positive instead of exception

I believe something like this might work.

 

DECLARE

 

   cursor test_csr is

      SELECT col1

          FROM test.table t

       WHERE exists(

             SELECT z.zip

                FROM zip_table

              WHERE t.zip = zip);

 

BEGIN

 

   OPEN test_csr;

 

   /* This step tests if any data is returned by the cursor */

  IF test_cursor%FOUND then

    /* Do stuff */

  ELSE

    /* Do something else */

 

END;

/

 

The %FOUND will test if the cursor returned rows while %NOTFOUND returns true if no data is returned. I do not believe opening a cursor in this manner will produce a no_data_found exception. Use the FETCH <cursor_name> INTO <variables> to retrieve values from the curpsr if needed. I hope I read your question correctly and this helped.

 

Alec

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Hagedorn, Linda
Sent: Wednesday, March 13, 2002 12:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Branching logic in PL/SQL, positive instead of exception

Hello,

I'm looking for an example of structured PL/SQL code that does not use exception logic for branching, but instead recognizes that data is found.

As a bad example, 

 Select col1 from test.table t where exists (select z.zip from zip_table z where t.zip=z.zip ) into v_col1 ;
 exception
 when no_data_found
   begin
   select col2 from test.table2 t2 where exists (select z.zip from zip_table z where t2.zip=z.zip) into v_col1 ; ;
   exception
   when no_data_found
     begin 

I've checked the books and cannot see how to test for a positive result instead of a negative (exception) result.   

Does anyone have a good example of branching logic on record found?

Thanks, Linda