8/16 PL/SQL, Function, Procedure, Cursor, Exception

PL/SQL의 개념
 Oracle에서 지원하는 프로그래밍 언어의 특성을 수용한 SQL의 확장
 PL/SQL Block내에서 SQL의 DML(데이터 조작어)문과 Query(검색어)문, 
 그리고 절차형 언어(IF, LOOP) 등을 사용하여 절차적으로 프로그래밍을 가능하게 한 강력한 트랜잭션 언어

 장점
 1) 프로그램 개발의 모듈화 : 복잡한 프로그램을 의미있고 잘 정의된 작은 Block 분해
 2) 변수 선언 : 테이블과 칼럼의 데이터 타입을 기반으로 하는 유동적인 변수를 선언
 3) 에러 처리 : Exception 처리 루틴을 사용하여 Oracle 서버 에러를 처리
 4) 이식성 : Oracle과 PL/SQL을 지원하는 어떤 호스트로도 프로그램 이동 가능
 5) 성능 향상 : 응용 프로그램의 성능을 향상

 

PL/SQL의 기본 Block (PL/SQL에서 실행부는 꼭 써야한다.)

 

* 함수 생성할 때 주석도 같이 저장하고 싶은 경우 함수 안에 함수설명 주석 작성하기.

 

-- 1. Function : 하나의 값을 돌려줘야 하는 경우에 Function을 생성
-- 문1) : 특정한 수에 세금을 7%로 계산하는 Function을 작성 
-- 조건1) 함수명 : tax
-- 조건2) parameter : p_num (급여)
-- 조건3) 계산을 통해 7% 값을 돌려줌
CREATE OR REPLACE FUNCTION tax(p_num IN NUMBER)
RETURN NUMBER --리턴값이 숫자다. 함수에서는 리턴을 선언해야 함.
IS
    v_tax NUMBER;
BEGIN
    v_tax := p_num*0.07; -- 값 넣을 때 := 를 사용
    RETURN (v_tax);
END;

SELECT tax(100) FROM dual;
SELECT tax(200) FROM dual;

SELECT empno, ename, tax(sal)FROM emp;


-- EMP 테이블에서 사번을 입력받아 해당 사원의 급여에 따른 세금을 구함.
-- 급여가 1000 미만이면 급여의 5%, 급여가 2000 미만이면 7%, 3000 미만이면 9%, 그 이상은 12%로 세금
--- FUNCTION emp_tax
-- 1) Parameter : 사번
-- 2) 사번을 가지고 급여를 구함
-- 3) 급여를 가지고 세율 계산 
-- 4) 계산 된 값 RETURN NUMBER
CREATE OR REPLACE FUNCTION  emp_tax
    (p_empno IN emp.empno%TYPE) -- 1) Parameter : 사번
RETURN NUMBER
IS
    v_sal emp.sal%TYPE;
    v_pct NUMBER(5,2); --5자리 수, 소수점은 2자리
BEGIN
    -- 2) 사번을 가지고 급여를 구함
    SELECT sal INTO v_sal FROM emp WHERE empno = p_empno ;
    -- 3) 급여를 가지고 세율 계산 
    IF  v_sal < 1000 THEN
        v_pct := (v_sal*0.05);
    ELSIF v_sal < 2000 THEN
        v_pct := (v_sal*0.07);
    ELSIF v_sal < 3000 THEN
        v_pct := (v_sal*0.09);
    ELSE      
        v_pct := (v_sal*0.12);
    END IF;
    RETURN(v_pct);  
END emp_tax;

SELECT ename, sal, emp_tax(empno) emp_rate FROM emp;

