ストアドプロシージャの使用

.

関連トピック

ストアドプロシージャを使用すると、ユーザーは複数の SQL ステートメントを手続き型ロジックと組み合わせることにより、複雑なビジネスロジックを含むことができるモジュラーコードを作成できます。

このトピックの内容：

ストアドプロシージャの作成

ストアドプロシージャ DDL

ストアドプロシージャは、ファーストクラスのデータベースオブジェクトです。次の DDL コマンドは、ストアドプロシージャに適用されます。

さらに、Snowflakeは、ストアドプロシージャを実行するための次のコマンドを提供します。

実装と API

Snowflakeストアドプロシージャは JavaScript で記述されており、 JavaScript API を呼び出すことで SQL ステートメントを実行できます。この API は、Snowflakeコネクタおよびドライバー（Node.js、 JDBC、Pythonなど）の APIs と似ていますが、同一ではありません。

API を使用すると、次のような操作を実行できます。

  • SQL ステートメントを実行します。

  • クエリの結果（つまり、結果セット）の取得。

  • 結果セットに関するメタデータ（列の数、列のデータ型など）の取得。

これらの操作は、次のオブジェクトのメソッドを呼び出すことにより実行されます。

  • snowflake、 Statement オブジェクトを作成する、 SQL コマンドを実行するメソッド。

  • Statement、準備済みステートメントを実行し、それらの準備済みステートメントのメタデータにアクセスし、 ResultSet オブジェクトを取得できるようにする。

  • ResultSet、クエリの結果を保持する（例： SELECT ステートメントに対して取得されたデータの行）。

  • SfDate、JavaScript 日付の拡張（追加のメソッドを含む）であり、Snowflakeの SQL データ型 TIMESTAMP_LTZ、 TIMESTAMP_NTZ、 TIMESTAMP_TZの戻り型として機能する。

これらのオブジェクトの詳細については、 ストアドプロシージャ API をご参照ください。

典型的なストアドプロシージャには、次の擬似コードに類似するコードが含まれています。

var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();

var my_sql_command2 = "delete from log_table where event_year < 2016";
var statement2 = snowflake.createStatement(my_sql_command2);
statement2.execute();

このコードは、 snowflake という名前のオブジェクトを使用します。これは、宣言なしで存在する特別なオブジェクトです。オブジェクトは各ストアドプロシージャのコンテキスト内で提供され、 API を公開して、サーバーと対話できるようにします。

他の変数（ statement1 など）は、 JavaScript var ステートメントで作成されます。例：

var statement1 = ...;

上記のコードサンプルに示すように、 snowflake オブジェクトを使用すると、 APIのメソッドの1つを呼び出すことで Statement オブジェクトを作成できます。

ResultSet を取得してそれを反復処理する例を次に示します。

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

例 セクション（このトピックの最後）には、ストアドプロシージャ JavaScript APIの各オブジェクトと多くのメソッドを実行する追加の例が記載されています。

SQL および JavaScript データ型マッピング

ストアドプロシージャの呼び出し、使用、値の取得をする場合、多くの場合においてSnowflake SQL データ型から JavaScript データ型、またはその逆に変換する必要があります。

SQL JavaScript への変換は、次の場合に発生する可能性があります。

  • 引数を指定してストアドプロシージャを呼び出す場合。引数は SQL データ型です。ストアドプロシージャ内の JavaScript 変数内に保存されている場合は、変換する必要があります。

  • ResultSet オブジェクトから JavaScript 変数に値を取得する場合。 ResultSet は値を SQL データ型として保持し、 JavaScript 変数は値を JavaScript データ型の1つとして保存する必要があります。

JavaScript SQL への変換は、次の場合に発生する可能性があります。

  • ストアドプロシージャから値を返す場合。通常、 return ステートメントには JavaScript 変数が含まれており、 SQL データ型に変換する必要があります。

  • JavaScript 変数の値を使用する SQL ステートメントを動的に構築する場合。

  • JavaScript 変数の値を準備されたステートメントにバインドする場合。

以下のセクションでは、データを SQL から JavaScript または JavaScript から SQL に変換する方法について説明します。

SQL から JavaScript への変換

次の表は、Snowflake SQL データ型と対応する JavaScript データ型を示しています。

SQL データ型

JavaScript データ型

注意

ARRAY

JSON

BOOLEAN

boolean

DATE

date

REAL, FLOAT, FLOAT8, FLOAT4, DOUBLE, DOUBLE PRECISION

number

TIME

string

TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ

date または SfDate

タイムスタンプが引数としてストアドプロシージャに渡されると、タイムスタンプは JavaScript date オブジェクトに変換されます。他の状況（ ResultSet から取得する場合など）では、タイムスタンプは SfDate オブジェクトに変換されます。標準の JavaScript データ型ではない SfDate データ型の詳細については、 ストアドプロシージャ API をご参照ください。

VARCHAR, CHAR, CHARACTER, STRING, TEXT

string

VARIANT

JSON

例えば、 SQL ステートメントがSnowflakeテーブルから VARIANT 列を選択する場合、 ResultSet から JavaScript 変数に値をコピーする際、 JavaScript 変数は JSON 型でなければなりません。

すべてのSnowflake SQL データ型に対応する JavaScript データ型があるわけではありません。例えば、 JavaScript は INTEGER または NUMBER データ型を直接サポートしていません。これらの場合、 SQL データ型を適切な代替データ型に変換する必要があります。例えば、 SQL INTEGER を SQL FLOAT に変換し、 number のデータ型の JavaScript 値に変換できます。

以下の表は、互換性のない SQL データ型の適切な変換を示しています。

互換性のない SQL データ型

互換性のある SQL データ型

INTEGER

FLOAT

NUMBER, NUMERIC, DECIMAL

FLOAT

BINARY

Uint8Array

OBJECT

Uint8Array

JavaScript から SQL への変換

値を返すとき

ストアドプロシージャの戻り値の型は、ストアドプロシージャの定義で宣言されています。JavaScript の return ステートメントがストアドプロシージャの宣言された戻り値の型と異なるデータ型を返す場合、可能であれば JavaScript 値は SQL データ型にキャストされます。たとえば、数値が返されるが、ストアドプロシージャが文字列を返すと宣言されている場合、数値は JavaScript内の文字列に変換され、 SQL ステートメントで返される文字列にコピーされます。（誤ったデータ型を返すなどの一部の JavaScript プログラミングエラーは、この動作によって隠される可能性があります）

変換に有効なキャストが存在しない場合、エラーが発生します。

値をバインドするとき

JavaScript 変数を SQL ステートメントにバインドすると、Snowflakeは JavaScript データ型から SQL データ型に変換します。次の JavaScript データ型の変数をバインドできます。

  • 数値。

  • 文字列。

  • SfDate。（標準の JavaScript データ型ではない SfDate データ型の詳細については、 ストアドプロシージャ API を参照。）

いくつかの例を含むバインディングの詳細については、 変数のバインド をご参照ください。

データ型変換に関する追加情報

次のトピックには、関連項目が記載されています。

データ型、引数名の大文字化、および NULL 値に関するこの情報は、ストアドプロシージャとユーザー定義関数（UDFs）に適用されます。

Snowflake SQL データ型の詳細については、 データ型の概要 をご参照ください。

ストアドプロシージャの命名規則

ストアドプロシージャはデータベースオブジェクトです。つまり、指定されたデータベースとスキーマで作成されます。これらは、 データベース.スキーマ.プロシージャ名 の形式で、名前空間によって定義された完全修飾名を持っています。例：

CALL temporary_db_qualified_names_test.temporary_schema_1.stproc_pi();

完全修飾名なしで呼び出された場合、ストアドプロシージャは、セッションで使用されているデータベースとスキーマに従って解決されます。

名前のオーバーロード

Snowflakeは、ストアドプロシージャ名のオーバーロードをサポートしています。同じスキーマ内の複数のストアドプロシージャは、引数の数または引数の種類によって署名が異なる限り、同じ名前を持つことができます。オーバーロードされたストアドプロシージャが呼び出されると、Snowflakeは引数をチェックし、正しいストアドプロシージャを呼び出します。

例については、 ストアドプロシージャ名のオーバーロード をご参照ください。

オーバーロードを使用する場合は注意してください。自動型変換とオーバーロードを組み合わせると、軽微なユーザーエラーによって予期しない結果が引き起こされやすくなります。例については、 ストアドプロシージャ名のオーバーロード をご参照ください。

システム定義関数およびユーザー定義関数との潜在的な競合

ストアドプロシージャとユーザー定義関数は、引数の数が異なるか、引数に対するデータ型が異なる場合、同じ名前を持つことができます。

