--1.CHECK
DBA_PROCEDURES
dba_errors
// check tr?ng thái c?a package (valid, invalid)
select * from DBA_OBJECTS where owner like 'PNE_OWNER' and object_type like 'PACKAGE'
// View content source package, function, procedure theo dòng
--2.CREATE PACKAGE
• Creating a Package:
CREATE OR REPLACE PACKAGE emp_mgmt AS
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
no_comm EXCEPTION;
no_sal EXCEPTION;
END emp_mgmt;
• Creating a Package Body:
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
tot_emps NUMBER;
tot_depts NUMBER;
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
curr_sal NUMBER;
BEGIN
SELECT salary INTO curr_sal FROM employees
WHERE employees.employee_id = increase_sal.employee_id;
IF curr_sal IS NULL
THEN RAISE no_sal;
ELSE
UPDATE employees
SET salary = salary + salary_incr
WHERE employee_id = employee_id;
END IF;
END;
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
curr_comm NUMBER;
BEGIN
SELECT commission_pct
INTO curr_comm
FROM employees
WHERE employees.employee_id = increase_comm.employee_id;
IF curr_comm IS NULL
THEN RAISE no_comm;
ELSE
UPDATE employees
SET commission_pct = commission_pct + comm_incr;
END IF;
END;
END emp_mgmt;
--3.ALTER PACKAGE
Recompile a stored package.
ALTER PACKAGE emp_mgmt COMPILE PACKAGE;
ALTER PACKAGE hr.emp_mgmt COMPILE BODY;
--4.DROP PACKAGE
DROP PACKAGE [BODY] [schema.]package_name;
