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;