| Oracle® Database 2日で開発者ガイド 12c リリース1 (12.1) B71350-02 |
|
 前 |
 次 |
この章では、簡易的な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 READ ON HR.DEPARTMENTS TO app_data; GRANT READ ON HR.EMPLOYEES TO app_data; GRANT READ ON HR.JOB_HISTORY TO app_data; GRANT READ 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 READ, INSERT, UPDATE, DELETE ON employees TO app_code; GRANT READ ON departments TO app_code; GRANT READ ON jobs TO app_code; GRANT READ, INSERT on job_history TO app_code; GRANT READ ON employees_sequence TO app_code;
admin_pkgを作成するために必要な権限のみをapp_adminに付与します。
GRANT READ, INSERT, UPDATE, DELETE ON jobs TO app_admin; GRANT READ, INSERT, UPDATE, DELETE ON departments TO app_admin; GRANT READ ON employees_sequence TO app_admin; GRANT READ 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
