Re: Possible tuse of EXISTS in PL/SQL?

Since you are using PL/SQL create a cursor and attempt fetch only one row matching your criteria. From everything I have heard and experiance this will be the fasted method to use

DECLARE   CURSOR rtest_cur
    IS SELECT * FROM rtest WHERE rcol='XX';

rtset_rec rtest_cur%ROWTYPE;

BEGIN
    OPEN rest_cur;
    FETCH rtest_cur INTO rtest_rec;
    IF retest_cur%NOTFOUND THEN

         < code to do an insert >
    END IF;    CLOSE rtest_cur;

END; "Randi Wølner" <rw_at_computas.no> wrote in message news:9BXG5.1014$G3.180981760_at_news.telia.no...
> I think that somewhere I have seen EXISTS been used in a smart way to find
> out whether some value exists in a table. To better explain what I mean, I
> will show how it is I want to use it:
>
> I have a very large table called rtest. If rtest have one or more rows
 with
> the value 'XX' in colum rcol, then I do not want to insert a new row,
> otherwise I will. My PL/SQL code now looks something like:
>
> select count(*)
> into rvariable
> from rtest
> where rcol='XX';
>
> if rvariable=0 then
> < code to do an insert >
> end if;
>
> This select will make Oracle perform a full table scan (unless I have an
> index on it, which in this case I do not), but if it is possible to use
> EXISTS in some way, I think Oracle would stop reading the table when the
> first record matching the query was found(??).
>
> Very thankful for help on this.
>
> Kind regards,
> Randi Wølner
>
>
Received on Thu Oct 19 2000 - 11:41:12 CDT