9 簡易的なOracle Databaseアプリケーションの開発
この簡易なアプリケーションを開発する手順に従って、Oracle Databaseアプリケーションの一般的な開発手順を学習します。
- アプリケーションについて
アプリケーションには、次の目的、構造、およびネーミング規則があります。 - アプリケーションのスキーマの作成
この項の手順を使用して、アプリケーションのスキーマを作成します。 - スキーマへの権限の付与
スキーマに権限を付与するには、SQL文GRANTを使用します。 - スキーマ・オブジェクトの作成およびデータのロード
この項では、アプリケーションの表、エディショニング・ビュー、トリガーおよび順序の作成方法、表へのデータのロード方法、およびこれらのスキーマ・オブヘクトの権限を必要とするユーザーへの付与方法について説明します。 - employees_pkgパッケージの作成
この項では、employees_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージ実行権限の付与方法、およびサブプログラムの起動方法を説明します。 - admin_pkgパッケージの作成
この項では、admin_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージ実行権限の付与方法、およびサブプログラムの起動方法を説明します。
9.1 アプリケーションについて
アプリケーションには、次の目的、構造、およびネーミング規則があります。
- アプリケーションの目的
アプリケーションは、会社内の2種類のユーザーを対象としています。 - アプリケーションの構造
アプリケーションでは、次のスキーマ・オブジェクトとスキーマが使用されます。 - アプリケーションのネーミング規則
アプリケーションでは、次のネーミング規則が使用されます。
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を介してのみアプリケーションにアクセスします。このため、パッケージ・サブプログラムを起動することによってのみデータを変更できます。
-
関連項目:
-
スキーマ・オブジェクトの詳細は、「Oracle Databaseについて」を参照してください。
-
EBRの詳細については、『Oracle Database開発ガイド』を参照してください。
親トピック: アプリケーションの構造
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;
関連項目:
-
スキーマの詳細は、「Oracle Databaseについて」を参照してください。
-
サンプル・スキーマ
HR
の詳細は、「サンプル・スキーマHRについて」を参照してください。
親トピック: アプリケーションの構造
9.1.3 アプリケーションのネーミング規則
アプリケーションでは、次のネーミング規則が使用されます。
アイテム | 名前 |
---|---|
表 |
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 |
table1#.column1からtable2#.column2へのREF制約 |
|
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を作成する手順:
関連項目:
-
DROP
USER
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
CREATE
USER
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
9.3 スキーマへの権限の付与
スキーマに権限を付与するには、SQL文GRANTを使用します。
SQL*PlusまたはSQL DeveloperのワークシートのいずれかでGRANT文を入力できます。セキュリティのために、各スキーマには必要な権限のみを付与してください。
関連項目:
-
GRANT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
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 スキーマ・オブジェクトの作成およびデータのロード
この項では、アプリケーションの表、エディショニング・ビュー、トリガーおよび順序の作成方法、表へのデータのロード方法、およびこれらのスキーマ・オブヘクトの権限を必要とするユーザーへの付与方法について説明します。
スキーマ・オブジェクトの作成およびデータのロード手順:
9.4.1 表の作成
この項では、データをロードした後に追加する必要がある制約を除き、すべての必要な制約を使用してアプリケーションの表を作成する方法を示します。
注意:
Oracle Databaseにはユーザーapp_data
として接続する必要があります。
次の手順では、SQL*PlusまたはSQL Developerのワークシートのいずれかで文を入力できます。または、SQL DeveloperツールのCreate Tableを使用して表を作成できます。
表を作成する手順:
関連項目:
親トピック: スキーマ・オブジェクトの作成およびデータのロード
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を使用して完成アプリケーションをその使用中にアップグレードできません。
関連項目:
-
エディショニング・ビューの一般的な情報は、『Oracle Database開発ガイド』を参照してください。
-
エディショニング・ビューを使用するためのアプリケーションの準備の詳細は、『Oracle Database開発ガイド』を参照してください。
親トピック: スキーマ・オブジェクトの作成およびデータのロード
9.4.3 トリガーの作成
注意:
Oracle Databaseにはユーザーapp_dataとして接続する必要があります。
アプリケーション内のトリガーによって、ビジネス・ルールが実施されます。
-
職務jの従業員は、職務jの最低給与および最高給与の範囲内の給与を得ている必要があります。
-
職務jの従業員の給与がsの場合、jの最低給与をsを超える値、またはjの最高給与をs未満の値に変更できません。(これを行うと、既存のデータが無効になります。)
- 1つ目のビジネス・ルールを実施するトリガーの作成
1つ目のビジネス・ルールは、職務jの従業員は、職務jの最低給与および最高給与の範囲内の給与を得ている必要があるというものです。 - 2つ目のビジネス・ルールを実施するトリガーの作成
2つ目のビジネス・ルールは、職務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の単一行をロックすることよりも同時接続性が高くなります。
関連項目:
-
IN SHARE MODEでの表のロックの詳細は、『Oracle Database開発ガイド』を参照してください。
-
SELECT FOR UPDATEの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
employees_aiuferトリガーの動作の詳細は、「チュートリアル: employees_pkgサブプログラムの動作内容の表示」を参照してください。
親トピック: トリガーの作成
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表の変更はまれであり、通常、表をロックすることがユーザーにとって不便ではない、アプリケーションのメンテナンス時に発生します。
関連項目:
-
SHARE
MODE
での表のロックの詳細は、『Oracle Database開発ガイド』を参照してください。 -
DBMS_LOCK
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
親トピック: トリガーの作成
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のワークシートのいずれかで文を入力できます。
表にデータをロードする手順:
関連項目:
-
NVL
ファンクションの詳細は、「問合せにおけるNULL関連ファンクションの使用」を参照してください。 -
SQL
ファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: スキーマ・オブジェクトの作成およびデータのロード
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パッケージの作成手順:
- employees_pkgのパッケージ仕様の作成
- employees_pkgのパッケージ本体の作成
- チュートリアル: employees_pkgサブプログラムの動作内容の表示
このチュートリアルでは、SQL*Plusを使用して、employees_pkgパッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーemployees_aiuferおよびCHECK制約job_history_date_checkの動作内容も表示します。 - app_userおよびapp_admin_userへの実行権限の付与
- チュートリアル: app_userまたはapp_admin_userとしてのget_job_historyの起動
このチュートリアルでは、SQL*Plusを使用して、サブプログラムapp_code.employees_pkg.get_job_historyをユーザーapp_user (通常はマネージャ)またはapp_admin_user(アプリケーション管理者)として起動する方法を示します。
関連項目:
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; /
関連項目:
-
CREATE PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 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; /
関連項目:
-
CREATE PACKAGE BODY文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 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サブプログラムの動作内容を表示する手順:
関連項目:
-
SQL*Plusコマンドの詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。
親トピック: employees_pkgパッケージの作成
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;
関連項目:
-
GRANT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: employees_pkgパッケージの作成
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を起動する手順:
親トピック: employees_pkgパッケージの作成
9.6 admin_pkgパッケージの作成
この項では、admin_pkgパッケージの作成方法、サブプログラムの動作内容、必要とするユーザーへのパッケージの実行権限の付与方法、およびサブプログラムの起動方法を説明します。
admin_pkgパッケージの作成手順:
- admin_pkgのパッケージ仕様の作成
- admin_pkgのパッケージ本体の作成
- チュートリアル: admin_pkgサブプログラムの動作内容の表示
このチュートリアルでは、SQL*Plusを使用して、admin_pkgパッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーjobs_auferの動作内容も示します。 - app_user_adminへの実行権限の付与
- チュートリアル: app_admin_userとしてのadd_departmentの起動
このチュートリアルでは、SQL*Plusを使用して、ファンクションapp_admin.admin_pkg.add_departmentをユーザーapp_admin_user(アプリケーション管理者)として起動し、新規部門の情報を表示する方法を示します。
関連項目:
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; /
関連項目:
-
CREATE PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 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; /
関連項目:
-
CREATE PACKAGE BODY文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: admin_pkgパッケージの作成
9.6.3 チュートリアル: admin_pkgサブプログラムの動作内容の表示
このチュートリアルでは、SQL*Plusを使用して、admin_pkgパッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーjobs_auferの動作内容も示します。
注意:
Oracle Databaseには、SQL*Plusでユーザーapp_adminとして接続する必要があります。
admin_pkgサブプログラムの動作内容を表示する手順:
関連項目:
親トピック: admin_pkgパッケージの作成
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;
関連項目:
-
GRANT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: admin_pkgパッケージの作成
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として起動する手順:
-
ユーザー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
新規部門の情報を表示する手順:
親トピック: admin_pkgパッケージの作成