[자바] 30일차 - PL/SQL 의 기초, 자바 JDBC 연동

[참고] 오라클클럽 > 오라클강좌 > 오라클 PL/SQL 강좌

 

2012/09/10 - [Dev. 자바/참고소스] - [자바 소스] PL/SQL 자바에서 이용하기 예제 소스

 

 

 

 

 

 

 

 

 

 

PL/SQL이란?

- PL/SQL 은 Oracle’s Procedural Language extension to SQL 의 약자 이다.

- SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,오라클 자체에 내장되어 있는 Procedure Language 이다.

- DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항 이다.

- PL/SQL 문은 블록 구조로 되어 있고 PL/SQL자신이 컴파일 엔진을 가지고 있다.

 

PL/SQL Block Structure

  PL/SQL은 프로그램을 논리적인 블록으로 나누는 구조화 된 블록 언어 이다.

  PL/SQL 블록은 선언부(선택적), 실행부(필수적), 예외 처리부(선택적)로 구성되어 있고, BEGIN과 END 키워드는 반드시 기술해 주어야 한다.

DECLARE - Optional - Variables, cursors, user-defined exceptions BEGIN - Mandatory - SQL Statements - PL/SQL Statements EXCEPTION - Actions to perform when errors occur END - Mandatory

 

PL/SQL 프로그램의 작성 요령

- PL/SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용 한다.

- END뒤에 세미콜론(;)을 사용하여 하나의 블록이 끝났다는 것을 명시 한다.

- PL/SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고, SQL 프롬프트에서 바로 작성 할 수도 있다.

- SQL*PLUS 환경에서는 DECLARE나 BEGIN이라는 키워드로 PL/SQL블럭이 시작하는 것을 알 수 있다.

- 단일행 주석 : --

- 여러행 주석 : /* */

- PL/SQL 블록은 행에 / 가 있으면 종결 된다.

 

PL/SQL 블럭의 유형

[ Anonymous ]	[ Procedure ]	[ Function ]

Anonymous Block (익명 블록)

  이름이 없는 블록을 의미 하며, 실행하기 위해 프로그램 안에서 선언 되고 실행시에 실행을 위해 PL/SQL 엔진으로 전달 된다.

  선행 컴파일러 프로그램과 SQL*Plus 또는 서버 관리자에서 익명의 블록을 내장 할 수 있다.

Procedure (프로시저)

  특정 작업을 수행할수 있는 이름이 있는 PL/SQL 블록으로서, 매개 변수를 받을수 있고, 반복적으로 사용할수 있다.

  보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 데이터베이스에 저장하기 위해 생성 한다.

프로시저 문법

 
CREATE OR REPLACE procedure name 
   IN argument 
   OUT argument 
   IN OUT argument 

IS 

   [변수의 선언]

BEGIN  --> 필수 

   [PL/SQL Block] 
   -- SQL문장, PL/SQL제어 문장 

   [EXCEPTION]  --> 선택
  -- error가 발생할 때 수행하는 문장

END;  --> 필수 
    

• - CREATE OR REPLACE 구문을 사용하여 생성 한다.
• - IS 로 PL/SQL의 블록을 시작 한다.
• - LOCAL 변수는 IS 와 BEGIN 사이에 선언 한다.

프로시저 작성 예제

-- 프로시저의 이름은 update_sal이다 
-- update_sal 프로시저는 사번을 입력받아 급여를 인상 한다. 
-- 프로시저를 끝마칠 때에는 항상 "/"를 지정 한다.
SQL> CREATE OR REPLACE PROCEDURE update_sal 
     /* IN  Parameter */
     (v_empno    IN    NUMBER) 
         
     IS 

     BEGIN 

       UPDATE emp 
       SET sal = sal  * 1.1 
       WHERE empno = v_empno; 

       COMMIT; 

     END update_sal; 
     /     
    

프로시저 실행 예제

EXECUTE 문을 이용해 프로시저를 실행 한다.

SQL> EXECUTE update_sal(7369);
PL/SQL 처리가 정상적으로 완료되었습니다.

-- 7369번 사원의 급여가 10% 인상 되었는지 확인해 보기 바란다.
    

 

 

