PL/SQL

PL/SQLソースプログラムの基本単位はブロックであり、関連する宣言とステートメントをグループ化します。PL/SQLブロックは、キーワードDECLARE、BEGIN、EXCEPTION、およびENDによって定義されます。これらのキーワードは、ブロックを宣言部、実行部、および例外処理部に分割します。宣言部はオプションであり、定数と変数を定義および初期化するために使用できます。変数が初期化されていない場合、デフォルト値はNULLになります。オプションの例外処理部は、実行時エラーを処理するために使用されます。実行部のみが必須です。ブロックにはラベルを付けることができます。^{[ 5 ]}

例えば：

<<label>> -- これはオプションですDECLARE -- このセクションはオプションですnumber1 NUMBER ( 2 ); number2 number1 %TYPE := 17 ; -- 値のデフォルト値text1 VARCHAR2 ( 12 ) := ' Hello world ' ; text2 DATE := SYSDATE ; -- 現在の日時BEGIN -- このセクションは必須です。少なくとも 1 つの実行可能なステートメントを含める必要がありますSELECT street_number INTO number1 FROM address WHERE name = 'INU' ; EXCEPTION -- このセクションはオプションですWHEN OTHERS THEN DBMS_OUTPUT . PUT_LINE ( 'エラー コードは ' || TO_CHAR ( sqlcode )); DBMS_OUTPUT . PUT_LINE ( 'エラー メッセージは ' || sqlerrm ); END ;

この記号は、値を変数に格納するための代入演算子:=として機能します。

ブロックはネストできます。つまり、ブロックは実行可能なステートメントであるため、実行可能なステートメントが許可されている場所であれば、他のブロック内にも配置できます。ブロックは、対話型ツール（SQL*Plusなど）に送信したり、OracleプリコンパイラまたはOCIプログラムに埋め込んだりできます。対話型ツールまたはプログラムは、ブロックを一度実行します。ブロックはデータベースに保存されないため、ラベルが付いていても匿名ブロックと呼ばれます。

PL/SQL関数の目的は、一般的に単一の値を計算して返すことです。返される値は、単一のスカラー値（数値、日付、文字列など）または単一のコレクション（ネストされたテーブルや配列など）である場合があります。 ユーザー定義関数は、 Oracle Corporationが提供する組み込み関数を補完します。^{[ 6 ]}

PL/SQL関数の形式は次のとおりです。

CREATE OR REPLACE FUNCTION < function_name > [(入力/出力変数宣言)] RETURN return_type [ AUTHID < CURRENT_USER | DEFINER > ] < IS | AS > -- 見出し部分金額数値; -- 宣言ブロックBEGIN -- 実行部分<戻り値ステートメントを含むPL / SQLブロック> RETURN < return_value > ; [ Exception none ] RETURN < return_value > ; END ;

パイプライン化されたテーブル関数はコレクション^{[ 7 ]}を返し 、次の形式をとります。

CREATE OR REPLACE FUNCTION < function_name > [(入力/出力変数宣言)] RETURN return_type [ AUTHID < CURRENT_USER | DEFINER > ] [ < AGGREGATE | PIPELINED > ] < IS | USING > [宣言ブロック] BEGIN <戻り値ステートメントを含むPL / SQLブロック> PIPE ROW <戻り値タイプ> ; RETURN ; [例外例外ブロック] PIPE ROW <戻り値タイプ> ; RETURN ; END ;

関数は、デフォルトのIN型パラメータのみを使用する必要があります。関数から出力される値は、関数が返す値のみであるべきです。

プロシージャは、繰り返し呼び出すことができる名前付きプログラム単位であるという点で関数に似ています。主な違いは、関数はSQL文で使用できるのに対し、プロシージャは使用できないことです。もう1つの違いは、プロシージャは複数の値を返すことができるのに対し、関数は単一の値しか返さないことです。^{[ 8 ]}

