Hospital Management System using PL/SQL

Hospital Information System having the following Information

Patient Number, Patient Name, Age, Doctor, Patient, type(in/out), Consultation charge, Blood test charge, Xray charge, other test

Write a PL/SQL procedure to retrieve the following

     1.  Patient undergo blood test

     2.  The patient taken x-ray

      3.  The patient who belong to patient’s category.

Program

SQL> create table patient(p_num number(10) primary key, p_name varchar(20),age number(3),dr varchar(20),p_type varchar(3) check(p_type=’in’ or p_type=’out’), cons_charge number(10,2), x_ray_charge number(10,2), b_t charge number(10,2), o_t_charge number(10,2));

Table Created

SQL> ed inspat

Insert into patient values(1,’anoop’,35,’anees’,’in’,250,125,360,130);

Insert into patient values(2,’najeeb’,35,’rajiya’,’out’,364,162,355,825);

Insert into patient values(3,’rafeeque’,30,’anees’,’out’,450,0,360,225);

Insert into patient values(4,’latheef’,38,’sajna’,’in’,255,250,0,35);

Insert into patient values(5,’mashbu’,33,’anoop’,’out’,625,0,0,256);

Insert into patient values(6,’basheer’,29,’naseef’,’out’,125,425,0,136);

SQL>@inspat;

15 /

6 rows created.

SQL>alter table patient add (tot_fee number(10,2));

Table altered

SQL>declare

2 cursor p is select*from patient;

3 v_t patient.tot_fee%type;

4 begin

5.for I in P loop

6 v_t:=i.cons_charge+i.b_t_charge+i.x_ray_charge

7 +i.o_t_charge;

8 update patient set tot_fee=v_t where

9 p_num=i.p_num;

10 end loop;

11 end;

12 /

PL/SQL procedure successfully completed

SQL>select p_name from patient where b_t_charge>0;

P_NAME

Anoop

Najeeb

Rafeeque

3 rows selected

SQL>select p_name from patient where x_ray_charge>0;

P_NAME

Anoop

Najeeb

Latheef

Basheer

4 rows selected

SQL>select p_name from patient where p_type=’in’;

P_NAME

Anoop

Latheef

2 rows selected