Can You code an example?
September 27, 2003 - 11pm Central time zone
Reviewer: Neeha from India
Thanks for your quick response but
....
you use a cursor if and only if and only when you cannot do it in a single sql
statement
....
This is what i want what will be the situation when we can not do it in a single sql, please code
some simple exampe
thansk for your quickest response
Followup September 28, 2003 - 9am Central time zone:
you need to procedurally process each record before doing something with it. there would be an
example. you need to
a) read
b) inspect
c) process
each record for example.
think about "a report" -- you use cursors there alot in order to retrieve and print the data on a
screen.
How about you want to present the data to a user?
September 28, 2003 - 12am Central time zone
Reviewer: Jim from Portland, OR USA
You often use a cursor when you want to present the data to a user in some sort of GIU.
September 28, 2003 - 3am Central time zone
Reviewer: A reader
Why (Explicit) Cursor
September 28, 2003 - 4am Central time zone
Reviewer: Bharath from India
When a SQL statement in PL/SQL returns more than one row then TOO MANY ROWS exception is thrown..
Because for this reason you need to declare a explicit cursor.. And also for another most important
reason given by TK..
Followup September 28, 2003 - 9am Central time zone:
well, there is BULK COLLECT as well
select .... BULK COLLECT into .... from t;
special cases when pl/sql is faster than single SQL
September 28, 2003 - 1pm Central time zone
Reviewer: A reader
Hi Thomas
I have a case where pl/sql is more suitable but I have a problem, the case is a bit different now
and I dont know how to do it in single cursor, may you have a look?
My case
select ename, mgr
from emp a
where sal = (select max(sal) from emp x where x.deptno = a.deptno)
changed into
declare
l_counter number := 0;
l_last_deptno number;
begin
for i in (select deptno, sal from emp order by deptno, sal desc)
loop
if l_counter = 0 or i.deptno != l_last_deptno then
dbms_output.pue_line(i.deptno||', '||i.ename);
end if;
l_last_deptno := i.deptno;
l_counter := l_counter + 1;
end loop;
end;
/
in first SQL I have to access emp several times (correlated) whereas in second only once
The problem is now I have this
select ename, mgr
from emp a
where sal = (select max(sal) from emp x where x.deptno = a.deptno and x.job != 'PRESIDENT')
I dont think I can change this into PL/SQL and access only once EMP anymore.... Do you think it is
still possible?
Cheers
Followup September 28, 2003 - 1pm Central time zone:
there are so many ways to rewrite that.
and very strange but for me -- the plsql code is always slower then any of the SQL based
approaches!
ops$tkyte@ORA920> drop table emp;
Table dropped.
Elapsed: 00:00:00.13
ops$tkyte@ORA920> create table emp
2 as
3 select object_name ename, mod(rownum,10)+1 deptno, object_id empno, object_id+1 mgr,
4 object_id/100 sal
5 from all_objects
6 /
Table created.
Elapsed: 00:00:01.99
ops$tkyte@ORA920>
ops$tkyte@ORA920> create index emp_idx on emp(deptno)
2 /
Index created.
Elapsed: 00:00:00.27
ops$tkyte@ORA920> analyze table emp compute statistics
2 for table
3 for all indexes
4 for all indexed columns
5 /
Table analyzed.
Elapsed: 00:00:00.40
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select ename, mgr
2 from emp a
3 where sal = ( select max(sal) from emp x where x.deptno = a.deptno )
4 /
ENAME MGR
------------------------------ ----------
MY_TABLE 55593
DO_IT 56050
EMP 56065
RUNSTATS_PKG 56032
RUNSTATS_PKG 56033
RUN_STATS 56026
STATS 56031
T 56053
BONUS 55402
DEPT_PK 55404
10 rows selected.
Elapsed: 00:00:00.10
ops$tkyte@ORA920>
ops$tkyte@ORA920> select a.ename, a.mgr
2 from emp a, ( select max(sal) max_sal, deptno
3 from emp
4 group by deptno ) b
5 where a.deptno = b.deptno
6 and a.sal = b.max_sal
7 /
ENAME MGR
------------------------------ ----------
MY_TABLE 55593
DO_IT 56050
EMP 56065
RUNSTATS_PKG 56032
RUNSTATS_PKG 56033
RUN_STATS 56026
STATS 56031
T 56053
BONUS 55402
DEPT_PK 55404
10 rows selected.
Elapsed: 00:00:00.13
ops$tkyte@ORA920>
ops$tkyte@ORA920> select ename, mgr
2 from ( select ename, mgr, sal, max(sal) over (partition by deptno) max_sal
3 from emp )
4 where sal = max_sal
5 /
ENAME MGR
------------------------------ ----------
EMP 56065
RUNSTATS_PKG 56032
RUNSTATS_PKG 56033
RUN_STATS 56026
STATS 56031
T 56053
BONUS 55402
MY_TABLE 55593
DEPT_PK 55404
DO_IT 56050
10 rows selected.
Elapsed: 00:00:00.18
ops$tkyte@ORA920>
ops$tkyte@ORA920> select substr( data, 10, 30 ) ename,
2 to_number( substr( data, 40 )) mgr
3 from (
4 select deptno, max( to_char( sal, 'fm000000.00' ) ||
5 rpad( ename, 30 ) ||
6 mgr ) data
7 from emp
8 group by deptno
9 )
10 /
ENAME MGR
------------------------------ ----------
EMP 56065
RUNSTATS_PKG 56032
RUNSTATS_PKG 56033
RUN_STATS 56026
STATS 56031
T 56053
BONUS 55402
MY_TABLE 55593
DEPT_PK 55404
DO_IT 56050
10 rows selected.
Elapsed: 00:00:00.35
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_counter number := 0;
3 l_last_deptno number;
4 begin
5 for i in (select * from emp order by deptno, sal desc)
6 loop
7 if l_counter = 0 or i.deptno != l_last_deptno then
8 dbms_output.put_line(i.deptno||', '||i.ename);
9 end if;
10 l_last_deptno := i.deptno;
11 l_counter := l_counter + 1;
12 end loop;
13 end;
14 /
1, EMP
2, RUNSTATS_PKG
3, RUNSTATS_PKG
4, RUN_STATS
5, STATS
6, T
7, BONUS
8, MY_TABLE
9, DEPT_PK
10, DO_IT
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.94
ops$tkyte@ORA920>
sory posted incorrect code
September 28, 2003 - 1pm Central time zone
Reviewer: A reader
Hi
The pl/sql code was an incorrect one, the correct one should be as follows
declare
l_counter number := 0;
l_max_sal number;
l_last_deptno number;
begin
for i in (select ename, deptno, comm, sal from emp order by deptno, sal desc)
loop
if l_counter = 0 or i.deptno != l_last_deptno or (i.deptno = l_last_deptno and i.sal =
l_max_sal) then
l_max_sal := i.sal;
dbms_output.put_line(i.ename||' '||i.deptno||' '||nvl(i.comm, 0)||' '||i.sal);
end if;
l_last_deptno := i.deptno;
l_counter := l_counter + 1;
end loop;
end;
/
cheers
I think for very large table PL/SQL would be faster?
September 28, 2003 - 3pm Central time zone
Reviewer: A reader
Hi
If the table involved has million of rows wouldnt pl/sql approach be faster? It would only has to
scan the table once instead of scan million of times using correlated sub query
Another question, I see you used analytic function in one of approaches, if the original query is
select ename, mgr
from emp a
where sal = ( select max(sal) from emp x where x.deptno = a.deptno where job != 'PRESIDENT' )
/
How can you convert that using analytic function?
Cheers
Followup September 28, 2003 - 3pm Central time zone:
tell you what, you benchmark it for us and let us know (my response is, as usual, if you can do it
in a single sql query ..... etc etc etc. The worst performing code I generally see is when people
try to second guess the SQL engine, try to "save it from doing work")......
Give it a go, SEE what happens. I gave you the framework, you just need to try it out (no, plsql
won't be faster)....
select max( case when job != 'PRESIDENT' then sal else null end ) over (partition by deptno)
max_sal
will be the analytic you were looking for -- to find the max salary for a non-president job by
deptno.
not able to find the link
September 28, 2003 - 11pm Central time zone
Reviewer: A reader
There was a thread where you showed 3 ways to do the below
ops$tkyte@ORA920> select substr( data, 10, 30 ) ename,
2 to_number( substr( data, 40 )) mgr
3 from (
4 select deptno, max( to_char( sal, 'fm000000.00' ) ||
5 rpad( ename, 30 ) ||
6 mgr ) data
7 from emp
8 group by deptno
9 )
10 /
i.e. display details along with group by, can you please give that link. I have searched but could
not find it. I remember " 3 ways" in the thread.
Can we not do similar things using Record or Table datatypes?
September 29, 2003 - 3am Central time zone
Reviewer: Neeha from India
Hi Again Sir,
You say that we do things one by one and see the results for each action.
By using PL/SQL tables datatypes we can do and select the whole table even, then how can you say
that Cursor is necessory?
(Sorrrrry, but I just want to learn a better)
thanks
Followup September 29, 2003 - 7am Central time zone:
benchmark it.
see which approach is
a) faster
b) more efficient (look at ram utilization, cpu usage, etc)
c) more scalable
d) easier to code
e) easier to maintain
f) readable
and decide for yourself. Me, i'm lazy, i like to write as little code as possible. cursors are
natural, cursors are easy.
Cursor Use
November 12, 2003 - 2am Central time zone
Reviewer: Kalyan from Mumbai, INDIA
We mainly use cursor in two places
1. Simplicity, readability and maintainability of codes. Theoritically most pl/sql can be converted
into a single sql with innovative way of thinking but at the cost of time,readability and
maintainability. Where as Cursor is transparent and gives u full control over the data. For
example, in employee table, if you have so and so combination of location, department, salary
grade, years of experience, no of years with the co and so many other criteria, you want to raise
salary by some percentage. for different combination, different rule and so on.
2. To avoid no data found / to many rows
We use a FOR LOOP with a default value set or exception as required.
Followup November 12, 2003 - 10am Central time zone:
1) i beg to differ, to disagree.
Just yesterday -- I took tons of procedural code, using "easy to read" (quote, unquote -- tongue in
cheek) procedural code (it was running "slow") and after spending lots of time "figuring out what
the HECK the goal was", rewrote the loops as simple INSERT INTO SELECTS -- well, the average loop
went from over 36 seconds, down to under 2. Hmmmm.
Cursors, processing slow by slow (opps, meant row by row) are an approach -- but usually the wrong
one.
Using CASE in SQL -- I'd betcha my sql would be more readable then your scads of procedural code --
AND it would outperform it by many times.
2) umm, thats called an exception
begin
select ... into ... from ...
exception
when no_data_found then <setup defaults>
end
i don't see HOW you can use this to avoid "too many rows" -- that would be AN ERROR would it not
(and if you just want the "first" row -- use "and rownum = 1", you won't get too many rows)
but -- if you cannot make the leap to set based processing... if it is deemed "too hard"...
|