-----------------------------------------------------
--  Procedure up_emp 실행 결과
-- SQL> EXECUTE up_emp(1200);  -- 파라미터는 사번 
-- 결과 : 급여 인상 저장
-- 변수 : v_job(업무),v_pct(세율)
-- 조건 1) job이 SALE문을 포함하면 v_pct : 10 (IF v_job LIKE 'SALE%' THEN)
--      2) job이 MAN문을 포함하면 v_pct : 7  
--      3) 나머지는 v_pct : 5
-- job에 따른 급여 인상을 수행 sal = sal+sal*v_pct/100
-- 확인 : DB -> TBL
-- 함수는 직접 실행할 수 있지만 프로시저는 우클릭 > 실행으로 실행시켜야 한다.
-- 하단 프로시저를 실행시키고 다시 emp 테이블을 확인해보면 데이터가 바껴있는 것을 확인할 수 있다.
-----------------------------------------------------
CREATE OR REPLACE PROCEDURE up_emp (p_empno IN emp.empno%TYPE)
IS
    v_job emp.job%TYPE;
    v_pct NUMBER(3);
BEGIN
   SELECT   job  
   INTO     v_job 
   FROM     emp
   WHERE    empno = p_empno ;
   
   IF       v_job LIKE 'SALE%' THEN
            v_pct := 10;
   ELSIF    v_job LIKE 'MAN%' THEN
            v_pct := 7;
   ELSE
            v_pct := 5;
   END IF;
   UPDATE   emp
   SET      sal = sal+sal*v_pct/100
   WHERE    empno = p_empno;
END;

SELECT up_emp (7839) FROM dual;


-- 문제
-- PROCEDURE Delete_emp
-- SQL> EXECUTE Delete_emp(5555);

-- 출력 결과
-- 사원번호 : 5555
-- 사원이름 : 55
-- 입 사 일 : 81/12/03
-- 데이터 삭제 성공

--  1. Parameter : 사번 입력
--  2. 사번 이용해 사원번호, 사원이름, 입사일 출력
--  3. 사번 해당하는 데이터 삭제 
CREATE OR REPLACE  PROCEDURE Delete_Emp
( p_empno IN emp.empno%TYPE )
IS
    -- 삭제 데이터를 확인하기 변수 선언
     v_empno     emp.empno%TYPE;
     v_ename     emp.ename%TYPE;
     v_hiredate  emp.hiredate%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
   -- 삭제할  데이터 확인용 쿼리
    SELECT  empno, ename, hiredate
    INTO    v_empno, v_ename, v_hiredate
    FROM    emp
    WHERE   empno = p_empno ;
    DBMS_OUTPUT.PUT_LINE( '사원번호 : ' ||v_empno );
    DBMS_OUTPUT.PUT_LINE( '사원이름 : ' ||v_ename );
    DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' ||v_hiredate );
   -- 삭제 쿼리
    DELETE
    FROM emp
    WHERE empno = p_empno ;
    DBMS_OUTPUT.PUT_LINE( '데이터 삭제 성공 ' );
END Delete_Emp;


-- 문제 : 75번인 것 삭제하기
-- 행동강령 : 부서번호 입력 해당 emp 정보 PROCEDURE 
-- SQL> EXECUTE DeptEmpSearch(75);
-- 조회화면 사번 : 5555 이름 : 홍길동

CREATE OR REPLACE PROCEDURE DeptEmpSearch1
    (p_deptno IN emp.deptno%type )
