Re: sql select statement question from newbie

Peter

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

CURSOR c1 IS

             SELECT emp_number
                 FROM payroll_employee_master
             WHERE emp_number = p_emp_number;

lv_emp_id payroll_employee_master.emp_number%TYPE;

BEGIN    OPEN c1;
   FETCH c1 INTO lv_emp_id ;
   EXIT WHEN c1%NOTFOUND;
   RETURN TRUE;
   CLOSE c1;

EXCEPTION   WHEN NO_DATA_FOUND THEN
     RETURN FALSE; END; Peter Schneider wrote in message <3547b2f9.3648153_at_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 Sat May 02 1998 - 06:11:46 CDT