ヘッダーをスキップ
Oracle® Database Express Edition 2日で開発者ガイド
11g リリース2 (11.2)
B66463-01
  目次へ移動
目次
索引へ移動
索引

前
 
次
 

7 ストアド・サブプログラムおよびパッケージの開発

この章の内容は次のとおりです。


ヒント:

PL/SQLコードを正常に作成または実行できない場合は、Oracle Databaseのトレース・ファイルを確認してください。トレース・ファイルの現在の場所は、USER_DUMP_DEST初期化パラメータによって指定されています。このパラメータの値は、SQL DeveloperのSQLワークシートまたはSQL*PlusでSHOW PARAMETER USER_DUMP_DESTを発行して確認することができます。トレース・ファイルの詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

ストアド・サブプログラムについて

サブプログラムは、特定の問題を解決したり、関連する一連のタスクを実行するSQL文およびPL/SQL文で構成されているPL/SQLユニットです。サブプログラムはパラメータを持つことができ、値は起動元から提供されます。サブプログラムは、プロシージャまたはファンクションです。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。

ストアド・サブプログラムは、データベースに格納されたサブプログラムです。ストアド・サブプログラムは、データベースに格納されているため、多様なデータベース・アプリケーションのビルディング・ブロックとして使用できます。別のサブプログラムまたは無名ブロック内で宣言されたサブプログラムは、ネストされたサブプログラムまたはローカル・サブプログラムと呼ばれます。宣言されたサブプログラムまたはブロックの外部から呼び出すことはできません。無名ブロックとは、データベースに格納されていないブロックです。

ストアド・サブプログラムは2種類あります。

スタンドアロン・ストアド・サブプログラムはプログラム・ロジックのテストに便利ですが、意図どおりに動作することが確実な場合は、パッケージに含めることをお薦めします。


参照:

  • ストアド・サブプログラムの一般情報は、『Oracle Database概要』を参照してください。

  • PL/SQLサブプログラムの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


パッケージについて

パッケージは、関連するサブプログラムと、サブプログラムで使用される明示カーソルおよび変数からなるPL/SQLユニットです。

サブプログラムは、パッケージに含めることをお薦めします。その理由の一部を次に示します。


注意:

Oracle Database XEは、多くのPL/SQLパッケージを提供してデータベース機能を拡張し、SQL機能へのPL/SQLによるアクセスを可能にしています。提供されたパッケージは、アプリケーションの作成や、独自のストアド・プロシージャを作成するアイデアのために使用できます。パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。


参照:

  • パッケージの一般情報は、『Oracle Database概要』を参照してください。

  • パッケージを使用する理由は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • PL/SQLパッケージの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • Oracleが提供するPL/SQLパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。


PL/SQL識別子について

すべてのPL/SQLサブプログラム、パッケージ、パラメータ、変数、定数、例外および明示カーソルには名前があり、これがPL/SQL識別子となります。

識別子は最短で1文字、最長で30文字です。最初は文字である必要がありますが、以降は文字、数字、ドル記号($)、アンダースコア(_)またはシャープ記号(#)を使用できます。たとえば、次に示すのが許容可能な識別子です。

X
t2
phone#
credit_limit
LastName
oracle$number
money$$$tree
SN##
try_again_

PL/SQLでは、識別子に関して大/小文字の区別がありません。たとえば、PL/SQLは次を同一とみなします。

lastname
LastName
LASTNAME

PL/SQLの予約語は、識別子として使用できません。PL/SQLキーワードは識別子として使用できますが、推奨されていません。PL/SQLの予約語およびキーワードのリストは、『Oracle Database PL/SQL言語リファレンス』を参照してください。


ヒント:

識別子には、ネーミング規則に従ったわかりやすい名前を使用します。たとえば、各定数名はcon_で始め、各変数名はvar_で始める、などです。


参照:

  • PL/SQL識別子の追加の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • PL/SQLのネーミング規則の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • PL/SQL識別子の適用範囲および可視性の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • PL/SQL識別子に関するデータの収集方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • PL/SQLが識別子名を解決する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


PL/SQLデータ型について

すべてのPL/SQL定数、変数、サブプログラム・パラメータおよびファンクション戻り値は、記憶形式、制約、値の有効範囲および実行できる演算を決定するデータ型を持っています。

PL/SQLデータ型は、SQLデータ型(VARCHAR2NUMBERDATEなど)またはPL/SQLのみのデータ型です。後者には、BOOLEANRECORDREF CURSORに加え、多くの事前定義サブタイプが含まれます。また、PL/SQLを使用して、独自のサブタイプを定義することもできます。

サブタイプは、他のデータ型のサブセットで、ベース型と呼ばれます。サブタイプには、そのベース型として同じ有効な操作がありますが、その有効な値のサブセットのみです。サブタイプでは、定数と変数の用途を示すことにより、信頼性の向上、ANSI/ISO型との互換性の提供、および見やすさの改善が可能です。

事前定義された数値のサブタイプPLS_INTEGERは特に便利です。演算に、ベース型が使用するライブラリ算術計算のかわりにハードウェア算術計算が使用されるためです。

PL/SQLのみのデータ型は、スキーマレベル(つまり、表またはスタンドアロン・ストアド・サブプログラム)では使用できません。そのため、PL/SQLのみのデータ型をストアド・サブプログラムで使用するには、サブプログラムをパッケージに含める必要があります。


参照:

  • PL/SQLデータ型の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • PLS_INTEGERデータ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • 「SQLデータ型について」


スタンドアロン・ストアド・サブプログラムの作成および管理

トピック:


注意:

このマニュアルのチュートリアルを行うには、ユーザーHRとして、SQL DeveloperからOracle Database XEに接続している必要があります。

サブプログラム構造について

サブプログラムは、PL/SQLブロック構造に従っています。つまり、次を含みます。

  • 宣言部分(オプション)

    宣言部分には、型、定数、変数、例外、明示カーソルおよびネストしたサブプログラムが含まれます。これらのアイテムは、サブプログラムに対してローカルであり、サブプログラムの実行が完了すると存在しなくなります。

  • 実行可能部分(必須)

    実行可能部分には、値を割り当て、実行を制御し、データを操作する文が含まれます。

  • 例外処理部分(オプション)

    例外処理部分には、例外(ランタイム・エラー)を処理するコードが含まれます。

コメントは、PL/SQLコードの任意の場所に表示可能です。PL/SQLコンパイラには無視されます。プログラムにコメントを追加することで、可読性が向上し、理解を助けます。単一行コメントは二重ハイフン(--)で始まり、行の末尾まで拡張されます。 複数行にまたがるコメントはスラッシュとアスタリスク(/*)で始まり、アスタリスクとスラッシュ(*/)で終わります。

プロシージャの構造は次のとおりです。

  PROCEDURE name [ ( parameter_list ) ]
  { IS | AS }
    [ declarative_part ]
  BEGIN  -- executable part begins
    statement; [ statement; ]...
  [ EXCEPTION -- executable part ends, exception-handling part begins]
    exception_handler; [ exception_handler; ]... ]
  END; /* exception-handling part ends if it exists;
          otherwise, executable part ends */

ファンクションの構造はプロシージャの構造に似ていますが、RETURN句および少なくとも1つのRETURN文(およびこのマニュアルの範囲外のオプション句)が含まれる点が異なります。

  FUNCTION name [ ( parameter_list ) ] RETURN data_type [ clauses ]
  { IS | AS }
    [ declarative_part ]
  BEGIN  -- executable part begins
    -- at least one statement must be a RETURN statement
    statement; [ statement; ]...
  [ EXCEPTION -- executable part ends, exception-handling part begins]
    exception_handler; [ exception_handler; ]... ]
  END; /* exception-handling part ends if it exists;
          otherwise, executable part ends */

PROCEDUREまたはFUNCTIONで始まりISまたはASの前で終わるコードは、サブプログラムの署名です。宣言部分、実行可能部分および例外処理部分は、サブプログラムの本体を構成します。例外ハンドラの構文は、「例外および例外ハンドラについて」を参照してください。


参照:

サブプログラムの部分の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

チュートリアル: スタンドアロン・ストアド・プロシージャの作成

スタンドアロン・ストアド・プロシージャを作成するには、SQL DeveloperのPL/SQLプロシージャ作成ツールまたはDDL文のCREATE PROCEDUREを使用します。

このチュートリアルでは、PL/SQLプロシージャ作成ツールを使用して、EVALUATIONS表に行を追加するADD_EVALUATIONというスタンドアロン・ストアド・プロシージャを作成する方法を示します。

PL/SQLプロシージャ作成ツールを使用してスタンドアロン・ストアド・プロシージャを作成するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「プロシージャ」を右クリックします。

    選択肢のリストが表示されます。

  3. 「新規プロシージャ」をクリックします。

    「PL/SQLプロシージャ作成」ウィンドウが開きます。

  4. 「スキーマ」では、デフォルト値のHRを受け入れます。

  5. 「名前」では、PROCEDURE1ADD_EVALUATIONに変更します。

  6. 「列の追加」アイコンをクリックします。

    列のヘッダーの下に1行表示されます。各フィールドには次のデフォルト値が含まれます。「名前」: PARAM1、「タイプ」: VARCHAR2、「モード」: IN、「デフォルト値」: 空。

  7. 「名前」では、param1evaluation_idに変更します。

  8. 「タイプ」で、「NUMBER」をメニューから選択します。

  9. 「モード」では、デフォルト値のINを受け入れます。

  10. 「デフォルト値」は空白のままにします。

  11. 「名前」にemployee_id、「型」にNUMBERを使用して、手順6から10を繰り返すことにより、2番目のパラメータを追加します。

  12. 「名前」にevaluation_date、「型」にDATEを使用して、手順6から10を繰り返すことにより、3番目のパラメータを追加します。

  13. 「名前」にjob_id、「型」にVARCHAR2を使用して、手順6から10を繰り返すことにより、4番目のパラメータを追加します。

  14. 「名前」にmanager_id、「型」にNUMBERを使用して、手順6から10を繰り返すことにより、5番目のパラメータを追加します。

  15. 「名前」にdepartment_id、「型」にNUMBERを使用して、手順6から10を繰り返すことにより、6番目のパラメータを追加します。

  16. 「名前」にtotal_score、「型」にNUMBERを使用して、手順6から10を繰り返すことにより、7番目のパラメータを追加します。

  17. 「OK」をクリックします。

    ADD_EVALUATIONペインが開き、プロシージャを作成したCREATE PROCEDURE文が表示されます。

    CREATE OR REPLACE PROCEDURE ADD_EVALUATION
    (
      EVALUATION_ID IN NUMBER
    , EMPLOYEE_ID IN NUMBER
    , EVALUATION_DATE IN DATE
    , JOB_ID IN VARCHAR2
    , MANAGER_ID IN NUMBER
    , DEPARTMENT_ID IN NUMBER
    , TOTAL_SCORE IN NUMBER
    ) AS
    BEGIN
      NULL;
    END ADD_EVALUATION;
    

    ADD_EVALUATIONペインのタイトルがイタリック・フォントになっています。プロシージャがデータベースに保存されていないことを示しています。

    プロシージャの実行部分にある唯一の文がNULLであるため、プロシージャは何も行いません。

  18. NULLを次の文に置換します。

    INSERT INTO EVALUATIONS (
       evaluation_id,
       employee_id,
       evaluation_date,
       job_id,
       manager_id,
       department_id,
       total_score 
    )
    VALUES (
      ADD_EVALUATION.evaluation_id,
      ADD_EVALUATION.employee_id,
      ADD_EVALUATION.evaluation_date,
      ADD_EVALUATION.job_id,
      ADD_EVALUATION.manager_id,
      ADD_EVALUATION.department_id,
      ADD_EVALUATION.total_score
    );
    

    (パラメータ名をプロシージャ名で修飾すると、パラメータは同じ名前の列と混同されなくなります。)

  19. 「ファイル」メニューから、「保存」を選択します。

    Oracle Database XEは、プロシージャをコンパイルして保存します。ADD_EVALUATIONペインのタイトルがイタリック・フォントではなくなります。


