142. (Oracle/오라클) 프로시저 (procedure) - 데이터 삽입 insert , 수정 update 및 트랜잭션 commit 수행 실시

---

[개발 환경 설정]

​

개발 툴 : Toad

개발 언어 : Oracle

---

[소스 코드]

​

CREATE OR REPLACE PROCEDURE TEST_INSERT_DEPT_PROCEDURE (
                                                I_IDX    IN VARCHAR, 
                                                I_NAME   IN VARCHAR,
                                                I_PHONE  IN VARCHAR,
                                                I_PLACE  IN VARCHAR,                                                                                               
                                                O_MSG    OUT VARCHAR,
                                                O_CURSOR OUT SYS_REFCURSOR
                                                )
                                               
/***************************************************************

[설 명]

특정 부서 목록 추가 INSERT, UPDATE 수행 실시

---------------------------------------------------------------

[생성]

edit 창에서 F9를 눌러서 쿼리 실행 시 자동 생성된다

---------------------------------------------------------------

[IN 인풋 데이터]
 
I_IDX - 인덱스 순번
I_NAME - 부서 이름
I_PHONE - 부서 연락처
I_PLACE - 부서 위치
     
---------------------------------------------------------------

[OUT 리턴 데이터]
 
O_MSG - 반환 결과 메시지
O_CURSOR - 반환 커서 데이터 (테이블)
      
---------------------------------------------------------------

[프로시져 호출]

EXEC TEST_INSERT_DEPT_PROCEDURE('15', '생산부', '070-123-4567', 'P-15', :AA,:BB);

---------------------------------------------------------------

[프로시져 결과]

Y : MESSAGE              
N : MESSAGE
CURSOR : TABLE DATA

---------------------------------------------------------------
              
[프로시져 삭제]

DROP PROCEDURE TEST_INSERT_DEPT_PROCEDURE;
              
****************************************************************/


--AS --선택
IS --선택


/******************[전역변수 생성 및 초기화]********************/
M_IDX     VARCHAR2 (50);
M_NAME    VARCHAR2 (50);
M_PHONE   VARCHAR2 (50);
M_PLACE   VARCHAR2 (50);
  
M_DEBUG   INTEGER;
M_COUNT   VARCHAR2 (50);


/*****************[메인부분 시작 및 커서생성]*******************/
BEGIN

   /*==========[변수값 대입 실시]==========*/   
   M_IDX := I_IDX; -- 인풋 데이터 삽입
   M_NAME := I_NAME; -- 인풋 데이터 삽입
   M_PHONE := I_PHONE; -- 인풋 데이터 삽입
   M_PLACE := I_PLACE; -- 인풋 데이터 삽입   
   
   
   
   
   /*=========[데이터 널 검증 실시]=========*/
   M_DEBUG := 1; -- 디버깅 단계 지정
   
   IF M_IDX IS NULL OR M_IDX = '' 
      OR M_NAME IS NULL 
      OR M_NAME = '' 
      OR M_PHONE IS NULL 
      OR M_PHONE = '' 
      OR M_PLACE IS NULL 
      OR M_PLACE = '' 
   THEN 
    BEGIN        
        O_MSG := 'N : ' || 'M_DEPT IS NULL' || ' [M_DEBUG := '|| M_DEBUG ||']'; -- 리턴 메시지 삽입
        OPEN O_CURSOR FOR SELECT '' AS "DATA_TABLE" FROM DUAL; -- 리턴 커서 호출
    END;
    RETURN; -- 프로시져 종료
   END IF;
   
   
   
   
   /*==[인덱스 확인 및 insert, update 판단]==*/
   M_DEBUG := 2; -- 디버깅 단계 지정
   
   -- 인풋으로 들어온 IDX 값이 기존에 저장된 값인지 확인 후 M_COUNT 변수에 COUNT 값을 담음
   SELECT COUNT(T_DEPT_IDX) INTO M_COUNT 
       FROM TEST_DEPT
   WHERE TRIM(T_DEPT_IDX) = TRIM(M_IDX); 
      
   DBMS_OUTPUT.PUT_LINE('==================');     
   DBMS_OUTPUT.PUT_LINE('[TEST_INSERT_DEPT_PROCEDURE] : ' || '[M_COUNT] : ' || M_COUNT);
   DBMS_OUTPUT.PUT_LINE('==================');
   
   
   
   
   /*==[기존에 IDX 값이 있으면 UPDATE, 없으면 INSERT]==*/
   M_DEBUG := 3; -- 디버깅 단계 지정
   
   CASE WHEN M_COUNT IS NULL OR M_COUNT = '' OR M_COUNT = 0 -- 널 값일 경우
      THEN
      DBMS_OUTPUT.PUT_LINE('==================');     
      DBMS_OUTPUT.PUT_LINE('[TEST_INSERT_DEPT_PROCEDURE] : ' || '[INSERT]');
      DBMS_OUTPUT.PUT_LINE('==================');
      
      INSERT INTO TEST_DEPT
      (T_DEPT_IDX, T_DEPT_NAME, T_DEPT_PHONE, T_DEPT_PLACE) -- 테이블 컬럼 지정 
      VALUES
      (M_IDX, M_NAME, M_PHONE, M_PLACE);
   ELSE
      DBMS_OUTPUT.PUT_LINE('==================');     
      DBMS_OUTPUT.PUT_LINE('[TEST_INSERT_DEPT_PROCEDURE] : ' || '[UPDATE]');
      DBMS_OUTPUT.PUT_LINE('=================='); 
      
      UPDATE TEST_DEPT
      SET
      T_DEPT_IDX = M_IDX, -- 부서 인덱스
      T_DEPT_NAME = M_NAME, -- 부서 이름
      T_DEPT_PHONE = M_PHONE, -- 부서 연락처
      T_DEPT_PLACE = M_PLACE -- 부서 장소
      WHERE 
         T_DEPT_IDX = M_IDX; -- 조건  
   END CASE;

   
   
   
   /*======[커서 생성 및 반환 쿼리 실행]======*/
   M_DEBUG := 4; -- 디버깅 단계 지정
   
   OPEN O_CURSOR FOR
    SELECT *                   
         FROM TEST_DEPT;
            
            
            
         
   /*===========[프로시저 종료]============*/
   M_DEBUG := 5; -- 디버깅 단계 지정
   
   COMMIT; -- INSERT , UPDATE 최종 완료 후 트랜잭션 커밋 수행 실시
   O_MSG := 'Y : SUCCESS'; -- 리턴 메시지 삽입
   
   --RETURN; -- 프로시저 종료 



/*==========[예외 발생 메시지 리턴]=========*/
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      BEGIN
         O_MSG := 'N : ' || 'EXCEPTION' || ' [NO_DATA_FOUND]';
         OPEN O_CURSOR FOR SELECT '' AS "DATA_TABLE" FROM DUAL;
      END;

   WHEN OTHERS
   THEN
      BEGIN
         O_MSG := 'N : ' || 'EXCEPTION' || ' [M_DEBUG := '|| M_DEBUG ||']';
         OPEN O_CURSOR FOR SELECT '' AS "DATA_TABLE" FROM DUAL;
      END;

      
/*******************[프로시저 종료 완료 부분]********************/
END TEST_INSERT_DEPT_PROCEDURE;
/

---

[결과 출력]

​

​

​

---