DECLARE — 커서 정의

DECLARE — 커서 정의

DECLARE 문으로 **커서(cursor)**를 만듭니다. 커서는 규모가 큰 질의 결과에서 한 번에 몇 줄씩만 꺼내 읽을 수 있게 만들어 주는 도구예요. 커서를 만든 뒤에는 FETCH로 실제 행을 하나씩 가져옵니다.

출처: PostgreSQL 문서

본문

개요 (Synopsis)

DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query

설명 (Description)

DECLARE는 사용자가 커서를 만들 수 있게 해요. 커서는 큰 질의에서 한 번에 적은 수의 행을 꺼내 읽는 데 쓰이죠. 커서가 만들어진 뒤에는 FETCH로 그 커서에서 행을 가져옵니다.

참고: 이 페이지는 커서를 SQL 명령 수준에서 사용하는 방법을 설명해요. PL/pgSQL 함수 안에서 커서를 쓰려면 규칙이 다르니 41.7절을 참고하세요.

매개변수 (Parameters)

  • name — 만들 커서의 이름이에요. 세션 안에서 다른 활성 커서 이름과 달라야 합니다.
  • BINARY — 커서가 데이터를 텍스트 형식이 아니라 이진(binary) 형식으로 돌려주게 만듭니다.
  • ASENSITIVE / INSENSITIVE — 커서 민감도(cursor sensitivity)는 커서가 선언된 뒤 같은 트랜잭션 안에서 커서의 기반 데이터가 바뀌었을 때 그 변경이 커서에 보이는지를 결정해요. INSENSITIVE는 보이지 않음을, ASENSITIVE는 구현에 따라 달라짐을 의미합니다. 세 번째 동작인 SENSITIVE(그런 변경이 커서에 보임)는 PostgreSQL에서 사용할 수 없어요. PostgreSQL에서는 모든 커서가 insensitive라서, 이 키워드들은 효과가 없고 SQL 표준과의 호환성을 위해서만 받아들여집니다.
    • INSENSITIVE를 FOR UPDATE나 FOR SHARE와 함께 지정하면 오류가 나요.
  • SCROLL / NO SCROLL — SCROLL은 커서가 순서를 거슬러(예: 뒤로) 행을 가져올 수 있게 지정해요. 질의 실행 계획의 복잡도에 따라 SCROLL을 지정하면 실행 시간에 성능 저하가 올 수 있어요. NO SCROLL은 커서로 순서를 거슬러 행을 가져올 수 없게 합니다. 기본값은 어떤 경우에는 스크롤링을 허용하는 것인데, SCROLL을 지정한 것과는 달라요. 자세한 내용은 아래 Notes를 보세요.
  • WITH HOLD / WITHOUT HOLD — WITH HOLD는 커서를 만든 트랜잭션이 성공적으로 커밋된 뒤에도 그 커서를 계속 사용할 수 있게 해요. WITHOUT HOLD는 커서를 만든 트랜잭션 밖에서는 커서를 쓸 수 없다는 뜻입니다. 둘 다 지정하지 않으면 WITHOUT HOLD가 기본값이에요.
  • query — 커서가 돌려줄 행을 제공할 SELECT 또는 VALUES 명령이에요.

ASENSITIVE, BINARY, INSENSITIVE, SCROLL 키워드는 어떤 순서로도 나타날 수 있습니다.

참고 사항 (Notes)

일반 커서는 SELECT가 만들어 내는 것과 같은 텍스트 형식으로 데이터를 돌려줘요. BINARY 옵션은 커서가 이진 형식으로 데이터를 돌려주게 지정해요. 이렇게 하면 서버와 클라이언트 양쪽의 변환 작업이 줄지만, 그만큼 플랫폼 의존적인 이진 데이터 형식을 다루는 프로그래머의 노력은 늘어나죠. 예를 들어 질의가 정수 컬럼에서 값 1을 반환한다면, 기본 커서로는 "1"이라는 문자열을 얻지만 이진 커서로는 그 값의 내부 표현(빅엔디안 바이트 순서)을 담은 4바이트 필드를 얻게 돼요.

