admin_pkgパッケージの作成
この項では、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を使用して表を作成できます。
-
パッケージ仕様を作成します。
-
パッケージ本体を作成します。
関連情報:
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のパッケージ本体の作成
ノート: 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サブプログラムの動作内容の表示
このチュートリアルでは、SQL*Plusを使用して、admin_pkgパッケージのサブプログラムの動作内容を表示します。また、チュートリアルでは、トリガーjobs_auferの動作も示します。
ノート: Oracle Databaseにユーザーapp_adminとしてSQL*Plusから接続する必要があります。
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
関連項目: 「パッケージの作成および管理」
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リファレンスを参照
チュートリアル: 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
新規部門の情報を表示するステップ:
-
Oracle Databaseにユーザーapp_adminとして接続します
-
新規部門に関する情報を表示します。
SELECT * FROM departments WHERE department_name LIKE 'New department%';結果:
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID ------------- ------------------------------ ---------- 275 New department