Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Cursor (Oracle 9i)  () 1 Vote
PL/SQL Cursor [message #381392] Fri, 16 January 2009 10:24 Go to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
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

Previous Topic: Proble with hierarchy query (merged 3)
Next Topic: using a pl/sql function
Goto Forum:
  


Current Time: Thu Dec 08 08:13:31 CST 2016

Total time taken to generate the page: 0.05975 seconds