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

이 항목에서는 SQL로 작성된 Snowflake Scripting을 사용하여 저장 프로시저를 작성하는 방법을 소개합니다. Snowflake Scripting에 대한 자세한 내용은 Snowflake Scripting 개발자 가이드 섹션을 참조하십시오.

이 항목의 내용:

소개¶

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

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

참고

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

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

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

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

참고

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

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

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

Copy

참고: Python Connector 코드에서 Snowflake CLI, SnowSQL, Classic Console, 또는 execute_stream 또는 execute_string 메서드를 사용하는 경우 이 예제를 대신 사용하십시오(Snowflake CLI, SnowSQL, Classic Console 및 Python Connector에서 Snowflake Scripting 사용하기 참조).

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

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 표현식에서 이름으로 해당 인자를 참조할 수 있습니다. Snowflake Scripting 저장 프로시저는 입력(IN)/출력(OUT) 인자를 지원합니다.

Snowflake Scripting 저장 프로시저의 정의에 출력 인자를 지정하면 저장 프로시저에서 출력 인자의 현재 값을 익명 블록이나 다른 저장 프로시저 같은 호출 프로그램에 반환할 수 있습니다. 저장 프로시저는 출력 인자의 초기 값을 받아 프로시저 본문의 변수에 이 값을 저장하고, 선택적으로 변수 값을 변경하는 작업을 수행한 후 업데이트된 값을 호출 프로그램에 반환합니다.

예를 들어, 영업사원의 사용자 식별자와 영업 분기를 emp_quarter_calling_sp_demo``라는 저장 프로시저에 전달할 수 있습니다. 이 저장 프로시저는 ``sales_total_out_sp_demo``라는 다른 저장 프로시저를 호출합니다. ``sales_total_out_sp_demo 저장 프로시저에는 해당 분기의 영업사원 총 매출을 호출 저장 프로시저 ``emp_quarter_calling_sp_demo``에 반환하는 작업을 수행하는 출력 인자가 있습니다. 이 시나리오의 예제는 출력 인자를 사용해 한 분기 동안의 직원 총 매출 반환하기 섹션을 참조하세요.

전달되는 값의 데이터 타입과 출력 인자의 데이터 타입이 일치하지 않으면 지원되는 강제 변환이 자동으로 수행됩니다. 예를 보려면 호출 프로시저의 입력 값과 데이터 타입이 다른 출력 인자 사용하기 를 참조하십시오. Snowflake가 자동으로 수행할 수 있는 강제 변환에 대한 자세한 내용은 캐스팅할 수 있는 데이터 타입 섹션을 참조하세요.

GET_DDL 함수 및 SHOW PROCEDURES 명령은 출력에 저장 프로시저 인자의 타입을 표시합니다(IN 또는 OUT). 저장 프로시저에 대한 메타데이터를 표시하는 다른 명령과 뷰는 인자의 타입(예: DESCRIBE PROCEDURE 명령, Information Schema PROCEDURES 뷰, Account Usage PROCEDURES 뷰)을 표시하지 않습니다.

저장 프로시저는 서명에 다른 인자 타입을 지정하여 오버로드할 수 없습니다. 예를 들어, 저장 프로시저에 다음 서명이 있다고 가정해 보겠습니다.

CREATE PROCEDURE test_overloading(a IN NUMBER)

Copy

다음 CREATE PROCEDURE 명령은 해당 인자 타입에서만 이전 예제와 다른 새 저장 프로시저를 만들려고 시도하기 때문에 프로시저가 이미 존재한다는 오류 메시지를 표시하며 실패합니다.

CREATE PROCEDURE test_overloading(a OUT NUMBER)

Copy

구문¶

다음 구문을 사용하여 Snowflake Scripting 저장 프로시저 정의에 인자를 지정합니다.

<arg_name> [ { IN | INPUT | OUT | OUTPUT } ] <arg_data_type>

Copy

여기서

arg_name

인자의 이름입니다. 이름은 오브젝트 식별자 에 대한 명명 규칙을 따라야 합니다.

{ IN | INPUT | OUT | OUTPUT }

인자가 입력 인자인지, 출력 인자인지를 지정하는 선택적 키워드입니다.

IN 또는 INPUT - 인자는 제공된 값으로 초기화되며, 이 값은 저장 프로시저 변수에 할당됩니다. 변수는 저장 프로시저 본문에서 수정할 수 있지만 최종 값을 호출 프로그램에 전달할 수는 없습니다.

IN 와 INPUT 는 동의어입니다.
OUT 또는 OUTPUT - 인자는 제공된 값으로 초기화되며, 이 값은 저장 프로시저 변수에 할당됩니다. 변수는 저장 프로시저 본문에서 수정할 수 있으며, 최종 값을 호출 프로그램에 전달할 수 있습니다. 저장 프로시저 본문에서는 변수를 사용해야만 출력 인자에 값을 할당할 수 있습니다.

출력 인자는 초기화되지 않은 변수도 전달할 수 있습니다. 연결된 변수가 할당 해제되면 출력 인자가 NULL을 반환합니다.

