JavaScript로 저장 프로시저 작성하기

이 항목에서는 저장 프로시저에 대한 JavaScript 코드를 작성하는 방법에 대해 설명합니다.

참고

익명 프로시저를 만들기도 하고 호출도 하려면 CALL(익명 프로시저 사용) 를 사용하십시오. 익명 프로시저를 만들고 호출하는 데는 CREATE PROCEDURE 스키마 권한이 있는 역할이 필요하지 않습니다.

이 항목의 내용:

처리기 코드가 실행될 때 로그 및 추적 데이터를 캡처할 수 있습니다. 자세한 내용은 로깅 및 추적 개요 섹션을 참조하십시오.

JavaScript API 이해하기

저장 프로시저용 JavaScript API는 Snowflake 커넥터 및 드라이버(Node.js, JDBC, Python 등)의 API와 유사하지만 동일하지는 않습니다.

API를 사용해 다음과 같은 작업을 수행할 수 있습니다.

  • SQL 문을 실행합니다.

  • 쿼리 결과(즉, 결과 세트) 불러오기.

  • 결과 세트에 대한 메타데이터(열 수, 열의 데이터 타입 등) 불러오기.

이러한 작업은 다음 오브젝트에서 메서드를 호출하여 수행됩니다.

  • snowflake: Statement 오브젝트를 만들고 SQL 명령을 실행하는 메서드를 가진 오브젝트.

  • Statement: 준비된 문을 실행하고 이러한 문에 대한 메타데이터에 액세스하는 데 도움이 되며 ResultSet 오브젝트를 다시 가져올 수 있게 해주는 오브젝트.

  • ResultSet: 쿼리 결과(예: SELECT 문에 대해 검색된 데이터 행)를 보유한 오브젝트.

  • SfDate: JavaScript Date의 확장(추가 메서드 포함)으로, Snowflake SQL 데이터 타입인 TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ에 대한 반환 형식 역할을 하는 오브젝트.

이러한 오브젝트는 JavaScript 저장 프로시저 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();
Copy

이 코드는 선언되지 않은 상태로 존재하는 특수한 오브젝트인 snowflake 로 명명된 오브젝트를 사용합니다. 이 오브젝트는 각 저장 프로시저의 컨텍스트 내에서 제공되며 서버와 상호 작용할 수 있게 해주는 API를 표시합니다.

다른 변수(예: statement1)는 JavaScript var 문으로 생성됩니다. 예:

var statement1 = ...;
Copy

위의 코드 샘플에 나타낸 것처럼, snowflake 오브젝트를 사용하면 API의 메서드 중 하나를 호출하여 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.
  $$
  ;
Copy

(이 항목의 끝에 있는) 예 섹션에서는 각 오브젝트를 실행하는 추가적인 예와 저장 프로시저 JavaScript API에 있는 많은 메서드를 제공합니다.

SQL 및 JavaScript 데이터 타입 매핑

저장 프로시저를 호출하고 사용하고 이러한 저장 프로시저에서 값을 다시 가져올 때, Snowflake SQL 데이터 타입에서 JavaScript 데이터 타입으로 변환하거나 그 반대로 변환해야 할 경우가 많습니다.

SQL에서 JavaScript로의 변환은 다음과 같은 경우에 발생할 수 있습니다.

  • 인자를 사용해 저장 프로시저를 호출할 경우. 인자는 SQL 데이터 타입으로, 저장 프로시저 내에서 JavaScript 변수 내부에 저장될 때 변환되어야 합니다.

  • ResultSet 오브젝트에서 JavaScript 변수로 값을 불러올 경우. ResultSet는 값을 SQL 데이터 타입으로 보유하고, JavaScript 변수는 값을 JavaScript 데이터 타입 중 하나로 저장해야 합니다.

JavaScript에서 SQL로의 변환은 다음과 같은 경우에 발생할 수 있습니다.

  • 저장 프로시저에서 값을 반환할 경우. return 문은 일반적으로 SQL 데이터 타입으로 변환해야 하는 JavaScript 변수를 포함합니다.

  • JavaScript 변수의 값을 사용하는 SQL 문을 동적으로 생성할 경우.

  • JavaScript 변수의 값을 준비된 문에 바인딩할 경우.