参照:

  • SQL Developerを使用してスタンドアロン・ストアド・プロシージャを作成する別の例は、『Oracle Database SQL Developerユーザーズ・ガイド』を参照してください。

  • CREATE PROCEDURE文に適用される一般情報は、「データ定義言語(DDL)文について」を参照してください。

  • CREATE PROCEDURE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


チュートリアル: スタンドアロン・ストアド・ファンクションの作成

スタンドアロン・ストアド・ファンクションを作成するには、SQL DeveloperのPL/SQLファンクション作成ツールまたはDDL文のCREATE FUNCTIONを使用します。

このチュートリアルでは、PL/SQLファンクション作成ツールを使用して、3つのパラメータを持ちNUMBER型の値を戻す、calculate_scoreというスタンドアロン・ストアド・ファンクションを作成する方法を示します。

PL/SQLファンクション作成ツールを使用してスタンドアロン・ストアド・ファンクションを作成するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「ファンクション」を右クリックします。

    選択肢のリストが表示されます。

  3. 「新規ファンクション」をクリックします。

    PL/SQLファンクションの作成ウィンドウが開きます。このウィンドウはPL/SQLプロシージャの作成ウィンドウ(「チュートリアル: スタンドアロン・ストアド・ファンクションの作成」参照)と似ていますが、パラメータ・ペインに、ファンクションから返された値の入る行があります。その行で、Nameの値は、<Return>であり、型のデフォルト値は、VARCHAR2です。

  4. 「スキーマ」では、デフォルト値のHRを受け入れます。

  5. 「名前」では、FUNCTION1calculate_scoreに変更します。

  6. 「パラメータ」ペインでは、唯一の行である「型」フィールドで、メニューからNUMBERを選択します。

  7. 「列の追加」アイコンをクリックします。

    列のヘッダーの下に1行表示されます。各フィールドには次のデフォルト値が含まれます。「名前」: PARAM1、「タイプ」: VARCHAR2、「モード」: IN、「デフォルト値」: 空。

  8. 「名前」では、PARAM1catに変更します。

  9. 「型」では、デフォルト値のVARCHAR2を受け入れます。

  10. 「モード」では、デフォルト値のINを受け入れます。

  11. 「デフォルト値」は空白のままにします。

  12. 「名前」にscore、「型」にNUMBERを使用して、手順7から11を繰り返すことにより、2番目のパラメータを追加します。

  13. 「名前」にweight、「型」にNUMBERを使用して、手順7から11を繰り返すことにより、3番目のパラメータを追加します。

  14. 「OK」をクリックします。

    CALCULATE_SCOREペインが開き、ファンクションを作成したCREATE FUNCTION文が表示されます。

    CREATE OR REPLACE FUNCTION CALCULATE_SCORE
    (
      CAT IN VARCHAR2
    , SCORE IN NUMBER
    , WEIGHT IN NUMBER
    ) RETURN NUMBER AS
    BEGIN
      RETURN NULL;
    END CALCULATE_SCORE;
    

    CALCULATE_SCOREペインのタイトルがイタリック・フォントになっています。ファンクションがデータベースに保存されていないことを示しています。

    ファンクションの実行部分にある唯一の文がRETURN NULLであるため、ファンクションは何も行いません。

  15. NULLscore * weightに置換します。

  16. 「ファイル」メニューから、「保存」を選択します。

    Oracle Database XEは、ファンクションをコンパイルして保存します。CALCULATE_SCOREペインのタイトルがイタリック・フォントではなくなります。


参照:

  • CREATE FUNCTION文に適用される一般情報は、「データ定義言語(DDL)文について」を参照してください。

  • CREATE FUNCTION文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


スタンドアロン・ストアド・サブプログラムの変更

スタンドアロン・ストアド・サブプログラムを変更するには、SQL Developerの編集ツール、DDL文のALTER PROCEDUREまたはALTER FUNCTIONを使用します。

編集ツールを使用してスタンドアロン・ストアド・サブプログラムを変更するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「ファンクション」または「プロシージャ」を展開します。

    ファンクションまたはプロシージャのリストが表示されます。

  3. 変更するファンクションまたはプロシージャをクリックします。

    「接続」ペインの右側に、フレームが表示されます。上部のタブには変更するサブプログラムの名前が表示されます。その下にあるのはサブタブです。

  4. 「コード」サブタブをクリックします。

    「コード」ペインが表示され、サブプログラムを作成したコードが表示されます。「コード」ペインが書込みモードになっています。(鉛筆アイコンをクリックすると、モードが書込みモードから読取り専用モードに、またはその逆に切り替わります。)

  5. 「コード」ペインで、コードを変更します。

    ペインのタイトルはイタリック・フォントになっており、変更がデータベースに保存されていないことを示しています。

  6. 「ファイル」メニューから、「保存」を選択します。

    Oracle Database XEは、サブプログラムをコンパイルして保存します。ペインのタイトルがイタリック・フォントではなくなります。


参照:

  • ALTER PROCEDUREおよびALTER FUNCTION文に適用される一般情報は、「データ定義言語(DDL)文について」を参照してください。

  • ALTER PROCEDURE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • ALTER FUNCTION文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


チュートリアル: スタンドアロン・ストアド・ファンクションのテスト

このチュートリアルでは、SQL Developerの実行ツールを使用して、スタンドアロン・ストアド・ファンクションcalculate_scoreをテストする方法を示します。

実行ツールを使用してCALCULATE_SCOREファンクションをテストするには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「ファンクション」を展開します。

    ファンクションのリストが表示されます。

  3. CALCULATE_SCOREを右クリックします。

    選択肢のリストが表示されます。

  4. 「実行」をクリックします。

    「PL/SQLの実行」ウィンドウが開きます。「PL/SQLブロック」フレームには、次のコードが含まれます。

    v_Return := CALCULATE_SCORE (
        CAT => CAT,
        SCORE => SCORE,
        WEIGHT => WEIGHT
      );
    
  5. SCOREおよびWEIGHTの値を、それぞれ8および0.2に変更します。

    v_Return := CALCULATE_SCORE (
        CAT => CAT,
        SCORE => 8,
        WEIGHT => 0.2
      );
    
  6. 「OK」をクリックします。

    「コード」ペインの下に「実行中」ウィンドウが開き、次の結果が表示されます。

    Connecting to the database hr_conn.
    Process exited.
    Disconnecting from the database hr_conn.
    

    「実行中」タブの右に「出力変数」タブが表示されます。

  7. 「出力変数」タブをクリックします。

    「変数」および「値」という2つのフレームが表示され、それぞれ<Return Value>および1.6と表示されます。


参照:

SQL Developerを使用したプロシージャおよびファンクションの実行とデバッグについては、『Oracle Database SQL Developerユーザーズ・ガイド』を参照してください。

スタンドアロン・ストアド・サブプログラムの削除

スタンドアロン・ストアド・サブプログラムを削除するには、SQL Developerのナビゲーション・フレームおよび削除ツール、あるいはDDL文のDROP PROCEDUREまたはDROP FUNCTIONを使用します。

削除ツールを使用してスタンドアロン・ストアド・サブプログラムを削除するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「ファンクション」または「プロシージャ」を展開します。

    ファンクションまたはプロシージャのリストが表示されます。

  3. 削除するファンクションまたはプロシージャの名前を右クリックします。

    選択肢のリストが表示されます。

  4. 「削除」をクリックします。

    「削除」ウィンドウが開きます。

  5. 「適用」をクリックします。

    「確認」ウィンドウが開きます。

  6. 「OK」をクリックします。


参照:

  • DROP PROCEDUREおよびDROP FUNCTION文に適用される一般情報は、「データ定義言語(DDL)文について」を参照してください。

  • DROP PROCEDURE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

  • DROP FUNCTION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。


パッケージの作成および管理

トピック:


参照:

パッケージ本体を変更する方法については、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。

パッケージ構造について

パッケージには必ず仕様部があり、通常、さらに本体があります。

パッケージ仕様には、パッケージの定義が記述されます。パッケージ外部から参照される可能性のある、型、変数、定数、例外、明示カーソル、およびサブプログラムが宣言されます。パッケージ仕様はapplication program interface(API)です。クライアント・プログラムからサブプログラムを起動するために必要な情報はすべて含まれていますが、それらの実装に関する情報は含まれません。

パッケージ本体は、パッケージ仕様部で宣言される明示カーソルの問合せおよびサブプログラムのコードを定義します(したがって、明示カーソルもサブプログラムもないパッケージには本体は必要ありません)。また、パッケージ本体は、仕様部で宣言されずパッケージの他のサブプログラムでのみ起動できるローカル・サブプログラムも定義できます。パッケージ本体の内容は、クライアント・プログラムに対して非表示です。パッケージ本体は、パッケージをコールするアプリケーションを無効にすることなく変更できます。


参照:

  • パッケージ仕様部の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • パッケージ本体の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


チュートリアル: パッケージ仕様部の作成

パッケージ仕様部を作成するには、SQL DeveloperのPL/SQLパッケージ作成ツールまたはDDL文のCREATE PACKAGEを使用します。

このチュートリアルでは、PL/SQLパッケージ作成ツールを使用して、EMP_EVALというパッケージの仕様部を作成する方法を示します。

このパッケージ仕様部は、このマニュアルのチュートリアルと例で、開発とデプロイの方法を説明するためのサンプル・アプリケーションのAPIです。

PL/SQLパッケージ作成ツールを使用してパッケージ仕様部を作成するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を右クリックします。

    選択肢のリストが表示されます。

  3. 「新規パッケージ」をクリックします。

    「PL/SQLパッケージ作成」ウィンドウが開きます。「スキーマ」フィールドには値HRが、「名前」フィールドにはデフォルト値PACKAGE1が入り、「新規ソースを小文字で追加」チェック・ボックスは選択解除されています。

  4. 「スキーマ」では、デフォルト値のHRを受け入れます。

  5. 「名前」では、PACKAGE1EMP_EVALに変更します。

  6. 「OK」をクリックします。

    EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。

    CREATE OR REPLACE PACKAGE emp_eval AS
     
      /* TODO enter package declarations (types, exceptions, methods etc) here */
     
    END emp_eval;
    

    ペインのタイトルがイタリック・フォントになっています。これは、パッケージがデータベースに保存されていないことを示しています。

  7. (オプション)CREATE PACKAGE文で、コメントを宣言に置換します。

    ここでこの手順を実行しない場合でも、「チュートリアル: パッケージ仕様部の変更」に示すように後で実行できます。

  8. 「ファイル」メニューから、「保存」を選択します。

    Oracle Database XEは、パッケージをコンパイルして保存します。EMP_EVALペインのタイトルがイタリック・フォントではなくなります。


