Re: sql select statement question from newbie

From: Peter Schneider <peter.schneider_at_okay.net>
Date: Wed, 29 Apr 1998 23:08:46 GMT
Message-ID: <3547b2f9.3648153@news.okay.net>

On Tue, 28 Apr 1998 20:51:21 -0500, Martin Meadows <mmeadows_at_indy.net> wrote:

>I want to do something like the following:
>
> select * from payroll_employee_master
> where :emp_number = emp_#;
> if sqlcode < 0 go to Ora-Err;
>
> I'm just checking to see if :emp_number is in
> the payroll_employee_master table. This sql select
> statement will be in a procobol program. Is this an okay
> way to handle this? Is there a better way?
>
> Thanks,
> Martin Meadows

Hi Martin,

if you just want to check whether a row exists that matches certain criteria without having to worry about no_data_found or too_many_rows exceptions, then in PL/SQL you would code:

FUNCTION check_emp_number
 (p_emp_number IN payroll_employee_master.emp_number%TYPE) RETURN BOOLEAN
IS

   row_exists NUMBER;
BEGIN    SELECT nvl(max(1),0)

     INTO row_exists
     FROM dual
    WHERE EXISTS
         (SELECT 1
            FROM payroll_employee_master
           WHERE emp_number = p_emp_number);

      -- Any other criteria you would like to check
      -- go into the where-clause of the subquery.
      -- If you would like to have it more flexible,
      -- then you could pass a table name and a where-clause
      -- as parameters and execute this statement with DBMS_SQL.

   IF (row_exists = 1) THEN
      RETURN TRUE;   -- yes it's there
   ELSE
      RETURN FALSE;  -- no it's not there

   END IF; END check_emp_number;

HTH,
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Wed Apr 29 1998 - 18:08:46 CDT