[IT실무고급교육★탑크리에듀]참조커서(REF CURSOR)

참조커서(REF CURSOR) 

 일반 커서는 정적이며 커서가 한번 SQL 영역을 가리키면 가리키는 곳을 변경 못한다. 하지만  참조커서(REF CURSOR)는 가능하다. 

Declare 
  Type refcur is ref cursor; 
  cursor c is select * from dual; 
  myCursor refCur; 
begin 
  //myCur는 ref cursor 
if (to_char(sysdate,’dd’) = 30) then 
    open myCur for select * from emp; 
elsif ((to_char(sysdate,’dd’) = 20) then 
    open myCur for select * from dept; 
end if; 
open c;  //일반커서 
end; 
/ 

 일반 커서가 가리키는 데이터는 함수나 프러시저를 호출한 곳으로 보낼 수 없지만 참조커서(REF CURSOR)가 가리키는 데이터는 가능하다. 

-- 레코드를 리턴할 수 있는 REF CURSOR 타입을 함수의 헤더정의에서 리턴형으로 사용하기 때문에 미리 Package의 전역변수로 Ref Cursor형의 CURTYPE이라는 변수를 만든다. 
SQL> CREATE or REPLACE PACKAGE TYPES 
    AS 
    Type CURTYPE  IS  Ref Cursor; 
    end; 
    / 

패키지가 생성되었습니다. 

-- 커서를 리턴할 수 있는 함수를 만든다. 
SQL> CREATE OR REPLACE FUNCTION emptest(v_deptno in number) RETURN TYPES.CURTYPE 
    AS 
      test_cursor TYPES.CURTYPE; 
      sql_string Varchar2(500); 
  BEGIN 
      -- sql_string := 'Select empno, ename, sal from scott.Emp where deptno = ' || v_deptno; 
  -- Open test_cursor FOR sql_string; 

      sql_string := 'Select empno, ename, sal from scott.Emp where deptno = :deptno' ; 
      Open test_cursor FOR sql_string USING v_deptno; 

      RETURN test_cursor; 

      CLOSE test_Cursor; 
  END; 
  / 

함수가 생성되었습니다. 

-- SQL*Plus 에서 확인 
SQL>variable a refcursor 
SQL>exec :a := emptest(10) 
SQL>print a 

EMPNO  ENAME            SAL 
---------- ----------      ---------- 
      7782    CLARK          2450 
      7839    KING            5000 
      7934    MILLER          1300 

-- JAVA/JSP등에서 확인 

Class.forName(driver_name); //jdbc 드라이버연결 
                conn =  DriverManager.getConnection(url,user,pwd); //Connection인수 입력 
              
                String proc_call = "{? = call emptest(?)}"; 
            
                // 오라클 함수 또는 프러시저를 호출하기 위해 callable statement를 이용 
                cstmt = conn.prepareCall(proc_call); 
  
                // key here is to register the output parameter 
                // of type cursor, execute, then cast it as a ResultSet. 
                -- emptiest 함수의 output 파라미터 타입을 CURSOR로 한다. 
                -- 첫번째  ?가 출력파라미터, 두번째 파리미터는 입력파라미터 
                cstmt.registerOutParameter(1, OracleTypes.CURSOR); 
                cstmt.setInt(2, 10);  //10번 부서의 데이터만 얻기 위해 
                cstmt.executeQuery(); 

                rs = (ResultSet)cstmt.getObject(1); 
                while(rs.next()) { 
                        out.println(rs.getString("ename") + "<br>"); 
                        …… 
                }