Home » Developer & Programmer » Forms » Get Cursor Values into Variables
Get Cursor Values into Variables [message #255557] Wed, 01 August 2007 03:41 Go to next message
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 #255586 is a reply to message #255557] Wed, 01 August 2007 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have already declared a cursor variable; why do you need another variables for the same purpose? Can't you just reference "c2.s1"?

BTW, I'd suggest you to use cursor FOR loop; it is much easier to maintain.
BEGIN
  FOR cur_r IN (SELECT deptno, SUM(sal) s1
                FROM EMP
                GROUP BY deptno
               )
  LOOP
    dbms_output.put_line(cur_r.deptno ||' '|| cur_r.s1);
  END LOOP;
END;
/

30 9400
20 10875
10 8750

PL/SQL procedure successfully completed.
Re: Get Cursor Values into Variables [message #255592 is a reply to message #255557] Wed, 01 August 2007 04:35 Go to previous messageGo to next message
azamkhan
Messages: 548
Registered: August 2005
Senior Member
DECLARE

v_counter NUMBER DEFAULT 0;

cursor c1 is
select deptno, sum(sal) sl
from emp
group by deptno;

BEGIN

FOR i IN c1 LOOP

v_counter := v_counter + 1;

DBMS_OUTPUT.PUT_LINE (v_counter||'-'||i.sl);

END LOOP;

END;
Re: Get Cursor Values into Variables [message #255602 is a reply to message #255592] Wed, 01 August 2007 04:47 Go to previous messageGo to next message
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
Littlefoot
Messages: 20901
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;
/
Previous Topic: Windows username And Password?
Next Topic: Cliemt Machine Name in forms 6i
Goto Forum:
  


Current Time: Fri Dec 09 15:58:18 CST 2016

Total time taken to generate the page: 0.14574 seconds