투케이2K

165. (Oracle/오라클) 프로시저 (PROCEDURE) - 특정 부서 데이터 조회 , 삽입 , 수정 , 삭제 수행 본문

OracleDB

165. (Oracle/오라클) 프로시저 (PROCEDURE) - 특정 부서 데이터 조회 , 삽입 , 수정 , 삭제 수행

투케이2K 2022. 5. 10. 15:13

[개발 환경 설정]

​

개발 툴 : Toad

개발 언어 : Oracle

 

[소스 코드]

/************************************************************/
CREATE OR REPLACE PROCEDURE PRO_TEST_DEPT 
(
  I_GUBUN         IN VARCHAR, -- [필수 : 1 = 특정 조회 / 2 = 삽입 / 3 = 수정 / 4 = 삭제 / 5 = 전체 조회]
  I_IDX           IN VARCHAR, -- [필수 : 인덱스 값 / 전체 조회 일 경우 0 값]
  I_DEPT_NAME     IN VARCHAR, -- [부서 이름]
  I_DEPT_CHANNER  IN VARCHAR, -- [채널]  
                                                                                             
  O_MSG           OUT VARCHAR, -- [리턴 : 메시지]
  O_CURSOR        OUT SYS_REFCURSOR -- [리턴 : 테이블]
)
                                               
/*************************************************************
[설 명] - 특정 부서 조회 , 수정 , 삽입 , 삭제 수행
-------------------------------------------------------------
[리 턴] - Y : MESSAGE / N : MESSAGE
-------------------------------------------------------------

[프로시져 호출]


-- [특정 부서 조회 실시]
EXEC PRO_TEST_DEPT('1', '1', '테스트부서', '1234567', :AA, :BB);


-- [특정 부서 삽입 실시]
EXEC PRO_TEST_DEPT('2', '1', '테스트부서', '1234567', :AA, :BB);


-- [특정 부서 수정 실시]
EXEC PRO_TEST_DEPT('3', '1', '테스트부서', '12345678', :AA, :BB);


-- [특정 부서 삭제 실시]
EXEC PRO_TEST_DEPT('4', '1', '테스트부서', '1234567', :AA, :BB);


-- [전체 부서 조회 실시 : 인덱스 값 하드 코딩 [0]]
EXEC PRO_TEST_DEPT('5', '0', '', '', :AA, :BB);

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

DROP PROCEDURE PRO_TEST_DEPT;
              
**************************************************************/


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


/******************* [전역변수 생성 및 초기화] *******************/

M_DEBUG  VARCHAR2 (100); -- [디버깅 단계]

M_DATE   VARCHAR2 (100); -- [현재 날짜 및 시간]

