1. 2日で開発者ガイド
  2. 簡易的なOracle Databaseアプリケーションの開発

9 簡易的なOracle Databaseアプリケーションの開発

この簡易なアプリケーションを開発する手順に従って、Oracle Databaseアプリケーションの一般的な開発手順を学習します。

内容は次のとおりです。

  • アプリケーションについて
    アプリケーションには、次の目的、構造、およびネーミング規則があります。
  • アプリケーションのスキーマの作成
    この項の手順を使用して、アプリケーションのスキーマを作成します。
  • スキーマへの権限の付与
    スキーマに権限を付与するには、SQL文GRANTを使用します。
  • スキーマ・オブジェクトの作成およびデータのロード
    この項では、アプリケーションの表、エディショニング・ビュー、トリガーおよび順序の作成方法、表へのデータのロード方法、およびこれらのスキーマ・オブヘクトの権限を必要とするユーザーへの付与方法について説明します。
  • employees_pkgパッケージの作成
    この項では、employees_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージ実行権限の付与方法、およびサブプログラムの起動方法を説明します。
  • admin_pkgパッケージの作成
    この項では、admin_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージ実行権限の付与方法、およびサブプログラムの起動方法を説明します。

9.1 アプリケーションについて

アプリケーションには、次の目的、構造、およびネーミング規則があります。

内容は次のとおりです。

9.1.1 アプリケーションの目的

アプリケーションは、会社内の2種類のユーザーを対象としています。

  • 一般ユーザー(従業員のマネージャ)

  • アプリケーション管理者

一般ユーザーは、次の操作を行えます。

  • 指定した部門の従業員の取得

  • 指定した従業員の職務履歴の取得

  • 指定した従業員の一般情報の表示(名前、部門、職務、マネージャ、給与など)

  • 指定した従業員の給与の変更

  • 指定した従業員の職務の変更

アプリケーション管理者は、次の操作を行えます。

  • 既存の職務のID、タイトルまたは給与範囲の変更

  • 新規職務の追加

  • 既存部門のID、名前またはマネージャの変更

  • 新規部門の追加

9.1.2 アプリケーションの構造

アプリケーションでは、次のスキーマ・オブジェクトとスキーマが使用されます。

内容は次のとおりです。

9.1.2.1 アプリケーションのスキーマ・オブジェクト

アプリケーションは、次のスキーマ・オブジェクトで構成されています。

  • 次のデータを格納している4つの表

    • ジョブ

    • 部門

    • 従業員

    • 従業員の職務履歴

  • 表をカバーし、エディションベース再定義(EBR)を使用して、使用中の完成アプリケーションをアップグレードできるようにする、4つのエディショニング・ビュー

  • ビジネス・ルールを実施する2つのトリガー

  • 新規部門および新規従業員用の一意の主キーを生成する2つの順序

  • 2つのパッケージ

    • employees_pkg(一般ユーザー用のアプリケーション・プログラム・インタフェース(API))

    • admin_pkg(アプリケーション管理者用のAPI)

    一般ユーザーおよびアプリケーション管理者は、APIを介してのみアプリケーションにアクセスします。このため、パッケージ・サブプログラムを起動することによってのみデータを変更できます。

関連項目:

9.1.2.2 アプリケーションのスキーマ

セキュリティのために、アプリケーションでは、次の5つのスキーマ(またはユーザー)が使用され、それぞれに必要な権限のみが付与されています。

  • app_data: パッケージ以外のすべてのスキーマ・オブジェクトを所有し、その表に、サンプル・スキーマHRの表のデータをロードするスキーマ

    パッケージを作成する開発者は、このスキーマでは作業しません。このため、開発者が誤ってアプリケーション・スキーマ・オブジェクトを変更したり削除したりすることはありません。

  • app_code: パッケージemployees_pkgのみを所有するスキーマ

    employees_pkgの開発者はこのスキーマで作業します。

  • app_admin: パッケージadmin_pkgのみを所有するスキーマ

    admin_pkgの開発者はこのスキーマで作業します。

  • app_user: 何も所有せず、employees_pkgのみを実行できる一般アプリケーション・ユーザー

    中間層アプリケーション・サーバーは、接続プールのデータベースにapp_userとして接続します。このスキーマが、SQLインジェクション・バグなどによって危険にさらされた場合、攻撃者は、employees_pkgサブプログラムによって表示と変更が許可されている内容のみを表示および変更できます。攻撃者は、表の削除、権限のエスカレート、スキーマ・オブジェクトの作成または変更など、他の作業は行えません。

  • app_admin_user: 何も所有せず、admin_pkgおよびemployees_pkgのみを実行できるアプリケーション管理者

    このスキーマの接続プールは、非常に小さく、権限を持つユーザーのみがアクセスできます。このスキーマが危険にさらされた場合、攻撃者は、 admin_pkgおよびemployees_pkgサブプログラムによって表示と変更が許可されている内容のみを表示および変更できます。

