cursors

SQL> select * from emp4;
ENAME EID SALARY JOD
---------- ---------- ---------- ---------
A
123
20000 25-DEC-10
B
456
25000 02-OCT-08
C
789
35000 07-JAN-06
D
753
4000 12-AUG-09
SQL> create table emp6(fname varchar(20),empid number(10),sal number(6)
);
Table created.

1.Create a PL/SQL code to copy the tuples with salary>5000 to another table
& print the same on the screen.Rise an exception when no rows are selected
SQL> declare
2 fname emp4.ename%type;
3 empid emp4.eid%type;
4 sal emp4.salary%type;
5 cursor c1 is select ename,eid,salary from emp4
6 where salary<5000;
7 begin
8 open c1;
9 loop
10 fetch c1 into fname,empid,sal;
11 if c1 %notfound then
12 dbms_output.put_line('no rows have salary <5000');
13 end if;
14 exit when c1 %notfound;
15 dbms_output.put_line('fname:'||fname);
16 dbms_output.put_line('empid:'||empid);
17 dbms_output.put_line('sal:'||sal);
18 insert into emp6 values(fname,empid,sal);end loop;
19 close c1;
20 end;
21 /

fname:D
empid:753
sal:4000
no rows have salary <5000
PL/SQL procedure successfully completed.

SQL> select * from emp6;
FNAME
EMPID
SAL
-------------------- ---------- ----------
D
753
4000

2. Create a PL/SQL code to retrieve the name,salary & hiredate of the
employee , whose id is obtained as input from the user
SQL> declare
2 fname emp4.ename%type;
3 empid emp4.eid%type;
4 sal emp4.salary %type;
5 hiredate emp4.jod%type;
6 cursor c2 is select ename,eid,salary,jod from emp4
7 where eid=&eid;
8 begin
9 open c2;
10 loop
11 fetch c2 into fname,empid,sal,hiredate;
12 if c2 %notfound then
13 dbms_output.put_line('no rows selected');
14 end if;
15 exit when c2 %notfound;
16 dbms_output.put_line('fname:'||fname);
17 dbms_output.put_line('empid:'||empid);
18 dbms_output.put_line('salary:'||sal);
19 dbms_output.put_line('hiredate:'||hiredate);
20 end loop;
21 close c2;
22 end;
23 /
Enter value for eid: 123
old 7: where empid=&eid;
new 7: where eid=123;
fname:A
empid:123
salary:20000
hiredate:25-DEC-10
no rows selected
PL/SQL procedure successfully completed.
3. Create a PL/SQL code to insert a row into a new table for any value of

employee name obtained as input from the user
SQL> declare
2 fname emp.ename%type;
3 empid emp.eid%type;
4 salary emp.salary%type;
5 hiredate emp.joindate%type;
6 cursor c3 is select ename,eid,salary,joindate from emp
7 where ename='&ename';
8 begin
9 open c3;
10 loop
11 fetch c3 into fname,empid,salary,hiredate;
12 if c3%notfound then
13 dbms_output.put_line('No rows found');
14 end if;
15 exit when c3%notfound;
16 dbms_output.put_line('fname:'||fname);
17 dbms_output.put_line('empid:'||empid);
18 dbms_output.put_line('salary:'||salary);
19 dbms_output.put_line('hiredate:'||hiredate);
20 insert into emp1 values(fname,empid,salary,hiredate);
21 end loop;
22 close c3;
23 end;
24 /

Enter value for ename: A
old 7: where ename='&ename';
new 7: where ename='A';
fname:A
empid:123
salary:20000
hiredate:25-DEC-10
No rows found
PL/SQL procedure successfully completed.

4. Create a PL/SQL code to delete records where salary greater than the given
input and also output on the screen as the employee number which has been

deleted.
SQL> declare
2 eid emp.eid%type;
3 salary emp.salary%type;
4 cursor c4 is select eid,salary from emp
5 where salary>&salary;
6 begin
7 open c4;
8 loop
9 fetch c4 into eid,salary;
10 if c4%notfound then
11 dbms_output.put_line('No rows found');
12 end if;
13 exit when c4%notfound;
14 dbms_output.put_line('Deleted record eid:'||emp);
15 delete from emp where eid=eid;
16 end loop;
17 close c4;
18 end;
19 /

Enter the value for salary:25000
old 5: where salary > & salary;
new 5: where salary > 25000
deleted record eid 753
no rows found
PL/SQL procedure successfully completed.

SQL> select * from emp4;
ENAME
EID SALARY JOD
---------- ---------- ---------- ---------
A
123
20000 25-DEC-10
B
456
25000 02-OCT-08
C
789
35000 07-JAN-06

5. Create a PL/SQL code to display the id,name & salary of two highly paid
employee.
SQL> declare

2 i number;
3 empid emp4.eid%type;
4 fname emp4.ename%type;
5 sal emp4.salary%type;
6 cursor c5 is select ename,eid,salary from emp4
7 where salary in((select max(salary)from emp4) union(select max(salary)
from emp4 where salary not in(select max(salary)from emp4))) order by salary
desc;
8 begin
9 open c5;
10 dbms_output.put_line('Top two paid employee');
11 for i in 1..2
12 loop
13 fetch c5 into fname,empid,sal;
14 dbms_output.put_line('emp_name:'||fname);
15 dbms_output.put_line('emp_id:'||empid);
16 dbms_output.put_line('salary:'||sal);
17 end loop;
18 close c5;
19 end;
20 /

Top two paid employee
emp_name:C
emp_id:789
salary:35000
emp_name:B
emp_id:456
salary:25000
PL/SQL procedure successfully completed.