| cursors (merged) [message #249536] |
Wed, 04 July 2007 22:46  |
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 #249682 is a reply to message #249543] |
Thu, 05 July 2007 09:50  |
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 #249696 is a reply to message #249536] |
Thu, 05 July 2007 10:09 |
flyboy
Messages: 1903
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;
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 |
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 #249784 is a reply to message #249536] |
Thu, 05 July 2007 20:55 |
flyboy
Messages: 1903
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 #249794 is a reply to message #249784] |
Thu, 05 July 2007 21:28 |
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 #249802 is a reply to message #249536] |
Thu, 05 July 2007 23:13 |
flyboy
Messages: 1903
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 |
 |
BlackSwan
Messages: 26766
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 #249816 is a reply to message #249800] |
Fri, 06 July 2007 00:23 |
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 .
|
|
|
|
|
|
|
|
|
| fetch [message #250127 is a reply to message #249536] |
Sun, 08 July 2007 09:36 |
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 #250135 is a reply to message #250133] |
Sun, 08 July 2007 10:56 |
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: cursors (merged) [message #250331 is a reply to message #249536] |
Mon, 09 July 2007 12:31 |
flyboy
Messages: 1903
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
Thanks for the help. 