app_userおよびapp_admin_userではなく、アプリケーションは、何も所有せず、employees_pkgおよびadmin_pkgの両方を実行できるスキーマのみを持つとします。このスキーマの接続プールには、一般ユーザーとアプリケーション管理者の両方に対して十分な大きさが必要です。SQLインジェクション・バグがemployees_pkgにあった場合、そのバグを利用した一般ユーザーはadmin_pkgにアクセスできます。

app_data、app_codeおよびapp_adminではなく、アプリケーションは、パッケージを含むすべてのスキーマ・オブジェクトを所有するスキーマを1つのみ持つとします。この場合、パッケージは、不要な権限と好ましくない権限の両方を含む、表のすべての権限を持ちます。

たとえば、監査証跡の表AUDIT_TRAILを使用しているとします。employees_pkgの開発者はAUDIT_TRAILへの書込みはできるが、読取りや変更はできないようにするとします。admin_pkgの開発者はAUDIT_TRAILの読取りと書込みはできるが、変更はできないようにするとします。AUDIT_TRAIL、employees_pkgおよびadmin_pkgが同じスキーマに属している場合、2つのパッケージの開発者はAUDIT_TRAILに対するすべての権限を持ちます。ただし、AUDIT_TRAILがapp_dataに属し、employees_pkgがapp_codeに属し、admin_pkgがapp_adminに属す場合、app_dataとしてデータベースにアクセスし、次を実行できます。

GRANT INSERT ON AUDIT_TRAIL TO app_code;
GRANT INSERT, SELECT ON AUDIT_TRAIL TO app_admin;

関連項目:

9.1.3 アプリケーションのネーミング規則

アプリケーションでは、次のネーミング規則が使用されます。

アイテム 名前

表

table#

table#のエディショニング・ビュー

table

エディショニング・ビューtableのトリガー

table_{a|b}event[_fer]

  • aは、AFTERトリガーを示します。

  • bは、BEFOREトリガーを示します。

  • ferは、FOR EACH ROWトリガーを示しています。

  • eventは、トリガーを起動するイベントを示します。たとえば、INSERTの場合i、INSERTまたはUPDATEの場合iu、DELETEの場合dです。

table#内のPRIMARY KEY制約

table_pk

table#.columnのNOT NULL制約

table_column_not_null脚注1

table#.columnのUNIQUE制約

table_column_unique脚注1

table#.columnのCHECK制約

table_column_check脚注1

table1#.columnからtable2#.columnへのREF制約

table1_to_table2_fk脚注1

table1#.column1からtable2#.column2へのREF制約

table1_col1_to_table2_col2_fk脚注1 脚注2

table#の順序

table_sequence

パラメータ名

p_name

ローカル変数名

l_name

脚注1

table、table1およびtable2は、employeesの場合はempに、departmentsの場合はdeptに、job_historyの場合はjob_histに短縮されます。

脚注2

col1およびcol2は、列名column1およびcolumn2の略語です。制約名は、30文字以下にする必要があります。

9.2 アプリケーションのスキーマの作成

この項の手順を使用して、アプリケーションのスキーマを作成します。

スキーマの名前は次のとおりです。

  • app_data

  • app_code

  • app_admin

  • app_user

  • app_admin_user

注意:

次の手順では、CREATE USERおよびDROP USERシステム権限を持つユーザーの名前とパスワードが必要です。

スキーマ(またはユーザー)schema_nameを作成する手順:

  1. SQL*Plusを使用して、CREATE USERおよびDROP USERシステム権限を持つユーザーとしてOracle Databaseに接続します。

    SQL>プロンプトが表示されます。

  2. スキーマが存在する場合、スキーマとそのオブジェクトを次のSQL文を使用して削除します。
    DROP USER schema_name CASCADE;

    スキーマが存在していた場合、システムは次のように応答します。

    User dropped.

    スキーマが存在しなかった場合、システムは次のように応答します。

    DROP USER schema_name CASCADE
          *
ERROR at line 1:
ORA-01918: user 'schema_name' does not exist
  3. schema_nameが、app_data、app_codeまたはapp_adminのいずれかの場合、次のSQL文を使用してスキーマを作成します。
    CREATE USER schema_name IDENTIFIED BY password
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS
ENABLE EDITIONS;

    それ以外の場合、次のSQL文を使用してスキーマを作成します。

    CREATE USER schema_name IDENTIFIED BY password
ENABLE EDITIONS;

    注意:

    安全なパスワードを選択してください。セキュアなパスワードの詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。

    システムは次のように応答します。

    User created.
  4. (オプション) 「SQL DeveloperからOracle Databaseへの接続」の説明に従って、SQL Developerで、スキーマの接続を作成します。

関連項目:

9.3 スキーマへの権限の付与

スキーマに権限を付与するには、SQL文GRANTを使用します。

SQL*PlusまたはSQL DeveloperのワークシートのいずれかでGRANT文を入力できます。セキュリティのために、各スキーマには必要な権限のみを付与してください。

内容は次のとおりです。

関連項目:

9.3.1 app_dataスキーマへの権限の付与