IS
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT empno, ename
    INTO v_empno, v_ename
    FROM emp
    WHERE deptno = p_deptno;
    DBMS_OUTPUT.PUT_LINE('사번 : ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || v_ename);
END DeptEmpSearch1;    

-- 행동강령 : 부서번호 입력 해당 emp 정보 PROCEDURE 
-- SQL> EXECUTE DeptEmpSearch2(75);
--  조회화면 사번 : 5555 이름 : 홍길동
-- (위의 똑같은 문제를) %ROWTYPE를 이용하는 방법
CREATE OR REPLACE PROCEDURE DeptEmpSearch2
    (p_deptno IN emp.deptno%type )
IS
    -- 테이블 자체를 선언하면서 이 객체를 그대로 선언하는 대신 into를 v_emp로 받음.
    v_emp emp%ROWTYPE;
--  v_empno emp.empno%TYPE;
--  v_ename emp.ename%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT *
    INTO v_emp
    FROM emp
    WHERE deptno = p_deptno;
    DBMS_OUTPUT.PUT_LINE('사번 : ' ||  v_emp.empno);
    DBMS_OUTPUT.PUT_LINE('이름 : ' ||  v_emp.ename);
END DeptEmpSearch2;    

-- 행동강령 : 부서번호 입력 해당 emp 정보  PROCEDURE 
-- SQL> EXECUTE DeptEmpSearch3(10);
-- 조회화면 사번 : 5555 이름 : 홍길동
-- %ROWTYPE를 이용하는 방법
-- (위의 똑같은 문제에서 추가로) EXCEPTION 이용하는 방법 
CREATE OR REPLACE PROCEDURE DeptEmpSearch3
   (p_deptno IN emp.deptno%type )
IS
    v_emp emp%ROWTYPE ;
--  v_empno emp.empno%TYPE;
--  v_ename emp.ename%TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT empno, ename
    INTO v_emp
    FROM emp
    WHERE deptno = p_deptno;
    DBMS_OUTPUT.PUT_LINE('사번 : ' ||  v_emp.empno);
    DBMS_OUTPUT.PUT_LINE('이름 : ' ||  v_emp.ename);
    
    -- Multi Row Error --> 실제 인출은 요구된 것보다 많은 수의 행을 추출
    EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR CODE 1 : ' || TO_CHAR(SQLCODE));
        DBMS_OUTPUT.PUT_LINE('ERR CODE 2 : ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END DeptEmpSearch3;    
--에러코드 -1422(하단 사진 참고)

 

3. CURSOR (중요 : 굉장히 중요함. 별 3개)
1) 정의 : Oracle Server는 SQL문을 실행하고 처리한 정보를 저장하기 위해 "Private SQL Area" 이라고 하는 작업영역을 이용
이 영역에 이름을 부여하고 저장된 정보를 처리할 수 있게 해주는 것이 CURSOR이다.
2) 종류 : Implicit(묵시적인) CURSOR는 DML문과 SELECT문에 의해 내부적으로 선언
 Explicit(명시적인) CURSOR : 사용자가 선언하고 이름을 정의해서 사용 
 커서는 SQL문을 실행해서 처리하는 저장공간이다.
 비유하자면 JAVA의 ResultSet하여 get Next를 통해 가져왔던 것과 비슷(*면접에서는 이렇게 말하면 안됨)
 3) 속성(attribute)
(1) SQL%ROWCOUNT : 가장 최근의 SQL문에 의해 처리된 Row 수
(2) SQL%FOUND    : 가장 최근의 SQL문에 의해 처리된 Row의 개수가 한 개 이상이면 True
(3) SQL%NOTFOUND : 가장 최근의 SQL문에 의해 처리된 Row의 개수가 없으면 True
 4) 단계 (중요 : 별 2개, 시험이나 면접에 자주 나옴)
(1) DECLARE 단계 : 커서에 이름을 부여하고 커서 내에서 수행할 SELECT문을 정의함으로써 CURSOR를 선언
(2) OPEN 단계 : OPEN문은 참조되는 변수를 연결하고, SELECT문을 실행
(3) FETCH 단계 : CURSOR로부터 Pointer가 존재하는 Record의 값을 변수에 전달
(4) CLOSE 단계 : Record의 Active Set을 닫아주고 다시 새로운 Active Set을 만들어 OPEN할 수 있게 해줌

 

