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

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/10/09
Message-ID: <19971009004900.UAA03903@ladder02.news.aol.com>#1/1

The most efficient way to check if records that meet a certain criteria exist is to create a cursor and then try to fetch a
single record.

Procedure xxxxx IS
CURSOR Check_Cur IS
  SELECT 1
    FROM my_table
   WHERE my_column = <value or variable>;

t_Data_Exists NUMBER := 0;

BEGIN
  OPEN Check_Cur;
   FETCH Check_Cur
    INTO t_Data_Exists;
   CLOSE Check_Cur;

/* If the fetch failed t_Data_Exists will

still have a value of zero */

  IF t_Date_Exists THEN
    Do_Something;
  ELSE
    Do_The_Other_Thing;
  END IF;
END: Hope this helps
John

John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd LVO-UPL
Las Vegas NV 89103

FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com

e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/

The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.

>The following should work unless I am missing something...
>
>select 1 into <local_var> where exists (select 1 from <table> where
><criteria>)
>The two '1' s do not have to be the same.
>
>
>
>> >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
>
>
>
>
>
>
>
>
>
>
Received on Thu Oct 09 1997 - 00:00:00 CDT