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;
참고: 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;
$$
;
다음은 저장 프로시저를 호출하는 예입니다.
CALL output_message('Hello World');
다음은 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');
익명 저장 프로시저에서는 프로시저 본문 주위에 문자열 리터럴 구분 기호 (' 또는 $$)를 사용해야 합니다.
저장 프로시저에 전달된 인자 사용하기¶
저장 프로시저에 인자를 전달하면 Snowflake Scripting 식에서 이름으로 해당 인자를 참조할 수 있습니다. 더 자세한 내용은 다음 섹션을 참조하십시오.
저장 프로시저에 전달된 인자를 사용하는 간단한 예¶
다음 저장 프로시저는 IF 및 RETURN 문에 이러한 인자의 값을 사용합니다.
CREATE OR REPLACE PROCEDURE return_greater(number_1 INTEGER, number_2 INTEGER)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
BEGIN
IF (number_1 > number_2) THEN
RETURN number_1;
ELSE
RETURN number_2;
END IF;
END;
참고: SnowSQL 또는 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;
$$
;
다음은 저장 프로시저를 호출하는 예입니다.
CALL return_greater(2, 3);
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;
참고: 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;
$$
;
다음은 저장 프로시저를 호출하는 예입니다.
CALL find_invoice_by_id('2');
인자를 오브젝트 식별자로 사용하기¶
오브젝트를 참조하는 인자를 사용해야 하는 경우(예: SELECT 문의 FROM 절에 있는 테이블 이름), IDENTIFIER 키워드를 사용하여 인자가 오브젝트 식별자를 표시함을 나타냅니다. 예:
CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)
RETURNS INTEGER NOT NULL
LANGUAGE SQL
AS
DECLARE
row_count INTEGER DEFAULT 0;
res RESULTSET DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:table_name));
c1 CURSOR FOR res;
BEGIN
FOR row_variable IN c1 DO
row_count := row_variable.count;
END FOR;
RETURN row_count;
END;
참고: SnowSQL 또는 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;
$$
;
다음은 저장 프로시저를 호출하는 예입니다.
CALL get_row_count('invoices');
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;
테이블 형식 데이터 반환하기¶
저장 프로시저에서 테이블 형식 데이터(예: RESULTSET의 데이터)를 반환해야 하는 경우 CREATE PROCEDURE 문에 RETURNS TABLE(…)을 지정합니다.
반환된 테이블에 있는 열의 Snowflake 데이터 타입 을 알면 RETURNS TABLE()에서 열 이름과 유형을 지정하십시오.
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
...
그렇지 않으면(예: 런타임 중에 열 유형을 결정하는 경우) 열 이름과 유형을 생략할 수 있습니다.
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE ()
...
참고
현재, RETURNS TABLE(...) 절에서는 GEOGRAPHY를 열 유형으로 지정할 수 없습니다. 이는 저장 프로시저를 생성하든 익명 프로시저를 생성하든 관계없이 적용됩니다.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE(g GEOGRAPHY)
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE(g GEOGRAPHY)
...
CALL test_return_geography_table_1();
GEOGRAPHY를 열 유형으로 지정하려고 할 경우 저장 프로시저를 호출하면 오류가 발생합니다.
Stored procedure execution error: data type of returned table does not match expected returned table type
이 문제를 해결하려면 RETURNS TABLE() 에서 열 인자와 유형을 생략하면 됩니다.
CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
RETURNS TABLE()
...
WITH test_return_geography_table_1() AS PROCEDURE
RETURNS TABLE()
...
CALL test_return_geography_table_1();
RESULTSET의 데이터를 반환해야 할 경우 RETURN 문에서 TABLE()을 사용합니다.
예:
CREATE OR REPLACE PROCEDURE get_top_sales()
RETURNS TABLE (sales_date DATE, quantity NUMBER)
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT sales_date, quantity FROM sales ORDER BY quantity DESC LIMIT 10);
BEGIN
RETURN TABLE(res);
END;
참고: SnowSQL 또는 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;
$$
;
다음은 저장 프로시저를 호출하는 예입니다.
CALL get_top_sales();
다른 저장 프로시저에서 저장 프로시저 호출하기¶
저장 프로시저에서 다른 저장 프로시저를 호출해야 하는 경우 다음 접근 방법 중 하나를 사용합니다.
반환된 값을 사용하지 않고 저장 프로시저 호출하기¶
(일반적으로 호출하는 것처럼) CALL 문을 사용하여 저장 프로시저를 호출합니다.
CALL 문에서 변수나 인자를 입력 인자로 전달해야 하는 경우 변수 이름 앞에 콜론(:)을 사용해야 합니다. (SQL 문에서 변수 사용하기(바인딩) 섹션을 참조하십시오.)
다음은 또 다른 저장 프로시저를 호출하지만 그 반환 값에 의존하지 않는 저장 프로시저의 예입니다.
먼저, 예제에서 사용할 테이블을 만듭니다.
-- Create a table for use in the example.
CREATE OR REPLACE TABLE int_table (value INTEGER);
그런 다음, 또 다른 저장 프로시저에서 호출할 저장 프로시저를 만듭니다.
-- Create a stored procedure to be called from another stored procedure.
CREATE OR REPLACE PROCEDURE insert_value(value INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
INSERT INTO int_table VALUES (:value);
RETURN 'Rows inserted: ' || SQLROWCOUNT;
END;
참고: SnowSQL 또는 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;
$$
;
다음으로, 첫 번째 저장 프로시저를 호출하는 두 번째 저장 프로시저를 만듭니다.
CREATE OR REPLACE PROCEDURE insert_two_values(value1 INTEGER, value2 INTEGER)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
CALL insert_value(:value1);
CALL insert_value(:value2);
RETURN 'Finished calling stored procedures';
END;
참고: SnowSQL 또는 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;
$$
;
마지막으로, 두 번째 저장 프로시저를 호출합니다.
CALL insert_two_values(4, 5);
저장 프로시저 호출에서 반환된 값 사용하기¶
스칼라 값을 반환하는 저장 프로시저를 호출하고 해당 값에 액세스해야 하는 경우 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;
참고: 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;
$$
;
다음은 저장 프로시저를 호출하는 예입니다.
CALL count_greater_than('invoices', 3);
저장 프로시저가 테이블을 반환하는 경우 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;
...