Re: Can't PL/SQL do the simple stuff that Transact-SQL can?

This solution would work however you are now counting all the rows in the table that match your criteria and this can be costly. If you only care if you have at least one, why count ever single one? Using an explicit OPEN, FETCH and then testing if the cursor returned a row allows you to skip counting every record that matches your search criteria (this can save a lot of time when you are dealing with very large tables).

Patrick Flahan
flahan_at_earthlink.net

Neil May <amay_at_home.com> wrote in message news:36CBA5DF.A2AD6745_at_home.com...
>Actually, a simpler solution is just to do the select as in your
>original example, then inspect the pseudo column that is returned with
>the rowcount. From memory it would look like:
>
> declare
> dummy number;
> begin
> select count(*) into dummy from table;
>
> if sql%rowcount >0 then...
> stuff...
> endif;
>
>No messing around with cursors. BTW, cursors are used for every
>statement. It's just ones like the above use implicit cursors, rather
>than explicit.
>
>Hope it helps,
>Andrew.
>
>Jonathan Tew wrote:
>>
>> Wow, thanks for posting a solution to the problem. That strikes we as
>> really complex code though. I've been told that cursors are very
>> inefficent things and should be used as little as possible. Under MS
>> SQL Server often a cursor will blow up in a stored proc causing
>> problems. Does Oracle have such problems with cursors and would this be
>> a bad thing performance wise? I'm under the impression that the kinds
>> of things that I'm trying to do are simple and common... why hasn't
>> Oracle taken care of this problem with an exists() construct?
>>
>> Thanks,
>> Jonathan Tew
Received on Sat Feb 20 1999 - 11:54:18 CST