[Oracle] REF CURSOR 예제

[Oracle] REF CURSOR 예제

-- 프로시져 Select, Multi Select 

< 발취 >

-- https://docs.microsoft.com/ko-kr/dotnet/framework/data/adonet/ref-cursor-examples 

-- https://docs.microsoft.com/ko-kr/dotnet/framework/data/adonet/ref-cursor-parameters-in-an-oracledatareader

-- https://docs.microsoft.com/ko-kr/dotnet/framework/data/adonet/retrieving-data-from-multiple-ref-cursors

-- https://docs.microsoft.com/ko-kr/dotnet/framework/data/adonet/filling-a-dataset-using-one-or-more-ref-cursors

1. Oracle 패키지 및 패키지 본문 만들기

1.1 패키지

CREATE OR REPLACE PACKAGE CURSPKG AS TYPE T_CURSOR IS REF CURSOR; PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, IO_CURSOR IN OUT T_CURSOR); PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOR OUT T_CURSOR); END CURSPKG; /

1.2 본문(프로시져)

CREATE OR REPLACE PACKAGE BODY CURSPKG AS ------------------------------------------------------------------- ----------- 1번째 프로시져 ---------------------------------------- ------------------------------------------------------------------- PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, IO_CURSOR IN OUT T_CURSOR) IS V_CURSOR T_CURSOR; BEGIN IF N_EMPNO <> 0 THEN OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO = N_EMPNO; ELSE OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; END IF; IO_CURSOR := V_CURSOR; END OPEN_ONE_CURSOR; ------------------------------------------------------------------- ----------- 2번째 프로시져 ---------------------------------------- ------------------------------------------------------------------- PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOR OUT T_CURSOR) IS V_CURSOR1 T_CURSOR; V_CURSOR2 T_CURSOR; BEGIN OPEN V_CURSOR1 FOR SELECT * FROM EMP; OPEN V_CURSOR2 FOR SELECT * FROM DEPT; EMPCURSOR := V_CURSOR1; DEPTCURSOR := V_CURSOR2; END OPEN_TWO_CURSORS; END CURSPKG; /

2. OracleDataReader의 REF CURSOR 매개 변수

Private Sub Button1_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim connString As New String(_ "Data Source=Oracle9i;User ID=scott;Password=tiger;") Using conn As New OracleConnection(connString) Dim cmd As New OracleCommand() Dim rdr As OracleDataReader conn.Open() cmd.Connection = conn cmd.CommandText = "CURSPKG.OPEN_ONE_CURSOR" cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New OracleParameter( "N_EMPNO", OracleType.Number)).Value = 7369 cmd.Parameters.Add(New OracleParameter( "IO_CURSOR", OracleType.Cursor)).Direction = ParameterDirection.Output rdr = cmd.ExecuteReader() While (rdr.Read()) REM do something with the values End While rdr.Close() End Using End Sub

3. OracleDataReader를 사용하여 여러 Multiple REF CURSOR에서 데이터 검색

Private Sub Button1_Click( _ ByVal sender As Object, ByVal e As System.EventArgs) _ Handles Button1.Click Dim connString As New String( _ "Data Source=Oracle9i;User ID=scott;Password=tiger;") Using conn As New OracleConnection(connString) Dim cmd As New OracleCommand() Dim rdr As OracleDataReader conn.Open() cmd.Connection = conn cmd.CommandText = "CURSPKG.OPEN_TWO_CURSORS" cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New OracleParameter( _ "EMPCURSOR", OracleType.Cursor)).Direction = _ ParameterDirection.Output cmd.Parameters.Add(New OracleParameter(_ "DEPTCURSOR", OracleType.Cursor)).Direction = _ ParameterDirection.Output rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection) While (rdr.Read()) REM do something with the values from the EMP table End While rdr.NextResult() While (rdr.Read()) REM do something with the values from the DEPT table End While rdr.Close() End Using End Sub

4. 하나 이상의 REF CURSOR를 사용하여 데이터 집합 필터링

Private Sub Button1_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim connString As New String(_ "Data Source=Oracle9i;User ID=scott;Password=tiger;") Dim ds As New DataSet() Using conn As New OracleConnection(connString) Dim cmd As New OracleCommand() cmd.Connection = conn cmd.CommandText = "CURSPKG.OPEN_TWO_CURSORS" cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New OracleParameter( _ "EMPCURSOR", OracleType.Cursor)).Direction = _ ParameterDirection.Output cmd.Parameters.Add(New OracleParameter( _ "DEPTCURSOR", OracleType.Cursor)).Direction = _ ParameterDirection.Output Dim da As New OracleDataAdapter(cmd) da.TableMappings.Add("Table", "Emp") da.TableMappings.Add("Table1", "Dept") da.Fill(ds) ds.Relations.Add("EmpDept", ds.Tables("Dept").Columns("Deptno"), _ ds.Tables("Emp").Columns("Deptno"), False) DataGrid1.DataSource = ds.Tables("Dept") End Using