app_dataスキーマには、次を行う権限のみを付与します。

  • Oracle Databaseに接続します。

    GRANT CREATE SESSION TO app_data;

  • アプリケーションの表、ビュー、トリガーおよび順序の作成

    GRANT CREATE TABLE, CREATE VIEW, CREATE TRIGGER, CREATE SEQUENCE TO app_data;

  • サンプル・スキーマHRの4つの表からそれ自体の表へのデータのロード

    GRANT SELECT ON HR.DEPARTMENTS TO app_data;
GRANT SELECT ON HR.EMPLOYEES TO app_data;
GRANT SELECT ON HR.JOB_HISTORY TO app_data;
GRANT SELECT ON HR.JOBS TO app_data;

9.3.2 app_codeスキーマへの権限の付与

app_codeスキーマには、次を行う権限のみを付与します。

  • Oracle Databaseに接続します。

    GRANT CREATE SESSION TO app_code;

  • パッケージemployees_pkgの作成

    GRANT CREATE PROCEDURE TO app_code;

  • シノニムの作成(利便性のため):

    GRANT CREATE SYNONYM TO app_code;

9.3.3 app_adminスキーマへの権限の付与

app_adminスキーマには、次を行う権限のみを付与します。

  • Oracle Databaseに接続します。

    GRANT CREATE SESSION TO app_admin;

  • パッケージadmin_pkgの作成

    GRANT CREATE PROCEDURE TO app_admin;

  • シノニムの作成(利便性のため):

    GRANT CREATE SYNONYM TO app_admin;

9.3.4 app_userおよびapp_admin_userスキーマへの権限の付与

app_userおよびapp_admin_userスキーマには、次を行う権限のみを付与します。

  • Oracle Databaseに接続します。

    GRANT CREATE SESSION TO app_user;
GRANT CREATE SESSION TO app_admin_user;

  • シノニムの作成(利便性のため)

    GRANT CREATE SYNONYM TO app_user;
GRANT CREATE SYNONYM TO app_admin_user;

9.4 スキーマ・オブジェクトの作成およびデータのロード

この項では、アプリケーションの表、エディショニング・ビュー、トリガーおよび順序の作成方法、表へのデータのロード方法、およびこれらのスキーマ・オブヘクトの権限を必要とするユーザーへの付与方法について説明します。

スキーマ・オブジェクトの作成およびデータのロード手順:

  1. Oracle Databaseにユーザーapp_data.として接続します
  2. データをロードした後に追加する必要がある外部キー制約を除き、すべての必要な制約を使用して表を作成します。
  3. エディショニング・ビューを作成します。
  4. トリガーを作成します。
  5. 順序を作成します。
  6. データを表にロードします。
  7. 外部キー制約を追加します。

内容は次のとおりです。

9.4.1 表の作成

この項では、データをロードした後に追加する必要がある制約を除き、すべての必要な制約を使用してアプリケーションの表を作成する方法を示します。

注意:

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

次の手順では、SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL DeveloperツールのCreate Tableを使用して表を作成できます。

表を作成する手順:

  1. 社内の職務に関する情報を格納するjobs#を作成します(職務ごとに1行)。
    CREATE TABLE jobs#
( job_id      VARCHAR2(10)
              CONSTRAINT jobs_pk PRIMARY KEY,
  job_title   VARCHAR2(35)
              CONSTRAINT jobs_job_title_not_null NOT NULL,
  min_salary  NUMBER(6)
              CONSTRAINT jobs_min_salary_not_null NOT NULL,
  max_salary  NUMBER(6)
              CONSTRAINT jobs_max_salary_not_null NOT NULL
)
/
  2. 社内の部門に関する情報を格納するdepartments#を作成します(部門ごとに1行)。
    CREATE TABLE departments#
( department_id    NUMBER(4)
                   CONSTRAINT departments_pk PRIMARY KEY,
  department_name  VARCHAR2(30)
                   CONSTRAINT department_name_not_null NOT NULL
                   CONSTRAINT department_name_unique UNIQUE,
  manager_id       NUMBER(6)
)
/
  3. 社内の従業員に関する情報を格納するemployees#を作成します(従業員ごとに1行)。
    CREATE TABLE employees#