Snowflake가 JavaScript 및 SQL 데이터 타입을 매핑하는 방법에 대한 자세한 내용은 SQL-JavaScript 데이터 타입 매핑 섹션을 참조하십시오.

일반적인 팁

줄 연속

SQL 문은 상당히 길 수 있으므로, 항상 단 한 줄로 맞출 수 있는 것은 아닙니다. JavaScript는 줄 바꿈을 문의 끝으로 취급합니다. 긴 SQL 문을 여러 줄로 분할하려면 다음을 포함하여 긴 문자열을 처리하는 일반적인 JavaScript 기술을 사용할 수 있습니다.

  • 줄 끝 바로 앞에 백슬래시(줄 연속 문자)를 넣습니다. 예:

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

  • 문자열 주위에 큰따옴표가 아니라 백틱(단일 역따옴표)를 사용합니다. 예:

    var sql_command = `SELECT *
                       FROM table1;`;
    Copy

  • 문자열을 누적합니다. 예:

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

JavaScript 저장 프로시저 고려 사항

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;
$$
;
Copy
select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+
Copy

처음 두 열은 일치해야 하고 세 번째 열은 0.0을 포함해야 합니다.

이 문제는 JavaScript 사용자 정의 함수(UDF) 및 저장 프로시저에 적용됩니다.

getColumnValue() 를 사용할 때 저장 프로시저에서 문제가 발생하는 경우, 예를 들어 다음을 통해 값을 문자열로 검색하여 문제를 피할 수 있습니다.

getColumnValueAsString()
Copy

그런 다음 저장 프로시저에서 문자열을 반환하고 문자열을 SQL의 숫자 데이터 타입으로 캐스팅할 수 있습니다.

JavaScript 오류 처리

저장 프로시저는 JavaScript로 작성되므로 JavaScript의 try/catch 구문을 사용할 수 있습니다.

저장 프로시저는 미리 정의된 예외 또는 사용자 지정 예외를 발생시킬 수 있습니다. 사용자 지정 예외를 발생시키는 간단한 예가 여기 에 있습니다.

try 블록 내에서 SQL 문을 실행할 수 있습니다. 오류가 발생하면 catch 블록이 모든 문을 롤백할 수 있습니다(트랜잭션에 문을 넣은 경우). 예 섹션에 저장 프로시저에서 트랜잭션을 롤백 하는 예가 있습니다.

저장 프로시저에 대한 제한 사항

저장 프로시저는 다음과 같은 제한 사항이 있습니다.

  • JavaScript 코드는 JavaScript eval() 함수를 호출할 수 없습니다.

  • JavaScript 저장 프로시저는 표준 JavaScript 라이브러리에 대한 액세스를 지원합니다. 여기에는 브라우저에서 일반적으로 제공하는 많은 오브젝트와 메서드가 제외됩니다. 추가 라이브러리를 가져오거나 포함하거나 호출하는 메커니즘이 없습니다. 서드 파티 라이브러리를 허용하면 보안상의 허점이 생길 수 있습니다.

  • JavaScript 코드는 제한된 엔진 내에서 실행되어 JavaScript 컨텍스트에서 시스템 호출을 방지하고(예: 네트워크 및 디스크 액세스 없음) 엔진, 특히 메모리에서 사용할 수 있는 시스템 리소스를 제한합니다.

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

SQL 문과 JavaScript 코드에서 대문자 식별자(특히 인자 이름)를 일관되게 사용하면 감지하기 어려운 오류가 감소하는 경향이 있습니다.

JavaScript 인자의 대/소문자 구분

인자 이름은 저장 프로시저 코드의 SQL 부분에서 대/소문자를 구분하지 않지만, JavaScript 부분에서는 대/소문자를 구분합니다.

JavaScript 구분 기호

저장 프로시저 코드의 JavaScript 부분은 작은따옴표 ' 또는 이중 달러 기호 $$ 로 묶어야 합니다.

$$ 를 사용하면 작은따옴표를 “이스케이프”하지 않고 작은따옴표를 포함한 JavaScript 코드를 더 쉽게 처리할 수 있습니다.

저장 프로시저 이름 오버로드하기

오버로드 및 명명 규칙에 대한 자세한 내용은 프로시저와 UDF의 명명 및 오버로딩 섹션을 참조하십시오.