OUT 와 OUTPUT 는 동의어입니다.

기본값: IN

arg_data_type

SQL 데이터 타입.

제한 사항¶

출력 인자는 저장 프로시저의 정의에 지정해야 합니다.
출력 인자는 :ref:`선택적 인자<label-procedure_function_arguments_optional>`로 지정할 수 없습니다. 즉, DEFAULT 키워드를 사용해 출력 인자를 지정할 수는 없습니다.
저장 프로시저 본문에서 변수를 사용하여 출력 인자에 값을 할당해야 합니다.
여러 출력 인자에 동일한 변수를 사용할 수는 없습니다.
세션 변수는 출력 인자에 전달할 수 없습니다.
사용자 정의 함수(UDFs)는 출력 인자를 지원하지 않습니다.
SQL 이외의 언어로 작성된 저장 프로시저는 출력 인자를 지원하지 않습니다.
출력 인자는 :doc:`비동기 하위 작업</developer-guide/snowflake-scripting/asynchronous-child-jobs>`에서 사용할 수 없습니다.
저장 프로시저는 입력/출력 인자를 모두 포함하여 500개의 인자로 제한됩니다.

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

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

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

Copy

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 문에서 변수 사용하기(바인딩) 섹션을 참조하십시오.

다음 섹션에는 저장 프로시저에서 바인드 변수를 사용하는 예가 포함되어 있습니다.

WHERE 절에서 바인드 변수를 사용하는 예
바인드 변수를 사용하여 속성 값을 설정하는 예
바인드 변수를 사용하여 명령에서 매개 변수를 설정하는 예
배열에 바인드 변수를 사용하는 예제

WHERE 절에서 바인드 변수를 사용하는 예¶

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

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

Copy

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

또한 TO_QUERY 함수는 SELECT 문의 FROM 절에서 직접 SQL 문자열을 받는 간단한 구문을 제공합니다. TO_QUERY 함수와 동적 SQL의 비교는 런타임에 SQL 구성하기 섹션을 참조하십시오.

바인드 변수를 사용하여 속성 값을 설정하는 예¶

다음 저장 프로시저는 comment 인자를 사용하여 CREATE TABLE 문에 테이블에 대한 설명을 추가합니다. 문에서 인자는 :comment 로 지정됩니다.

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;

Copy