プロシージャは、プロシージャ名とオプションでプロシージャパラメータリストを格納する必須のヘッダー部分から始まります。次に、PL/SQLの匿名ブロックと同様に、宣言部分、実行部分、例外処理部分が続きます。簡単なプロシージャは次のようになります。

CREATE PROCEDURE create_email_address ( -- プロシージャのヘッダー部分の開始name1 VARCHAR2 , name2 VARCHAR2 , company VARCHAR2 , email OUT VARCHAR2 ) -- プロシージャのヘッダー部分の終了AS -- 宣言部分の開始 (オプション) error_message VARCHAR2 ( 30 ) := 'メールアドレスが長すぎます。' ; BEGIN -- 実行可能部分の開始 (必須) email := name1 || '.' || name2 || '@' || company ; EXCEPTION -- 例外処理部分の開始 (オプション) WHEN VALUE_ERROR THEN DBMS_OUTPUT . PUT_LINE ( error_message ); END create_email_address ;

上記の例はスタンドアロンプ​​ロシージャを示しています。このタイプのプロシージャは、CREATE PROCEDURE文を使用してデータベーススキーマに作成および格納されます。プロシージャはPL/SQLパッケージ内に作成することもできます。これはパッケージプロシージャと呼ばれます。PL/SQL匿名ブロック内に作成されたプロシージャは、ネストされたプロシージャと呼ばれます。データベースに格納されるスタンドアロンプ​​ロシージャまたはパッケージプロシージャは、「ストアドプロシージャ」と呼ばれます。

手順には、IN、OUT、IN OUTの3種類のパラメータがあります。

1. INパラメータは入力専用です。INパラメータは参照渡しされますが、非アクティブなプログラムによって変更される可能性があります。
2. OUTパラメータは初期状態ではNULLです。プログラムはパラメータに値を割り当て、その値を呼び出し元のプログラムに返します。
3. IN OUT パラメータには初期値がある場合とない場合があります。その初期値は、呼び出されたプログラムによって変更される場合とされない場合があります。パラメータに加えられた変更は、デフォルトではコピーによって呼び出し元のプログラムに返されますが、NO-COPY ヒントを使用すると参照渡しすることも可能です。

PL/SQLはOracleデータベースの標準ext-procプロセスを介して外部プロシージャもサポートしています。^{[ 9 ]}

パッケージは、概念的にリンクされた関数、プロシージャ、変数、PL/SQL テーブルおよびレコード TYPE ステートメント、定数、カーソルなどのグループです。パッケージを使用すると、コードの再利用が促進されます。パッケージは、パッケージ仕様とオプションのパッケージ本体で構成されます。仕様はアプリケーションへのインターフェースであり、使用可能な型、変数、定数、例外、カーソル、およびサブルーチンを宣言します。本体はカーソルとサブルーチンを完全に定義し、仕様を実装します。パッケージの 2 つの利点は次のとおりです。^{[ 10 ]}

1. モジュール型アプローチ、ビジネスロジックのカプセル化／隠蔽、セキュリティ、パフォーマンス向上、再利用性。関数オーバーロードやカプセル化といったオブジェクト指向プログラミング機能をサポートしています。
2. パッケージ変数を使用すると、セッションレベル（スコープ付き）の変数を宣言できます。これは、パッケージ仕様で宣言された変数がセッションスコープを持つためです。

データベーストリガーは、指定されたイベントが発生するたびにOracle Databaseが自動的に呼び出すストアドプロシージャのようなものです。データベースに格納される名前付きPL/SQLユニットであり、繰り返し呼び出すことができます。ストアドプロシージャとは異なり、トリガーは有効化と無効化が可能ですが、明示的に呼び出すことはできません。トリガーが有効になっている間は、トリガーイベントが発生するたびにデータベースが自動的にトリガーを呼び出します（つまり、トリガーが起動します）。トリガーが無効になっている間は、トリガーは起動しません。