-- EXECUTE 문을 이용해 함수를 실행합니다.
-- SQL>EXECUTE show_emp3(7900);
CREATE OR REPLACE  PROCEDURE show_emp3 --프로시저 show_emp3을 만들고
(p_empno IN emp.empno%TYPE)
IS   
    --파라미터로 p_empno를 받으면 그것에 관련된 where을 가져옴. 
    -- 1. DECLARE 단계 : is와 begin 사이에서 선언을 함.
    CURSOR emp_cursor IS
    SELECT ename, job, sal
    FROM emp
    WHERE empno Like p_empno||'%';
   
   v_ename   emp.ename%TYPE;
   v_sal     emp.sal%TYPE;
   v_job    emp.job%TYPE;
   
BEGIN
     -- 2) OPEN 단계(실행 단계)
     OPEN emp_cursor; 
        DBMS_OUTPUT.PUT_LINE('이름  ' || '업무 ' || '급여');
        DBMS_OUTPUT.PUT_LINE('----------------------');
    LOOP
       -- 3) FETCH 단계  --> 하나씩 꺼냄 
        --open한 걸 fetch(하나씩 꺼내는 것을 fetch라고 함.)
        --커서가 발견되지 않을때까지(notfound) loop를 계속 돌림
        --한사람 이상일 때 계속해서 돌려줌
       FETCH emp_cursor INTO v_ename, v_job, v_sal;
       EXIT WHEN emp_cursor%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(v_ename||'   '|| v_job||'   '|| v_sal);
    END LOOP; 
    DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT||'개의 행 선택.');
    -- 4) CLOSE 단계
    CLOSE emp_cursor;
END;

실행 결과

 

--문제
-- Fetch 문 
-- SQL> EXECUTE  Cur_sal_Hap (5);
-- CURSOR 문 이용 구현 
-- 부서만큼 반복 
-- 	부서명 : 인사팀
-- 	인원수 : 5
-- 	급여합 : 5000

CREATE OR REPLACE PROCEDURE Cur_sal_Hap 
    (p_deptno in emp.deptno%type)
IS
    CURSOR dept_sum IS  
         SELECT dname, COUNT(*) cnt, SUM(sal) sumSal 
         FROM emp e, Dept d
         WHERE e.deptno= d.deptno 
         AND e.deptno LIKE p_deptno||'%' 
         GROUP BY dname;
    vdname   dept.dname%TYPE;
    vcnt     NUMBER;
    vsumSal  NUMBER;
BEGIN
    DBMS_OUTPUT.ENABLE;
    OPEN   dept_sum;
    LOOP
        -- 3. FETCH 단계
        FETCH           dept_sum INTO vdname , vcnt, vsumSal;
        EXIT WHEN dept_sum%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('부서명 : '||vdname);
        DBMS_OUTPUT.PUT_LINE('인원수 : '||vcnt);
        DBMS_OUTPUT.PUT_LINE('급여합 : '||vsumSal);
    END LOOP;  
    CLOSE  dept_sum;
END Cur_sal_Hap;

 

-- FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 
-- 따로 기술할 필요가 없고, 레코드 이름도 자동선언되므로 따로 선언할 필요가 없다.
-- 실무에서는 FOR문을 많이 사용한다.
CREATE OR REPLACE  PROCEDURE ForCursor_sal_Hap
IS
-- 1. DECLARE 단계 --> Cursor 선언
CURSOR dept_sum IS
              SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary
              FROM emp a, dept b
              WHERE a.deptno = b.deptno
              GROUP BY b.dname;
BEGIN
    DBMS_OUTPUT.ENABLE;
    -- Cursor를 FOR문에서 실행시킨다 --> OPEN, FETCH, CLOSE가 자동 발생
    -- FOR record_name IN cursor_name LOOP (데이터를 읽어와서 저장할 변수도 따로 선언할 필요도 없다.)
    FOR emp_list IN dept_sum LOOP
            DBMS_OUTPUT.PUT_LINE('부서명 : ' || emp_list.dname);
            DBMS_OUTPUT.PUT_LINE('사원수 : ' || emp_list.cnt);
            DBMS_OUTPUT.PUT_LINE('급여합계 : ' || emp_list.salary);
    END LOOP;
    
    EXCEPTION
    WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