( employee_id     NUMBER(6)
                  CONSTRAINT employees_pk PRIMARY KEY,
  first_name      VARCHAR2(20)
                  CONSTRAINT emp_first_name_not_null NOT NULL,
  last_name       VARCHAR2(25)
                  CONSTRAINT emp_last_name_not_null NOT NULL,
  email_addr      VARCHAR2(25) 
                  CONSTRAINT emp_email_addr_not_null NOT NULL,
  hire_date       DATE
                  DEFAULT TRUNC(SYSDATE)
                  CONSTRAINT emp_hire_date_not_null NOT NULL
                  CONSTRAINT emp_hire_date_check
                    CHECK(TRUNC(hire_date) = hire_date),
  country_code    VARCHAR2(5)
                  CONSTRAINT emp_country_code_not_null NOT NULL,
  phone_number    VARCHAR2(20)
                  CONSTRAINT emp_phone_number_not_null NOT NULL,
  job_id          CONSTRAINT emp_job_id_not_null NOT NULL
                  CONSTRAINT emp_jobs_fk REFERENCES jobs#,
  job_start_date  DATE
                  CONSTRAINT emp_job_start_date_not_null NOT NULL,
                  CONSTRAINT emp_job_start_date_check
                    CHECK(TRUNC(JOB_START_DATE) = job_start_date),
  salary          NUMBER(6)
                  CONSTRAINT emp_salary_not_null NOT NULL,
  manager_id      CONSTRAINT emp_mgr_to_empno_fk REFERENCES employees#,
  department_id   CONSTRAINT emp_to_dept_fk REFERENCES departments#
)
/

    REF制約の理由は次のとおりです。

    • 従業員には既存の職務が必要です。つまり、列employees#.job_idの値は、列jobs#.job_idの値でもある必要があります。

    • 従業員には、従業員でもあるマネージャが必要です。つまり、列employees#.manager_idの値は、列employees#.employee_idの値でもある必要があります。

    • 従業員は、既存の部門で勤務している必要があります。つまり、列employees#.department_idの値は、列departments#.department_idの値でもある必要があります。

    また、従業員のマネージャは、従業員が勤務している部門のマネージャである必要があります。つまり、列employees#.manager_idの値は、列departments#.manager_idの値でもある必要があります。ただし、employees#がまだ存在していなかったため、departments#を作成したときに、必要な制約を指定できませんでした。したがって、外部キー制約をdepartments#に後で追加する必要があります(「外部キー制約の追加」を参照)。

  4. 社内の従業員の職務履歴を格納するjob_history#を作成します(従業員の職務ごとに1行。)
    CREATE TABLE job_history#
( employee_id  CONSTRAINT job_hist_to_employees_fk REFERENCES employees#,
  job_id       CONSTRAINT job_hist_to_jobs_fk REFERENCES jobs#,
  start_date   DATE
               CONSTRAINT job_hist_start_date_not_null NOT NULL,
  end_date     DATE
               CONSTRAINT job_hist_end_date_not_null NOT NULL,
  department_id
             CONSTRAINT job_hist_to_departments_fk REFERENCES departments#
             CONSTRAINT job_hist_dept_id_not_null NOT NULL,
             CONSTRAINT job_history_pk PRIMARY KEY(employee_id,start_date),
             CONSTRAINT job_history_date_check CHECK( start_date < end_date )
)
/

    REF制約の理由は、従業員、職務および部門が存在している必要があることです。つまり、次のようになります。

    • 列job_history#.employee_idの値は、列employees#.employee_idの値でもある必要があります。

    • 列job_history#.job_idの値は、列jobs#.job_idの値でもある必要があります。

    • 列job_history#.department_idの値は、列departments#.department_idの値でもある必要があります。

関連項目:

「表の作成」

9.4.2 エディショニング・ビューの作成

注意:

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

エディショニング・ビューを作成するには、次の文を使用します(順序は任意)。SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL Developerのビューの作成ツールを使用して表を作成できます。 

CREATE OR REPLACE EDITIONING VIEW jobs AS SELECT * FROM jobs#
/
CREATE OR REPLACE EDITIONING VIEW departments AS SELECT * FROM departments#
/
CREATE OR REPLACE EDITIONING VIEW employees AS SELECT * FROM employees#
/
CREATE OR REPLACE EDITIONING VIEW job_history AS SELECT * FROM job_history#
/

注意:

アプリケーションは、必ずエディショニング・ビューを介して元表を参照する必要があります。そうではない場合、エディショニング・ビューによって表はカバーされず、EBRを使用して完成アプリケーションをその使用中にアップグレードできません。

関連項目:

9.4.3 トリガーの作成

注意:

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

アプリケーション内のトリガーによって、ビジネス・ルールが実施されます。

  • 職務jの従業員は、職務jの最低給与および最高給与の範囲内の給与を得ている必要があります。

  • 職務jの従業員の給与がsの場合、jの最低給与をsを超える値、またはjの最高給与をs未満の値に変更できません。(これを行うと、既存のデータが無効になります。)

内容は次のとおりです。

関連項目:

トリガーの詳細は、「トリガーの使用」を参照してください。

9.4.3.1 1つ目のビジネス・ルールを実施するトリガーの作成

1つ目のビジネス・ルールは、職務jの従業員は、職務jの最低給与および最高給与の範囲内の給与を得ている必要があるというものです。

このルールは、新規行がemployees表に挿入されるとき、またはemployees表のsalaryまたはjob_id列が更新されたときに違反となる可能性があります。

ルールを実施するには、エディショニング・ビューemployeesに次のトリガーを作成します。SQL*PlusまたはSQL DeveloperのワークシートのいずれかでCREATE TRIGGER文を入力できます。または、SQL DeveloperツールのCreate Triggerを使用してトリガーを作成できます。 

