저장 프로시저 작업하기¶

저장 프로시저를 사용하면여러 SQL 문을 프로시저 논리와 결합하여 복잡한 비즈니스 논리를 포함할 수 있는 모듈식 코드를 만들 수 있습니다.

이 항목의 내용:

참고

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

저장 프로시저의 명명 규칙¶

Snowflake에서 시행하는 규칙에 따라 프로시저의 이름을 지정해야 합니다.

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

트랜잭션 관리¶

저장 프로시저는 원자성이 아닙니다. 저장 프로시저의 한 문이 실패할 경우 저장 프로시저의 다른 문이 반드시 롤백되는 것은 아닙니다.

트랜잭션과 함께 저장 프로시저를 사용해 문 그룹을 원자성으로 만들 수 있습니다. 자세한 내용은 저장 프로시저와 트랜잭션 섹션을 참조하십시오.

일반적인 팁¶

대칭 코드¶

어셈블리 언어로 프로그래밍하는 작업에 익숙하다면 다음과 같은 유사성이 도움이 된다는 점을 알아차릴 수도 있습니다. 어셈블리 언어에서는 함수가 종종 대칭적인 방식으로 환경을 만들고 실행 취소합니다. 예:

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

Copy

저장 프로시저에서 다음 접근 방식을 사용할 수 있습니다.

저장 프로시저가 세션을 일시적으로 변경하는 경우 그 프로시저는 해당 변경 사항을 실행 취소한 후에 반환해야 합니다.
저장 프로시저가 예외 처리 또는 분기를 활용하거나 어떤 문을 실행할지에 영향을 줄 수 있는 다른 논리를 활용하는 경우, 특정 호출 중에 선택하는 분기와 관계없이 생성한 모든 것을 정리해야 합니다.

예를 들어, 작성한 코드가 아래에 표시된 의사 코드와 비슷한 형태일 수 있습니다.

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

Copy

저장 프로시저 호출하기¶

SQL 명령을 사용하여 저장 프로시저를 호출합니다. 저장 프로시저 호출에 대한 자세한 내용은 저장 프로시저 호출하기 섹션을 참조하십시오.

권한¶

저장 프로시저는 다음 두 가지 유형의 권한을 활용합니다.

저장 프로시저 자체에 대한 직접적인 권한.
저장 프로시저가 액세스하는 데이터베이스 오브젝트(예: 테이블)에 대한 권한.

저장 프로시저에 대한 권한¶

저장 프로시저는 다른 데이터베이스 오브젝트(테이블, 뷰, UDFs 등)와 마찬가지로 역할이 소유하고 다른 역할에 부여할 수 있는 하나 또는 그 이상의 권한을 가집니다.

현재, 다음 권한이 저장 프로시저에 적용됩니다.

USAGE
OWNERSHIP

저장 프로시저를 사용하려는 역할은 소유자이거나 저장 프로시저에 대한 USAGE 권한을 부여받았어야 합니다.

저장 프로시저에서 액세스하는 데이터베이스 오브젝트에 대한 권한¶

이 주제는 Understanding Caller’s Rights and Owner’s Rights Stored Procedures 에서 다룹니다.

저장 프로시저 고려 사항¶

저장 프로시저는 중첩과 재귀를 허용하지만, 사용자 정의 저장 프로시저에 대한 중첩 호출의 현재 최대 스택 깊이는 5이며(최상위 저장 프로시저 포함), 호출 체인의 개별 저장 프로시저가 많은 양의 리소스를 사용하면 이보다 적을 수 있습니다.
드문 경우지만, 저장 프로시저를 동시에 너무 많이 호출하면 교착 상태가 발생할 수 있습니다.

SQL 주입¶

저장 프로시저는 동적으로 SQL 문을 만들고 실행할 수 있습니다. 하지만 이렇게 하면 특히 공개 소스나 신뢰할 수 없는 소스의 입력 데이터를 사용해 SQL 문을 만드는 경우 SQL 주입 공격을 허용할 수 있습니다.

텍스트를 연결하기보다는 매개 변수를 바인딩하여 SQL 주입 공격의 위험을 최소화할 수 있습니다. 변수를 바인딩하는 예는 변수 바인딩하기 를 참조하십시오.