END;

 

CREATE PROCEDURE 프로시저명(파라미터, 파라미터타입) IS     CURSOR  커서명 IS     SELECT ~     변수 선언 BEGIN     DBMS_OUTPUT.ENABLE;     OPEN 커서명     LOOP         FETCH 커서명 INTO 변수         EXIT WHEN 커서명%~     END LOOP;     CLOSE 커서명; END 프로시저명 인데, FOR문을 사용하면 CREATE PROCEDURE 프로시저명(파라미터, 파라미터타입) IS     CURSOR  커서명 IS     SELECT ~     변수 선언 BEGIN     DBMS_OUTPUT.ENABLE;     FOR 레코드명 IN 커서명 LOOP     END LOOP; END; 로 간단해진다.

 

--오라클 PL/SQL은 자주 일어나는 몇가지 예외를 미리 정의해 놓았으며, 
--이러한 예외는 개발자가 따로 선언할 필요가 없다.
--미리 정의된 예외의 종류
-- NO_DATA_FOUND : SELECT문이 아무런 데이터 행을 반환하지 못할 때
-- DUP_VAL_ON_INDEX : UNIQUE 제약을 갖는 컬럼에 중복되는 데이터 INSERT 될 때
-- ZERO_DIVIDE : 0으로 나눌 때
-- INVALID_CURSOR : 잘못된 커서 연산
CREATE OR REPLACE  PROCEDURE PreException
(v_deptno IN emp.deptno%TYPE)
IS
  v_emp emp%ROWTYPE;
BEGIN
    DBMS_OUTPUT.ENABLE;
    
    SELECT empno, ename, deptno
    INTO v_emp.empno, v_emp.ename, v_emp.deptno
    FROM emp
    WHERE deptno = v_deptno ;
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || v_emp.empno);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || v_emp.ename);
    DBMS_OUTPUT.PUT_LINE('부서번호 : ' || v_emp.deptno);
    EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
                DBMS_OUTPUT.PUT_LINE('중복 데이터가 존재 합니다.');
                DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생');
       WHEN TOO_MANY_ROWS THEN
                DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS에러 발생');
       WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND에러 발생');
       WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('기타 에러 발생');
END;

Exception 유형 참조하기. 1~5번째까지는 자주 나옴.

 

-- Procedure : in_emp
-- Action : emp Insert
-- 1. error 유형
-- 1) DUP_VAL_ON_INDEX : PreDefiend --> Oracle 선언 Error
-- 2) User Defind Error : lowsal_err(최저급여 ->1500)
CREATE OR REPLACE PROCEDURE in_emp
 (p_name IN emp.ename%TYPE ,   -- 1) DUP_VAL_ON_INDEX
  p_sal IN emp.sal%TYPE ,            -- 2) 개발자 Defind Error :  lowsal_err (최저급여 ->1500)  
  p_job IN emp.job%TYPE
  )
IS
  v_empno emp.empno%TYPE;
  -- 개발자 Defind Error
  lowsal_err EXCEPTION;
BEGIN
   DBMS_OUTPUT.ENABLE;
   SELECT MAX(empno)+1
   INTO   v_empno 
   FROM   emp ;

   IF  p_sal  >=  1500 THEN
      INSERT INTO  emp(empno,ename,sal,job,deptno,hiredate)
      VALUES(v_empno,p_name, p_sal,p_job,10,SYSDATE) ;
  ELSE
      RAISE  lowsal_err ;
  END IF ;

  EXCEPTION
       -- Oracle PreDefined Error
       WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('중복 데이터 ename 존재 합니다.');
            DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생');
       -- 개발자   Defined Error   
       WHEN lowsal_err THEN
            DBMS_OUTPUT.PUT_LINE ('ERROR!!!-지정한 급여가 너무 적습니다. 1500이상으로 다시 입력하세요.') ;
END in_emp;