参照:

CREATE PACKAGE文(パッケージ仕様部)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

チュートリアル: パッケージ仕様部の変更

パッケージ仕様部を変更するには、SQL Developerの編集ツール、またはOR REPLACE句を持つCREATE PACKAGEDDL文を使用します。

このチュートリアルでは、編集ツールを使用して、EMP_EVALパッケージの仕様を変更する方法を表示します。具体的には、eval_departmentプロシージャおよびcalculate_scoreファンクションに宣言を追加する方法を示します。

編集ツールを使用してEMP_EVALパッケージ仕様部を変更するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を展開します。

    パッケージのリストが表示されます。

  3. EMP_EVALを右クリックします。

    選択肢のリストが表示されます。

  4. 「編集」をクリックします。

    EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。

    CREATE OR REPLACE PACKAGE emp_eval AS
     
      /* TODO enter package declarations (types, exceptions, methods etc) here */
     
    END emp_eval;
    

    ペインのタイトルがイタリック・フォントになっていません。これは、パッケージがデータベースに保存されていることを示しています。

  5. EMP_EVALペインで、コメントを次のコードに置換します。

    PROCEDURE eval_department ( dept_id IN NUMBER );
    
    FUNCTION calculate_score ( evaluation_id IN NUMBER
                             , performance_id IN NUMBER)
                             RETURN NUMBER;
    

    EMP_EVALペインのタイトルがイタリック・フォントに変更されます。その変更がまだデータベースに保存されていないことを示しています。

  6. 「コンパイル」アイコンをクリックします。

    変更されたパッケージ仕様部は、コンパイルされデータベースに保存されます。EMP_EVALペインのタイトルがイタリック・フォントではなくなります。


参照:

OR REPLACE句が指定されたCREATE PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

チュートリアル: パッケージ本体の作成

パッケージ本体を作成するには、SQL Developerの本体の作成ツールまたはDDL文のCREATE PACKAGE BODYを使用します。

このチュートリアルでは、本体の作成ツールを使用して、EMP_EVALパッケージの本体を作成する方法を表示します。

このパッケージ本体は、このマニュアルのチュートリアルと例で、開発とデプロイの方法を説明するためのサンプル・アプリケーションの実装の詳細を含みます。

本体の作成ツールを使用してEMP_EVALパッケージの本体を作成するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を展開します。

    パッケージのリストが表示されます。

  3. EMP_EVALを右クリックします。

    選択肢のリストが表示されます。

  4. 「本体の作成」をクリックします。

    EMP_EVAL本体ペインが表示され、パッケージ本体の自動生成されたコードが表示されます。

    CREATE OR REPLACE
    PACKAGE BODY EMP_EVAL AS
     
      PROCEDURE eval_department(dept_id IN NUMBER) AS
      BEGIN
        /* TODO implementation required */
        NULL;
      END eval_department;
     
      FUNCTION calculate_score ( evaluation_id IN NUMBER
                               , performance_id IN NUMBER)
                               RETURN NUMBER AS
      BEGIN
        /* TODO implementation required */
        RETURN NULL;
      END calculate_score;
    
    END EMP_EVAL;
    

    ペインのタイトルがイタリック・フォントになっています。コードがデータベースに保存されていないことを示しています。

  5. (オプション)CREATE PACKAGE BODY文で、次の手順を実行します。

    • コメントを実行可能文に置換します。

    • (オプション)プロシージャの実行可能部分で、NULLを削除するか、または実行可能文に置換します。

    • (オプション)ファンクションの実行可能部分で、NULLを別の式に置換します。

    ここでこの手順を実行しない場合でも、「チュートリアル: サブプログラムでの変数および定数の宣言」に示すように後で実行できます。

  6. 「コンパイル」アイコンをクリックします。

    変更されたパッケージ本体は、コンパイルされデータベースに保存されます。 EMP_EVAL本体ペインのタイトルがイタリック・フォントではなくなります。


参照:

CREATE PACKAGE BODY文(パッケージ本体)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

パッケージの削除

パッケージ(仕様部および本体)を削除するには、SQL Developerのナビゲーション・フレームおよび削除ツール、またはDDL文のDROP PACKAGEを使用します。

削除ツールを使用してパッケージを削除するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を展開します。

    パッケージのリストが表示されます。

  3. 削除するパッケージの名前を右クリックします。

    選択肢のリストが表示されます。

  4. 「パッケージの削除」をクリックします。

    「削除」ウィンドウが開きます。

  5. 「適用」をクリックします。

    「確認」ウィンドウが開きます。

  6. 「OK」をクリックします。


参照:

DROP PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

変数および定数の宣言と値の割当て

PL/SQLがSQLより優れている点の1つは、PL/SQLでは変数および定数を宣言して使用できることです。

パッケージ仕様部で宣言された変数または定数は、パッケージにアクセス可能なプログラムで使用できます。パッケージ本体またはサブプログラムで宣言された変数または定数は、そのパッケージまたはサブプログラムに対してローカルです。

変数には特定のデータ型の値が格納されます。プログラムによって実行時に値を変更できます。 定数には、変更できない値が格納されます。

変数または定数には、任意のPL/SQLデータ型を指定できます。変数を宣言する際に初期値を割り当てることができ、割り当てない場合はこの値がNULLになります。定数を宣言するときには初期値を割り当てる必要があります。変数または定数に初期値を割り当てるには、代入演算子(:=)を使用します。


ヒント:

変わらないすべての値を定数として宣言します。これによってコンパイル・コードが最適化され、ソース・コードがメンテナンスしやすくなります。

トピック:


参照:

変数および定数の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

チュートリアル: サブプログラムでの変数および定数の宣言

このチュートリアルでは、SQL Developerの編集ツールを使用して、(「チュートリアル: パッケージ仕様部の作成」で指定した)EMP_EVAL.calculate_scoreファンクションで変数および定数を宣言する方法を示します。(また、このチュートリアルはパッケージ本体の変更の例も示します。)

calculate_scoreファンクションで変数および定数を宣言するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を展開します。

    パッケージのリストが表示されます。

  3. EMP_EVALを展開します。

    リストが表示されます。

  4. EMP_EVAL Bodyを右クリックします。

    選択肢のリストが表示されます。

  5. 「編集」をクリックします。

    EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。

    CREATE OR REPLACE
    PACKAGE BODY EMP_EVAL AS
     
      PROCEDURE eval_department ( dept_id IN NUMBER ) AS
    
      BEGIN
        /* TODO implementation required */
        NULL;
      END eval_department;
     
      FUNCTION calculate_score ( evaluation_id IN NUMBER
                               , performance_id IN NUMBER)
                               RETURN NUMBER AS
      BEGIN
        /* TODO implementation required */
        RETURN NULL;
      END calculate_score;
    
    END EMP_EVAL;
    
  6. RETURN NUMBER ASBEGINの間に、次の変数および定数の宣言を追加します。

    n_score       NUMBER(1,0);                -- variable
    n_weight      NUMBER;                     -- variable
    max_score     CONSTANT NUMBER(1,0) := 9;  -- constant, initial value 9
    max_weight    CONSTANT NUMBER(8,8) := 1;  -- constant, initial value 1
    

    EMP_EVAL本体ペインのタイトルがイタリック・フォントに変更されます。コードがまだデータベースに保存されていないことを示しています。

  7. 「ファイル」メニューから、「保存」を選択します。

    Oracle Database XEは、変更されたパッケージ本体をコンパイルして保存します。 EMP_EVAL本体ペインのタイトルがイタリック・フォントではなくなります。


参照:


変数、定数およびパラメータのデータ型が正しいことの確認

「チュートリアル: サブプログラムでの変数および定数の宣言」の後、EMP_EVALパッケージ本体のcalculate_scoreファンクションのコードは、次のようになります。

FUNCTION calculate_score ( evaluation_id IN NUMBER
                          , performance_id IN NUMBER )
                          RETURN NUMBER AS
  n_score       NUMBER(1,0);                -- variable
  n_weight      NUMBER;                     -- variable
  max_score     CONSTANT NUMBER(1,0) := 9;  -- constant, initial value 9
  max_weight    CONSTANT NUMBER(8,8) := 1;  -- constant, initial value 1
  BEGIN
    /* TODO implementation required */
    RETURN NULL;
  END calculate_score;

ファンクションの変数、定数およびパラメータは、SCORES表およびPERFORMANCE_PARTS表の値を示しています。

  • n_score変数には、SCORES表のSCORE列の値が格納され、max_score定数がこれらの値と比較されます。

  • n_weight変数には、PERFORMANCE_PARTS表のWEIGHT列の値が格納され、max_weight定数がこれらの値と比較されます。

  • evaluation_idパラメータには、SCORES表のEVALUATION_ID列の値が格納されます。

  • performance_idパラメータには、SCORES表のPERFORMANCE_ID列の値が格納されます。

このため、各変数、定数およびパラメータのデータ型は、対応する列と同じです。

列のデータ型が変更された場合、変数、定数およびパラメータも同じデータ型に変更される必要があります。変更されない場合、calculate_scoreファンクションが無効化されます。

変数、定数およびパラメータのデータ型を列のデータ型と常に一致させるには、%TYPE属性を付けて宣言します。 %TYPE属性は、表の列または別の変数のデータ型を提供し、正しいデータ型の割当てを保証します。


参照:

  • %TYPE属性の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • %TYPE属性の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。


チュートリアル: %TYPE属性を使用するための宣言の変更

このチュートリアルでは、SQL Developerの編集ツールを使用して、EMP_EVAL.calculate_scoreファンクションの変数、定数および仮パラメータの宣言(「チュートリアル: サブプログラムでの変数および定数の宣言」を参照)を、%TYPE属性を使用した宣言に変更する方法を示します。

%TYPEを使用するようにcalculate_scoreの宣言を変更するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を展開します。

    パッケージのリストが表示されます。

  3. EMP_EVALを展開します。

    リストが表示されます。

  4. EMP_EVAL Bodyを右クリックします。

    選択肢のリストが表示されます。

  5. 「編集」をクリックします。

    EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。

    CREATE OR REPLACE
    PACKAGE BODY emp_eval AS
     
      PROCEDURE eval_department ( dept_id IN NUMBER ) AS
      BEGIN
        /* TODO implementation required */
        NULL;
      END eval_department;
     
      FUNCTION calculate_score ( evaluation_id IN NUMBER
                               , performance_id IN NUMBER )
                               RETURN NUMBER AS
      n_score       NUMBER(1,0);                -- variable
      n_weight      NUMBER;                     -- variable
      max_score     CONSTANT NUMBER(1,0) := 9;  -- constant, initial value 9
      max_weight    CONSTANT NUMBER(8,8) := 1;  -- constant, initial value 1
      BEGIN
        /* TODO implementation required */
        RETURN NULL;
      END calculate_score;
    
    END emp_eval;
    
  6. ファンクションのコードに、太字で示された変更を加えます。

      FUNCTION calculate_score ( evaluation_id IN SCORES.EVALUATION_ID%TYPE
                                , performance_id IN SCORES.PERFORMANCE_ID%TYPE)
                                RETURN NUMBER AS
      n_score       SCORES.SCORE%TYPE;
      n_weight      PERFORMANCE_PARTS.WEIGHT%TYPE;
      max_score     CONSTANT SCORES.SCORE%TYPE := 9;
      max_weight    CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
    
  7. EMP_EVALを右クリックします。

    選択肢のリストが表示されます。

  8. 「編集」をクリックします。

    EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。

    CREATE OR REPLACE PACKAGE EMP_EVAL AS
     
    PROCEDURE eval_department(dept_id IN NUMBER);
    FUNCTION calculate_score(evaluation_id IN NUMBER
                            , performance_id IN NUMBER)
                              RETURN NUMBER;
     
    END EMP_EVAL;
    
  9. ファンクションのコードに、太字で示された変更を加えます。

    FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                            , performance_id IN scores.performance_id%TYPE)
    
  10. EMP_EVALを右クリックします。

    選択肢のリストが表示されます。

  11. 「コンパイル」をクリックします。

  12. EMP_EVAL Bodyを右クリックします。

    選択肢のリストが表示されます。

  13. 「コンパイル」をクリックします。

