오라클 프로시저 만들기, 오라클 잡 등록하기

1. PL/SQL이란?

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

- SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,

오라클 자체에 내장되어 있는 Procedure Language 이다.

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

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

2. PL/SQL의 장점

- PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB로 보내서 처리하므로 수행속도를 향상 시킬수 있다.

- PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.

- 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있다.

- VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL문장과 Procedural 문장에서 사용 한다.

- 단순, 복잡한 데이터 형태의 변수를 선언 한다.

- 테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 할 수 있다.

- EXCEPTION 처리 루틴을 이용하여 Oracle Server Error를 처리 한다.

- 사용자 정의 에러를 선언하고 EXCEPTION 처리 루틴으로 처리 가능 하다.

3. 오라클 프로시저 샘플로 만들기

다음은 새직원의 대한 연차를 +1씩 업데이트치는 루프를 프로시저로 만든 예제이다.

CREATE OR REPLACE PROCEDURE INTRANET.JOB_AUTO_VAC_NEW_EMPLOYEE
IS
    --FOR문을 위한 커서 생성
    CURSOR EMP_VAC_CUR IS
     SELECT * FROM(
        SELECT EMP_NO
                 , EMP_NM
                 , VAC_TOTAL_CNT
                 , JOIN_DT
                 , TO_CHAR(TRUNC(A.JOIN_DT, 'DD'),'YYYY') AS JOIN_YEAR
          FROM EMP_EMPLOYEE A
       ) A1
      WHERE A1.JOIN_YEAR = TO_CHAR(TRUNC(SYSDATE, 'DD'),'YYYY');
      
    total_cnt VARCHAR2(10);
BEGIN
    FOR EMP_VAC IN EMP_VAC_CUR LOOP
        
        -- 자신의 연차+1 을 변수에 저장
        total_cnt := EMP_VAC.VAC_TOTAL_CNT + 1;
        
        UPDATE EMP_EMPLOYEE A
              SET VAC_TOTAL_CNT =  total_cnt
         WHERE EMP_NO = EMP_VAC.EMP_NO;
 
    END LOOP;
END;
/
 

1. CREATE OR REPLACE PROCEDURE 사용해서 프로시저를 만들거나 교체한다.

2. CURSOR를 만들고 만든 커서를 VAC_CUR을 가지고 BEGIN 과 END사이 FOR반복을 돌린다.

3. FOR반복을 돌며 EMP(직원)의 현재연차를 가져와 현재연차+1을 하여 변수에 저장한다.

4. UPDATE를 하나씩 쳐주면서 반복한다.

5. 반복이 끝나면 종료한다.

4. 프로시저 실행하기

EXECUTE INTRANET.JOB_AUTO_VAC_NEW_EMPLOYEE

5. 오라클 잡 등록하기

오라클 잡(ORACLE JOB)을 등록하여 원하는날짜와 시간대에 프로시저가 실행될수 있게 스케쥴링 할 수 있다.

toad를 사용한다면 create job을 통해 실행될 날짜를 설정할수 있고 어떤 프로시저를 실행할지 명시할 수 있다.

오라클 잡을 생성하게 되면 다음과 같이 PL/SQL 문이 만들어지고 job에 대한 상세한 내용을 다시 확인할 수 있다. 

내용을 풀이하면 job의 간격은 매월 1일에 실행할 것이고 다음실행 날짜는 2019년 2월 1일에

INTRANET 프로시저를 실행하겠다는 것이다. 

이 job은 2019년 2월 1일에 실행되고 다음 실행날짜는 3월 1일로 설정된다.