연결을 사용하려는 경우 공개 소스의 입력 데이터를 사용해 SQL을 동적으로 생성할 때 입력 데이터를 주의 깊게 확인해야 합니다. 제한된 권한(예: 읽기 전용 액세스 또는 특정 테이블이나 뷰에만 액세스)을 가진 역할을 사용한 쿼리와 같은 다른 예방 조치를 취할 수도 있습니다.

SQL 주입 공격에 대한 자세한 내용은 (Wikipedia에서) SQL 주입 을 참조하십시오.

저장 프로시저의 디자인 팁¶

저장 프로시저 디자인을 위한 몇 가지 팁을 소개합니다.

이 저장 프로시저에 필요한 리소스(예: 테이블)는 무엇일까요?
어떤 특권이 필요할까요?

어떤 데이터베이스 오브젝트에 액세스하고 어떤 역할이 저장 프로시저를 실행하며 해당 역할에 어떤 권한이 필요할지 생각해보십시오.

프로시저가 호출자 권한 저장 프로시저여야 할 경우에는 그 특정 프로시저 또는 관련 프로시저의 그룹을 실행할 역할을 만들 수 있습니다. 그런 다음, 그 역할에 필수적인 권한을 부여하고 그 역할을 알맞은 사용자에게 부여할 수 있습니다.
저장 프로시저를 호출자 권한 또는 소유자 권한으로 실행해야 합니까? 이 항목에 대한 자세한 내용은 Understanding Caller’s Rights and Owner’s Rights Stored Procedures 를 참조하십시오.
프로시저는 오류를 어떻게 처리해야 할까요? 예를 들어 필요한 테이블이 없거나 인자가 잘못된 경우 프로시저가 어떻게 해야 할까요?
저장 프로시저가 예컨대 로그 테이블에 작성하는 방식으로 활동이나 오류를 기록해야 할까요?
저장 프로시저를 사용할 때와 UDF를 사용할 때에 대해 설명한 저장 프로시저를 작성할지 사용자 정의 함수를 작성할지 선택하기 도 참조하십시오.

저장 프로시저 문서화하기¶

저장 프로시저는 보통 재사용하거나 종종 공유할 수도 있도록 작성됩니다. 저장 프로시저를 문서화하면 저장 프로시저를 더 쉽게 사용하고 유지 관리할 수 있습니다.

아래에 저장 프로시저 문서화에 대한 몇 가지 일반적인 권장 사항을 소개합니다.

일반적으로, 다음과 같이 최소한 두 개 이상의 대상 그룹이 저장 프로시저에 대해 알고 싶어 합니다.

사용자/호출자.
프로그래머/작성자.

사용자와 프로그래머를 위해 다음 사항을 각각 문서화하십시오.

저장 프로시저의 이름입니다.

저장 프로시저의 “위치”(데이터베이스 및 스키마).

저장 프로시저의 목적.

각 입력 매개 변수의 이름, 데이터 타입, 의미.

반환 값의 이름, 데이터 타입, 의미. 반환 값이 하위 필드를 포함하는 VARIANT와 같은 복합 형식인 경우 해당 하위 필드를 문서화하십시오.

저장 프로시저가 해당 환경의 정보(예: 세션 변수 또는 세션 매개 변수)에 의존하는 경우 이들의 이름, 목적, 유효한 값을 문서화하십시오.

반환된 오류, 발생한 예외 등.

프로시저를 실행하기 위해 필요한 역할 또는 권한. (이 주제에 대한 자세한 내용은 저장 프로시저의 디자인 팁 의 역할에 대한 설명을 참조하십시오.)

프로시저가 호출자 권한 프로시저인지, 소유자 권한 프로시저인지 여부.

모든 필수 조건(예: 프로시저가 호출되기 전에 존재해야 하는 테이블).

(반환 값 이외의) 모든 출력(예: 생성된 새 테이블).

모든 “파생 효과”(예: 권한 변경, 이전 데이터 삭제 등.) (함수와는 달리) 저장 프로시저는 대부분 반환 값이 아니라 파생 효과에 대해 특별히 호출되므로, 그와 같은 파생 효과를 문서화해야 합니다.

저장 프로시저를 실행한 후 정리가 필요한 경우 해당 정리 사항을 문서화합니다.

