PL/pgSQL의 구조

PL/pgSQL은 다른 프로시저 언어와 여러 동일한 구문를 가진 프로시저 언어입니다.

블록

PL/pgSQL은 블록 구조 언어입니다. 프로시저의 전체 본문은 블록으로 정의되어 있으며, 변수 선언 및 PL/pgSQL 문이 포함되어 있습니다. 문은 중첩 블록, 즉 하위 블록일 수도 있습니다.

선언 및 문은 세미콜론으로 끝납니다. 세미콜론이 있는 블록 또는 하위 블록에서는 END 키워드를 따릅니다. DECLARE 및 BEGIN 키워드 뒤에는 세미콜론을 사용하지 마십시오.

모든 키워드 및 식별자는 대문자와 소문자 혼합으로 작성할 수 있습니다. 큰 따옴표로 묶이지 않은 한 식별자는 소문자로 암시적으로 변환됩니다.

이중 하이픈(--)은 행의 끝까지 확장되는 주석을 시작합니다. /*는 다음 */까지 확장되는 블록 주석을 시작합니다. 블록 주석은 중첩할 수 없습니다. 그러나 이중 하이픈 주석을 블록 주석으로 묶을 수 있고, 이중 하이픈은 블록 주석 구분 기호 /* 및 */를 숨길 수 있습니다.

블록의 문 섹션의 모든 문은 하위 블록일 수 있습니다. 하위 블록을 사용하여 논리적 그룹화를 하거나 변수를 작은 문 그룹으로 지역화할 수 있습니다.

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
END [ label ];  

블록 앞에 오는 선언 섹션에 선언된 변수는 블록이 입력될 때마다 기본값으로 초기화됩니다. 다시 말해 함수 호출당 한 번만 초기화되지 않습니다.

다음은 그 한 예입니다.

CREATE PROCEDURE update_value() AS $$
DECLARE
  value integer := 20;
BEGIN
  RAISE NOTICE 'Value here is %', value;  -- Value here is 20
  value := 50;
  --
  -- Create a subblock
  --
  DECLARE
    value integer := 80;
  BEGIN
    RAISE NOTICE 'Value here is %', value;  -- Value here is 80
  END;

  RAISE NOTICE 'Value here is %', value;  -- Value here is 50
END;
$$ LANGUAGE plpgsql;               

레이블을 사용하여 EXIT 문에 사용할 블록을 식별하거나 블록에 선언된 변수의 이름을 한정합니다.

PL/pgSQL의 문을 그룹화하기 위한 BEGIN/END 사용을 트랜잭션 제어를 위한 데이터베이스 명령과 혼동하지 마십시오. PL/pgSQL의 BEGIN 및 END는 그룹화에만 사용되며, 트랜잭션을 시작하거나 종료하지 않습니다.

변수 선언

블록의 DECLARE 섹션에서 루프 변수를 제외하고 블록의 모든 변수를 선언합니다. 변수는 모든 유효한 Amazon Redshift 데이터 형식을 사용할 수 있습니다. 지원되는 데이터 형식은 데이터 타입 섹션을 참조하세요.

PL/pgSQL 변수는 Amazon Redshift 지원 데이터 형식과 RECORD 및 refcursor일 수 있습니다. RECORD에 대한 자세한 내용은 레코드 형식 섹션을 참조하세요. refcursor에 대한 자세한 내용은 커서 섹션을 참조하세요.

DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

다음으로 변수 선언 예를 찾을 수 있습니다.

customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;

정수 범위를 반복하는 FOR 루프의 루프 변수는 자동으로 정수 변수로 선언됩니다.

DEFAULT 절이 제공된 경우 블록이 입력되면 변수에 할당되는 초기 값을 지정합니다. DEFAULT 절이 제공되지 않은 경우 변수는 SQL NULL 상태로 초기화됩니다. CONSTANT 옵션은 변수가 할당되지 못하게 합니다. 따라서 값이 블록 기간 동안 일정하게 유지됩니다. NOT NULL이 지정되지 않은 경우 null 값을 할당하면 실행 시간 오류가 발생합니다. NOT NULL로 선언된 모든 변수는 null이 아닌 기본값이 지정되어야 합니다.

