Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste.
I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences:
Functions:
create or replace function a return varchar2
is
begin
for srec in (select dummy from ctest)
loop
return srec.dummy;
end loop;
return null;
end;
/
create or replace function b return varchar2
is
cursor c1
is
select dummy
from ctest;
v_dummy varchar2(1) := null;
begin
open c1;
fetch c1 into v_dummy;
close c1;
return v_dummy;
end;
/
create or replace function b2 return varchar2
is
v_dummy varchar2(1) := null;
begin
select dummy
into v_dummy
from ctest;
return v_dummy;
exception
when no_data_found
then return null;
end;
/
Testrun:
set serveroutput on size 20000
exec runstats_pkg.rs_start;
declare
l_loop number := 0;
l_dummy varchar2(1);
begin
for l_loop in 1..1000
loop
l_dummy := a;
end loop;
end;
/
exec runstats_pkg.rs_middle;
declare
l_loop number := 0;
l_dummy varchar2(1);
begin
for l_loop in 1..1000
loop
l_dummy := b;
end loop;
end;
/
exec runstats_pkg.rs_stop(1);
For the test of b2 b was simply replaced by b2)
The results:
a vs b:
Run1 ran in 18 hsecs
Run2 ran in 18 hsecs
run 1 ran in 100% of the time
Name Run1 Run2 Diff
LATCH.enqueue hash chains 28 26 -2
LATCH.enqueues 28 26 -2
LATCH.library cache 2,067 2,069 2
LATCH.redo allocation 33 31 -2
LATCH.library cache pin 2,046 2,048 2
STAT...enqueue requests 16 14 -2
STAT...enqueue releases 16 14 -2
STAT...calls to get snapshot s 4,011 4,009 -2
STAT...active txn count during 16 8 -8
STAT...consistent gets - exami 16 8 -8
STAT...calls to kcmgcs 16 8 -8
STAT...cleanout - number of kt 16 8 -8
STAT...CPU used by this sessio 33 23 -10
STAT...consistent gets 3,026 3,016 -10
STAT...CPU used when call star 33 23 -10
STAT...redo entries 46 34 -12
LATCH.cache buffers chains 6,226 6,212 -14
STAT...db block changes 63 49 -14
STAT...db block gets 100 68 -32
STAT...session logical reads 3,126 3,084 -42
STAT...redo size 30,224 29,720 -504
STAT...recursive calls 1,001 2,001 1,000
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
11,543 11,525 -18 100.16%
a vs b2:
Run1 ran in 17 hsecs
Run2 ran in 23 hsecs
run 1 ran in 73.91% of the time
Name Run1 Run2 Diff
LATCH.enqueue hash chains 28 26 -2
LATCH.enqueues 28 26 -2
LATCH.library cache 2,067 2,069 2
STAT...bytes received via SQL* 984 986 2
LATCH.library cache pin 2,046 2,048 2
LATCH.redo allocation 34 31 -3
STAT...CPU used by this sessio 32 29 -3
STAT...enqueue releases 17 14 -3
STAT...enqueue requests 17 14 -3
STAT...CPU used when call star 32 29 -3
STAT...calls to get snapshot s 4,013 4,009 -4
STAT...active txn count during 17 8 -9
STAT...cleanout - number of kt 17 8 -9
STAT...calls to kcmgcs 17 8 -9
STAT...consistent gets - exami 17 8 -9
STAT...consistent gets 3,029 3,016 -13
STAT...recursive cpu usage 12 25 13
STAT...redo entries 49 34 -15
STAT...db block changes 69 48 -21
LATCH.cache buffers chains 6,235 6,207 -28
STAT...db block gets 111 66 -45
STAT...session logical reads 3,140 3,082 -58
STAT...redo size 30,648 29,660 -988
STAT...recursive calls 1,001 2,001 1,000
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
11,557 11,519 -38 100.33%
(9.2.0.2/SuSE 8.1)
I find it quite strange that results vary from run to run. E.g. in a vs b active txn count was 16 for a, whilst it was 17 for a vs. b2. Why this difference?
B variants are consequently cheaper in redo size, session logical reads. Recursive calls is conseqently 1000 higher for b/b2.
What counts is runtime, and a and b have no differences, b2, is appr. 25% slower. b/b2 consume slightly less latches, but the diff is < 1 %.
So, IMHO what remains is the question of taste. I started 22 years ago as software engineer, worked in R&D departments (of commercial software firms) and built a 4GL, including a compiler and a universal interpreter for the code produced. Maybe bearing the burdens of my career as a stone-age 'C'-developer influences my 'taste' of beautiness of code (or the lack off it).
Unless I've put some flaws in my testruns (which I'm glad to hear of), I rest my case.
Regards, Carel-Jan
===
If you think education is expensive, try ignorance. (Derek Bok)
===
At 17:44 28-12-03 -0800, you wrote:
Carel,
It might seem that the loop construct would be more expensive, but
it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0 )
function a:
create or replace function a return varchar2
is
begin
for srec in (select dummy from ctest)
loop
return srec.dummy;
end loop;
return null;
end;
/
function b:
create or replace function b return varchar2
is
cursor c1
is
select dummy
from ctest;
v_dummy varchar2(1) := null;
begin
open c1;
fetch c1 into v_dummy;
close c1;
return v_dummy;
end;
/
The resource consumption for a 1000 iterations of each: ( a is the first
column )
17:38:42 poirot.jks.com - [EMAIL PROTECTED] SQL> @run_stats
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.Consistent RBA 0 1 1
LATCH.cache buffers lru chain 1 0 -1
LATCH.lgwr LWN SCN 0 1 1
LATCH.mostly latch-free SCN 0 1 1
LATCH.session idle bit 0 1 1
STAT...calls to get snapshot scn: kcmgss 3012 3013 1
STAT...calls to kcmgcs 7 6 -1
STAT...cleanout - number of ktugct calls 0 1 1
STAT...consistent gets - examination 0 1 1
STAT...session cursor cache hits 1 2 1
STAT...parse count (total) 1 2 1
STAT...opened cursors current 1 2 1
STAT...opened cursors cumulative 1 2 1
STAT...messages sent 0 1 1
STAT...free buffer requested 1 0 -1
STAT...execute count 1003 1004 1
STAT...deferred (CURRENT) block cleanout 4 3 -1
applications
STAT...calls to kcmgas 0 1 1
STAT...user commits 0 1 1
STAT...active txn count during cleanout 0 1 1
LATCH.enqueues 0 1 1
LATCH.dml lock allocation 0 2 2
LATCH.session allocation 0 2 2
STAT...db block changes 25 27 2
STAT...enqueue releases 0 2 2
STAT...consistent gets 3010 3012 2
LATCH.cache buffers chains 6130 6133 3
STAT...redo entries 17 20 3
STAT...recursive cpu usage 4 7 3
STAT...db block gets 30 33 3
LATCH.redo writing 0 3 3
LATCH.undo global data 1 4 3
LATCH.library cache 7 4 -3
LATCH.enqueue hash chains 0 4 4
LATCH.redo allocation 18 22 4
LATCH.library cache pin 7 3 -4
LATCH.messages 0 5 5
STAT...session logical reads 3040 3045 5
STAT...commit cleanouts 0 7 7
STAT...commit cleanouts successfully com 0 7 7
pleted
STAT...redo size 27184 27820 636
STAT...recursive calls 2004 3007 1003
42 rows selected.
The for loop actually appears to be somewhat less expensive in terms
of database resources.
Jared
On Sun, 2003-12-28 at 11:39, Carel-Jan Engel wrote:
> What I don't understand is the loop construction:
>
> Actually only one (row) is read form the cursor, and then the function is
> left with a return. Because it's an unconditional return, the code within
> the loop will either execute once, or never. When no data is found NULL is
> returned. When an error occurs NULL is returned as well. So, why a loop?
>
> Wouldn't it be better to have something like:
>
> create or replace function XYZ(gid in number) return varchar2 is
> l_c1 tab1.C1%TYPE; /* local variable to store C1 */
> begin
> select c1
> into l_c1
> from tab1
> where id = gid;
>
> return l_c1;
>
> exception
> when no_data_found
> then return <some_error_code>; /* let the caller know that no data is
> found */
> when others
> then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code,
> preceded by the text ERROR for identification */
> end;
>
> Sure, a loop prevents an ORA-1422, but I don't think a loop construction
> should be abused for this. Just think about all loop controlling code that
> needs to be set up by the interpreter. tab1.ID should be unique, so a 1422
> normally cannot occur. Robust programming however asks us to prevent any
> error. I would prefer to think about how a 1422 should be handled, and
> write some code accordingly.
>
> Regards, Carel-Jan
>
> ===
> If you think education is expensive, try ignorance. (Derek Bok)
> ===
>
>
> At 09:39 28-12-03 -0800, you wrote:
> >Hi
> >
> >if we assume it is implements this way (see below) there will only be
> >one cursor since c_gid
> >is a bind variable and there for the cursor will be sharded from call to
> >call of the function.
> >
> >create or replace function XYZ (gid in number) return varchar2 is
> > cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid;
> >begin
> > for x in cur1(gid) loop
> > return x.c1;
> > end loop;
> > return null;
> >exception
> > when others then return null;
> >end;
> >
> >It will only be one coursor
> >
> >Guang Mei wrote:
> >>
> >>I have a function like below (psudo code). If cursor cur1 have multiple
> >>rows, would the code leave the cursor open when this function is called?
> >>So if this function is called 1000 times, I would have 1000 open cursors?
> >>
> >>function XYZ(gid in number) return varchar2 is
> >> cursor cur1 is select C1 from tab1 where ID = gid;
> >>begin
> >> for x in cur1 loop
> >> return x.c1;
> >> end loop;
> >> return null;
> >>exception
> >> when others then return null;
> >>end;
> >>
> >>
> >
> >--
> >
> >Best regards/Venlig hilsen
> >
> ><mailto:[EMAIL PROTECTED]>Peter Gram
> >
> ><http://www.miracleas.dk/>Miracle<http://www.miracleas.dk/> A/S
> >Kratvej 2
> >DK - 2760 Måløv
> >Cell: (+45) 2527 7107
> >Phone: (+45) 4466 8855
> >Fax: (+45) 4466 8856
> >Home: (+45) 3874 5696
> >Email: <mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).