오라클 DBMS 정리 11 | younggeun0.dev

오라클 DBMS 정리 11 - PL/SQL with DML, CURSOR

PL/SQL에서 Query문 사용하기

DDL, DCL, DML 사용할 수 있다.
- 다른건 다 같으나 SELECT만 좀 다르다.
SELECT는 INTO절이 들어가며 한 행만 조회되어야 한다.
FUNCTION, PROCEDURE가 매개변수를 가지고 Query를 사용하면 매개변수명과 WHERE절에서 사용하는 컬럼명이 달라야 한다.
- 가능하면 변수명이 컬럼명과 같지 않도록 이름을 짓는다.
INSERT, UPDATE, DELETE는 수행한 행의 수를 반환하는 암시적(묵시적) 커서를 사용할 수 있다.

-- 암시적(묵시적) 커서 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;
/

11-02

-- 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절이 정의됨
- 조회된 컬럼값이 변수명에 들어감
- 변수와 컬럼의 데이터형과 갯수는 일치해야 한다.
- 부분이 나눠지기 때문에 컬럼명과 변수명이 같아도 된다(INSERT 처럼)
조회결과가 한행이 아니라면 error가 발생!
- 에러발생 시 EXCEPTION 절로 넘어감

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;
/

CURSOR

다른 언어로 보면 포인터
암시적(묵시적) 커서와 명시적 커서로 제공됨
- 암시적 커서
  - 개발자가 정의하지 않아도 쿼리문을 실행하면 자동으로 생성되는 커서.
  - 커서명 : SQL
- 명시적커서
  - 개발자가 정의해서 사용하는 커서
  - 생명주기를 가진다
    - 선언->열기->인출->닫기
  - 사용할 수 있는 속성이 많다
커서는 인라인뷰를 사용, 메모리를 계속 사용한다.
- 꼭 닫기로 닫아줘야 메모리 누수를 잡을 수 있다.

-- 암시적(묵시적)커서
SQL%속성명
 
-- 명시적커서
커서명%속성명
 
-- 커서 속성사용
커서명%속성명
 
-- 커서 동작 제어
동작명 커서명

커서의 동작과 속성

11-03

커서 사용법

선언 : 커서가 실행할 쿼리문을 정의
열기
인출
닫기

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;
/

헷갈리는 내용 추가복습

TABLE TYPE
- 테이블변수명(인덱스) := 레코드변수명
  - 레코드할당
- 테이블변수명.COUNT
  - 테이블 레코드 수를 반환
커서명 속성
- 커서명%ROWCOUNT
  - 커서로 패치한 수를 반환
- 커서명%NOTFOUND
  - 커서가 패치할 레코드가 없으면 FALSE를 반환
  - LOOP문 빠져나갈때 EXIT 조건으로 사용

숙제풀이 (새 창에서 열림)

오라클 DBMS 정리 11 - PL/SQL with DML, CURSOR

PL/SQL에서 Query문 사용하기

SELECT

CURSOR

헷갈리는 내용 추가복습

댓글