기본값은 블록이 입력될 때마다 평가됩니다. 예를 들어 timestamp 유형의 변수에 now()를 할당하면 변수가 함수가 미리 컴파일된 시간이 아니라 현재 함수 호출의 시간을 갖게 됩니다.

quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;

refcursor 데이터 형식은 저장 프로시저 내 커서 변수의 데이터 형식입니다. refcursor 값은 저장 프로시저 내에서 반환될 수 있습니다. 자세한 내용은 결과 세트 반환 섹션을 참조하세요.

별칭 선언

저장 프로시저의 서명에 인수 이름이 생략된 경우, 해당 인수의 별칭을 선언할 수 있습니다.

name ALIAS FOR $n;

기본 제공 변수

다음 기본 제공 변수가 지원됩니다.

• FOUND

• SQLSTATE

• SQLERRM

• GET DIAGNOSTICS integer_var := ROW_COUNT;

FOUND는 부울 형식의 특수 변수입니다. FOUND는 각 프로시저 호출 내에서 false를 시작합니다. FOUND는 다음 형식의 문으로 설정됩니다.

• SELECT INTO

  행을 반환하면 FOUND를 true로 설정하고, 행을 반환하지 않으면 false로 설정합니다.

• UPDATE, INSERT, DELETE

  하나 이상의 행이 영향을 받으면 FOUND를 true로 설정하고, 행이 영향을 받지 않으면 false로 설정합니다.

• FETCH

  행을 반환하면 FOUND를 true로 설정하고, 행을 반환하지 않으면 false로 설정합니다.

• FOR 문

  FOR 문이 한 번 이상 반복되면 FOUND를 true로 설정하고, 그렇지 않으면 false로 설정합니다. 이는 FOR 문의 세 가지 변형인 정수 FOR 루프, 레코드 세트 FOR 루프, 동적 레코드 세트 FOR 루프 모두에 적용됩니다.

  FOR 루프가 종료되면 FOUND가 설정됩니다. 루프 런타임 내부에서 FOUND는 FOR 문에 의해 수정되지 않습니다. 그러나 루프 본문 내 다른 문의 실행에 의해 변경될 수 있습니다.

다음은 그 한 예입니다.

CREATE TABLE employee(empname varchar);
CREATE OR REPLACE PROCEDURE show_found()
AS  $$
DECLARE
  myrec record;
BEGIN
  SELECT INTO myrec * FROM employee WHERE empname = 'John';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'employee John not found';
  END IF;
END;
$$ LANGUAGE plpgsql;

예외 핸들러 내의 특수 변수 SQLSTATE에는 발생한 예외에 해당하는 오류 코드가 포함되어 있습니다. 특수 변수 SQLERRM에는 해당 예외와 관련된 오류 메시지가 포함되어 있습니다. 이러한 변수는 예외 핸들러 외부에서 정의되지 않으며 사용하면 오류가 발생합니다.

다음은 그 한 예입니다.

CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS
$$
BEGIN
  UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
  EXECUTE 'select invalid';
  EXCEPTION WHEN OTHERS THEN
  RAISE INFO 'error message SQLERRM %', SQLERRM;
  RAISE INFO 'error message SQLSTATE %', SQLSTATE;
END;
$$ LANGUAGE plpgsql;               

ROW_COUNT는 GET DIAGNOSTICS 명령과 함께 사용됩니다. 이는 SQL 엔진으로 전송된 마지막 SQL 명령에서 처리된 행 수를 보여 줍니다.

다음은 그 한 예입니다.

CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;               

레코드 형식

RECORD 형식은 데이터 형식이 아니라, 자리 표시자에 불과합니다. 레코드 형식 변수는 SELECT 또는 FOR 명령 중에 할당된 행의 실제 행 구조를 가정합니다. 레코드 변수의 하위 구조는 값이 할당될 때마다 변경될 수 있습니다. 레코드 변수가 처음 할당될 때까지 하위 구조가 없습니다. 그 안에 있는 필드에 액세스하려고 시도하면 실행 시간 오류가 발생합니다.

name RECORD;

다음은 그 한 예입니다.

CREATE TABLE tbl_record(a int, b int);
INSERT INTO tbl_record VALUES(1, 2);
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    RAISE INFO 'a = %', rec.a;
  END LOOP;
END;
$$;