변수 바인딩하기

SQL 문에 변수를 바인딩 하면 문에서 변수의 값을 사용할 수 있습니다.

NULL 값과 NULL이 아닌 값을 모두 바인딩할 수 있습니다.

변수의 데이터 타입은 SQL 문에서 값을 사용하기에 알맞아야 합니다. 현재는 숫자, 문자열, SfDate 형식의 JavaScript 변수만 바인딩할 수 있습니다. (SQL 데이터 타입과 JavaScript 데이터 타입 간의 매핑에 대한 자세한 내용은 SQL 및 JavaScript 데이터 타입 매핑 을 참조하십시오.)

다음은 바인딩의 간단한 예입니다.

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

다음은 더욱더 완전한 예입니다. 이 예에서는 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);
$$
;
Copy
CALL right_bind('2019-09-16 01:02:03');
+------------+
| RIGHT_BIND |
|------------|
| True       |
+------------+
Copy

다음은 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
                     );
Copy

저장 프로시저를 만듭니다. 이 프로시저는 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; 
$$ ;
Copy

프로시저를 호출합니다.

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

행이 삽입되었는지 확인합니다.

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 |
+---------------------+-------------------------------+------+----------+-------------+-------------------------+------------+----------+
Copy

JavaScript의 데이터를 바인딩하는 추가적인 예는 문 매개 변수 바인딩하기 를 참조하십시오.

코드 요구 사항

JavaScript 코드는 저장 프로시저가 유효하도록 단일 리터럴 JavaScript 오브젝트를 정의해야 합니다.

JavaScript 코드가 이 요구 사항을 충족하지 못할 경우 저장 프로시저가 생성되지만 호출 시 실패합니다.

코드 크기

Snowflake에서는 JavaScript 저장 프로시저의 본문에서 JavaScript 소스 코드의 최대 크기를 제한합니다. Snowflake에서는 이 크기를 100KB로 제한할 것을 권장합니다. (코드는 압축된 형태로 저장되며 정확한 제한은 코드의 압축률에 따라 다릅니다.)

런타임 오류

저장 프로시저의 오류는 대부분 런타임에 표시되는데, 이는 저장 프로시저가 생성될 때가 아니라 실행될 때 JavaScript 코드가 해석되기 때문입니다.

동적 SQL 지원

저장 프로시저는 SQL 문을 동적으로 생성하는 데 사용할 수 있습니다. 예를 들어, 미리 구성된 SQL 입력값과 사용자 입력값(예: 사용자 계정 번호)이 섞여 있는 SQL 명령 문자열을 작성할 수 있습니다.

예를 들어, 동적으로 SQL 문 만들기 및 예 섹션을 참조하십시오.

동기 API

Snowflake 저장 프로시저용 API는 동기식입니다. 저장 프로시저 내에서는 한 번에 하나의 스레드만 실행할 수 있습니다.

이는 비동기 스레드를 실행할 수 있게 해주는 Node.js 커넥터로 실행하는 JavaScript의 규칙과 다른 점입니다.

예

기본 예

다음은 저장 프로시저를 만들고 호출하는 기본 구문을 보여주는 예입니다. 이 구문이 SQL 또는 프로시저 코드를 실행하는 것은 아닙니다. 하지만 나중에 더욱 현실적인 예를 위한 시작점이 됩니다.

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

$$ 구분 기호는 JavaScript 코드의 시작과 끝을 표시합니다.

이제 방금 만든 프로시저를 호출합니다.

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

다음 예에서는 저장 프로시저 내에서 SQL 문을 실행하는 방법을 보여줍니다.

  1. 테이블을 만듭니다.

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

  2. 저장 프로시저를 만듭니다. 이 저장 프로시저는 stproc_test_table1 로 명명된 기존 테이블에 행을 삽입하고 “Succeeded.” 값을 반환합니다. 반환된 값이 SQL 관점에서 특별히 유용한 것은 아니지만, 이 값을 통해 상태 정보(예: “Succeeded.” 또는 “Failed.”)를 사용자에게 반환할 수 있습니다.

    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.
        }
    $$
    ;
    Copy

  3. 저장 프로시저를 호출합니다.

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

  4. 저장 프로시저가 행을 삽입했는지 확인합니다.

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