ただし、Snowflakeでは、システム定義関数と同じ名前のストアドプロシージャは作成できません。

トランザクション管理

ストアドプロシージャはアトミックではありません。ストアドプロシージャ内の1つのステートメントが失敗した場合、ストアドプロシージャ内の他のステートメントは必ずしもロールバックされるとは限りません。

トランザクションでストアドプロシージャを使用して、ステートメントのグループをアトミックにすることができます。詳細については、 ストアドプロシージャとトランザクション をご参照ください。

一般的なヒント

対称コード

アセンブリ言語でのプログラミングに精通している場合は、次の類推をご参照ください。アセンブリ言語では、関数は多くの場合、環境を対称的に作成および取り消します。例：

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

ストアドプロシージャでこのアプローチを使用できます。

  • ストアドプロシージャがセッションに一時的な変更を加えた場合、そのプロシージャは戻る前にそれらの変更を元に戻す必要があります。

  • ストアドプロシージャが例外処理や分岐、またはどのステートメントが実行されるかに影響を与える可能性のある他のロジックを利用する場合、特定の呼び出し中にどのブランチを取るかに関係なく、作成したものをクリーンアップする必要があります。

例えば、コードは以下に示す擬似コードのようになります。

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

行の継続

SQL ステートメントは非常に長くなる可能性があり、それらを1行に収めることが常に実用的ではありません。JavaScript は、改行をステートメントの終わりとして扱います。長い SQL ステートメントを複数の行に分割する場合、次のような通常の JavaScript テクニックを使用して長い文字列を処理できます。

  • 行の終わりの直前にバックスラッシュ（行継続文字）を置きます。例：

    var sql_command = "SELECT * \
                       FROM table1;";

  • 文字列を二重引用符で囲むのではなく、逆引用符（一重の逆引用符）を使用します。例：

    var sql_command = `SELECT *
                       FROM table1;`;

  • 文字列を累積します。例：

    var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

ストアドプロシージャの呼び出し

ストアドプロシージャを実行するには、 CALL ステートメントを使用します。例：

call stproc1(5.14::FLOAT);

ストアドプロシージャの各引数は、一般的な式にすることができます。

CALL stproc1(2 * 5.14::FLOAT);

引数はサブクエリにすることができます。

CALL stproc1(SELECT COUNT(*) FROM stproc_test_table1);

CALL ステートメントごとに1つのストアドプロシージャのみを呼び出すことができます。例えば、次のステートメントは失敗します。

call proc1(1), proc2(2);                          -- Not allowed

また、ストアドプロシージャ CALL を式の一部として使用することはできません。例えば、次のステートメントはすべて失敗します。

call proc1(1) + proc1(2);                         -- Not allowed
call proc1(1) + 1;                                -- Not allowed
call proc1(proc2(x));                             -- Not allowed
select * from (call proc1(1));                    -- Not allowed

ただし、ストアドプロシージャ内では、ストアドプロシージャは別のストアドプロシージャを呼び出すことも、再帰的に呼び出すこともできます。このトピック内の 例 セクションに例が記載されています。

ご用心

ネストされた呼び出しは、許可される最大スタック深度を超える可能性があるため、呼び出しをネストするとき、特に再帰を使用するときは注意してください。

権限

ストアドプロシージャは、2種類の権限を利用します。

  • ストアドプロシージャ自体に対する直接の権限。

  • ストアドプロシージャがアクセスするデータベースオブジェクト（テーブルなど）の権限。

ストアドプロシージャの権限

他のデータベースオブジェクト（テーブル、ビュー、 UDFsなど）と同様に、ストアドプロシージャはロールによって所有され、他のロールに付与できる1つ以上の権限を持っています。

現在、次の権限がストアドプロシージャに適用されます。

  • USAGE

  • OWNERSHIP

ロールがストアドプロシージャを使用するには、ロールが所有者であるか、ストアドプロシージャに対する USAGE 権限が付与されている必要があります。

ストアドプロシージャがアクセスするデータベースオブジェクトの権限

この件は、 呼び出し元権限と所有者権限のストアドプロシージャの理解 で説明されています。

ストアドプロシージャの考慮事項

JavaScript 数値範囲

精度がそのままの数値の範囲は

-(2^53 -1)

から

(2^53 -1)

Snowflake NUMBER（p、s）および DOUBLE データ型の有効な値の範囲が大きくなりました。Snowflakeから値を取得して JavaScript 数値変数に格納すると、精度が失われる可能性があります。例：

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

最初の2つの列は一致し、3番目の列は0.0を含む必要があります。

この問題は、 JavaScript ユーザー定義関数（UDFs）およびストアドプロシージャに適用されます。

getColumnValue() を使用しているときにストアドプロシージャで問題が発生した場合、次のように値を文字列として取得することで問題を回避できる可能性があります。

getColumnValueAsString()

その後、ストアドプロシージャから文字列を返し、文字列を SQLの数値データ型にキャストできます。

JavaScript のエラー処理

ストアドプロシージャは JavaScriptで記述されているため、 JavaScriptのtry/catch構文を使用できます。

ストアドプロシージャは、事前定義の例外またはカスタム例外をスローできます。カスタム例外をスローする簡単な例は、 こちら です。

SQL ステートメントは、tryブロック内で実行できます。エラーが発生した場合は、catchブロックにより、すべてのステートメントをロールバックできます（ステートメントをトランザクションに配置した場合）。例セクションには、 ストアドプロシージャ でトランザクションをロールバックする例が含まれています。

ストアドプロシージャの制限

ストアドプロシージャには次の制限があります。

  • JavaScript コードは、 JavaScript eval() 関数を呼び出すことができません。

  • JavaScript ストアドプロシージャは、標準 JavaScript ライブラリへのアクセスをサポートします。これにより、ブラウザーが通常提供する多くのオブジェクトとメソッドが除外されます。追加のライブラリをインポートする、含める、または呼び出すメカニズムはありません。サードパーティライブラリを許可すると、セキュリティホールが生じる可能性があります。

  • JavaScript コードは制限されたエンジン内で実行され、 JavaScript コンテキストからのシステムコール（例: ネットワークおよびディスクアクセスなし）を防ぎ、エンジンで使用できるシステムリソース、特にメモリを制限します。

  • ストアドプロシージャはネストと再帰を許可しますが、現在の最大スタック深度は約8であり、コールチェーン内の個々のストアドプロシージャが大量のリソースを消費する場合は、それより小さくなる可能性があります。

  • まれに、同時に多くのストアドプロシージャを呼び出すと、デッドロックが発生する可能性があります。

JavaScript 引数の大文字と小文字の区別

引数名は、ストアドプロシージャコードの SQL 部分では大文字と小文字を区別しませんが、JavaScript部分では大文字と小文字を区別します。

JavaScriptを使用するストアドプロシージャ（および UDFs）の場合、ステートメントの SQL 部分の識別子（引数名など）は自動的に大文字に変換されます（二重引用符で識別子を区切らない限り） JavaScript 部分は元のケースのままになります。これにより、引数が表示されないため、明示的なエラーメッセージを返さずにストアドプロシージャが失敗する可能性があります。

以下は、 JavaScript コードの引数の名前が SQL コードの引数の名前と一致しないストアドプロシージャの例です。大文字と小文字が異なるだけです。

次の例では、名前 argument1 が小文字であるため、最初の割り当てステートメントは正しくありません。

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

SQL ステートメントと JavaScript コードで一貫して大文字の識別子（特に引数名）を使用すると、サイレントエラーが減少する傾向があります。

JavaScript 区切り記号

ストアドプロシージャコードの JavaScript 部分は、一重引用符 ' または二重ドル記号 $$ で囲む必要があります。

$$ を使用すると、一重引用符を含む JavaScript コードを、それらの引用符を「エスケープ」せずに処理しやすくなります。

変数のバインド

変数を SQL ステートメントにバインドすると、ステートメントで変数の値を使用できます。

NULL の値とNULL の値をバインドできます。

変数のデータ型は、 SQL ステートメントの値の使用に適している必要があります。現在、型番号、文字列、および SfDate の JavaScript 変数のみをバインドできます。（ SQL データ型と JavaScriptデ ータ型間のマッピングの詳細については、 SQL および JavaScript データ型マッピング を参照。）

バインドの簡単な例を次に示します。

var stmt = snowflake.createStatement(
   {
   sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
   binds:["LiteralValue1", variable2]
   }
);

