오늘 불현듯...
User Define Cursor 에 대한 부분이 궁금해 졌다...
자바에서 오라클 패키지 호출하여 커서값을 반환하여 자유 스럽게 사용했는데
그 커서 값을 오라클에서 받아서 해볼까 하는...
사실 cursor is 구문이라던지 직접 사용했지
그동안 cursor 반환은 무관심 했었다..
방법은...
즉, 커서 out 값에 l_cursor에 받아와서
3개의 select 한 값을 return 하는데... 각 값들의 속성을 l_ename, l_empno, l_deptno 로 fetch 하여 받아가지고 옴...
참고용
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE REF_CUR IS REF CURSOR;
l_cursor REF_CUR;
l_ename emp.ename%TYPE;
l_empno emp.empno%TYPE;
l_deptno emp.deptno%TYPE;
BEGIN
get_emp_rs (p_deptno => 30,
p_recordset => l_cursor);
LOOP
FETCH l_cursor
INTO l_ename, l_empno, l_deptno;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
END LOOP;
CLOSE l_cursor;
END;
/
출처 :
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
Using Ref Cursors To Return Recordsets
Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types. The example below uses a ref cursor to return a subset of the records in the EMP table.
The following procedure opens a query using a SYS_REFCURSOR output parameter. Notice the cursor is not closed in the procedure. It is up to the calling code to manage the cursor once it has been opened.
CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/
The resulting cursor can be referenced from PL/SQL as follows.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_cursor SYS_REFCURSOR;
l_ename emp.ename%TYPE;
l_empno emp.empno%TYPE;
l_deptno emp.deptno%TYPE;
BEGIN
get_emp_rs (p_deptno => 30,
p_recordset => l_cursor);
LOOP
FETCH l_cursor
INTO l_ename, l_empno, l_deptno;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
END LOOP;
CLOSE l_cursor;
END;
/
The cursor can be used as an ADO Recordset.
Dim conn, cmd, rs
Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"
Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "get_emp_rs"
cmd.CommandType = 4 'adCmdStoredProc
Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30
Set rs = cmd.Execute
Do Until rs.BOF Or rs.EOF
-- Do something
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = nothing
Set param1 = nothing
Set cmd = nothing
Set conn = nothing
The cursor can also be referenced as a Java ResultSet.
import java.sql.*;
import oracle.jdbc.*;
public class TestResultSet {
public TestResultSet() {
try {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
CallableStatement stmt = conn.prepareCall("BEGIN get_emp_rs(?, ?); END;");
stmt.setInt(1, 30); // DEPTNO
stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
stmt.execute();
ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
while (rs.next()) {
System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno"));
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
}
}
public static void main (String[] args) {
new TestResultSet();
}
}
If you are using a version of Oracle before 9i, then create the following package and replace any references to SYS_REFCURSOR with TYPES.cursor_type.CREATE OR REPLACE PACKAGE types AS
TYPE cursor_type IS REF CURSOR;
END Types;
/
Hope this helps. Regards Tim...