<%@ page language="java" contentType="text/html; charset=euc-kr" %>
<%@ page import="java.sql.*,oracle.jdbc.driver.* " %>
<%  
/*
※ 미리 실행 되어야할 코드.

CREATE or REPLACE PACKAGE TYPES
AS
 Type cursorType IS Ref Cursor;
end;
/

CREATE OR REPLACE FUNCTION emptest(v_deptno in number) RETURN TYPES.CURSORTYPE
AS
   test_cursor TYPES.CURSORTYPE;
   sql_string Varchar2(500);
BEGIN
   sql_string := 'Select * from scott.Emp where deptno = ' || v_deptno;
   Open test_cursor FOR sql_string;
   RETURN test_cursor;
   CLOSE test_Cursor;
END;
/
*/     
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        String driver_name = "oracle.jdbc.driver.OracleDriver"; //오라클 드라이버
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";   //호스트
        String user = "SCOTT"; //계정
        String pwd = "tiger";  //비밀번호
        String query = "";

        try {
                Class.forName(driver_name); //jdbc 드라이버연결
                conn =  DriverManager.getConnection(url,user,pwd); //Connection인수 입력
                
                String proc_call = "{? = call emptest(?)}";
                
                // create 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. 
                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>");
                }
        } 
        catch(Throwable e) 
        { 
                out.println(e); 
        } 
        finally {
                try {
                        rs.close();
                        cstmt.close();
                        conn.close();
                }
                catch(Exception e) {}
        }
%>