오라클 DBMS 정리 14

-- 프로시저 CRUD 연습용 테이블 생성(구조만 복사)
CREATE TABLE test_proc
AS (SELECT empno, ename, hiredate,sal,job
    FROM emp
    WHERE 1=0);
                                   
-- pk 설정
ALTER TABLE test_proc ADD CONSTRAINT pk_test_proc PRIMARY KEY(empno);
          
-- pk 설정 확인
SELECT * FROM user_constraints
WHERE TABLE_NAME='TEST_PROC';

-- 프로시저를 이용한 INSERT
-- 사원번호, 사원명, 연봉, 직급를 입력받아 test_proc테이블에
-- 추가하는 PROCEDURE를 작성하세요.   
-- 제약들                      
--   * 사원번호는 1~9999 사이의 값만 입력되어야 한다.
--   * 연봉 2500~8000 사이만 입력되어야한다.
--     2500 이하라면 2500으로 8000 이상이라면 8000으로 설정하여 추가
--   * 직급은 사원, 주임, 대리, 과장, 차장, 부장, 이사가 입력되었을 때만
--     추가한다.
--   * 사원명은 영어인 경우 첫자를 대문자로 변경하여 추가                                         
--   * 중복된 사원 번호가 입력되면 예외로 처리한다.

CREATE OR REPLACE PROCEDURE insert_test_proc(
    empno NUMBER,
    ename VARCHAR2,
    sal NUMBER,
    job VARCHAR2,
    msg OUT VARCHAR2,
    row_cnt OUT NUMBER
)                       
IS
    -- in parameter는 값 변경이 안되기 때문에 값이 변경되는 경우
    -- 새로운 변수를 생성해야 한다
    temp_sal NUMBER := sal;
    

BEGIN
    row_cnt := 0;

    IF empno BETWEEN 1 AND 9999 THEN
                                
        IF temp_sal < 2500 THEN
            temp_sal := 2500;
        ELSIF temp_sal > 8000 THEN
            temp_sal := 8000;
        END IF;              
        
        IF job IN ('사원', '주임', '대리', '과장', '차장', '부장', '이사') THEN
            
            INSERT INTO test_proc(empno, ename, hiredate, sal, job)
            VALUES(empno, INITCAP(ename), SYSDATE, temp_sal, job);
                   
            row_cnt := SQL%ROWCOUNT;
            -- COMMIT은 커서의 값을 사용 후에 사용(커서값을 날려버림)
            COMMIT;
            msg := empno||'번 사원 정보가 추가되었습니다.';
            
        ELSE
            msg := job||'은 입력가능한 직급이 아닙니다';        
        ENd IF;                   
                
    ELSE
        msg := empno||'사원번호는 1에서 9999사이가 아닙니다.';
    END IF;  
    
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        msg := empno||'번 사원번호는 이미 존재합니다.';
  
    -- 이런식이 좋은 예외처리
    WHEN OTHERS THEN                                   
        IF sqlcode = -12899 THEN
            msg := ename||'명은 한글 3자, 영어 10자를 초과하였습니다.';
        END IF;

END;
/

-- insert_test_proc 프로시저 out parameter를 받을 바인드 변수 선언
VAR msg VARCHAR2(300);
VAR cnt NUMBER;

-- 직접 실행
EXEC insert_test_proc(1111,'테스트',3000,'주임',:msg,:cnt);

-- 바인드 변수 출력, 결과 확인
PRINT msg;
PRINT cnt;

-- 프로시저를 이용한 UPDATE
-- 사원번호, 직급, 연봉을 입력받아 test_proc테이블에서 변경하는 프로세저
-- 제약들
--  * 직급은 사원, 주임, 대리, 과장, 차장, 부장, 이사,
--    상무, 전무, 대표이사가 입력 되었을 때만 변경
--  * 연봉이 현재 연봉보다 작다면 현재 연봉보다 5% 인상액으로 변경

CREATE OR REPLACE PROCEDURE update_test_proc(
    i_empno NUMBER,
    i_job VARCHAR2,
    sal    NUMBER,
    res_msg OUT VARCHAR2,
    row_cnt OUT NUMBER    
)
IS
    -- 현재 연봉을 받아오기 위해 만든 변수
    temp_sal NUMBER;

