[Oracle] REF CURSOR 예제

프로그래밍/Oracle

[Oracle] REF CURSOR 예제

도깨비방망이 2018. 4. 2. 15:10

[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 패키지

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 매개 변수

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

저작자표시 비영리 변경금지