Snowflake Scripting에서 저장 프로시저 작성하기¶

이 항목에서는 Snowflake Scripting을 사용하여 저장 프로시저를 SQL로 작성하는 방법에 대해 설명합니다.

이 항목의 내용:

소개¶

Snowflake Scripting을 사용하는 저장 프로시저를 작성하는 방법은 다음과 같습니다.

LANGUAGE SQL과 함께 CREATE PROCEDURE 또는 WITH … CALL … 명령을 사용합니다.
저장 프로시저의 본문에서(AS 절) Snowflake Scripting 블록 을 사용합니다.

참고

SnowSQL 또는 Classic Console 에서 Snowflake Scripting 프로시저를 생성하는 경우 저장 프로시저의 본문 주위에 문자열 리터럴 구분 기호 (' 또는 $$)를 사용해야 합니다.

자세한 내용은 SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 섹션을 참조하십시오.

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

다음 사항을 참고하십시오.

호출자 권한 vs. 소유자 권한 에 대한 동일한 규칙이 이러한 저장 프로시저에 적용됩니다.
저장 프로시저 작업하기 의 동일한 고려 사항 및 지침이 Snowflake Scripting 저장 프로시저에 적용됩니다.

다음은 전달된 인자의 값을 반환하는 간단한 저장 프로시저의 예입니다.

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

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

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

Copy

다음은 저장 프로시저를 호출하는 예입니다.

CALL output_message('Hello World');

Copy

다음은 WITH … CALL … 명령을 사용하여 익명 저장 프로시저를 생성하고 호출하는 예입니다.

WITH anonymous_output_message AS PROCEDURE (message VARCHAR)
    RETURNS VARCHAR NOT NULL
    LANGUAGE SQL
    AS
    $$
    BEGIN
      RETURN message;
    END;
    $$
  CALL anonymous_output_message('Hello World');

Copy

익명 저장 프로시저에서는 프로시저 본문 주위에 문자열 리터럴 구분 기호 (' 또는 $$)를 사용해야 합니다.

저장 프로시저에 전달된 인자 사용하기¶

저장 프로시저에 인자를 전달하면 Snowflake Scripting 식에서 이름으로 해당 인자를 참조할 수 있습니다. 더 자세한 내용은 다음 섹션을 참조하십시오.

저장 프로시저에 전달된 인자를 사용하는 간단한 예
SQL 문에서 인자 사용하기(바인딩)
인자를 오브젝트 식별자로 사용하기
SQL 문의 문자열을 작성할 때 인자 사용하기

저장 프로시저에 전달된 인자를 사용하는 간단한 예¶

다음 저장 프로시저는 IF 및 RETURN 문에 이러한 인자의 값을 사용합니다.

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  IF (number_1 > number_2) THEN
    RETURN number_1;
  ELSE
    RETURN number_2;
  END IF;
END;
$$
;

Copy

다음은 저장 프로시저를 호출하는 예입니다.

CALL return_greater(2, 3);

Copy

SQL 문에서 인자 사용하기(바인딩)¶

Snowflake Scripting 변수를 사용할 때와 마찬가지로, SQL 문에서 인자를 사용해야 할 경우 인자 이름 앞에 콜론(:)을 넣습니다. (SQL 문에서 변수 사용하기(바인딩) 섹션을 참조하십시오.)

예를 들어 다음 저장 프로시저에서는 SELECT 문의 WHERE 절에서 id 인자를 사용합니다. WHERE 절에서는 인자가 :id 로 지정됩니다.

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

다음은 저장 프로시저를 호출하는 예입니다.

CALL find_invoice_by_id('2');

Copy

인자를 오브젝트 식별자로 사용하기¶

오브젝트를 참조하는 인자를 사용해야 하는 경우(예: SELECT 문의 FROM 절에 있는 테이블 이름), IDENTIFIER 키워드를 사용하여 인자가 오브젝트 식별자를 표시함을 나타냅니다. 예:

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
  c1 CURSOR FOR res;
BEGIN
  FOR row_variable IN c1 DO
    row_count := row_variable.count;
  END FOR;
  RETURN row_count;
END;
$$
;

Copy

다음은 저장 프로시저를 호출하는 예입니다.

CALL get_row_count('invoices');

Copy

SQL 문의 문자열을 작성할 때 인자 사용하기¶

SQL 문을 EXECUTE IMMEDIATE 에 전달할 문자열로 작성할 경우(선언된 RESULTSET에 쿼리 할당하기 참조), 인자 앞에 콜론을 추가하지 마십시오. 예:

CREATE OR REPLACE PROCEDURE find_invoice_by_id_via_execute_immediate(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  select_statement VARCHAR;
  res RESULTSET;
BEGIN
  select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
  res := (EXECUTE IMMEDIATE :select_statement);
  RETURN TABLE(res);
END;

Copy

테이블 형식 데이터 반환하기¶

저장 프로시저에서 테이블 형식 데이터(예: RESULTSET의 데이터)를 반환해야 하는 경우 CREATE PROCEDURE 문에 RETURNS TABLE(…)을 지정합니다.

반환된 테이블에 있는 열의 Snowflake 데이터 타입 을 알면 RETURNS TABLE()에서 열 이름과 유형을 지정하십시오.

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...

Copy

그렇지 않으면(예: 런타임 중에 열 유형을 결정하는 경우) 열 이름과 유형을 생략할 수 있습니다.

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
...

Copy

참고

현재, RETURNS TABLE(...) 절에서는 GEOGRAPHY를 열 유형으로 지정할 수 없습니다. 이는 저장 프로시저를 생성하든 익명 프로시저를 생성하든 관계없이 적용됩니다.

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

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();

Copy

GEOGRAPHY를 열 유형으로 지정하려고 할 경우 저장 프로시저를 호출하면 오류가 발생합니다.

Stored procedure execution error: data type of returned table does not match expected returned table type

Copy

이 문제를 해결하려면 RETURNS TABLE() 에서 열 인자와 유형을 생략하면 됩니다.

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

Copy

WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();

Copy

RESULTSET의 데이터를 반환해야 할 경우 RETURN 문에서 TABLE()을 사용합니다.

예:

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

다음은 저장 프로시저를 호출하는 예입니다.

CALL get_top_sales();

Copy

다른 저장 프로시저에서 저장 프로시저 호출하기¶

저장 프로시저에서 다른 저장 프로시저를 호출해야 하는 경우 다음 접근 방법 중 하나를 사용합니다.

반환된 값을 사용하지 않고 저장 프로시저 호출하기
저장 프로시저 호출에서 반환된 값 사용하기

반환된 값을 사용하지 않고 저장 프로시저 호출하기¶

(일반적으로 호출하는 것처럼) CALL 문을 사용하여 저장 프로시저를 호출합니다.

CALL 문에서 변수나 인자를 입력 인자로 전달해야 하는 경우 변수 이름 앞에 콜론(:)을 사용해야 합니다. (SQL 문에서 변수 사용하기(바인딩) 섹션을 참조하십시오.)

다음은 또 다른 저장 프로시저를 호출하지만 그 반환 값에 의존하지 않는 저장 프로시저의 예입니다.

먼저, 예제에서 사용할 테이블을 만듭니다.

-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);

Copy

그런 다음, 또 다른 저장 프로시저에서 호출할 저장 프로시저를 만듭니다.

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO int_table VALUES (:value);
  RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
$$
;

Copy

다음으로, 첫 번째 저장 프로시저를 호출하는 두 번째 저장 프로시저를 만듭니다.

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
  CALL insert_value(:value1);
  CALL insert_value(:value2);
  RETURN 'Finished calling stored procedures';
END;
$$
;

Copy

마지막으로, 두 번째 저장 프로시저를 호출합니다.

CALL insert_two_values(4, 5);

Copy

저장 프로시저 호출에서 반환된 값 사용하기¶

스칼라 값을 반환하는 저장 프로시저를 호출하고 해당 값에 액세스해야 하는 경우 CALL 문의 INTO :snowflake_scripting_variable 절을 사용하여 Snowflake Scripting 변수 의 값을 캡처하십시오.

다음 예에서는 인자를 오브젝트 식별자로 사용하기 에 정의된 get_row_count 저장 프로시저를 호출합니다.

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;

Copy

참고: SnowSQL 또는 Classic Console 을 사용하는 경우 대신 다음 예를 사용하십시오(SnowSQL 및 Classic Console 에서 Snowflake Scripting 사용하기 참조).

CREATE OR REPLACE PROCEDURE count_greater_than(table_name VARCHAR, maximum_count INTEGER)
  RETURNS BOOLEAN NOT NULL
  LANGUAGE SQL
  AS
  $$
  DECLARE
    count1 NUMBER;
  BEGIN
    CALL get_row_count(:table_name) INTO :count1;
    IF (:count1 > maximum_count) THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END;
  $$
  ;

Copy

다음은 저장 프로시저를 호출하는 예입니다.

CALL count_greater_than('invoices', 3);

Copy

저장 프로시저가 테이블을 반환하는 경우 RESULTSET 를 CALL 문을 포함하는 문자열로 설정하여 반환 값을 캡처할 수 있습니다. (선언된 RESULTSET에 쿼리 할당하기 섹션을 참조하십시오.)

호출에서 반환 값을 검색하려면 RESULTSET 에 대해 CURSOR 를 사용할 수 있습니다. 예:

DECLARE
  res1 RESULTSET;
BEGIN
res1 := (CALL my_procedure());
LET c1 CURSOR FOR res1;
FOR row_variable IN c1 DO
  IF (row_variable.col1 > 0) THEN
    ...;
  ELSE
    ...;
  END IF;
END FOR;
...

Copy