BEGIN                   
    row_cnt := 0;   
    
    IF i_job IN ('사원','주임','대리','과장',
               '차장','부장','이사','상무',
               '전무','대표이사') THEN
        
        SELECT sal
        INTO temp_sal
        FROM test_proc
        WHERE empno = i_empno;
        
        IF sal < temp_sal THEN
            temp_sal := TRUNC(temp_sal*1.05, 0);
        ELSE
            temp_sal := sal;
        END IF;                
        
        UPDATE test_proc
        SET sal = temp_sal, job = i_job
        WHERE empno = i_empno;    
        
        row_cnt := SQL%ROWCOUNT;
                                
        COMMIT;                  
        
        res_msg := i_empno||'번 사원의 정보가 변경되었습니다. 제시연봉 : '||sal||
                        ', 확정연봉 : '||temp_sal;
    
    ELSE
        res_msg := i_job||'은 사내 직무가 아닙니다.';
    END IF;
    
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        res_msg := i_empno||'번 사원은 존재하지 않습니다.';
    WHEN OTHERS THEN
        res_msg := sqlerrm;

END;
/

-- 바인드 변수(외부 변수) 선언
VAR msg VARCHAR2(100);
VAR row_cnt NUMBER;

-- 프로시저 실행
EXEC update_test_proc(1112,'주임',3000,:msg,:row_cnt);

-- 처리결과(바인드 변수) 출력
PRINT msg;
PRINT row_cnt;

-- 프로시저를 이용한 DELETE                 
-- 언어가 아니라 confirm dialog 없이 삭제처리
-- 사원번호를 입력받아 test_proc 테이블에서 삭제하는 PROCEDURE

CREATE OR REPLACE PROCEDURE delete_test_proc(
    i_empno NUMBER,
    msg OUT VARCHAR2,
    cnt OUT NUMBER
)                 
IS
    res_msg VARCHAR2(15);
BEGIN
    
     DELETE FROM test_proc
     WHERE empno = i_empno;
     
     cnt := SQL%ROWCOUNT;
     IF cnt = 1 THEN
         COMMIT;
         msg := i_empno||'번 사원 퇴사. 행복하세요~~';       
     ELSE                               
         msg := i_empno||'번 사원은 존재하지 않습니다.';    
     END IF;
END;
/

-- 바인드 변수 선언
VAR msg VARCHAR2(100);
VAR cnt NUMBER;

-- 프로시저 실행
-- 삭제 : 존재하는 사원번호로 삭제
EXEC delete_test_proc(2222, :msg, :cnt);
               
-- 처리 결과 출력
PRINT msg;
PRINT cnt;

-- 프로시저를 이용한 SELECT
-- 한 조회결과는 내보낼 수 있으나 여러 조회 결과는 REFCURSOR를 사용해야 함
-- 프로시저를 이용한 SELECT
-- 직무(사원, 주임, 대리, 과장, 차장, 부장, 이사, 상무,
-- 전무, 대표이사)일 때 입력받아 해당 직무의
-- 사원수와 연봉합(커서사용)을 out parameter에 설정하는 프로시저

CREATE OR REPLACE PROCEDURE select_test_proc_cnt(
    i_job VARCHAR2,
    cnt_emp OUT NUMBER,
    sum_sal OUT NUMBER,
    msg OUT VARCHAR2
)                     
IS
    temp_sal NUMBER := 0;
    
BEGIN
    sum_sal := 0;
    cnt_emp := 0;      

    IF i_job IN ('사원', '주임', '대리', '과장',
              '차장', '부장', '이사', '상무', '전무', '대표이사') THEN   
  
      -- 커서를 사용해서 여러 조회결과 처리
      FOR rec_emp IN (SELECT sal
                      FROM test_proc
                      WHERE job = i_job)
      LOOP
      -- 조회가 실행될 때마다 사원수 증가
        cnt_emp := cnt_emp + 1;
        sum_sal := sum_sal + rec_emp.sal;
      END LOOP;                              
      