이진 커서는 조심해서 써야 합니다. psql을 포함한 많은 응용 프로그램이 이진 커서를 처리할 준비가 되어 있지 않고, 데이터가 텍스트 형식으로 돌아오기를 기대하거든요.

참고: 클라이언트 응용 프로그램이 "extended query" 프로토콜로 FETCH 명령을 보내면, Bind 프로토콜 메시지가 데이터를 텍스트 형식으로 가져올지 이진 형식으로 가져올지를 지정해요. 이 선택이 커서가 정의된 방식을 덮어씁니다. 따라서 extended query 프로토콜을 쓸 때는 그 자체로서의 "이진 커서" 개념이 낡은 것이나 다름없어요 — 어떤 커서든 텍스트나 이진 중 어느 쪽으로도 취급할 수 있으니까요.

WITH HOLD를 지정하지 않으면, 이 명령으로 만든 커서는 현재 트랜잭션 안에서만 쓸 수 있어요. 그러니 WITH HOLD 없는 DECLARE는 트랜잭션 블록 밖에서는 쓸모가 없어요 — 커서가 명령이 끝날 때까지만 살아남으니까요. 그래서 PostgreSQL은 트랜잭션 블록 밖에서 그런 명령을 쓰면 오류를 보고해요. 트랜잭션 블록을 정의하려면 BEGIN과 COMMIT(또는 ROLLBACK)을 사용하면 됩니다.

WITH HOLD가 지정되고 커서를 만든 트랜잭션이 성공적으로 커밋되면, 같은 세션의 이후 트랜잭션들도 그 커서에 계속 접근할 수 있어요. (단, 만든 트랜잭션이 중단(abort)되면 커서는 제거됩니다.) WITH HOLD로 만든 커서는 명시적인 CLOSE 명령을 내리거나 세션이 끝나면 닫혀요. 현재 구현에서, 잡고 있는(held) 커서가 나타내는 행들은 임시 파일이나 메모리 영역에 복사되어 이후 트랜잭션에서도 계속 사용 가능하게 유지됩니다.

WITH HOLD는 질의가 FOR UPDATE나 FOR SHARE를 포함할 때는 지정할 수 없어요.

SCROLL 옵션은 뒤로 가져오기를(fetch backwards) 할 커서를 정의할 때 지정해야 합니다. 이것은 SQL 표준이 요구하는 사항이에요. 다만 이전 버전과의 호환성을 위해 PostgreSQL은, 지원에 필요한 추가 오버헤드가 없을 정도로 커서의 질의 계획이 단순하면 SCROLL 없이도 뒤로 가져오기를 허용해요. 하지만 응용 프로그램 개발자는 SCROLL로 만들지 않은 커서에서 뒤로 가져오기를 쓰는 것에 의존하지 않는 게 좋아요. NO SCROLL을 지정하면 어떤 경우에도 뒤로 가져오기가 금지됩니다.

질의가 FOR UPDATE나 FOR SHARE를 포함할 때도 뒤로 가져오기는 금지돼요. 따라서 이 경우엔 SCROLL을 지정할 수 없습니다.

주의: 스크롤 가능한 커서가 어떤 휘발성(volatile) 함수를 호출하면 예상 밖의 결과가 나올 수 있어요(36.7절 참고). 이전에 가져온 행을 다시 가져올 때 그 함수들이 다시 실행되어 처음과 다른 결과를 낳을 수 있거든요. 휘발성 함수를 포함하는 질의에는 NO SCROLL을 지정하는 게 가장 좋습니다. 그게 실용적이지 않다면, 커서를 SCROLL WITH HOLD로 선언하고 행을 읽기 전에 트랜잭션을 커밋하는 방법이 있어요. 그러면 커서 출력 전체가 임시 저장소에 구체화(materialized)되어, 휘발성 함수가 행마다 정확히 한 번씩만 실행됩니다.

커서의 질의가 FOR UPDATE나 FOR SHARE를 포함하면, 반환되는 행은 일반 SELECT 명령으로 그 옵션들을 쓸 때와 같은 방식으로 처음 가져올 때 잠겨요. 게다가 반환되는 행은 가장 최신 버전이 됩니다.

