CREATE PROCEDURE

새 저장 프로시저를 생성하거나 현재 데이터베이스의 기존 프로시저를 바꿉니다.

자세한 정보와 지침은 Amazon Redshift에서 저장 프로시저 생성 섹션을 참조하세요.

필수 권한

CREATE OR REPLACE PROCEDURE를 실행하려면 다음 방법 중 하나에 대한 권한이 있어야 합니다.

CREATE PROCEDURE의 경우
- 수퍼유저
- 저장 프로시저가 생성된 스키마에 대한 CREATE 및 USAGE 권한이 있는 사용자
REPLACE PROCEDURE의 경우
- 수퍼유저
- 프로시저 소유자

구문


CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

파라미터

OR REPLACE

이름 및 입력 인수 데이터 형식이 같은 프로시저 또는 서명이 같은 프로시저인 경우 이런 프로시저가 이미 하나 존재하므로 기존 프로시저가 대체됨을 지정하는 절입니다. 프로시저는 똑같은 데이터 형식 집합을 정의하는 새 프로시저로만 바꿀 수 있습니다.

기존 프로시저와 이름이 동일하지만 서명이 다른 프로시저를 정의하는 경우, 새 프로시저를 생성합니다. 다시 말해 프로시저 이름이 오버로드됩니다. 자세한 내용은 프로시저 이름 오버로드 단원을 참조하십시오.

sp_procedure_name

프로시저의 이름입니다. 스키마 이름을 지정하는 경우(예: myschema.myprocedure), 프로시저는 지정된 스키마에서 생성됩니다. 그렇지 않으면, 프로시저가 현재 스키마에서 생성됩니다. 유효한 이름에 대한 자세한 내용은 이름 및 식별자 섹션을 참조하세요.

모든 저장 프로시저 이름에 sp_를 접두사로 사용하는 것이 좋습니다. Amazon Redshift는 저장 프로시저 이름용으로 sp_ 접두사를 예약합니다. sp_ 접두사를 사용하여 저장 프로시저 이름이 기존 또는 향수 Amazon Redshift 기본 제공 저장 프로시저 또는 함수 이름과 충돌하지 않도록 할 수 있습니다. 자세한 내용은 저장 프로시저 명명 단원을 참조하십시오.

입력 인수에 대한 데이터 형식 또는 서명이 서로 다른 경우 같은 이름을 가진 프로시저를 2개 이상 정의할 수 있습니다. 다시 말해 이 경우 프로시저 이름이 오버로드됩니다. 자세한 내용은 프로시저 이름 오버로드 섹션을 참조하세요.

[argname] [ argmode] argtype

인수 이름, 인수 모드 및 데이터 형식의 목록입니다. 데이터 형식만 필수입니다. 이름과 모드는 선택 사항이고, 위치를 전환할 수 있습니다.

인수 모드는 IN, OUT 또는 INOUT일 수 있습니다. 기본값은 IN입니다.

OUT 및 INOUT 인수를 사용하여 프로시저 호출에서 값을 하나 이상 반환할 수 있습니다. OUT 또는 INOUT 인수가 있는 경우, 프로시저 호출이 n 열이 포함된 결과 행 하나를 반환합니다. 여기에서 n은 OUT 또는 INOUT 인수의 총 수입니다.

INOUT 인수는 동시에 입력 및 출력 인수입니다. 입력 인수에는 IN 인수와 INOUT 인수가 모두 포함되며, 출력 인수에는 OUT 인수와 INOUT 인수가 모두 포함됩니다.

OUT 인수는 CALL 문의 일부로 지정되지 않습니다. 저장 프로시저 CALL 문에서 INOUT 인수를 지정합니다. INOUT 인수는 중첩 호출에서 값을 전달 및 반환하고 refcursor를 반환할 때 유용할 수 있습니다. refcursor 형식에 대한 자세한 내용은 커서 섹션을 참조하세요.

인수 데이터 형식은 모든 표준 Amazon Redshift 데이터 형식일 수 있습니다. 또한 인수 데이터 형식은 refcursor일 수 있습니다.

최대 32개의 입력 인수와 32개의 출력 인수를 지정할 수 있습니다.

AS $$ procedure_body $$

실행할 프로시저를 둘러싼 구문입니다. 리터럴 키워드 AS $$ 및 $$가 필요합니다.

Amazon Redshift에서는 $ 인용이라는 형식을 사용하여 프로시저의 문을 묶어야 합니다. 묶음 기호 내에 있는 것은 모두 정확히 그대로 전달됩니다. 문자열의 내용은 문자 그대로 작성되므로 특수 문자를 이스케이프할 필요가 없습니다.

$ 인용 사용 시, 달러 기호 쌍($$)을 사용하면 다음 예에 표시된 것처럼 실행할 문의 시작과 끝을 나타낼 수 있습니다.


$$ my statement $$

각 쌍의 달러 기호들 사이에서 선택적으로 문을 식별하는 데 도움이 되는 문자열을 지정할 수 있습니다. 사용하는 문자열은 묶음 쌍의 시작과 끝에서 모두 동일해야 합니다. 이 문자열은 대/소문자를 구분하고 달러 기호를 포함할 수 없는 경우를 제외하면 따옴표가 없는 식별자와 똑같은 제약 조건을 따릅니다. 다음 예에서는 문자열 테스트를 사용합니다.


$test$ my statement $test$

