오라클 DBMS 정리 11

-- 암시적(묵시적) 커서 SQL(커서명) % 속성명
-- 포스트 아래에 커서정리 참고
SQL%속성

-- PL/SQL에서 DML 사용
BEGIN
    INSERT INTO cp_emp3(empno, ename, deptno, job, sal)
    VALUES(3333, '유재석', 10, '부장', 8000);

    COMMIT;
END;
/

  -- 암시적(묵시적) 커서 ROWCOUNT속성을 이용하면 
  -- 수행한 행의 수를 얻을 수 있다.

  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 추가');

END;
/

  ...
  -- job의 데이터크기보다 큰 데이터를 넣기때문에 error!
  -- PL/SQL에서 INSERT는 성공 또는 실패(예외)
  INSERT INTO cp_emp3(empno, ename, deptno, job, sal)
  VALUES(3333, '유재석', 10, '부장님최고', 8000);

  COMMIT;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 추가');
  
  -- 예외처리는 여기서
  EXCEPTION
  WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('문제발생');

END;
/

-- PL/SQL은 값을 제어한 후에 SQL문을 수행한다
-- PL/SQL을 사용하면 입력값에 따라 다른 테이블에 INSERT 가능하다

-- 사원번호, 사원명, 연봉을 사용자에게 입력받아 cp_emp2테이블에
-- 연봉이 5000이상이면 cp_emp3테이블에 추가하고 5000미만이라면
-- cp_emp2 테이블에 추가하는 PL/SQL 작성
-- 단, cp_emp3 테이블에 추가되는 사원의 부서 번호는 10번부서로 추가

SET serveroutput ON
SET verify OFF

ACCEPT empno PROMPT '사원번호 : '
ACCEPT ename PROMPT '사원명 : '
ACCEPT sal PROMPT '연봉 : '

DECLARE
    empno cp_emp2.empno%TYPE;
    ename cp_emp2.ename%TYPE;
    sal NUMBER := &sal;
BEGIN                

    empno := &empno;
    ename := '&ename';
    
    IF sal >= 5000 THEN
        INSERT INTO cp_emp3(empno, ename, sal)
        VALUES(empno, ename, sal, 10);
        DBMS_OUTPUT.PUT_LINE('연봉이 5000이상이므로 cp_emp3 테이블에 추가');
    ELSE                        
        INSERT INTO cp_emp2(empno, ename, sal, hiredate)
        VALUES(empno, ename, sal, SYSDATE);                      
        DBMS_OUTPUT.PUT_LINE('연봉이 5000미만이므로 cp_emp2 테이블에 추가');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 입력성공');
    COMMIT;
    
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Exception!');

END;
/

-- 업데이트는 수행 횟수를 알아야 한다(0~n개의 레코드가 바뀜)
-- PL/SQL에서 UPDATE 사용
SET serveroutput ON
   
DECLARE
    cnt NUMBER;
BEGIN
    UPDATE cp_emp2
    SET sal = 3000
    WHERE empno = 2221;

    -- 암시적(묵시적) 커서 SQL을 사용하여 변경한 행의 수를 얻는다
    cnt := SQL%ROWCOUNT;
    -- 변경된 사원이 1명일때만 Transcation을 완료하고 그렇지 않다면
    -- 작업을 취소.

    IF cnt = 1 THEN
        DBMS_OUTPUT.PUT_LINE(cnt||'건이 변경되었습니다');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('조회된 사원이 여러명이거나 없으므로 변경작업을 취소합니다..');
        ROLLBACK;
    END IF;
END;
/

-- 삭제
SET serveroutput ON
BEGIN
    DELETE FROM cp_emp2
    WHERE empno = 2222;
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'건이 삭제되었습니다.');
END;
/

SELECT 컬럼명, ... 
INTO   변수명, ...
FROM   테이블명
...

-- cp_emp2에서 사원번호가 7566인 사원의
-- 사원명, 입사일, 연봉을 조회하여 출력하는 PL/SQL
SET serveroutput ON

DECLARE
    ename cp_emp2.ename%TYPE;
    input_date VARCHAR2(10);
    sal cp_emp2.sal%TYPE;
BEGIN
    
    SELECT ename, TO_CHAR(hiredate, 'yyyy-mm-dd'), sal
    INTO ename, input_date, sal
    FROM cp_emp2
    WHERE empno = 7566;         
    
    DBMS_OUTPUT.PUT_LINE(ename||' / ' ||input_date||' / '
                         ||sal||' / '||SQL%ROWCOUNT);
    
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('문제발생');

END;
/

-- SELECT와 INSERT를 수행하는 PL/SQL 예제
-- 사원번호, 사원명, 부서번호, 직무를 입력받아 
-- cp_emp3 테이블에 추가하는 PL/SQL
-- 단, 사원의 연봉은 부서번호에 따라 동일 연봉으로 추가한다.
-- 10번 부서 - emp 테이블의 사원번호가 7566인 사원과 같은 연봉,
-- 20번 부서 - emp 테이블의 사원번호가 7698인 사원과 같은 연봉,
-- 30번 부서 - emp 테이블의 사원번호가 7782인 사원과 같은 연봉,
-- 그 외 부서번호가 입력되면 7788 사원의 연봉으로 추가
SET serveroutput ON
                                           
ACCEPT empno PROMPT '사원번호 입력 : '
ACCEPT ename PROMPT '사원명 입력 : '
ACCEPT deptno PROMPT '부서번호 입력 : '
ACCEPT job PROMPT '직무 입력 : '

