Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Simple SQL request

Re: Simple SQL request

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Fri, 14 Jan 2005 13:57:37 +0100
Message-ID: <cs8fi2$4bn$1@news3.zwoll1.ov.home.nl>


Bavoux Jean-Baptiste wrote:
> My need is to search if there is at least one recordset in a table.
> (from a procdure in Oracle)
>

Ok - clear enough.

> My solution is:
>
> my_count PLS_INTEGER;
>
> ...
>
> SELECT COUNT(*) INTO my_count FROM my_table WHERE (my_filters ...);
> IF (my_count >= 1) THEN

Hmmm... that's the wrong question... The question that belongs to this answer is: how many rows does my_table have?

> The probleme is that i don't need to count them all.

Correct.

>
> 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.

Ah - who said simple things came easy?
The solution you found is a very elegant and efficient one, and even suggested by one of the guru's of PL/SQL, Steven Feuerstein.
>
> Is there another solution?
> Something like SELECT FIRST(*) FROM my_Table WHERE ...

You could try and add rownum = 1 to your where clause.

And... what about using where exists in the following step(s)?

-- 
Regards,
Frank van Bortel
Received on Fri Jan 14 2005 - 06:57:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US