프로시저를 다중 문 트랜잭션의 일부로 호출할 수 있는지(AUTOCOMMIT=FALSE), 트랜잭션 외부에서 실행해야 하는지(AUTOCOMMIT=TRUE) 여부.

호출 예와 반환되는 내용의 예.

제한 사항(해당되는 경우). 예를 들어, 프로시저가 테이블을 읽고 테이블의 각 행에서 정보를 포함한 VARIANT를 반환한다고 가정합니다. VARIANT가 VARIANT의 유효한 최대 크기보다 커질 수 있으므로, 프로시저가 액세스하는 테이블의 최대 행 수에 대한 정보를 호출자에게 제공해야 할 수도 있습니다.

경고(해당되는 경우).

문제 해결 팁.

프로그래머의 경우:

작성자.

프로시저가 호출자 권한 프로시저 또는 소유자 권한 프로시저로 생성된 이유를 설명합니다. 이유가 분명하지 않을 수도 있습니다.

저장 프로시저는 중첩될 수 있지만, 중첩 깊이에는 제한이 있습니다. 저장 프로시저가 다른 저장 프로시저를 호출하고 다른 저장 프로시저가 이 저장 프로시저를 호출할 가능성도 있는 경우에는 호출자가 저장 프로시저 호출이 최대 호출 스택 깊이를 초과할 수 있을지 여부를 알 수 있도록 저장 프로시저 호출 스택의 알려진 최대 깊이를 지정할 수 있습니다.

디버깅 팁.

이 정보의 위치와 형식은 사용자가 결정할 수 있습니다. 예를 들어, 내부 웹 사이트에 HTML 형식으로 정보를 저장할 수 있습니다. 정보를 저장할 위치를 결정하기 전에 조직에서 다른 제품에 대한 유사한 정보나 뷰, 사용자 정의 함수 등의 다른 Snowflake 기능에 대한 유사한 정보를 저장하는 위치에 대해 생각해보십시오.

기타 팁:

거의 모든 소스 코드 조각에 대해 그래야 하는 것처럼, 소스 코드에 주석을 포함하십시오.
- 코드를 보고 그 의미를 리버스 엔지니어링하기는 어렵다는 점을 기억하십시오. 알고리즘의 작동 방식뿐 아니라 그 알고리즘의 목적도 설명하십시오.
저장 프로시저는 CREATE PROCEDURE 또는 ALTER PROCEDURE 문으로 지정할 수 있는 선택적 COMMENT를 허용합니다. 다른 사람들은 SHOW PROCEDURES 명령을 실행하여 이 주석을 읽을 수 있습니다.
가능하다면 소스 코드 제어 시스템에 각 저장 프로시저의 CREATE PROCEDURE 명령 마스터 복사본을 보관하기 바랍니다. Snowflake의 Time Travel 기능은 저장 프로시저에 적용되지 않으므로, 이전 버전의 저장 프로시저 조회는 Snowflake 외부에서 수행해야 합니다. 소스 코드 제어 시스템을 사용할 수 없는 경우 테이블의 VARCHAR 필드에 CREATE PROCEDURE 명령을 저장하고 각각의 새 버전을 추가하여(이전 버전을 바꾸지 않고) 제어 시스템 하나를 부분적으로 시뮬레이션할 수 있습니다.
저장 프로시저에 대한 정보를 제공하는 데 도움이 되는 명명 규칙을 사용하십시오. 예를 들어, 이름의 접두사나 접미사는 프로시저가 호출자 권한 저장 프로시저인지, 소유자 권한 저장 프로시저인지 여부를 나타낼 수 있습니다. (예: 호출자 권한에 대한 접두사로 cr_ 을 사용할 수 있습니다.)
주석뿐 아니라 입력 인자의 데이터 타입과 순서도 보려면 SHOW PROCEDURES 명령을 사용할 수 있습니다. 하지만 이 명령은 인자의 이름과 데이터 타입만 표시할 뿐, 인자를 설명하는 것은 아닙니다.
적절한 권한이 있는 경우 DESCRIBE PROCEDURE 명령을 사용해 다음을 볼 수 있습니다.
- 인자의 이름과 데이터 타입.
- 프로시저의 본문과 프로시저가 소유자 또는 호출자로 실행되는지 여부.
- 반환 값의 데이터 타입.
- 기타 유용한 정보.