Can You code an example?
Neeha, September 27, 2003 - 11:40 pm UTC
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
September 28, 2003 - 9:15 am UTC
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?
Jim, September 28, 2003 - 12:27 am UTC
You often use a cursor when you want to present the data to a user in some sort of GIU.
A reader, September 28, 2003 - 3:13 am UTC
Why (Explicit) Cursor
Bharath, September 28, 2003 - 4:01 am UTC
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..
September 28, 2003 - 9:25 am UTC
well, there is BULK COLLECT as well
select .... BULK COLLECT into .... from t;
special cases when pl/sql is faster than single SQL
A reader, September 28, 2003 - 1:19 pm UTC
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
September 28, 2003 - 1:48 pm UTC
there are so many ways to rewrite that.
and very strange but for me -- the plsql code <b>is always slower then any of the SQL based approaches!</b>
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
A reader, September 28, 2003 - 1:47 pm UTC
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?
A reader, September 28, 2003 - 3:08 pm UTC
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
September 28, 2003 - 3:30 pm UTC
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
A reader, September 28, 2003 - 11:06 pm UTC
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.
September 29, 2003 - 7:30 am UTC
Can we not do similar things using Record or Table datatypes?
Neeha, September 29, 2003 - 3:48 am UTC
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
September 29, 2003 - 7:50 am UTC
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
Kalyan, November 12, 2003 - 2:33 am UTC
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.
November 12, 2003 - 10:23 am UTC
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"...