create table doctor
(doc_id varchar2(5) primary key,
p_cd varchar2(5),
sex char(1) check(sex in('f','m')),
p_age number(3) check(p_age>0));
insert into doctor values('&docid','&pcd','&sex',&age);
Enter value for docid: d101
Enter value for pcd: p101
Enter value for sex: f
Enter value for age: 2
old 1: insert into doctor values('&docid','&pcd','&sex',&age)
new 1: insert into doctor values('d101','p101','f',2)
select * from doctor;
DOC_I P_CD S P_AGE
----- ----- - ---------
d101 p101 f 2
d102 p102 m 21
d103 p103 f 45
d104 p104 m 65
d105 p105 f 40
1) Cursor
DECLARE
tpcd doctor.p_cd%type;
tsex doctor.sex%type;
tage doctor.p_age%type;
cnt1 number(5) :=0;
cnt2 number(5) :=0;
cnt3 number(5) :=0;
cnt4 number(5) :=0;
cnt5 number(5) :=0;
ans1 number(5) :=0;
f1 number(5):=0;
f2 number(5):=0;
f3 number(5):=0;
f4 number(5):=0;
f5 number(5):=0;
ans2 number(5):=0;
ans3 number(5):=0;
t1 number(5):=0;
t2 number(5):=0;
t3 number(5):=0;
t4 number(5):=0;
t5 number(5):=0;
CURSOR c1 is select p_cd,p_age,upper(sex) from doctor where sex='f';
CURSOR c2 is select p_cd,p_age,upper(sex) from doctor where sex='m';
BEGIN
dbms_output.put_line('------------------------------------------------------------------------');
dbms_output.put_line('');
dbms_output.put_line('');
dbms_output.put_line('');
dbms_output.put_line(' SEX ' || ' Patient in Age Group' );
dbms_output.put_line('');
dbms_output.put_line('');
dbms_output.put_line('------------------------------------------------------------------------');
dbms_output.put_line( ' ' || ' 1-12 ' || ' 13-25 '||' 26-50 '||' 50-100 '||' Above 100'|| ' ' || 'Total');
dbms_output.put_line('------------------------------------------------------------------------');
dbms_output.put_line('');
dbms_output.put_line('');
OPEN c1;
IF c1%isopen then
LOOP
FETCH c1 into tpcd,tage,tsex;
exit when c1%notfound;
IF tage>=1 and tage<=12 then
cnt1:=cnt1 + 1;
t1:=t1 + 1;
ELSIF tage>=13 and tage<=25 then
cnt2:= cnt2 +1;
t2:=t2 + 1;
ELSIF tage>=26 and tage<=50 then
cnt3:= cnt3 +1;
t3:=t3 + 1;
ELSIF tage>=51 and tage<=100 then
cnt4 := cnt4 + 1;
t4:=t4 + 1;
ELSIF tage>=100 then
cnt5 := cnt5 + 1 ;
t5:=t5 + 1;
ELSE
dbms_output.put_line('Nots Found');
END IF;
END LOOP;
END IF;
CLOsE c1;
COMMIT;
ans1:=cnt1+cnt2+cnt3+cnt4+cnt5;
dbms_output.put_line(tsex|| ' '|| cnt1 ||' '|| cnt2 || ' '||' '|| cnt3 ||' '||cnt4||' '||cnt5 || ' '|| ' '||ans1);
OPEN c2;
IF c2%isopen then
LOOP
FETCH c2 into tpcd,tage,tsex;
exit when c2%notfound;
IF tage>=1 and tage<=12 then
f1:=f1 + 1;
t1:=t1 + 1;
ELSIF tage>=13 and tage<=25 then
f2:= f2 +1;
t2:=t2 + 1;
ELSIF tage>=26 and tage<=50 then
f3:= f3 +1;
t3:=t3 + 1;
ELSIF tage>=51 and tage<=100 then
f4 := f4 + 1;
t4:=t4 + 1;
ELSIF tage>=100 then
f5 := f5 + 1 ;
t5:=t5 + 1;
ELSE
dbms_output.put_line('Nots Found');
END IF;
END LOOP;
END IF;
CLOsE c2;
COMMIT;
ans2:=f1+f2+f3+f4+f5;
ans3:=ans1+ans2;
dbms_output.put_line(tsex|| ' '|| f1 ||' '|| f2 || ' '||' '|| f3 ||' '||f4||' '||f5 || ' '|| ' '||ans2);
dbms_output.put_line('------------------------------------------------------------------------');
dbms_output.put_line('Total'|| ' '|| t1 ||' '|| t2 || ' '||' '|| t3 ||' '||t4||' '||t5 || ' '|| ' '||ans3);
END;
OUTPUT
SQL> /
BadriKedar Charitable Trust Ltd
----------------------------------------------------------------------------------
SEX Patient in Age Group
----------------------------------------------------------------------------------
1-12 13-25 26-50 50-100 Above 100 Total
----------------------------------------------------------------------------------
F 1 0 2 0 0 3
M 0 1 0 1 0 2
----------------------------------------------------------------------------------
Total 1 1 2 1 0 5
2) Perform various queries using IMPLICIT CURSOR WITH Appropriate Error messages.
I) Display all fields of EMP table using record type variable.
declare
type e1 is record(tno emp.empno%type,tnm emp.ename%type,esal emp.sal%type);
emprec e1;
cursor cemp is select empno,ename,sal from emp;
begin
open cemp;
if cemp%isopen then
loop
fetch cemp into emprec;
exit when cemp%notfound;
dbms_output.put_line('empno ' || emprec.tno || ' empname ' || emprec.tnm || ' salary ' || emprec.esal);
end loop;
end if;
end;
OUTPUT
empno 7369 empname SMITH salary 800
empno 7499 empname ALLEN salary 1600
empno 7521 empname WARD salary 1250
empno 7566 empname JONES salary 2975
empno 7654 empname MARTIN salary 1437.5
empno 7698 empname BLAKE salary 2850
II) Delete the record for given employee.
declare
tempno emp.empno%type;
begin
delete from emp where empno='&tempno';
if sql%notfound then
dbms_output.put_line('Not found');
else
dbms_output.put_line('Found');
end if;
end;
OUTPUT
Enter value for tempno: 7369
old 4: delete from emp where empno='&tempno';
new 4: delete from emp where empno='7369';
Found
III) Update the salary of employee by 10%.
begin
update emp set sal=sal+(sal * 0.1);
if sql%notfound then
dbms_output.put_line('not found');
end if;
end;
OUTPUT
Select sal from emp;
SAL
-------- -
800
1600
1250
after execution,
select sal from emp;
SAL
--------
1760
1375
3272.5
IV) Display names of employees of emp table using Cursor for FOR loop.
declare
cursor c1 is select * from emp;
begin
for emp in c1
loop
dbms_output.put_line(emp.ename);
end loop;
end;
OUTPUT
ALLEN
WARD
JONES
MARTIN
BLAKE
3) The HRD manager has decided to raise salary of all employees for particular department by 0.05. Whenever such raise is given to employees, a record for same is maintained in emp_raise table.
Tables :
EMP : empcode, empname, sal , deptno
EMP_RAISE : empcode, raise name, raise amount.
USE EXPLICIT CURSOR.
create table emp1
(empno varchar2(5) primary key,
empname varchar2(10),
sal number(10),
deptno varchar2(5));
insert into emp1 values('&empcd','&empnm',&sal,'&deptno');
select * from emp1;
EMPNO EMPNAME SAL DEPTN
----- ---------- ---------- -----
e101 abc 50000 d101
e102 xyz 15000 d102
create table emp_raise
(empno varchar2(5) references emp1(empno),
raisenm varchar2(10),
raiseamt varchar2(10));
Cursor
DECLARE
tdpt emp1.deptno%type;
tsal emp1.sal%type;
cursor c1(td emp1.deptno%type) is
select * from emp1
where deptno=td
FOR UPDATE OF sal;
cur c1%rowtype;
BEGIN
tdpt:='&tdpt';
open c1(tdpt);
loop
fetch c1 into cur;
exit when c1%notfound;
update emp1
set sal = sal + (sal * 0.05)
WHERE CURRENT OF c1;
select sal into tsal from emp1 where empno=cur.empno;
insert into emp_raise values(cur.empno,cur.empname,tsal);
end loop;
close c1;
end;
OUTPUT
select * from emp1;
EMPNO EMPNAME SAL DEPTN
----- ---------- ---------- -----
e101 abc 15750 d101
e102 xyz 10500 d102
select * from emp_raise;
EMPNO RAISENM RAISEAMT
----- ---------- ----------
e101 abc 15000
e102 xyz 10500