| select statement inside cursor??? [message #38241] |
Wed, 03 April 2002 15:33  |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
Hi,
Is it possible to inclue a select statement in cursor or update statement? I tried both and failed, how to fix this?
1. CREATE OR REPLACE PROCEDURE test AS
CURSOR c1 IS SELECT empno, sal from emp;
BEGIN
for i in c1
loop
begin
update emp set comm=1, sal = i.sal + decode(deptno, 10, (select avg(sal) from emp where deptno =10), 5000) where empno= i.empno;
exception
when others then
dbms_output.put_line('Error ');
end;
end loop;
END;
2.
CREATE OR REPLACE PROCEDURE test AS
CURSOR c1 IS SELECT empno, sal, decode(deptno, 10, (select avg(sal) from emp where deptno =10), 5000) as avg from emp;
BEGIN
for i in c1
loop
begin
update emp set comm=1, sal = i.Employee + i.avg where empno= i.empno;
exception
when others then
dbms_output.put_line('Error ');
end;
end loop;
END;
Thanks
|
|
|
|
| Re: select statement inside cursor??? [message #38246 is a reply to message #38241] |
Thu, 04 April 2002 06:01  |
tinel
Messages: 42
Registered: November 2001
|
Member |
|
|
Hi!
You can use a select..from into pl/sql, you must use select..into..from.
I'm not sure I understand what is the meaning of your procedure. For that update you can use something like that, you don't need to use a decode statement there:
CREATE OR REPLACE PROCEDURE Test AS
CURSOR c1 IS SELECT empno, sal, deptno FROM EMP ;
avg_value NUMBER(7,2);
c1_rec c1%ROWTYPE;
BEGIN
SELECT AVG(sal) INTO avg_value FROM EMP WHERE deptno = 10;
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1%notfound;
IF c1_rec.deptno = 10 THEN
UPDATE EMP SET comm=1, sal = sal + avg_value WHERE empno= c1_rec.empno;
ELSE
UPDATE EMP SET sal = sal + 5000 WHERE empno= c1_rec.empno;
END IF;
END LOOP;
END;
/
Bye
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