トリガーは、CREATE TRIGGER ステートメントを使用して作成します。トリガーとなるイベントは、トリガーステートメントと、それらが作用する対象項目によって指定します。トリガーは、対象項目（テーブル、ビュー、スキーマ、またはデータベース）に対して作成または定義されます。また、トリガーがトリガーステートメントの実行前か実行後か、およびトリガーステートメントが影響を与える各行に対してトリガーが実行されるかどうかを決定するタイミングポイントも指定します。

トリガーがテーブルまたはビュー上に作成される場合、トリガーイベントはDMLステートメントで構成され、そのトリガーはDMLトリガーと呼ばれます。トリガーがスキーマまたはデータベース上に作成される場合、トリガーイベントはDDLステートメントまたはデータベース操作ステートメントで構成され、そのトリガーはシステムトリガーと呼ばれます。

INSTEAD OF トリガーとは、ビューに対して作成された DML トリガー、または CREATE ステートメントに対して定義されたシステム トリガーのいずれかです。データベースは、トリガーとなるステートメントを実行する代わりに、INSTEAD OF トリガーを起動します。

変数名番号([ P , S ]) := 0 ;

数値変数を定義するには、プログラマーは変数名の定義に変数型「NUMBER」を追加します。精度（P）と小数点以下桁数（S）を指定するには、これらを丸括弧で囲み、カンマで区切って追加します。（ここでいう「精度」とは変数が保持できる桁数を指し、「小数点以下桁数」とは小数点以下の桁数を指します。）

数値変数に使用できるその他のデータ型としては、binary_float、binary_double、dec、decimal、double precision、float、integer、int、numeric、real、small-int、binary_integer などがあります。

variable_name varchar2 ( 20 ) := 'Text' ;-- 例: address varchar2 ( 20 ) := 'lake view road' ;

文字変数を定義する場合、プログラマーは通常、変数名の定義に変数型「VARCHAR2」を追加します。その後に括弧で囲んで、変数に格納できる最大文字数を指定します。

文字変数に使用できるその他のデータ型には、varchar、char、long、raw、long raw、nchar、nchar2、clob、blob、bfileなどがあります。

variable_name date := to_date ( '01-01-2005 14:20:23' , 'DD-MM-YYYY hh24:mi:ss' );

日付変数には日付と時刻を含めることができます。時刻を省略することはできますが、時刻のみを含む変数を定義する方法はありません。DATETIME 型はありません。TIME 型はありますが、ミリ秒またはナノ秒までの細かいタイムスタンプを格納できる TIMESTAMP 型はありません。このTO_DATE関数は、文字列を日付値に変換するために使用できます。この関数は、2 番目の引用符付き文字列を定義として使用して、最初の引用符付き文字列を日付に変換します。例:

to_date ( '31-12-2004' , 'dd-mm-yyyy' )

または

to_date ( '31-Dec-2004' , 'dd-mon-yyyy' , 'NLS_DATE_LANGUAGE = American' )

日付を文字列に変換するには、関数を使用しますTO_CHAR (date_string, format_string)。

PL/SQLはANSI日付および間隔リテラルの使用もサポートしています。^{[ 11 ]}次の句は18か月の範囲を指定します。

WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH AND DATE '2004-12-30'

例外（コード実行中のエラー）には、ユーザー定義の例外と事前定義の例外の2種類があります。

ユーザー定義例外は、プログラマが通常の実行を継続することが不可能だと判断した状況で、常に明示的に、RAISEまたはコマンドを使用して発生します。コマンドの構文は次のとおりです。RAISE_APPLICATION_ERRORRAISE

例外名をRAISEする;

Oracle Corporationは、、などのいくつかの例外を事前に定義しています。各例外には、SQLエラー番号とSQLエラーメッセージが関連付けられています。プログラマーは、および関数 を使用してこれらにアクセスできます。NO_DATA_FOUNDTOO_MANY_ROWSSQLCODESQLERRM

