Snowflakeスクリプトでのストアドプロシージャの記述¶
このトピックでは、Snowflake Scriptingを使用して SQL でストアド プロシージャを記述する方法を紹介します。Snowflake Scriptingの詳細については、 Snowflakeスクリプト開発者ガイド をご参照¥ください。
このトピックの内容:
概要¶
Snowflakeスクリプトを使用するストアドプロシージャを記述するには、
CREATE PROCEDURE または WITH ... CALL ... コマンドを LANGUAGE SQL で使用します。
ストアドプロシージャの本文(AS 句)では、 Snowflakeスクリプトのブロック を使用します。
注釈
SnowSQL または Classic Console でSnowflakeスクリプトプロシージャを作成する場合は、ストアドプロシージャの本文の前後に 文字列リテラル区切り文字 (
'
または$$
)を使用する必要があります。詳細については、 Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する をご参照ください。
Snowflake は、Snowflakeスクリプトストアドプロシージャの本文にあるソースコードの最大サイズを制限します。Snowflakeは、サイズを100 KB に制限することをお勧めします。(コードは圧縮された形式で保存され、正確な制限はコードの圧縮率によって異なります。)
ハンドラーコードの実行時にログをキャプチャし、データをトレースできます。詳細については、 ロギング、トレース、メトリクス をご参照ください。
注釈
呼び出し元の権限と所有者の権限 に関する同じ規則が、これらのストアドプロシージャに適用されます。
ストアドプロシージャの使用 と同じ考慮事項とガイドラインが、Snowflakeスクリプトのストアドプロシージャに適用されます。
以下は、渡された引数の値を返す単純なストアドプロシージャの例です。
CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
RETURN message;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
RETURN message;
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL output_message('Hello World');
以下は、 WITH ... CALL ... コマンドを使用して、匿名ストアドプロシージャを作成し、呼び出す例です。
WITH anonymous_output_message AS PROCEDURE (message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
RETURN message;
END;
$$
CALL anonymous_output_message('Hello World');
匿名ストアドプロシージャでは、 文字列リテラル区切り文字 ('
または $$
)をプロシージャ本文の周囲に使用しなければならないことに注意してください。
ストアドプロシージャに渡される引数の使用¶
ストアドプロシージャに引数を渡すと、Snowflakeスクリプトの式でそれらの引数を名前で参照できます。Snowflakeスクリプトストアドプロシージャは、入力引数(IN)と出力引数(OUT)をサポートしています。
Snowflakeスクリプトストアドプロシージャの定義で出力引数を指定すると、ストアドプロシージャは、出力引数の現在の値を、匿名ブロックや別のストアドプロシージャなどの呼び出し元プログラムに返すことができます。ストアドプロシージャは、出力引数の初期値を取得し、その値をプロシージャ本文の変数に保存します。オプションで、変数の値を変更する操作を実行してから、更新された値を呼び出し元プログラムに返します。
たとえば、営業担当者のユーザー識別子と四半期の売上を emp_quarter_calling_sp_demo
というストアドプロシージャに渡すことができます。このストアドプロシージャは、sales_total_out_sp_demo
という別のストアドプロシージャを呼び出します。sales_total_out_sp_demo
ストアドプロシージャには、営業担当者の四半期の売上合計を呼び出し元ストアドプロシージャ emp_quarter_calling_sp_demo
に返す操作を実行する出力引数があります。このシナリオの例については、出力引数を使用して、四半期における従業員の総売上を返します。 をご参照ください。
渡される値のデータ型と出力引数のデータ型が不一致の場合は、サポートされている強制が自動的に実行されます。例については、 呼び出し元のプロシージャからの入力値とは異なるデータ型の出力引数の使用 をご参照ください。Snowflakeが自動的に実行できる強制の情報については、キャストできるデータ型 をご参照ください。
GET_DDL 関数と SHOW PROCEDURES コマンドは、出力にストアドプロシージャの引数の型(IN
または OUT
)を表示します。ストアドプロシージャに関するメタデータを表示するその他のコマンドとビュー(DESCRIBE PROCEDURE コマンド、情報スキーマ PROCEDURES ビュー、アカウント使用状況 PROCEDURES ビュー など)では、引数の型は表示されません。
署名で異なる引数の型を指定してストアドプロシージャをオーバーロードすることはできません。たとえば、ストアドプロシージャが次の署名を持つとします。
CREATE PROCEDURE test_overloading(a IN NUMBER)
次の CREATEPROCEDURE コマンドは、引数の型のみが前の例とは異なる新しいストアドプロシージャを作成しようとするため、プロシージャが既に存在するというエラーで失敗します。
CREATE PROCEDURE test_overloading(a OUT NUMBER)
構文¶
Snowflakeスクリプトのストアドプロシージャ定義で引数を指定するには、次の構文を使用します。
<arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type>
条件:
arg_name
引数の名前。名前は、 オブジェクト識別子 の名前付け規則に従う必要があります。
{ IN | INPUT | OUT | OUTPUT }
引数が入力引数か出力引数かを指定するオプションのキーワード。
IN
またはINPUT
- 引数は指定された値で初期化され、この値がストアドプロシージャ変数に割り当てられます。変数はストアドプロシージャ本文で変更できますが、その最終値を呼び出し元プログラムに渡すことはできません。IN
とINPUT
は同義語です。OUT
またはOUTPUT
- 引数は指定された値で初期化され、この値がストアドプロシージャ変数に割り当てられます。変数はストアドプロシージャの本文で変更でき、その最終値は呼び出し元プログラムに渡すことができます。ストアドプロシージャ本文では、出力引数には変数を使用してのみ値を割り当てることができます。出力引数に初期化されていない変数を渡すこともできます。関連付けられている変数が割り当てられていない場合、出力引数は NULL を返します。
OUT
とOUTPUT
は同義語です。
デフォルト:
IN
arg_data_type
制限事項¶
出力引数は、ストアドプロシージャの定義で指定する必要があります。
出力引数は :ref:` オプション引数 <label-procedure_function_arguments_optional>` として指定できません。つまり、出力引数は DEFAULT キーワードを使用して指定できません。
ストアドプロシージャ本文では、出力引数に値を割り当てるために変数を使用する必要があります。
同じ変数を複数の出力引数に使用することはできません。
セッション変数を出力引数に渡すことはできません。
ユーザー定義関数(UDFs)は出力引数をサポートしていません。
SQL 以外の言語で記述されたストアドプロシージャは出力引数をサポートしていません。
出力引数は :doc:` 非同期子ジョブ </developer-guide/snowflake-scripting/asynchronous-child-jobs>` では使用できません。
ストアドプロシージャの引数は、入力引数と出力引数を含めて500個までに制限されています。
例¶
ストアドプロシージャに渡される引数を使用する単純な例¶
次のストアドプロシージャは、 IF および RETURN ステートメントの引数の値を使用します。
CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
IF (number_1 > number_2) THEN
RETURN number_1;
ELSE
RETURN number_2;
END IF;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
IF (number_1 > number_2) THEN
RETURN number_1;
ELSE
RETURN number_2;
END IF;
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL return_greater(2, 3);
SQL ステートメントでの引数の使用(バインド)¶
Snowflakeスクリプト変数の場合と同様に、 SQL ステートメントで引数を使用する必要がある場合は、引数名の前にコロン(:
)を付けます。詳細については、 SQL ステートメントでの変数の使用(バインド) をご参照ください。
以下の節では、ストアドプロシージャでバインド変数を使用する例を示します。
WHERE 句でバインド変数を使う例¶
次のストアドプロシージャは、 SELECT ステートメントの WHERE 句で id
引数を使用します。WHERE 句では、引数は :id
として指定されます。
CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
RETURN TABLE(res);
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
RETURN TABLE(res);
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL find_invoice_by_id('2');
さらに、 TO_QUERY 関数は、 SELECT ステートメントの FROM 句で直接 SQL 文字列を受け入れるための簡単な構文を提供します。TO_QUERY 関数とダイナミック SQL の比較については 実行時に SQL を構築する をご参照ください。
バインド変数を使ってプロパティの値をセットする例¶
以下のストアドプロシージャは、 comment
引数を使用して、 CREATE TABLE ステートメント内のテーブルにコメントを追加します。ステートメントでは、引数は :comment
として指定されます。
CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL test_bind_comment('My Test Table');
INFORMATION_SCHEMA の TABLES ビュー をクエリして、テーブルのコメントを表示します。
SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT';
+---------------+
| COMMENT |
|---------------|
| My Test Table |
+---------------+
コメントを表示するには、 SHOW TABLES コマンドを実行します。
バインド変数を使ってコマンドのパラメーターをセットする例¶
CSV ファイルを持つ st
というステージがあるとします。
CREATE OR REPLACE STAGE st;
PUT file://good_data.csv @st;
PUT file://errors_data.csv @st;
CSV ファイルのデータを test_bind_stage_and_load
という名前のテーブルに読み込みたいのです。
CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR);
次のストアドプロシージャは、 COPY INTO <テーブル> ステートメントで FROM、 ON_ERROR、 VALIDATION_MODE パラメーターを使用します。ステートメントでは、パラメーター値をそれぞれ :my_stage_name
、 :on_error
、 :valid_mode
と指定します。
CREATE OR REPLACE PROCEDURE test_copy_files_validate(
my_stage_name VARCHAR,
on_error VARCHAR,
valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
COPY INTO test_bind_stage_and_load
FROM :my_stage_name
ON_ERROR=:on_error
FILE_FORMAT=(type='csv')
VALIDATION_MODE=:valid_mode;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE test_copy_files_validate(
my_stage_name VARCHAR,
on_error VARCHAR,
valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
COPY INTO test_bind_stage_and_load
FROM :my_stage_name
ON_ERROR=:on_error
FILE_FORMAT=(type='csv')
VALIDATION_MODE=:valid_mode;
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');
配列にバインド変数を使う例¶
配列 を表すバインド変数を個々の値のリストに展開するには、スプレッド演算子(**
)を使います。詳細情報と例については、 展開演算子 をご参照ください。
オブジェクト識別子としての引数の使用¶
オブジェクトを参照するために引数を使用する必要がある場合(例: SELECT ステートメントの FROM 句のテーブル名)は、 IDENTIFIER キーワードを使用して、引数がオブジェクト識別子を表すことを示します。例:
CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
row_count INTEGER DEFAULT 0;
res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
c1 CURSOR FOR res;
BEGIN
FOR row_variable IN c1 DO
row_count := row_variable.count;
END FOR;
RETURN row_count;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
row_count INTEGER DEFAULT 0;
res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
c1 CURSOR FOR res;
BEGIN
FOR row_variable IN c1 DO
row_count := row_variable.count;
END FOR;
RETURN row_count;
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL get_row_count('invoices');
次の例では、引数で指定されたテーブル名に基づいて、ストアドプロシージャの CREATE TABLE ... AS SELECT(CTAS)ステートメントを実行します。
CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
RETURNS TEXT
LANGUAGE SQL
AS
BEGIN
CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
SELECT * FROM IDENTIFIER(:existing_table);
RETURN 'Table created';
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
RETURNS TEXT
LANGUAGE SQL
AS
$$
BEGIN
CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
SELECT * FROM IDENTIFIER(:existing_table);
RETURN 'Table created';
END;
$$
;
プロシージャを呼び出す前に、簡単なテーブルを作成してデータを挿入します。
CREATE OR REPLACE TABLE test_table_for_ctas_sp (
id NUMBER(2),
v VARCHAR(2))
AS SELECT
column1,
column2,
FROM
VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');
ストアドプロシージャを呼び出して、このテーブルをベースとした新しいテーブルを作成します。
CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup');
SQL ステートメントの文字列作成時における引数の使用¶
EXECUTE IMMEDIATE に渡される文字列として SQL ステートメントを作成する場合(宣言された RESULTSET へのクエリの割り当て を参照)は、引数の前にコロンを付けないでください。例:
CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
select_statement VARCHAR;
res RESULTSET;
BEGIN
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);
RETURN TABLE(res);
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
select_statement VARCHAR;
res RESULTSET;
BEGIN
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);
RETURN TABLE(res);
END;
$$
;
出力引数を使用して単一の値を返す¶
次の例では、定義内に出力引数 xout
を使用してストアドプロシージャ simple_out_sp_demo
を作成します。このストアドプロシージャは、xout
の値を 2
に設定します。
CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
xout := 2;
RETURN 'Done';
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
xout := 2;
RETURN 'Done';
END;
$$
;
次の匿名ブロックは、x
変数の値を 1
に設定します。次に、simple_out_sp_demo
ストアドプロシージャを呼び出し、その変数を引数として指定します。
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x);
RETURN x;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
EXECUTE IMMEDIATE
$$
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x);
RETURN x;
END;
$$
;
出力から、simple_out_sp_demo
ストアドプロシージャが出力引数の値を 2
に設定する操作を実行し、その値を匿名ブロックに返したことがわかります。
+-----------------+
| anonymous block |
|-----------------|
| 2 |
+-----------------+
次の匿名ブロックは simple_out_sp_demo
ストアドプロシージャを呼び出し、変数ではなく式を使用して出力引数に値を割り当てようとしているため、エラーを返します。
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x + 2);
RETURN x;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
EXECUTE IMMEDIATE
$$
BEGIN
LET x := 1;
CALL simple_out_sp_demo(:x + 2);
RETURN x;
END;
$$
;
出力引数を使用して、ストアドプロシージャへの複数の呼び出しで複数の値を返す¶
次の例は、ストアドプロシージャと入力引数および出力引数に関連する次の動作を示しています。
ストアドプロシージャは、その定義内に複数の入力引数と出力引数を持つことができます。
プログラムは出力引数を持つストアドプロシージャを複数回呼び出すことができ、出力引数の値は各呼び出し後に保持されます。
入力引数は、呼び出し元のプログラムに値を返しません。
定義内に複数の入力引数と出力引数を持つストアドプロシージャ multiple_out_sp_demo
を作成します。ストアドプロシージャは、同等の入力引数と出力引数に対して同じ操作を実行します。たとえば、ストアドプロシージャは 1
を p1_in
入力引数と p1_out
出力引数に追加します。
CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
p1_in NUMBER,
p1_out OUT NUMBER,
p2_in VARCHAR(100),
p2_out OUT VARCHAR(100),
p3_in BOOLEAN,
p3_out OUT BOOLEAN)
RETURNS NUMBER
LANGUAGE SQL
AS
BEGIN
p1_in := p1_in + 1;
p1_out := p1_out + 1;
p2_in := p2_in || ' hi ';
p2_out := p2_out || ' hi ';
p3_in := (NOT p3_in);
p3_out := (NOT p3_out);
RETURN 1;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
p1_in NUMBER,
p1_out OUT NUMBER,
p2_in VARCHAR(100),
p2_out OUT VARCHAR(100),
p3_in BOOLEAN,
p3_out OUT BOOLEAN)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
BEGIN
p1_in := p1_in + 1;
p1_out := p1_out + 1;
p2_in := p2_in || ' hi ';
p2_out := p2_out || ' hi ';
p3_in := (NOT p3_in);
p3_out := (NOT p3_out);
RETURN 1;
END;
$$
;
以下の匿名ブロックは、multiple_out_sp_demo
ストアドプロシージャの引数に対応する変数に値を割り当て、その後、ストアドプロシージャを複数回呼び出します。最初の呼び出しは匿名ブロックで指定された変数値を使用しますが、後続の各呼び出しは multiple_out_sp_demo
ストアドプロシージャの出力引数によって返された値を使用します。
BEGIN
LET x_in INT := 1;
LET x_out INT := 1;
LET y_in VARCHAR(100) := 'hello';
LET y_out VARCHAR(100) := 'hello';
LET z_in BOOLEAN := true;
LET z_out BOOLEAN := true;
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
EXECUTE IMMEDIATE
$$
BEGIN
LET x_in INT := 1;
LET x_out INT := 1;
LET y_in VARCHAR(100) := 'hello';
LET y_out VARCHAR(100) := 'hello';
LET z_in BOOLEAN := true;
LET z_out BOOLEAN := true;
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
$$
;
+------------------------+
| anonymous block |
|------------------------|
| [ |
| 1, |
| 4, |
| "hello", |
| "hello hi hi hi ", |
| true, |
| false |
| ] |
+------------------------+
呼び出し元のプロシージャからの入力値とは異なるデータ型の出力引数の使用¶
ユースケースによっては、ストアドプロシージャに渡される値のデータ型とプロシージャの出力引数のデータ型が不一致になる可能性があります。このような場合 サポートされている強制 が自動的に実行されます。
注釈
強制はサポートされている場合もありますが、推奨されていません。
この例では、NUMBER データ型の出力引数に渡される FLOAT 値の自動変換を示します。その FLOAT 値は自動的に NUMBER 値に変換され、呼び出し元の匿名ブロックに返されます。
NUMBER 型の出力引数を取る sp_out_coercion
ストアドプロシージャを作成します。
CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
x := x * 2;
RETURN 'Done';
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
x := x * 2;
RETURN 'Done';
END;
$$
;
FLOAT 値を sp_out_coercion
ストアドプロシージャに渡す匿名ブロックを実行します。
BEGIN
LET a FLOAT := 500.662;
CALL sp_out_coercion(:a);
RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
EXECUTE IMMEDIATE
$$
BEGIN
LET a FLOAT := 500.662;
CALL sp_out_coercion(:a);
RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
$$
;
SYSTEM$TYPEOF 関数を呼び出すことにより、出力には返される値と返される値のデータ型の両方が表示されます。ストアドプロシージャから値が返された後、NUMBER 値から FLOAT 値に強制されることに注意してください。
+---------------------------+
| anonymous block |
|---------------------------|
| 1002 (Type FLOAT[DOUBLE]) |
+---------------------------+
出力引数を使用して、四半期における従業員の総売上を返します。¶
この例では、以下の quarterly_sales
テーブルを使用します。
CREATE OR REPLACE TABLE quarterly_sales(
empid INT,
amount INT,
quarter TEXT)
AS SELECT * FROM VALUES
(1, 10000, '2023_Q1'),
(1, 400, '2023_Q1'),
(2, 4500, '2023_Q1'),
(2, 35000, '2023_Q1'),
(1, 5000, '2023_Q2'),
(1, 3000, '2023_Q2'),
(2, 200, '2023_Q2'),
(2, 90500, '2023_Q2'),
(1, 6000, '2023_Q3'),
(1, 5000, '2023_Q3'),
(2, 2500, '2023_Q3'),
(2, 9500, '2023_Q3'),
(3, 2700, '2023_Q3'),
(1, 8000, '2023_Q4'),
(1, 10000, '2023_Q4'),
(2, 800, '2023_Q4'),
(2, 4500, '2023_Q4'),
(3, 2700, '2023_Q4'),
(3, 16000, '2023_Q4'),
(3, 10200, '2023_Q4');
従業員識別子と四半期の2つの入力引数と、指定された従業員と四半期の売上合計を計算する1つの出力引数を取るストアドプロシージャ sales_total_out_sp_demo
を作成します。
CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo(
id INT,
quarter VARCHAR(20),
total_sales OUT NUMBER(38,0))
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
SELECT SUM(amount) INTO total_sales FROM quarterly_sales
WHERE empid = :id AND
quarter = :quarter;
RETURN 'Done';
END;
$$
;
sales_total_out_sp_demo
ストアドプロシージャを呼び出すストアドプロシージャ emp_quarter_calling_sp_demo
を作成します。このストアドプロシージャも、従業員識別子と四半期の2つの入力引数を取ります。
CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
id INT,
quarter VARCHAR(20))
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
LET x NUMBER(38,0);
CALL sales_total_out_sp_demo(:id, :quarter, :x);
RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
id INT,
quarter VARCHAR(20))
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
LET x NUMBER(38,0);
CALL sales_total_out_sp_demo(:id, :quarter, :x);
RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
$$
;
引数 2``(従業員識別子)と ``'2023_Q4'``(四半期)を指定して ``emp_quarter_calling_sp_demo
を呼び出します。
CALL emp_quarter_calling_sp_demo(2, '2023_Q4');
+-----------------------------------------------------+
| emp_quarter_calling_sp_demo |
|-----------------------------------------------------|
| Total sales for employee 2 in quarter 2023_Q4: 5300 |
+-----------------------------------------------------+
表形式のデータを返す¶
ストアドプロシージャから表形式のデータ(例: RESULTSET からのデータ)を返す必要がある場合は、 CREATE PROCEDURE ステートメントで RETURNS TABLE(...)を指定します。
返されたテーブルの列の Snowflakeデータ型 がわかっている場合は、 RETURNS TABLE()で列名と型を指定します。
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...
それ以外の場合(例: 実行時に列型を決定する場合)は、列名と型を省略できます。
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
...
注釈
現在、 RETURNS TABLE(...)
句では、 GEOGRAPHY を列タイプとして指定することはできません。これは、ストアドプロシージャまたは匿名プロシージャを作成する場合に適用されます。
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE(g GEOGRAPHY)
...
CALL test_return_geography_table_1();
列タイプとして GEOGRAPHY を指定しようとすると、ストアドプロシージャの呼び出しはエラーになります。
Stored procedure execution error: data type of returned table does not match expected returned table type
この問題を回避するには、 RETURNS TABLE()
の列の引数と型を省略します。
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE()
...
CALL test_return_geography_table_1();
RESULTSET でデータを返す必要がある場合は、 RETURN ステートメントで TABLE()を使用します。
例:
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL get_top_sales();
別のストアドプロシージャからのストアドプロシージャ呼び出し¶
ストアドプロシージャで、別のストアドプロシージャを呼び出す必要がある場合は、次のいずれかの方法を使用します。
戻り値を使用しないストアドプロシージャ呼び出し¶
CALL ステートメントを使用して、ストアドプロシージャを呼び出します(通常どおり)。
CALL ステートメントで入力引数として変数または引数を渡す必要がある場合は、変数名の前にコロン(:
)を使用することを忘れないでください。(SQL ステートメントでの変数の使用(バインド) を参照。)
以下は、別のストアドプロシージャを呼び出すが、戻り値に依存しないストアドプロシージャの例です。
まず、例で使用するテーブルを作成します。
-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);
次に、別のストアドプロシージャから呼び出すストアドプロシージャを作成します。
-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
INSERT INTO int_table VALUES (:value);
RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
INSERT INTO int_table VALUES (:value);
RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;
次に、最初のストアドプロシージャを呼び出す2番目のストアドプロシージャを作成します。
CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
CALL insert_value(:value1);
CALL insert_value(:value2);
RETURN 'Finished calling stored procedures';
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
CALL insert_value(:value1);
CALL insert_value(:value2);
RETURN 'Finished calling stored procedures';
END;
$$
;
最後に、2番目のストアドプロシージャを呼び出します。
CALL insert_two_values(4, 5);
ストアドプロシージャ呼び出しからの戻り値の使用¶
スカラー値を返すストアドプロシージャを呼び出していて、その値にアクセスする必要がある場合は、 CALL ステートメントで INTO :snowflake_scripting_variable
句を使用して、 Snowflakeスクリプト変数 の値をキャプチャします。
次の例では、 オブジェクト識別子としての引数の使用 で定義された get_row_count
ストアドプロシージャを呼び出します。
CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
DECLARE
count1 NUMBER;
BEGIN
CALL get_row_count(:table_name) INTO :count1;
IF (:count1 > maximum_count) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
count1 NUMBER;
BEGIN
CALL get_row_count(:table_name) INTO :count1;
IF (:count1 > maximum_count) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$
;
以下は、ストアドプロシージャを呼び出す例です。
CALL count_greater_than('invoices', 3);
ストアドプロシージャがテーブルを返す場合は、 RESULTSET を CALL ステートメントを含む文字列に設定することで、戻り値をキャプチャできます。(宣言された RESULTSET へのクエリの割り当て を参照。)
呼び出しから戻り値を取得するには、 RESULTSET の CURSOR を使用できます。例:
DECLARE
res1 RESULTSET;
BEGIN
res1 := (CALL my_procedure());
LET c1 CURSOR FOR res1;
FOR row_variable IN c1 DO
IF (row_variable.col1 > 0) THEN
...;
ELSE
...;
END IF;
END FOR;
...
ストアドプロシージャから呼び出しストアドプロシージャへの出力引数値の引き渡し¶
Snowflakeスクリプトストアドプロシージャの定義で出力引数が指定されている場合、ストアドプロシージャは出力引数の現在の値を呼び出し元のストアドプロシージャに返すことができます。ストアドプロシージャは、出力引数の初期値を取得し、その値をプロシージャ本文の変数に保存し、オプションで変数の値を変更する操作を実行します。次に、ストアドプロシージャは更新された値を呼び出し元のストアドプロシージャに返します。
例については、 出力引数を使用して、四半期における従業員の総売上を返します。 をご参照ください。
ネストされたストアドプロシージャの使用¶
ネストされたストアドプロシージャ*とは、匿名ブロック内、または別のストアドプロシージャ(*親ストアドプロシージャ)内のブロック内で定義されるストアドプロシージャのことです。
ネストされたストアドプロシージャは、CREATE PROCEDURE ステートメントの一部となるブロックの DECLARE セクションで宣言します。次の例は、ネストされたストアドプロシージャの宣言を示しています。
DECLARE
<nested_stored_procedure_name> PROCEDURE (<arguments>)
RETURNS <data_type>
AS
BEGIN
<nested_procedure_procedure_statements>
END;
BEGIN
<statements>
END;
ネストされたストアドプロシージャの宣言構文については、ネストされたストアドプロシージャ宣言構文 をご参照ください。
ネストされたストアドプロシージャは、その :doc:` ブロック </developer-guide/snowflake-scripting/blocks>` のスコープ内にのみ存在します。ネストされたストアドプロシージャは、そのブロックのどのセクションからでも呼び出すことができます(DECLARE、BEGIN ... END、EXCEPTION)。1つのブロックには複数のネストされたストアドプロシージャを含めることができ、1つのネストされたストアドプロシージャは、同じブロックで別のネストされたストアドプロシージャを呼び出すことができます。ネストされたプロシージャは、そのブロックの外部から呼び出すことやアクセスすることはできません。
ネストされたストアドプロシージャは、それを定義するブロックと同じセキュリティコンテキストで動作します。ネストされたストアドプロシージャが親ストアドプロシージャで定義されると、親ストアドプロシージャと同じ権限で自動的に実行されます。
注釈
ネストされたストアドプロシージャの宣言と CALL WITH コマンドはどちらも、スコープが制限された一時ストアドプロシージャを作成します。それぞれの違いは以下のとおりです。
CALL WITH ステートメントは、ストアドプロシージャ内を含め、SQL ステートメントが使用できる場所であればどこでも使用できますが、ネストされたストアドプロシージャの宣言はSnowflakeスクリプトのブロック内である必要があります。
CALLWITH ストアドプロシージャはそのステートメントのスコープ内にのみ存在しますが、ネストされたストアドプロシージャはそのSnowflakeスクリプトブロックのスコープ内に存在します。
ネストされたストアドプロシージャの利点¶
ネストされたストアドプロシージャには、次の利点があります。
ロジックを匿名ブロックや親ストアドプロシージャ内にカプセル化することで、ブロックまたは親の外部からのアクセスを防ぎ、セキュリティを強化、簡素化できます。
コードを論理的に小さなチャンクに分割することでコードのモジュール性を維持し、メンテナンスとデバッグを容易にします。
ネストされたストアドプロシージャはそのブロックのローカル変数に直接アクセスできるため、グローバル変数や追加の引数の必要性が減り、メンテナンス性が改善します。
ネストされたストアドプロシージャを呼び出すための使用上の注意¶
ネストされたストアドプロシージャの呼び出しには、次の使用上の注意が適用されます。
ネストされたストアドプロシージャに引数を渡すために、ブロックでは定数値、Snowflakeスクリプト変数、バインド変数、SQL(セッション)変数、ユーザー定義関数 の呼び出しを使用できます。
渡される値のデータ型と引数のデータ型が不一致の場合、Snowflakeはサポートされている強制を自動的に実行します。Snowflakeが自動的に実行できる強制の情報については、データ型の変換 をご参照ください。
ネストされたストアドプロシージャ内の変数の使用上の注意¶
ネストされたストアドプロシージャ内の変数には、次の使用上の注意が適用されます。
ネストされたストアドプロシージャは、そのブロックの DECLARE セクション内のネストされたストアドプロシージャの宣言より前に宣言された変数をそのブロックから参照できます。DECLARE セクションで自身の宣言より後に定義された変数は参照できません。
ネストされたストアドプロシージャは、ブロックの BEGIN ... END セクションの LET ステートメントで宣言された変数にアクセスできません。
参照される変数の値は、ネストされたストアドプロシージャが呼び出された時点の値を反映します。
ネストされたストアドプロシージャは、参照している変数の値を変更でき、その変更された値は、匿名ブロックの1回の実行または親ストアドプロシージャへの1回の呼び出しで、ブロック内および同じネストされたプロシージャの複数の呼び出しにわたって保持されます。
ネストされたストアドプロシージャの呼び出しの前に宣言された変数の値は、ネストされたストアドプロシージャに引数として渡すことができます。変数の値は、その変数がネストされたストアドプロシージャの宣言後や LET ステートメント内で宣言されたものであっても、呼び出し時の引数として渡すことができます。
たとえば、次のストアドプロシージャでは複数の変数が宣言されています。
CREATE OR REPLACE PROCEDURE outer_sp ()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
var_before_nested_proc NUMBER DEFAULT 1;
test_nested_variables PROCEDURE(arg1 NUMBER)
-- <nested_sp_logic>
var_after_nested_proc NUMBER DEFAULT 2;
BEGIN
LET var_let_before_call NUMBER DEFAULT 3;
LET result := CALL nested_proc(:<var_name>);
LET var_let_after_call NUMBER DEFAULT 3;
RETURN result;
END;
$$;
この例では、nested_sp_logic
で参照できるのは var_before_nested_proc
のみです。
ネストされたストアドプロシージャの呼び出しでは、以下の変数のいずれの値も、var_name
の引数としてネストされたストアドプロシージャに渡すことができます。
var_before_nested_proc
var_after_nested_proc
var_let_before_call
var_let_after_call
の値は、ネストされたストアドプロシージャに引数として渡すことはできません。
ネストされたストアドプロシージャの制限¶
ネストされたストアドプロシージャの定義には、次の制限が適用されます。
他のネストされたストアドプロシージャ内、または FOR ループや WHILE ループなどの制御構造内では定義できません。
ネストされた各ストアドプロシージャは、そのブロック内で一意の名前を持つ必要があります。つまり、ネストされたストアドプロシージャはオーバーロードできません。
出力(OUT)引数はサポートされていません。
デフォルト値を持つオプションの引数はサポートされていません。
ネストされたストアドプロシージャの呼び出しには、次の制限が適用されます。
EXECUTE IMMEDIATE ステートメントでは呼び出せません。
:doc:` 非同期子ジョブ </developer-guide/snowflake-scripting/asynchronous-child-jobs>` では呼び出せません。
名前付き入力引数(
arg_name => arg
)はサポートされていません。引数は位置で指定する必要があります。詳細については、 CALL をご参照ください。
ネストされたストアドプロシージャの例¶
次の例では、ネストされたストアドプロシージャを使用しています。
表形式データを返すネストされたストアドプロシージャを定義する¶
次の例では、表形式データを返すネストされたストアドプロシージャを定義します。この例では、nested_procedure_example_table
と呼ばれる親ストアドプロシージャと、nested_return_table
と呼ばれるネストされたストアドプロシージャが作成されます。コードには、次のロジックが含まれています。
RESULTSET 型の
res
という変数を宣言します。ネストされたストアドプロシージャに次のロジックが含まれます。
res2
という変数を宣言します。nested_table
というテーブルに値を挿入します。res2
変数を、テーブルに対する SELECT の結果に設定します。結果セットの表形式のデータを返します。
親ストアドプロシージャにテーブル
nested_table
を作成します。ネストされたストアドプロシージャ
nested_return_table
を呼び出し、res
変数をネストされたストアドプロシージャへの呼び出しの結果に設定します。表形式の結果を
res
変数に返します。
CREATE OR REPLACE PROCEDURE nested_procedure_example_table()
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
res RESULTSET;
nested_return_table PROCEDURE()
RETURNS TABLE()
AS
DECLARE
res2 RESULTSET;
BEGIN
INSERT INTO nested_table VALUES(1);
INSERT INTO nested_table VALUES(2);
res2 := (SELECT * FROM nested_table);
RETURN TABLE(res2);
END;
BEGIN
CREATE OR REPLACE TABLE nested_table(col1 INT);
res := (CALL nested_return_table());
RETURN TABLE(res);
END;
$$;
ストアドプロシージャを呼び出します。
CALL nested_procedure_example_table();
+------+
| COL1 |
|------|
| 1 |
| 2 |
+------+
スカラー値を返すネストされたストアドプロシージャを定義する¶
次の例では、スカラー値を返すネストされたストアドプロシージャを定義します。この例では、nested_procedure_example_scalar
と呼ばれる親ストアドプロシージャと、simple_counter
と呼ばれるネストされたストアドプロシージャが作成されます。コードには、次のロジックが含まれています。
NUMBER 型の変数
counter
を宣言し、この変数の値を0
に設定します。ネストされたストアドプロシージャが、
counter
型の変数の現在の値に1
を加えるよう指定します。親ストアドプロシージャで、ネストされたストアドプロシージャを3回呼び出します。
counter
変数の値は、ネストされたストアドプロシージャの呼び出し間で引き継がれます。counter
変数の値(3
)を返します。
CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
counter NUMBER := 0;
simple_counter PROCEDURE()
RETURNS VARCHAR
AS
BEGIN
counter := counter + 1;
RETURN counter;
END;
BEGIN
CALL simple_counter();
CALL simple_counter();
CALL simple_counter();
RETURN counter;
END;
$$;
ストアドプロシージャを呼び出します。
CALL nested_procedure_example_scalar();
+---------------------------------+
| NESTED_PROCEDURE_EXAMPLE_SCALAR |
|---------------------------------|
| 3 |
+---------------------------------+
匿名ブロックでネストされたストアドプロシージャを定義します¶
次の例は、ストアドプロシージャではなく、匿名ブロック内でネストされたストアドプロシージャを定義していることを除き、スカラー値を返すネストされたストアドプロシージャを定義する の例と同じです。
EXECUTE IMMEDIATE $$
DECLARE
counter NUMBER := 0;
simple_counter PROCEDURE()
RETURNS VARCHAR
AS
BEGIN
counter := counter + 1;
RETURN counter;
END;
BEGIN
CALL simple_counter();
CALL simple_counter();
CALL simple_counter();
RETURN counter;
END;
$$;
+-----------------+
| anonymous block |
|-----------------|
| 3 |
+-----------------+
引数を渡されるネストされたストアドプロシージャを定義する¶
次の例では、引数を渡されるネストされたストアドプロシージャを定義します。この例では、ネストされたストアドプロシージャは、次のテーブルに値を挿入します。
CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT);
この例では、nested_procedure_example_arguments
と呼ばれる親ストアドプロシージャと、log_and_multiply_numbers
と呼ばれるネストされたストアドプロシージャが作成されます。ネストされたストアド プロシージャは、NUMBER 型の引数を2つ取ります。コードには、次のロジックが含まれています。
NUMBER 型の変数
a
、b
、x
を宣言します。次のアクションを実行するネストされたストアドプロシージャが含まれます。
バインド変数を使用して、親ストアドプロシージャから渡された2つの数値を
log_nested_values
テーブルに挿入します。変数
x
の値を、2つの引数値を乗算した結果に設定します。x
の値を親ストアドプロシージャに返します。
変数
a
の値を5
に、変数b
の値を10
に設定します。ネストされたストアドプロシージャを呼び出します。
ネストされたストアドプロシージャで設定された
x
変数の値を返します。
CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
a NUMBER;
b NUMBER;
x NUMBER;
log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER)
RETURNS NUMBER
AS
BEGIN
INSERT INTO log_nested_values VALUES(:num1, :num2);
x := :num1 * :num2;
RETURN x;
END;
BEGIN
a := 5;
b := 10;
CALL log_and_multiply_numbers(:a, :b);
RETURN x;
END;
$$;
ストアドプロシージャを呼び出します。
CALL nested_procedure_example_arguments();
+------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_ARGUMENTS |
|------------------------------------|
| 50 |
+------------------------------------+
log_nested_values
テーブルをクエリして、ネストされたストアドプロシージャが渡された値を挿入したことを確認します。
SELECT * FROM log_nested_values;
+------+------+
| COL1 | COL2 |
|------+------|
| 5 | 10 |
+------+------+
別のネストされたストアドプロシージャを呼び出すネストされたストアドプロシージャを定義する¶
次の例では、別のネストされたストアドプロシージャを呼び出すネストされたストアドプロシージャを定義します。この例では、counter_nested_proc
と call_counter_nested_proc
と呼ばれる2つのネストされたストアドプロシージャを持つ、nested_procedure_example_call_from_nested
と呼ばれる親ストアドプロシージャを作成します。コードには、次のロジックが含まれています。
NUMBER 型の変数
counter
を宣言し、この変数の値を0
に設定します。counter
の値に10
を加算するネストされたストアドプロシージャcounter_nested_proc
が含まれます。counter
の値に15
を加えるネストされたストアドプロシージャcall_counter_nested_proc
を含み、counter_nested_proc
も呼び出します(これは、counter
の値にさらに10
を加えます)。親ストアドプロシージャでネストされた両方のストアドプロシージャを呼び出します。
counter
変数の値(35
)を返します。
CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
counter NUMBER := 0;
counter_nested_proc PROCEDURE()
RETURNS NUMBER
AS
DECLARE
var1 NUMBER := 10;
BEGIN
counter := counter + var1;
END;
call_counter_nested_proc PROCEDURE()
RETURNS NUMBER
AS
DECLARE
var2 NUMBER := 15;
BEGIN
counter := counter + var2;
CALL counter_nested_proc();
END;
BEGIN
counter := 0;
CALL counter_nested_proc();
CALL call_counter_nested_proc();
RETURN counter;
END;
$$;
ストアドプロシージャを呼び出します。
CALL nested_procedure_example_call_from_nested();
+-------------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED |
|-------------------------------------------|
| 35 |
+-------------------------------------------+
ストアドプロシージャでの SQL 変数の使用とセット¶
デフォルトでは、Snowflake Scriptingストアドプロシージャはオーナー権限で実行されます。ストアドプロシージャがオーナー権限で実行されると、 SQL (またはセッション) 変数にアクセスできなくなります。
しかし、呼び出し元の権限ストアドプロシージャは、呼び出し元のセッション変数を読み取り、ストアド プロシージャのロジックで使用することができます。例えば、呼び出し元のライツストアドプロシージャは、 SQL 変数の値をクエリで使用することができます。呼び出し元の権限で実行されるストアドプロシージャを作成するには、 CREATE PROCEDURE ステートメントで EXECUTE AS CALLER
パラメーターを指定します。
これらの例は、この呼び出し元権限と所有者権限のストアドプロシージャのキーの違いを示しています。これらは以下の2つの方法で SQL 変数の利用を試みています。
ストアドプロシージャを呼び出す前に SQL 変数をセットし、ストアドプロシージャ内で SQL 変数を使用します。
ストアドプロシージャの内部で SQL 変数をセットし、ストアドプロシージャから戻った後に SQL 変数を使用します。
SQL 変数の使用も SQL 変数のセットも、呼び出し元権利ストアドプロシージャで正しく機能します。呼び出し元が 所有者 であっても、所有者権限ストアドプロシージャを使用すると、両方とも失敗します。
所有者の権利および発信者の権利に関する情報については、 呼び出し元権限と所有者権限のストアドプロシージャについて をご覧ください。
ストアドプロシージャ内での SQL 変数の使用¶
次の例では、ストアドプロシージャで SQL 変数を使用します。
まず、セッションに SQL 変数をセットします。
SET example_use_variable = 2;
呼び出し元権限で実行され、この SQL 変数を使用する単純なストアドプロシージャを作成します。
CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
sess_var_x_2 NUMBER;
BEGIN
sess_var_x_2 := 2 * $example_use_variable;
RETURN sess_var_x_2;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
sess_var_x_2 NUMBER;
BEGIN
sess_var_x_2 := 2 * $example_use_variable;
RETURN sess_var_x_2;
END;
$$
;
ストアドプロシージャを呼び出します。
CALL use_sql_variable_proc();
+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
| 4 |
+-----------------------+
SQL 変数を別の値にセットします。
SET example_use_variable = 9;
プロシージャを再度呼び出して、返される値が変わったことを確認します。
CALL use_sql_variable_proc();
+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
| 18 |
+-----------------------+
ストアドプロシージャの SQL 変数のセット¶
呼び出し元権限で実行されているストアドプロシージャで、 SQL 変数をセットすることができます。ストアドプロシージャで SQL 変数を使用する際のガイドラインなど、詳細情報については 呼び出し元権限ストアドプロシージャ を参照してください。
注釈
ストアドプロシージャの内部で SQL 変数をセットし、プロシージャの終了後もセットしたままにすることはできますが、Snowflake ではこの方法を推奨して いません。
次の例では、ストアドプロシージャで SQL 変数を設定します。
まず、セッションに SQL 変数をセットします。
SET example_set_variable = 55;
SQL 変数の値を確認します。
SHOW VARIABLES LIKE 'example_set_variable';
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
| session_id | created_on | updated_on | name | value | type | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55 | fixed | |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
例えば、次のストアドプロシージャは、 SQL 変数 example_set_variable
を新しい値にセットし、新しい値を返します。
CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
SET example_set_variable = $example_set_variable - 3;
RETURN $example_set_variable;
END;
注意:Snowflake CLI、 SnowSQL、 Classic Console、 execute_stream
、 execute_string
メソッドを Python Connector コードで使用する場合は、代わりにこの例を使用してください(Snowflake CLI、 SnowSQL、 Classic Console、Python ConnectorでSnowflake Scriptingを使用する を参照)。
CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
SET example_set_variable = $example_set_variable - 3;
RETURN $example_set_variable;
END;
$$
;
ストアドプロシージャを呼び出します。
CALL set_sql_variable_proc();
+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
| 52 |
+-----------------------+
SQL 変数の新しい値を確認します。
SHOW VARIABLES LIKE 'example_set_variable';
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
| session_id | created_on | updated_on | name | value | type | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52 | fixed | |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+