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

ストアドプログラムでは、システム変数とユーザー定義変数を使用できます。ストアドプログラムは 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