- 執行CREATE PACKAGE BODY句可建立程式包裝的本體部分,指定 [OR REPLACE] 可重建已建立的程式包裝本體部分,此時不必再次授與該程式包裝的物件權限,也不必再次授與物件權限給己獲此權限的使用者,程式包裝本體部分描述程式包裝成員 [預儲程式和預儲數的處理內容]
格式
CREATE [ OR REPLACE] PACKAGE BODY [schema.]package_name
{ IS | AS}
package_specification
END [package_name];
schema : 結構名稱
package_name : 程式包裝名稱
package_specification : 程式包裝規格,類型定義,指標/變數/常數/例外的聲明,PL/SQL子程式的規格和PL/SQL內部紀錄的(或者是)java常式的聲明(叫用規格)等語法示範
說明 : 建立預儲程式包裝emp_mgmt的程式包裝本體部分
create or replace package body emp_mgmt as
tot_emps NUMBER;
tot_depts NUMBER;
FUNCTION hire
(last_name VARCHAR2, job_id VARCHAR2,
manager_id NUMBER, salary NUMBER,
commission_pct NUMBER, department_id NUMBER)
RETURN NUMBER IS new_empno NUMBER;
BEGIN
SELECT employees_seq.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT INTO employees
VALUES (new_empno, 'First', 'Last','first.last@oracle.com',
'(123)123-1234','18-JUN-02','IT_PROG',90000000,00,
100,110);
tot_emps := tot_emps + 1;
RETURN(new_empno);
END;
FUNCTION create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER IS
new_deptno NUMBER;
BEGIN
SELECT departments_seq.NEXTVAL
INTO new_deptno
FROM dual;
INSERT INTO departments
VALUES (new_deptno, 'department name', 100, 1700);
tot_depts := tot_depts + 1;
RETURN(new_deptno);
END;
PROCEDURE remove_emp (employee_id NUMBER) IS
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
PROCEDURE remove_dept(department_id NUMBER) IS
BEGIN
DELETE FROM departments
WHERE departments.department_id = remove_dept.department_id;
tot_depts := tot_depts - 1;
SELECT COUNT(*) INTO tot_emps FROM employees;
END;
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;
/