Re: SQL : problem with if statement

From: Chris Halioris <halioris_chris_nonlilly_at_lilly.com>
Date: 1997/08/13
Message-ID: <33F1F04E.2E6@lilly.com>#1/1

Astrid Ritscher wrote:
>
> Hi,
> I would like to know if there is any possibility to code in PSQL
> something like this (from Sybase Transact SQL):
>
> IF EXISTS (SELECT k_num FROM kunde)
> BEGIN
> ...
> END
>
> which tests for the existece of a certain row. This is faster than
> counting the number of rows because EXISTS stops the select after
> having found the first row.
>
> Thank you for any hints,
> Astrid.
>
> --
> Astrid Ritscher, ISL, Bremen, Germany

When testing for this type of condition you should always declare an explicit cursor, fetch one row to check for existence, then close the cursor.
Example:

DECLARE
  CURSOR c_exists IS

     SELECT k_num FROM kunde;
  krec c_exists%ROWTYPE;
BEGIN
  OPEN c_exists;
  FETCH c_exists INTO krec;
  IF c_exists%FOUND THEN
  ...
  END IF;
  CLOSE c_exists;
END; Note that this is better than the following:

DECLARE
   l_num kunde.k_num%TYPE;
BEGIN
   SELECT k_num INTO l_num FROM kunde;

• do whatever you want here, as row must exist or exception would
• taken over ... EXCEPTION WHEN NO_DATAFOUND THEN
  • doesn't exist NULL; END IF;

The reason the first scenario is better is that in only performs one fetch by using an explicit cursor. The second scenario uses an implicit cursor to perform the select. In this case, a second fetch must be performed by the implicit cursor just to see if it need to raise the exception TOO_MANY_ROWS, which in this case is unhandled, but you could handle it if you wished. Anyhow, the point is that the second scenario will always issue two fetches, but by using an explicit cursor you can control the number of fetches performed.

Chris Halioris
Tactics, Inc. Received on Wed Aug 13 1997 - 00:00:00 CDT