DECLARE                         
    i_empno cp_emp3.empno%TYPE := &empno;
    i_ename cp_emp3.ename%TYPE := '&ename';
    i_deptno cp_emp3.deptno%TYPE := &deptno;
    i_job cp_emp3.job%TYPE := '&job';    
    
    sal cp_emp3.sal%TYPE;
    select_empno cp_emp.empno%TYPE := 7788;           
BEGIN        
  
    -- 입력되는 부서번호별로 참조할 연봉을 조회하기 위한
    -- 사원번호를 설정
    IF i_deptno = 10 THEN
        select_empno := 7566;  
    ELSIF i_deptno = 20 THEN
        select_empno := 7698;  
    ELSIF i_deptno = 30 THEN
        select_empno := 7782;    
    END IF;
    
    -- 설정된 사원번호별로 연봉을 조회
    SELECT sal
    INTO sal
    FROM emp
    WHERE empno = select_empno;
    
    -- 조회된 연봉으로 추가 작업
    INSERT INTO cp_emp3(empno, ename, deptno, job, sal)
    VALUES(i_empno, i_ename, i_deptno, i_job, sal);    
    
    DBMS_OUTPUT.PUT_LINE(i_empno||'번 사원정보를 추가하였습니다.');
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'건 입력 성공');
    COMMIT;
    
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('에러발생');
END;
/

-- 암시적(묵시적)커서
SQL%속성명

-- 명시적커서
커서명%속성명

-- 커서 속성사용
커서명%속성명

-- 커서 동작 제어
동작명 커서명

DECLARE
  -- 1
  CURSOR 커서명 IS 
    SELECT ...
BEGIN
  -- 2
  OPEN 커서명;
  -- 3
  LOOP
    FETCH 커서명 INTO 변수명,... ;

    -- 커서에서 인출한 값이 없다면 반복문을 빠져나가야한다
    EXIT WHEN (커서명%NOTFOUND);
        
    -- 값이 있었을 때 처리할 코드들..
  END LOOP;
  -- 4
  CLOSE 커서명;  
END;
/

SET serveroutput ON

DECLARE
  -- 1. 선언
  CURSOR cur_dept IS
    SELECT deptno, dname, loc
    FROM dept;
           
  dept_data dept%ROWTYPE;
BEGIN      
  -- 2. 열기
  OPEN cur_dept;
    
    
  LOOP
    -- 3. 인출(커서가 움직이며 레코드의 컬럼값을 얻기)
    FETCH cur_dept INTO dept_data;
      
    EXIT WHEN(cur_dept%NOTFOUND);
      
    DBMS_OUTPUT.PUT_LINE(dept_data.deptno||' '||dept_data.dname
                                              ||' '||dept_data.loc);
  END LOOP;

  -- 4. 닫기
  CLOSE cur_dept;
END;
/ 

--- 데이터는 가져오는부분(처리하는부분)과 보여주는 부분(VIEW)으로 나눔

-- 부서번호를 입력받아 해당부서에 근무하는
-- 사원번호, 사원명, 연봉, 입사일을 조회하고,
-- 조회한 후 총 사원 수와, 연봉합을 구하시오.

SET serveroutput ON
SET verify OFF

ACCEPT deptno PROMPT '부서번호 : '

DECLARE
    CURSOR cur_emp IS
     SELECT empno, ename, sal,
               TO_CHAR(hiredate, 'yyyy-mm-dd') hiredate
     FROM   emp
     WHERE  deptno = &deptno;
     
    TYPE rec_emp IS RECORD (
        empno    emp.empno%TYPE,
        ename emp.ename%TYPE,
        sal emp.sal%TYPE,
        hiredate VARCHAR2(10)
    );
    
    -- 테이블 생성 (데이터 처리와 보여주는 부분을 분리시키기 위해)
    TYPE tab_emp IS TABLE OF rec_emp
        INDEX BY BINARY_INTEGER;
  
    emp_data rec_emp;                             
    arr_emp tab_emp;
  
    total_sal NUMBER := 0;
    i NUMBER := 0;
BEGIN
    IF cur_emp%ISOPEN THEN
        CLOSE cur_emp;
    END IF;
    
    OPEN cur_emp;
    
    -- 데이터 처리하는 부분    
    LOOP
        FETCH cur_emp INTO emp_data;
        EXIT WHEN cur_emp%NOTFOUND;
        i := i+1;
        arr_emp(i) := emp_data;
    END LOOP;                           
                               
    -- 화면 구성하는 부분
    IF i != 0 THEN
        DBMS_OUTPUT.PUT_LINE('사원번호 사원명  연봉   입사일');

        FOR idx IN 1 .. arr_emp.COUNT LOOP        
            DBMS_OUTPUT.PUT_LINE(arr_emp(idx).empno||
                ' '||arr_emp(idx).ename||' '||arr_emp(idx).sal||
                ' '||arr_emp(idx).hiredate);
                                                
            total_sal := total_sal + arr_emp(idx).sal;  
        END LOOP;                                   
        
        DBMS_OUTPUT.PUT(&deptno||'번 부서 사원수 : '||cur_emp%ROWCOUNT);    
        DBMS_OUTPUT.PUT_LINE(', 연봉합 : '||total_sal);        
    ELSE
      DBMS_OUTPUT.PUT_LINE(&deptno||'번 부서는 사원이 없습니다.');
    END IF;                                         
    CLOSE cur_emp;
END;
/