Home » Other » Training & Certification » cursors (merged)
cursors (merged) [message #249536] Wed, 04 July 2007 22:46 Go to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
hi

I am just stuck here , how can I use cursor with parameters to find top n earners in a dept. Without order by query

create or replace procedure toppers(N IN Integer ) IS

Cursor dep_cur(sal_in NUMBER default 10000) IS Select ename , sal from dept where sal < sal_in;

v_name dept.name%TYPE ;
v_sal dept.sal%TYPE;

Begin


open dep_cur(10000) ;
Loop

Fetch dep_cur into v_name ,v_sal ;
Exit when dep_cur%NOTFOUND ;

end loop ;
Re: cursors [message #249543 is a reply to message #249536] Wed, 04 July 2007 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
"Top" is only defined if you give an order.
Otherwise define "top".

Regards
Michel
Re: cursors [message #249682 is a reply to message #249543] Thu, 05 July 2007 09:50 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
Sorry for the confusion.
I am not trying to use "TOP" .

I need to use explicit cursor with parameter (sal) in order to find the top N earners from a table .
I was trying to send previous max sal as parameter and then find the next maximum salary . We have restrictions of not using delete and order by .

I dont know how to use explicit cursor with param to do this .

thanks

[Updated on: Thu, 05 July 2007 09:50]

Report message to a moderator

Re: cursors [message #249687 is a reply to message #249682] Thu, 05 July 2007 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I surely not do it with PL/SQL loop as you can do it in a single SQL statement but as you surely want to do it in the bad way then use:
Cursor dep_cur(sal_in NUMBER default 10000) IS 
SELECT * FROM (
Select ename , sal from dept where sal < sal_in
ORDER BY SAL DESC
)
WHERE ROWNUM <= N;

Regards
Michel
Re: cursors [message #249696 is a reply to message #249536] Thu, 05 July 2007 10:09 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
No, it is not possible this way. Once you open a cursor you cannot change its definition (including parameter values).
v_sal := 10000;
FOR i IN 1..N LOOP
 SELECT MAX(sal) INTO v_sal
  FROM dept
  WHERE sal < v_sal;
 SELECT ename INTO v_name
  FROM dept
  WHERE sal = v_sal
    AND rownum = 1; -- there may be more employees with the same salary
                    -- just pick the random one
END LOOP;
Take it just as an example as it is totally outperformant.
Note it takes top N salaries with one employee representing each one.

I wonder why you cannot use ORDER BY.
Are you restricted from analytic functions too (they should contain ORDER BY clause in this case).
Re: cursors [message #249781 is a reply to message #249696] Thu, 05 July 2007 20:22 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
I tried following but i get no data found error
My table has 4 records with salaries 70000 ,78000,60000,50000
I can't use Order By , delete as per problem statement

drop procedure top_salary
/

create or replace procedure top_salary(N IN Integer ) is
v_name emp.name%TYPE ;
v_sal emp.sal%TYPE;
i integer ;
Begin

v_sal := 10000;
For i in 1..N loop
Select max(sal) into v_sal from emp where sal < v_sal ;

select name ,sal into v_name ,v_sal from emp where sal = v_sal and rownum = i ;

DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_sal);
end loop ;

end;
/

declare

n integer := 3;

begin

top_salary(n);
end;
/
Re: cursors [message #249783 is a reply to message #249536] Thu, 05 July 2007 20:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>select name ,sal into v_name ,v_sal from emp where sal = v_sal and rownum = i ;

This will return no rows.
Re: cursors [message #249784 is a reply to message #249536] Thu, 05 July 2007 20:55 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
 select name ,sal into v_name ,v_sal from emp where sal = v_sal and rownum = i ;

You should understand what rownum means. Have a look into documentation, Tom Kyte's article or AskTom thread (all of them found in this forum when searched for ROWNUM in subject).

Quote:
I can't use Order By , delete as per problem statement

I do not understand it. Could you elaborate it further?

By the way, why did you change 1 to I in my example?
Also you should read and follow How to format your posts (found as the first post in list).

[Edit: Added the last paragraph]

[Updated on: Thu, 05 July 2007 21:12]

Report message to a moderator

Re: cursors [message #249786 is a reply to message #249536] Thu, 05 July 2007 20:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The teacher said that ORDER BY can not be used for this homework assignment.
Re: cursors [message #249794 is a reply to message #249784] Thu, 05 July 2007 21:28 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
I tried with rownum = 1 it gave no data found error .
I am trying to use a cursor with parameter as max prev sal so as to find next maximum salary .I dont know how to pass parameter to a cursor recursively .
drop procedure top_salary
/
create or replace procedure top_salary(N IN Integer ) IS

Cursor dep_cur(sal_in NUMBER) IS Select dep.name , dep.sal from dept where sal < sal_in ;

v_name dep.name%TYPE ;
v_sal dep.sal%TYPE;
i integer ;

Begin
select max(sal) into v_sal from dept ;
open dep_cur(v_sal) ;
loop
Fetch emp_cur into v_name ,v_sal ;
Exit when emp_cur%NOTFOUND ;

DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_sal);

end loop ;
close dep_cur;
end;
/
declare
n integer := 3;
begin
top_salary(n);
end;
/
-------------------------------------
This gives me all records except max salary record which I have eliminated by passing it as param .
now I want to filter remaining records by passing prev max sal so as to get next max sal .
Re: cursors [message #249795 is a reply to message #249536] Thu, 05 July 2007 21:36 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>This gives me all records except max salary record which I have eliminated by passing it as param .
>now I want to filter remaining records by passing prev max sal so as to get next max sal .
WOW, somebody is purposely designing a non-scalable "solution"; which "scales" at an N-factorial rate.

This problem can be solved with a single Full Table Scan (FTS).
Re: cursors [message #249796 is a reply to message #249536] Thu, 05 July 2007 21:54 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I dont know how to pass parameter to a cursor recursively .

You cannot since you open the cursor.

Quote:
I can't use Order By , delete as per problem statement

Please could you specify the 'delete as per problem'? I do not see any delete in this example.

Quote:
This problem can be solved with a single Full Table Scan (FTS).

Maybe you hit the goal of this homework. Store the full table into a collection and then implement some sorting algorithm in PL/SQL Wink.
Re: cursors [message #249799 is a reply to message #249536] Thu, 05 July 2007 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> Store the full table into a collection
There is NO need to store the whole results set; just the N largest.
icon9.gif  Re: cursors [message #249800 is a reply to message #249796] Thu, 05 July 2007 22:32 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member

Can not fetch all records & use any sorting algorithm as per problem statement ...
Hint given was to get max sal and then pass it as param in cursor and use it recursively to find next max sal .
Re: cursors [message #249802 is a reply to message #249536] Thu, 05 July 2007 23:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Yes, definitely the homework.

Once you open a cursor you cannot change its definition (including parameter values).
What if you closed it every time after you fetch one row and re-opened it before another fetch (with the new parameter)?
Re: cursors [message #249804 is a reply to message #249536] Thu, 05 July 2007 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>What if you closed it every time after you fetch one row and re-opened it before another fetch (with the new parameter)?
Trivial to do recursively.

But can be done with 1 FTS & no sorting.

[Updated on: Thu, 05 July 2007 23:21] by Moderator

Report message to a moderator

Re: cursors [message #249815 is a reply to message #249802] Fri, 06 July 2007 00:22 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
Yes it works Cool Thanks for the help.
if i open -close -reopen with new params .
But if table contains same salaries then it picks it as one record
for ex : A 50000
B 60000
c 70000
D 60000
then on quering for topmst 2 salaries it gives
c 70000 C 70000
B 60000 Instead of ===> B 60000
D 60000

I have to fetch one record at a time otherwise gets no data found for later records ,that might be the reason.
Re: cursors [message #249816 is a reply to message #249800] Fri, 06 July 2007 00:23 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
buzzw wrote on Thu, 05 July 2007 22:32

Can not fetch all records & use any sorting algorithm as per problem statement ...
Hint given was to get max sal and then pass it as param in cursor and use it recursively to find next max sal .

Re: cursors [message #249817 is a reply to message #249804] Fri, 06 July 2007 00:25 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
Thanks a lot for your help . I need to read & learn a lot on this topic .
Re: cursors [message #250126 is a reply to message #249784] Sun, 08 July 2007 08:07 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
hi
So far with the attached code I could find distinct top earners , if I give N : 2 then it finds top 2 earners but if there is 3rd one with same salary then it doesnt list it .
In this code , I am fetching max sal and finding emp who has max sal But how can I need to fetch again to find all other employees who has same max salaries .
I dont know how can I fetch one more time to find others who has same sal .
I have one exact fetch for max sal if i try to fetch more it gives more rows fetched for exact fetch error.
pls advise.
thanks
  • Attachment: sal.sql
    (Size: 1.12KB, Downloaded 184 times)
fetch [message #250127 is a reply to message #249536] Sun, 08 July 2007 09:36 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
Can one "fetch" same cursor twice in a loop to get different records .
thanks
Re: fetch [message #250133 is a reply to message #250127] Sun, 08 July 2007 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
Each time you fetch you get another record.

Regards
Michel
Re: fetch [message #250135 is a reply to message #250133] Sun, 08 July 2007 10:56 Go to previous messageGo to next message
buzzw
Messages: 11
Registered: July 2007
Junior Member
Here How can i fetch cursor twice so that i can get "all" emps who have max salary. This works for finding top n earners but if table has more than 1 emp who have same sal , it picks up only one of the records . when i try to fetch more by chnaging rownum it gives error "more rows fetched in exact fetch"


Begin
select max(sal) into v_sal from emp ;
for i in 1..N Loop
open emp_cur(v_sal) ;
Fetch emp_cur into v_name ,v_sal ;
select name , sal into v_name,v_sal from emp
where sal = v_sal and rownum = 1 ;
DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_sal);
close emp_cur ;
select max(sal) into v_sal from emp where sal < v_sal ;
end loop ;
Re: fetch [message #250138 is a reply to message #250127] Sun, 08 July 2007 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please SEARCH this forum for "TOP N".
Re: cursors (merged) [message #250331 is a reply to message #249536] Mon, 09 July 2007 12:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First, it seems, you ignore How to format your posts suggestions. Please read at your posts and compare it with other code provided. Which of them seems more readable to you? If you are not sure with formatting, just use "Preview Message" button.

To your question. SELECT INTO gets only one record. You have to use method which goes through all records with the same salary. You are already using it in your code. Just add other such construction and replace SELECT INTO with it.
Previous Topic: certification question
Next Topic: where i get the materials for oracle certification?
Goto Forum:
  


Current Time: Thu Dec 08 04:04:46 CST 2016

Total time taken to generate the page: 0.05715 seconds