/* 
      -- 이럴 땐 커서 사용 안하고 
      -- COUNT(), SUM() 사용가능
      SELECT COUNT(empno), SUM(sal)
      INTO sum_sal, cnt_emp
      FROM test_proc
      WHERE job=i_job;
*/           
    IF cnt_emp != 0 THEN
      msg := i_job||' 으로 조회된 결과 있음';        
    ELSE
      msg := i_job||' 직급의 사원은 존재하지 않습니다.';
      -- cnt_emp = 0이면 결과가 NULL이 나오기 때문에 할당
      cnt_emp := 0;            
    END IF;                                         
  ELSE
    msg := i_job||'은 없는 직무입니다.';
  END IF;
END;
/

-- 직급에 따른 사원 수, 연봉합 조회
-- out parameter 담을 바인드변수 선언
VAR cnt_emp NUMBER;
VAR sum_sal NUMBER;
VAR msg VARCHAR2(100);

-- 프로시저 실행
EXEC select_test_proc_cnt('주임',:cnt_emp, :sum_sal, :msg);
        
-- 프로시저 결과 출력
PRINT msg;
PRINT cnt_emp;
PRINT sum_sal;

CURSOR 커서명 IS 
    SELECT zipcode ...
    FROM   zipcode
--    WHERE dong LIKE i_dong% 
--    i_dong이란 이름의 커서로 인식 error!
    WHERE dong LIKE i_dong||'%';  -- 이런식으로 붙여 사용!

-- 변수명과 %를 같이 사용하기
-- 동이름을 입력받아 zipcode테이블의 주소 검색하는 PROCEDURE
-- 우편번호의 오름차순 정렬했을 때 가장 먼저 조회되는
-- 우편번호, 시도, 구군, 동, 번지를 묶어서 out parameter에 할당하고
-- 조회된 우편번호의 건수를 out parameter에 할당할 것            

CREATE OR REPLACE PROCEDURE search_zipcode(
    i_dong VARCHAR2,   
    
    address OUT VARCHAR2,
    search_cnt OUT NUMBER
)
IS                
  -- 1. 커서 선언
    CURSOR cur_zipcode IS
        SELECT   zipcode, sido, gugun,
                 dong, NVL(bunji, '번지없음') bunji
        FROM     zipcode
        WHERE    dong LIKE i_dong||'%'
        ORDER BY zipcode;      
      
    TYPE rec_zip IS RECORD(   
        -- 변수명이 테이블명.컬럼명과 모두 같다면 인식 못하는 버그발생 가능
        -- zipcode zipcode.zipcode%TYPE,
        zip CHAR(7),                     
        -- 위에 선언된 변수가 테이블명과 같다면 테이블을 참조하지 못할 수 있음
        -- zipcode CHAR(7)로 선언 시 밑에 테이블명.속성명이 변수.속성명으로 인식가능.
        sido zipcode.sido%TYPE,
        gugun zipcode.gugun%TYPE,
        dong zipcode.dong%TYPE,
        bunji zipcode.dong%TYPE            
    );                       
    
    -- 인출한 데이터를 저장할 레코드 변수
    zip_data rec_zip;                    
    
    -- 가장 첫 레코드를 저장한 RECORD 변수
    first_data rec_zip;                     
    
    -- 가장 첫 레코드를 알기위한 인덱스 변수
    cnt NUMBER := 0;

BEGIN
             
  -- 2. 커서 열기
  IF cur_zipcode%ISOPEN THEN
        CLOSE cur_zipcode;  
  END IF;             
  OPEN cur_zipcode;
  
  -- 3. 인출
  LOOP
        FETCH cur_zipcode INTO zip_data;
        EXIT WHEN cur_zipcode%NOTFOUND;
        
        cnt := cnt + 1;
        
        IF cnt = 1 THEN
            first_data := zip_data;
        END IF;
  END LOOP;
  
  search_cnt := cur_zipcode%ROWCOUNT;
    
  IF search_cnt != 0 THEN
      address := first_data.zip||' '||first_data.sido||' '||
            first_data.gugun||' '||first_data.dong||' '||first_data.bunji;
  ELSE
      address := i_dong||'은 존재하지 않습니다.';     
  END IF;                        
  
  -- 4. 커서 닫기
  CLOSE cur_zipcode  

  EXCEPTION
  WHEN invalid_cursor THEN
      address := '커서가 잘못된 연산을 수행';
  WHEN others THEN
      address := sqlerrm;

