Package 만들기 기본형

create or replace package employee_process as
 type emprecord is record(emp_id int, salary real);
 type deptrecord is record(dept_id int, loc_id int);

 cursor salaries return emprecord;
 invalid_salary exception;

 procedure hire_employee(
   first_name varchar2,
   last_name varchar2,
   emils varchar2,
   job_id varchar2,
   mgr_id real,
   salary real,
   commission real,
   dept_id real);
 
 procedure fire_employee(emp_id int);
 procedure raise_salary(emp_id int, j_id varchar2, amount real);
 function nth_highest_salary(n int) return emprecord;
 function sal_ok(j_id varchar2, salary real) return boolean;

end employee_process;

 

create or replace package body employee_process as
 number_hired int;
 
 cursor salaries return emprecord is
  select employee_id ,salary
  from employees
  order by salary desc;
 
 procedure hire_employee(
  first_name varchar2,
  last_name varchar2,
  emils varchar2,
  job_id varchar2,
  mgr_id real,
  salary real,
  commission real,
  dept_id real) is
   
  new_employee_id int;
   
 begin
  select employees_seq.nextval   
  into new_employee_id
  from dual;
   
 
  INSERT INTO EMPLOYEES
  (
   EMPLOYEE_ID,
   FIRST_NAME,
   LAST_NAME,
   EMAIL,   
   HIRE_DATE,
   JOB_ID,
   SALARY,
   COMMISSION_PCT,
   MANAGER_ID,
   DEPARTMENT_ID
  )
  VALUES
  (
   new_employee_id,
   first_name,
   last_name,
   emils,
   sysdate,
   job_id,
   salary,
   commission,
   mgr_id,
   dept_id
  );
   
  number_hired :=number_hired +1;
  commit;
 end hire_employee;
 
 procedure fire_employee(emp_id int) is
 begin
  delete employees
  where employee_id = emp_id;

  commit;
   
  exception when others then
   dbms_output.put_line('delete errors');
   rollback;
 end fire_employee;
 
 
 function sal_ok(j_id varchar2, salary real)
  return boolean is
   min_sal real;
   max_sal real;
 begin
  select min_salary, max_salary
  into min_sal, max_sal
  from jobs
  where job_id = j_id;
     
  return (salary >= min_sal) and (salary <= max_sal);
 end sal_ok;
 
 procedure raise_salary(emp_id int, j_id varchar2, amount real) is
  current_sal real;
 begin
  select salary
  into current_sal
  from employees
  where employee_id = emp_id;
   
   
  if sal_ok(j_id, current_sal + amount) then
   update employees
   set salary = current_sal + amount
   where employee_id = emp_id;
  else
   raise invalid_salary;
  end if;
   dbms_output.put_line('update');
  exception when invalid_salary then
   dbms_output.put_line('salaries too high');
 end raise_salary;
 
 function nth_highest_salary(n int)
  return EmpRecord is
  emp_rec emprecord;
 begin
  open salaries;
   
  for i in 1..n loop
   fetch salaries into emp_rec;
  end loop;
   
  close salaries;
   
  return emp_rec;
 
 end nth_highest_salary;

 begin
  number_hired:=0;
 end employee_process;

 

 

실행

exec employee_process.hire_employee('test', 'tete', 'blackstone', 'IT_PROG', 218, 5000, 0, 60);
exec employee_process.raise_salary(218, 'IT_PROG', 100000);
exec employee_process.fire_employee(219);

 

--최고 순위 가져오기

declare
test_emp employee_process.EmpRecord;
begin
  test_emp :=employee_process.nth_highest_salary(1);
 
  dbms_output.put_line('employee_id is: ' || test_emp.emp_id);
  dbms_output.put_line('Salary is : ' || test_emp.salary);
end;