이 구문은 중첩 달러 인용에도 유용합니다. 달러 인용에 대한 자세한 내용은 PostgreSQL 설명서의 어휘 구조에서 “달러 기호로 인용된 문자열 상수”를 참조하십시오.

procedure_body

유효한 PL/pgSQL 문 세트입니다. PL/pgSQL 문은 루프 및 조건 표현식을 비롯한 프로시저 구문으로 SQL 명령을 보완하여 논리 흐름을 제어합니다. COPY, UNLOAD, INSERT 등의 데이터 수정 언어(DML)와 CREATE TABLE 등의 데이터 정의 언어(DDL)를 포함한 대부분의 SQL 명령을 프로시저 본문에서 사용할 수 있습니다. 자세한 내용은 PL/pgSQL 언어 참조 단원을 참조하십시오.

LANGUAGE plpgsql

언어 값입니다. plpgsql를 지정합니다. plpgsql을 사용하려면 언어에 대한 사용 권한이 필요합니다. 자세한 내용은 GRANT 단원을 참조하십시오.

NONATOMIC

NONATOMIC 트랜잭션 모드에서 저장 프로시저를 생성합니다. NONATOMIC 모드는 프로시저 내에서 문을 자동으로 커밋합니다. 또한 NONATOMIC 프로시저 내에서 오류가 발생하여 예외 블록에서 처리되는 경우 오류가 다시 발생하지 않습니다. 자세한 내용은 트랜잭션 관리 및 RAISE 단원을 참조하세요.

저장 프로시저를 NONATOMIC으로 정의할 때 다음 사항을 고려하세요.

저장 프로시저 호출을 중첩할 때는 모든 프로시저를 동일한 트랜잭션 모드에서 생성해야 합니다.
NONATOMIC 모드에서 프로시저를 생성할 때는 SECURITY DEFINER 옵션과 SET configuration_parameter 옵션이 지원되지 않습니다.
암시적 커밋이 처리되면 (명시적으로 또는 암시적으로) 열린 모든 커서가 자동으로 닫힙니다. 따라서 커서 루프를 시작하기 전에 명시적 트랜잭션을 열어 루프 반복 내의 SQL이 암시적으로 커밋되지 않도록 해야 합니다.

SECURITY INVOKER | SECURITY DEFINER

NONATOMIC이 지정된 경우 SECURITY DEFINER 옵션이 지원되지 않습니다.

프로시저의 보안 모드는 실행 시간에 프로시저의 액세스 권한을 결정합니다. 프로시저는 기본 데이터베이스 객체에 액세스할 권한이 있어야 합니다.

SECURITY INVOKER 모드에서 프로시저는 프로시저를 호출하는 사용자의 권한을 사용합니다. 사용자는 기본 데이터베이스 객체에 대한 명시적 권한이 있어야 합니다. 기본값은 SECURITY INVOKER입니다.

SECURITY DEFINER 모드에서, 프로시저는 프로시저 소유자의 권한을 사용합니다. 프로시저 소유자는 런타임에 프로시저를 소유하는 사용자로 정의되며, 프로시저를 처음 정의한 사용자일 필요는 없습니다. 프로시저를 호출하는 사용자는 프로시저에 대한 권한을 실행해야 하지만, 기본 객체에 대한 권한은 필요하지 않습니다.

SET configuration_parameter { TO value | = value }

NONATOMIC이 지정된 경우 이 옵션이 지원되지 않습니다.

SET 절은 프로시저가 입력될 때 지정된 configuration_parameter가 지정된 값으로 설정되게 합니다. 그런 다음 이 절은 프로시저가 종료되면 configuration_parameter를 이전 값으로 복원합니다.

사용 노트

SECURITY DEFINER 옵션을 사용하여 저장 프로시저를 생성한 경우, 저장 프로시저 내에서 CURRENT_USER 함수를 호출하면 Amazon Redshift는 저장 프로시저 소유자의 사용자 이름을 반환합니다.

예

참고

이러한 예제를 실행할 때 다음과 비슷한 오류가 발생하는 경우


ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

Amazon Redshift의 저장 프로시저 개요 섹션을 참조하세요.

다음 예제에서는 입력 파라미터가 두 개 있는 프로시저를 생성합니다.


CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;

참고

저장 프로시저를 작성할 때는 민감한 값을 보호하기 위한 모범 사례를 참조하는 것이 좋습니다.

민감한 정보를 저장 프로시저 로직 내에 하드 코딩하지 마십시오. 예를 들어, 저장 프로시저 본문의 CREATE USER 문에 사용자 암호를 할당하지 마십시오. 하드 코딩된 값이 카탈로그 테이블에 스키마 메타 데이터로 기록될 수 있기 때문에 보안 위험이 따릅니다. 암호와 같은 민감한 값은 파라미터를 사용하여 저장 프로시저에 인수로 전달하십시오.

저장된 프로시저에 대한 자세한 내용은 프로시저 생성 및 Amazon Redshift에서 저장 프로시저 생성을 참조하십시오. 카탈로그 테이블에 대한 자세한 내용은 시스템 카탈로그 테이블을 참조하십시오.

다음 예제에서는 IN 파라미터 한 개, OUT 파라미터 한 개, INOUT 파라미터 한 개가 있는 프로시저를 생성합니다.


CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;

javascript가 브라우저에서 비활성화되거나 사용이 불가합니다.

AWS 설명서를 사용하려면 Javascript가 활성화되어야 합니다. 지침을 보려면 브라우저의 도움말 페이지를 참조하십시오.

문서 규칙

사용 사례

CREATE RLS POLICY