다음은 결과를 불러오는 예입니다.

  1. 테이블의 행 수를 계산하는 프로시저를 만듭니다(select count(*) from table 과 같음).

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

  2. 테이블에 몇 개의 행이 있는지 저장 프로시저에 묻습니다.

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

  3. 올바른 수가 나왔는지 따로 확인하십시오.

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

재귀적 저장 프로시저의 예

다음 예에서는 기본적이지만 특별히 현실적이지는 않은 재귀적 저장 프로시저를 보여줍니다.

create or replace table stproc_test_table2 (col1 FLOAT);
Copy
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;
    $$
    ;
Copy
call recursive_stproc(4.0::FLOAT);
+------------------+
| RECURSIVE_STPROC |
|------------------|
| 4:3:2:1:0:       |
+------------------+
Copy
SELECT * 
    FROM stproc_test_table2
    ORDER BY col1;
+------+
| COL1 |
|------|
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
+------+
Copy

동적으로 SQL 문 만들기

다음 예에서는 동적으로 SQL 문을 만드는 방법을 보여줍니다.

참고

(이 항목의) SQL 주입 에서 언급하는 바와 같이, 동적 SQL을 사용할 때 공격으로부터 보호하는 데 주의를 기울이십시오.

  1. 저장 프로시저를 만듭니다. 이 프로시저를 통해 테이블 이름을 전달하고 해당 테이블의 행 수를 얻을 수 있습니다(select count(*) from table_name 과 같음).

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

  2. 저장 프로시저를 호출합니다.

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

  3. 같은 테이블에 대해 select count(*) 의 결과를 표시합니다.

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

결과 집합 메타데이터 불러오기

다음은 결과 세트에서 소량의 메타데이터를 불러오는 방법을 보여주는 예입니다.

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 */
    );
Copy
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)
    $$
    ;
Copy
call get_column_scale(1);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
Copy
call get_column_scale(2);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                4 |
+------------------+
Copy
call get_column_scale(3);
+------------------+
| GET_COLUMN_SCALE |
|------------------|
|                0 |
+------------------+
Copy

Try/Catch를 사용해 오류 Catch하기

다음은 JavaScript try/catch 블록을 사용해 저장 프로시저 내부의 오류를 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;
      $$
      ;
    Copy

  2. 저장 프로시저를 호출합니다. 이 저장 프로시저는 오류 번호와 기타 정보를 보여주는 오류를 반환해야 합니다.

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

다음은 사용자 지정 예외를 발생시키는 방법을 보여주는 예입니다.

  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;
    }
$$;
    Copy

  2. 유효한 값과 유효하지 않은 값으로 저장 프로시저를 호출합니다.

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

저장 프로시저에서 트랜잭션 사용하기

다음은 트랜잭션에서 여러 관련된 문을 래핑하고 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');
Copy

오류 기록하기

JavaScript API의 snowflake 오브젝트를 사용하여 JavaScript 처리기 코드에서 로그 및 추적 데이터를 캡처할 수 있습니다. 이 작업을 수행할 때 쿼리로 분석할 수 있는 이벤트 테이블에 로그 메시지와 추적 데이터가 저장됩니다.

자세한 내용은 다음을 참조하십시오.

RESULT_SCAN을 사용해 저장 프로시저에서 결과 불러오기

다음은 RESULT_SCAN 함수를 사용해 CALL 문의 결과를 불러오는 방법을 보여주는 예입니다.

  1. 테이블을 만들고 로딩합니다.

    CREATE TABLE western_provinces(ID INT, province VARCHAR);
    Copy
    INSERT INTO western_provinces(ID, province) VALUES
    (1, 'Alberta'),
    (2, 'British Columbia'),
    (3, 'Manitoba')
    ;
    Copy

  2. 저장 프로시저를 만듭니다. 이 프로시저는 세 행의 결과 세트처럼 보이지만 실제로는 단일 문자열인 올바른 형식의 문자열을 반환합니다.

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

  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            |
+------------------------+
    Copy

RESULT_SCAN 함수에서 반환된 값에 대해 더 복잡한 작업을 수행할 수 있습니다. 이 경우, 반환되는 값은 단일 문자열이므로 그 문자열에 포함된 것으로 나타나는 개별 “행”을 추출하고 그러한 행을 다른 테이블에 저장할 수 있습니다.