変数への値の割当て

次の方法で変数に値を割り当てることができます。

  • 代入演算子を使用して、式の値を割り当てます。

  • SELECT INTO文またはFETCH文を使用して、表の値を割り当てます。

  • OUTまたはIN OUTパラメータとしてサブプログラムに渡し、サブプログラム内で値を割り当てます。

  • 変数を値にバインドします。

トピック:


参照:

  • 変数への値の割当ての詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • 変数のバインドの詳細は、Oracle Database Express Edition 2日で.NET開発者ガイドfor Microsoft Windowsを参照してください。

  • 変数のバインドの詳細は、『Oracle Database Express Edition 2日でPHP開発者ガイド』を参照してください。

  • 変数のバインドの詳細は、『Oracle Database Express Edition 2日でJava開発者ガイド』を参照してください。

  • 変数のバインドの詳細は、『Oracle Database Express Edition 2日でApplication Express開発者ガイド』を参照してください。


代入演算子を使用した変数への値の割当て

代入演算子(:=)を使用して、サブプログラムの宣言部分または実行可能部分の変数に式の値を割り当てることができます。

サブプログラムの宣言部分では、宣言時に、変数に初期値を割り当てることができます。次に構文を示します。

variable_name data_type := expression;

サブプログラムの実行可能部分では、代入文によって変数に値を割り当てることができます。次に構文を示します。

variable_name := expression;

例7-1では、EMP_EVAL.calculate_score関数に対して行う変更が太字で表示されて、変数running_totalが追加され、この新しい変数が関数の戻り値として使用されます。代入演算子は、ファンクションの宣言部と実行可能部の両方に表示されます。(running_totalのデータ型は、異なる精度およびスケールを持つ2つのNUMBER値の積を保持するため、SCORES.SCORE%TYPEまたはPERFORMANCE_PARTS.WEIGHT%TYPEではなく、NUMBERである必要があります。)

例7-1 代入演算子を使用した変数への値の割当て

FUNCTION calculate_score(evaluation_id IN SCORES.EVALUATION_ID%TYPE
                         , performance_id IN SCORES.PERFORMANCE_ID%TYPE)
                         RETURN NUMBER AS
  n_score       SCORES.SCORE%TYPE;
  n_weight      PERFORMANCE_PARTS.WEIGHT%TYPE;
  running_total NUMBER := 0;
  max_score     CONSTANT SCORES.SCORE%TYPE := 9;
  max_weight    CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE:= 1;
BEGIN
  running_total := max_score * max_weight;
  RETURN running_total;
END calculate_score;

参照:

  • 変数宣言の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • 代入文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。


SELECT INTO文を使用した変数への値の割当て

サブプログラムまたはパッケージの表の値を使用するには、SELECT INTO文によって変数に値を割り当てる必要があります。

例7-2では、表の値からrunning_totalを計算させるためにEMP_EVAL.calculate_scoreファンクションに対して加える変更を太字で示しています。

例7-2 SELECT INTOを使用した変数への表の値の割当て

FUNCTION calculate_score ( evaluation_id IN scores.evaluation_id%TYPE
                         , performance_id IN scores.performance_id%TYPE )
                         RETURN NUMBER AS

  n_score       scores.score%TYPE;
  n_weight      performance_parts.weight%TYPE;
  running_total NUMBER := 0;
  max_score     CONSTANT scores.score%TYPE := 9;
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;
BEGIN
  SELECT s.score INTO n_score
  FROM SCORES s
  WHERE evaluation_id = s.evaluation_id 
  AND performance_id = s.performance_id;

  SELECT p.weight INTO n_weight
  FROM PERFORMANCE_PARTS p
  WHERE performance_id = p.performance_id;

  running_total := n_score * n_weight;
  RETURN running_total;
END calculate_score;

例7-3に示すadd_evalプロシージャは、EVALUATIONS表への行挿入に、EMPLOYEES表の対応する行の値を使用する場合の例です。add_evalプロシージャは、EMP_EVALパッケージの本体にのみ追加し、仕様には追加しません。add_evalは仕様内には定義しないので、そのパッケージのローカル・プロシージャになり、パッケージ内の他のサブプログラムからのみ起動でき、パッケージ外部からは起動できません。

例7-3 他の表からの値を使用した表の行の挿入

PROCEDURE add_eval ( employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
                   , today IN DATE )
AS
  job_id         EMPLOYEES.JOB_ID%TYPE;
  manager_id     EMPLOYEES.MANAGER_ID%TYPE;
  department_id  EMPLOYEES.DEPARTMENT_ID%TYPE;
BEGIN
  SELECT e.job_id INTO job_id
  FROM EMPLOYEES e
  WHERE employee_id = e.employee_id;

  SELECT e.manager_id INTO manager_id
  FROM EMPLOYEES e 
  WHERE employee_id = e.employee_id;

  SELECT e.department_id INTO department_id
  FROM EMPLOYEES e
  WHERE employee_id = e.employee_id;
 
  INSERT INTO EVALUATIONS (
    evaluation_id,
    employee_id,
    evaluation_date,
    job_id,
    manager_id,
    department_id,
    total_score
  )
  VALUES (
    evaluations_seq.NEXTVAL,   -- evaluation_id
    add_eval.employee_id,      -- employee_id
    add_eval.today,            -- evaluation_date
    add_eval.job_id,           -- job_id
    add_eval.manager_id,       -- manager_id
    add_eval.department_id,    -- department_id
    0                          -- total_score
  );
END add_eval;

参照:

SELECT INTO文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

プログラム・フローの制御

入力の順序に従って文を実行するSQLとは異なり、PL/SQLには、プログラム・フローを制御できる制御文があります。

トピック:

制御文について

PL/SQLには、次の3つのカテゴリの制御文があります。

  • 条件付き選択文は、異なるデータ値に対して異なる文を実行します。

    条件付き選択文は、IFおよびCASEです。

  • 繰り返し文は、一連の異なるデータ値で同じ文を繰り返します。

    繰り返し文は、FOR LOOPWHILE LOOPおよび基本のLOOPです。

    EXIT文は、制御をループの終わりに転送します。 CONTINUE文は、現在のループの反復を終了し、制御を次の反復に転送します。 EXITおよびCONTINUEには、オプションのWHEN句があり、条件を指定できます。

  • 順次制御文は、指定されたラベル付き文に移動するか、または何も処理をしません。

    順次制御文は、GOTOおよびNULLです。


参照:

  • PL/SQL制御文の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


IF文の使用

IF文は、ブール式の値に応じて一連の文を実行またはスキップします。

IF文の構文は、次のとおりです。

IF boolean_expression THEN statement [, statement ]
[ ELSIF boolean_expression THEN statement [, statement ] ]...
[ ELSE  statement [, statement ] ]
END IF;

企業が雇用の最初の10年は1年に2回、その後は1年に1回のみ、従業員を評価するとします。これには従業員の評価頻度を戻すファンクションが必要です。この場合、例7-4のように、IF文を使用してファンクションの戻り値を判断できます。

eval_frequency関数をEMP_EVALパッケージの本体に追加しますが、仕様には追加しません。eval_frequencyは、仕様にはないため、パッケージに対してローカルであり、パッケージ内の他のサブプログラムでのみ起動でき、パッケージ外からは起動できません。

例7-4 ファンクションの戻り値を判断するIF文

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date     EMPLOYEES.HIRE_DATE%TYPE;
  today      EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq  PLS_INTEGER;
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;
  ELSE
    eval_freq := 2;
  END IF;

  RETURN eval_freq;
END eval_frequency;

参照:

  • IF文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • IF文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


CASE文の使用

CASE文は、一連の条件から選択し、対応する文を実行します。

単純なCASE文は、1つの式を評価し、それをいくつかの潜在的な値と比較します。CASE文には、この構文があります。

CASE expression
WHEN value THEN statement
[ WHEN value THEN statement ]...
[ ELSE statement [, statement ]... ]
END CASE;

検索CASE文は、複数のブール式を評価し、値がTRUEとなった最初の式を選択します。検索CASE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


ヒント:

CASE文またはネストされたIF文のいずれも使用できる場合、CASE文を使用すると、より読みやすく効率的です。

従業員が1年に1回のみ評価され、eval_frequencyファンクションに、JOB_IDに応じて給与の値上げを推奨させるとします。

例7-5に太字で示されているように、eval_frequencyファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。)

例7-5 出力する文字列を判断するCASE文

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date     EMPLOYEES.HIRE_DATE%TYPE;
  today      EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq  PLS_INTEGER;
  j_id       EMPLOYEES.JOB_ID%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    CASE j_id
       WHEN 'PU_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 8% salary increase for employee # ' || emp_id);
       WHEN 'SH_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 7% salary increase for employee # ' || emp_id);
       WHEN 'ST_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 6% salary increase for employee # ' || emp_id);
       WHEN 'HR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee # ' || emp_id);
       WHEN 'PR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee # ' || emp_id);
       WHEN 'MK_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 4% salary increase for employee # ' || emp_id);
       ELSE DBMS_OUTPUT.PUT_LINE(
         'Nothing to do for employee #' || emp_id);
    END CASE;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

参照:

  • 「問合せにおけるCASE式の使用」

  • CASE文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • CASE文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


FOR LOOP文の使用

FOR LOOP文は、lower_boundからupper_boundまでの範囲の各整数に対して1回ずつ、一連の文を繰り返します。次に構文を示します。

FOR counter IN lower_bound..upper_bound LOOP
  statement [, statement ]...
END LOOP;

LOOPEND LOOP間の文では、counterを使用できますが、値は変更できません。

給与の値上げを推奨するだけでなく、eval_frequencyファンクションに、5年間毎年、推奨された額の値上げをした場合の給与額もレポートさせるとします。

例7-6に太字で示されているように、eval_frequencyファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。)

