MyTetra Share
Делитесь знаниями!
Package Example
Время создания: 22.10.2017 09:28
Раздел: PL/SQL - Задачник - Packages
Запись: xintrea/mytetra_db_mcold/master/base/15086537075onsniig2t/text.html на raw.githubusercontent.com

Example 10-8 creates a table, log, and a package, emp_admin, and then invokes package subprograms from an anonymous block. The package has both specification and body.

The specification declares a public type, cursor, and exception, and three public subprograms. One public subprogram is overloaded (for information about overloaded subprograms, see "Overloaded Subprograms").

The body declares a private variable, defines the public cursor and subprograms that the specification declares, declares and defines a private function, and has an initialization part.

The initialization part (which runs only the first time the anonymous block references the package) inserts one row into the table log and initializes the private variable number_hired to zero. Every time the package procedure hire_employee is invoked, it updates the private variable number_hired.


Example 10-8 Creating emp_admin Package

-- Log to track changes (not part of package):


DROP TABLE log;

CREATE TABLE log (

date_of_action DATE,

user_id VARCHAR2(20),

package_name VARCHAR2(30)

);


-- Package specification:


CREATE OR REPLACE PACKAGE emp_admin AS

-- Declare public type, cursor, and exception:

TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);

CURSOR desc_salary RETURN EmpRecTyp;

invalid_salary EXCEPTION;


-- Declare public subprograms:


FUNCTION hire_employee (

last_name VARCHAR2,

first_name VARCHAR2,

email VARCHAR2,

phone_number VARCHAR2,

job_id VARCHAR2,

salary NUMBER,

commission_pct NUMBER,

manager_id NUMBER,

department_id NUMBER

) RETURN NUMBER;


-- Overload preceding public subprogram:

PROCEDURE fire_employee (emp_id NUMBER);

PROCEDURE fire_employee (emp_email VARCHAR2);


PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);

FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;

END emp_admin;

/

-- Package body:


CREATE OR REPLACE PACKAGE BODY emp_admin AS

number_hired NUMBER; -- private variable, visible only in this package


-- Define cursor declared in package specification:


CURSOR desc_salary RETURN EmpRecTyp IS

SELECT employee_id, salary

FROM employees

ORDER BY salary DESC;


-- Define subprograms declared in package specification:


FUNCTION hire_employee (

last_name VARCHAR2,

first_name VARCHAR2,

email VARCHAR2,

phone_number VARCHAR2,

job_id VARCHAR2,

salary NUMBER,

commission_pct NUMBER,

manager_id NUMBER,

department_id NUMBER

) RETURN NUMBER

IS

new_emp_id NUMBER;

BEGIN

new_emp_id := employees_seq.NEXTVAL;

INSERT INTO employees (

employee_id,

last_name,

first_name,

email,

phone_number,

hire_date,

job_id,

salary,

commission_pct,

manager_id,

department_id

)

VALUES (

new_emp_id,

hire_employee.last_name,

hire_employee.first_name,

hire_employee.email,

hire_employee.phone_number,

SYSDATE,

hire_employee.job_id,

hire_employee.salary,

hire_employee.commission_pct,

hire_employee.manager_id,

hire_employee.department_id

);

number_hired := number_hired + 1;

DBMS_OUTPUT.PUT_LINE('The number of employees hired is '

|| TO_CHAR(number_hired) );

RETURN new_emp_id;

END hire_employee;


PROCEDURE fire_employee (emp_id NUMBER) IS

BEGIN

DELETE FROM employees WHERE employee_id = emp_id;

END fire_employee;


PROCEDURE fire_employee (emp_email VARCHAR2) IS

BEGIN

DELETE FROM employees WHERE email = emp_email;

END fire_employee;


-- Define private function, available only inside package:


FUNCTION sal_ok (

jobid VARCHAR2,

sal NUMBER

) RETURN BOOLEAN

IS

min_sal NUMBER;

max_sal NUMBER;

BEGIN

SELECT MIN(salary), MAX(salary)

INTO min_sal, max_sal

FROM employees

WHERE job_id = jobid;


RETURN (sal >= min_sal) AND (sal <= max_sal);

END sal_ok;


PROCEDURE raise_salary (

emp_id NUMBER,

amount NUMBER

)

IS

sal NUMBER(8,2);

jobid VARCHAR2(10);

BEGIN

SELECT job_id, salary INTO jobid, sal

FROM employees

WHERE employee_id = emp_id;


IF sal_ok(jobid, sal + amount) THEN -- Invoke private function

UPDATE employees

SET salary = salary + amount

WHERE employee_id = emp_id;

ELSE

RAISE invalid_salary;

END IF;

EXCEPTION

WHEN invalid_salary THEN

DBMS_OUTPUT.PUT_LINE ('The salary is out of the specified range.');

END raise_salary;


FUNCTION nth_highest_salary (

n NUMBER

) RETURN EmpRecTyp

IS

emp_rec EmpRecTyp;

BEGIN

OPEN desc_salary;

FOR i IN 1..n LOOP

FETCH desc_salary INTO emp_rec;

END LOOP;

CLOSE desc_salary;

RETURN emp_rec;

END nth_highest_salary;


BEGIN -- initialization part of package body

INSERT INTO log (date_of_action, user_id, package_name)

VALUES (SYSDATE, USER, 'EMP_ADMIN');

number_hired := 0;

END emp_admin;

/

-- Invoke packages subprograms in anonymous block:


DECLARE

new_emp_id NUMBER(6);

BEGIN

new_emp_id := emp_admin.hire_employee (

'Belden',

'Enrique',

'EBELDEN',

'555.111.2222',

'ST_CLERK',

2500,

.1,

101,

110

);

DBMS_OUTPUT.PUT_LINE ('The employee id is ' || TO_CHAR(new_emp_id));

emp_admin.raise_salary (new_emp_id, 100);


DBMS_OUTPUT.PUT_LINE (

'The 10th highest salary is '||

TO_CHAR (emp_admin.nth_highest_salary(10).sal) ||

', belonging to employee: ' ||

TO_CHAR (emp_admin.nth_highest_salary(10).emp_id)

);


emp_admin.fire_employee(new_emp_id);

-- You can also delete the newly added employee as follows:

-- emp_admin.fire_employee('EBELDEN');

END;

/

Result is similar to:

The number of employees hired is 1

The employee id is 212

The 10th highest salary is 12075, belonging to employee: 168

There are now 107 employees.

 
MyTetra Share v.0.60
Яндекс индекс цитирования