이전 예에서 계속 이어지는 다음 예에서는 이 작업을 수행하는 한 가지 방법을 보여줍니다.

  1. 장기간 저장하기 위한 테이블을 만듭니다. 이 테이블에는 CALL 명령에서 반환된 문자열에서 추출한 후의 지역 이름과 지역 ID가 있습니다.

    CREATE TABLE all_provinces(ID INT, province VARCHAR);
    Copy

  2. 저장 프로시저를 호출하고 RESULT_SCAN을 사용해 결과를 불러온 다음, 문자열에서 세 개의 행을 추출해 테이블에 넣습니다.

    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 |
+-------------------------+
    Copy

  3. 테이블에 있는 행을 표시하여 이 작업이 제대로 수행되었는지 확인합니다.

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

다음은 대체로 같은 코드지만, 더 세부적인 단계로 되어 있습니다.

  1. one_string 이라는 테이블을 만듭니다. 이 테이블은 CALL 명령의 결과를 임시로 저장합니다. CALL의 결과는 단일 문자열이므로, 이 테이블은 단일 VARCHAR 값만 저장합니다.

    CREATE TRANSIENT TABLE one_string(string_col VARCHAR);
    Copy

  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 |
+-------------------------+
    Copy

    다음은 one_string 테이블에 새 행을 표시하는 예입니다. 세 행처럼 보이도록 형식이 지정되어 있지만, 실제로는 단일 문자열이라는 점을 기억하십시오.

    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 |
+----------+
    Copy

    다음 명령은 문자열에서 여러 행을 추출하는 방법을 보여줍니다.

    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         |
+---------------------+
    Copy

  3. 다음으로, three_strings 로 명명된 테이블을 만듭니다. 이 테이블은 개별 줄/문자열로 분할한 후 결과를 보유합니다.

    CREATE TRANSIENT TABLE three_strings(string_col VARCHAR);
    Copy

  4. one_string 테이블의 한 문자열을 세 개의 개별 문자열로 변환하고, 그 문자열이 실제로는 세 개의 문자열임을 표시합니다.

    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 |
+----------+
    Copy

  5. 이제 all_provinces 로 명명된 장기 테이블에서 세 문자열을 세 행으로 변환합니다.

    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 |
+-------------------------+
    Copy

  6. 장기 테이블에 세 행을 표시합니다.

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

오류 메시지 배열 반환하기

저장 프로시저가 둘 이상의 SQL 문을 실행할 수 있고 각 SQL 문에 대한 상태/오류 메시지를 반환할 수 있습니다. 하지만 저장 프로시저는 단일 행을 반환할 뿐, 여러 행을 반환하도록 설계되지 않았습니다.

모든 메시지가 ARRAY 형식의 단일 값에 맞는 경우 추가 작업을 통해 저장 프로시저에서 모든 메시지를 가져올 수 있습니다.

다음 예에서 이 작업을 수행하는 한 가지 방법을 보여줍니다(표시된 오류 메시지는 실제 오류 메시지가 아니지만, 실제 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;
      $$
      ;
Copy
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." |
+-------+---------------------------------------------+
Copy

이것은 일반적인 해결책은 아니라는 점에 유의하십시오. 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');
Copy
-- 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;
      $$
      ;
Copy
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" |
+-------+-----------+
Copy

다음은 이전의 두 줄을 한 줄로 결합하는 방법을 보여주는 예입니다.

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" |
+-------+-----------+
Copy

편의상, 뷰에서 이전의 줄을 줄 바꿈할 수 있습니다. 이 뷰는 또한 ‘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);
Copy
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 |
+-------+---------+
Copy

이를 실제 뷰로 사용할 수도 있습니다(즉, 그 서브세트 선택).

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 |
+---------+
Copy

이것은 일반적인 해결책은 아니라는 점에 유의하십시오. 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);
    Copy
    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!');
    Copy

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

  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    |
+-------------+-------+-------+------------+---------------+
    Copy

  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    |
+-------------+-------+-------+------------+---------------+
    Copy

  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    |
+-------------+-------+-------+------------+---------------+
    Copy

  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    |
