Gehen Sie wie folgt vor, um ein PL/SQL-Paket zu erstellen:
Eine Paketspezifikation definiert, auf welche Paketobjekte von außerhalb des Projekts verwiesen werden kann. Sie gibt Routinen an und deklariert Ausnahmen.
Ein Pakethauptteil enthält die Implementierung aller Prozeduren und Funktionen, die in der Paketspezifikation deklariert sind.
CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER DEFAULT 10 ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE DEFAULT sysdate, p_comm NUMBER DEFAULT 0, p_mgr NUMBER, p_deptno NUMBER DEFAULT 10 ); PROCEDURE fire_emp ( p_empno NUMBER
-- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER DEFAULT 10 ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE DEFAULT sysdate, p_comm NUMBER DEFAULT 0, p_mgr NUMBER, p_deptno NUMBER DEFAULT 10 ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;
Das neue PL/SQL-Paket enthält eine Spezifikation und einen Hauptteil.