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のシノニムの作成ツールを使用して表を作成できます。
パッケージ仕様を作成します。
パッケージ本体を作成します。

関連情報:

employees_pkgのパッケージ仕様の作成

ノート: Oracle Databaseにはユーザーapp_codeとして接続する必要があります。

マネージャ用のAPIであるemployees_pkgのパッケージ仕様を作成するには、次のCREATE PACKAGE文を使用します。SQL*PlusまたはSQL Developerのワークシートのいずれかで、文を入力できます。または、SQL Developerツールの「パッケージの作成」を使用してパッケージを作成できます。

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

関連情報:

アプリケーションについて
パッケージの作成および管理
CREATE PACKAGE文の詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。

employees_pkgのパッケージ本体の作成

ノート: Oracle Databaseにはユーザーapp_codeとして接続する必要があります。

マネージャ用のAPIであるemployees_pkgのパッケージ本体を作成するには、次の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;
/

関連情報:

アプリケーションについて
パッケージの作成および管理
CREATE PACKAGE BODY文の詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。

チュートリアル: employees_pkgサブプログラムの動作内容の表示

このチュートリアルでは、SQL*Plusを使用して、employees_pkgパッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーemployees_aiuferおよびCHECK制約job_history_date_checkの動作内容を表示します。

ノート: Oracle Databaseにユーザーapp_codeとしてSQL*Plusから接続する必要があります。

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に、職務の範囲内の新規給与を割り当て、再度従業員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

従業員に関する情報が表示されます。(給与が前のステップの文で変更されず、17500ではなく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  10-mar-2015
SALARY          18000
MANAGER         Steven King
DEPARTMENT_NAME Executive

11 rows selected.

関連情報:

SQL*Plusコマンドの詳細は、SQL*Plusユーザーズ・ガイドおよびリファレンスを参照してください。
パッケージの作成および管理

app_userおよびapp_admin_userへの実行権限の付与

ノート: Oracle Databaseにはユーザーapp_codeとして接続する必要があります。

パッケージemployees_pkgの実行権限をapp_user(通常はマネージャ)およびapp_admin_user(アプリケーション管理者)に付与するには、次のGRANT文を使用します(順番はいずれか)。SQL*PlusまたはSQL Developerのワークシートのいずれかで、文を入力できます。

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

関連情報:

アプリケーションのスキーマ
GRANT文の詳細は、Oracle Database SQLリファレンスを参照

チュートリアル: app_userまたはapp_admin_userとしてのget_job_historyの起動

このチュートリアルは、SQL*Plusを使用して、サブプログラムapp_code.employees_pkg.get_job_historyをユーザーapp_user (通常はマネージャ)またはapp_admin_user (アプリケーション管理者)として起動する方法を示します。

app_userまたはapp_admin_userとしてemployees_pkg.get_job_historyを起動するステップ:

ユーザーapp_userまたはapp_admin_userとしてOracle DatabaseにSQL*Plusから接続します。

手順については、「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