//assignment 3 -p1
declare
cursor c1 is select * from emp;
a c1%rowtype;
begin
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line('employye no:'||a.empno);
dbms_output.put_line('employye name:'||a.ename);
dbms_output.put_line('dept no:'||a.deptno);
dbms_output.put_line('sal:'||a.sal);
dbms_output.put_line('job:'||a.job);
end loop;
close c1;
end;
//p2
declare
eno emp.empno%type;
enm emp.ename%type;
jb emp.job%type;
sal emp.sal%type;
dno emp.deptno%type;
cursor c1 is select empno,ename,job,sal,deptno from emp where deptno=10;
begin
open c1;
loop
fetch c1 into eno,enm,jb,sal,dno;
exit when c1%notfound;
dbms_output.put_line('employee no:'||eno);
dbms_output.put_line('employee name:'||enm);
dbms_output.put_line('dept no:'||dno);
dbms_output.put_line('sal:'||sal);
dbms_output.put_line('job:'||jb);
end loop;
close c1;
end;
//p3
declare
cursor c1 is select * from emp where ename like 'a%';
a c1%rowtype;
begin
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line('Emp no:'||a.empno);
dbms_output.put_line('Ename :'||a.ename);
dbms_output.put_line('sal:'||a.sal);
end loop;
close c1;
end;
//p4
declare
cursor c1 is select * from emp where TO_CHAR(hiredate, 'YY')='81';
a c1%rowtype;
begin
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line('empno:'||a.empno);
dbms_output.put_line('hiredate:'||a.hiredate);
end loop;
close c1;
end;
//p5
declare
cursor c1 is select * from dept;
a c1%rowtype;
begin
dbms_output.put_line('Department Information');
dbms_output.put_line('======================================');
dbms_output.put_line(rpad('deptno',15,' ')||rpad('dname',15,' ')||'loc');
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line(rpad(a.deptno,15,' ')||rpad(a.dname,15,' ')||a.loc);
end loop;
dbms_output.put_line('========================================');
close c1;
end;
//p6
declare
CURSOR c1 IS SELECT e.empno, e.ename, e.deptno, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;
a c1%rowtype;
begin
dbms_output.put_line('Emplyee information');
dbms_output.put_line('=======================');
dbms_output.put_line(rpad('empno',15,' ')||rpad('ename',15,' ')||
rpad('Deptno',15,' ')||'dept name');
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line(rpad(a.empno,15,' ')||rpad(a.ename,15,' ')||
rpad(a.deptno,15,' ')||a.dname);
end loop;
close c1;
end;
//p7
declare
cursor c1 is select * from dept;
a c1%rowtype;
begin
dbms_output.put_line('Department Information');
dbms_output.put_line('======================================');
dbms_output.put_line(rpad('deptno',15,' ')||rpad('dname',15,'
')||'location');
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line(rpad(a.deptno,15,' ')||rpad(a.dname,15,' ')||a.loc);
end loop;
dbms_output.put_line('========================================');
dbms_output.put_line('Total records:'||c1%rowcount);
close c1;
end;
//p8
declare
cursor c1 is select * from emp;
a c1%rowtype;
begin
dbms_output.put_line('employee Information');
dbms_output.put_line('======================================');
dbms_output.put_line(rpad('empno',15,' ')||rpad('ename',15,' ')||
rpad('sal',15,' ')||'annual sal');
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
DBMS_OUTPUT.PUT_LINE(
RPAD(a.empno, 15, ' ') ||
RPAD(a.ename, 15, ' ') ||
RPAD(TO_CHAR(a.sal, '99999.99'), 15, ' ') ||
TO_CHAR(a.sal * 12, '99999999.99')
);
end loop;
dbms_output.put_line('========================================');
close c1;
end;
//p9
declare
cursor c1 is select * from emp;
a c1%rowtype;
tot number(5):=0;
begin
dbms_output.put_line('employee Information');
dbms_output.put_line('======================================');
dbms_output.put_line(rpad('empno',15,' ')||rpad('ename',15,' ')||'salary');
dbms_output.put_line('======================================');
open c1;
loop
fetch c1 into a;
exit when c1%notfound;
dbms_output.put_line(rpad(a.empno,15,' ')||rpad(a.ename,15,' ')||a.sal);
tot := tot + a.sal;
end loop;
dbms_output.put_line('======================================');
dbms_output.put_line('Total salary:'||tot);
close c1;
end;