Function (함수)

  보통 값을 계산하고 결과 값을 반환하기 위해서 함수를 많이 사용 한다.

  대부분 구성이 프로시저와 유사 하지만 IN 파라미터만 사용 할 수 있다.

  반드시 반환 될 값의 데이터 타입을 RETURN문에 선언해야 한다.

  또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 한다.

 

함수 문법

 
-- PL/SQL 블록에는 적어도 한 개의 RETURN 문이 있어야 한다. 
-- PL/SQL Block은 함수가 수행 할 내용을 정의한 몸체부분이다.    
CREATE OR REPLACE FUNCTION function name 
  [(argument...)] 
  
  RETURN datatype
    -- datatype은 반환되는 값의 datatype입니다. 

IS 

  [변수 선언 부분]

BEGIN

  [PL/SQL Block]
   
  -- 리턴문이 꼭 존재해야 합니다
  RETURN 변수; 
        
END;     
    

함수 작성 예제

SQL> CREATE OR REPLACE FUNCTION FC_update_sal
     (v_empno         IN    NUMBER)

      -- 리턴되는 변수의 데이터타입을 꼭 정의해야 합니다
      RETURN  NUMBER

    IS
    
    -- %type 변수가 사용(스칼라 데이터 타입 참고)
    v_sal  emp.sal%type;

    BEGIN

    UPDATE emp
    SET sal  = sal  * 1.1
    WHERE empno  = v_empno;

    COMMIT;

    SELECT sal
    INTO v_sal
    FROM emp
    WHERE empno = v_empno;

    -- 리턴문이 꼭 존재해야 합니다
    RETURN v_sal;

   END;   
   /   
    

함수 실행 예제

-- 먼저 함수의 반환값을 저장 할 변수 선언. 
SQL> VAR salary NUMBER; 

-- EXECUTE 문을 이용해 함수를 실행합니다. 
SQL>EXECUTE :salary := FC_update_sal(7900); 

-- PRINT문을 사용하여 출력 
SQL>PRINT salary;
 
    SALARY
----------
      1045
    
-- 아래와 같이 SELECT문장에서도 사용 할 수 있다. 
SQL> SELECT ename, FC_update_sal(sal) FROM emp;
    

 

 

FOR LOOP 문법

• - index 는 자동 선언되는 binary_integer형 변수이고, 1씩 증가 한다.
• - reverse 옵션이 사용 될 경우 index 는 upper_bound에서 lower_bound로 1씩 감소 한다.
• - IN 다음에는 coursor나 select 문이 올 수 있다.

FOR LOOP 예제

 
    DECLARE

    -- 사원 이름을 출력하기 위한 PL/SQL 테이블 선언 
    TYPE ename_table IS TABLE OF emp.ename%TYPE
    INDEX BY BINARY_INTEGER;
	
    -- 사원 급여를 출력하기 위한 PL/SQL 테이블 선언 
    TYPE sal_table IS TABLE OF emp.sal%TYPE
    INDEX BY BINARY_INTEGER;
	
    ename_tab    ename_table;
    sal_tab      sal_table;
	
    i BINARY_INTEGER := 0;

BEGIN

    DBMS_OUTPUT.ENABLE;
	
    FOR emp_list IN  (SELECT ename, sal 
	                  FROM emp 
	                  WHERE deptno = 10) LOOP
	
        i := i +1 ;
	
        -- 테이블에 상품 이름을 저장
        ename_tab(i) := emp_list.ename;
	        
        -- 테이블에 상품 가격을 저장 
        sal_tab(i)   := emp_list.sal;        
	
    END LOOP;
	
    --  화면에 출력
    FOR cnt IN  1..i   LOOP  
	
        DBMS_OUTPUT.PUT_LINE('사원이름 : ' || ename_tab(cnt)); 
        DBMS_OUTPUT.PUT_LINE('사원급여 : ' || sal_tab(cnt));
	
    END LOOP;

END;
/ 

사원이름 : CLARK
사원급여 : 2450
사원이름 : KING
사원급여 : 5000
사원이름 : MILLER
사원급여 : 1300

        

 

 

LOOP문 문법

