■ SYS_REFCURSOR 출력 타입의 입력 매개 변수를 갖는 오라클 저장 프로시저를 호출하는 방법을 보여준다.
▶ 저장 프로시저 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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; |
※ 오라클 11G의 경우 설치하면 SCOTT 계정이 설치되어 있다.
▶ OracleDynamicParameters.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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 } } |