CREATE OR REPLACE TRIGGER employees_aiufer
AFTER INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE jobs IN SHARE MODE;  -- Ensure that jobs does not change
                                  -- during the following query.
  SELECT COUNT(*) INTO l_cnt
  FROM jobs
  WHERE job_id = :NEW.job_id
  AND :NEW.salary BETWEEN min_salary AND max_salary;
 
  IF (l_cnt<>1) THEN
    RAISE_APPLICATION_ERROR( -20002,
      CASE
        WHEN :new.job_id = :old.job_id
        THEN 'Salary modification invalid'
        ELSE 'Job reassignment puts salary out of range'
      END );
  END IF;
END;
/

LOCK TABLE jobs IN SHARE MODEは、トリガーが問合せしている表jobsを他のユーザーが変更できないようにします。トリガーは、employeesを変更中に、非ブロック読取りによりその他のユーザーによってjobsに行われた変更を確認できなくなるため(および、ユーザーはトリガーによりemployeesに行われた変更を確認できなくなるため)、問合せ中にjobsを変更できなくする必要があります。

問合せ中にjobsが変更されないようにするもう1つの方法は、SELECT文にFOR UPDATE句を含めることです。ただし、SELECT FOR UPDATEでは、LOCK TABLE jobs IN SHARE MODEよりも同時実行性が制限されます。

LOCK TABLE jobs IN SHARE MODEは、jobsをユーザーが変更できないようにしますが、共有モードのjobs自体はロックされません。通常、jobsへの変更は、employeesへの変更よりもまれにしか行われません。このため、共有モードのjobsをロックすることにより、排他モードのjobsの単一行をロックすることよりも同時接続性が高くなります。

関連項目:

9.4.3.2 2つ目のビジネス・ルールを実施するトリガーの作成

2つ目のビジネス・ルールは、職務jの従業員の給与がsの場合、jの最低給与をsを超える値、またはjの最高給与をs未満の値に変更できないというものです。(これを行うと、既存のデータが無効になります。)

このルールは、jobs表のmin_salaryまたはmax_salary列が更新されたときに違反となる可能性があります。

ルールを実施するには、エディショニング・ビューjobsに次のトリガーを作成します。SQL*PlusまたはSQL DeveloperのワークシートのいずれかでCREATE TRIGGER文を入力できます。または、SQL DeveloperツールのCreate Triggerを使用してトリガーを作成できます。 

CREATE OR REPLACE TRIGGER jobs_aufer
AFTER UPDATE OF min_salary, max_salary ON jobs FOR EACH ROW
WHEN (NEW.min_salary > OLD.min_salary OR NEW.max_salary < OLD.max_salary)
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE employees IN SHARE MODE;

  SELECT COUNT(*) INTO l_cnt
  FROM employees
  WHERE job_id = :NEW.job_id
  AND salary NOT BETWEEN :NEW.min_salary and :NEW.max_salary;

  IF (l_cnt>0) THEN
    RAISE_APPLICATION_ERROR( -20001,
      'Salary update would violate ' || l_cnt || ' existing employee records' );
  END IF;
END;
/

LOCK TABLE employees IN SHARE MODEは、トリガーが問合せしている表employeesを他のユーザーが変更できないようにします。トリガーは、jobsを変更中に、非ブロック読取りによりその他のユーザーによってemployeesに行われた変更を確認できなくなるため(および、ユーザーはトリガーによりjobsに行われた変更を確認できなくなるため)、問合せ中にemployeesを変更できなくする必要があります。

このトリガーの場合、SELECT FOR UPDATEは、LOCK TABLE IN SHARE MODEの代替手段になりません。ユーザーがこの職務の給与範囲の変更を試行したときに、このトリガーは、他のユーザーが給与を新規範囲外に変更しないようにする必要があります。このため、トリガーは、このjob_idを持つemployees表のすべての行をロックし、このjob_idを持つように変更される可能性のあるすべての行をロックする必要があります。

LOCK TABLE employees IN SHARE MODEの代替手段の1つは、DBMS_LOCKパッケージを使用して、job_idの名前で名前付きロックを作成し、employees表とjobs表の両方でトリガーを使用して、この名前付きロックを使用し同時更新を回避する方法です。ただし、DBMS_LOCKおよび複数のトリガーを使用すると、実行時のパフォーマンスに悪影響を及ぼします。

LOCK TABLE employees IN SHARE MODEの代替手段の1つは、employeesの各変更行について、対応するjobsの職務行をロックするトリガーをemployees表で作成することです。ただし、この手法では、頻繁に起こるemployees表の更新時に大量の作業が発生します。

LOCK TABLE employees IN SHARE MODEは、前述の代替手段よりも単純で、jobs表の変更はまれであり、通常、表をロックすることがユーザーにとって不便ではない、アプリケーションのメンテナンス時に発生します。

関連項目:

9.4.4 順序の作成

注意:

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

新規部門および新規従業員用の一意の主キーを生成する順序を作成するには、次の文を(いずれかの順番で)使用しますSQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL DeveloperツールのCreate Sequenceを使用して順序を作成できます。 

CREATE SEQUENCE employees_sequence START WITH 210;
CREATE SEQUENCE departments_sequence START WITH 275;

サンプル・スキーマHRの表からロードするデータとの競合を回避するために、employees_sequenceおよびdepartments_sequenceの開始番号は、employees.employee_idとdepartments.department_idの最大値をそれぞれ超える値である必要があります。データのロード後、この問合せにより、これらの最大値が表示されます。 

