Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Simple SQL request
"Bavoux Jean-Baptiste" <jbb1_at_laposte.net> wrote in message
news:41e7ac4a$0$10272$626a14ce_at_news.free.fr...
> My need is to search if there is at least one recordset in a table.
> (from a procdure in Oracle)
>
> My solution is:
>
> my_count PLS_INTEGER;
>
> ...
>
> SELECT COUNT(*) INTO my_count FROM my_table WHERE (my_filters ...);
> IF (my_count >= 1) THEN
> -- ok
> ELSE
> -- nok
> ENDIF
>
> The probleme is that i don't need to count them all.
> Il my table has 1 000 0000 recorset and the half match my filters
> it will be very slow even if the first recorset matchs my filters.
>
> I ve try to do thinks like:
>
> CURSOR my_cursor IS
> SELECT 1
> FROM my_table
> WHERE (my_filters ...);
>
> BEGIN
>
> OPEN EMP_CUR; -- open the cursor
> FETCH EMP_CUR INTO v_FoundOne;
> CLOSE EMP_CUR;
>
> IF (v_FoundOne IS NOT NULL) THEN
> -- ok
> ELSE
> -- nok
> ENDIF
>
> It works but I find that the code i not very simple.
>
> Is there another solution?
> Something like SELECT FIRST(*) FROM my_Table WHERE ...
look into cursor for loops or 'where exists' sub-queries
++ mcs Received on Fri Jan 14 2005 - 05:58:51 CST