Oracle 프로시저 활용하기 ①

양현지·2024년 2월 26일

C--

목록 보기

1/1

1. 개요

c# .NET을 활용해 데스크탑 어플리케이션을 구축하고자 한다.
이때 Oracle 데이터베이스와 연동하고자 하며, 프로시저 호출을 수행하고자 한다.

1) 프로시저란?

일련의 쿼리를 하나의 함수처럼 실행하는 쿼리의 집합. 여러 쿼리를 한 번에 완전 무결한 실행을 돕는다.

프로시저와 함수의 차이?
: 함수란 프로시저의 각 프로세스(쿼리)를 수행하기 위해 필요한 기능을 정의한 것이다.

프로시저와 달리 함수는
i) 리턴 값을 반드시 가지며, 그 값은 유잃다ㅏ.
ii) 특정 기능(계산)을 수행한다. 반면 프로시저는 특정 작업(더 넓은 범위)을 수행

2) 프로시저의 활용

프로시저를 사용하는 주요 목적은 다음과 같다.

① 재사용성

프로그램의 여러 부분에서 호출될 수 있으며, 반복 수행 시에도 재사용 될 수 있다.

② 모듈화

큰 프로그램을 작은 모듈 (가령 사용자 관리 ERP 시스템에서 로그인 프로시저, 로그아웃 프로시저, 사용자 추가 프로시저로)로 나누어 관리 및 유지보수에 용이하도록 한다.

③ 유연성

함수와 달리 다수의 리턴값을 가질 수 있으며, 입력 매개변수 또한 유연하게 받을 수 있다.

3) 프로시저 호출

visual studio 에서 .net wpf 프로젝트를 설정한 뒤 아래의 과정을 거쳐 oracle dbms와 연동 및 프로시저 호출을 실행할 수 있다.

* 이때 사용 할 프로시저는 미리 dbeaver 등을 통해 해당 oracle dbms에 등록하도록 한다.

① 연결 생성

: oracle 데이터베이스와 연결을 생성한다.

public const string OracleConnectionString = "User Id=;'유저이름'Password='유저비번';Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='host명')(PORT='port번호')))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME='스키마명')));";


using (OracleConnection connection = new OracleConnection(OracleConnectionString))
{
    connection.Open();

    using (OracleCommand command = new OracleCommand("HMX_KCTC.P_LOG_IN", connection))
}

※ 문자열 버퍼 오류

위 오류의 경우 Oracle Function 혹은 Procedure 내 선언된 변수의 크기보다 더 큰 값을 받아오는 경우 발생하는 오류이다. 이러한 경우 대개 4000byte(오라클 지원 최대 VARCHAR bytes) 로 설정해 줌으로써 해결할 수 있다.

② 매개변수 설정

: 프로시저의 입출력 매개변수를 설정하도록 한다.

아래 예제의 프로시저는 사용자 정보 삭제 프로시저로써 json형태의 입력 매개변수를 가진다.

 using (OracleCommand command = new OracleCommand("HMX_KCTC.P_DELETE_USER_HJ", connection))
 {
     command.CommandType = CommandType.StoredProcedure;

     // 입력 매개변수 (JSON)
     string jsonInput = JsonConvert.SerializeObject(new { ID_T_USER, USER_ID });

     command.Parameters.Add("PV_JSON_I", OracleDbType.Varchar2).Value = jsonInput;

     // 출력 매개변수 설정
     command.Parameters.Add("PV_JSON_O", OracleDbType.Varchar2, 4000, "", ParameterDirection.Output);
     command.Parameters.Add("PV_RETCOD_O", OracleDbType.Int32, ParameterDirection.Output);
     command.Parameters.Add("PV_RETMSG_O", OracleDbType.Varchar2, 4000, "", ParameterDirection.Output);

③ 프로시저 실행

: 입출력 매개변수를 추가한 후 프로시저를 호출 및 실행하도록 한다.

     command.ExecuteNonQuery();

     int retCode = command.Parameters["PV_RETCOD_O"].Value != null ? ((OracleDecimal)command.Parameters["PV_RETCOD_O"].Value).ToInt32() : -1;
     string retMessage = command.Parameters["PV_RETMSG_O"].Value != null ? command.Parameters["PV_RETMSG_O"].Value.ToString() : "No message";

④ 프로시저 리턴값 확인

: 프로시저의 리턴값을 확인하여 실행 여부를 검사하도록한다.


     if (retCode == 0)
     {
         MessageBox.Show($"{retMessage}", "알람");
         Application.Current.Dispatcher.Invoke(() =>
         {
             UserList.Remove((UserData)selectedRow.Item);
         });
     }
     else
         MessageBox.Show($"사용자 정보 삭제 실패 : {retMessage} ; {retCode}", "경고");

 }

※ Json Parameter
: json 형태로 파라미터를 추가하는 경우 Newtonsoft.Json 패키치를 사용하도록 한다.

프로시저를 사용해 코드의 가독성을 높이고, 안전성을 보장할 수 있다. MsSQL 또한 Oracle과 마찮가지로 c# wpf 및 winform 어플리케이션에서 데이터베이스에 접속하고 프로시저 호출을 구현할 수 있다.

양현지