SELECT MAX(e.employee_id), MAX(d.department_id)
FROM employees e, departments d;

結果:

MAX(E.EMPLOYEE_ID) MAX(D.DEPARTMENT_ID)
------------------ --------------------
               206                  270

関連項目:

「順序の作成および管理」

9.4.5 データのロード

注意:

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

サンプル・スキーマHRの表のデータをアプリケーションの表にロードします。

注意:

次の手順は、エディショニング・ビューを介してアプリケーションの表を参照します。

次の手順では、SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。

表にデータをロードする手順:

  1. 表HR.JOBSのデータをjobsにロードします。
    INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
SELECT job_id, job_title, min_salary, max_salary
  FROM HR.JOBS
/

    結果:

    19 rows created.
  2. 表HR.DEPARTMENTSのデータをdepartmentsにロードします。
    INSERT INTO departments (department_id, department_name, manager_id)
SELECT department_id, department_name, manager_id
  FROM HR.DEPARTMENTS
/

    結果:

    27 rows created.
  3. HR.phone_numberからemployees.country_codeおよびemployees.phone_numberを取得する検索CASE式およびSQLファンクション、およびHR.JOB_HISTORYからemployees.job_start_dateを取得するSQLファンクションおよびスカラー副問合せを使用して、表HR.EMPLOYEESおよびHR.JOB_HISTORYのデータをemployeesにロードします。
    INSERT INTO employees (employee_id, first_name, last_name, email_addr,
  hire_date, country_code, phone_number, job_id, job_start_date, salary,
  manager_id, department_id)
SELECT employee_id, first_name, last_name, email, hire_date,
  CASE WHEN phone_number LIKE '011.%'
    THEN '+' || SUBSTR( phone_number, INSTR( phone_number, '.' )+1,
      INSTR( phone_number, '.', 1, 2 ) -  INSTR( phone_number, '.' ) - 1 )
    ELSE '+1'
  END country_code,
  CASE WHEN phone_number LIKE '011.%'
    THEN SUBSTR( phone_number, INSTR(phone_number, '.', 1, 2 )+1 )
    ELSE phone_number
  END phone_number,
  job_id,
  NVL( (SELECT MAX(end_date+1)
        FROM HR.JOB_HISTORY jh
        WHERE jh.employee_id = employees.employee_id), hire_date),
  salary, manager_id, department_id  
  FROM HR.EMPLOYEES
/

    結果:

    107 rows created.

    注意:

    前述のINSERT文は、「1つ目のビジネス・ルールを実施するトリガーの作成」で作成されたトリガーを起動します。

  4. 表HR.JOB_HISTORYのデータをjob_historyにロードします。
    INSERT INTO job_history (employee_id, job_id, start_date, end_date,
  department_id)
SELECT employee_id, job_id, start_date, end_date, department_id
  FROM HR.JOB_HISTORY
/

    結果:

    10 rows created.
  5. 変更をコミットします。
    COMMIT;

関連項目:

9.4.6 外部キー制約の追加

注意:

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

表departmentsおよびemployeesにデータをロードしたら、次のALTER TABLE文を使用して外部キー制約を追加します。SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL DeveloperツールのAdd Foreign Keyを使用して制約を追加できます。 

ALTER TABLE departments#
ADD CONSTRAINT dept_to_emp_fk
FOREIGN KEY(manager_id) REFERENCES employees#;

departments#およびemployees#にデータをロードする前に、この外部キー制約を追加すると、いずれかにデータをロードしようとしたときに次のエラーが表示されます。

ORA-02291: integrity constraint (APP_DATA.JOB_HIST_TO_DEPT_FK) violated - parent key not found

関連項目:

「チュートリアル: 既存の表への制約の追加」

9.4.7 ユーザーへのスキーマ・オブジェクトの権限の付与

注意:

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

ユーザーに権限を付与するには、SQL文GRANTを使用します。SQL*PlusまたはSQL DeveloperのワークシートのいずれかでGRANT文を入力できます。

employees_pkgを作成するために必要な権限のみをapp_codeに付与します。 

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_code;
GRANT SELECT ON departments TO app_code;
GRANT SELECT ON jobs TO app_code;
GRANT SELECT, INSERT on job_history TO app_code;
GRANT SELECT ON employees_sequence TO app_code;

admin_pkgを作成するために必要な権限のみをapp_adminに付与します。 

GRANT SELECT, INSERT, UPDATE, DELETE ON jobs TO app_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON departments TO app_admin;
GRANT SELECT ON employees_sequence TO app_admin;
GRANT SELECT ON departments_sequence TO app_admin;

関連項目:

GRANT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

9.5 employees_pkgパッケージの作成

この項では、employees_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージ実行権限の付与方法、およびサブプログラムの起動方法を説明します。

employees_pkgパッケージの作成手順:

  1. Oracle Databaseにユーザーapp_code.として接続します
  2. 次のシノニムを作成します。
    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のシノニムの作成ツールを使用して表を作成できます。

  3. パッケージ仕様を作成します。
  4. パッケージ本体を作成します。