+-------------+-------+-------+------------+---------------+
    Copy

호출자 권한 및 소유자 권한 저장 프로시저에서 세션 변수 사용하기

다음 예에서는 호출자 권한 저장 프로시저와 소유자 권한 저장 프로시저 간의 주요 차이점 중 하나를 보여줍니다. 다음 두 가지 방법으로 세션 변수 사용을 시도합니다.

  • 저장 프로시저를 호출하기 전에 세션 변수를 설정한 다음, 그 세션 변수를 저장 프로시저 내에서 사용합니다.

  • 저장 프로시저 내에서 세션 변수를 설정한 다음, 저장 프로시저에서 반환 후 세션 변수를 사용합니다.

세션 변수 사용 및 세션 변수 설정 동작이 모두 호출자 권한 저장 프로시저에서 올바로 작동합니다. 호출자가 소유자 라 하더라도 소유자 권한 저장 프로시저를 사용할 때 둘 다 실패합니다.

호출자 권한 저장 프로시저

다음 예에서는 호출자 권한 저장 프로시저를 보여줍니다.

  1. 테이블을 만들고 로딩합니다.

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

  2. 세션 변수를 설정합니다.

    set SESSION_VAR1 = 50;
    Copy

  3. 한 세션 변수를 사용하고 다른 세션 변수를 설정하는 호출자 권한 저장 프로시저를 만듭니다.

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

  4. 프로시저를 호출합니다.

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

  5. 저장 프로시저 내에 설정된 세션 변수의 값을 봅니다.

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

참고

저장 프로시저 내에서 세션 변수를 설정하고 프로시저가 끝난 후 세션 변수를 설정된 상태로 남겨둘 수 있지만, 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;
  $$
  ;
    Copy

  2. 프로시저를 호출합니다(호출에 실패할 것임).

    CALL cannot_use_session_vars();
    Copy

  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.
  $$
  ;
    Copy

  4. 프로시저를 호출합니다(호출에 실패할 것임).

    CALL cannot_set_session_vars();
    Copy

문제 해결

일반적인 문제 해결 기법은 JavaScript try/catch 블록을 사용해 오류를 catch하고 오류 정보를 표시하는 것입니다. 오류 오브젝트는 다음을 포함합니다.

  • 오류 코드.

  • 오류 메시지입니다.

  • 오류 상태.

  • 실패 지점의 스택 추적.

예를 포함하여, 이 정보의 사용법에 대한 자세한 내용은 (이 항목에 있는) Try/Catch를 사용해 오류 Catch하기 를 참조하십시오.

다음 섹션에서는 특정 문제를 디버그하는 데 도움이 되는 추가 제안 사항을 제시합니다.

저장 프로시저 또는 UDF가 예기치 않게 NULL 반환

원인:

저장 프로시저/UDF에 매개 변수가 있고 프로시저/UDF 내에서 이 매개 변수는 소문자 이름으로 참조되지만, Snowflake가 자동으로 이름을 대문자로 변환했습니다.

해결책:

다음 중 하나를 수행합니다.

  • JavaScript 코드 내에서 변수 이름에 대문자를 사용합니다.

  • 또는 SQL 코드에서 변수 이름을 큰따옴표로 묶습니다.

자세한 내용은 JavaScript 인자 및 반환 값 섹션을 참조하십시오.

저장 프로시저가 실행을 마치지 않음

원인:

JavaScript 코드에 무한 루프가 있을 수 있습니다.

해결책:

무한 루프가 있는지 확인하고 있으면 수정하십시오.

오류: Failed: empty argument passed

원인:

저장 프로시저에서 “sqlText”가 있어야 하는 곳에 “sqltext”가 있을 수 있습니다(즉, 대/소문자를 혼합 사용해야 하는 곳에서 모두 소문자를 사용한 경우).

해결책:

“sqlText”를 사용하십시오.

오류: JavaScript out of memory error: UDF thread memory limit exceeded

원인:

JavaScript 코드에 무한 루프가 있을 수 있습니다.

해결책:

무한 루프가 있는지 확인하고 있으면 수정하십시오. 특히, 결과 세트가 소진될 때(즉, resultSet.next() 가 false 를 반환할 때) 다음 행에 대해 호출을 중지해야 합니다.

언어: 한국어