• - EXIT 문이 사용되었을 경우, 무조건 LOOP문을 빠져나간다.
• - EXITH WHEN 이 사용될 경우 WHEN 절에 LOOP를 빠져 나가는 조건을 제어 할 수 있다.

 

 

LOOP문 예제

 
-- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON ;  

SQL> DECLARE

        v_cnt NUMBER := 100;

     BEGIN

        DBMS_OUTPUT.ENABLE ;

        LOOP
            INSERT INTO emp(empno, ename , hiredate)
            VALUES(v_cnt, 'test'||to_char(v_cnt),  sysdate);

            v_cnt := v_cnt+1;

            EXIT WHEN v_cnt > 110;

        END LOOP;

        DBMS_OUTPUT.PUT_LINE('데이터 입력 완료');
        DBMS_OUTPUT.PUT_LINE(v_cnt-100 || '개의 데이터가 입력되었습니다');

     END;           
     /

데이터 입력 완료
11개의 데이터가 입력되었습니다 
 
        

 

 

WHILE LOOP문 예제

WHILE LOOP문은 FOR문과 비슷하며, 조건이 TRUE일 경우 만 반복되는 LOOP문 이다.

 
SQL> DECLARE

       v_cnt number := 100; 
	
     BEGIN
	
       DBMS_OUTPUT.ENABLE; 
	
       WHILE v_cnt < 110 LOOP 
	
           INSERT INTO emp(empno, ename , hiredate) 
           VALUES(emp_seq.nextval, 'test', sysdate); 
	
           v_cnt := v_cnt+1; 
	
           EXIT WHEN v_cnt > 110; 
	
       END LOOP; 
	
       DBMS_OUTPUT.PUT_LINE('데이터 입력 완료'); 
       DBMS_OUTPUT.PUT_LINE(v_cnt-100 || '개의 데이터가 입력되었습니다'); 
		
    END; 
    / 
        

 

 

 

 

커서란 무엇인가?

• - 커서는 Private SQL의 작업 영역이다.
• - 오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서를 소유하고 있다.
• - 커서의 종류
  • 암시적 커서 : 모든 DML과 PL/SQL SELECT문에 대해 선언된다.
  • 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서이다.

Explicit Cursor의 흐름도?

문법(Syntax)

 

FOR문에서 커서 사용 문법

 FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 따로 기술할 필요가 없고, 레코드 이름도 자동 선언되므로 따로 선언할 필요가 없다.

 

 

FOR문에서 커서 사용 예제

 
SQL> CREATE OR REPLACE PROCEDURE ForCursor_Test
     IS

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

       -- Cursor를 FOR문에서 실행시킨다
       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;
   /

-- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON ; 

-- 실행
SQL> EXECUTE ForCursor_Test;
부서명 : ACCOUNTING
사원수 : 3
급여합계 : 8750
부서명 : RESEARCH
사원수 : 6
급여합계 : 10875
부서명 : SALES
사원수 : 6
급여합계 : 9305
    

 

 

 

예외(Exception)란?

  오라클 PL/SQL의 오류를 예외라고 한다.

  오류는 PL/SQL을 컴파일 할때 문법적인 오류로 발생하는 컴파일 타임 오류와, 프로그램을 실행 할 때 발생하는 실행타임 오류로 구분 할 수 있다.

 

예외처리 문법

• - 예외 처리절은 EXCEPTION부터 시작 한다.
• - 예외가 발생하면 여러 개의 예외 처리부 중에 하나의 예외 처리부에 트랩(Trap) 된다.
• - WHEN OTHERS절은 맨 마지막 온다.

 

미리 정의된 예외의 종류

  - NO_DATA_FOUND : SELECT문이 아무런 데이터 행을 반환하지 못할 때

  - DUP_VAL_ON_INDEX : UNIQUE 제약을 갖는 컬럼에 중복되는 데이터가 INSERT 될 때

  - ZERO_DIVIDE : 0으로 나눌 때

  - INVALID_CURSOR : 잘못된 커서 연산

  - Predefined PL/SQL Exceptions 더 보기

 

 

사용자 정의 예외 예제

