スキーマ・オブジェクトの作成およびデータのロード
この項では、アプリケーションの表、エディショニング・ビュー、トリガーおよび順序の作成方法、表へのデータのロード方法、およびこれらのスキーマ・オブヘクトの権限を必要とするユーザーへの付与方法について説明します。
スキーマ・オブジェクトを作成し、データをロードするステップは次のとおりです。
-
ユーザーapp_dataとしてOracle Databaseに接続します。
手順については、「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 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を使用して完成アプリケーションをその使用中にアップグレードできません。
関連情報:
-
エディショニング・ビューの一般的な情報は、Oracle Database開発ガイドを参照してください。
-
エディショニング・ビューを使用するためのアプリケーションの準備の詳細は、Oracle Database開発ガイドを参照
トリガーの作成
ノート: Oracle Databaseにユーザーapp_dataとして接続する必要があります。
アプリケーション内のトリガーによって、ビジネス・ルールが実施されます。
-
職務jの従業員は、職務jの最低給与および最高給与の範囲内の 給与を得ている必要があるというもの。
-
職務jの従業員の給与がsの場合、jの最低給与をsを超える値、またはjの最高給与をs未満の値に変更できません。(これを行うと、既存のデータが無効になります。)
関連項目:トリガーの詳細は、「トリガーの使用」を参照してください。
1つ目のビジネス・ルールを実施するトリガーの作成
1つ目のビジネス・ルールは、職務jの従業員は、職務jの最低給与および最高給与の範囲内の 給与を得ている必要があるということです。
このルールは、新規行がemployees表に挿入されるとき、またはemployees表のsalaryまたはjob_id列が更新されたときに違反となる可能性があります。
ルールを実施するには、エディショニング・ビュー従業員に次のトリガーを作成します。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は、トリガーが問合せしている表ジョブを他のユーザーが変更できないようにします。トリガーが従業員を変更している間、非ブロック読取りによって他のユーザーがジョブに行う変更を監視できなくなるため(および、ユーザーはトリガーにより従業員に行われた変更を参照できなくなるため)問合せ中にジョブの変更を防止する必要があります。
問合せ中にジョブが変更されないようにするもう1つの方法は、SELECT文にFOR UPDATE句を含める方法です。ただし、SELECT FOR UPDATEでは、LOCK TABLE jobs IN SHARE MODEよりも同時実行性が制限されています。
LOCK TABLE jobs IN SHARE MODEは、2をユーザーが変更できないようにしますが、共有モードのジョブはロックされません。ジョブへの変更は、従業員への変更よりもまれにしか行われない可能性があります。このため、共有モードのジョブをロックすることにより、排他モードのジョブの単一行をロックすることよりも同時接続が高くなります。
関連情報:
-
IN SHARE MODEでの表のロックの詳細は、Oracle Database開発ガイドを参照してください。
-
SELECT FOR UPDATEの詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。
-
employees_aiuferトリガーの動作を確認するには、「チュートリアル: employees_pkgサブプログラムの動作内容の表示」を参照してください
2つ目のビジネス・ルールを実施するトリガーの作成
2つ目のビジネス・ルールは、職務jの従業員の給与がsの場合、jの最低給与をsを超える値、またはjの最高給与をs未満の値に変更できないというものです。(これを行うと、既存のデータが無効になります。)
このルールは、jobs表のmin_salary列またはmax_salary列が更新されたときに違反となる可能性があります。
ルールを実施するには、エディショニング・ビュー・ジョブに次のトリガーを作成します。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は、トリガーが問合せしている表従業員を他のユーザーが変更できないようにします。トリガーがジョブの変更中に、非ブロック読取りにより他のユーザーによって従業員に行われた変更を参照できなくなるため(および、ユーザーはトリガーによりジョブに行われた変更を参照できなくなるため)問合せ中に従業員を変更できなくなるため、問合せ中に従業員の変更を防止する必要があります。
このトリガーの場合、SELECT FOR UPDATEは、LOCK TABLE IN SHARE MODEの代替手段になりません。ユーザーがこの職務の給与範囲の変更を試行したときに、このトリガーは、他のユーザーが給与を新規範囲外に変更しないようにする必要があります。このため、トリガーは、このjob_idを持つemployees表のすべての行をロッキングし、このjob_idを持つように更新される可能性のあるすべての行をロッキングする必要があります。
LOCK TABLE employees IN SHARE MODEのかわりに、DBMS_LOCKパッケージを使用してjob_idの名前で名前付きロックを作成し、employees表とjobs表の両方でトリガーを使用してこの名前付きロックを使用し同時更新を回避する方法があります。ただし、DBMS_LOCKおよび複数のトリガーを使用すると、実行時のパフォーマンスに悪の影響を及ぼします。
Another alternative to LOCK TABLE employees IN SHARE MODE is to create a trigger on the employees table which, for each changed row of employees, locks the corresponding job row in jobs. ただし、この手法では、頻繁に起こるemployees表の更新時に大量の作業が発生します。
LOCK TABLE employees IN SHARE MODEは、前述の代替手段よりも単純で、ジョブ表の変更はまれであり、通常は、表をロックすることがユーザーには不便ではない、アプリケーションのメンテナンス時に発生します。
関連情報:
-
SHAREMODEでの表のロックの詳細は、『Oracle Database開発ガイド』を参照してください -
DBMS_LOCKパッケージの詳細は、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンスを参照してください。
順序の作成
ノート: 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のデータをジョブにロードします:
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のデータを部門にロードします:
INSERT INTO departments (department_id, department_name, manager_id) SELECT department_id, department_name, manager_id FROM HR.DEPARTMENTS /結果:
27 rows created. -
HR.JOB_HISTORYからemployees.job_start_dateを取得する検索CASE式およびSQL関数を使用して、HR.phone_numberおよびSQLファンクションからemployees.country_codeおよびemployees.phone_numberを取得し、スカラー副問合せを使用して、HR.EMPLOYEESおよびHR.JOB_HISTORYのデータをロードします。
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つ目のビジネス・ルールを実施するトリガーの作成」で作成したトリガーを起動します。 -
表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;
関連情報:
-
NVLファンクションの詳細は、「問合せにおけるNULL関連ファンクションの使用」を参照してください。 -
SQLファンクションの詳細は、Oracle Database SQL言語リファレンスを参照してください。
外部キー制約の追加
ノート: Oracle Databaseにユーザーapp_dataとして接続する必要があります。
表部門および従業員にデータが含まれるようになったら、次の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#;department#および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文を入力できます。
employee_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;app_adminにadmin_pkgを作成するのに必要な権限のみを付与します。
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言語リファレンス』を参照