Re: Q: Newbie PL/SQL inquiry regarding existence check.

From: Peter Schneider <pschneider.ctj_at_metronet.de>
Date: 1997/10/08
Message-ID: <343c146a.10059996@pop-news.metronet.de>#1/1

On 7 Oct 1997 16:50:49 GMT, "Mick Davies" <mdavies_at_elekom.com> wrote:

>Respects to all Oracle experts.
>I would like to find the most processing efficient solution to the
>following:
>
>Assume a table of records.
>I would like to query this table for the existence of one or more records
>meeting a WHERE clause criterion, and set a local PL/SQL variable
>to 1 if ANY records exist, and 0 if NO records exist.
>
>For instance, I could use:
>
>Select count(*) into <local_var> From <Table> Where <criteria>;
>
>However, could this be a very costly query for a very large table?
>
>Is there a better way....anyone?
>
>Thanks in advance.
>Posting and e-mail reply would be nice.
>
>Mick Davies, mdavies_at_elekom.com

Hi Mick,

I was missing a construct like 'IF EXISTS (SELECT something FROM sometable)' in PL/SQL, so I wrote a generic function which performs this check. Here is the code:

FUNCTION exists_row

   (p_table_name IN VARCHAR2,
    p_where_con IN VARCHAR2 DEFAULT '1=1') RETURN NUMBER    /*

   || This function takes a tablename and an optional
   || WHERE-condition for that table.
   || It will return 1, if at least one row exists that
   || satisfies the condition.
   || Otherwise, 0 will be returned. In case of error,
   || sqlcode (negative) will be returned.
   ||
   || Author:  Peter Schneider, UBH  10-APR-1997

   */

IS

   stmt VARCHAR2(32767) :=

      'SELECT nvl(max(1),0) retval '
   || 'FROM sys.dual '
   || 'WHERE EXISTS '
   || '(SELECT ''TRUE'''
   || 'FROM ' || p_table_name || ' '
   || 'WHERE ' || p_where_con || ')';

   hndl INTEGER;
   retval NUMBER;
   dummy INTEGER;

BEGIN    hndl := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(hndl, stmt, DBMS_SQL.V7);    DBMS_SQL.DEFINE_COLUMN(hndl, 1, retval);

   dummy := DBMS_SQL.EXECUTE_AND_FETCH(hndl);    DBMS_SQL.COLUMN_VALUE(hndl, 1, retval);

   DBMS_SQL.CLOSE_CURSOR(hndl);

   RETURN retval;

   EXCEPTION WHEN OTHERS THEN

      IF DBMS_SQL.IS_OPEN(hndl) THEN
         DBMS_SQL.CLOSE_CURSOR(hndl);
      END IF;

      RETURN sqlcode;

END; /* function exists_row */

Please note that you cannot use it in a SQL-statement, as it performs dynamic SQL, but only in a PL/SQL expression, like

DECLARE x NUMBER;
BEGIN
   x := exists_row('EMP', 'mgr IS NULL');    IF x = 0 THEN ...
   ELSE ...
   END IF;
END; As this function does a WHERE EXISTS-subquery, which will stop if the first matching record is found, it is as efficient as possible. However, if you specify a WHERE-clause that cannot make use of any indexes, the function will, as any other SQL-statement, perform a full table scan. This may take due time, if your first matching records happens to be (physically) at the end of a very large table.

Hope this helps,
Peter

Peter Schneider
pschneider.ctj_at_metronet.de Received on Wed Oct 08 1997 - 00:00:00 CDT