오라클 저장함수 RAISE_APPLICATION_ERROR를 사용하여 오류코드 -20000부터 -20999의 범위 내에서 사용자 정의 예외를 만들수 있다.

  - STEP 1 : 예외의 이름을 선언 (선언절)

  - STEP 2 : RAISE문을 사용하여 직접적으로 예외를 발생시킨다(실행절)

  - STEP 3 : 예외가 발생할 경우 해당 예외를 참조한다(예외절)

 

 

사용자 정의 예외 예제

 
-- 입력한 부서의 사원이 5명보다 적으면 사용자 정의 예외가 발생하는 예제
SQL> CREATE OR REPLACE PROCEDURE User_Exception 
        (v_deptno IN emp.deptno%type ) 
     IS

       -- 예외의 이름을 선언
       user_define_error EXCEPTION;     -- STEP 1
       cnt     NUMBER;

     BEGIN

       DBMS_OUTPUT.ENABLE;   

       SELECT COUNT(empno) 
       INTO cnt
       FROM emp
       WHERE deptno = v_deptno;

       IF cnt < 5 THEN
         -- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
          RAISE user_define_error;         -- STEP 2
       END IF;

      EXCEPTION
        -- 예외가 발생할 경우 해당 예외를 참조한다.
       WHEN user_define_error THEN      -- STEP 3
           RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..');

   END; 
  /

-- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용 
SQL> SET SERVEROUTPUT ON ; 

-- 10부서의 사원이 5보다 적기 때문에 사용자 정의 예외가 발생.
SQL> EXECUTE user_exception(10);
BEGIN user_exception(10); END;
 *
1행에 오류:
ORA-20001: 부서에 사원이 몇명 안되네요..
ORA-06512: "SCOTT.USER_EXCEPTION", 줄 17에서
ORA-06512: 줄 1에서

-- 20부서로 실행을 하면 에러가 발생하지 않는 것 을 알 수 있다.
SQL> EXECUTE user_exception(20);
PL/SQL 처리가 정상적으로 완료되었습니다.
    

 

 

 

패키지 선언절 문법

• - 선언절은 패키지에 포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외절을 선언 한다.
• - 패키지 선언부에서 선언한 모든 요소들은 패키지 전체에 적용 된다.
• - 즉 선언부에서 선언한 변수는 PUBLIC 변수로 사용 된다.

패키지 본문 문법

• - 패키지 본문은 패키지에서 선언된 부분의 실행을 정의 한다.
• - 즉 실재 프로시져나 함수의 내용에 해당하는 부분이 온다.

 

 

아래는 네 개의 프로시저를 하나의 패키지로 생성하는 아주 간단한 예제이다.

프로시저명	프로시저 기능
all_emp_info	모든 사원의 사원 정보 (사번, 성명, 입사일)
all_sal_info	모든 사원의 급여 정보 (평균급여, 최고급여, 최소급여)
dept_emp_info	특정 부서의 사원 정보 (사번, 성명, 입사일)
dept_sql_info	특정 부서의 급여 정보 (평균급여, 최고급여, 최소급여)

패키지 선언부 생성 예제

 
SQL> CREATE OR REPLACE PACKAGE emp_info AS

       PROCEDURE all_emp_info;   -- 모든 사원의  사원 정보
        
        PROCEDURE all_sal_info;   -- 모든 사원의  급여 정보

        -- 특정 부서의  사원 정보
        PROCEDURE dept_emp_info (v_deptno IN  NUMBER) ;

        -- 특정 부서의  급여 정보
        PROCEDURE dept_sal_info (v_deptno IN  NUMBER) ;

    END emp_info;
    /
    