M_COUNT  INTEGER; -- [매핑되는 조건 카운트]


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


   /*=======================================================*/
   
   
   -- [디버깅 단계 지정]
   M_DEBUG := '1';
   
   
   -- [현재 날짜 및 시간 데이터 지정]
   M_DATE := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'); 
   
   /*
   DBMS_OUTPUT.PUT_LINE(''); 
   DBMS_OUTPUT.PUT_LINE('=============================');     
   DBMS_OUTPUT.PUT_LINE('[PRO_TEST_DEPT] : [' || M_DEBUG || '] : ' || M_COUNT);
   DBMS_OUTPUT.PUT_LINE('M_DATE : ' || M_DATE );
   DBMS_OUTPUT.PUT_LINE('=============================');
   DBMS_OUTPUT.PUT_LINE(''); 
   --*/
   
   /*=======================================================*/
   
   
   -- [디버깅 단계 지정]
   M_DEBUG := '2'; 
   
   
   -- [필수 인풋 데이터 널 체크 실시]
   IF I_GUBUN IS NULL OR I_GUBUN = ''
      OR I_IDX IS NULL OR I_IDX = '' 
   THEN 
      BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || '인풋 데이터 값을 다시 확인해주세요. [I_GUBUN] [I_IDX]' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
          SELECT '' AS "DATA_TABLE" 
            FROM DUAL; 
      END;
    
      -- [프로시져 종료]
      RETURN; 
   END IF;
   
   
   /*=======================================================*/
   
   
   -- [디버깅 단계 지정]
   M_DEBUG := '3'; 
   
   
   /*
   DBMS_OUTPUT.PUT_LINE(''); 
   DBMS_OUTPUT.PUT_LINE('=============================');     
   DBMS_OUTPUT.PUT_LINE('[PRO_TEST_DEPT] : [' || M_DEBUG || '] : ' || M_COUNT);
   DBMS_OUTPUT.PUT_LINE('I_GUBUN : ' || TRIM(I_GUBUN) );
   DBMS_OUTPUT.PUT_LINE('=============================');
   DBMS_OUTPUT.PUT_LINE(''); 
   --*/
   
   
   -- [인풋으로 들어온 구분 값 확인 실시]
   IF TRIM(I_GUBUN) = '1' THEN -- [특정 조회]
   
      -- [디버깅 단계 지정]
      M_DEBUG := '3-1';
      
      
      -- [인풋값으로 들어온 조건으로 검색 되는 데이터가 있는지 확인]
      SELECT COUNT(*) INTO M_COUNT
          FROM TEST_DEPT
      WHERE TRIM(DEPT_IDX) = TRIM(I_IDX)
      ;
      
      
      -- [카운트 값 확인 실시]
      IF M_COUNT > 0 THEN -- [검색된 데이터가 있는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-1-1';
         
         BEGIN
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT DEPT_IDX AS 순서 ,
                   DEPT_NAME AS 부서이름 ,
                   DEPT_CHANNER AS 채널 ,
                   DEPT_UPDT AS 수정일자
              FROM TEST_DEPT
            WHERE TRIM(DEPT_IDX) = TRIM(I_IDX);
               
          -- [리턴 메시지 삽입]        
          O_MSG := 'Y : ' || '특정 데이터 조회를 완료했습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']';      
         END;
         
         -- [프로시져 종료]
         RETURN;
         
      ELSE -- [검색된 데이터가 없는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-1-2';
         
         BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || '검색 조건을 만족하는 데이터가 없습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT '' AS "DATA_TABLE" 
               FROM DUAL; 
         END;
    
         -- [프로시져 종료]
         RETURN;
         
      END IF;
      
   /*=================================*/
      
   ELSIF TRIM(I_GUBUN) = '2' THEN -- [삽입]
   
      -- [디버깅 단계 지정]
      M_DEBUG := '3-2'; 
      
      
      -- [인풋값으로 들어온 조건으로 검색 되는 데이터가 있는지 확인]
      SELECT COUNT(*) INTO M_COUNT
          FROM TEST_DEPT
      WHERE TRIM(DEPT_IDX) = TRIM(I_IDX)
      ;
      
      
      -- [카운트 값 확인 실시]
      IF M_COUNT > 0 THEN -- [검색된 데이터가 있는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-2-1';
         
         BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || '이미 등록된 데이터가 있습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT '' AS "DATA_TABLE" 
               FROM DUAL; 
         END;
    
         -- [프로시져 종료]
         RETURN;
         
      ELSE -- [검색된 데이터가 없는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-2-2';
         
         BEGIN
          -- [데이터 삽입 실시]
          INSERT INTO TEST_DEPT (DEPT_IDX, DEPT_NAME, DEPT_CHANNER, DEPT_UPDT) 
             VALUES (I_IDX, I_DEPT_NAME, I_DEPT_CHANNER, M_DATE);
         END;
         
         -- [커밋 수행]
         COMMIT;
         
         BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'Y : ' || '데이터 삽입을 완료했습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT '' AS "DATA_TABLE" 
               FROM DUAL; 
         END;
         
         -- [프로시져 종료]
         RETURN;
      END IF;
      
   /*=================================*/
      
   ELSIF TRIM(I_GUBUN) = '3' THEN -- [수정]
   
      -- [디버깅 단계 지정]
      M_DEBUG := '3-3'; 
      
      
      -- [인풋값으로 들어온 조건으로 검색 되는 데이터가 있는지 확인]
      SELECT COUNT(*) INTO M_COUNT
          FROM TEST_DEPT
      WHERE TRIM(DEPT_IDX) = TRIM(I_IDX)
      ;
      
      
      -- [카운트 값 확인 실시]
      IF M_COUNT > 0 THEN -- [검색된 데이터가 있는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-3-1';
         
         BEGIN
          -- [데이터 수정 실시]
          UPDATE TEST_DEPT 
              SET DEPT_NAME = I_DEPT_NAME , DEPT_CHANNER = I_DEPT_CHANNER , DEPT_UPDT = M_DATE
          WHERE TRIM(DEPT_IDX) = TRIM(I_IDX);
         END;
         
         -- [커밋 수행]
         COMMIT;
         
         BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'Y : ' || '데이터 수정을 완료했습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT '' AS "DATA_TABLE" 
               FROM DUAL; 
         END;
         
         -- [프로시져 종료]
         RETURN;
         
      ELSE -- [검색된 데이터가 없는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-3-2';
         
         BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || '수정 조건을 만족하는 데이터가 없습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT '' AS "DATA_TABLE" 
               FROM DUAL; 
         END;
    
         -- [프로시져 종료]
         RETURN;
         
      END IF;
   
   /*=================================*/   
      
   ELSIF TRIM(I_GUBUN) = '4' THEN -- [삭제]
   
      -- [디버깅 단계 지정]
      M_DEBUG := '3-4'; 
      
      
      -- [인풋값으로 들어온 조건으로 검색 되는 데이터가 있는지 확인]
      SELECT COUNT(*) INTO M_COUNT
          FROM TEST_DEPT
      WHERE TRIM(DEPT_IDX) = TRIM(I_IDX)
      ;
      
      
      -- [카운트 값 확인 실시]
      IF M_COUNT > 0 THEN -- [검색된 데이터가 있는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-4-1';
         
         BEGIN
          -- [데이터 수정 실시]
          DELETE FROM TEST_DEPT 
              WHERE TRIM(DEPT_IDX) = TRIM(I_IDX);
         END;
         
         -- [커밋 수행]
         COMMIT;
         
         BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'Y : ' || '데이터 삭제를 완료했습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT '' AS "DATA_TABLE" 
               FROM DUAL; 
         END;
         
         -- [프로시져 종료]
         RETURN;
         
      ELSE -- [검색된 데이터가 없는 경우]
      
         -- [디버깅 단계 지정]
         M_DEBUG := '3-4-2';
         
         BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || '삭제 조건을 만족하는 데이터가 없습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
            SELECT '' AS "DATA_TABLE" 
               FROM DUAL; 
         END;
    
         -- [프로시져 종료]
         RETURN;
         
      END IF;
   
   /*=================================*/
   
   ELSIF TRIM(I_GUBUN) = '5' THEN -- [전체 조회]
   
      -- [디버깅 단계 지정]
      M_DEBUG := '3-5';
      
      BEGIN
       -- [리턴 테이블]
       OPEN O_CURSOR FOR 
       SELECT DEPT_IDX AS 순서 ,
              DEPT_NAME AS 부서이름 ,
              DEPT_CHANNER AS 채널 ,
              DEPT_UPDT AS 수정일자
          FROM TEST_DEPT
       ORDER BY TO_NUMBER(DEPT_IDX) ASC;
               
       -- [리턴 메시지 삽입]        
       O_MSG := 'Y : ' || '전체 데이터 조회를 완료했습니다.' || ' [M_DEBUG : '|| M_DEBUG ||']';      
      END;
         
      -- [프로시져 종료]
      RETURN;
   
   /*=================================*/
      
   ELSE -- [정의 되지 않은 구분 값]
   
      -- [디버깅 단계 지정]
      M_DEBUG := '3-6';
      
      BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || '구분 값을 다시 확인해주세요.' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
          SELECT '' AS "DATA_TABLE" 
            FROM DUAL; 
      END;
    
      -- [프로시져 종료]
      RETURN; 
      
   END IF;        
   
   
   /*=======================================================*/
   
   
/***************** [예외 발생 처리 수행 부분] ******************/
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      BEGIN
          -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || 'EXCEPTION : NO_DATA_FOUND' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
          SELECT '' AS "DATA_TABLE" 
            FROM DUAL;
      END;

   WHEN OTHERS
   THEN
      BEGIN
         -- [리턴 메시지 삽입]        
          O_MSG := 'N : ' || 'EXCEPTION : OTHERS' || ' [M_DEBUG : '|| M_DEBUG ||']'; 
          
          -- [리턴 테이블]
          OPEN O_CURSOR FOR 
          SELECT '' AS "DATA_TABLE" 
            FROM DUAL;
      END;

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

[결과 출력]

​

 

반응형
저작자표시

'OracleDB' 카테고리의 다른 글

167. (Oracle/오라클) case when then 문을 사용해서 begin end 구문에서 조건문 판단 실시  (0) 2022.05.16
166. (Oracle/오라클) 프로시저 (PROCEDURE) - 사용자 로그인 처리 수행  (0) 2022.05.13
164. (Oracle/오라클) 테이블 생성 시 (create table) 컬럼 인덱스 시퀀스 자동 증가 설정  (0) 2022.05.10
163. (Oracle/오라클) regexp_like 정규식 패턴을 사용해 특정 패턴 포함 데이터 출력 실시  (0) 2022.05.10
162. (Oracle/오라클) 오라클 주기적 배치 작업 수행 프로시저, 테이블 등 목록 확인 방법 - Jobs , Scheduler  (0) 2022.03.30
'OracleDB' Related Articles more
Comments