END;
/

-- 동이름으로 우편번호 검색
VAR address VARCHAR2(100);
VAR search_num NUMBER;

EXEC search_zipcode('상도동',:address,:search_num);

PRINT address;
PRINT search_num;

-- 해당 쿼리의 첫번째 레코드와 결과가 같으면 됨
SELECT *
FROM zipcode
WHERE dong LIKE '상도동%'
ORDER BY zipcode;

CREATE OR REPLACE PROCEDURE 프로시저명(
    커서명 OUT SYS_REFCURSOR
)
IS 

BEGIN
    OPEN 커서명 FOR 쿼리문정의;
    -- 프로시저안에서 절대 인출(FETCH)하면 안됨!
    -- 인출시 밖에서 커서를 받아갈 수 없어짐!
END;
/

VAR 커서명 REFCURSOR;    -- 1

EXEC 프로시저명(:REF커서명);  -- 2

PRINT 커서명;            -- 3

-- 프로시저를 이용한 SELECT(다중행조회)
-- 조회된 결과가 여러개라면 out parameter에 담을 수 없다.
-- 바인드변수로 테이블과 레코드타입은 지원안함
-- 커서의 제어권을 밖으로 내주면 됨 = SYS_REFCURSOR를 사용

-- dept 테이블에서 모든 부서정보를 조회하여
-- out parameter로 설정하는  프로시저를 작성

CREATE OR REPLACE PROCEDURE select_all_dept(
    cur_dept OUT SYS_REFCURSOR
)
IS
BEGIN

    OPEN cur_dept
        FOR SELECT deptno, dname, loc
              FROM dept;

END;
/

-- 1. 바인드변수(커서) 선언, SYS_REFCURSOR 사용
VAR cur_dept REFCURSOR;

-- 2. 프로시저 실행
EXEC select_all_dept(:cur_dept);

-- 3. 결과 출력
PRINT cur_dept;

-- 위 select_all_dept 프로시저에서 내부에 FETCH로 커서사용
...
IS  
    dept_data dept%ROWTYPE;
BEGIN
...
    LOOP
        FETCH cur_dept INTO dept_data;
        EXIT WHEN cur_dept%NOTFOUND;
    
    END LOOP;
...

-- 만약 프로시저 내부에서 FETCH 후 외부에서 커서를 출력하면
-- 당연히 바인드변수를 출력해도 결과가 안나온다(No Records)
EXEC select_all_dept(:cur_dept);
PRINT cur_dept;

-- SYS_REFCURSOR를 이용한 프로시저결과 다중행 조회
-- 부서번호를 입력받아 emp테이블에서 부서별 사원정보를 조회하여
-- out parameter로 저장하는 프로시저 작성
-- * 사원번호, 사원명, 연봉, 부서번호, 부서명, 위치를 조회
-- * 입력값이 10~19번 입력되면 10번부서 조회
--           20~29번 입력되면 20번부서 조회
--           그외는 30번 부서를 조회

CREATE OR REPLACE PROCEDURE select_emp(
    deptno NUMBER,
    cur_join OUT SYS_REFCURSOR
)
IS
    temp_deptno NUMBER := deptno;
BEGIN

    -- 입력되는 부서번호를 10또는 20으로 생성
    -- 10으로 나눈 몫*10으로 원하는 부서별 구함                                   
    temp_deptno := TRUNC(temp_deptno/10, 0)*10;

    IF temp_deptno NOT IN (10, 20) THEN
        temp_deptno := 30;
    END IF;      
    
    OPEN cur_join
        FOR SELECT e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc
            FROM emp e, dept d
            WHERE (e.deptno = d.deptno)
              AND d.deptno = temp_deptno;
END;
/

-- 부서번호 입력하여 부서별 사원정보 조회
VAR cur_emp REFCURSOR;

-- 부서번호 : 10~19 : 10, 20~29 : 20, 그외 : 30
EXEC select_emp(14,:cur_emp);

PRINT cur_emp;