CREATE OR REPLACE PROCEDURE test_bind_comment(comment VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE test_table_with_comment(a VARCHAR, n NUMBER) COMMENT = :comment;
END;
$$
;

Copy

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

CALL test_bind_comment('My Test Table');

Copy

INFORMATION_SCHEMA에서 TABLES 뷰 를 쿼리하여 테이블에 대한 설명을 확인합니다.

SELECT comment FROM information_schema.tables WHERE table_name='TEST_TABLE_WITH_COMMENT';

Copy

+---------------+
| COMMENT       |
|---------------|
| My Test Table |
+---------------+

SHOW TABLES 명령을 실행하여 설명을 볼 수도 있습니다.

바인드 변수를 사용하여 명령에서 매개 변수를 설정하는 예¶

CSV 파일이 있는 st 라는 스테이지가 있다고 가정합니다.

CREATE OR REPLACE STAGE st;
PUT file://good_data.csv @st;
PUT file://errors_data.csv @st;

Copy

CSV 파일에 있는 데이터를 test_bind_stage_and_load 라는 테이블에 로딩하려고 합니다.

CREATE OR REPLACE TABLE test_bind_stage_and_load (a VARCHAR, b VARCHAR, c VARCHAR);

Copy

다음 저장 프로시저는 COPY INTO <테이블> 문에서 FROM, ON_ERROR 및 VALIDATION_MODE 매개 변수를 사용합니다. 문에서 매개 변수 값은 각각 :my_stage_name, :on_error, :valid_mode 로 지정됩니다.

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;

Copy

CREATE OR REPLACE PROCEDURE test_copy_files_validate(
  my_stage_name VARCHAR,
  on_error VARCHAR,
  valid_mode VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  COPY INTO test_bind_stage_and_load
    FROM :my_stage_name
    ON_ERROR=:on_error
    FILE_FORMAT=(type='csv')
    VALIDATION_MODE=:valid_mode;
END;
$$
;

Copy

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

CALL test_copy_files_validate('@st', 'skip_file', 'return_all_errors');

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

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

다음 예제에서는 인자에 제공된 테이블 이름을 기반으로 저장 프로시저에서 CREATE TABLE … AS SELECT (CTAS) 문을 실행합니다.

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;

Copy

CREATE OR REPLACE PROCEDURE ctas_sp(existing_table VARCHAR, new_table VARCHAR)
  RETURNS TEXT
  LANGUAGE SQL
AS
$$
BEGIN
  CREATE OR REPLACE TABLE IDENTIFIER(:new_table) AS
    SELECT * FROM IDENTIFIER(:existing_table);
  RETURN 'Table created';
END;
$$
;

Copy

프로시저를 호출하기 전에 간단한 테이블을 만들고 데이터를 삽입합니다.

CREATE OR REPLACE TABLE test_table_for_ctas_sp (
  id NUMBER(2),
  v  VARCHAR(2))
AS SELECT
  column1,
  column2,
FROM
  VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c');

Copy

저장 프로시저를 호출하여 이 테이블을 기반으로 하는 새 테이블을 만듭니다.

CALL ctas_sp('test_table_for_ctas_sp', 'test_table_for_ctas_sp_backup');

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

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

출력 인자를 사용해 단일 값 반환하기¶

다음 예제에서는 정의에 xout 출력 인자가 포함된 저장 프로시저 simple_out_sp_demo``를 만듭니다. 저장 프로시저는 ``xout 값을 ``2``로 설정합니다.

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  xout := 2;
  RETURN 'Done';
END;

Copy

CREATE OR REPLACE PROCEDURE simple_out_sp_demo(xout OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  xout := 2;
  RETURN 'Done';
END;
$$
;

Copy

다음 익명 블록은 x 변수의 값을 1``로 설정합니다. 그런 다음 ``simple_out_sp_demo 저장 프로시저를 호출하고 해당 변수를 인자로 지정합니다.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x);
  RETURN x;
END;
$$
;

Copy

출력은 simple_out_sp_demo 저장 프로시저가 출력 인자의 값을 ``2``로 설정하는 작업을 수행한 후 이 값을 익명 블록에 반환했음을 보여줍니다.

+-----------------+
| anonymous block |
|-----------------|
|               2 |
+-----------------+

다음 익명 블록은 simple_out_sp_demo 저장 프로시저를 호출하고 오류를 반환합니다. 변수 대신 표현식을 사용해 출력 인자에 값을 할당하려고 시도하기 때문입니다.

BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x := 1;
  CALL simple_out_sp_demo(:x + 2);
  RETURN x;
END;
$$
;

Copy

출력 인자를 사용해 저장 프로시저에 대한 여러 호출에 여러 값 반환하기¶

다음 예제에서는 저장 프로시저 및 입력/출력 인자와 관련된 아래와 같은 동작을 보여줍니다.

저장 프로시저는 정의에 입력/출력 인자를 여러 개 포함할 수 있습니다.
프로그램은 출력 인자를 사용해 저장 프로시저를 여러 번 호출할 수 있으며, 출력 인자의 값은 매번 호출한 뒤에 보존됩니다.
입력 인자는 호출 프로그램에 값을 반환하지 않습니다.

정의에 입력/출력 인자가 여러 개 포함된 저장 프로시저 multiple_out_sp_demo``를 만듭니다. 저장 프로시저는 동등한 입력/출력 인자에 동일한 작업을 수행합니다. 예를 들어, 저장 프로시저는 ``p1_in 입력 인자와 p1_out 출력 인자에 ``1``을 더합니다.

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;

Copy

CREATE OR REPLACE PROCEDURE multiple_out_sp_demo(
    p1_in NUMBER,
    p1_out OUT NUMBER,
    p2_in VARCHAR(100),
    p2_out OUT VARCHAR(100),
    p3_in BOOLEAN,
    p3_out OUT BOOLEAN)
  RETURNS NUMBER
  LANGUAGE SQL
AS
$$
BEGIN
  p1_in := p1_in + 1;
  p1_out := p1_out + 1;
  p2_in := p2_in || ' hi ';
  p2_out := p2_out || ' hi ';
  p3_in := (NOT p3_in);
  p3_out := (NOT p3_out);
  RETURN 1;
END;
$$
;

Copy

다음 익명 블록은 multiple_out_sp_demo 저장 프로시저의 인자에 해당하는 변수에 값을 할당한 후 저장 프로시저를 여러 번 호출합니다. 첫 번째 호출은 익명 블록에 지정된 변수 값을 사용하지만, 각 후속 호출은 multiple_out_sp_demo 저장 프로시저의 출력 인자에서 반환된 값을 사용합니다.

BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET x_in INT := 1;
  LET x_out INT := 1;
  LET y_in VARCHAR(100) := 'hello';
  LET y_out VARCHAR(100) := 'hello';
  LET z_in BOOLEAN := true;
  LET z_out BOOLEAN := true;

  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  CALL multiple_out_sp_demo(:x_in, :x_out, :y_in, :y_out, :z_in, :z_out);
  RETURN [x_in, x_out, y_in, y_out, z_in, z_out];
END;
$$
;

Copy

+------------------------+
| anonymous block        |
|------------------------|
| [                      |
|   1,                   |
|   4,                   |
|   "hello",             |
|   "hello hi  hi  hi ", |
|   true,                |
|   false                |
| ]                      |
+------------------------+

호출 프로시저의 입력 값과 데이터 타입이 다른 출력 인자 사용하기¶

일부 사용 사례의 경우 저장 프로시저에 전달되는 값의 데이터 타입과 프로시저 출력 인자의 데이터 타입이 일치하지 않을 수 있습니다. 이러한 경우 :ref:`지원되는 강제 변환 <label-valid_casting_and_coercions>`이 자동으로 수행됩니다.

참고

경우에 따라 강제 변환이 지원되지만 권장되지는 않습니다.

이 예제는 데이터 타입이 NUMBER인 출력 인자에 전달되는 FLOAT 값의 자동 변환을 보여줍니다. FLOAT 값은 NUMBER 값으로 자동 변환된 후 호출하는 익명 블록으로 다시 전달됩니다.

NUMBER 형식의 출력 인자를 받는 sp_out_coercion 저장 프로시저를 만듭니다.

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  x := x * 2;
  RETURN 'Done';
END;

Copy

CREATE OR REPLACE PROCEDURE sp_out_coercion(x OUT NUMBER)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  x := x * 2;
  RETURN 'Done';
END;
$$
;

Copy

sp_out_coercion 저장 프로시저에 FLOAT 값을 전달하는 익명 블록을 실행합니다.

BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;

Copy

EXECUTE IMMEDIATE
$$
BEGIN
  LET a FLOAT := 500.662;
  CALL sp_out_coercion(:a);
  RETURN a || ' (Type ' || SYSTEM$TYPEOF(a) || ')';
END;
$$
;

Copy

출력은 SYSTEM$TYPEOF 함수를 호출하여 반환된 값과 반환된 값의 데이터 타입을 모두 표시합니다. 값이 저장 프로시저에서 반환되면 NUMBER 값에서 FLOAT 값으로 다시 강제 변환됩니다.

+---------------------------+
| anonymous block           |
|---------------------------|
| 1002 (Type FLOAT[DOUBLE]) |
+---------------------------+

출력 인자를 사용해 한 분기 동안의 직원 총 매출 반환하기¶

이 예제에서는 다음 quarterly_sales 테이블을 사용합니다.

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT,
  amount INT,
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');

Copy

직원 식별자 및 분기에 대한 입력 인자 두 개와 지정된 직원 및 분기에 대한 총 매출 합계를 계산하기 위한 출력 인자 한 개를 사용하는 저장 프로시저 ``sales_total_out_sp_demo``를 만듭니다.

CREATE OR REPLACE PROCEDURE sales_total_out_sp_demo(
    id INT,
    quarter VARCHAR(20),
    total_sales OUT NUMBER(38,0))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  SELECT SUM(amount) INTO total_sales FROM quarterly_sales
    WHERE empid = :id AND
          quarter = :quarter;
  RETURN 'Done';
END;
$$
;

Copy

저장 프로시저 sales_total_out_sp_demo``를 호출하는 ``emp_quarter_calling_sp_demo 저장 프로시저를 만듭니다. 이 저장 프로시저는 직원 식별자와 분기에 대한 입력 인자도 두 개 사용합니다.

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;

Copy

CREATE OR REPLACE PROCEDURE emp_quarter_calling_sp_demo(
    id INT,
    quarter VARCHAR(20))
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  LET x NUMBER(38,0);
  CALL sales_total_out_sp_demo(:id, :quarter, :x);
  RETURN 'Total sales for employee ' || id || ' in quarter ' || quarter || ': ' || x;
END;
$$
;

Copy

``2``(직원 식별자) 및 ``’2023_Q4’``(분기) 인자가 포함된 ``emp_quarter_calling_sp_demo``를 호출합니다.

CALL emp_quarter_calling_sp_demo(2, '2023_Q4');

Copy

+-----------------------------------------------------+
| emp_quarter_calling_sp_demo                         |
|-----------------------------------------------------|
| Total sales for employee 2 in quarter 2023_Q4: 5300 |
+-----------------------------------------------------+

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

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

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

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

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

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

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

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

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

저장 프로시저의 출력 인자 값을 호출 저장 프로시저로 전달하기¶

출력 인자가 Snowflake Scripting 저장 프로시저의 정의에 지정되어 있으면 저장 프로시저에서 출력 인자의 현재 값을 호출 저장 프로시저에 반환할 수 있습니다. 저장 프로시저는 출력 인자의 초기 값을 받아 프로시저 본문의 변수에 값을 저장하고, 선택적으로 변수 값을 변경하는 작업을 수행합니다. 그러면 저장 프로시저가 업데이트된 값을 호출 저장 프로시저에 반환합니다.

예를 보려면 출력 인자를 사용해 한 분기 동안의 직원 총 매출 반환하기 를 참조하십시오.

중첩 저장 프로시저 사용하기¶

중첩 저장 프로시저*는 다른 저장 프로시저(*상위 저장 프로시저)의 익명 블록 또는 블록 범위 내에 정의된 저장 프로시저입니다.

CREATE PROCEDURE 문의 일부일 수 있는 블록의 DECLARE 섹션에 중첩 저장 프로시저를 선언합니다. 다음 예제는 중첩 저장 프로시저 선언을 보여줍니다.

DECLARE
  <nested_stored_procedure_name> PROCEDURE (<arguments>)
     RETURNS <data_type>
     AS
     BEGIN
       <nested_procedure_procedure_statements>
     END;
BEGIN
  <statements>
END;

Copy

중첩 저장 프로시저의 선언 구문에 대한 자세한 내용은 중첩 저장 프로시저 선언 구문 섹션을 참조하세요.

중첩 저장 프로시저는 :doc:`블록</developer-guide/snowflake-scripting/blocks>`의 범위 내에서만 존재합니다. 이는 블록(DECLARE, BEGIN… END, EXCEPTION)의 모든 섹션에서 호출할 수 있습니다. 단일 블록에 중첩 저장 프로시저가 여러 개 포함될 수 있으며, 한 개의 중첩 저장 프로시저가 동일한 블록의 다른 중첩 저장 프로시저를 호출할 수 있습니다. 중첩 프로시저는 블록 외부에서 호출하거나 액세스할 수 없습니다.

중첩 저장 프로시저는 이를 정의하는 블록과 동일한 보안 컨텍스트에서 작동합니다. 중첩 저장 프로시저가 상위 저장 프로시저에 정의되어 있으면 상위 저장 프로시저와 동일한 권한을 사용하여 자동으로 실행됩니다.

참고

중첩 저장 프로시저 선언과 CALLWITH 명령 모두 범위가 제한된 임시 저장 프로시저를 만듭니다. 이 둘은 다음과 같은 면에서 다릅니다.

CALLWITH 문은 저장 프로시저 내부를 포함하여 SQL 문이 표시될 수 있는 모든 곳에 나타날 수 있지만, 중첩 저장 프로시저 선언은 Snowflake Scripting 블록에 있어야 합니다.
CALLWITH 저장 프로시저는 해당 문의 범위 내에만 존재하지만, 중첩 저장 프로시저는 Snowflake Scripting 블록의 범위 내에 존재합니다.

중첩 저장 프로시저의 이점¶

중첩 저장 프로시저가 제공하는 이점은 다음과 같습니다.

익명 블록 또는 상위 저장 프로시저 내부에 논리를 캡슐화하여 보안을 강화하고 단순화함으로써 블록 또는 상위 저장 프로시저 외부에서 액세스하는 것을 방지할 수 있습니다.
코드를 논리적으로 더 작은 청크로 분할하여 모듈식으로 유지하므로, 유지 관리 및 디버깅 작업이 더욱 수월해집니다.
중첩 저장 프로시저는 해당 블록의 로컬 변수에 직접 액세스할 수 있기 때문에 전역 변수 또는 추가 인자를 사용할 필요성이 줄어 들어 유지 관리 작업이 개선됩니다.

중첩 저장 프로시저 호출에 대한 사용법 노트¶

다음과 같은 사용법 노트가 중첩 저장 프로시저 호출에 적용됩니다.

중첩 저장 프로시저에 인자를 전달하려면 블록에서 상수 값 Snowflake Scripting 변수, 바인드 변수, SQL(세션) 변수 및 사용자 정의 함수 호출을 사용하면 됩니다.
전달되는 값의 데이터 타입과 인자의 데이터 타입이 일치하지 않으면 Snowflake에서 지원되는 강제 변환을 자동으로 수행합니다. Snowflake가 자동으로 수행할 수 있는 강제 변환에 대한 자세한 내용은 데이터 타입 변환 섹션을 참조하세요.

중첩 저장 프로시저의 변수에 대한 사용법 노트¶

다음과 같은 사용법 노트는 중첩 저장 프로시저의 변수에 적용됩니다.

중첩 저장 프로시저는 해당 블록의 DECLARE 섹션에서 중첩 저장 프로시저 선언 이전에 선언된 블록의 변수를 참조할 수 있습니다. DECLARE 섹션에서 이후에 선언된 변수는 참조할 수 없습니다.
중첩 저장 프로시저는 블록의 BEGIN… END 섹션에서 LET 문에 선언된 변수에 액세스할 수 없습니다.
참조된 변수의 값은 중첩 저장 프로시저가 호출된 시기의 값을 반영합니다.
중첩 저장 프로시저는 참조된 변수 값을 수정할 수 있으며, 수정된 값은 익명 블록의 단일 실행 또는 상위 저장 프로시저에 대한 단일 호출에서 동일한 중첩 프로시저를 여러 번 호출할 때 해당 블록에서 유지됩니다.
중첩 저장 프로시저 호출 이전에 선언된 변수의 값을 중첩 저장 프로시저에 인자로 전달할 수 있습니다. 변수 값은 중첩 저장 프로시저 선언 이후에 변수가 선언되거나 LET 문에서 선언된 경우에도 호출 시 인자로 전달할 수 있습니다.

예를 들어, 다음 저장 프로시저는 여러 변수를 선언합니다.

CREATE OR REPLACE PROCEDURE outer_sp ()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  var_before_nested_proc NUMBER DEFAULT 1;
  test_nested_variables PROCEDURE(arg1 NUMBER)
    -- <nested_sp_logic>
  var_after_nested_proc NUMBER DEFAULT 2;
BEGIN
  LET var_let_before_call NUMBER DEFAULT 3;
  LET result := CALL nested_proc(:<var_name>);
  LET var_let_after_call NUMBER DEFAULT 3;
  RETURN result;
END;
$$;

Copy

이 예제에서는 ``var_before_nested_proc``만 :samp:`{nested_sp_logic}`에서 참조할 수 있습니다.

중첩 저장 프로시저 호출에서 다음 변수 중 하나의 값을 :samp:`{var_name}`의 인자로 중첩 저장 프로시저에 전달할 수 있습니다.

var_before_nested_proc
var_after_nested_proc
var_let_before_call

``var_let_after_call``의 값은 중첩 저장 프로시저에 인자로 전달할 수 없습니다.

중첩 저장 프로시저에 대한 제한 사항¶

중첩 저장 프로시저를 정의하는 데는 다음과 같은 제한 사항이 적용됩니다.

다른 중첩 저장 프로시저나 제어 구조(예: FOR 또는 WHILE) 내부에 정의할 수 없습니다.
각 중첩 저장 프로시저는 블록에 고유한 이름이 있어야 합니다. 즉, 중첩 저장 프로시저는 오버로드할 수 없습니다.
출력(OUT) 인자를 지원하지 않습니다.
기본값이 있는 선택적 인자를 지원하지 않습니다.

중첩 저장 프로시저를 호출하는 데는 다음과 같은 제한 사항이 적용됩니다.

EXECUTE IMMEDIATE 문에서 호출할 수 없습니다.
:doc:`비동기 하위 작업</developer-guide/snowflake-scripting/asynchronous-child-jobs>`에서 호출할 수 없습니다.
명명된 입력 인자(arg_name => arg)를 지원하지 않습니다. 인자는 위치에 따라 지정해야 합니다. 자세한 내용은 CALL 섹션을 참조하십시오.

중첩 저장 프로시저의 예제¶

다음 예제에서는 중첩 저장 프로시저를 사용합니다.

테이블 형식 데이터를 반환하는 중첩 저장 프로시저 정의하기
스칼라 값을 반환하는 중첩 저장 프로시저 정의하기
익명 블록에서 중첩 저장 프로시저 정의하기
인자를 전달하는 중첩 저장 프로시저 정의하기
다른 중첩 저장 프로시저를 호출하는 중첩 저장 프로시저 정의하기

테이블 형식 데이터를 반환하는 중첩 저장 프로시저 정의하기¶

다음 예제에서는 테이블 형식 데이터를 반환하는 중첩 저장 프로시저를 정의합니다. 이 예제에서는 ``nested_return_table``이라는 중첩 저장 프로시저가 포함된 ``nested_procedure_example_table``이라는 상위 저장 프로시저를 만듭니다. 코드에는 다음 논리가 포함됩니다.

RESULTSET 타입의 ``res``라는 변수를 선언합니다.
중첩 저장 프로시저에는 다음과 같은 논리가 포함됩니다.
- ``res2``라는 변수를 선언합니다.
- ``nested_table``라는 테이블에 값을 삽입합니다.
- 테이블의 SELECT 결과에 res2 변수를 설정합니다.
- 결과 세트의 테이블 형식 데이터를 반환합니다.
상위 저장 프로시저에 nested_table 테이블을 만듭니다.
nested_return_table 중첩 저장 프로시저를 호출하고, 중첩 저장 프로시저에 대한 호출 결과에 res 변수를 설정합니다.
res 변수에 테이블 형식 결과를 반환합니다.

CREATE OR REPLACE PROCEDURE nested_procedure_example_table()
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
  nested_return_table PROCEDURE()
    RETURNS TABLE()
    AS
    DECLARE
      res2 RESULTSET;
    BEGIN
      INSERT INTO nested_table VALUES(1);
      INSERT INTO nested_table VALUES(2);
      res2 := (SELECT * FROM nested_table);
      RETURN TABLE(res2);
    END;
BEGIN
  CREATE OR REPLACE TABLE nested_table(col1 INT);
  res := (CALL nested_return_table());
  RETURN TABLE(res);
END;
$$;

Copy

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

CALL nested_procedure_example_table();

Copy

+------+
| COL1 |
|------|
|    1 |
|    2 |
+------+

스칼라 값을 반환하는 중첩 저장 프로시저 정의하기¶

다음 예제에서는 스칼라 값을 반환하는 중첩 저장 프로시저를 정의합니다. 이 예제에서는 ``simple_counter``이라는 중첩 저장 프로시저가 포함된 ``nested_procedure_example_scalar``이라는 상위 저장 프로시저를 만듭니다. 코드에는 다음 논리가 포함됩니다.

NUMBER 타입의 ``counter``라는 변수를 선언하고, 이 변수의 값을 ``0``으로 설정합니다.
중첩 저장 프로시저에서 counter 변수의 현재값에 ``1``을 더하도록 지정합니다.
상위 저장 프로시저에서 중첩 저장 프로시저를 세 번 호출합니다. counter 변수의 값은 중첩 저장 프로시저를 호출하는 중간에 전달됩니다.
counter 변수의 값인 ``3``을 반환합니다.

CREATE OR REPLACE PROCEDURE nested_procedure_example_scalar()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

Copy

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

CALL nested_procedure_example_scalar();

Copy

+---------------------------------+
| NESTED_PROCEDURE_EXAMPLE_SCALAR |
|---------------------------------|
| 3                               |
+---------------------------------+

익명 블록에서 중첩 저장 프로시저 정의하기¶

다음 예제는 저장 프로시저 대신 익명 블록에 중첩 저장 프로시저를 정의한다는 점을 제외하면 :ref:`label-stored_procedure_snowscript_nested_stored_procedures_example_scalar`의 예제와 동일합니다.

EXECUTE IMMEDIATE $$
DECLARE
  counter NUMBER := 0;
  simple_counter PROCEDURE()
    RETURNS VARCHAR
    AS
    BEGIN
      counter := counter + 1;
      RETURN counter;
    END;
BEGIN
  CALL simple_counter();
  CALL simple_counter();
  CALL simple_counter();
  RETURN counter;
END;
$$;

Copy

+-----------------+
| anonymous block |
|-----------------|
|               3 |
+-----------------+

인자를 전달하는 중첩 저장 프로시저 정의하기¶

다음 예제에서는 인자를 전달하는 중첩 저장 프로시저를 정의합니다. 이 예제에서 중첩 저장 프로시저는 다음 테이블에 값을 삽입합니다.

CREATE OR REPLACE TABLE log_nested_values(col1 INT, col2 INT);

Copy

이 예제에서는 ``log_and_multiply_numbers``이라는 중첩 저장 프로시저가 포함된 ``nested_procedure_example_arguments``이라는 상위 저장 프로시저를 만듭니다. 중첩 저장 프로시저는 NUMBER 타입의 인자를 두 개 사용합니다. 코드에는 다음 논리가 포함됩니다.

NUMBER 타입의 a, b, x 변수를 선언합니다.
다음 작업을 수행하는 중첩 저장 프로시저를 포함합니다.
- 바인드 변수를 사용하여 상위 저장 프로시저가 전달한 두 개의 숫자 값을 log_nested_values 테이블에 삽입합니다.
- 두 인자 값을 곱한 결과로 x 변수의 값을 설정합니다.
- 상위 저장 프로시저에 ``x``의 값을 반환합니다.
a 변수의 값을 5``로, ``b 변수의 값을 ``10``으로 설정합니다.
중첩 저장 프로시저를 호출합니다.
중첩 저장 프로시저에 설정된 x 변수의 값을 반환합니다.

CREATE OR REPLACE PROCEDURE nested_procedure_example_arguments()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  a NUMBER;
  b NUMBER;
  x NUMBER;
  log_and_multiply_numbers PROCEDURE(num1 NUMBER, num2 NUMBER)
    RETURNS NUMBER
    AS
    BEGIN
      INSERT INTO log_nested_values VALUES(:num1, :num2);
      x := :num1 * :num2;
      RETURN x;
    END;
BEGIN
  a := 5;
  b := 10;
  CALL log_and_multiply_numbers(:a, :b);
  RETURN x;
END;
$$;

Copy

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

CALL nested_procedure_example_arguments();

Copy

+------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_ARGUMENTS |
|------------------------------------|
|                                 50 |
+------------------------------------+

log_nested_values 테이블을 쿼리하여 중첩 저장 프로시저가 전달된 값을 삽입했는지 확인합니다.

SELECT * FROM log_nested_values;

Copy

+------+------+
| COL1 | COL2 |
|------+------|
|    5 |   10 |
+------+------+

다른 중첩 저장 프로시저를 호출하는 중첩 저장 프로시저 정의하기¶

다음 예제에서는 다른 중첩 저장 프로시저를 호출하는 중첩 저장 프로시저를 정의합니다. 이 예제에서는 counter_nested_proc 및 ``call_counter_nested_proc``라는 두 중첩 저장 프로시저가 포함된 ``nested_procedure_example_call_from_nested``라는 상위 저장 프로시저를 만듭니다. 코드에는 다음 논리가 포함됩니다.

NUMBER 타입의 ``counter``라는 변수를 선언하고, 이 변수의 값을 ``0``으로 설정합니다.
``counter``의 값에 ``10``을 더하는 중첩 저장 프로시저 ``counter_nested_proc``를 포함합니다.
``counter``의 값에 ``15``를 더하고 ``counter_nested_proc``(``counter``의 값에 다시 ``10``을 더하는)도 호출하는 중첩 저장 프로시저 ``call_counter_nested_proc``를 포함합니다.
상위 저장 프로시저에서 중첩 저장 프로시저를 모두 호출합니다.
counter 변수의 값인 ``35``을 반환합니다.

CREATE OR REPLACE PROCEDURE nested_procedure_example_call_from_nested()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
  counter NUMBER := 0;
  counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var1 NUMBER := 10;
    BEGIN
      counter := counter + var1;
    END;
  call_counter_nested_proc PROCEDURE()
    RETURNS NUMBER
    AS
    DECLARE
      var2 NUMBER := 15;
    BEGIN
      counter := counter + var2;
      CALL counter_nested_proc();
    END;
BEGIN
  counter := 0;
  CALL counter_nested_proc();
  CALL call_counter_nested_proc();
  RETURN counter;
END;
$$;

Copy

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

CALL nested_procedure_example_call_from_nested();

Copy

+-------------------------------------------+
| NESTED_PROCEDURE_EXAMPLE_CALL_FROM_NESTED |
|-------------------------------------------|
|                                        35 |
+-------------------------------------------+

저장 프로시저에서 SQL 변수 사용 및 설정하기¶

기본적으로 Snowflake Scripting 저장 프로시저는 소유자 권한으로 실행됩니다. 저장 프로시저가 소유자 권한으로 실행되는 경우 SQL (또는 세션) 변수 에 액세스할 수 없습니다.

그러나 호출자의 권한 저장 프로시저는 호출자의 세션 변수를 읽고 이를 저장 프로시저의 로직에 사용할 수 있습니다. 예를 들어, 호출자의 권한 저장 프로시저는 쿼리에서 SQL 변수의 값을 사용할 수 있습니다. 호출자의 권한으로 실행되는 저장 프로시저를 만들려면 CREATE PROCEDURE 문에 EXECUTE AS CALLER 매개 변수를 지정하십시오.

이 예는 호출자 권한과 소유자 권한 저장 프로시저의 주요 차이점을 보여줍니다. SQL 변수를 두 가지 방식으로 사용하려고 합니다.

저장 프로시저를 호출하기 전에 SQL 변수를 설정한 다음 저장 프로시저 내부에서 SQL 변수를 사용합니다.
저장 프로시저 내에서 SQL 변수를 설정한 다음, 저장 프로시저에서 반환 후 SQL 변수를 사용합니다.

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

소유자 권한 및 호출자 권한에 대한 자세한 내용은 호출자 권한 및 소유자 권한 저장 프로시저 이해하기 섹션을 참조하십시오.

저장 프로시저에서 SQL 변수 사용하기¶

다음 예제에서는 저장 프로시저에서 SQL 변수를 사용합니다.

먼저 세션에 SQL 변수를 설정합니다.

SET example_use_variable = 2;

Copy

호출자의 권한으로 실행되고 이 SQL 변수를 사용하는 간단한 저장 프로시저를 만듭니다.

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;

Copy

CREATE OR REPLACE PROCEDURE use_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  sess_var_x_2 NUMBER;
BEGIN
  sess_var_x_2 := 2 * $example_use_variable;
  RETURN sess_var_x_2;
END;
$$
;

Copy

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

CALL use_sql_variable_proc();

Copy

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                     4 |
+-----------------------+

SQL 변수를 다른 값으로 설정합니다.

SET example_use_variable = 9;

Copy

프로시저를 다시 호출하여 반환 값이 변경되었는지 확인합니다.

CALL use_sql_variable_proc();

Copy

+-----------------------+
| USE_SQL_VARIABLE_PROC |
|-----------------------|
|                    18 |
+-----------------------+

저장 프로시저에서 SQL 변수 설정하기¶

호출자 권한으로 실행 중인 저장 프로시저에서 SQL 변수를 설정할 수 있습니다. 저장 프로시저에서 SQL 변수를 사용하기 위한 지침을 비롯한 자세한 내용은 호출자 권한 저장 프로시저 섹션을 참조하십시오.

참고

저장 프로시저 내에서 SQL 변수를 설정하고 프로시저가 끝난 후 세션 변수를 설정된 상태로 남겨둘 수 있지만, Snowflake에서는 이를 권장하지 않습니다.

다음 예제에서는 저장 프로시저에서 SQL 변수를 설정합니다.

먼저 세션에 SQL 변수를 설정합니다.

SET example_set_variable = 55;

Copy

SQL 변수의 값을 확인합니다.

SHOW VARIABLES LIKE 'example_set_variable';

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:20:17.255 -0800 | EXAMPLE_SET_VARIABLE | 55    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+

예를 들어 다음 저장 프로시저는 SQL 변수 example_set_variable 을 새 값으로 설정하고 새 값을 반환합니다.

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;

Copy

CREATE OR REPLACE PROCEDURE set_sql_variable_proc()
RETURNS NUMBER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
  SET example_set_variable = $example_set_variable - 3;
  RETURN $example_set_variable;
END;
$$
;

Copy

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

CALL set_sql_variable_proc();

Copy

+-----------------------+
| SET_SQL_VARIABLE_PROC |
|-----------------------|
|                    52 |
+-----------------------+

SQL 변수의 새 값을 확인합니다.

SHOW VARIABLES LIKE 'example_set_variable';

Copy

+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+
|     session_id | created_on                    | updated_on                    | name                 | value | type  | comment |
|----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------|
| 10363782631910 | 2024-11-27 08:18:32.007 -0800 | 2024-11-27 08:24:04.027 -0800 | EXAMPLE_SET_VARIABLE | 52    | fixed |         |
+----------------+-------------------------------+-------------------------------+----------------------+-------+-------+---------+