以下に、より完全な例を示します。この例では、 TIMESTAMP 情報をバインドします。 SQL TIMESTAMP データの直接バインドはサポートされていないため、この例ではタイムスタンプを VARCHARとして渡し、それをステートメントにバインドします。 SQL ステートメント自体は、 TO_TIMESTAMP（）関数を呼び出して VARCHAR を TIMESTAMP に変換することに注意してください。

この単純な関数は、指定されたタイムスタンプが現在より前であれば TRUE を返し、そうでなければ FALSE を返します。

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+

これは、 VARCHAR、 TIMESTAMP_LTZ、およびその他のデータ型を INSERT ステートメントにバインドする方法を示しています。 TIMESTAMP_LTZ は、ストアドプロシージャ内で作成された SfDate 変数をバインドします。

テーブルを作成します。

CREATE TABLE table1 (v VARCHAR,
                     ts1 TIMESTAMP_LTZ(9), 
                     int1 INTEGER,
                     float1 FLOAT,
                     numeric1 NUMERIC(10,9),
                     ts_ntz1 TIMESTAMP_NTZ,
                     date1 DATE,
                     time1 TIME
                     );

ストアドプロシージャを作成します。このプロシージャは VARCHAR を受け入れ、 SQLを使用して VARCHAR を TIMESTAMP_LTZ に変換します。その後、プロシージャは ResultSetから変換された値を取得します。値は、タイプ SfDate の JavaScript 変数に保存されます。ストアドプロシージャは、元の VARCHAR と TIMESTAMP_LTZ の両方を INSERT ステートメントにバインドします。これは、 JavaScript 数値データのバインドも示しています。

CREATE OR REPLACE PROCEDURE string_to_timestamp_ltz(TSV VARCHAR) 
RETURNS TIMESTAMP_LTZ 
LANGUAGE JAVASCRIPT 
AS 
$$ 
    // Convert the input varchar to a TIMESTAMP_LTZ.
    var sql_command = "SELECT '" + TSV + "'::TIMESTAMP_LTZ;"; 
    var stmt = snowflake.createStatement( {sqlText: sql_command} ); 
    var resultSet = stmt.execute(); 
    resultSet.next(); 
    // Retrieve the TIMESTAMP_LTZ and store it in an SfDate variable.
    var my_sfDate = resultSet.getColumnValue(1); 

    f = 3.1415926;

    // Specify that we'd like position-based binding.
    sql_command = `INSERT INTO table1 VALUES(:1, :2, :3, :4, :5, :6, :7, :8);` 
    // Bind a VARCHAR, a TIMESTAMP_LTZ, a numeric to our INSERT statement.
    result = snowflake.execute(
        { 
        sqlText: sql_command, 
        binds: [TSV, my_sfDate, f, f, f, my_sfDate, my_sfDate, '12:30:00.123' ] 
        }
        ); 

    return my_sfDate; 
$$ ;

プロシージャを呼び出します。

CALL string_to_timestamp_ltz('2008-11-18 16:00:00');
+-------------------------------+
| STRING_TO_TIMESTAMP_LTZ       |
|-------------------------------|
| 2008-11-18 16:00:00.000 -0800 |
+-------------------------------+

行が挿入されたことを確認します。

SELECT * FROM table1;
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
| V                   | TS1                           | INT1 |   FLOAT1 |    NUMERIC1 | TS_NTZ1                 | DATE1      | TIME1    |
|---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------|
| 2008-11-18 16:00:00 | 2008-11-18 16:00:00.000 -0800 |    3 | 3.141593 | 3.141593000 | 2008-11-18 16:00:00.000 | 2008-11-18 | 12:30:00 |
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+

JavaScriptのデータバインドのその他の例については、 ステートメントパラメーターのバインド をご参照ください。

コード要件

ストアドプロシージャを有効にするには、 JavaScript コードで単一のリテラル JavaScript オブジェクトを定義する必要があります。

JavaScript コードがこの要件を満たさない場合、ストアドプロシージャが作成されます。ただし、呼び出されると失敗します。

ランタイムエラー

ストアドプロシージャのほとんどのエラーは、実行時に表示されます。これは、 JavaScript コードが、ストアドプロシージャの作成時ではなく、ストアドプロシージャの実行時に解釈されるためです。

動的な SQLのサポート

ストアドプロシージャを使用して、 SQL ステートメントを動的に構築できます。例えば、事前に構成された SQL とユーザー入力（ユーザーのアカウント番号など）が混在する SQL コマンド文字列を作成できます。

例については、 SQL ステートメントの動的な作成 および 例 セクションをご参照ください。

同期 API

Snowflakeストアドプロシージャの API は同期的です。ストアドプロシージャ内では、一度に1つのスレッドのみを実行できます。

これは、非同期スレッドを実行できるNode.jsコネクターで実行する JavaScript のルールとは異なります。

SQL インジェクション

ストアドプロシージャは、 SQL ステートメントを動的に作成して実行できます。ただし、これにより、特にパブリックまたは信頼できないソースからの入力を使用して SQL ステートメントを作成する場合、 SQL インジェクション攻撃が可能になります。

テキストを連結するのではなく、パラメーターをバインドすることにより、 SQL インジェクション攻撃のリスクを最小限に抑えることができます。変数のバインドの例については、 変数のバインド をご参照ください。

連結を使用することを選択した場合は、パブリックソースからの入力を使用して SQL を動的に構築するときに入力を慎重に確認する必要があります。また、権限が制限されているロール（読み取り専用アクセス、特定のテーブルまたはビューへのアクセスなど）を使用したクエリなど、他の予防措置を講じることもできます。

SQL インジェクション攻撃の詳細については、 SQL インジェクション （Wikipedia）をご参照ください。

ストアドプロシージャの設計のヒント

ストアドプロシージャを設計するためのヒントを次に示します。

  • このストアドプロシージャには、どのようなリソース（例：テーブル）が必要ですか。

  • どの権限が必要ですか。

    アクセスするデータベースオブジェクト、ストアドプロシージャを実行するロール、およびそれらのロールに必要な権限を検討します。

    プロシージャを呼び出し元権限ストアドプロシージャにする必要がある場合は、その特定のプロシージャ、または関連するプロシージャのグループのいずれかを実行するロールを作成できます。その後、必要な権限をそのロールに付与し、適切なユーザーにそのロールを付与できます。

  • ストアドプロシージャは、呼び出し元権限または所有者権限で実行する必要がありますか。このトピックの詳細については、 呼び出し元権限と所有者権限のストアドプロシージャの理解 をご参照ください。

  • プロシージャはどのようにエラーを処理する必要がありますか。たとえば、必要なテーブルが欠落している場合、または引数が無効な場合、プロシージャは何をする必要がありますか。

  • ストアドプロシージャは、例えばログテーブルに書き込むことによって、アクティビティまたはエラーをログに記録する必要がありますか？

  • ストアドプロシージャを使用する場合と UDF を使用する場合についての説明もご参照ください： ストアドプロシージャまたは UDF の作成を選択します。

ストアドプロシージャの文書化

ストアドプロシージャは通常、再利用されるように記述されており、多くの場合、共有されます。ストアドプロシージャを文書化すると、ストアドプロシージャの使用と保守が容易になります。

以下は、ストアドプロシージャを文書化するための一般的な推奨事項です。

通常、ストアドプロシージャについて知りたいユーザーが少なくとも2人います。

  • ユーザー/呼び出し元。

  • プログラマー/作成者。

ユーザー（およびプログラマー）の場合、次のそれぞれを文書化します。

  • ストアドプロシージャの名前。

  • ストアドプロシージャ（データベースとスキーマ）の「場所」。

  • ストアドプロシージャの目的。

  • 各入力パラメーターの名前、データ型、および意味。

  • 戻り値の名前、データ型、および意味。戻り値がサブフィールドを含む VARIANT などの複合型の場合、それらのサブフィールドを文書化します。

  • ストアドプロシージャがその環境の情報（セッション変数やセッションパラメーターなど）に依存している場合は、それらの名前、目的、および有効な値を文書化します。

  • 返されたエラー、スローされた例外など。

  • プロシージャを実行するために必要なロールまたは権限。（このトピックの詳細については、 ストアドプロシージャの設計のヒント のロールの説明をご参照ください。）

  • プロシージャが呼び出し元権限プロシージャであるか、所有者権限プロシージャであるか。

  • 前提条件。例えば、プロシージャが呼び出される前に存在する必要のあるテーブル。

  • 作成される新しいテーブルなど、いずれかの出力（戻り値以外）。

  • 権限の変更、古いデータの削除などの「副作用」。ほとんどのストアドプロシージャ（関数とは異なります）は、戻り値ではなく、副作用のために特別に呼び出されます。したがって、それらの効果を文書化していることを確認してください。

  • ストアドプロシージャの実行後にクリーンアップが必要な場合は、そのクリーンアップを文書化します。

  • プロシージャを複数ステートメントのトランザクションの一部として（ AUTOCOMMIT=FALSE を使用して）呼び出すことができるかどうか、またはトランザクションの外部で（ AUTOCOMMIT=TRUE を使用して）実行する必要があるかどうか。

  • 呼び出しの例および戻り値の例。

  • 制限（該当する場合）。例えば、プロシージャがテーブルを読み取り、テーブルの各行の情報を含む VARIANT を返すとします。 VARIANT が VARIANT の最大有効サイズよりも大きくなる可能性があるため、呼び出し元に、プロシージャがアクセスするテーブル内の最大行数を知らせる必要がある場合があります。

  • 警告（該当する場合）。

  • トラブルシューティングのヒント。