例7-6 5年後の給与を計算するFOR LOOP文

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date      EMPLOYEES.HIRE_DATE%TYPE;
  today       EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq   PLS_INTEGER;
  j_id        EMPLOYEES.JOB_ID%TYPE;
  sal         EMPLOYEES.SALARY%TYPE;
  sal_raise   NUMBER(3,3) := 0;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT SALARY INTO sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    CASE j_id
      WHEN 'PU_CLERK' THEN sal_raise := 0.08;
      WHEN 'SH_CLERK' THEN sal_raise := 0.07;
      WHEN 'ST_CLERK' THEN sal_raise := 0.06;
      WHEN 'HR_REP'   THEN sal_raise := 0.05;
      WHEN 'PR_REP'   THEN sal_raise := 0.05;
      WHEN 'MK_REP'   THEN sal_raise := 0.04;
      ELSE NULL;
    END CASE;

    IF (sal_raise != 0) THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
          ROUND((sal_raise * 100),0) ||
          '% each year for 5 years, it will be:');

        FOR i IN 1..5 LOOP
          sal := sal * (1 + sal_raise);
          DBMS_OUTPUT.PUT_LINE(ROUND(sal, 2) || ' after ' || i || ' year(s)');
        END LOOP;
      END;
    END IF;

  ELSE
    eval_freq := 2;
  END IF;

  RETURN eval_freq;
END eval_frequency;

参照:

  • FOR LOOP文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • FOR LOOP文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


WHILE LOOP文の使用

WHILE LOOP文は、条件がTRUEであるかぎり一連の文を繰り返します。次に構文を示します。

WHILE condition LOOP
  statement [, statement ]...
END LOOP;

注意:

LOOPEND LOOPの間の文によってconditionFALSEにならない場合、WHILE LOOP文は無限に実行され続けます。

eval_frequencyファンクションがFOR LOOP文ではなくWHILE LOOP文を使用し、推奨された給与がJOB_IDの給与の最大値を超過した後に終了するとします。

例7-7に太字で示されているように、eval_frequencyファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。)

例7-7 最大値まで給与を計算するWHILE LOOP文

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date      EMPLOYEES.HIRE_DATE%TYPE;
  today       EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq   PLS_INTEGER;
  j_id        EMPLOYEES.JOB_ID%TYPE;
  sal         EMPLOYEES.SALARY%TYPE;
  sal_raise   NUMBER(3,3) := 0;
  sal_max     JOBS.MAX_SALARY%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT SALARY INTO sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT MAX_SALARY INTO sal_max
    FROM JOBS
    WHERE JOB_ID = j_id;

    CASE j_id
      WHEN 'PU_CLERK' THEN sal_raise := 0.08;
      WHEN 'SH_CLERK' THEN sal_raise := 0.07;
      WHEN 'ST_CLERK' THEN sal_raise := 0.06;
      WHEN 'HR_REP'   THEN sal_raise := 0.05;
      WHEN 'PR_REP'   THEN sal_raise := 0.05;
      WHEN 'MK_REP'   THEN sal_raise := 0.04;
      ELSE NULL;
    END CASE;

    IF (sal_raise != 0) THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
          ROUND((sal_raise * 100),0) ||
          '% each year, it will be:');

        WHILE sal <= sal_max LOOP
          sal := sal * (1 + sal_raise);
          DBMS_OUTPUT.PUT_LINE(ROUND(sal, 2));
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Maximum salary for this job is ' || sal_max);
      END;
    END IF;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

参照:

  • WHILE LOOP文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • WHILE LOOP文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


基本のLOOPおよびEXIT WHEN文の使用

基本のLOOP文は、一連の文を繰り返します。次に構文を示します。

LOOP
  statement [, statement ]...
END LOOP;

少なくとも1つのは、EXIT文である必要があります。そうでない場合、LOOP文は無限に実行され続けます。

EXIT WHEN文(オプションのWHEN句を持つEXIT文)は、条件がTRUEのときにループを終了し、制御をループの終わりに転送します。

eval_frequencyファンクションのWHILE LOOP文の最後の反復では、最後に計算された値は通常、給与の最大値を超過します。

例7-8に示すように、WHILE LOOP文を、EXIT WHEN文を含む基本のLOOP文に変更します。

WHILE LOOP

例7-8 EXIT WHEN文の使用

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date      EMPLOYEES.HIRE_DATE%TYPE;
  today       EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq   PLS_INTEGER;
  j_id        EMPLOYEES.JOB_ID%TYPE;
  sal         EMPLOYEES.SALARY%TYPE;
  sal_raise   NUMBER(3,3) := 0;
  sal_max     JOBS.MAX_SALARY%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT SALARY INTO sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT MAX_SALARY INTO sal_max
    FROM JOBS
    WHERE JOB_ID = j_id;

    CASE j_id
      WHEN 'PU_CLERK' THEN sal_raise := 0.08;
      WHEN 'SH_CLERK' THEN sal_raise := 0.07;
      WHEN 'ST_CLERK' THEN sal_raise := 0.06;
      WHEN 'HR_REP'   THEN sal_raise := 0.05;
      WHEN 'PR_REP'   THEN sal_raise := 0.05;
      WHEN 'MK_REP'   THEN sal_raise := 0.04;
      ELSE NULL;
    END CASE;

    IF (sal_raise != 0) THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
          ROUND((sal_raise * 100),0) ||
          '% each year, it will be:');

        LOOP
          sal := sal * (1 + sal_raise);
          EXIT WHEN sal > sal_max;
          DBMS_OUTPUT.PUT_LINE(ROUND(sal,2));
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Maximum salary for this job is ' || sal_max);
      END;
    END IF;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

参照:

  • LOOP文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • EXIT文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • LOOP文およびEXIT文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


レコードおよびカーソルの使用

トピック:


参照:

レコードの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

レコードについて

レコードは、C、C++、Javaなどのstruct型に似た、異なる型のデータ値を格納できるPL/SQLの複合変数です。レコードの内部コンポーネントは、フィールドと呼ばれます。レコード・フィールドにアクセスするには、ドット表記法record_name.field_nameを使用します。

レコード・フィールドは、スカラー変数のように扱うことができます。レコード全体をサブプログラム・パラメータとして渡すこともできます(ただし、送受信されるサブプログラムがいずれもスタンドアロン・ストアド・サブプログラムではない場合)。

レコードは、表の行からのデータ、または表の行の特定列からのデータを格納するのに便利です。各レコード・フィールドは表の列に対応しています。

レコードを作成する方法は3つあります。

  • RECORD型を宣言し、その型の変数を宣言します。

    次に構文を示します。

    TYPE record_name IS RECORD
      ( field_name data_type [:= initial_value]
     [, field_name data_type [:= initial_value ] ]... );
    
    variable_name record_name;
    
  • table_name%ROWTYPE型の変数を宣言します。

    レコードのフィールドの名前およびデータ型は、表の列と同じです。

  • cursor_name%ROWTYPE型の変数を宣言します。

    レコードのフィールドの名前およびデータ型は、カーソルSELECT文のFROM句の表の列と同じです。


参照:

  • RECORD型の定義およびその型のレコードの宣言の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • RECORD型の定義の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • %ROWTYPE属性の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • %ROWTYPE属性の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。


チュートリアル: RECORD型の宣言

このチュートリアルでは、SQL Developerの編集ツールを使用してRECORD型のsal_infoを宣言する方法を示します。フィールドには、ジョブID、そのジョブIDの給与の最小値および最大値、現在の給与、推奨される値上げなどの、従業員の給与情報を格納できます。

RECORD型sal_infoを宣言するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を展開します。

    パッケージのリストが表示されます。

  3. EMP_EVALを右クリックします。

    選択肢のリストが表示されます。

  4. 「編集」をクリックします。

    EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。

    CREATE OR REPLACE PACKAGE EMP_EVAL AS
     
    PROCEDURE eval_department(dept_id IN NUMBER);
    FUNCTION calculate_score(evaluation_id IN NUMBER
                            , performance_id IN NUMBER)
                              RETURN NUMBER;
     
    END EMP_EVAL;
    
  5. EMP_EVALペインで、END EMP_EVALの直前に次のコードを追加します。

    TYPE sal_info IS RECORD
      ( j_id     jobs.job_id%type
      , sal_min  jobs.min_salary%type
      , sal_max  jobs.max_salary%type
      , sal      employees.salary%type
      , sal_raise NUMBER(3,3) );
    

    ペインのタイトルがイタリック・フォントになっています。これは、変更がデータベースに保存されていないことを示しています。

  6. 「コンパイル」アイコンをクリックします。

    変更されたパッケージ仕様部は、コンパイルされデータベースに保存されます。EMP_EVALペインのタイトルがイタリック・フォントではなくなります。

    これで、「チュートリアル: レコード・パラメータによるサブプログラムの作成および起動」に示すようにsal_info型のレコードを宣言できます。

チュートリアル: レコード・パラメータによるサブプログラムの作成および起動

このチュートリアルでは、RECORDsal_info「チュートリアル: RECORD型の宣言」で宣言した場合に、SQL Developerの編集ツールを使用して、次の手順を実行する方法を示します。

  • sal_info型のパラメータを含むsalary_scheduleプロシージャを作成します。

  • sal_info型のemp_salレコードを宣言し、フィールドを移入してsalary_scheduleプロシージャに渡すようにeval_frequencyファンクションを変更します。

eval_frequencysalary_scheduleを起動するため、salary_scheduleの宣言はeval_frequencyの宣言に先行する必要があります(そうでない場合、パッケージはコンパイルされません)。ただし、salary_scheduleの定義はパッケージ本体の任意の位置に置くことができます。

