Get Cursor Values into Variables [message #255557] |
Wed, 01 August 2007 03:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hamdard
Messages: 37 Registered: July 2005
|
Member |
|
|
Someonen help please.
declare
v_row1 number;
v_row2 number;
v_row3 number;
cursor c1 is
select deptno, sum(sal) sl
from emp
group by deptno;
c2 c1%rowtype;
begin
open c1;
loop fetch c1 into c2;
exit when c1%notfound;
if c1%rowcount = 1 then
v_row1 := c2.sl;
end if;
if c1%rowcount = 2 then
v_row2 := c2.sl;
end if;
if c1%rowcount = 3 then
v_row3 := c2.sl;
end if;
-- dbms_output.put_line(v_row1);
-- dbms_output.put_line(v_row2);
-- dbms_output.put_line(v_row3);
dbms_output.put_line(c1%rowcount||' '||c2.sl);
end loop;
close c1;
end;
1 8750
2 10875
3 9400
I have this cursor and I want these sum(sal) values to populate into the defined variables.
I tried but in vain.
Can someone help please?
Thanks
|
|
|
|
|
Re: Get Cursor Values into Variables [message #255602 is a reply to message #255592] |
Wed, 01 August 2007 04:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hamdard
Messages: 37 Registered: July 2005
|
Member |
|
|
Thanks for the replies.
My problem is that I have this table
SQL> create table t
2 (col1 number,
3 col2 number,
4 col3 number)
5 /
Table created.
After running the code the values should be populated in my table like this
SQL> select * from t;
COL1 COL2 COL3
---------- ---------- ----------
8750 10875 9400
Can you explain please how I can get these three values into the columns in my table?
I need the first value in first column of the table. Second value in second column and third value in third column.
I'm workin on an application and I require this scenarion in my application design.
Thanks and regards
[Updated on: Wed, 01 August 2007 04:49] Report message to a moderator
|
|
|
Re: Get Cursor Values into Variables [message #255636 is a reply to message #255602] |
Wed, 01 August 2007 06:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
The way you put it, it appears that you are certain that there are exactly three possible column values you are groupping results by (in your example, three DEPTNOs).
What do you plan to do when DEPTNO 40 shows up? Where would you put its values?
Furthermore, you said Quote: | I need the first value in first column of the table. Second value in second column and third value in third column.
|
What do you call "the first value"? Which one is it? There's no ORDER BY in your first post; it means that the result will be in no specific order (so it may happen that sum of deptno 30 salaries will get into the col1 column).
Finally, why don't you insist on PL/SQL solution? What's wrong with SQL? This is a stupid way to do it, but it works:INSERT INTO t
(COL1, COL2, COL3)
VALUES
((SELECT SUM(sal) FROM EMP WHERE deptno = 10),
(SELECT SUM(sal) FROM EMP WHERE deptno = 20),
(SELECT SUM(sal) FROM EMP WHERE deptno = 30)
);
Otherwise, such a PL/SQL block might do what you need:DECLARE
l_cnt NUMBER := 1;
BEGIN
FOR cur_r IN (SELECT deptno, SUM(sal) s1
FROM EMP
GROUP BY deptno
ORDER BY deptno
)
LOOP
IF l_cnt = 1 THEN
INSERT INTO t (col1) VALUES (cur_r.s1);
ELSIF l_cnt = 2 THEN
UPDATE t SET col2 = cur_r.s1;
ELSIF l_cnt = 3 THEN
UPDATE t SET col3 = cur_r.s1;
END IF;
l_cnt := l_cnt + 1;
END LOOP;
END;
/
|
|
|