결과 세트 반환

커서 또는 임시 테이블을 사용하여 결과 세트를 반환할 수 있습니다.

커서 반환

커서를 반환하려면 refcursor 데이터 형식으로 정의된 INOUT 인수를 사용하여 프로시저를 생성합니다. 프로시저를 호출할 때 커서에 이름을 지정합니다. 그러면 커서에서 이름을 기준으로 결과를 가져올 수 있습니다.

다음 예제에서는 refcursor 데이터 형식을 사용하여 rs_out라는 INOUT 인수로 get_result_set라는 프로시저를 생성합니다. 이 프로시저는 SELECT 문을 사용하여 커서를 엽니다.

CREATE OR REPLACE PROCEDURE get_result_set (param IN integer, rs_out INOUT refcursor)
AS $$
BEGIN
  OPEN rs_out FOR SELECT * FROM fact_tbl where id >= param;
END;
$$ LANGUAGE plpgsql;

다음 CALL 명령은 이름이 mycursor인 커서를 엽니다. 트랜잭션 내에서만 커서를 사용합니다.

BEGIN;
CALL get_result_set(1, 'mycursor');   

커서가 열리면 다음 예제와 같이 커서에서 가져올 수 있습니다.

FETCH ALL FROM mycursor;

    id | secondary_id | name
-------+--------------+---------
     1 |            1 | Joe
     1 |            2 | Ed
     2 |            1 | Mary
     1 |            3 | Mike
(4 rows)   

결국 트랜잭션이 커밋되거나 롤백됩니다.

COMMIT;   

저장 프로시저에 의해 반환된 커서에는 DECLARE CURSOR에서 설명한 것과 동일한 제약 및 성능 고려 사항이 적용됩니다. 자세한 내용은 커서 제약 조건 섹션을 참조하세요.

다음 예제에서는 JDBC의 refcursor 데이터 형식을 사용하여 get_result_set 저장 프로시저의 호출을 보여 줍니다. 리터럴 'mycursor'(커서 이름)는 prepareStatement로 전달됩니다. 그런 다음 ResultSet에서 결과를 가져옵니다.

static void refcursor_example(Connection conn) throws SQLException {
    conn.setAutoCommit(false);
    PreparedStatement proc = conn.prepareStatement("CALL get_result_set(1, 'mycursor')");
    proc.execute();
    ResultSet rs = statement.executeQuery("fetch all from mycursor");
    while (rs.next()) {
      int n = rs.getInt(1);
      System.out.println("n " + n);
    }

임시 테이블 사용

결과를 반환하기 위해 핸들을 결과 행이 포함된 임시 테이블로 반환할 수 있습니다. 클라이언트는 이름을 파라미터로 저장 프로시저에 제공할 수 있습니다. 저장 프로시저 내부에서 동적 SQL을 사용하여 임시 테이블에서 작업할 수 있습니다. 다음은 그 한 예입니다.

CREATE PROCEDURE get_result_set(param IN integer, tmp_name INOUT varchar(256)) as $$
DECLARE
  row record;
BEGIN
  EXECUTE 'drop table if exists ' || tmp_name;
  EXECUTE 'create temp table ' || tmp_name || ' as select * from fact_tbl where id <= ' || param;
END;
$$ LANGUAGE plpgsql;

CALL get_result_set(2, 'myresult');
 tmp_name
-----------
 myresult
(1 row)

SELECT * from myresult;
 id | secondary_id | name
----+--------------+------
  1 |            1 | Joe
  2 |            1 | Mary
  1 |            2 | Ed
  1 |            3 | Mike
(4 rows)