Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » DatabaseRSS Feeds

Database of Doctor information related to total patient age wise in his hospital

Posted By: William Bouchard     Category: Oracle     Views: 3004

Prepare following report for a Doctor information related to total patient age wise in his hospital.
Sex Patient in Age Group Total
-------------------------------------------------------
1-12 13-25 25-50 50-100 Above 100


-------------------------------------------------------
Total ------- -------- ------ -------

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
  
Share: 

 
 


William Bouchard
William Bouchard author of Database of Doctor information related to total patient age wise in his hospital is from Montreal, Canada.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!