[C#/COMMON/DAPPER] SYS_REFCURSOR 출력 타입의 입력 매개 변수를 갖는 오라클 저장 프로시저 호출하기

■ SYS_REFCURSOR 출력 타입의 입력 매개 변수를 갖는 오라클 저장 프로시저를 호출하는 방법을 보여준다.

▶ 저장 프로시저 (SQL)


CREATE OR REPLACE PROCEDURE SCOTT.GETMEMBERLIST
(
    I_NAME   IN  VARCHAR2,
    O_CURSOR OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN O_CURSOR FOR
        SELECT *
        FROM   SCOTT.EMP
        WHERE  ENAME LIKE I_NAME || '%';
END;

CREATE OR REPLACE PROCEDURE SCOTT.GETMEMBERLIST

(

I_NAME IN VARCHAR2,

O_CURSOR OUT SYS_REFCURSOR

)

BEGIN

OPEN O_CURSOR FOR

SELECT *

FROM SCOTT.EMP

WHERE ENAME LIKE I_NAME || '%';

END;

※ 오라클 11G의 경우 설치하면 SCOTT 계정이 설치되어 있다.

▶ OracleDynamicParameters.cs


using System.Collections.Generic;
using System.Data;

using Dapper;

using Oracle.ManagedDataAccess.Client;

namespace TestProject
{
    /// <summary>
    /// 오라클 동적 매개 변수 목록
    /// </summary>
    public class OracleDynamicParameters : SqlMapper.IDynamicParameters
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 동적 매개 변수 목록
        /// </summary>
        private readonly DynamicParameters dynamicParameters = new DynamicParameters();

        /// <summary>
        /// 오라클 매개 변수 리스트
        /// </summary>
        private readonly List<OracleParameter> oracleParameterList = new List<OracleParameter>();

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 추가하기 - Add(name, value, dbType, direction, size)

        /// <summary>
        /// 추가하기
        /// </summary>
        /// <param name="name">명칭</param>
        /// <param name="value">값</param>
        /// <param name="dbType">DB 타입</param>
        /// <param name="direction">방향</param>
        /// <param name="size">크기</param>
        public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)
        {
            this.dynamicParameters.Add(name, value, dbType, direction, size);
        }

        #endregion
        #region 추가하기 - Add(name, oracleDbType, direction)

        /// <summary>
        /// 추가하기
        /// </summary>
        /// <param name="name">명칭</param>
        /// <param name="oracleDbType">오라클 DB 타입</param>
        /// <param name="direction">방향</param>
        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
        {
            OracleParameter parameter = new OracleParameter(name, oracleDbType, direction);

            this.oracleParameterList.Add(parameter);
        }

        #endregion

        #region (SqlMapper.IDynamicParameters) 매개 변수 목록 추가하기 - AddParameters(command, identity)

        /// <summary>
        /// 매개 변수 목록 추가하기
        /// </summary>
        /// <param name="command">명령</param>
        /// <param name="identity">식별자</param>
        public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            ((SqlMapper.IDynamicParameters)this.dynamicParameters).AddParameters(command, identity);

            OracleCommand oracleCommand = command as OracleCommand;

            if(oracleCommand != null)
            {
                oracleCommand.Parameters.AddRange(this.oracleParameterList.ToArray());
            }
        }

        #endregion
    }
}

using System.Collections.Generic;

using System.Data;

using Dapper;

using Oracle.ManagedDataAccess.Client;

namespace TestProject

{

/// <summary>

/// 오라클 동적 매개 변수 목록

/// </summary>

public class OracleDynamicParameters : SqlMapper.IDynamicParameters

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Field

////////////////////////////////////////////////////////////////////////////////////////// Private

#region Field

/// <summary>

/// 동적 매개 변수 목록

/// </summary>

private readonly DynamicParameters dynamicParameters = new DynamicParameters();

/// <summary>

/// 오라클 매개 변수 리스트

/// </summary>

private readonly List<OracleParameter> oracleParameterList = new List<OracleParameter>();

#endregion

//////////////////////////////////////////////////////////////////////////////////////////////////// Constructor

////////////////////////////////////////////////////////////////////////////////////////// Public

#region 추가하기 - Add(name, value, dbType, direction, size)

/// <summary>

/// 추가하기

/// </summary>

/// <param name="name">명칭</param>

/// <param name="value">값</param>

/// <param name="dbType">DB 타입</param>

/// <param name="direction">방향</param>

/// <param name="size">크기</param>

public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)

{

this.dynamicParameters.Add(name, value, dbType, direction, size);

}

#endregion

#region 추가하기 - Add(name, oracleDbType, direction)

/// <summary>

/// 추가하기

/// </summary>

/// <param name="name">명칭</param>

/// <param name="oracleDbType">오라클 DB 타입</param>

/// <param name="direction">방향</param>

public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)

{

OracleParameter parameter = new OracleParameter(name, oracleDbType, direction);

this.oracleParameterList.Add(parameter);

}

#endregion

#region (SqlMapper.IDynamicParameters) 매개 변수 목록 추가하기 - AddParameters(command, identity)

/// <summary>

/// 매개 변수 목록 추가하기

/// </summary>

/// <param name="command">명령</param>

/// <param name="identity">식별자</param>

public void AddParameters(IDbCommand command, SqlMapper.Identity identity)

{

((SqlMapper.IDynamicParameters)this.dynamicParameters).AddParameters(command, identity);

OracleCommand oracleCommand = command as OracleCommand;

if(oracleCommand != null)

{

oracleCommand.Parameters.AddRange(this.oracleParameterList.ToArray());

}

#endregion

}

▶ Program.cs


using System;
using System.Data;
using System.Linq;

using Oracle.ManagedDataAccess.Client;

using Dapper;

namespace TestProject
{
    /// <summary>
    /// 프로그램
    /// </summary>
    class Program
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region 프로그램 시작하기 - Main()

        /// <summary>
        /// 프로그램 시작하기
        /// </summary>
        private static void Main()
        {
            string connectionString = "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orcl)));User ID=SYSTEM;Password=P@ssw0rd";

            using(OracleConnection connection = new OracleConnection(connectionString))
            {
                OracleDynamicParameters parameters = new OracleDynamicParameters();

                parameters.Add("I_NAME"  , "S");
                parameters.Add("O_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

                var resultList = connection.Query<dynamic>
                (
                    "SCOTT.GETMEMBERLIST",
                    param : parameters,
                    commandType : CommandType.StoredProcedure
                )
                .ToList();

                foreach(var item in resultList)
                {
                    Console.WriteLine(item.ENAME);
                }
            }
        }

        #endregion
    }
}

using System;

using System.Data;

using System.Linq;

using Oracle.ManagedDataAccess.Client;

using Dapper;

namespace TestProject

{

/// <summary>

/// 프로그램

/// </summary>

class Program

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Static

//////////////////////////////////////////////////////////////////////////////// Private

#region 프로그램 시작하기 - Main()

/// <summary>

/// 프로그램 시작하기

/// </summary>

private static void Main()

{

string connectionString = "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =

(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orcl)));User ID=SYSTEM;Password=P@ssw0rd";

using(OracleConnection connection = new OracleConnection(connectionString))

{

OracleDynamicParameters parameters = new OracleDynamicParameters();

parameters.Add("I_NAME" , "S");

parameters.Add("O_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

var resultList = connection.Query<dynamic>

(

"SCOTT.GETMEMBERLIST",

param : parameters,

commandType : CommandType.StoredProcedure

)

.ToList();

foreach(var item in resultList)

{

Console.WriteLine(item.ENAME);

}

#endregion

}

TestProject.zip