プログラマーの場合：

  • 作成者。

  • プロシージャが呼び出し元権限プロシージャまたは所有者権限プロシージャとして作成された理由を説明してください。理由は明らかではない可能性があります。

  • ストアドプロシージャはネストできますが、ネストの深さに制限があります。ストアドプロシージャが他のストアドプロシージャを呼び出し、それ自体が他のストアドプロシージャによって呼び出される可能性がある場合、ストアドプロシージャの呼び出しスタックの既知の最大深度を指定できます。そのため、呼び出し元は、ストアドプロシージャの呼び出しが最大呼び出しスタック深度を超える可能性があるかどうかをある程度把握できます。

  • デバッグのヒント。

この情報の場所と形式はユーザー次第です。たとえば、内部ウェブサイトに HTML 形式で情報を保存できます。保存先を決定する前に、組織が他の製品の類似情報、またはビュー、ユーザー定義関数などの他のSnowflake機能の類似情報をどこに保存するかを考えます。

その他のヒント：

  • ほとんどすべてのソースコードに必要なコメントをソースコードに含めます。

    • コードからのリバースエンジニアリングの意味は難しいことを忘れないでください。アルゴリズムの仕組みだけでなく、そのアルゴリズムの目的も説明してください。

  • ストアドプロシージャでは、 CREATE PROCEDURE または ALTER PROCEDURE ステートメントで指定できるオプションの COMMENT を使用できます。他の人は SHOW PROCEDURES コマンドを実行してこのコメントを読むことができます。

  • 実用的な場合は、各ストアドプロシージャの CREATE PROCEDURE コマンドのマスターコピーをソースコード管理システムに保存することを検討してください。SnowflakeのTime Travel機能はストアドプロシージャには適用されないため、Snowflakeの外部で古いバージョンのストアドプロシージャを検索する必要があります。ソースコード管理システムが利用できない場合は、テーブルの VARCHAR フィールドに CREATE PROCEDURE コマンドを保存し、新しいバージョンを（古いバージョンを置き換えずに）追加することにより、一部をシミュレートできます。

  • ストアドプロシージャに関する情報の提供に役立つ命名規則の使用を検討します。たとえば、名前のプレフィックスまたはサフィックスは、プロシージャが呼び出し元権限ストアドプロシージャであるか、所有者権限ストアドプロシージャであるかを示す場合があります。（たとえば、呼び出し元権限のプレフィックスとして cr_ を使用できます。）

  • コメントだけでなく、入力引数のデータ型と順序を表示するには、 SHOW PROCEDURES コマンドを使用できます。ただし、これは引数の名前とデータ型のみを表示することに注意してください。引数については説明しません。

  • 適切な権限がある場合は、 DESCRIBE PROCEDURE コマンドを使用して以下を確認できます。

    • 引数の名前とデータ型。

    • プロシージャの本体、およびプロシージャが所有者または呼び出し元として実行されるかどうか。

    • 戻り値のデータ型。

    • その他の有用な情報。

例

基本的な例

次の例は、ストアドプロシージャを作成して呼び出す基本的な構文を示しています。SQL または手続き型コードは実行されません。ただし、後でより現実的な例の開始点を提供します。

create or replace procedure sp_pi()
    returns float not null
    language javascript
    as
    $$
    return 3.1415926;
    $$
    ;

$$ 区切り文字は JavaScript コードの開始と終了を示します。

作成したプロシージャを呼び出します。

CALL sp_pi();
+-----------+
|     SP_PI |
|-----------|
| 3.1415926 |
+-----------+

次の例は、ストアドプロシージャ内で SQL ステートメントを実行する方法を示しています。

  1. テーブルを作成します。

    CREATE TABLE stproc_test_table1 (num_col1 numeric(14,7));

  2. ストアドプロシージャを作成します。これにより、 stproc_test_table1 という名前の既存テーブルに行が挿入され、「成功」の値が返されます。返される値は SQL の観点からは特に有用ではありませんが、ユーザーにステータス情報を返すことができます（「成功」または「失敗」など）。

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    execute as owner
    as
    $$
    var sql_command = 
     "INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$
    ;

  3. ストアドプロシージャを呼び出します。

    call stproc1(5.14::FLOAT);
+------------+
| STPROC1    |
|------------|
| Succeeded. |
+------------+

  4. ストアドプロシージャが行を挿入したことを確認します。

    select * from stproc_test_table1;
+-----------+
|  NUM_COL1 |
|-----------|
| 5.1400000 |
+-----------+

次の例では、結果を取得します。

  1. テーブル内の行数をカウントするプロシージャを作成します（ select count(*) from テーブル と同等）：

    create or replace procedure get_row_count(table_name VARCHAR)
  returns float not null
  language javascript
  as
  $$
  var row_count = 0;
  // Dynamically compose the SQL statement to execute.
  var sql_command = "select count(*) from " + TABLE_NAME;
  // Run the statement.
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();
  // Get back the row count. Specifically, ...
  // ... get the first (and in this case only) row from the result set ...
  res.next();
  // ... and then get the returned value, which in this case is the number of
  // rows in the table.
  row_count = res.getColumnValue(1);
  return row_count;
  $$
  ;

  2. テーブル内の行数をストアドプロシージャに問い合わせます。

    call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+

  3. 適切な番号を取得したことを個別に確認します。

    select count(*) from stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

再帰ストアドプロシージャの例

次の例は、基本的ですが、特に現実的ではない再帰ストアドプロシージャを示しています。

create or replace table stproc_test_table2 (col1 FLOAT);

create or replace procedure recursive_stproc(counter FLOAT)
    returns varchar not null
    language javascript
    as
    -- "$$" is the delimiter that shows the beginning and end of the stored proc.
    $$
    var counter1 = COUNTER;
    var returned_value = "";
    var accumulator = "";
    var stmt = snowflake.createStatement(
        {
        sqlText: "INSERT INTO stproc_test_table2 (col1) VALUES (?);",
        binds:[counter1]
        }
        );
    var res = stmt.execute();
    if (COUNTER > 0)
        {
        stmt = snowflake.createStatement(
            {
            sqlText: "call recursive_stproc (?);",
            binds:[counter1 - 1]
            }
            );
        res = stmt.execute();
        res.next();
        returned_value = res.getColumnValue(1);
        }
    accumulator = accumulator + counter1 + ":" + returned_value;
    return accumulator;
    $$
    ;

call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+

SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+

SQL ステートメントの動的な作成

次の例は、 SQL ステートメントを動的に作成する方法を示しています。

注釈

このトピック内の SQL インジェクション で述べたように、動的な SQLを使用するときは攻撃に注意してください。

  1. ストアドプロシージャを作成します。このプロシージャを使用すると、テーブルの名前を渡し、そのテーブルの行数を取得できます（ select count(*) from テーブル名 と同等）。

    create or replace procedure get_row_count(table_name VARCHAR)
    returns float 
    not null
    language javascript
    as
    $$
    var row_count = 0;
    // Dynamically compose the SQL statement to execute.
    // Note that we uppercased the input parameter name.
    var sql_command = "select count(*) from " + TABLE_NAME;
    // Run the statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );
    var res = stmt.execute();
    // Get back the row count. Specifically, ...
    // ... first, get the first (and in this case only) row from the
    //  result set ...
    res.next();
    // ... then extract the returned value (which in this case is the
    // number of rows in the table).
    row_count = res.getColumnValue(1);
    return row_count;
    $$
    ;

  2. ストアドプロシージャを呼び出します。

    call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+

  3. 同じテーブルの select count(*) からの結果を表示します。

    SELECT COUNT(*) FROM stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

結果セットのメタデータの取得

この例は、結果セットから少量のメタデータを取得する方法を示しています。

