この章の内容は、次のとおりです。
アプリケーションを開発するデータベースは、開発環境と呼ばれます。アプリケーションを開発した後、他のユーザーが実行できるデプロイメント環境と呼ばれる他のデータベースにインストールできます。
第一のデプロイメント環境はテスト環境です。テスト環境では、アプリケーションの機能を詳細にテストして、正確に構造化されているかどうかを判断し、本番環境にデプロイする前に問題を修正できます。
アプリケーションは本番環境へのデプロイ前後に、教育環境にもデプロイすることができます。教育環境は、他の環境に影響を及ぼさずにアプリケーションの実行方法を練習する場をユーザーに提供します。
必要なデプロイメント環境が編成内に存在しない場合は、作成できます。
スクリプトは、名前が.sql
で終わるファイル(たとえば、create_app.sql
)内の一連のSQL文です。SQL*PlusまたはSQL Developerなどのクライアント・プログラムのスクリプトを実行すると、SQL文がスクリプトの表示順に実行されます。SQL文がアプリケーションを作成するスクリプトは、インストール・スクリプトと呼ばれます。インストール・スクリプトには、アプリケーションの作成に必要なすべてのSQL文または他のスクリプトを実行するマスター・スクリプトを設定できます。
アプリケーションをデプロイするには、デプロイメント環境で1つ以上のインストール・スクリプトを実行します。新しいアプリケーションでは、インストール・スクリプトを作成する必要があります。古いアプリケーションでは、インストール・スクリプトが存在する可能性がありますが、存在しない場合は作成できます。
この項の内容は次のとおりです。
インストール・スクリプトには、スキーマ・オブジェクトを作成するDDL文およびオプションでDDL文が作成する表にデータをロードするINSERT
文が含まれます。
インストール・スクリプトを正確に作成し、正しい順序で複数のインストール・スクリプトを実行するには、アプリケーションのスキーマ・オブジェクト間の依存性を理解する必要があります。
オブジェクトAの定義でオブジェクトBを参照している場合、AはBに依存しています。そのため、Aを作成する前に、Bを作成する必要があります。これを行わないと、オブジェクト・タイプによって、Bを作成する文が失敗したり、Bが無効な状態で作成されます。
複雑なアプリケーションの場合、オブジェクトを作成する順序ははっきりしていません。通常は、データベース設計者に相談するか、設計のダイアグラムを考慮する必要があります。
INSERT
文を含むインストール・スクリプトを実行する場合、INSERT
文は、ソース表(開発環境)からデプロイメント環境の対応する新規表にデータを挿入します。アプリケーションのソース表ごとに、新しい表へデータを挿入するときに違反となる可能性のある制約がないか確認する必要があります。そのような制約がある場合は、まずその制約を無効にし、データを挿入してから、制約を再度有効にする必要があります。データ項目に制約違反がある場合、そのデータ項目を修正するまで、制約を再度有効にすることはできません。
「データのロード」に示すように、正しい順序で参照データを単純に挿入する場合は、制約違反は起こりません。そのため、制約を無効にしておく必要はありません。
外部のソース(ファイル、スプレッドシート、以前のアプリケーションなど)や、依存データを多く含む大量の表からデータを挿入する場合は、データを挿入する前に制約を無効にしてください。
制約を無効にし、再度有効にするには、次の方法があります。
SQL Developerを使用して、制約の無効化と再度有効化を一度に実行します。
「接続」フレームで、適切な表を選択します。
表名のラベルが付いたペインで、サブタブ「制約」を選択します。
すべての表の制約のリストで、ENABLED
をDISABLED
(またはその逆)に変更します。
インストール・スクリプトを編集して、各制約を無効化し再度有効化するSQL文を追加します。
各制約を無効にし、再度有効にするSQL文を含んだSQLスクリプトを作成します。
Oracle Databaseデータ・ディクショナリで制約を検索して、各制約を無効化および有効化するSQL文を使用してSQLスクリプトを作成します。
たとえば、「表の作成」
からEVALUATIONS
、PERFORMANCE_PARTS
、およびSCORES表で使用される制約を検索および有効化するには、ワークシートに次の文を入力します。
SELECT 'ALTER TABLE '|| TABLE_NAME || ' DISABLE CONSTRAINT '|| CONSTRAINT_NAME ||';' FROM user_constraints WHERE table_name IN ('EVALUATIONS','PERFORMANCE_PARTS','SCORES'); SELECT 'ALTER TABLE '|| TABLE_NAME || ' ENABLE CONSTRAINT '|| CONSTRAINT_NAME ||';' FROM user_constraints WHERE table_name IN ('EVALUATIONS','PERFORMANCE_PARTS','SCORES');
インストール・スクリプトでDDLおよびINSERT
文のみが必要な場合、SQL Developerまたは任意のテキスト・エディタで作成できます。SQL Developerで、カートまたはデータベース・エクスポート・ウィザードを使用できます。複数のデプロイメント環境で実行する予定のインストール・スクリプトの場合はカート、単一のデプロイメント環境でのみ実行する予定のインストール・スクリプトの場合はデータベース・エクスポート・ウィザードを使用することをお薦めします。
インストール・スクリプトでDDLおよびINSERT
文ではないSQL文が必要な場合、テキスト・エディタで作成できます。
この項では、カートおよびデータベース・エクスポート・ウィザードを使用してインストール・スクリプトを作成する方法、順序およびトリガーを作成するインストール・スクリプトを編集するタイミングおよび方法および第9章「簡易的なOracle Databaseアプリケーションの開発」(「サンプル・アプリケーション」)のアプリケーションのインストール・スクリプトを作成する方法について説明します。
この項の内容は次のとおりです。
カートは、1つ以上のデータベース接続から宛先接続にOracle Databaseオブジェクトをデプロイするための便利なツールです。ナビゲータ・フレームから「カート」ウィンドウにオブジェクトをドラッグ・アンド・ドロップし、必要なオプションを指定し、「カートのエクスポート」アイコンをクリックして「オブジェクトのエクスポート」ダイアログ・ボックスを表示します。このダイアログ・ボックスに情報を入力した後、SQL Developerは、スクリプト(マスター・スクリプトを含む)を含む.zip
ファイルを作成して、必要な宛先の接続のスキーマのオブジェクトを作成します。
カートを使用してインストール・スクリプトを作成するには、次の手順を実行します。
SQL Developerウィンドウで、「表示」メニューをクリックします。
「表示」メニューから、「カート」を選択します。
カート・ウィンドウが開きます。「カートのエクスポート」アイコンは、非アクティブ(グレー)です。
ヒント: カート・ウィンドウで、カート・ユーザーのプリファレンスの情報を取得するには、[F1]キーを押します。 |
「接続」フレームで、インストール・スクリプトで作成するスキーマ・オブジェクトを選択し、「カート」ウィンドウにドラッグします。
「カート」ウィンドウで、「カートのエクスポート」アイコンがアクティブ(グレーではない)になります。
インストール・スクリプトでデータをエクスポートする場合は、TABLE
型の各選択済オブジェクトで、「データ」オプションを選択します。
「カートのエクスポート」をクリックします。
「オブジェクトのエクスポート」ダイアログ・ボックスで、フィールドに必要な値を入力します。
これらのフィールドの詳細は、『Oracle Database SQL Developerユーザーズ・ガイド』を参照してください。
「適用」をクリックします。
SQL Developerは、スクリプト(マスター・スクリプトを含む)を含む.zip
ファイルを作成して、必要な宛先の接続のスキーマのオブジェクトを作成します。
マスター・スクリプトおよびその実行するスクリプトで、次をチェックします。
参照されているオブジェクトが、依存オブジェクトの前に作成されること。
データを挿入する前に、挿入先の表が作成されること。
インストール・スクリプトで順序を作成する場合、「順序を作成するインストール・スクリプトの編集」を参照してください。
インストール・スクリプトでトリガーを作成する場合、「順序を作成するインストール・スクリプトの編集」を参照してください。
必要な場合は、ワークシートまたは任意のテキスト・エディタでインストール・ファイルを編集します。
参照: カートの詳細は、『Oracle Database SQL Developerユーザーズ・ガイド』を参照してください |
データベース・エクスポート・ウィザードを使用してSQL Developerでインストール・スクリプトを作成するには、インストール・スクリプトの名前、エクスポートするオブジェクトとデータおよび必要なオプションを指定すると、ウィザードがインストール・スクリプトを生成します。
注意: 次の手順では、すべてのフィールドとオプションが表示されるように、SQL Developerウィンドウを拡大する必要のある場合があります。 |
データベース・エクスポート・ウィザードを使用してインストール・スクリプトを作成するには、次の手順を実行します。
そのようにしたことがない場合、Oracle Databaseインストール・ディレクトリとは別にインストール・スクリプト用のディレクトリを作成します(例: C:\my_exports
)。
SQL Developerウィンドウで「ツール」メニューをクリックします。
メニューから、「データベース・エクスポート」を選択します。
エクスポート・ウィザード - ステップ1/5 (ソース/宛先)ウィンドウで、次の手順を実行します。
「接続」フィールドで、開発環境への接続を選択します。
必要な「DDLのエクスポート」オプションを選択します(不要なオプションは選択を解除します)。
注意: 「終了文字」の選択を解除すると、インストール・ファイルは失敗します。 |
インストール・スクリプトでデータをエクスポートしない場合、「データのエクスポート」の選択を解除します。
「別名保存」フィールドで、デフォルトの「単一ファイル」を受け入れて、インストール・スクリプトのフルパス名(たとえば、C:\my_exports\hr_export.sql
)を入力します。
ファイル名の末尾は.sql
である必要があります。
「次」をクリックします。
エクスポート・ウィザード - ステップ2/5 (エクスポートするタイプ)ウィンドウで、次の手順を実行します。
エクスポートしない型のチェック・ボックスを選択解除します。
「すべて設定」を選択または選択解除すると、すべてのチェック・ボックスが選択または選択解除されます。
「次」をクリックします。
エクスポート・ウィザード - ステップ3/5 (オブジェクトの指定)ウィンドウで、次の手順を実行します。
「詳細」をクリックします。
「スキーマ」フィールドで、スキーマをメニューから選択します。
「タイプ」フィールドで、メニューから「すべてのオブジェクト」
または特定のオブジェクト・タイプ(「TABLE」
など)のいずれかを選択します。
「参照」をクリックします。
左側のフレームにオブジェクトのリストが表示されます。「タイプ」フィールドの値が「すべてのオブジェクト」
の場合、リストには選択したスキーマのすべてのオブジェクトが含まれます。「タイプ」フィールドの値が特定のオブジェクト・タイプの場合、リストには選択したスキーマのそのタイプのすべてのオブジェクトが含まれます。
エクスポートするオブジェクトを、左のフレームから右のフレームに移動します。
すべてのオブジェクトを移動するには、「>>」をクリックします。(すべてのオブジェクトを元に戻すには、「<<」をクリックします。)
選択したオブジェクトを移動するには、オブジェクトを選択して「>」をクリックします。(選択したオブジェクトを元に戻すには、オブジェクトを選択して「<」をクリックします。)
「次」をクリックします。
「ソース/宛先」ウィンドウで「データのエクスポート」の選択を解除した場合、「エクスポートのサマリー」ウィンドウが表示されるので、手順3に進みます。
「ソース/宛先」ウィンドウで「データのエクスポート」の選択を解除しなかった場合、エクスポート・ウィザード - ステップ4/5 (データの指定)ウィンドウが表示されます。下部フレームには、「オブジェクトの指定」ウィンドウで指定したオブジェクトがリスト表示されます。
「データの指定」ウィンドウで、次の手順を実行します。
データをエクスポートしないオブジェクトを、下部フレームから上部フレームに移動します。
すべてのオブジェクトを移動するには、上向きの二重矢印アイコンをクリックします。(すべてのオブジェクトを元に戻すには、下向きの二重矢印アイコンをクリックします。)
選択したオブジェクトを移動するには、オブジェクトを選択して上向きの一重矢印アイコンをクリックします。
「次」をクリックします。
エクスポート・ウィザード - ステップ5/5 (エクスポートのサマリー)ウィンドウで、「終了」をクリックします。
エクスポート中であることを示す「エクスポート中」ウィンドウが開きます。エクスポートが完了すると、「エクスポート中」ウィンドウが閉じ、ワークシートに「ソース/宛先」ウィンドウで指定したインストール・スクリプトのコンテンツが表示されます。
インストール・スクリプトで次をチェックします。
参照されているオブジェクトが、依存オブジェクトの前に作成されること。
データを挿入する前に、挿入先の表が作成されること。
必要に応じて、ワークシートまたはテキスト・エディタでファイルを編集します。
参照: データベース・エクスポート・ウィザードの詳細は、『Oracle SQL Developerユーザーズ・ガイド』を参照してください |
順序については、START
WITH
の値が開発環境における順序の現在の値に関連する順序となっているCREATE
SEQUENCE
文が、SQL Developerで生成されます。
アプリケーションが一意のキーを生成する順序を使用しており、ソース表から関連する新規表にデータを挿入していない場合、インストール・スクリプトのSTART
WITH
値を編集できます。
インストール・スクリプトはワークシートまたはテキスト・エディタで編集できます。
ソース表にBEFORE INSERTトリガーがあるアプリケーションで、ソース表から関連する新規表にデータを挿入する場合、インストール・スクリプトの各
INSERT
文による新規表へのデータの挿入の前に、トリガーを起動するかどうか決定する必要があります。
たとえば、(「チュートリアル: 行を挿入する前に行に対して主キーを生成するトリガーの作成」
で作成した)NEW_EVALUATION_TRIGGERは、行がEVALUATIONS
表に挿入される前に起動します。トリガーは、EVALUATIONS_SEQUENCE
を使用して、その行の主キーに対する一意の番号を生成します。
ソースのEVALUATIONS
表は主キーとともに移入されています。インストール・スクリプトを新規EVALUATIONS
表の新規主キーしない場合、インストール・スクリプトのCREATE
TRIGGER
文を次のように編集する必要があります。
CREATE OR REPLACE TRIGGER NEW_EVALUATION_TRIGGER BEFORE INSERT ON EVALUATIONS FOR EACH ROW BEGIN IF :NEW.evaluation_id IS NULL THEN :NEW.evaluation_id := evaluations_sequence.NEXTVAL END IF; END;
また、順序の現在の値が主キー列の最大値より大きくない場合、大きくする必要があります。
インストール・スクリプトはワークシートまたはテキスト・エディタで編集できます。
インストール・スクリプトの編集には2つの方法があります。
ソース・ファイルのトリガー定義を変更し、インストール・スクリプトを再作成します。
トリガーの変更の詳細は、「トリガーの変更」を参照してください。
トリガーを無効にしてからデータ・インストール・スクリプトを実行し、トリガーを再度有効にします。
トリガーの無効化および有効化の詳細は、「トリガーの無効化および有効化」を参照してください。
この項では、第9章「簡易的なOracle Databaseアプリケーションの開発」(「サンプル・アプリケーション」)のアプリケーションの次のインストール・スクリプトを作成する方法について説明します。
「アプリケーションのスキーマの作成」および「スキーマへの権限の付与」
の開発環境の実行内容をデプロイメント環境で実行するschemas.sql
「スキーマ・オブジェクトの作成およびデータのロード」の開発環境の実行内容をデプロイメント環境で実行する
objects.sql
「employees_pkgパッケージの作成」の開発環境の実行内容をデプロイメント環境で実行する
employees.sql
前のスクリプトを実行し、デプロイメント環境にサンプル・アプリケーションをデプロイするマスター・スクリプトのcreate_app.sql
任意の順序でスクリプトを作成できます。schemas.sql
およびcreate_app.sql
を作成するには、テキスト・エディタを使用する必要があります。他のスクリプトを作成するには、テキスト・エディタまたはSQL Developerを使用できます。
この項の内容は次のとおりです。
インストール・スクリプトschemas.sql
は、「アプリケーションのスキーマの作成」および「スキーマへの権限の付与」の開発環境の実行内容をデプロイメント環境で実行します。
schemas.sql
を作成するには、任意のテキスト・エディタで次のテキストを入力し、schemas.sql
としてファイルを保存します。
注意: セキュアなパスワードを選択します。セキュアなパスワードの詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。 |
----------------- -- Create schemas ----------------- DROP USER app_data CASCADE; CREATE USER app_data IDENTIFIED BY password DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ENABLE EDITIONS; DROP USER app_code CASCADE; CREATE USER app_code IDENTIFIED BY password DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ENABLE EDITIONS; DROP USER app_admin CASCADE; CREATE USER app_admin IDENTIFIED BY password DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ENABLE EDITIONS; DROP USER app_user CASCADE; CREATE USER app_user IDENTIFIED BY password ENABLE EDITIONS; DROP USER app_admin_user CASCADE; CREATE USER app_admin_user IDENTIFIED BY password ENABLE EDITIONS; ------------------------------ -- Grant privileges to schemas ------------------------------ GRANT CREATE SESSION TO app_data; GRANT CREATE TABLE, CREATE VIEW, CREATE TRIGGER, CREATE SEQUENCE TO app_data; 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; GRANT CREATE SESSION, CREATE PROCEDURE, CREATE SYNONYM TO app_code; GRANT CREATE SESSION, CREATE PROCEDURE, CREATE SYNONYM TO app_admin; GRANT CREATE SESSION, CREATE SYNONYM TO app_user; GRANT CREATE SESSION, CREATE SYNONYM TO app_admin_user;
インストール・スクリプトobjects.sql
は、「スキーマ・オブジェクトの作成およびデータのロード」の開発環境の実行内容をデプロイメント環境で実行します。
テキスト・エディタまたはSQL Developerを使用して、objects.sql
を作成できます。
任意のテキスト・エディタでobjects.sql
を作成するには、次のテキストを入力し、objects.sql
としてファイルを保存します。パスワード
に、ユーザーapp_data
の作成時にschema.sql
が指定するパスワードを使用します。
注意: デプロイメント環境に一般的なHR スキーマが存在する場合のみ、データをロードするINSERT 文は有効です。存在しない場合、SQL Developerを使用してソース表のデータ(開発環境)とともに新規表(デプロイメント環境)をロードするスクリプトを作成するか、次のスクリプトでINSERT 文を変更します。 |
------------------------ -- Create schema objects ------------------------ CONNECT app_data/password 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 ) / CREATE TABLE departments# ( department_id NUMBER(4) CONSTRAINT departments_pk PRIMARY KEY, department_name VARCHAR2(30) CONSTRAINT dept_department_name_not_null NOT NULL CONSTRAINT dept_department_name_unique UNIQUE, manager_id NUMBER(6) ) / 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_to_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_mgrid_to_emp_empid_fk REFERENCES employees#, department_id CONSTRAINT emp_to_dept_fk REFERENCES departments# ) / CREATE TABLE job_history# ( employee_id CONSTRAINT job_hist_to_emp_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_dept_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 ) ) / 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# / 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; / 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; / CREATE SEQUENCE employees_sequence START WITH 210; CREATE SEQUENCE departments_sequence START WITH 275; ------------ -- Load data ------------ INSERT INTO jobs (job_id, job_title, min_salary, max_salary) SELECT job_id, job_title, min_salary, max_salary FROM HR.JOBS / INSERT INTO departments (department_id, department_name, manager_id) SELECT department_id, department_name, manager_id FROM HR.DEPARTMENTS / 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 / 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 / COMMIT; ----------------------------- -- Add foreign key constraint ----------------------------- ALTER TABLE departments# ADD CONSTRAINT dept_to_emp_fk FOREIGN KEY(manager_id) REFERENCES employees#; ---------------------------------------------- -- Grant privileges on schema objects to users ---------------------------------------------- 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; 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 SELECT ON jobs TO app_admin_user; GRANT SELECT ON departments TO app_admin_user;
インストール・スクリプトemployees.sql
は、「employees_pkgパッケージの作成」の開発環境の実行内容をデプロイメント環境で実行します。
テキスト・エディタまたはSQL Developerを使用して、employees.sql
を作成できます。
任意のテキスト・エディタでemployees.sql
を作成するには、次のテキストを入力し、employees.sql
としてファイルを保存します。パスワード
に、ユーザーapp_code
の作成時にschema.sql
が指定するパスワードを使用します。
-----------------------
-- Create employees_pkg
-----------------------
CONNECT app_code/password
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;
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 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;
/
---------------------------------------------
-- Grant privileges on employees_pkg to users
---------------------------------------------
GRANT EXECUTE ON employees_pkg TO app_user;
GRANT EXECUTE ON employees_pkg TO app_admin_user;
インストール・スクリプトadmin.sql
は、「admin_pkgパッケージの作成」の開発環境の実行内容をデプロイメント環境で実行します。
テキスト・エディタまたはSQL Developerを使用して、admin.sql
を作成できます。
任意のテキスト・エディタでadmin.sql
を作成するには、次のテキストを入力し、admin.sql
としてファイルを保存します。パスワード
に、ユーザーapp_admin
の作成時にschema.sql
が指定するパスワードを使用します。
-------------------
-- Create admin_pkg
-------------------
CONNECT app_admin/password
CREATE SYNONYM departments FOR app_data.departments;
CREATE SYNONYM jobs FOR app_data.jobs;
CREATE SYNONYM departments_sequence FOR app_data.departments_sequence;
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 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;
/
----------------------------------------
-- Grant privileges on admin_pkg to user
----------------------------------------
GRANT EXECUTE ON admin_pkg TO app_admin_user;
マスター・インストール・スクリプトcreate_app.sql
は、サンプル・アプリケーションの他の4つのインストール・スクリプトを正しい順序で実行して、デプロイメント環境にサンプル・アプリケーションをデプロイします。
create_app.sql
を作成するには、任意のテキスト・エディタで次のテキストを入力し、create_app.sql
としてファイルを保存します。
@schemas.sql @objects.sql @employees.sql @admin.sql
この項では、「サンプル・アプリケーションのインストール・スクリプトの作成」で作成したインストール・スクリプトを使用してサンプル・アプリケーションをデプロイする方法について説明します。
注意: 次の手順では、CREATE USER およびDROP USER システム権限を持つユーザーの名前とパスワードが必要です。 |
SQL*Plusを使用してサンプル・アプリケーションをデプロイするには、次の手順を実行します。
「サンプル・アプリケーションのインストール・スクリプトの作成」で作成したインストール・スクリプトをデプロイメント環境にコピーします。
デプロイメント環境で、CREATE
USER
およびDROP
USER
システム権限を持つユーザーとしてOracle Databaseに接続します。
SQL>
プロンプトで、マスター・インストール・スクリプトを実行します。
@create_app.sql
マスター・インストール・スクリプトは、サンプル・アプリケーションの他の4つのインストール・スクリプトを正しい順序で実行して、デプロイメント環境にサンプル・アプリケーションをデプロイします。
SQL Developerを使用してサンプル・アプリケーションをデプロイするには、次の手順を実行します。
必要であれば、デプロイメント環境への接続を作成します。
「接続名」に、開発環境の接続名ではない名前を入力します。
「サンプル・アプリケーションのインストール・スクリプトの作成」で作成したインストール・スクリプトをデプロイメント環境にコピーします。
デプロイメント環境でCREATE
USER
およびDROP
USER
システム権限を持つユーザーとしてOracle Databaseに接続します。
新しいペインが表示されます。タブにはデプロイメント環境への接続の名前が表示されます。ペインには、「ワークシート」および「クエリー・ビルダー」の2つのサブペインが表示されます。
「ワークシート」ペインで、マスター・インストール・スクリプトを実行するコマンドを入力します。
@create_app.sql
「スクリプトの実行」アイコンをクリックします。
マスター・インストール・スクリプトは、サンプル・アプリケーションの他の4つのインストール・スクリプトを正しい順序で実行して、デプロイメント環境にサンプル・アプリケーションをデプロイします。出力が「ワークシート」ペインの下の「スクリプトの出力」ペインに表示されます。
「接続」フレームでは、デプロイメント環境への接続を展開し、次にサンプル・アプリケーションが使用する各オブジェクトのタイプを展開すると、サンプル・アプリケーションのオブジェクトが表示されます。
参照:
|
デプロイメント環境にアプリケーションをインストールした後、SQL Developerで次の方法により有効性をチェックできます。
「接続」フレームで、次の手順を実行します。
デプロイメント環境への接続を展開します。
新しいオブジェクトの定義を確認します。
「レポート」ペインで、次の手順を実行します。
「データ・ディクショナリ・レポート」を展開します。
データ・ディクショナリ・レポートのリストが表示されます。
「すべてのオブジェクト」を展開します。
オブジェクト・レポートのリストが表示されます。
「すべてのオブジェクト」を選択します。
「接続の選択」ウィンドウが表示されます。
「接続」フィールドで、デプロイメント環境への接続をメニューから選択します。
「OK」をクリックします。
「バインド値の入力」ウィンドウで、「所有者」または「オブジェクト名」のいずれかを選択します。
「適用」をクリックします。
「結果の表示」メッセージの後に、結果が表示されます。
このレポートではオブジェクトごとに、「所有者」、「オブジェクト・タイプ」、「オブジェクト名」、「ステータス」(「有効」または「無効」)、「作成日」および「最終DDL」のリストを出力します。「最終DDL」は、オブジェクトに影響を与えた最後のDDL操作の日付です。
「レポート」ペインで、「無効なオブジェクト」を選択します。
「バインド値の入力」ウィンドウで、「適用」をクリックします。
このレポートでは、「ステータス」が「無効」の各オブジェクトについて、「所有者」、「オブジェクト・タイプ」および「オブジェクト名」のリストを出力します。
参照: SQL Developerレポートの詳細は、『Oracle Database SQL Developerユーザーズ・ガイド』を参照してください。 |