変数名テーブル名.列名%型;

この構文は、参照されるテーブル上の参照される列の型の変数を定義します。

プログラマーは、以下の構文でユーザー定義データ型を指定します。

type data_typeはrecord ( field_1 type_1 := xyz , field_2 type_2 := xyz , ... , field_n type_n := xyz );

例えば：

declare type t_address is record ( name address . name %type , street address . street %type , street_number address . street_number %type , postcode address . postcode %type ); v_address t_address ; begin select name , street , street_number , postcode into v_address from address where rownum = 1 ; end ;

このサンプルプログラムは、 t_addressという独自のデータ型を定義しており、 name、street、street_number、postcodeというフィールドが含まれています。

この例によれば、データベースからプログラムのフィールドにデータをコピーすることができる。

このデータ型を使用して、プログラマーはv_addressという変数を定義し、ADDRESSテーブルからデータをロードしました。

プログラマーは、ドット表記法を用いて、このような構造内の個々の属性にアクセスできます。

v_address.street := 'ハイストリート';

インデックス付きテーブルでは、配列を数値または文字列でインデックス付けできます。これは、キーと値のペアで構成されるJavaのマップに相当します。次元は1つだけで、制限はありません。

ネストされたテーブルでは、プログラマーはネストされている内容を理解する必要があります。ここでは、複数のコンポーネントで構成される新しい型が作成されます。その型を使用してテーブルの列を作成することができ、その列の中にコンポーネントがネストされます。

可変サイズ配列（Varrays）を使う場合、「可変サイズ配列」という表現の「可変」という言葉は、皆さんが想像するような配列のサイズを指すものではないことを理解しておく必要があります。配列の宣言時に指定されるサイズは実際には固定されています。配列内の要素数は、宣言されたサイズまで可変です。つまり、可変サイズ配列は、実際にはそれほどサイズが可変ではないと言えるでしょう。

<<parent_loop>>ループ文<<child_loop>>ループステートメントexit parent_loop when < condition > ; -- 両方のループを終了exit when < condition > ; -- 制御を parent_loop に戻すend loop child_loop ; if < condition > then continue ; -- 次のイテレーションに進むend if ;<条件>が満たされたら終了する; END LOOP parent_loop ;

^{[ 13 ]}

EXITループは、キーワードを使用するか、例外を発生させることによって終了できます。

DECLARE var NUMBER ; BEGIN /* 注: PL/SQL の for ループ変数は新規宣言であり、スコープはループ内のみです */ FOR var IN 0 .. 10 LOOP DBMS_OUTPUT . PUT_LINE ( var ); END LOOP ;IF var IS NULL THEN DBMS_OUTPUT . PUT_LINE ( 'var is null' ); ELSE DBMS_OUTPUT . PUT_LINE ( 'var is not null' ); END IF ; END ;

出力：

0 1 2 3 4 5 6 7 8 9 10 var は null です

FOR RecordIndex IN ( SELECT person_code FROM people_table ) LOOP DBMS_OUTPUT . PUT_LINE ( RecordIndex . person_code ); END LOOP ;

カーソルforループは、自動的にカーソルを開き、データを読み込み、カーソルを閉じます。

代替案として、PL/SQLプログラマはカーソルのSELECT文を事前に定義しておくことで、（例えば）再利用を可能にしたり、コードをより理解しやすくしたりすることができます（特に、長くて複雑なクエリの場合に役立ちます）。

DECLARE CURSOR cursor_person IS SELECT person_code FROM people_table ; BEGIN FOR RecordIndex IN cursor_person LOOP DBMS_OUTPUT . PUT_LINE ( recordIndex . person_code ); END LOOP ; END ;

FORループ内のperson_codeの概念は、ドット表記（"."）で表されます。

RecordIndex.person_code​​