create or replace table stproc_test_table3 (
    n10 numeric(10,0),     /* precision = 10, scale = 0 */
    n12 numeric(12,4),     /* precision = 12, scale = 4 */
    v1 varchar(19)         /* scale = 0 */
    );

create or replace procedure get_column_scale(column_index float)
    returns float not null
    language javascript
    as
    $$
    var stmt = snowflake.createStatement(
        {sqlText: "select n10, n12, v1 from stproc_test_table3;"}
        );
    stmt.execute();  // ignore the result set; we just want the scale.
    return stmt.getColumnScale(COLUMN_INDEX); // Get by column index (1-based)
    $$
    ;

call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+

call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+

Try/Catchを使用したエラーのキャッチ

この例では、 JavaScript try/catchブロックを使用して、ストアドプロシージャ内のエラーをキャッチします。

  1. ストアドプロシージャを作成します。

        create procedure broken()
      returns varchar not null
      language javascript
      as
      $$
      var result = "";
      try {
          snowflake.execute( {sqlText: "Invalid Command!;"} );
          result = "Succeeded";
          }
      catch (err)  {
          result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
          result += "\n  Message: " + err.message;
          result += "\nStack Trace:\n" + err.stackTraceTxt; 
          }
      return result;
      $$
      ;

  2. ストアドプロシージャを呼び出します。これにより、エラー番号とその他の情報を示すエラーが返されます。

        -- This is expected to fail.
    call broken();
+---------------------------------------------------------+
| BROKEN                                                  |
|---------------------------------------------------------|
| Failed: Code: 100183                                    |
|   State: P0000                                          |
|   Message: SQL compilation error:                       |
| syntax error line 1 at position 0 unexpected 'Invalid'. |
| Stack Trace:                                            |
| At Snowflake.execute, line 4 position 20                |
+---------------------------------------------------------+

次の例は、カスタム例外のスローを示しています。

  1. ストアドプロシージャを作成します。

    CREATE OR REPLACE PROCEDURE validate_age (age float)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    try {
        if (AGE < 0) {
            throw "Age cannot be negative!";
        } else {
            return "Age validated.";
        }
    } catch (err) {
        return "Error: " + err;
    }
$$;

  2. 有効な値と無効な値を使用してストアドプロシージャを呼び出します。

    CALL validate_age(50);
+----------------+
| VALIDATE_AGE   |
|----------------|
| Age validated. |
+----------------+
CALL validate_age(-2);
+--------------------------------+
| VALIDATE_AGE                   |
|--------------------------------|
| Error: Age cannot be negative! |
+--------------------------------+

ストアドプロシージャでのトランザクションの使用

次の例では、トランザクション内の複数の関連ステートメントをラップし、try/catchを使用してコミットまたはロールバックします。パラメーター force_failure を使用すると、呼び出し元は実行の成功と意図的なエラーのどちらかを選択できます。

