この章では、簡易的なOracle Databaseアプリケーションを開発する方法を説明します。
この章の内容は、次のとおりです。
この項の内容は次のとおりです。
アプリケーションは、会社内の2種類のユーザーを対象としています。
一般ユーザー(従業員のマネージャ)
アプリケーション管理者
一般ユーザーは、次の操作を行えます。
指定した部門の従業員の取得
指定した従業員の職務履歴の取得
指定した従業員の一般情報の表示(名前、部門、職務、マネージャ、給与など)
指定した従業員の給与の変更
指定した従業員の職務の変更
アプリケーション管理者は、次の操作を行えます。
既存の職務のID、タイトルまたは給与範囲の変更
新規職務の追加
既存部門のID、名前またはマネージャの変更
新規部門の追加
この項の内容は次のとおりです。
アプリケーションは、次のスキーマ・オブジェクトで構成されています。
次のデータを格納している4つの表
ジョブ
部門
従業員
従業員の職務履歴
表をカバーし、エディションベース再定義(EBR)を使用して、使用中の完成アプリケーションをアップグレードできるようにする、4つのエディショニング・ビュー
ビジネス・ルールを実施する2つのトリガー
新規部門および新規従業員用の一意の主キーを生成する2つの順序
2つのパッケージ
employees_pkg
(一般ユーザー用のアプリケーション・プログラム・インタフェース(API))
admin_pkg
(アプリケーション管理者用のAPI)
一般ユーザーおよびアプリケーション管理者は、APIを介してのみアプリケーションにアクセスします。このため、パッケージ・サブプログラムを起動することによってのみデータを変更できます。
参照:
|
セキュリティのために、アプリケーションでは、次の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;
項目 | 名前 |
---|---|
表 | table # |
table # のエディショニング・ビュー |
table |
エディショニング・ビューtable のトリガー |
table _{a|b} event [_fer]
|
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 |
脚注1table
、table1
およびtable2
は、employees
の場合はemp
に、departments
の場合はdept
に、job_history
の場合はjob_hist
に短縮されます。
脚注2col1
およびcol2
は、column1
およびcolumn2
の列名の略語です。制約名は、30文字以下にする必要があります。
この項の手順を使用して、次の名前のアプリケーション・スキーマを作成します。
app_data
app_code
app_admin
app_user
app_admin_user
注意: 次の手順では、CREATE USER およびDROP USER システム権限を持つユーザーの名前とパスワードが必要です。 |
スキーマ(またはユーザー)schema_nameを作成する手順:
SQL*Plusを使用して、CREATE
USER
およびDROP
USER
システム権限を持つユーザーとしてOracle Databaseに接続します。
SQL>
プロンプトが表示されます。
スキーマが存在する場合、スキーマとそのオブジェクトを次の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
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.
(オプション)「SQL DeveloperからOracle Databaseへの接続」の説明に従って、SQL Developerで、スキーマの接続を作成します。
参照:
|
スキーマに権限を付与するには、SQL文GRANT
を使用します。SQL*PlusまたはSQL DeveloperのワークシートのいずれかでGRANT
文を入力できます。セキュリティのために、各スキーマには必要な権限のみを付与してください。
この項の内容は次のとおりです。
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;
app_code
スキーマには、次を行う権限のみを付与します。
Oracle Databaseに接続します。
GRANT CREATE SESSION TO app_code;
パッケージemployees_pkg
の作成
GRANT CREATE PROCEDURE TO app_code;
シノニムの作成(利便性のため)
GRANT CREATE SYNONYMS TO app_code;
app_admin
スキーマには、次を行う権限のみを付与します。
Oracle Databaseに接続します。
GRANT CREATE SESSION TO app_admin;
パッケージadmin_pkg
の作成
GRANT CREATE PROCEDURE TO app_admin;
シノニムの作成(利便性のため)
GRANT CREATE SYNONYMS TO app_admin;
app_user
およびapp_admin_user
スキーマには、次を行う権限のみを付与します。
Oracle Databaseに接続します。
GRANT CREATE SESSION TO app_user; GRANT CREATE SESSION TO app_admin_user;
シノニムの作成(利便性のため)
GRANT CREATE SYNONYMS TO app_user; GRANT CREATE SYNONYMS TO app_admin_user;
この項では、アプリケーションの表、エディショニング・ビュー、トリガーおよび順序の作成方法、表へのデータのロード方法、およびこれらのスキーマ・オブヘクトの権限を必要とするユーザーへの付与方法について説明します。
スキーマ・オブジェクトの作成およびデータのロード手順:
Oracle Databaseにユーザーapp_data
として接続します。
手順は、「SQL*PlusからOracle Databaseへの接続」または「SQL DeveloperからOracle Databaseへの接続」を参照してください。
データをロードした後に追加する必要がある外部キー制約を除き、すべての必要な制約を使用して表を作成します。
エディショニング・ビューを作成します。
トリガーを作成します。
順序を作成します。
データを表にロードします。
外部キー制約を追加します。
この項の内容は次のとおりです。
注意: Oracle Databaseにはユーザーapp_data として接続する必要があります。 |
この項では、データをロードした後に追加する必要がある制約を除き、すべての必要な制約を使用してアプリケーションの表を作成する方法を示します。
次の手順では、SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL DeveloperツールのCreate Tableを使用して表を作成できます。
表を作成する手順:
社内の職務に関する情報を格納する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 ) /
社内の部門に関する情報を格納する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) ) /
社内の従業員に関する情報を格納する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#
に後で追加する必要があります(「外部キー制約の追加」を参照)。
社内の従業員の職務履歴を格納する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
の値でもある必要があります。
注意: Oracle Databaseにはユーザーapp_data として接続する必要があります。 |
エディショニング・ビューを作成するには、次の文を使用します(順序は任意)。SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL DeveloperツールのCreate Viewを使用して表を作成できます。
CREATE EDITIONING VIEW jobs AS SELECT * FROM jobs# / CREATE EDITIONING VIEW departments AS SELECT * FROM departments# / CREATE EDITIONING VIEW employees AS SELECT * FROM employees# / CREATE EDITIONING VIEW job_history AS SELECT * FROM job_history# /
参照:
|
注意: Oracle Databaseにはユーザーapp_data として接続する必要があります。 |
アプリケーション内のトリガーによって、ビジネス・ルールが実施されます。
職務jの従業員は、職務jの最低給与および最高給与の範囲内の給与を得ている必要があります。
職務jの従業員の給与がsの場合、jの最低給与をsを超える値、またはjの最高給与をs未満の値に変更できません。(これを行うと、既存のデータが無効になります。)
この項の内容は次のとおりです。
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
IN
SHARE
MODE
よりも同時実行性が制限されます。
LOCK
TABLE
jobs
IN
SHARE
MODE
は、jobs
をユーザーが変更できないようにしますが、共有モードのjobs
自体はロックされません。通常、jobs
への変更は、employees
への変更よりもまれにしか行われません。このため、共有モードのjobs
をロックすることにより、排他モードのjobs
の単一行をロックすることよりも同時接続性が高くなります。
参照:
|
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
表の変更はまれであり、通常、表をロックすることがユーザーにとって不便ではない、アプリケーションのメンテナンス時に発生します。
参照:
|
注意: 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
注意: Oracle Databaseにはユーザーapp_data として接続する必要があります。 |
サンプル・スキーマHR
の表のデータをアプリケーションの表にロードします。
注意: 次の手順は、エディショニング・ビューを介してアプリケーションの表を参照します。 |
次の手順では、SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。
表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.
表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.
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.
表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.
変更をコミットします。
COMMIT;
参照:
|
注意: 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
注意: 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言語リファレンス』 を参照してください。 |
この項では、employees_pkg
パッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージ実行権限の付与方法、およびサブプログラムの起動方法を説明します。
employees_pkgパッケージの作成手順:
Oracle Databaseにユーザーapp_code
として接続します。
手順は、「SQL*PlusからOracle Databaseへの接続」または「SQL DeveloperからOracle Databaseへの接続」を参照してください。
次のシノニムを作成します。
CREATE SYNONYM employees FOR app_data.employees; CREATE SYNONYM departments FOR app_data.departments; CREATE SYNONYM jobs FOR app_data.jobs; CREATE SYNONYM job_history FOR app_data.job_history;
SQL*PlusまたはSQL DeveloperのワークシートのいずれかでCREATE
SYNONYM
文を入力できます。または、SQL DeveloperツールのCreate Synonymを使用して表を作成できます。
パッケージ仕様を作成します。
パッケージ本体を作成します。
この項の内容は次のとおりです。
注意: 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; /
注意: 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; /
参照:
|
このチュートリアルでは、SQL*Plusを使用して、employees_pkg
パッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーemployees_aiufer
およびCHECK
制約job_history_date_check
の動作内容も表示します。
注意: Oracle Databaseには、SQL*Plusでユーザーapp_code として接続する必要があります。 |
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
注意: 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;
このチュートリアルでは、SQL*Plusを使用して、サブプログラムapp_code.employees_pkg.get_job_history
をユーザーapp_user
(通常はマネージャ)またはapp_admin_user
(アプリケーション管理者)として起動する方法を示します。
app_userまたはapp_admin_userとして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
この項では、admin_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージの実行権限の付与方法、およびサブプログラムの起動方法を説明します。
admin_pkgパッケージの作成手順:
Oracle Databaseにユーザーapp_admin
として接続します。
手順は、「SQL*PlusからOracle Databaseへの接続」または「SQL DeveloperからOracle Databaseへの接続」を参照してください。
次のシノニムを作成します。
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を使用して表を作成できます。
パッケージ仕様を作成します。
パッケージ本体を作成します。
この項の内容は次のとおりです。
注意: 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; /
注意: 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 ); RETURN l_department_id; END add_department; END admin_pkg; /
参照:
|
このチュートリアルでは、SQL*Plusを使用して、admin_pkg
パッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーjobs_aufer
の動作内容も示します。
注意: Oracle Databaseには、SQL*Plusでユーザーapp_admin として接続する必要があります。 |
admin_pkgサブプログラムの動作内容を表示する手順:
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
この職務の最高給与を高くし、再度情報を表示します。
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
IDがIT_PROG
である職務の情報を表示します。
SELECT * FROM jobs WHERE job_id = 'IT_PROG';
結果:
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- ----------------------------------- ---------- ---------- IT_PROG Programmer 4000 10000
この職務の最低給与を高くすることを試みます。
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
新規職務を追加し、その情報を表示します。
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
部門100に関する情報を表示します。
SELECT * FROM departments WHERE department_id = 100;
結果:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 100 Finance 108
部門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
注意: Oracle Databaseにはユーザーapp_admin として接続する必要があります。 |
パッケージadmin_pkg
の実行権限をapp_admin_user
(アプリケーション管理者)に付与するには、次のGRANT
文を使用します。SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。
GRANT EXECUTE ON admin_pkg TO app_admin_user;
このチュートリアルでは、SQL*Plusを使用して、ファンクションapp_admin.admin_pkg.add_department
をユーザーapp_admin_user
(アプリケーション管理者)として起動し、新規部門の情報を表示する方法を示します。
admin_pkg.add_departmentをapp_admin_userとして起動する手順:
ユーザーapp_admin_user
としてOracle DatabaseにSQL*Plusから接続します。
手順は、「SQL*PlusからOracle Databaseへの接続」を参照してください。
次のシノニムを作成します。
CREATE SYNONYM admin_pkg FOR app_admin.admin_pkg;
ファンクションの戻り値のバインド変数を宣言します。
VARIABLE n NUMBER
マネージャなしで新規部門を追加します。
EXEC :n := admin_pkg.add_department( 'New department', NULL );
新規部門のマネージャIDを表示します。
PRINT :n
結果:
N ---------- 275
新規部門の情報を表示する手順:
Oracle Databaseにユーザーapp_admin
として接続します。
新規部門に関する情報を表示します。
SELECT * FROM departments WHERE department_name LIKE 'New department%';
結果:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 275 New department