SQL ストアドプロシージャを操作する

ストアドプロシージャは、他のクエリまたはストアドプロシージャから呼び出すことができるステートメントの集合です。プロシージャは、入力引数を受け取り、出力として値を返すことができます。プロシージャに名前を付けて、BigQuery データセットに保存します。ストアドプロシージャを使用すると、複数のユーザーが複数のデータセットのデータにアクセスしたり、データを変更することができます。また、マルチステートメントクエリを含めることもできます。

一部のストアドプロシージャは BigQuery に組み込まれているため、作成する必要はありません。これらをシステムプロシージャと呼びます。詳細については、システムプロシージャのリファレンスをご覧ください。

ストアドプロシージャは手続き型言語ステートメントをサポートしているため、変数の定義や制御フローの実装などを行うことができます。手続き型言語ステートメントの詳細については、手続き型言語のリファレンスをご覧ください。

ストアドプロシージャを作成する

プロシージャを作成するには、CREATE PROCEDURE ステートメントを使用します。

次の概念例では、procedure_name がプロシージャを表しています。プロシージャの本文は BEGIN ステートメントと END ステートメントの間にあります。

CREATE PROCEDURE dataset_name.procedure_name()
BEGIN
-- statements here
END

次の例は、マルチステートメントクエリを含むプロシージャを示しています。このマルチステートメントクエリでは、変数を設定し、INSERT ステートメントを実行して、書式設定されたテキスト文字列として結果を表示します。

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

上記の例では、プロシージャの名前は mydataset.create_customer で、プロシージャの本文は BEGIN と END ステートメントの間にあります。

プロシージャを呼び出すには、CALL ステートメントを使用します。

CALL mydataset.create_customer();

入力パラメータを使用して値を渡す

プロシージャには入力パラメータを含めることができます。入力パラメータはプロシージャの入力には使用できますが、出力には使用できません。

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

出力パラメータを使用して値を渡す

プロシージャには出力パラメータを含めることができます。出力パラメータは、プロシージャから値を返しますが、プロシージャの入力はできません。出力パラメータを作成するには、パラメータの名前の前に OUT キーワードを使用します。

たとえば、このバージョンのプロシージャでは、id パラメータを使用して新規顧客 ID を返します。

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

このプロシージャを呼び出すには、変数を使用して出力値を取得する必要があります。

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM mydataset.customers
WHERE customer_id = id;

入出力パラメータを使用して値を渡す

プロシージャには、入出力パラメータを含めることもできます。入出力パラメータは、プロシージャから値を返し、プロシージャの入力も受け入れます。入出力パラメータを作成するには、パラメータの名前の前に INOUT キーワードを使用します。詳細については、引数モードをご覧ください。

ルーティンを承認する

ストアドプロシージャはルーティンとして承認できます。承認済みのルーティンを使用すると、結果を生成した基盤となるテーブルへのアクセス権を特定のユーザーまたはグループに付与せずに、そのユーザーまたはグループとクエリ結果を共有できます。たとえば、承認済みのルーティンを使用してデータ集計を実行したり、テーブル値を検索してその値を計算で使用できます。

承認済みのルーティンでは、テーブルの作成、削除、操作のほかに、基盤となるテーブルで他のストアドプロシージャを呼び出すこともできます。

詳しくは、承認済みのルーティンをご覧ください。

ストアドプロシージャを呼び出す

作成したストアドプロシージャを呼び出すには、CALL ステートメントを使用します。たとえば、次のステートメントはストアドプロシージャ create_customer を呼び出します。

CALL mydataset.create_customer();

システムプロシージャを呼び出す

組み込みのシステムプロシージャを呼び出すには、CALL ステートメントを使用します。たとえば、次のステートメントはシステムプロシージャ BQ.REFRESH_MATERIALIZED_VIEW を呼び出します。

CALL BQ.REFRESH_MATERIALIZED_VIEW;

SQL ストアド プロシージャを操作する

ストアド プロシージャを作成する