创建 employees_pkg 程序包

本节说明如何创建 employees_pkg 程序包、其子程序的工作方式、如何向需要此程序包的用户授予对该程序包的执行权限，以及这些用户如何调用其某个子程序。

创建 employees_pkg 程序包的步骤：

以 app_code 用户身份连接到 Oracle Database。

有关说明，请参阅“从 SQL*Plus 连接到 Oracle Database ”或“从 SQL Developer 连接到 Oracle Database ”。

创建以下同义词：

 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;

可以在 SQL*Plus 或 SQL Developer 工作表中输入 CREATE SYNONYM 语句。或者，可以使用 SQL Developer 工具“Create Synonym（创建同义词）”创建同义词。

创建程序包说明。
创建程序包体。

另请参见：

“创建同义词”
“关于程序包”

创建 employees_pkg 的程序包规范

注：您必须以 app_code 用户身份连接到 Oracle Database。

要为 employees_pkg（经理的 API）创建程序包规范，请使用以下 CREATE PACKAGE 语句。可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。或者，可以使用 SQL Developer 工具“Create Package（创建程序包）”创建程序包。

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;
/

另请参见：

“关于应用程序”
“创建和管理程序包”
Oracle Database PL/SQL Language Reference（了解有关 CREATE PACKAGE 语句的信息）

为 employees_pkg 创建程序包主体

注：您必须以 app_code 用户身份连接到 Oracle Database。

要为 employees_pkg（经理的 API）创建程序包主体，请使用以下 CREATE PACKAGE BODY 语句。可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。或者，可以使用 SQL Developer 工具“Create Body（创建正文）”创建程序包。

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;
/

另请参见：

“关于应用程序”
“创建和管理程序包”
Oracle Database PL/SQL Language Reference（了解有关 CREATE PACKAGE BODY 语句的信息）

教程：显示 employees_pkg 子程序的工作方式

本教程使用 SQL*Plus，说明了 employees_pkg 程序包的子程序是如何工作的。此教程还显示了触发器 employees_aiufer 和 CHECK 约束条件 job_history_date_check 的工作方式。

注：您必须以 SQL*Plus 用户 app_code 的身份连接到 Oracle Database。

要使用 SQL*Plus 显示 employees_pkg 子程序的工作方式，请执行以下操作：

使用格式命令可提高输出的可读性。例如：

 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

为子程序参数 p_result_set 的值声明绑定变量：
```
 VARIABLE c REFCURSOR
```

显示部门 90 中的雇员：

 EXEC employees_pkg.get_employees_in_dept( 90, :c );
 PRINT c

结果：

 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
 ===========================================================================

显示员工 101 的职务历史记录：

 EXEC employees_pkg.get_job_history( 101, :c );
 PRINT c

结果：

 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

显示有关员工 101 的一般信息：

 EXEC employees_pkg.show_employee( 101, :c );
 PRINT c

结果：

 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.

显示有关职务管理副总裁的信息：

 SELECT * FROM jobs WHERE job_title = 'Administration Vice President';

结果：

 JOB_ID     JOB_TITLE                     MIN_SALARY MAX_SALARY

 ---------- ----------------------------- ---------- ----------
 AD_VP      Administration Vice President      15000      30000

尝试为员工 101 指定超出其职务范围的新薪金：

 EXEC employees_pkg.update_salary( 101, 30001 );

结果：

 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

为员工 101 指定其职务范围内的新薪金，并再次显示有关她的一般信息：

 EXEC employees_pkg.update_salary( 101, 18000 );
 EXEC employees_pkg.show_employee( 101, :c );
 PRINT c

结果：

 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.

将雇员 101 的职务更改为其薪金较低的当前职务：

 EXEC employees_pkg.change_job( 101, 'AD_VP', 17500, 90 );

结果：

 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

显示有关员工的信息。（请注意，前一步骤中的语句并未更改薪金；该值是 18000，而不是 17500。）

exec employees_pkg.show_employee( 101, :c );
print c

结果：

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.

另请参见：

SQL*Plus 用户指南和参考，了解有关 SQL*Plus 命令的信息
“创建和管理程序包”

向 app_user 和 app_admin_user 授予执行权限

注：您必须以 app_code 用户身份连接到 Oracle Database。

要向 app_user（通常是经理）和 app_admin_user（应用程序管理员）授予对 package employees_pkg 的执行权限，请使用以下 GRANT 语句（按任意顺序）。可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。

GRANT EXECUTE ON employees_pkg TO app_user;
GRANT EXECUTE ON employees_pkg TO app_admin_user;

另请参见：

“应用方案”
Oracle Database SQL Language Reference（了解有关 GRANT 语句的信息）

教程：以 app_user 或 app_admin_user 的身份调用 get_job_history

本教程使用 SQL*Plus，说明如何以 app_user（通常是经理）或 app_admin_user（应用程序管理员）身份调用子程序 app_code.employees_pkg.get_job_history。

以 app_user 或 app_admin_user 身份调用 employees_pkg.get_job_history 的步骤：

以 app_user 或 app_admin_user 的身份从 SQL*Plus 连接到 Oracle Database。

有关说明，请参阅从 SQL*Plus 连接到 Oracle Database 。

创建以下同义词：

 CREATE SYNONYM employees_pkg FOR app_code.employees_pkg;

显示员工 101 的职务历史记录：

 EXEC employees_pkg.get_job_history( 101, :c );
 PRINT c

结果：

 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