| PL/SQL Cursor [message #381392] |
Fri, 16 January 2009 10:24  |
ygsunilkumar
Messages: 100
Registered: August 2008
|
Senior Member |
|
|
Please any one explain the logic why fetch sal_cur into v_sal is used after the insert statement. If fetch statement is ignored after the insert statement getting error as "No Text Area".
define p_num = 5;
declare
v_num number(3) := &p_num;
v_sal employees.salary%type;
cursor sal_cur is
select distinct salary
from employees
order by salary desc;
begin
open sal_cur;
fetch sal_cur into v_sal;
while sal_cur%rowcount <= v_num
and sal_cur%found
loop
insert into top_dogs(salary)
values (v_sal);
fetch sal_cur into v_sal;
end loop;
close sal_cur;
end;
/ |
|
|
|
| Re: PL/SQL Cursor [message #381396 is a reply to message #381392] |
Fri, 16 January 2009 10:45  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| ygsunilkumar wrote on Fri, 16 January 2009 17:24 | Please any one explain the logic why fetch sal_cur into v_sal is used after the insert statement.
|
Because somebody decided to use this code flow.
| ygsunilkumar wrote on Fri, 16 January 2009 17:24 | If fetch statement is ignored after the insert statement getting error as "No Text Area".
|
I do not understand this, but: are you aware, there should be FETCH statement inside the LOOP - otherwise you would use the same info about row fetched before LOOP again and again. This would definitely lead to infinite LOOP.
But I would rewrite it to INSERT SELECT statement anyway (if this is not homework on PL/SQL loops).
|
|
|
|
| Re: PL/SQL Cursor [message #381596 is a reply to message #381392] |
Sun, 18 January 2009 22:12 |
st33chen
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
hi,
the structure i used, fyr :
OPEN CURSORNAME
LOOP
FETCH CURSORNAME INTO VARNAME1, VARNAME2, VARNAME3, ...;
EXIT WHEN CURSORNAME%notfound;
DO SOMETHING ...
END LOOP;
CLOSE CURSORNAME;
|
|
|
|
| Re: PL/SQL Cursor [message #381620 is a reply to message #381596] |
Mon, 19 January 2009 00:29 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| st33chen wrote on Mon, 19 January 2009 05:12 | hi,
the structure i used, fyr :
OPEN CURSORNAME
LOOP
FETCH CURSORNAME INTO VARNAME1, VARNAME2, VARNAME3, ...;
EXIT WHEN CURSORNAME%notfound;
DO SOMETHING ...
END LOOP;
CLOSE CURSORNAME;
|
Well, you'd better read up on cursor loops then. If you use cursors in PL/SQL, it is far more elegant and less error-prone to do:
declare
cursor c_cur
is
select whatever
from wherever
;
begin
for r_cur in c_cur
loop
<do intelligent stuff>
end loop;
end;
No need to explicitly open or close the cursor, no need to check if a record was fetched.
|
|
|
|
| Re: PL/SQL Cursor [message #381647 is a reply to message #381396] |
Mon, 19 January 2009 02:00 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
@st33chen: Yes, this code flow is also possible; however if the "DO SOMETHING" action is just that one INSERT statement from you initial post, then
| flyboy wrote on Fri, 16 January 2009 17:45 | I would rewrite it to INSERT SELECT statement anyway (if this is not homework on PL/SQL loops).
|
(or use appropriate commands for similarly simple actions)
|
|
|
|
| Re: PL/SQL Cursor [message #381655 is a reply to message #381596] |
Mon, 19 January 2009 02:34 |
st33chen
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
yes, what FRANK suggested is one of the 3 ways i used to handle a cursor :
1.
OPEN CURSORNAME
LOOP
FETCH CURSORNAME INTO VARNAME1, VARNAME2, VARNAME3, ...;
EXIT WHEN CURSORNAME%notfound;
DO SOMETHING ...
END LOOP;
CLOSE CURSORNAME;
2.
FOR TMPCUR IN CURSORNAME LOOP
DO SOMETHING ...
END LOOP;
3. (DIDN'T DECLARE A CURSOR)
FOR TMPCUR IN (SELECT ... FROM ... WHERE ... ORDER BY ...) LOOP
DO SOMETHING ...
END LOOP;
THE DIFFERENCE BETWEEN 1. AND (2. OR 3.) IS THE NAMING OF THE FIELDS :
1. DIRECTLY USE VARNAME1, VARNAME2, ...
2. OR 3. TMPCUR.FIELD1, TMPCUR.FIELD2, ...
FYR.
[Updated on: Mon, 19 January 2009 02:52] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
