Snowflakeスクリプトでのストアドプロシージャの記述

このトピックでは、Snowflakeスクリプトを使用して、 SQL でストアドプロシージャを記述する方法について説明します。

このトピックの内容:

紹介

Snowflakeスクリプトを使用するストアドプロシージャを記述するには、

次の点に注意してください。

以下は、渡された引数の値を返す単純なストアドプロシージャの例です。

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  RETURN message;
END;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  RETURN message;
END;
$$
;

以下は、ストアドプロシージャを呼び出す例です。

CALL output_message('Hello World');

ストアドプロシージャに渡される引数の使用

ストアドプロシージャに引数を渡すと、Snowflakeスクリプトの式でそれらの引数を名前で参照できます。詳細については、次のセクションをご参照ください。

ストアドプロシージャに渡される引数を使用する単純な例

次のストアドプロシージャは、 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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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 ステートメントでの変数の使用(バインド) を参照。)

たとえば、次のストアドプロシージャは、 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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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');

オブジェクト識別子としての引数の使用

オブジェクトを参照するために引数を使用する必要がある場合(例: 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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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');

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;

表形式のデータを返す

ストアドプロシージャから表形式のデータ(例: 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 ()
...

注釈

現在、 CREATE PROCEDURERETURNS TABLE(...) 句では、列型として GEOGRAPHY を指定できません。

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...

指定した場合にストアドプロシージャを呼び出すと、次のエラーが発生します。

CALL test_return_geography_table_1();
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()
...

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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

-- 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;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

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);

ストアドプロシージャ呼び出しからの戻り値の使用

呼び出しているストアドプロシージャの戻り値を使用する必要がある場合は、 RESULTSET を CALL ステートメントを含む文字列に設定します。(宣言された RESULTSET へのクエリの割り当て を参照。)

呼び出しから戻り値を取得するには、 RESULTSET の CURSOR を使用できます。呼び出しの結果にアクセスする場合、結果を含む列の名前は、呼び出したストアドプロシージャの名前であることに注意してください。

たとえば、ストアドプロシージャ get_row_count を呼び出した場合は、 row_variable.get_row_count から値を取得できます。

次の例では、 オブジェクト識別子としての引数の使用 で定義された get_row_count ストアドプロシージャを呼び出します。

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
DECLARE
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;

注: SnowSQL または 従来のウェブインターフェイス を使用している場合は、代わりに次の例を使用します(SnowSQL でのSnowflakeスクリプトと従来のウェブインターフェイスの使用 を参照)。

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  res1 RESULTSET;
BEGIN
  res1 := (CALL get_row_count(:table_name));
  LET c1 CURSOR FOR res1;
  FOR row_variable in c1 DO
    IF (row_variable.get_row_count > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
 END FOR;
END;
$$
;

以下は、ストアドプロシージャを呼び出す例です。

CALL count_greater_than('invoices', 3);
最上部に戻る