salary_scheduleを作成しeval_frequencyを変更するには、次の手順を実行します。

  1. 「接続」タブで、hr_connを展開します。

    hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。

  2. 「パッケージ」を展開します。

    パッケージのリストが表示されます。

  3. EMP_EVALを展開します。

    リストが表示されます。

  4. EMP_EVAL Bodyを右クリックします。

    選択肢のリストが表示されます。

  5. 「編集」をクリックします。

    EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。

  6. EMP_EVAL本体ペインで、END EMP_EVALの直前に、次のsalary_scheduleプロシージャの定義を追加します。

    PROCEDURE salary_schedule (emp IN sal_info) AS
      accumulating_sal  NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE('If salary ' || emp.sal || 
        ' increases by ' || ROUND((emp.sal_raise * 100),0) || 
        '% each year, it will be:');
    
      accumulating_sal := emp.sal;
    
      WHILE accumulating_sal <= emp.sal_max LOOP
        accumulating_sal := accumulating_sal * (1 + emp.sal_raise);
        DBMS_OUTPUT.PUT_LINE(ROUND(accumulating_sal,2) ||', ');
      END LOOP;
    END salary_schedule;
    

    ペインのタイトルがイタリック・フォントになっています。これは、変更がデータベースに保存されていないことを示しています。

  7. EMP_EVAL本体ペインで、太字で示されたコードを次の位置に入力します。

    CREATE OR REPLACE
    PACKAGE BODY EMP_EVAL AS
    
    FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
      RETURN PLS_INTEGER;
    PROCEDURE salary_schedule(emp IN sal_info);
    PROCEDURE add_eval(employee_id IN employees.employee_id%type, today IN DATE);
     
    PROCEDURE eval_department (dept_id IN NUMBER) AS
    
  8. eval_frequencyファンクションを編集し、太字で示された変更を加えます。

    FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
      RETURN PLS_INTEGER
    AS
      h_date     EMPLOYEES.HIRE_DATE%TYPE;
      today      EMPLOYEES.HIRE_DATE%TYPE;
      eval_freq  PLS_INTEGER;
      emp_sal    SAL_INFO;  -- replaces sal, sal_raise, and sal_max
     
    BEGIN
      SELECT SYSDATE INTO today FROM DUAL;
     
      SELECT HIRE_DATE INTO h_date
      FROM EMPLOYEES
      WHERE EMPLOYEE_ID = emp_id;
     
      IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
         eval_freq := 1;
     
         /* populate emp_sal */
     
         SELECT JOB_ID INTO emp_sal.j_id FROM EMPLOYEES
         WHERE EMPLOYEE_ID = emp_id;
     
         SELECT MIN_SALARY INTO emp_sal.sal_min FROM JOBS
         WHERE JOB_ID = emp_sal.j_id;
     
         SELECT MAX_SALARY INTO emp_sal.sal_max FROM JOBS
         WHERE JOB_ID = emp_sal.j_id;
     
         SELECT SALARY INTO emp_sal.sal FROM EMPLOYEES
         WHERE EMPLOYEE_ID = emp_id;
     
         emp_sal.sal_raise := 0;  -- default
     
         CASE emp_sal.j_id
           WHEN 'PU_CLERK' THEN emp_sal.sal_raise := 0.08;
           WHEN 'SH_CLERK' THEN emp_sal.sal_raise := 0.07;
           WHEN 'ST_CLERK' THEN emp_sal.sal_raise := 0.06;
           WHEN 'HR_REP' THEN emp_sal.sal_raise := 0.05;
           WHEN 'PR_REP' THEN emp_sal.sal_raise := 0.05;
           WHEN 'MK_REP' THEN emp_sal.sal_raise := 0.04;
           ELSE NULL;
         END CASE;
     
         IF (emp_sal.sal_raise != 0) THEN
           salary_schedule(emp_sal);
         END IF;
       ELSE
         eval_freq := 2;
       END IF;
     
       RETURN eval_freq;
     END eval_frequency;
    
  9. 「コンパイル」をクリックします。

カーソルについて

Oracle Database XEは、SQL文の実行時に、結果セットおよび処理情報を無名のプライベートSQL領域に保存します。この無名の領域へのポインタはカーソルと呼ばれ、結果セットの行を1つずつ取得できます。 カーソル属性は、カーソルの状態に関する情報を戻します。

SQL DML文またはPL/SQL SELECT INTO文を実行するたびに、PL/SQLは暗黙カーソルをオープンします。このカーソルに関する情報は属性から得られますが、制御はできません。文の実行後、データベースはカーソルをクローズしますが、属性の値は別のDMLまたはSELECT INTO文が実行されるまで使用可能です。

PL/SQLでは、明示カーソルも宣言できます。明示カーソルには名前があり、通常は複数行を戻す問合せ(SQL SELECT文)に関連付けられています。明示カーソルを宣言した後、カーソルをオープンし(OPEN文)、結果セットから行を1つずつフェッチし(FETCH文)、カーソルをクローズする(CLOSE文)必要があります。カーソルのクローズ後は、結果セットからレコードをフェッチしたり、カーソル属性の値を参照することはできません。

暗黙カーソル属性の値の構文は、SQL属性(SQL%FOUNDなど)です。 SQL属性は常に、最後に実行されたDMLまたはSELECT INTO文を参照します。

明示カーソル属性の値の構文は、直後に属性のあるcursor_name(c1%FOUNDなど)です。

表7-1に、カーソル属性および戻すことのできる値のリストを示します。(暗黙カーソルには、このマニュアルの範囲外の追加属性があります。)

表7-1 カーソル属性の値

属性 明示カーソルの値 暗黙カーソルの値

%FOUND

カーソルがオープンされていない場合、INVALID_CURSOR

カーソルはオープンされているが、フェッチが試みられていない場合、NULL

最後のフェッチが行を戻した場合、TRUE

最後のフェッチが行を戻さなかった場合、FALSE

DMLまたはSELECT INTO文が実行されていない場合、NULL

最後のDMLまたはSELECT INTO文が行を戻した場合、TRUE

最後のDMLまたはSELECT INTO文が行を戻さなかった場合、FALSE

%NOTFOUND

カーソルがオープンされていない場合、INVALID_CURSOR

カーソルはオープンされているが、フェッチが試みられていない場合、NULL

最後のフェッチが行を戻した場合、FALSE

最後のフェッチが行を戻さなかった場合、TRUE

DMLまたはSELECT INTO文が実行されていない場合、NULL

最後のDMLまたはSELECT INTO文が行を戻した場合、FALSE

最後のDMLまたはSELECT INTO文が行を戻さなかった場合、TRUE

%ROWCOUNT

カーソルがオープンされていない場合、INVALID_CURSOR。それ以外の場合は、0以上の数字。

DMLまたはSELECT INTO文が実行されていない場合、NULL。それ以外の場合は、0以上の数字。

%ISOPEN

カーソルがオープンされている場合、TRUE。オープンされていない場合、FALSE

常にFALSE



参照:

  • 「問合せについて」

  • 「データ操作言語(DML)文について」

  • SELECT INTO文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • アプリケーションでのカーソルの使用の詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。

  • PL/SQLでのカーソルの管理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


結果セット行を1つずつ取得するための明示カーソルの使用

次の手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。

明示カーソルを使用して結果セット行を1つずつ取得するには、次の手順を実行します。

  1. 宣言部分で、次の手順を実行します。

    1. カーソルを宣言します。

      CURSOR cursor_name IS query;
      

      明示カーソル宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    2. カーソルによって戻された行を格納するレコードを宣言します。

      record_name cursor_name%ROWTYPE;
      

      %ROWTYPE構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  2. 実行可能部分で、次の手順を実行します。

    1. カーソルをオープンします。

      OPEN cursor_name;
      

      OPEN文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    2. 次に、似た構文を持つLOOP文を使用して、カーソルから行(結果セットからの行)を1つずつフェッチします。

      LOOP
        FETCH cursor_name INTO record_name;
        EXIT WHEN cursor_name%NOTFOUND;
        -- Process row that is in record_name:
        statement;
        [ statement; ]...
      END LOOP;
      

      FETCH文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    3. カーソルをクローズします。

      CLOSE cursor_name;
      

      CLOSE文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

チュートリアル: 結果セット行を1つずつ取得するための明示カーソルの使用

このチュートリアルでは、EMP_EVAL.eval_departmentプロシージャを実装する方法を示します。これには、emp_cursor明示カーソルを使用します。

EMP_EVAL.eval_departmentプロシージャを実装するには、次の手順を実行します。

  1. EMP_EVALパッケージ仕様部で、eval_departmentプロシージャの宣言を太字で示されているように変更します。

    PROCEDURE eval_department(dept_id IN employees.department_id%TYPE);
    
  2. EMP_EVALパッケージ本体で、eval_departmentプロシージャの定義を太字で示されているように変更します。

    PROCEDURE eval_department (dept_id IN employees.department_id%TYPE)
    AS
      CURSOR emp_cursor IS
        SELECT * FROM EMPLOYEES
        WHERE DEPARTMENT_ID = dept_id;
    
      emp_record  EMPLOYEES%ROWTYPE;  -- for row returned by cursor
      all_evals   BOOLEAN;  -- true if all employees in dept need evaluations
      today       DATE;
    
    BEGIN
      today := SYSDATE;
    
      IF (EXTRACT(MONTH FROM today) < 6) THEN
        all_evals := FALSE; -- only new employees need evaluations
      ELSE
        all_evals := TRUE;  -- all employees need evaluations
      END IF;
    
      OPEN emp_cursor;
    
      DBMS_OUTPUT.PUT_LINE (
        'Determining evaluations necessary in department # ' ||
        dept_id );
    
      LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN emp_cursor%NOTFOUND;
    
        IF all_evals THEN
          add_eval(emp_record.employee_id, today);
        ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
          add_eval(emp_record.employee_id, today);
        END IF;
      END LOOP;
    
      DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
    
      CLOSE emp_cursor;
    END eval_department;
    

    (パッケージ本体を変更する手順の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)

  3. EMP_EVALパッケージ仕様部をコンパイルします。

  4. EMP_EVALパッケージ本体をコンパイルします。

カーソル変数について

カーソル変数はカーソルに似ていますが(「カーソルについて」を参照)、1つの問合せに限定されない点が異なります。カーソル変数を問合せに対してオープンし、結果セットを処理した後、カーソル変数を別の問合せのために使用できます。カーソル変数は、問合せ結果をサブプログラム間で受け渡すのに便利です。

カーソル変数を宣言するには、REF CURSOR型を宣言し、その型の変数を宣言します(このため、カーソル変数はREF CURSORと呼ばれることもあります)。REF CURSOR型には強弱があります。

強い REF CURSOR は、カーソル変数のRECORD型である戻り型を指定します。PL/SQLコンパイラでは、これらの強い型指定のカーソル変数を、戻り型と異なる行を戻す問合せに使用できません。強いREF CURSOR型は、弱い型よりもエラー発生の可能性が少なく、弱い型はより柔軟です。

弱い REF CURSOR は、戻り型を指定しません。PL/SQLコンパイラでは、弱い型指定のカーソル変数をすべての問合せに使用できます。弱いREF CURSOR型は置換可能なため、弱いREF CURSOR型を作成するかわりに、事前定義型の、弱いカーソル型SYS_REFCURSORを使用できます。

カーソル変数を宣言した後、(OPEN FOR文を使用して)特定の問合せに対して変数をオープンし、(FETCH文を使用して)結果セットから行を1つずつフェッチしてから、(CLOSE文を使用して)カーソルをクローズするか、または(OPEN FOR文を使用して)別の特定の問合せに対してオープンする必要があります。カーソル変数を別の問合せに対してオープンすると、前の問合せに対してはクローズされます。特定の問合せに対してカーソル変数をクローズした後は、その問合せの結果セットからレコードをフェッチしたり、カーソル属性の値を参照することはできません。


参照:

  • カーソル変数の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • カーソル変数宣言の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。


結果セット行を1つずつ取得するためのカーソル変数の使用

次の手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。

カーソル変数を使用して結果セット行を1つずつ取得するには、次の手順を実行します。

  1. 宣言部分で、次の手順を実行します。

    1. REF CURSOR型を宣言します。

      TYPE cursor_type IS REF CURSOR [ RETURN return_type ];
      

      REF CURSOR型宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    2. その型のカーソル変数を宣言します。

      cursor_variable cursor_type;
      

      カーソル変数宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    3. カーソルによって戻された行を格納するレコードを宣言します。

      record_name return_type;
      

      レコード宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  2. 実行可能部分で、次の手順を実行します。

    1. カーソル変数を特定の問合せに対してオープンします。

      OPEN cursor_variable FOR query;
      

      OPEN FOR文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    2. 次に、似た構文を持つLOOP文を使用して、カーソル変数から行(結果セットからの行)を1つずつフェッチします。

      LOOP
        FETCH cursor_variable INTO record_name;
        EXIT WHEN cursor_variable%NOTFOUND;
        -- Process row that is in record_name:
        statement;
        [ statement; ]...
      END LOOP;
      

      FETCH文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    3. カーソル変数をクローズします。

      CLOSE cursor_variable;
      

      または、カーソル変数を別の問合せに対してオープンすることで、現在の問合せに対してクローズすることもできます。

      CLOSE文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

