Creating a Package and Package Body
CREATE OR REPLACE PACKAGE emp_actions
AS -- package specification
PROCEDURE hire_employee(employee_id NUMBER,last_name VARCHAR2,first_name VARCHAR2,email VARCHAR2,phone_number VARCHAR2,hire_date DATE,job_id VARCHAR2,salary NUMBER,commission_pct NUMBER,manager_id NUMBER,department_id NUMBER);
PROCEDURE fire_employee(emp_id NUMBER);
FUNCTION num_above_salary(emp_id NUMBER) RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions
AS -- package body
-- code for procedure hire_employee
PROCEDURE Hire_employee
(employee_id NUMBER,
last_name VARCHAR2,
first_name VARCHAR2,
email VARCHAR2,
phone_number VARCHAR2,
hire_date DATE,
job_id VARCHAR2,
salary NUMBER,
commission_pct NUMBER,
manager_id NUMBER,
department_id NUMBER)
IS
BEGIN
INSERT INTO employees
VALUES (employee_id,
last_name,
first_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id);
END hire_employee;
-- code for procedure fire_employee
PROCEDURE Fire_employee
(emp_id NUMBER)
IS
BEGIN
DELETE FROM employees
WHERE employee_id = emp_id;
END fire_employee;
-- code for function num_above salary
FUNCTION Num_above_salary
(emp_id NUMBER)
RETURN NUMBER
IS
emp_sal NUMBER(8,2);
num_count NUMBER;
BEGIN
SELECT salary
INTO emp_sal
FROM employees
WHERE employee_id = emp_id;
SELECT COUNT(* )
INTO num_count
FROM employees
WHERE salary > emp_sal;
RETURN num_count;
END num_above_salary;
END emp_actions;
/
Calling a Procedure in a Package :-
CALL emp_actions.hire_employee(300, 'Belden', 'Enrique', 'EBELDEN',
'555.111.2222', '31-AUG-04', 'AC_MGR', 9000, .1, 101, 110);
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Number of employees with higher salary: ' ||
TO_CHAR(emp_actions.num_above_salary(120)));
emp_actions.fire_employee(300);
END;
/
Posts
0 comments:
Post a Comment