저장 프로시저 작업하기

저장 프로시저를 사용하면여러 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 명령을 사용해 다음을 볼 수 있습니다.

    • 인자의 이름과 데이터 타입.

    • 프로시저의 본문과 프로시저가 소유자 또는 호출자로 실행되는지 여부.

    • 반환 값의 데이터 타입.

    • 기타 유용한 정보.

언어: 한국어