チュートリアル: 結果セット行を1つずつ取得するためのカーソル変数の使用

このチュートリアルでは、明示カーソルのかわりにカーソル変数を使用するようにEMP_EVAL.eval_departmentプロシージャを変更し、複数部門の処理を可能にする方法を示します。変更には、カーソル変数を使用するプロシージャの追加が含まれます。

また、このチュートリアルは、EMP_EVAL.eval_departmentおよびEMP_EVAL.add_evalをより効率的にする方法も示しています。レコードの1つのフィールドをadd_evalに渡し、add_evalで3つの問合せを使用して同じレコードの他の3つのフィールドを抽出するのではなく、eval_departmentがレコード全体をadd_evalに渡し、add_evalでドット表記法を使用して他の3つのフィールドの値にアクセスします。

カーソル変数を使用するようにEMP_EVAL.eval_departmentプロシージャを変更するには、次の手順を実行します。

  1. EMP_EVALパッケージ仕様部で、太字で示されているように、プロシージャの宣言およびREF CURSOR型の定義を追加します。

    create or replace
    PACKAGE emp_eval AS
    
      PROCEDURE eval_department (dept_id IN employees.department_id%TYPE);
    
      PROCEDURE eval_everyone;
    
      FUNCTION calculate_score(eval_id IN scores.evaluation_id%TYPE 
                             , perf_id IN scores.performance_id%TYPE) 
                               RETURN NUMBER;
      TYPE SAL_INFO IS RECORD
          ( j_id jobs.job_id%type
          , sal_min jobs.min_salary%type
          , sal_max jobs.max_salary%type
          , salary employees.salary%type
          , sal_raise NUMBER(3,3));
          
    
      TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
    END emp_eval;
    
  2. EMP_EVALパッケージ本体で、太字で示されているように、eval_loop_controlプロシージャの前の宣言を追加し、add_evalプロシージャの宣言を変更します。

    create or replace
    PACKAGE BODY EMP_EVAL AS
    
      FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
        RETURN PLS_INTEGER;
    
      PROCEDURE salary_schedule(emp IN sal_info);
    
      PROCEDURE add_eval(emp_record IN EMPLOYEES%ROWTYPE, today IN DATE);
    
      PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type);
    ...
    

    (パッケージ本体を変更する手順の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)

  3. 部門に基づいた3つの異なる結果セットを取得し、eval_loop_controlプロシージャを起動するように、eval_departmentプロシージャを太字で示されているように変更します。

    PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS
      emp_cursor    emp_refcursor_type;
      current_dept  departments.department_id%TYPE;
    
    BEGIN
      current_dept := dept_id;
    
      FOR loop_c IN 1..3 LOOP
        OPEN emp_cursor FOR
          SELECT * 
          FROM employees
          WHERE current_dept = dept_id;
    
        DBMS_OUTPUT.PUT_LINE
          ('Determining necessary evaluations in department #' ||
           current_dept);
    
        eval_loop_control(emp_cursor);
    
        DBMS_OUTPUT.PUT_LINE
          ('Processed ' || emp_cursor%ROWCOUNT || ' records.');
    
        CLOSE emp_cursor;
        current_dept := current_dept + 10; 
      END LOOP;
    END eval_department;
    
  4. add_evalプロシージャを太字で示されているように変更します。

    PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE)
    AS
    -- (Delete local variables)
    BEGIN
      INSERT INTO EVALUATIONS (
        evaluation_id,
        employee_id,
        evaluation_date,
        job_id,
        manager_id,
        department_id,
        total_score
      )
      VALUES (
        evaluations_seq.NEXTVAL,   -- evaluation_id
        emp_record.employee_id,    -- employee_id
        today,                     -- evaluation_date
        emp_record.job_id,         -- job_id
        emp_record.manager_id,     -- manager_id
        emp_record.department_id,  -- department_id
        0                           -- total_score
    );
    END add_eval;
    
  5. END EMP_EVALの前に、結果セットから個々のレコードをフェッチして処理する、次のプロシージャを追加します。

    PROCEDURE eval_loop_control (emp_cursor IN emp_refcursor_type) AS
       emp_record      EMPLOYEES%ROWTYPE;
       all_evals       BOOLEAN;
       today           DATE;
    BEGIN
      today := SYSDATE;
    
      IF (EXTRACT(MONTH FROM today) < 6) THEN
        all_evals := FALSE;
      ELSE 
        all_evals := TRUE;
      END IF;
    
      LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN emp_cursor%NOTFOUND;
    
        IF all_evals THEN
          add_eval(emp_record, today);
        ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
          add_eval(emp_record, today);
        END IF;
      END LOOP;
    END eval_loop_control;
    
  6. END EMP_EVALの前に、企業のすべての従業員を含む結果セットを取得する次のプロシージャを追加します。

    PROCEDURE eval_everyone AS
      emp_cursor emp_refcursor_type;
    BEGIN
      OPEN emp_cursor FOR SELECT * FROM employees;
      DBMS_OUTPUT.PUT_LINE('Determining number of necessary evaluations.');
      eval_loop_control(emp_cursor);
      DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
      CLOSE emp_cursor;
    END eval_everyone;
    
  7. EMP_EVALパッケージ仕様部をコンパイルします。

  8. EMP_EVALパッケージ本体をコンパイルします。

連想配列の使用

連想配列は、コレクションの型です。

トピック:


参照:

コレクションの詳細は、次を参照してください。
  • 『Oracle Database概要』

  • 『Oracle Database PL/SQL言語リファレンス』


コレクションについて

コレクションは、1次元配列に似ていて、同じ型の要素を指定された順序で格納するPL/SQLの複合変数です。コレクションの内部コンポーネントは、要素と呼ばれます。各要素には、コレクション内での位置を識別する一意のサブスクリプトがあります。コレクション要素にアクセスするには、サブスクリプト表記法collection_name(element_subscript)を使用します。

コレクション要素は、スカラー変数のように扱うことができます。コレクション全体をサブプログラム・パラメータとして渡すこともできます(ただし、送受信されるサブプログラムがいずれもスタンドアロン・ストアド・サブプログラムではない場合)。

コレクション・メソッドは、コレクションに関する情報を戻す、またはコレクション上で動作する埋込みPL/SQLサブプログラムです。コレクション・メソッドを起動するには、ドット表記法collection_name.method_nameを使用します。たとえば、collection_name.COUNTはコレクションの要素の数を戻します。

PL/SQLには、次の3つの型のコレクションがあります。

  • 連想配列(以前の「PL/SQL表」または「索引付き表」)

  • ネストした表

  • 可変配列(VARRAY)

このマニュアルでは、連想配列のみを説明します。


参照:

  • PL/SQLコレクション型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • コレクション・メソッドの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


連想配列について

連想配列は、制限のない一連のキーと値のペアです。各キーは一意であり、対応する値を保持する要素のサブスクリプトとして機能します。そのため、配列内の位置がわからなくても、また配列を横断しなくても要素にアクセスできます。

キーのデータ型は、PLS_INTEGERまたはVARCHAR2(length)です。

データ型がPLS_INTEGERの場合、連想配列は整数で索引付けされ稠密です。つまり、要素間に差分が発生せず、最初から最後までの各要素が定義され、値があります(NULLの場合もあります)。

キーの型がVARCHAR2 (length)の場合、連想配列は文字列によって索引付けされ(length文字)、スパースです。つまり、要素間に差分のある場合があります。

稠密連想配列を横断するときは要素間の差分に注意する必要はありませんが、スパース連想配列を横断するときは注意する必要があります。

連想配列の要素に値を割り当てるには、代入演算子を使用できます。

array_name(key) := value

keyが配列内にない場合、代入文はkey-valueペアを配列に追加します。そうでない場合、文がarray_name(key)の値をvalueに変更します。

連想配列は、データの一時的な格納に便利です。連想配列では、表が必要とするディスク領域やネットワーク操作を使用しません。ただし、連想配列は一時的な格納を目的としているため、DML文で操作したり、SELECT INTO文を使用して値を変数に割り当てることはできません。

パッケージで連想配列を宣言し、値をパッケージ本体の変数に割り当てる場合、連想配列はデータベース・セッションが存続するかぎり存在します。そうでない場合は、宣言をしたサブプログラムが存続するかぎり存在します。


参照:

連想配列の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

連想配列の宣言

連想配列を宣言するには、連想配列型を宣言し、その型の変数を宣言します。次に、最も単純な構文を示します。

TYPE array_type IS TABLE OF element_type INDEX BY key_type;

array_name  array_type;

効率的に連想配列を宣言するには、次の手順でカーソルを使用します。この手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。

カーソルを使用して連想配列を宣言するには、次の手順を実行します。

  1. 宣言部分で、次の手順を実行します。

    1. カーソルを宣言します。

      CURSOR cursor_name IS query;
      

      明示カーソル宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    2. 連想配列型を宣言します。

      TYPE array_type IS TABLE OF cursor_name%ROWTYPE
        INDEX BY { PLS_INTEGER | VARCHAR2 length }
      

      連想配列型宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    3. その型の連想配列変数を宣言します。

      array_name  array_type;
      

      変数宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

例7-9では、前述の手順を使用して2つの連想配列employees_jobsおよびjobs_を宣言し、カーソルを使用せずに3つ目の連想配列job_titles_typeを宣言します。初めの2つの配列は整数によって索引付けされ、3つ目の配列は文字列によって索引付けされます。


注意:

employees_jobs_cursorの宣言のORDER BY句が、連想配列employee_jobsの要素の格納順序を決定します。

例7-9 連想配列の宣言

DECLARE
  -- Declare cursor:

  CURSOR employees_jobs_cursor IS
    SELECT FIRST_NAME, LAST_NAME, JOB_ID
    FROM EMPLOYEES
    ORDER BY JOB_ID, LAST_NAME, FIRST_NAME;

  -- Declare associative array type:

  TYPE employees_jobs_type IS TABLE OF employees_jobs_cursor%ROWTYPE
    INDEX BY PLS_INTEGER;

  -- Declare associative array:

  employees_jobs  employees_jobs_type;

  -- Use same procedure to declare another associative array:

  CURSOR jobs_cursor IS
    SELECT JOB_ID, JOB_TITLE
    FROM JOBS;

  TYPE jobs_type IS TABLE OF jobs_cursor%ROWTYPE
    INDEX BY PLS_INTEGER;

  jobs_  jobs_type;

-- Declare associative array without using cursor:

  TYPE job_titles_type IS TABLE OF JOBS.JOB_TITLE%TYPE
    INDEX BY JOBS.JOB_ID%TYPE;  -- jobs.job_id%type is varchar2(10)

  job_titles  job_titles_type;

BEGIN
  NULL;
END;
/

参照:

  • 「カーソルについて」

  • 連想配列宣言の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。


連想配列の移入

最も効率的に稠密連想配列を移入するには、次の手順で、カーソルおよびBULK COLLECT INTO句のあるFETCH文を使用します。この手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。

次の手順を使用してスパース連想配列を移入することはできません。かわりに、繰り返し文の中の代入文を使用する必要があります。繰り返し文の詳細は、「プログラム・フローの制御」を参照してください。