주의: 커서를 UPDATE ... WHERE CURRENT OF나 DELETE ... WHERE CURRENT OF와 함께 쓸 생각이라면 일반적으로 FOR UPDATE를 쓰는 게 좋아요. FOR UPDATE를 쓰면 다른 세션이 행을 가져온 시점과 갱신하는 시점 사이에 그 행을 바꾸는 것을 막아줘요. FOR UPDATE 없이는, 커서가 만들어진 뒤 행이 바뀌었다면 이후의 WHERE CURRENT OF 명령이 아무 효과도 없을 수 있어요.

FOR UPDATE를 써야 하는 또 다른 이유는, 그것 없이는 커서 질의가 SQL 표준의 "단순히 갱신 가능(simply updatable)" 규칙을 충족하지 못하면 이후의 WHERE CURRENT OF가 실패할 수 있기 때문이에요(특히 커서가 테이블 하나만 참조하고 GROUP BY나 ORDER BY를 쓰지 않아야 합니다). 단순히 갱신 가능하지 않은 커서는 계획 선택 세부 사항에 따라 동작할 수도 있고 아닐 수도 있어서, 최악의 경우 응용 프로그램이 테스트에서는 작동하다가 운영 환경에서 실패할 수 있죠. FOR UPDATE를 지정하면 커서가 갱신 가능함이 보장됩니다.

WHERE CURRENT OF와 함께 FOR UPDATE를 쓰지 말아야 하는 주된 이유는, 커서가 스크롤 가능해야 하거나(또는 동시 갱신으로부터 격리되어야 하면, 즉 계속 예전 데이터를 보여줘야 하면) 그럴 때예요. 그런 요구가 있다면 위에 보인 주의 사항들을 잘 살펴보아야 합니다.

SQL 표준은 커서를 **내장 SQL(embedded SQL)**에서만 사용하도록 규정해요. PostgreSQL 서버는 커서를 위한 OPEN 문을 구현하지 않아요 — 커서는 선언되면 열린 것으로 간주됩니다. 하지만 PostgreSQL용 내장 SQL 전처리기인 ECPG는 DECLARE와 OPEN 문을 포함한 표준 SQL 커서 관례를 지원해요.

열린 커서의 기반이 되는 서버 데이터 구조를 **포털(portal)**이라고 불러요. 포털 이름은 클라이언트 프로토콜에 노출되는데, 클라이언트가 포털 이름을 안다면 열린 포털에서 직접 행을 가져올 수 있어요. DECLARE로 커서를 만들 때 포털 이름은 커서 이름과 같습니다.

모든 가용 커서는 pg_cursors 시스템 뷰를 질의해서 볼 수 있어요.

예시 (Examples)

커서를 선언하려면:

DECLARE liahona CURSOR FOR SELECT * FROM films;

커서 사용의 더 많은 예시는 FETCH를 참고하세요.

호환성 (Compatibility)

SQL 표준은 커서를 내장 SQL과 모듈에서만 허용해요. PostgreSQL은 커서를 대화형으로 사용할 수 있게 해 줍니다.

SQL 표준에 따르면, UPDATE ... WHERE CURRENT OF와 DELETE ... WHERE CURRENT OF 문이 insensitive 커서에 가한 변경은 그 같은 커서에 보여요. PostgreSQL은 이 문들을 다른 모든 데이터 변경 문처럼 취급해서, insensitive 커서에는 보이지 않게 합니다.

이진 커서는 PostgreSQL 확장이에요.

함께 보기 (See Also)

  • CLOSE — 커서 닫기
  • FETCH — 커서에서 행 가져오기
  • MOVE — 커서의 위치만 이동

더 알아보기 (Learn more)

  • FETCH — DECLARE로 만든 커서에서 행을 실제로 꺼내는 명령이에요.
  • DECLARE (PL/pgSQL) — 함수 안에서 커서를 다루는 별도 규칙을 설명해요.
  • pg_cursors — 세션에서 현재 열려 있는 커서를 보여주는 시스템 뷰예요.