employees_pkg-Paket erstellen
In diesem Abschnitt wird gezeigt, wie das employees_pkg-Package erstellt wird, wie seine Unterprogramme funktionieren, wie die Berechtigung EXECUTE für das Package den Benutzern erteilt wird, die es benötigen, und wie diese Benutzer eines ihrer Unterprogramme aufrufen können.
So erstellen Sie das Paket employees_pkg:
-
Melden Sie sich bei der Datenbank als Benutzer app_code an.
Eine Anleitung finden Sie unter "Verbindung mit Oracle AI Database aus SQL*Plus herstellen" oder "Verbindung mit Oracle AI Database aus SQL Developer herstellen".
-
Erstellen Sie diese Synonyme:
CREATE OR REPLACE SYNONYM employees FOR app_data.employees; CREATE OR REPLACE SYNONYM departments FOR app_data.departments; CREATE OR REPLACE SYNONYM jobs FOR app_data.jobs; CREATE OR REPLACE SYNONYM job_history FOR app_data.job_history;Sie können die CREATE SYNONYM-Anweisungen entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie die Synonyme mit dem SQL Developer-Tool "Synonym erstellen" erstellen.
-
Erstellen Sie die Packagespezifikation.
-
Erstellen Sie den Package Body.
Siehe:
Packagespezifikation für employees_pkg erstellen
Hinweis: Sie müssen als Benutzer app_code mit der Datenbank verbunden sein.
Um die Packagespezifikation für employees_pkg, die API für Manager, zu erstellen, verwenden Sie die folgende CREATE PACKAGE-Anweisung. Sie können die Anweisung entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie das Package mit dem SQL Developer-Tool {\b Create Package} erstellen.
CREATE OR REPLACE PACKAGE employees_pkg
AS
PROCEDURE get_employees_in_dept
( p_deptno IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR );
PROCEDURE get_job_history
( p_employee_id IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR );
PROCEDURE show_employee
( p_employee_id IN employees.employee_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR );
PROCEDURE update_salary
( p_employee_id IN employees.employee_id%TYPE,
p_new_salary IN employees.salary%TYPE );
PROCEDURE change_job
( p_employee_id IN employees.employee_id%TYPE,
p_new_job IN employees.job_id%TYPE,
p_new_salary IN employees.salary%TYPE := NULL,
p_new_dept IN employees.department_id%TYPE := NULL );
END employees_pkg;
/Siehe:
-
Oracle AI Database PL/SQL Language Reference für Informationen über die CREATE PACKAGE-Anweisung
Package Body für employees_pkg erstellen
Hinweis: Sie müssen als Benutzer app_code mit der Datenbank verbunden sein.
Um den PACKAGE BODY für employees_pkg, die API für Manager, zu erstellen, verwenden Sie die folgende CREATE PACKAGE BODY-Anweisung. Sie können die Anweisung entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie das Package mit dem SQL Developer-Tool {\b Create Body} erstellen.
CREATE OR REPLACE PACKAGE BODY employees_pkg
AS
PROCEDURE get_employees_in_dept
( p_deptno IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR )
IS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN p_result_set FOR
SELECT e.employee_id,
e.first_name || ' ' || e.last_name name,
TO_CHAR( e.hire_date, 'Dy Mon ddth, yyyy' ) hire_date,
j.job_title,
m.first_name || ' ' || m.last_name manager,
d.department_name
FROM employees e INNER JOIN jobs j ON (e.job_id = j.job_id)
LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id)
INNER JOIN departments d ON (e.department_id = d.department_id)
WHERE e.department_id = p_deptno ;
END get_employees_in_dept;
PROCEDURE get_job_history
( p_employee_id IN employees.department_id%TYPE,
p_result_set IN OUT SYS_REFCURSOR )
IS
BEGIN
OPEN p_result_set FOR
SELECT e.First_name || ' ' || e.last_name name, j.job_title,
e.job_start_date start_date,
TO_DATE(NULL) end_date
FROM employees e INNER JOIN jobs j ON (e.job_id = j.job_id)
WHERE e.employee_id = p_employee_id
UNION ALL
SELECT e.First_name || ' ' || e.last_name name,
j.job_title,
jh.start_date,
jh.end_date
FROM employees e INNER JOIN job_history jh
ON (e.employee_id = jh.employee_id)
INNER JOIN jobs j ON (jh.job_id = j.job_id)
WHERE e.employee_id = p_employee_id
ORDER BY start_date DESC;
END get_job_history;
PROCEDURE show_employee
( p_employee_id IN employees.employee_id%TYPE,
p_result_set IN OUT sys_refcursor )
IS
BEGIN
OPEN p_result_set FOR
SELECT *
FROM (SELECT TO_CHAR(e.employee_id) employee_id,
e.first_name || ' ' || e.last_name name,
e.email_addr,
TO_CHAR(e.hire_date,'dd-mon-yyyy') hire_date,
e.country_code,
e.phone_number,
j.job_title,
TO_CHAR(e.job_start_date,'dd-mon-yyyy') job_start_date,
to_char(e.salary) salary,
m.first_name || ' ' || m.last_name manager,
d.department_name
FROM employees e INNER JOIN jobs j on (e.job_id = j.job_id)
RIGHT OUTER JOIN employees m ON (m.employee_id = e.manager_id)
INNER JOIN departments d ON (e.department_id = d.department_id)
WHERE e.employee_id = p_employee_id)
UNPIVOT (VALUE FOR ATTRIBUTE IN (employee_id, name, email_addr, hire_date,
country_code, phone_number, job_title, job_start_date, salary, manager,
department_name) );
END show_employee;
PROCEDURE update_salary
( p_employee_id IN employees.employee_id%type,
p_new_salary IN employees.salary%type )
IS
BEGIN
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_employee_id;
END update_salary;
PROCEDURE change_job
( p_employee_id IN employees.employee_id%TYPE,
p_new_job IN employees.job_id%TYPE,
p_new_salary IN employees.salary%TYPE := NULL,
p_new_dept IN employees.department_id%TYPE := NULL )
IS
BEGIN
INSERT INTO job_history (employee_id, start_date, end_date, job_id,
department_id)
SELECT employee_id, job_start_date, TRUNC(SYSDATE), job_id, department_id
FROM employees
WHERE employee_id = p_employee_id;
UPDATE employees
SET job_id = p_new_job,
department_id = NVL( p_new_dept, department_id ),
salary = NVL( p_new_salary, salary ),
job_start_date = TRUNC(SYSDATE)
WHERE employee_id = p_employee_id;
END change_job;
END employees_pkg;
/Siehe:
-
Oracle AI Database PL/SQL Language Reference für Informationen über die Anweisung CREATE PACKAGE BODY
Tutorial: Funktionsweise der employees_pkg-Unterprogramme anzeigen
In diesem Tutorial wird mit SQL*Plus gezeigt, wie die Unterprogramme des employees_pkg-Packages funktionieren. Das Tutorial zeigt auch, wie der Trigger employees_aiufer und der CHECK-Constraint job_history_date_check funktionieren.
Hinweis: Sie müssen über SQL*Plus als Benutzer app_code mit Oracle AI Database verbunden sein.
So zeigen Sie mit SQL*Plus, wie die Unterprogramme employees_pkg funktionieren:
-
Verwenden Sie Formatierungsbefehle, um die Lesbarkeit der Ausgabe zu verbessern. Beispiel:
SET LINESIZE 80 SET RECSEP WRAPPED SET RECSEPCHAR "=" COLUMN NAME FORMAT A15 WORD_WRAPPED COLUMN HIRE_DATE FORMAT A20 WORD_WRAPPED COLUMN DEPARTMENT_NAME FORMAT A10 WORD_WRAPPED COLUMN JOB_TITLE FORMAT A29 WORD_WRAPPED COLUMN MANAGER FORMAT A11 WORD_WRAPPED -
Deklarieren Sie eine Bind-Variable für den Wert des Unterprogrammparameters p_result_set:
VARIABLE c REFCURSOR -
Mitarbeiter in Abteilung 90 anzeigen:
EXEC employees_pkg.get_employees_in_dept( 90, :c ); PRINT cErgebnis:
EMPLOYEE_ID NAME HIRE_DATE JOB_TITLE ----------- --------------- -------------------- -------------------------- MANAGER DEPARTMENT ----------- ---------- 100 Steven King Tue Jun 17th, 2003 President Executive =========================================================================== 102 Lex De Haan Sat Jan 13th, 2001 Administration Vice President Steven King Executive =========================================================================== 101 Neena Kochhar Wed Sep 21st, 2005 Administration Vice President Steven King Executive =========================================================================== -
Tätigkeitshistorie von Mitarbeiter 101 anzeigen:
EXEC employees_pkg.get_job_history( 101, :c ); PRINT cErgebnis:
NAME JOB_TITLE START_DAT END_DATE --------------- ----------------------------- --------- --------- Neena Kochhar Administration Vice President 16-MAR-05 Neena Kochhar Accounting Manager 28-OCT-01 15-MAR-05 Neena Kochhar Public Accountant 21-SEP-97 27-OCT-01 -
Allgemeine Informationen zu Mitarbeiter 101 anzeigen:
EXEC employees_pkg.show_employee( 101, :c ); PRINT cErgebnis:
ATTRIBUTE VALUE --------------- ---------------------------------------------- EMPLOYEE_ID 101 NAME Neena Kochhar EMAIL_ADDR NKOCHHAR HIRE_DATE 21-sep-2005 COUNTRY_CODE +1 PHONE_NUMBER 515.123.4568 JOB_TITLE Administration Vice President JOB_START_DATE 16-mar-05 SALARY 17000 MANAGER Steven King DEPARTMENT_NAME Executive 11 rows selected. -
Informationen zum Job Administration Vice President anzeigen:
SELECT * FROM jobs WHERE job_title = 'Administration Vice President';Ergebnis:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------- ---------- ---------- AD_VP Administration Vice President 15000 30000 -
Versuchen Sie, dem Mitarbeiter 101 ein neues Gehalt außerhalb des Bereichs für seine Tätigkeit zu geben:
EXEC employees_pkg.update_salary( 101, 30001 );Ergebnis:
SQL> EXEC employees_pkg.update_salary( 101, 30001 ); BEGIN employees_pkg.update_salary( 101, 30001 ); END; * ERROR at line 1: ORA-20002: Salary modification invalid ORA-06512: at "APP_DATA.EMPLOYEES_AIUFER", line 13 ORA-04088: error during execution of trigger 'APP_DATA.EMPLOYEES_AIUFER' ORA-06512: at "APP_CODE.EMPLOYEES_PKG", line 77 ORA-06512: at line 1 -
Geben Sie dem Mitarbeiter 101 ein neues Gehalt innerhalb des Bereichs für seine Tätigkeit und zeigen Sie erneut allgemeine Informationen zu ihm an:
EXEC employees_pkg.update_salary( 101, 18000 ); EXEC employees_pkg.show_employee( 101, :c ); PRINT cErgebnis:
ATTRIBUTE VALUE --------------- ---------------------------------------------- EMPLOYEE_ID 101 NAME Neena Kochhar EMAIL_ADDR NKOCHHAR HIRE_DATE 21-sep-2005 COUNTRY_CODE +1 PHONE_NUMBER 515.123.4568 JOB_TITLE Administration Vice President JOB_START_DATE 16-mar-05 SALARY 18000 MANAGER Steven King DEPARTMENT_NAME Executive 11 rows selected. -
Ändern Sie die Tätigkeit des Mitarbeiters 101 in seine aktuelle Tätigkeit mit einem niedrigeren Gehalt:
EXEC employees_pkg.change_job( 101, 'AD_VP', 17500, 90 );Ergebnis:
SQL> exec employees_pkg.change_job( 101, 'AD_VP', 17500, 90 ); BEGIN employees_pkg.change_job( 101, 'AD_VP', 17500, 80 ); END; * ERROR at line 1: ORA-02290: check constraint (APP_DATA.JOB_HISTORY_DATE_CHECK) violated ORA-06512: at "APP_CODE.EMPLOYEES_PKG", line 101 ORA-06512: at line 1 -
Daten zum Mitarbeiter anzeigen (Beachten Sie, dass das Gehalt durch die Anweisung in der vorherigen Stufe nicht geändert wurde. Es ist 18000, nicht 17500.)
exec employees_pkg.show_employee( 101, :c ); print cErgebnis:
ATTRIBUTE VALUE --------------- ---------------------------------------------- EMPLOYEE_ID 101 NAME Neena Kochhar EMAIL_ADDR NKOCHHAR HIRE_DATE 21-sep-2005 COUNTRY_CODE +1 PHONE_NUMBER 515.123.4568 JOB_TITLE Administration Vice President JOB_START_DATE 10-mar-2015 SALARY 18000 MANAGER Steven King DEPARTMENT_NAME Executive 11 rows selected.
Siehe:
-
SQL*Plus User's Guide and Reference für Informationen zu SQL*Plus-Befehlen
EXECUTE-Berechtigung für app_user und app_admin_user erteilen
Hinweis: Sie müssen als Benutzer app_code mit der Datenbank verbunden sein.
Um die Berechtigung EXECUTE für das Paket employees_pkg an app_user (in der Regel ein Manager) und app_admin_user (ein Anwendungsadministrator) zu erteilen, verwenden Sie die folgenden GRANT-Anweisungen (in jeder Reihenfolge). Sie können die Anweisungen entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben.
GRANT EXECUTE ON employees_pkg TO app_user;
GRANT EXECUTE ON employees_pkg TO app_admin_user;Siehe:
-
Oracle AI Database SQL Language Reference für Informationen über die GRANT-Anweisung
Tutorial: get_job_history wird als app_user oder app_admin_user aufgerufen
In diesem Tutorial wird mit SQL*Plus gezeigt, wie Sie das Unterprogramm app_code.employees_pkg.get_job_history als Benutzer app_user (in der Regel ein Manager) oder app_admin_user (ein Anwendungsadministrator) aufrufen.
So rufen Sie employees_pkg.get_job_history als app_user oder app_admin_user auf:
-
Melden Sie sich über SQL*Plus als Benutzer app_user oder app_admin_user bei der Datenbank an.
Eine Anleitung finden Sie unter "Verbindung mit Oracle AI Database aus SQL*Plus herstellen".
-
Erstellen Sie das folgende Synonym:
CREATE SYNONYM employees_pkg FOR app_code.employees_pkg; -
Tätigkeitshistorie von Mitarbeiter 101 anzeigen:
EXEC employees_pkg.get_job_history( 101, :c ); PRINT cErgebnis:
NAME JOB_TITLE START_DAT END_DATE --------------- ----------------------------- --------- --------- Neena Kochhar Administration Vice President 16-MAR-05 15-MAY-12 Neena Kochhar Accounting Manager 28-OCT-01 15-MAR-05 Neena Kochhar Public Accountant 21-SEP-97 27-OCT-01