カーソルを使用して整数に索引付けされた連想配列を移入するには、次の手順を実行します。

  1. 連想配列を宣言していない場合は、「連想配列の宣言」の手順に従って、カーソルを使用して連想配列を宣言します。

  2. 連想配列を宣言したPL/SQLユニットの実行可能部分で、次の手順を実行します。

    1. カーソルをオープンします。

      OPEN cursor_name;
      

      OPEN文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    2. BULK COLLECT INTO句のあるFETCH文を使用して、一度にすべての行をカーソルから連想配列変数にフェッチします。

      FETCH cursor_name BULK COLLECT INTO aa_variable;
      

      FETCH文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

    3. カーソルをクローズします。

      CLOSE cursor_name;
      

      CLOSE文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

例7-10では、前述の手順を使用して、整数によって索引付けされた連想配列employees_jobsおよびjobs_を移入します。次に、FOR LOOP文にある代入文を使用して、文字列によって索引付けされた連想配列job_titles_typeを移入します。

例7-10 連想配列の移入

-- Declarative part from Example 7-9 goes here.
BEGIN
  -- Populate associative arrays indexed by integer:

  OPEN employees_jobs_cursor;
  FETCH employees_jobs_cursor BULK COLLECT INTO employees_jobs;
  CLOSE employees_jobs_cursor;

  OPEN jobs_cursor;
  FETCH jobs_cursor BULK COLLECT INTO jobs_;
  CLOSE jobs_cursor;

  -- Populate associative array indexed by string:

  FOR i IN 1..jobs_.COUNT() LOOP
    job_titles(jobs_(i).job_id) := jobs_(i).job_title;
  END LOOP;
END;
/

稠密連想配列の横断

稠密な連想配列(整数による索引付け)には、要素間の差異がなく、最初と最後の要素の間のすべての要素が定義されていて、それぞれに値があります(値はNULLの場合もあります)。例7-11のように、FOR LOOP文を使用して稠密な配列を横断できます。

例7-11FOR LOOP文は、例7-10の実行可能部分に挿入すると、employees_jobs配列を移入するコードの後に、employees_jobs配列の要素を、格納された順序で出力します。格納順序は、employees_jobsを宣言するために使用されたemployees_jobs_cursor宣言のORDER BY句によって決定されます(例7-9を参照)。

FOR LOOPFOR LOOP

FOR LOOP文の上限employees_jobs.COUNTにより、配列内の要素の数を戻すコレクション・メソッドが起動されます。COUNTの詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。

例7-11 稠密連想配列の横断

-- Code that populates employees_jobs must precede this code:

FOR i IN 1..employees_jobs.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(
    RPAD(employees_jobs(i).first_name, 23) ||
    RPAD(employees_jobs(i).last_name,  28) ||     employees_jobs(i).job_id);
  END LOOP;

結果:

William                Gietz                       AC_ACCOUNT
Shelley                Higgins                     AC_MGR
Jennifer               Whalen                      AD_ASST
Steven                 King                        AD_PRES
Lex                    De Haan                     AD_VP
Neena                  Kochhar                     AD_VP
John                   Chen                        FI_ACCOUNT
...
Jose Manuel            Urman                       FI_ACCOUNT
Nancy                  Greenberg                   FI_MGR
Susan                  Mavris                      HR_REP
David                  Austin                      IT_PROG
...
Valli                  Pataballa                   IT_PROG
Michael                Hartstein                   MK_MAN
Pat                    Fay                         MK_REP
Hermann                Baer                        PR_REP
Shelli                 Baida                       PU_CLERK
...
Sigal                  Tobias                      PU_CLERK
Den                    Raphaely                    PU_MAN
Gerald                 Cambrault                   SA_MAN
...
Eleni                  Zlotkey                     SA_MAN
Ellen                  Abel                        SA_REP
...
Clara                  Vishney                     SA_REP
Sarah                  Bell                        SH_CLERK
...
Peter                  Vargas                      ST_CLERK
Adam                   Fripp                       ST_MAN
...
Matthew                Weiss                       ST_MAN

スパース連想配列の横断

スパース連想配列(文字列によって索引付けされたもの)は、要素間に差分がある場合があります。例7-12のように、WHILE LOOP文を使用して横断できます。

例7-12でコードを実行し、job_titles配列の要素を出力するには、次の手順を実行します。

  1. 例7-9の宣言部分の終わりに、次の変数宣言を挿入します。

    i jobs_.job_id%TYPE;
    
  2. 例7-10の実行可能部分で、job_titles配列を移入するコードの後に、例7-12のコードを挿入します。

例7-12には、job_titles.FIRSTおよびjob_titles.NEXT(i)という2つのコレクション・メソッドの起動が含まれます。job_titles.FIRSTでは、job_titlesの最初の要素が返され、job_titles.NEXT(i)では、iに続くサブスクリプトが返されます。FIRSTの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。NEXTの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

例7-12 スパース連想配列の横断

/* Declare this variable in declarative part:

   i jobs_.job_id%TYPE;

   Add this code to the executable part,
   after code that populates job_titles:
*/

i := job_titles.FIRST;

WHILE i IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(RPAD(i, 12) || job_titles(i));
  i := job_titles.NEXT(i);
END LOOP;

結果:

AC_ACCOUNT  Public Accountant
AC_MGR      Accounting Manager
AD_ASST     Administration Assistant
AD_PRES     President
AD_VP       Administration Vice President
FI_ACCOUNT  Accountant
FI_MGR      Finance Manager
HR_REP      Human Resources Representative
IT_PROG     Programmer
MK_MAN      Marketing Manager
MK_REP      Marketing Representative
PR_REP      Public Relations Representative
PU_CLERK    Purchasing Clerk
PU_MAN      Purchasing Manager
SA_MAN      Sales Manager
SA_REP      Sales Representative
SH_CLERK    Shipping Clerk
ST_CLERK    Stock Clerk
ST_MAN      Stock Manager

例外処理(ランタイム・エラー)

トピック:


参照:

PL/SQLエラーの処理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

例外および例外ハンドラについて

PL/SQLコード内でランタイム・エラーが発生すると、例外が発生します。例外が発生したサブプログラム(またはブロック)に例外処理部分がある場合は制御が転送され、そうでない場合は実行が停止します。

ランタイム・エラーは、設計ミス、コーディングの失敗、ハードウェア障害およびその他の多くの要因によって生じる可能性があります。すべてのエラーを予測することはできないため、サブプログラムに例外処理部分を含めることをお薦めします(例外処理部分を含める場所については、「サブプログラム構造について」を参照)。

Oracle Database XEには多くの事前定義済の例外があり、プログラムがデータベース・ルールに違反したり、システム依存の限度を超えた場合に自動的に発生します。たとえば、SELECT INTO文で行が返されない場合、事前定義済の例外NO_DATA_FOUNDが発生します。PL/SQLの事前定義済の例外の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

PL/SQLでは、独自の例外を定義(宣言)できます。例外宣言の構文は、次のとおりです。

exception_name EXCEPTION;

事前定義済の例外と異なり、ユーザー定義の例外RAISE文またはDBMS_STANDARD.RAISE_APPLICATION_ERRORプロシージャを使用して、明示的に発生させる必要があります。次に例を示します。

IF condition THEN RAISE exception_name;

DBMS_STANDARD.RAISE_APPLICATION_ERRORプロシージャの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

サブプログラムの例外処理部分には、1つ以上の例外ハンドラが含まれています。 例外ハンドラの構文は、次のとおりです。

WHEN { exception_name [ OR exception_name ]... | OTHERS } THEN
  statement; [ statement; ]...

WHEN OTHERS例外ハンドラは、予期しないランタイム・エラーを処理します。これは、最後に使用する必要があります。次に例を示します。

EXCEPTION
  WHEN exception_1 THEN
    statement; [ statement; ]...
  WHEN exception_2 OR exception_3 THEN
    statement; [ statement; ]...
  WHEN OTHERS THEN
    statement; [ statement; ]...
END;

WHEN OTHERS例外ハンドラのかわりに、EXCEPTION_INITプラグマを使用することもできます。このプラグマによって、ユーザー定義の例外の名前がOracle Databaseのエラー番号に関連付けられます。


参照:

  • 例外宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • 例外ハンドラの構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • EXCEPTION_INITプラグマの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


事前定義済の例外の処理

例7-13では、EMP_EVAL.eval_departmentプロシージャを変更して事前定義済の例外NO_DATA_FOUNDを処理する方法を太字で示しています。この変更を行い、変更したプロシージャをコンパイルします。(パッケージ本体を変更する方法の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)

例7-13 事前定義済の例外NO_DATA_FOUNDの処理

PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS
  emp_cursor    emp_refcursor_type;
  current_dept  departments.department_id%TYPE;

BEGIN
  current_dept := dept_id;

  FOR loop_c IN 1..3 LOOP
    OPEN emp_cursor FOR
      SELECT * 
      FROM employees
      WHERE current_dept = dept_id;

    DBMS_OUTPUT.PUT_LINE
      ('Determining necessary evaluations in department #' ||
       current_dept);

    eval_loop_control(emp_cursor);

    DBMS_OUTPUT.PUT_LINE
      ('Processed ' || emp_cursor%ROWCOUNT || ' records.');

    CLOSE emp_cursor;
    current_dept := current_dept + 10; 
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');
END eval_department;

参照:

  • 事前定義済の例外の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • 発生した例外の処理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。


ユーザー定義の例外の宣言および処理

例7-14では、EMP_EVAL.calculate_scoreファンクションを変更して2つのユーザー定義の例外wrong_weightおよびwrong_scoreを宣言および処理する方法を太字で示しています。この変更を行い、変更したファンクションをコンパイルします。(パッケージ本体を変更する方法の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)

例7-14 ユーザー定義の例外の処理

FUNCTION calculate_score ( evaluation_id IN scores.evaluation_id%TYPE
                         , performance_id IN scores.performance_id%TYPE )
                         RETURN NUMBER AS

  weight_wrong  EXCEPTION;
  score_wrong   EXCEPTION;
  n_score       scores.score%TYPE;
  n_weight      performance_parts.weight%TYPE;
  running_total NUMBER := 0;
  max_score     CONSTANT scores.score%TYPE := 9;
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;
BEGIN
  SELECT s.score INTO n_score
  FROM SCORES s
  WHERE evaluation_id = s.evaluation_id 
  AND performance_id = s.performance_id;

  SELECT p.weight INTO n_weight
  FROM PERFORMANCE_PARTS p
  WHERE performance_id = p.performance_id;

  BEGIN
    IF (n_weight > max_weight) OR (n_weight < 0) THEN
      RAISE weight_wrong;
    END IF;
  END;

  BEGIN
    IF (n_score > max_score) OR (n_score < 0) THEN
      RAISE score_wrong;
    END IF;
  END;

  running_total := n_score * n_weight;
  RETURN running_total;

EXCEPTION
  WHEN weight_wrong THEN
    DBMS_OUTPUT.PUT_LINE(
      'The weight of a score must be between 0 and ' || max_weight);
    RETURN -1;
  WHEN score_wrong THEN
    DBMS_OUTPUT.PUT_LINE(
      'The score must be between 0 and ' || max_score);
    RETURN -1;
END calculate_score;

参照:

  • ユーザー定義の例外の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

  • 発生した例外の処理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。