패키지 본문 생성 예제

 
SQL> CREATE OR REPLACE PACKAGE BODY emp_info AS

     -- 모든 사원의  사원 정보 
     PROCEDURE all_emp_info
     IS

         CURSOR emp_cursor IS
         SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
         FROM emp
         ORDER BY hiredate;

     BEGIN

         FOR  aa  IN emp_cursor LOOP

             DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
             DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
             DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);

         END LOOP;

         EXCEPTION
           WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

     END all_emp_info;


     -- 모든 사원의  급여 정보 
     PROCEDURE all_sal_info
     IS
    
         CURSOR emp_cursor IS
         SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
         FROM emp;
    
     BEGIN

         FOR  aa  IN emp_cursor LOOP
 
             DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
             DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
             DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
         
         END LOOP;


         EXCEPTION
            WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
     END all_sal_info;


     --특정 부서의  사원 정보
     PROCEDURE dept_emp_info (v_deptno IN  NUMBER)
     IS

         CURSOR emp_cursor IS
         SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
         FROM emp
         WHERE deptno = v_deptno
         ORDER BY hiredate;

     BEGIN

         FOR  aa  IN emp_cursor LOOP

             DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
             DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
             DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);

         END LOOP;

        EXCEPTION
            WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

     END dept_emp_info;


     --특정 부서의  급여 정보
     PROCEDURE dept_sal_info (v_deptno IN  NUMBER)
     IS
    
         CURSOR emp_cursor IS
         SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
         FROM emp 
         WHERE deptno = v_deptno;
             
     BEGIN

         FOR  aa  IN emp_cursor LOOP 
 
             DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
             DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
             DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
         
         END LOOP;

         EXCEPTION
             WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');

     END dept_sal_info;        
    
  END emp_info;
  /
    

패키지 실행

패키지의 실행은 패키지 명 다음에 점(.)을 찍고 프로시저냐 함수 명을 적어주면 된다.

 
-- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> SET SERVEROUTPUT ON ; 

-- 패키지 실행
SQL> EXEC emp_info.all_emp_info;

SQL> EXEC emp_info.all_sal_info;

SQL> EXEC emp_info.dept_emp_info(10);

SQL> EXEC emp_info.dept_sal_info(10); 
    

 

 

 

트리거란?

  INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때 묵시적으로 수행되는 PROCEDURE 이다.

  트리거는 TABLE과는 별도로 DATABASE에 저장 된다.

  트리거는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의 될 수 있다.

  행 트리거 : 컬럼의 각각의 행의 데이터 행 변화가 생길때마다 실행되며, 그 데이터 행의 실제값을 제어할 수 있다.

  문장 트리거 : 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어 할 수 없다.

트리거 문법

• - BEFORE : INSERT, UPDATE, DELETE문이 실행되기 전에 트리거가 실행 된다.
• - AFTER : INSERT, UPDATE, DELETE문이 실행된 후 트리거가 실행 된다.
• - trigger_event : INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있다.
• - FOR EACH ROW : 이 옵션이 있으면 행 트리거가 된다.

 

간단한 트리거 예제 1

 
SQL> CREATE OR REPLACE TRIGGER triger_test
       BEFORE
       UPDATE ON dept
       FOR EACH ROW
	   
	   BEGIN
        DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' || : old.dname);
        DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' || : new.dname);
     END;
     /

-- DBMS_OUTPUT.PUT_LINE을 출력
SQL> SET SERVEROUTPUT ON ; 

-- UPDATE문을 실행시키면.. 
SQL> UPDATE dept SET dname = '총무부' WHERE deptno = 30

-- 트리거가 자동 실행되어 결과가 출력된다. 
변경 전 컬럼 값 : 인사과
변경 후 컬럼 값 : 총무부

1 행이 갱신되었습니다.
    

간단한 트리거 예제 2

 
SQL> CREATE OR REPLACE TRIGGER sum_trigger
       BEFORE
       INSERT OR UPDATE ON emp
       FOR EACH ROW

    DECLARE
	
        -- 변수를 선언할 때는 DECLARE문을 사용해야 한다 
        avg_sal NUMBER;
	
    BEGIN
	
        SELECT ROUND(AVG(sal),3)
        INTO avg_sal
        FROM emp;

        DBMS_OUTPUT.PUT_LINE('급여 평균 : ' || avg_sal);
	
    END; 
    /

-- DBMS_OUTPUT.PUT_LINE을 출력
SQL> SET SERVEROUTPUT ON ;

-- INSERT문을 실행해보자.
SQL> INSERT INTO EMP(EMPNO, ENAME, JOB, HIREDATE, SAL)
     VALUES(1000, 'LION', 'SALES', SYSDATE, 5000);

-- INSERT문을 실행되기 전까지의 급여 평균이 출력된다. 
급여 평균 : 2073.214

1 개의 행이 만들어졌습니다.