-- Create the procedure
create or replace procedure cleanup(force_failure varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "BEGIN WORK;"} );
  try {
      snowflake.execute( {sqlText: "DELETE FROM child;"} );
      snowflake.execute( {sqlText: "DELETE FROM parent;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "DELETE FROM no_such_table;"} );
          }
      snowflake.execute( {sqlText: "COMMIT WORK;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "ROLLBACK WORK;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

call cleanup('fail');

call cleanup('do not fail');

エラーのログ

次の例では、エラーをテーブルに記録します。より現実的な例では、おそらくエラーが発生した時刻のタイムスタンプも含めることになります（CURRENT_TIMESTAMP 関数を使用）。

CREATE OR REPLACE TABLE error_log (error_code number, error_state string, error_message string, stack_trace string);

CREATE OR REPLACE PROCEDURE broken() 
RETURNS varchar 
NOT NULL 
LANGUAGE javascript 
AS $$
var result;
try {
    snowflake.execute({ sqlText: "Invalid Command!;" });
    result = "Succeeded";
} catch (err) {
    result = "Failed";
    snowflake.execute({
      sqlText: `insert into error_log VALUES (?,?,?,?)`
      ,binds: [err.code, err.state, err.message, err.stackTraceTxt]
      });
}
return result;
$$;

call broken();
+--------+
| BROKEN |
|--------|
| Failed |
+--------+

select * from error_log;
+------------+-------------+---------------------------------------------------------+------------------------------------------+
| ERROR_CODE | ERROR_STATE | ERROR_MESSAGE                                           | STACK_TRACE                              |
|------------+-------------+---------------------------------------------------------+------------------------------------------|
|     100183 | P0000       | SQL compilation error:                                  | At Snowflake.execute, line 4 position 14 |
|            |             | syntax error line 1 at position 0 unexpected 'Invalid'. |                                          |
+------------+-------------+---------------------------------------------------------+------------------------------------------+

エラーのログ（バージョン2）

これは、ストアドプロシージャを使用して仮テーブルにメッセージをログする方法を示しています。この例では、呼び出し元がログテーブル名を指定できるようにし、テーブルがまだ存在しない場合は作成します。この例では、呼び出し元がログのオンとオフを簡単に切り替えることもできます。

また、これらのストアドプロシージャの1つは、再利用できる小さな JavaScript 関数を作成することに注意してください。反復コードを使用する長いストアドプロシージャでは、ストアドプロシージャ内に JavaScript 関数を作成すると便利です。

プロシージャを作成します。

CREATE or replace PROCEDURE do_log(MSG STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$
 
    // See if we should log - checks for session variable do_log = true.
    try {
       var stmt = snowflake.createStatement( { sqlText: `select $do_log` } ).execute();
    } catch (ERROR){
       return ''; //swallow the error, variable not set so don't log
    }
    stmt.next();
    if (stmt.getColumnValue(1)==true){ //if the value is anything other than true, don't log
       try {
           snowflake.createStatement( { sqlText: `create temp table identifier ($log_table) if not exists (ts number, msg string)`} ).execute();
           snowflake.createStatement( { sqlText: `insert into identifier ($log_table) values (:1, :2)`, binds:[Date.now(), MSG] } ).execute();
       } catch (ERROR){
           throw ERROR;
       }
    }
 $$
;

CREATE or replace PROCEDURE my_test()
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS $$

    var accumulated_log_messages = '';

    // Define the SP call as a function - it's cleaner this way.
    // Add this function to your stored procs.
    function log(msg) {
        snowflake.createStatement( { sqlText: `call do_log(:1)`, binds:[msg] } ).execute();
        }

    try {
        accumulated_log_messages += 'log this message\n';
        var x = 10/10;
        accumulated_log_messages += 'x = ' + x.toString() + '\n';
        //do some stuff here
        x = no_such_function();   // Force an error so that we catch it.
    } catch(ERROR) {
        log(accumulated_log_messages);
        log(ERROR.message); //we can even catch/log the error messages
        return ERROR.message;
    }

    if (accumulated_log_messages != '') {
        log(accumulated_log_messages)
        }

    return 'No error detected.';

    $$
;

ロギングをオンにします。

set do_log = true; --true to enable logging, false (or undefined) to disable
set log_table = 'my_log_table';  -- The name of the temp table where log messages go.

プロシージャを呼び出します。

CALL my_test();
+---------------------------------+
| MY_TEST                         |
|---------------------------------|
| no_such_function is not defined |
+---------------------------------+

テーブルが作成され、メッセージがログに記録されたことを確認します。

select msg 
    from my_log_table 
    order by 1;
+---------------------------------+
| MSG                             |
|---------------------------------|
| log this message                |
| x = 1                           |
|                                 |
| no_such_function is not defined |
+---------------------------------+

重要

テーブルにログメッセージを個別に挿入すると、コストがかかり非効率になる可能性があります。特に、コードが処理された行ごとに、1つ以上のメッセージをログに記録する場合はそうです。

さらに、多くのストアドプロシージャが同時に実行され、それぞれが同じログテーブルに多くのメッセージを追加する場合は、同時実行のボトルネックが発生する可能性があります。

これらの潜在的な問題を回避するために、上記のサンプルコードは、ストアドプロシージャが終了するまで（またはエラーがキャッチされるまで）メッセージを文字列に蓄積し、蓄積されたメッセージを単一の INSERT に書き込みます。

ストアドプロシージャ名のオーバーロード

このトピック内の ストアドプロシージャの命名規則 で説明されているように、ストアドプロシージャ名をオーバーロードできます。例：

  1. 最初に、名前は同じでも引数の数が異なる2つのストアドプロシージャを作成します。

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    as
    $$
    return FLOAT_PARAM1;
    $$
    ;

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT, FLOAT_PARAM2 FLOAT)
    returns string
    language javascript
    strict
    as
    $$
    return FLOAT_PARAM1 * FLOAT_PARAM2;
    $$
    ;

  2. 次に、2つのプロシージャを呼び出します。

    call stproc1(5.14::FLOAT);
+---------+
| STPROC1 |
|---------|
| 5.14    |
+---------+

    call stproc1(5.14::FLOAT, 2.00::FLOAT);
+---------+
| STPROC1 |
|---------|
| 10.28   |
+---------+

注釈

また、ストアドプロシージャに対する他の操作の引数のデータ型を指定する必要があります。例えば、 GRANT および REVOKE には、引数型とストアドプロシージャ名が必要です。

次のオーバーロードの例は、オーバーロードと自動型変換を組み合わせて、予期しない結果を簡単に得る方法を示しています。

FLOAT パラメーターを受け取るストアドプロシージャを作成します。

CREATE PROCEDURE add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + 3.1415926;
    $$;

ストアドプロシージャを2回呼び出します。1回目は、 FLOATを渡します。2回目は、 VARCHARを渡します。 VARCHAR は FLOATに変換され、各呼び出しからの出力は同じです。

CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+

次に、 VARCHAR パラメーターを受け取るオーバーロードストアドプロシージャを作成します。

CREATE PROCEDURE add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + '3.1415926';
    $$;

前とまったく同じ CALLs を使用します。これら2つの CALLs と前の2つの CALLsの出力の違いに注意してください。

CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+--------------+
| ADD_PI       |
|--------------|
| 1.03.1415926 |
+--------------+

RESULT_SCAN を使用してストアドプロシージャから結果を取得する

この例は、 RESULT_SCAN 関数を使用して CALL ステートメントから結果を取得する方法を示しています。

  1. テーブルを作成してロードします。

    CREATE TABLE western_provinces(ID INT, province VARCHAR);

    INSERT INTO western_provinces(ID, province) VALUES
    (1, 'Alberta'),
    (2, 'British Columbia'),
    (3, 'Manitoba')
    ;

  2. ストアドプロシージャを作成します。このプロシージャは、3行の結果セットのように見える適切にフォーマットされた文字列を返しますが、実際は単一の文字列です。

    CREATE OR REPLACE PROCEDURE read_western_provinces()
  RETURNS VARCHAR NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var return_value = "";
  try {
      var command = "SELECT * FROM western_provinces ORDER BY province;"
      var stmt = snowflake.createStatement( {sqlText: command } );
      var rs = stmt.execute();
      if (rs.next())  {
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      while (rs.next())  {
          return_value += "\n";
          return_value += rs.getColumnValue(1);
          return_value += ", " + rs.getColumnValue(2);
          }
      }
  catch (err)  {
      result =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      result += "\n  Message: " + err.message;
      result += "\nStack Trace:\n" + err.stackTraceTxt;
      }
  return return_value;
  $$
  ;

  3. ストアドプロシージャを呼び出してから、 RESULT_SCAN を使用して結果を取得します。

    CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+

RESULT_SCAN 関数によって返された値に対して、より複雑な操作を実行できます。この場合、返される値は単一の文字列であるため、その文字列に含まれているように見える個々の「行」を抽出し、それらの行を別のテーブルに保存することができます。

前の例の続きである次の例は、これを行う1つの方法を示しています。

  1. 長期保存用のテーブルを作成します。このテーブルには、 CALL コマンドによって返された文字列から抽出した後の州名と州 ID が含まれます。

    CREATE TABLE all_provinces(ID INT, province VARCHAR);

  2. ストアドプロシージャを呼び出し、 RESULT_SCAN を使用して結果を取得し、文字列から3行を抽出して、それらの行をテーブルに配置します。

    INSERT INTO all_provinces
  WITH 
    one_string (string_col) AS
      (SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))),
    three_strings (one_row) AS
      (SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n'))
  SELECT
         STRTOK(one_row, ',', 1) AS ID,
         STRTOK(one_row, ',', 2) AS province
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

  3. テーブルに行を表示して、これが機能したことを確認します。

    SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+

これはほぼ同じコードですが、手順は次のとおりです。

  1. one_string という名前のテーブルを作成します。このテーブルは、 CALL コマンドの結果を一時的に保存します。 CALL の結果は単一の文字列なので、このテーブルには単一の VARCHAR 値のみが保存されます。

    CREATE TRANSIENT TABLE one_string(string_col VARCHAR);

  2. ストアドプロシージャを呼び出し、 RESULT_SCANを使用して結果（文字列）を取得し、それを one_string という名前の中間テーブルに保存します。

    CALL read_western_provinces();
+------------------------+
| READ_WESTERN_PROVINCES |
|------------------------|
| 1, Alberta             |
| 2, British Columbia    |
| 3, Manitoba            |
+------------------------+
INSERT INTO one_string
    SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

    これにより、 one_string テーブルの新しい行が表示されます。これは3行のようにフォーマットされていますが、実際には単一の文字列です。

    SELECT string_col FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
-- Show that it's one string, not three rows:
SELECT '>>>' || string_col || '<<<' AS string_col 
    FROM one_string;
+---------------------+
| STRING_COL          |
|---------------------|
| >>>1, Alberta       |
| 2, British Columbia |
| 3, Manitoba<<<      |
+---------------------+
SELECT COUNT(*) FROM one_string;
+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

    次のコマンドは、文字列から複数の行を抽出する方法を示しています。

    SELECT * FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+-----+-------+---------------------+
| STRING_COL          | SEQ | INDEX | VALUE               |
|---------------------+-----+-------+---------------------|
| 1, Alberta          |   1 |     1 | 1, Alberta          |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     2 | 2, British Columbia |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
| 1, Alberta          |   1 |     3 | 3, Manitoba         |
| 2, British Columbia |     |       |                     |
| 3, Manitoba         |     |       |                     |
+---------------------+-----+-------+---------------------+
SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+---------------------+
| VALUE               |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+

  3. 次に、 three_strings という名前のテーブルを作成します。このテーブルは、個々の行/文字列に分割した後、結果を保持します。

    CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);

  4. one_string テーブルの1つの文字列を3つの個別の文字列に変換し、実際に3つの文字列であることを示します。

    INSERT INTO three_strings
  SELECT VALUE FROM one_string, LATERAL SPLIT_TO_TABLE(one_string.string_col, '\n');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT string_col 
    FROM three_strings;
+---------------------+
| STRING_COL          |
|---------------------|
| 1, Alberta          |
| 2, British Columbia |
| 3, Manitoba         |
+---------------------+
SELECT COUNT(*) 
    FROM three_strings;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

  5. ここで、 all_provinces という名前の長期テーブルで3つの文字列を3つの行に変換します。

    INSERT INTO all_provinces
  SELECT 
         STRTOK(string_col, ',', 1) AS ID, 
         STRTOK(string_col, ',', 2) AS province 
    FROM three_strings
    WHERE NOT (ID IS NULL AND province IS NULL);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+

  6. 長期テーブルの3行を表示します。

    SELECT ID, province 
    FROM all_provinces;
+----+-------------------+
| ID | PROVINCE          |
|----+-------------------|
|  1 |  Alberta          |
|  2 |  British Columbia |
|  3 |  Manitoba         |
+----+-------------------+
SELECT COUNT(*) 
    FROM all_provinces;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

エラーメッセージの配列を返す

ストアドプロシージャは複数の SQL ステートメントを実行する可能性があり、各 SQL ステートメントに対してステータス/エラーメッセージを返すことができます。ただし、ストアドプロシージャは単一の行を返します。複数の行を返すようには設計されていません。

すべてのメッセージが ARRAY型の単一の値に収まる場合、追加の労力でストアドプロシージャからすべてのメッセージを取得できます。

次の例は、これを行う1つの方法を示しています（表示されるエラーメッセージは実際のものではありませんが、実際の SQL ステートメントで動作するようにこのコードを拡張できます）。

CREATE OR REPLACE PROCEDURE sp_return_array()
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This array will contain one error message (or an empty string) 
      // for each SQL command that we executed.
      var array_of_rows = [];

      // Artificially fake the error messages.
      array_of_rows.push("ERROR: The foo was barred.")
      array_of_rows.push("WARNING: A Carrington Event is predicted.")

      return array_of_rows;
      $$
      ;

CALL sp_return_array();
+-----------------------------------------------+
| SP_RETURN_ARRAY                               |
|-----------------------------------------------|
| [                                             |
|   "ERROR: The foo was barred.",               |
|   "WARNING: A Carrington Event is predicted." |
| ]                                             |
+-----------------------------------------------+
-- Now get the individual error messages, in order.
SELECT INDEX, VALUE 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(INPUT => res.$1)
    ORDER BY index
    ;
+-------+---------------------------------------------+
| INDEX | VALUE                                       |
|-------+---------------------------------------------|
|     0 | "ERROR: The foo was barred."                |
|     1 | "WARNING: A Carrington Event is predicted." |
+-------+---------------------------------------------+

これは、汎用ソリューションでは ありません。ARRAY データ型の最大サイズには制限があり、結果セット全体が単一の ARRAY に収まる必要があります。

結果セットを返す

このセクションでは、 エラーメッセージの配列を返す で説明した例について詳しく説明します。この例はより一般的であり、クエリから結果セットを返すことができます。

ストアドプロシージャは、単一の列を含む単一の行を返します。結果セットを返すようには設計されていません。ただし、結果セットが VARIANT 型または ARRAY の単一の値に収まるほど小さい場合は、追加のコードを使用してストアドプロシージャから結果セットを返すことができます。

CREATE TABLE return_to_me(col_i INT, col_v VARCHAR);
INSERT INTO return_to_me (col_i, col_v) VALUES
    (1, 'Ariel'),
    (2, 'October'),
    (3, NULL),
    (NULL, 'Project');

-- Create the stored procedure that retrieves a result set and returns it.
CREATE OR REPLACE PROCEDURE sp_return_table(TABLE_NAME VARCHAR, COL_NAMES ARRAY)
      RETURNS VARIANT NOT NULL
      LANGUAGE JAVASCRIPT
      AS
      $$
      // This variable will hold a JSON data structure that holds ONE row.
      var row_as_json = {};
      // This array will contain all the rows.
      var array_of_rows = [];
      // This variable will hold a JSON data structure that we can return as
      // a VARIANT.
      // This will contain ALL the rows in a single "value".
      var table_as_json = {};

      // Run SQL statement(s) and get a resultSet.
      var command = "SELECT * FROM " + TABLE_NAME;
      var cmd1_dict = {sqlText: command};
      var stmt = snowflake.createStatement(cmd1_dict);
      var rs = stmt.execute();

      // Read each row and add it to the array we will return.
      var row_num = 1;
      while (rs.next())  {
        // Put each row in a variable of type JSON.
        row_as_json = {};
        // For each column in the row...
        for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
          var col_name = COL_NAMES[col_num];
          row_as_json[col_name] = rs.getColumnValue(col_num + 1);
          }
        // Add the row to the array of rows.
        array_of_rows.push(row_as_json);
        ++row_num;
        }
      // Put the array in a JSON variable (so it looks like a VARIANT to
      // Snowflake).  The key is "key1", and the value is the array that has
      // the rows we want.
      table_as_json = { "key1" : array_of_rows };

      // Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
      return table_as_json;
      $$
      ;

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
-- Use "ResultScan" to get the data from the stored procedure that
-- "did not return a result set".
-- Use "$1:key1" to get the value corresponding to the JSON key named "key1".
SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
+------------------------+
| $1:KEY1                |
|------------------------|
| [                      |
|   {                    |
|     "COL_I": 1,        |
|     "COL_V": "Ariel"   |
|   },                   |
|   {                    |
|     "COL_I": 2,        |
|     "COL_V": "October" |
|   },                   |
|   {                    |
|     "COL_I": 3,        |
|     "COL_V": null      |
|   },                   |
|   {                    |
|     "COL_I": null,     |
|     "COL_V": "Project" |
|   }                    |
| ]                      |
+------------------------+
-- Now get what we really want.
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS res, LATERAL FLATTEN(input => res.$1)
  ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

これは、前の2行を1行に結合する方法を示しています。

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT VALUE:COL_I AS col_i, value:COL_V AS col_v
       FROM (SELECT $1:key1 FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res,
            LATERAL FLATTEN(input => res.$1)
       ORDER BY COL_I;
+-------+-----------+
| COL_I | COL_V     |
|-------+-----------|
| 1     | "Ariel"   |
| 2     | "October" |
| 3     | null      |
| null  | "Project" |
+-------+-----------+

便宜上、前の行をビューでラップできます。このビューは、文字列「null」を真の NULL に変換します。ビューを作成する必要があるのは一度だけです。ただし、ビューを使用する際は 毎回 、このビューから選択する 直前に ストアドプロシージャを呼び出す 必要があります。ビュー内の RESULT_SCAN の呼び出しは、最新のステートメントから取得しています。これは CALLでなければなりません。

CREATE VIEW stproc_view (col_i, col_v) AS 
  SELECT NULLIF(VALUE:COL_I::VARCHAR, 'null'::VARCHAR), 
         NULLIF(value:COL_V::VARCHAR, 'null'::VARCHAR)
    FROM (SELECT $1:key1 AS tbl FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))) AS res, 
         LATERAL FLATTEN(input => res.tbl);

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT * 
    FROM stproc_view
    ORDER BY COL_I;
+-------+---------+
| COL_I | COL_V   |
|-------+---------|
| 1     | Ariel   |
| 2     | October |
| 3     | NULL    |
| NULL  | Project |
+-------+---------+

真のビューとして使用することもできます（そのサブセットを選択）。

CALL sp_return_table(
        -- Table name.
        'return_to_me',
        -- Array of column names.
        ARRAY_APPEND(TO_ARRAY('COL_I'), 'COL_V')
        );
+--------------------------+
| SP_RETURN_TABLE          |
|--------------------------|
| {                        |
|   "key1": [              |
|     {                    |
|       "COL_I": 1,        |
|       "COL_V": "Ariel"   |
|     },                   |
|     {                    |
|       "COL_I": 2,        |
|       "COL_V": "October" |
|     },                   |
|     {                    |
|       "COL_I": 3,        |
|       "COL_V": null      |
|     },                   |
|     {                    |
|       "COL_I": null,     |
|       "COL_V": "Project" |
|     }                    |
|   ]                      |
| }                        |
+--------------------------+
SELECT COL_V 
    FROM stproc_view
    WHERE COL_V IS NOT NULL
    ORDER BY COL_V;
+---------+
| COL_V   |
|---------|
| Ariel   |
| October |
| Project |
+---------+

これは、汎用ソリューションでは ありません。VARIANT および ARRAY データ型の最大サイズには制限があり、結果セット全体が単一の VARIANT または ARRAY に収まる必要があります。

プライバシーの保護

この例は、オンライン小売業者に役立つストアドプロシージャを示しています。このストアドプロシージャは、小売業者と顧客の両方の正当な利益を保護しながら、顧客のプライバシーを尊重します。顧客が小売業者にプライバシー上の理由で顧客データを削除するように依頼した場合、このストアドプロシージャは顧客データのほとんどを削除しますが、次のいずれかが当てはまる場合は顧客の購入履歴を残します。

  • 購入したアイテムに、まだ期限が切れていない保証が付いている場合。

  • 顧客が未払いの（または払い戻しをしている）場合。

これのより現実的なバージョンでは、支払いが行われ保証が期限切れになった個々の行を削除します。

  1. 最初に、テーブルを作成してロードします。

    create table reviews (customer_ID VARCHAR, review VARCHAR);
create table purchase_history (customer_ID VARCHAR, price FLOAT, paid FLOAT,
                               product_ID VARCHAR, purchase_date DATE);

    insert into purchase_history (customer_ID, price, paid, product_ID, purchase_date) values 
    (1, 19.99, 19.99, 'chocolate', '2018-06-17'::DATE),
    (2, 19.99,  0.00, 'chocolate', '2017-02-14'::DATE),
    (3, 19.99,  19.99, 'chocolate', '2017-03-19'::DATE);

insert into reviews (customer_ID, review) values (1, 'Loved the milk chocolate!');
insert into reviews (customer_ID, review) values (2, 'Loved the dark chocolate!');

  2. ストアドプロシージャを作成します。

    create or replace procedure delete_nonessential_customer_data(customer_ID varchar)
    returns varchar not null
    language javascript
    as
    $$

    // If the customer posted reviews of products, delete those reviews.
    var sql_cmd = "DELETE FROM reviews WHERE customer_ID = " + CUSTOMER_ID;
    snowflake.execute( {sqlText: sql_cmd} );

    // Delete any other records not needed for warranty or payment info.
    // ...

    var result = "Deleted non-financial, non-warranty data for customer " + CUSTOMER_ID;

    // Find out if the customer has any net unpaid balance (or surplus/prepayment).
    sql_cmd = "SELECT SUM(price) - SUM(paid) FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    var net_amount_owed = rs.getColumnValue(1);

    // Look up the number of purchases still under warranty...
    var number_purchases_under_warranty = 0;
    // Assuming a 1-year warranty...
    sql_cmd = "SELECT COUNT(*) FROM purchase_history ";
    sql_cmd += "WHERE customer_ID = " + CUSTOMER_ID;
    // Can't use CURRENT_DATE() because that changes. So assume that today is 
    // always June 15, 2019.
    sql_cmd += "AND PURCHASE_DATE > dateadd(year, -1, '2019-06-15'::DATE)";
    var stmt = snowflake.createStatement( {sqlText: sql_cmd} );
    var rs = stmt.execute();
    // There should be only one row, so should not need to iterate.
    rs.next();
    number_purchases_under_warranty = rs.getColumnValue(1);

    // Check whether need to keep some purchase history data; if not, then delete the data.
    if (net_amount_owed == 0.0 && number_purchases_under_warranty == 0)  {
        // Delete the purchase history of this customer ...
        sql_cmd = "DELETE FROM purchase_history WHERE customer_ID = " + CUSTOMER_ID;
        snowflake.execute( {sqlText: sql_cmd} );
        // ... and delete anything else that that should be deleted.
        // ...
        result = "Deleted all data, including financial and warranty data, for customer " + CUSTOMER_ID;
        }
    return result;
    $$
    ;

  3. そのデータを削除する前に、テーブル内のデータを表示します。

    SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 1           | Loved the milk chocolate! |
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

  4. 顧客1には、まだ有効な保証があります。ストアドプロシージャは、投稿したレビューコメントを削除しますが、保証の購入記録を保持します。

    call delete_nonessential_customer_data(1);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 1 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+---------------------------+
| CUSTOMER_ID | REVIEW                    |
|-------------+---------------------------|
| 2           | Loved the dark chocolate! |
+-------------+---------------------------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

  5. 顧客2には、未払い金があります。ストアドプロシージャはレビューコメントを削除しますが、購入記録を保持します。

    call delete_nonessential_customer_data(2);
+---------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                       |
|---------------------------------------------------------|
| Deleted non-financial, non-warranty data for customer 2 |
+---------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
| 3           | 19.99 | 19.99 | chocolate  | 2017-03-19    |
+-------------+-------+-------+------------+---------------+

  6. 顧客3には未払金がありません。（払い戻しもありません）。保証の期限が切れたため、ストアドプロシージャはレビューコメントと購入記録の両方を削除します。

    call delete_nonessential_customer_data(3);
+-------------------------------------------------------------------------+
| DELETE_NONESSENTIAL_CUSTOMER_DATA                                       |
|-------------------------------------------------------------------------|
| Deleted all data, including financial and warranty data, for customer 3 |
+-------------------------------------------------------------------------+
SELECT * FROM reviews;
+-------------+--------+
| CUSTOMER_ID | REVIEW |
|-------------+--------|
+-------------+--------+
SELECT * FROM purchase_history;
+-------------+-------+-------+------------+---------------+
| CUSTOMER_ID | PRICE |  PAID | PRODUCT_ID | PURCHASE_DATE |
|-------------+-------+-------+------------+---------------|
| 1           | 19.99 | 19.99 | chocolate  | 2018-06-17    |
| 2           | 19.99 |  0    | chocolate  | 2017-02-14    |
+-------------+-------+-------+------------+---------------+

呼び出し元権限と所有者権限のストアドプロシージャでのセッション変数の使用

これらの例は、呼び出し元権限と所有者権限のストアドプロシージャの主な違いの1つを示しています。例では、セッション変数を次の2つの方法で使用しようとします。

  • ストアドプロシージャを呼び出す前にセッション変数を設定し、ストアドプロシージャ内でセッション変数を使用します。

  • ストアドプロシージャ内でセッション変数を設定し、ストアドプロシージャから戻った後にセッション変数を使用します。

セッション変数の使用とセッション変数の設定の両方が、呼び出し元権限ストアドプロシージャで正しく機能します。呼び出し元が 所有者 であっても、所有者権限ストアドプロシージャを使用すると、両方とも失敗します。

呼び出し元権限ストアドプロシージャ

次の例は、呼び出し元権限ストアドプロシージャを示しています。

  1. テーブルを作成してロードします。

    create table sv_table (f float);
insert into sv_table (f) values (49), (51);

  2. セッション変数を設定します。

    set SESSION_VAR1 = 50;

  3. 1つのセッション変数を使用し、別のセッション変数を設定する呼び出し元権限ストアドプロシージャを作成します。

    create procedure session_var_user()
  returns float
  language javascript
  EXECUTE AS CALLER
  as
  $$
  // Set the second session variable
  var stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  var rs = stmt.execute();  // we ignore the result in this case
  // Run a query using the first session variable
  stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  rs = stmt.execute();
  rs.next();
  var output = rs.getColumnValue(1);
  return output;
  $$
  ;

  4. プロシージャを呼び出します。

    CALL session_var_user();
+------------------+
| SESSION_VAR_USER |
|------------------|
|               51 |
+------------------+

  5. ストアドプロシージャ内で設定されたセッション変数の値を表示します。

    SELECT $SESSION_VAR2;
+------------------------------+
| $SESSION_VAR2                |
|------------------------------|
| I was set inside the StProc. |
+------------------------------+

注釈

ストアドプロシージャ内でセッション変数を設定し、プロシージャの終了後に設定したままにしておくことはできますが、Snowflakeはそれは 推奨しません。

所有者権限ストアドプロシージャ

次の例は、所有者権限ストアドプロシージャを示しています。

  1. セッション変数を使用する所有者権限ストアドプロシージャを作成します。

    create procedure cannot_use_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Run a query using the first session variable
  var stmt = snowflake.createStatement(
      {sqlText: "select f from sv_table where f > $SESSION_VAR1"}
      );
  var rs = stmt.execute();
  rs.next();
  var output = rs.getColumnValue(1);
  return output;
  $$
  ;

  2. プロシージャを呼び出します（失敗するはずです）：

    100183 (P0000): Execution error in store procedure CANNOT_USE_SESSION_VARS:
Use of session variable '$SESSION_VAR1' is not allowed in owners rights stored procedure
At Statement.execute, line 6 position 16

  3. セッション変数を設定しようとする所有者権限ストアドプロシージャを作成します。

    create procedure cannot_set_session_vars()
  returns float
  language javascript
  EXECUTE AS OWNER
  as
  $$
  // Set the second session variable
  var stmt = snowflake.createStatement(
      {sqlText: "set SESSION_VAR2 = 'I was set inside the StProc.'"}
      );
  var rs = stmt.execute();  // we ignore the result in this case
  return 3.0;   // dummy value.
  $$
  ;

  4. プロシージャを呼び出します（失敗するはずです）：

    100183 (P0000): Execution error in store procedure CANNOT_SET_SESSION_VARS:
Stored procedure execution error: Unsupported statement type 'SET'.
At Statement.execute, line 6 position 16

トラブルシューティング

一般的なトラブルシューティングの手法は、JavaScript try/catchブロックを使用してエラーをキャッチし、エラー情報を表示することです。エラーオブジェクトには次が含まれます。

  • エラーコード。

  • エラーメッセージ。

  • エラー状態。

  • 失敗点でのスタックトレース。

この情報の使用方法の例を含む詳細については、このトピック内の Try/Catchを使用したエラーのキャッチ をご参照ください。

次のセクションでは、特定の問題のデバッグに役立つ追加の提案について説明します。

ストアドプロシージャまたは UDF が予期せず返される NULL

原因

ストアドプロシージャ/UDF にはパラメーターがあり、プロシージャ/UDF内では、パラメーターは小文字の名前で参照されますが、Snowflakeは名前を自動的に大文字に変換しました。

解決策

次のいずれかを実行します。

  • JavaScript コード内の変数名に大文字を使用します。

  • SQL コードで変数名を二重引用符で囲みます。

詳細については、 JavaScript 引数と戻り値 をご参照ください。

ストアドプロシージャの実行が終了しない

原因

JavaScript コードに無限ループがある可能性があります。

解決策

無限ループを確認して修正します。

エラー： Failed: empty argument passed

原因

ストアドプロシージャに含まれるべき「sqlText」が「sqltext」になっている可能性があります（1番目は大文字と小文字が混在、2番目はすべて小文字）。

解決策

「sqlText」を使用します。

エラー： JavaScript out of memory error: UDF thread memory limit exceeded

原因

JavaScript コードに無限ループがある可能性があります。

解決策

無限ループを確認して修正します。特に、結果セットがなくなったら（ resultSet.next() が false を返すと）次の行の呼び出しを停止してください。