内容は次のとおりです。

関連項目:

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

関連項目:

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

関連項目:

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

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

注意:

Oracle Databaseには、SQL*Plusでユーザーapp_codeとして接続する必要があります。

SQL*Plusを使用してemployees_pkgサブプログラムの動作内容を表示する手順:

  1. 書式設定コマンドを使用して、出力を読みやすくします。次に例を示します。
    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
  2. サブプログラム・パラメータp_result_setの値のバインド変数を宣言します。
    VARIABLE c REFCURSOR
  3. 部門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                                                      
===========================================================================
  4. 従業員の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
  5. 従業員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.
  6. 職務管理副社長の情報を表示します。
    SELECT * FROM jobs WHERE job_title = 'Administration Vice President';

    結果:

    JOB_ID     JOB_TITLE                     MIN_SALARY MAX_SALARY
---------- ----------------------------- ---------- ----------
AD_VP      Administration Vice President      15000      30000
  7. 従業員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
  8. 従業員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.
  9. 従業員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
  10. 従業員に関する情報が表示されます。(給与が前の手順の文で変更され、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.

関連項目:

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

関連項目:

9.5.5 チュートリアル: 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としてget_job_historyを起動する手順:

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

    手順は、「SQL*PlusからOracle Databaseへの接続」を参照してください。

  2. 次のシノニムを作成します。
    CREATE SYNONYM employees_pkg FOR app_code.employees_pkg;
  3. 従業員の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

9.6 admin_pkgパッケージの作成

この項では、admin_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージの実行権限の付与方法、およびサブプログラムの起動方法を説明します。

admin_pkgパッケージの作成手順:

  1. Oracle Databaseにユーザーapp_admin.として接続します
  2. 次のシノニムを作成します。
    CREATE SYNONYM departments FOR app_data.departments;
CREATE SYNONYM jobs FOR app_data.jobs;
CREATE SYNONYM departments_sequence FOR app_data.departments_sequence;

    SQL*PlusまたはSQL DeveloperのワークシートのいずれかでCREATE SYNONYM文を入力できます。または、SQL DeveloperツールのCreate Synonymを使用して表を作成できます。

  3. パッケージ仕様を作成します。
  4. パッケージ本体を作成します。

内容は次のとおりです。

関連項目:

9.6.1 admin_pkgのパッケージ仕様の作成

注意:

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

アプリケーション管理者用のAPIである、admin_pkgのパッケージ仕様を作成するには、次のCREATE PACKAGE文を使用します。SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL Developerツールのパッケージの作成を使用してパッケージを作成できます。 

CREATE OR REPLACE PACKAGE admin_pkg
AS
  PROCEDURE update_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE := NULL,
      p_min_salary  IN jobs.min_salary%TYPE := NULL,
      p_max_salary  IN jobs.max_salary%TYPE := NULL );

  PROCEDURE add_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE,
      p_min_salary  IN jobs.min_salary%TYPE,
      p_max_salary  IN jobs.max_salary%TYPE );

  PROCEDURE update_department
    ( p_department_id     IN departments.department_id%TYPE,
      p_department_name   IN departments.department_name%TYPE := NULL,
      p_manager_id        IN departments.manager_id%TYPE := NULL,
      p_update_manager_id IN BOOLEAN := FALSE );

  FUNCTION add_department
    ( p_department_name   IN departments.department_name%TYPE,
      p_manager_id        IN departments.manager_id%TYPE )
    RETURN departments.department_id%TYPE;

END admin_pkg;
/

関連項目:

9.6.2 admin_pkgのパッケージ本体の作成

注意:

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

アプリケーション管理者用のAPIである、admin_pkgのパッケージ本体を作成するには、次のCREATE PACKAGE BODY文を使用します。SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL DeveloperツールのCreate Bodyを使用してパッケージを作成できます。 

CREATE OR REPLACE PACKAGE BODY admin_pkg
AS
  PROCEDURE update_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE := NULL,
      p_min_salary  IN jobs.min_salary%TYPE := NULL,
      p_max_salary  IN jobs.max_salary%TYPE := NULL )
  IS
  BEGIN
    UPDATE jobs
    SET job_title  = NVL( p_job_title, job_title ),
        min_salary = NVL( p_min_salary, min_salary ),
        max_salary = NVL( p_max_salary, max_salary )
    WHERE job_id = p_job_id;
  END update_job;

  PROCEDURE add_job
    ( p_job_id      IN jobs.job_id%TYPE,
      p_job_title   IN jobs.job_title%TYPE,
      p_min_salary  IN jobs.min_salary%TYPE,
      p_max_salary  IN jobs.max_salary%TYPE )
  IS
  BEGIN
    INSERT INTO jobs ( job_id, job_title, min_salary, max_salary )
    VALUES ( p_job_id, p_job_title, p_min_salary, p_max_salary );
  END add_job;

  PROCEDURE update_department
    ( p_department_id     IN departments.department_id%TYPE,
      p_department_name   IN departments.department_name%TYPE := NULL,
      p_manager_id        IN departments.manager_id%TYPE := NULL,
      p_update_manager_id IN BOOLEAN := FALSE )
  IS
  BEGIN
    IF ( p_update_manager_id ) THEN
      UPDATE departments
      SET department_name = NVL( p_department_name, department_name ),
          manager_id = p_manager_id
      WHERE department_id = p_department_id;
    ELSE
      UPDATE departments
      SET department_name = NVL( p_department_name, department_name )
      WHERE department_id = p_department_id;
    END IF;
  END update_department;

  FUNCTION add_department
    ( p_department_name   IN departments.department_name%TYPE,
      p_manager_id        IN departments.manager_id%TYPE )
    RETURN departments.department_id%TYPE
  IS
    l_department_id departments.department_id%TYPE;
  BEGIN
    INSERT INTO departments ( department_id, department_name, manager_id )
      VALUES ( departments_sequence.NEXTVAL, p_department_name, p_manager_id )
      RETURNING department_id INTO l_department_id;
    RETURN l_department_id;
  END add_department;

