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 キーワードを使用します。詳細については、引数モードをご覧ください。

ルーティンを承認

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

ルーティンとして承認されたストアドプロシージャを使用すると、基になるテーブルに対してデータ操作言語（DML）のステートメントを実行することもできます。

詳細については、承認済みルーティンをご覧ください。

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

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

CALL mydataset.create_customer();

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

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

CALL BQ.REFRESH_MATERIALIZED_VIEW;

SQL ストアド プロシージャを使用する

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