| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. PL/pgSQL - SQL수속 언어 | Fast Forward | Next |
쿼리 전체를 한 번에 실행하는 것이 아니라, 커서를 설정해, 쿼리를 캡슐화해, 쿼리의 결과를 한 번에 몇 줄기씩 읽어낼 수가 있습니다. 이것을 실시하는 이유의 하나는, 결과내에 다수의 행이 있는 경우의 메모리의 고갈을 막는 것입니다 (그러나,PL/pgSQL사용자는 통상 이것을 걱정할 필요는 없습니다. FOR루프는 자동적으로 커서를 내부적으로 사용해 메모리의 문제를 막기 때문입니다). 보다 흥미로운 사용 방법으로서 호출해 원이 행을 읽어내는 것을 할 수 있도록, 작성된 커서에의 참조를 돌려주는 방법이 있습니다. 이것에 의해, 함수로부터 대량의 행 집합을 돌려줄 때의 효율이 향상합니다.
PL/pgSQL에서의 커서에의 접근은 모두, 커서 변수를 경유합니다. 커서 변수는, 항상 특수한 refcursor데이터형입니다. 커서 변수를 작성하는 1개의 방법은, 단지 refcursor형의 변수로서 선언하는 것입니다. 다른 방법은, 커서 선언 구문을 사용하는 것입니다. 이하에 그 일반형을 나타냅니다.
name
CURSOR [ (
arguments
) ] FOR
query
;
(Oracle와의 호환성이기 때문에, FOR는IS에 옮겨놓을 수 있습니다.) 만약 SCROLL를 지정하면, 커서는 역방향으로 이동할 수 있습니다. 만약 NO SCROLL를 지정하면, 역방향의 행의 취득은 할 수 없습니다. 어느 쪽이나 지정하지 않을 때, 역방향으로 취득 할 수 있을지는 쿼리에 의존합니다. 만약 arguments 가 있으면, name datatype 를 컴마로 단락지은 리스트로, 주어진 쿼리내의 매개변수값으로서 치환되는 이름을 정의합니다. 그 이름에 실제로 치환되는 값은, 커서를 연 시점보다 후로 지정됩니다.
이하에 예를 나타냅니다.
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
이것들 3개의 변수는 모두 refcursor데이터형을 가집니다만, 최초의 것은 모든 쿼리에 사용할 수 있어 2번째에는 완전한 쿼리가 이미 바운드되고 있습니다(연결시킬 수 있고 있습니다). 또, 마지막 것에는, 매개변수 첨부의 쿼리가 바운드 되고 있습니다 (key는 커서가 열렸을 때에 정수 매개변수값으로 옮겨집니다). curs1변수는, 특정의 쿼리에 바운드되지 않았기 때문에, 언바운드라고 불립니다.
커서를 사용해 행을 꺼내기 전에, 열릴 필요가 있습니다 (이것은 DECLARE CURSOR SQL 명령의 동작과 같습니다). PL/pgSQL에는 3 종류의 OPEN문이 있어, 그 중의 2가지는 언바운드 커서 변수를 사용해, 나머지의 하나는 바운드 커서 변수를 사용합니다.
OPEN unbound_cursor FOR query ;
커서 변수는 열려 실행하도록 지정한 쿼리가 부여됩니다. 이미 연 커서를 열지 못하고, 또, 언바운드 커서로서(즉, 단순한 refcursor변수로서) 선언되어야 합니다. 이 쿼리는 SELECT문인지, 또는 EXPLAIN와 같이 행을 돌려주는 것이 아니면 안됩니다. 이 SELECT쿼리는, 다른 PL/pgSQL의 SELECT문과 같은 방법으로 다루어집니다. PL/pgSQL의 변수명은 옮겨놓을 수 있어 쿼리 계획은 재이용할 수 있도록 캐쉬됩니다. PL/pgSQL변수가 커서를 사용하는 쿼리에 대입되었을 때, 변수는 OPEN시의 값이 되어, 그 후의 변경은 커서의 움직임에 영향을 주지 않습니다. 바운드 커서에 관한 SCROLL 및 NO SCROLL옵션의 의미도 마찬가지됩니다.
이하에 예를 나타냅니다.
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN unbound_cursor FOR EXECUTE query_string ;
커서 변수는 열려 실행하도록 지정한 쿼리가 부여됩니다. 이미 연 커서를 열지 못하고, 또, 언바운드 커서로서(즉, 단순한refcursor변수로서) 선언되어야 합니다. 쿼리는, EXECUTE명령과 같은 방법에 따르는 문자열식으로서 지정됩니다. 통상과 같이, 이것에 의해, 다음번에 실행할 때에 다른 쿼리를 계획할 수 있는 유연성을 얻을 수 있습니다.
이하에 예를 나타냅니다.
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
OPEN
bound_cursor
[ (
argument_values
) ];
선언시에 쿼리가 바운드 된 커서 변수를 열기 위해서 사용되는 OPEN의 형식입니다. 이미 연 커서를 열 수 없습니다. 실제 인자의 평가식 리스트는 커서가 인자를 취한다고 선언되었을 경우에게만 나타납니다. 이러한 값은, 쿼리 중에서 옮겨놓을 수 있습니다. 바운드 커서의 쿼리 계획은 항상 캐쉬 가능으로 간주해집니다. 이 경우, EXECUTE와 등가인 것은 없습니다. SCROLL 및 NO SCROLL를 지정할 수 없는 것에 주의해 주세요. 커서 이동의 사양은 벌써 정해져 있기 때문입니다.
예：
OPEN curs2; OPEN curs3(42);
커서를 열고 나서, 여기서 설명하는 명령문을 사용해 커서를 취급할 수 있습니다.
이러한 조작은, 커서를 개시하기 위해서 연 함수내에서 실시할 필요는 없습니다. 함수로부터 refcursor값을 돌려줘, 호출원으로 그 커서의 조작을 시킬 수도 있습니다 (내부적으로는 refcursor값은, 커서에의 유효한 쿼리를 가지는 포털의 이름을 나타내는 단순한 문자열입니다. 이 이름은, 포털을 부수는 일 없이, 다른 refcursor형의 변수에 대입하는 것으로, 그 밖으로 건네줄 수 있습니다).
모든 포털은, 암묵적으로 트랜잭션의 마지막에 닫힙니다. 따라서, refcursor값은 그 트랜잭션의 마지막까지의 사이만 연 커서에의 참조로서 유효합니다.
FETCH cursor INTO target ;
FETCH는SELECT INTO와 같게, 커서로부터 다음의 행을 추출해, 대상으로 저장합니다. 대상이란, 행 변수, 레코드 변수, 또는 단순한 변수를 칸마로 단락지은 리스트입니다. SELECT INTO의 경우와 같이, 특수한 FOUND변수를 검사하는 것으로, 행을 입수할 수 있었는지 없었는지를 확인할 수가 있습니다.
예：
FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz;
CLOSE cursor ;
CLOSE는 포털의 배후의 커서를 닫습니다. 이것을 사용해 트랜잭션의 마지막보다 전에 자원을 해방할 수가 있어 또, 커서 변수를 해방해, 재차 열 수가 있습니다.
예：
CLOSE curs1;
PL/pgSQL함수에서는, 호출 바탕으로 커서를 돌려줄 수가 있습니다. 이 방법은, 함수로부터 복수행 또는 복수열을 돌려주는 경우, 특히 그 결과 집합이 매우 큰 경우에 사용합니다. 이것을 실시하려면, 함수는, 커서를 열어, 호출 바탕으로 커서명을 돌려줍니다 (혹은, 만약 호출해 원으로 포털명을 알 수 있고 있으면, 단순하게 지정된 포털명을 사용해 커서를 엽니다). 이것에 의해, 호출원래는, 커서로부터 행을 꺼낼 수 있게 됩니다. 커서는, 호출원으로 닫을 수 있습니다. 또는, 트랜잭션이 종료했을 때에 자동적으로 닫혀집니다.
커서용의 포털명은, 프로그래머가 지정하는지, 또는 자동으로 생성됩니다. 포털명을 지정하려면 , 열기 전에, 단지 refcursor변수에 문자열을 대입합니다. refcursor변수의 문자열값은 OPEN에 의해, 배후의 포털명으로서 사용됩니다. 그러나, refcursor변수가 NULL의 경우, OPEN는 자동적으로 기존의 포털과 충돌하지 않는 이름을 생성해, 그것을 refcursor변수에 대입합니다.
Note: 바운드 커서 변수는, 그 이름을 표현하는 문자열값으로 초기화됩니다. 그 때문에, 프로그래머가 커서를 열기 전에 대입에 의해 덧쓰기하지 않는 한, 포털명은 커서 변수와 같게 됩니다. 그러나, 언바운드 커서 변수의 초기값은 기본값으로 NULL입니다. 그 때문에, 덧쓰기되지 않은 경우에 자동적으로 생성된 유일한 이름을 받습니다.
이하의 예는, 호출원으로 커서명을 지정하는 방법을 나타내고 있습니다.
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
이하의 예에서는, 자동 커서명 생성을 사용하고 있습니다.
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
이하의 예는 단일 함수로부터 복수의 커서를 돌려주는 방법을 나타내고 있습니다.
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;
-- 커서를 사용하려면, 트랜잭션 내부일 필요가 있습니다.
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;