END admin_pkg;
/

関連項目:

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

このチュートリアルでは、SQL*Plusを使用して、admin_pkgパッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーjobs_auferの動作内容も示します。

注意:

Oracle Databaseには、SQL*Plusでユーザーapp_adminとして接続する必要があります。

admin_pkgサブプログラムの動作内容を表示する手順:

  1. IDがAD_VPである職務の情報を表示します。
    SELECT * FROM jobs WHERE job_id = 'AD_VP';

    結果:

    JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_VP      Administration Vice President            15000      30000
  2. この職務の最高給与を高くし、再度情報を表示します。
    EXEC admin_pkg.update_job( 'AD_VP', p_max_salary => 31000 );
SELECT * FROM jobs WHERE job_id = 'AD_VP';

    結果:

    JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_VP      Administration Vice President            15000      31000
  3. IDがIT_PROGである職務の情報を表示します。
    SELECT * FROM jobs WHERE job_id = 'IT_PROG';

    結果:

    JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
IT_PROG    Programmer                                4000      10000
  4. この職務の最低給与を高くすることを試みます。
    EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );

    結果(SQL*Plusから)

    SQL> EXEC admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 );
BEGIN admin_pkg.update_job( 'IT_PROG', p_max_salary => 4001 ); END;
 
*
ERROR at line 1:
ORA-20001: Salary update would violate 5 existing employee records
ORA-06512: at "APP_DATA.JOBS_AUFER", line 12
ORA-04088: error during execution of trigger 'APP_DATA.JOBS_AUFER'
ORA-06512: at "APP_ADMIN.ADMIN_PKG", line 10
ORA-06512: at line 1
  5. 新規職務を追加し、その情報を表示します。
    EXEC admin_pkg.add_job( 'AD_CLERK', 'Administrative Clerk', 3000, 7000 );
SELECT * FROM jobs WHERE job_id = 'AD_CLERK';

    結果:

    JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_CLERK   Administrative Clerk                      3000       7000
  6. 部門100に関する情報を表示します。
    SELECT * FROM departments WHERE department_id = 100;

    結果:

    DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID
------------- ------------------------------ ----------
          100 Finance                               108
  7. 部門100の名前およびマネージャを変更し、その情報を表示します。
    EXEC admin_pkg.update_department( 100, 'Financial Services' );
EXEC admin_pkg.update_department( 100, p_manager_id => 111,
  p_update_manager_id => true );
SELECT * FROM departments WHERE department_id = 100;

    結果:

    DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID
------------- ------------------------------ ----------
          100 Financial Services                    111

関連項目:

「パッケージの作成および管理」

9.6.4 app_admin_userへの実行権限の付与

注意:

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

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

GRANT EXECUTE ON admin_pkg TO app_admin_user;

関連項目:

9.6.5 チュートリアル: app_admin_userとしてのadd_departmentの起動

このチュートリアルでは、SQL*Plusを使用して、ファンクションapp_admin.admin_pkg.add_departmentをユーザーapp_admin_user(アプリケーション管理者)として起動し、新規部門の情報を表示する方法を示します。

admin_pkg.add_departmentをapp_admin_userとして起動する手順:

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

    手順は、「SQL*PlusからOracle Databaseへの接続」を参照してください。

  2. 次のシノニムを作成します。

    CREATE SYNONYM admin_pkg FOR app_admin.admin_pkg;

  3. ファンクションの戻り値のバインド変数を宣言します。

    VARIABLE n NUMBER

  4. マネージャなしで新規部門を追加します。

    EXEC :n := admin_pkg.add_department( 'New department', NULL );

  5. 新規部門のマネージャIDを表示します。

    PRINT :n

    結果:

             N
----------
       275

新規部門の情報を表示する手順:

  1. Oracle Databaseにユーザーapp_admin.として接続します
  2. 新規部門に関する情報を表示します。
    SELECT * FROM departments WHERE department_name LIKE 'New department%';

    結果:

    DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID
------------- ------------------------------ ----------
          275 New department