Docstoc

Cursor22 - DOC

Document Sample
Cursor22 - DOC Powered By Docstoc
					2.Transfer the records in ct2 to ct3 where salary is greater than 5000

Solution

SQL> select * from ct2;

 EMPNO ENAME                  JOB MANGERNO JOINDATE PAY DEPTNO
------ ------------------------- ------------------------- --------- --------- --------- ---------
    1001       munwor teacher                  1           14-JAN-98           9000       30

   1002        alexander engineer             2            12-JUN-95          4500        32

   1003        holley        analyst          3             03-FEB-99         6500        33

   1004        jeorge        programmer        4            01-MAR-01          5760       34

   1005        Jinu          Programmer        5            12-OCT-90          7500       34



declare
cursor c2 is select empno,ename,job,mangerno,joindate,pay,deptno from ct2 where pay>5000;
e ct2.empno%type;
n ct2.ename%type;
j ct2.job%type;
m ct2.mangerno%type;
d ct2.joindate%type;
p ct2.pay%type;
dp ct2.deptno%type;
begin
     open c2;
        loop
         fetch c2 into e,n,j,m,d,p,dp;
       exit when c2%notfound;
        if p>5000 then
         insert into ct3 values(e,n,j,m,d,p,dp);
  delete from ct2 where empno=e and ename=n and job=j and mangerno=m and joindate=d and pay=p
and deptno=dp;
         end if;
end loop;
close c2;
commit;
end;
SQL>select * from ct3;



EMPNO ENAME                  JOB MANGERNO JOINDATE PAY DEPTNO
------ ------------------------- ------------------------- --------- --------- --------- ---------
    1001       munwor teacher                  1           14-JAN-98           9000       30

   1003        holley        analyst           3            03-FEB-99         6500         33

   1004        jeorge        programmer        4             01-MAR-01         5760        34

   1005        Jinu          Programmer         5            12-OCT-90         7500        34


SQL> select * from ct2;

EMPNO ENAME                  JOB MANGERNO JOINDATE PAY DEPTNO
------ ------------------------- ------------------------- --------- --------- --------- ---------
1002        alexander engineer              2           12-JUN-95           4500        32

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:10/17/2012
language:
pages:2
Description: oracle, sql