Table Creation:
  Create table institute(
  name varchar(20),
  phno varchar(20),
  email_id varchar(20),
  course varchar(20),
  enq_date varchar(20),
  fee int,
  paid int,
  due int,
  status varchar(20),
  join_date varchar(20)

  );

Step:1 Package Creation
create package school_tech 
as 
procedure reg(name1 institute.name%type,
phno1 institute.phno%type,
email_id1 institute.email_id%type,
course1 institute.course%type,
enq_date1 institute.enq_date%type);

procedure viewstudentdetails(phno1 institute.phno%type);
procedure joinstudent(phno1 institute.phno%type,fee1 int,paid1 int);
procedure viewstudentfeedetails(phno1 institute.phno%type);
procedure duepayment(phno1 institute.phno%type,new_paid int);
procedure duelist1;
end school_tech;


Step -2: Package Body Creation

create or replace package body school_tech
 is

--Student Registration Program Open
procedure registration(name1 institute.name%type,
phno1 institute.phno%type,
email_id1 institute.email_id%type,
course1 institute.course%type,
enq_date1 institute.enq_date%type) is

begin 
insert into institute(name,phno,email_id,course,enq_date)
values(name1,phno1,email_id1,course1,enq_date1);
end registration;

--Student Registration Program Close


-- Check Student Details in Database Program open

procedure viewstudentdetails(phno1 institute.phno%type) is
cursor view_cursor is
select name,phno,email_id,course,enq_date from institute where phno=phno1;
view_rec view_cursor%rowtype;
begin
 open view_cursor;
loop
fetch view_cursor into view_rec;
exit when view_cursor%notfound;
dbms_output.put_line('name is : '||view_rec.name);
dbms_output.put_line('phone  is : '||view_rec.phno);
dbms_output.put_line('email id is : '||view_rec.email_id);
dbms_output.put_line('course: '||view_rec.course);
dbms_output.put_line('enquiry : '||view_rec.enq_date);
end loop;
end viewstudentdetails;

-- Check Student Details in Database Program Close 

-- Joining Student Program Open

procedure joinstudent(phno1 institute.phno%type,fee1 int,paid1 int) is 
begin
update institute set fees=fee1,paid=paid1,due=fee1-paid1,status='joined',join_date=sysdate where phno=phno1;
end joinstudent;

-- Joining Student Program Close


-- View Student  Fee Details Program Open

procedure viewstudentfeedetails(phno1 institute.phno%type) is
cursor view_cursor is
select name,phno,fees,paid,due,course from institute where phno=phno1 ;
view_rec view_cursor%rowtype;
begin
 open view_cursor;
loop
fetch view_cursor into view_rec;
exit when view_cursor%notfound;
dbms_output.put_line('name is : '||view_rec.name);
dbms_output.put_line('phone  is : '||view_rec.phno);
dbms_output.put_line('total fees is:'||view_rec.fees);
dbms_output.put_line('fees paid is : '||view_rec.paid);
dbms_output.put_line('course: '||view_rec.course);
dbms_output.put_line('due : '||view_rec.due);
end loop;
end viewstudentfeedetails;

-- View Student  Fee Details Program Close



-- Due Payment Program Open

procedure duepayment(phno1 institute.phno%type,new_paid int) is 
begin
update institute set paid=paid+new_paid,due=due-new_paid where phno=phno1;
end duepayment;

-- Due Payment Program close


-- Due List Program Open

procedure duelist1 is
cursor view_cursor is
select name,phno,email_id,course,enq_date,fee,paid,due from institute where due>0;
view_rec view_cursor%rowtype;
begin
 open view_cursor;
loop
fetch view_cursor into view_rec;
exit when view_cursor%notfound;
dbms_output.put_line('name is : view_rec.name);
dbms_output.put_line('phone  is : '||view_rec.phno);
dbms_output.put_line('email id is : '||view_rec.email_id);
dbms_output.put_line('course: '||view_rec.course);
dbms_output.put_line('enquiry : '||view_rec.enq_date);
dbms_output.put_line('fee : '||view_rec.fee);
dbms_output.put_line('paid : '||view_rec.paid);
dbms_output.put_line('due : '||view_rec.duw);
end loop;
end duelist1;

-- Due List Program Close

end school_tech;



Step: 3 Package Calling Program

begin
--school_tech.registration('murali',9000014367,'krishnaduggineni11@gmail','plsql developer',sysdate);
--school_tech.viewstudentdetails(9000014367);
--school_tech.joinstudent(9000014367,9000,1000);
--school_tech.viewstudentfeedetails(9000014367);
--school_tech.duepayment(9000014367,2000);
--school_tech.duelist1;

end;