ストアドプログラムの変数

ストアドプログラムでは、システム変数とユーザー定義変数を使用できます。ストアドプログラムは DECLARE を使用してローカル変数を定義でき、ストアドプログラム（プロシージャと関数）はパラメータを宣言し、プログラムとその呼び出し元との間で値を渡すことができます。

ローカル変数の定義

DECLARE ステートメントを使用してローカル変数を定義します。ローカル変数は宣言時にデフォルト値を指定するか、SET ステートメントを使用して代入することができます。

ストアドプログラム内のローカル変数を定義する際、DECLARE ステートメントは DEFAULT 句を使用して変数にデフォルト値を提供します。このデフォルト値は式として指定でき、必ずしも定数である必要はありません。DEFAULT 句が欠けている場合、初期値は NULL です。構文は以下のとおりです：

DECLARE var_name [, var_name] ... data_type [DEFAULT value]

ここで、data_type はSQLデータ型をサポートします。さらに、PLは以下のデータ型とも互換性があります：

• INT2（内部では SMALLINT に変換されます）

• INT4（内部では INT に変換されます）

• LONG（内部では MEDIUMTEXT に変換されます）

DEFAULT 値をストアドプロシージャや関数のパラメータ、ローカル変数に代入することは許可されておらず（例えば、SET var_name=DEFAULT ステートメントの使用）、これは構文エラーを引き起こします。

SELECT INTO またはカーソルを開く FETCH INTO ステートメントを使用すると、クエリ結果をローカル変数に取得できます。

変数宣言は、カーソルや例外ハンドラーの宣言より前に記述する必要があります。ローカル変数名は大文字小文字を区別しません。許可される文字と参照規則は他の識別子と同じです。

ローカル変数の作用域と解決

ローカル変数の作用域と、不明な名前をどのように解決するかについて説明します。

ローカル変数の作用域は、宣言された BEGIN END ブロック内です。この変数は、宣言ブロック内のネストされたブロックで参照できますが、同名の変数を持つブロックは含まれません。

ローカル変数はストアドプログラムの実行期間中のみ有効であるため、ストアドプログラム内で作成されたプリペアドステートメントではこれらのローカル変数を参照することはできません。プリペアドステートメントの作用域は現在のセッションであり、ストアドプログラムではないため、プログラム終了後にこのステートメントを実行しても、変数は作用域内に存在しません。例えば、SELECT ... INTO local_var はプリペアドステートメントとして使用できません。この制限はストアドプロシージャや関数のパラメータにも適用されます。

以下の例では、データベースは SELECT ステートメント内の vname を、vname 列への参照ではなく、vname 変数への参照として解決します。したがって、ストアドプロシージャ proc1() を呼び出すと、tbl1.vname の値に関係なく、newname 変数は "OceanBase" を返します。

obclient> DELIMITER //

obclient> CREATE PROCEDURE proc1 (x VARCHAR(10))
    BEGIN
      DECLARE vname VARCHAR(10) DEFAULT 'OceanBase';
      DECLARE newname VARCHAR(10);
      DECLARE xid INT;

      SELECT vname, id INTO newname, xid
        FROM tbl1 WHERE vname = vname;
      SELECT newname;
    END //
Query OK, 0 rows affected

同様に、以下の例のストアドプロシージャ内のカーソル定義に含まれる SELECT ステートメントは vname を参照しています。データベースはこれを列参照ではなく、その名前が対応する変数への参照として解釈します。

obclient> CREATE PROCEDURE proc2 (x VARCHAR(10))
    BEGIN
      DECLARE vname VARCHAR(10) DEFAULT 'OceanBase';
      DECLARE newname VARCHAR(10);
      DECLARE xid INT;
      DECLARE done TINYINT DEFAULT 1;
      DECLARE curdo CURSOR FOR SELECT vname, id FROM tbl1;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;

      OPEN curdo;
      readlable1: LOOP
        FETCH FROM curdo INTO newname, xid;
        IF done THEN LEAVE readlable1; END IF;
        SELECT newname;
      END LOOP;
      CLOSE curdo;
    END //
Query OK, 0 rows affected