런타임에 SQL 구성하기¶

Snowflake는 런타임에 동적으로 SQL 문의 문자열을 구성하는 여러 가지 기법을 지원합니다. 이러한 기법을 사용하면 런타임까지 SQL 문의 전체 텍스트를 알 수 없는 사용 사례에 대해 보다 일반적이고 유연한 SQL 문자열을 지정할 수 있습니다.

저장 프로시저 또는 애플리케이션은 사용자 입력을 받은 다음 SQL 문에서 해당 입력을 사용할 수 있습니다. 예를 들어, 테이블에는 판매 주문에 대한 정보가 저장될 수 있습니다. 애플리케이션 또는 저장 프로시저는 주문 ID를 입력으로 받아들이고 특정 주문에 대한 결과만 반환하는 쿼리를 실행할 수 있습니다.

개발자는 자리 표시자가 포함된 SQL 문으로 저장 프로시저 코드 또는 애플리케이션 코드를 작성한 다음 코드에서 해당 자리 표시자에 변수를 바인딩할 수 있습니다. 이러한 자리 표시자를 변수 바인딩 이라고 합니다. 개발자는 입력 문자열에서 SQL 문을 구성하는 코드를 작성할 수도 있습니다(예: SQL 명령, 매개 변수 및 값이 포함된 문자열을 연결하는 방식).

런타임에 동적으로 SQL 문을 구성하는 데 사용할 수 있는 기법은 다음과 같습니다.

TO_QUERY 함수 - 이 함수는 선택적 매개 변수가 있는 SQL 문자열을 입력으로 받습니다.
동적 SQL - 저장 프로시저 또는 애플리케이션의 코드는 입력을 받고 이 입력을 사용하여 동적 SQL 문을 구성합니다. 코드는 Snowflake Scripting 또는 Javascript 저장 프로시저 또는 Snowflake Scripting 익명 블록의 일부일 수 있습니다. 이 기법은 Snowflake 드라이버 또는 Snowflake SQL REST API 를 사용하는 애플리케이션 코드에서도 사용할 수 있습니다.

참고

프로그램이 사용자 입력으로 SQL 문을 구성할 때 SQL 삽입과 같은 잠재적인 보안 위험이 있습니다. SQL 문에 대한 입력이 외부 소스에서 제공된 경우 해당 입력의 유효성을 검사해야 합니다. 자세한 내용은 SQL 주입 섹션을 참조하십시오.

TO_QUERY 함수 사용¶

코드에서 TO_QUERY 함수를 사용하여 SQL 문을 동적으로 구성하는 저장 프로시저 및 애플리케이션에 사용할 수 있습니다. 이 테이블 함수는 SQL 문자열을 입력으로 받습니다. 선택적으로, SQL 문자열에 매개 변수를 포함할 수 있으며, 매개 변수에 바인딩 변수로 전달할 인자를 지정할 수 있습니다.

다음은 함수를 호출하는 간단한 예제입니다.

SELECT COUNT(*) FROM TABLE(TO_QUERY('SELECT 1'));

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

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

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;

Copy

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

CREATE OR REPLACE PROCEDURE get_num_results_tq(query VARCHAR)
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET DEFAULT (SELECT COUNT(*) FROM TABLE(TO_QUERY(:query)));
BEGIN
  RETURN TABLE(res);
END;
$$
;

Copy

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

CALL get_num_results_tq('SELECT 1');

Copy

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

저장 프로시저 및 애플리케이션에서 동적 SQL 사용¶

사용자 입력을 받는 SQL 문을 구성하려면 Snowflake Scripting 또는 Javascript 저장 프로시저 또는 Snowflake Scripting 익명 블록에서 동적 SQL을 사용할 수 있습니다. Snowflake 드라이버 또는 Snowflake SQL REST API 를 사용하는 애플리케이션 코드에서 동적 SQL을 사용할 수도 있습니다.

이 예제에서는 Snowflake Scripting을 사용하여 저장 프로시저를 생성합니다. 저장 프로시저는 SQL 텍스트를 입력으로 받고 여기에 텍스트를 추가하여 SQL 문이 포함된 문자열을 구성합니다. 그런 다음 EXECUTE IMMEDIATE 명령을 사용하여 동적 SQL을 실행합니다.

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;

Copy

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
  row_count INTEGER DEFAULT 0;
  stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
  res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
  cur CURSOR FOR res;
BEGIN
  OPEN cur;
  FETCH cur INTO row_count;
  RETURN row_count;
END;
$$
;

Copy

다음 예제에서는 프로시저를 호출합니다.

CALL get_num_results('SELECT 1');

Copy

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

동적 SQL은 바인딩 변수를 지원합니다. 다음 Snowflake Scripting 예제는 ? 자리 표시자로 표시되는 바인딩 변수를 사용하여 런타임에 동적으로 SQL 문을 구성합니다. 이 블록은 다음 invoices 테이블에서 데이터를 선택합니다.

CREATE OR REPLACE TABLE invoices (price NUMBER(12, 2));
INSERT INTO invoices (price) VALUES
  (11.11),
  (22.22);

Copy

익명 블록을 실행합니다.

DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;

Copy

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

Copy

+-------+
| PRICE |
|-------|
| 22.22 |
+-------+

동적으로 SQL을 구성하는 기법 비교¶

다음 테이블에서는 동적으로 SQL을 구성하는 기법의 장단점을 설명합니다.

기법	장점	단점
TO_QUERY 함수	간단한 구문 내장된 오류 처리 동적으로 SQL을 구성하는 사용 사례에 대한 특정 의미 체계 자동으로 결정된 결과 세트	실행 전에 쿼리를 설명하거나 설명할 수 없습니다. SELECT 문의 FROM 절에서만 유효합니다. Snowflake 특정
동적 SQL	TO_QUERY 함수보다 더 일반적이고 유연함 쿼리는 실행 전에 설명되거나 설명될 수 있습니다.	TO_QUERY 함수보다 더 복잡함 수동 오류 처리