오라클의 스케쥴 작업 (Jobs) 및 프로시저(procedure) 살펴보기 - 1

갑자기 통계쪽 데이터가 맞지 않다는 요청이 있어서 살펴 보았습니다. 일단은 검색을 통해 해당 테이블을 사용하는 곳이 있는지를 살펴 보았습니다. 통계를 보여주는 메인 페이지에서 SELECT 하는것 외에는 사용하는 곳이 없네요. 헐.. 대체 UPDATE 혹은 INSERT 를 해주는 곳이 없다면 이 테이블의 데이터는 대체 어디서 만들어 주는 것일까요.. 그래서 오라클에 혹시 예약작업이나 자동화 작업이 되는게 있는지 검색을 해보았습니다. 역시나.. jobs 라는 것이 있네요. 첨알았어요..;

바로 toad 의 schema browser 탭들을 뒤적여 보니 Jobs 가 있었고, 해당하는 테이블의 이름을 딴 스케쥴이 정의되어 있습니다. script를 살펴 보니 procedure 를 호출하도록 되어 있습니다. 이번엔 Proc 탭을 가서 찾아보니 빙고. 이곳에 작업이 정의 되어 있네요. 유레카!

하지만 전 DBA가 아니니 전문적으로 알필요까지는 없겠지만.. 그래도 간단하게 알고 넘어가봐야 겠습니다. 혹시라도 수동으로 동작시킨다던가, 스케쥴 시간을 조정하는 정도는 해야 할지도 모르니까요. 예제를 한두개 만들어 보는정도로 만족하려 합니다.

준비를 위해 테스트 테이블을 만들겠습니다. 년, 월 구분해서 갯수 뽑아내는 정도의 간단한 작업을 하려 합니다.

CREATE TABLE TEST_DATA ( T_YEAR CHAR(4 BYTE), T_MONTH CHAR(2 BYTE) );

Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '5 ');
Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '1 ');
Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '3 ');
Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '5 ');
Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '4 ');
Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '3 ');
Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '2 ');
Insert into TEST_DATA (T_YEAR, T_MONTH) Values ('2012', '1 ');
COMMIT;

CREATE TABLE TEST_STATS ( T_YEAR CHAR(4 BYTE), T_MONTH CHAR(2 BYTE), T_COUNT NUMBER );

우선 프로시저의 뜻 정도는 알고 가야 겠네요..

프로시저(Procedure)란?(http://www.oracleclub.com/lecture/1041)

• 특정 작업을 수행 하는, 이름이 있는 PL/SQL BLOCK 이다.
• 매개 변수를 받을 수 있고, 반복적으로 사용 할 수 있는 BLOCK 이다.
• 보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL BLOCK을 데이터베이스에 저장하기 위해 생성 한다.

PL/SQL 은 그럼 뭘까요..(http://www.oracleclub.com/lecture/1039)

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의 장점

• PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB로 보내서 처리하므로 수행속도를 향상 시킬수 있다.
• PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.
• 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있다.
• VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL문장과 Procedural 문장에서 사용 한다.
• 단순, 복잡한 데이터 형태의 변수를 선언 한다.
• 테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 할 수 있다.
• EXCEPTION 처리 루틴을 이용하여 Oracle Server Error를 처리 한다.
• 사용자 정의 에러를 선언하고 EXCEPTION 처리 루틴으로 처리 가능 하다.

끝이 없네요.. 전문가가 될것도 아니고 여기까지만 보겠습니다.

우선 procedure 를 작성해 보겠습니다.

사용하는 Toad 버전은 8.5.3.2, 오라클은9 입니다.

프로시저 에디터가 열리고 기본 문장들이 뭔가 많이 들어가 있는데요. 필요한 부분만 남기고 지우고 사용하면 되겠습니다.

다음은 최초 작성한 코드입니다.

CREATE OR REPLACE PROCEDURE pc_test_stats IS
tmpVar NUMBER;

BEGIN
   tmpVar := 0;
  
   SELECT COUNT(*) INTO tmpVar
   FROM TEST_DATA
   WHERE t_year = '2012'
   AND t_month = '1';
  
END pc_test_stats;
/

파란색의 부분들은 항상지켜져야 하는 문법입니다. 생성구문으로 시작하고 IS 와 BEGIN 사이에서 변수를 선언합니다. 프로시저를 끝낼때는 항상 '/' 를 넣어줘야합니다. Toad 의 프로시저에디터에서 작성을 마치면 F9 혹은 왼쪽 상단의화살표 버튼을 클릭하시면 컴파일이 실행됩니다. 문법의 오류가 있다면 여기서 에러를 출력하여 보여줍니다.

문제 없이 컴파일이 되었으면 다음으로 넘어가 보겠습니다. 이번엔 테스트용 통계 테이블에 값을 넣는 부분을 추가해 보겠습니다.

CREATE OR REPLACE PROCEDURE pc_test_stats IS

/* 변수가 선언되는 부분입니다. 타입을 지정해 주어야 합니다. IS 와 BEGIN 사이에 위치해야 합니다. */

tmpVar NUMBER;

BEGIN
   tmpVar := 0;
  
   SELECT COUNT(*) INTO tmpVar /* select 를 통해 얻은 결과값이 tmpVar 변수에 들어갑니다. */
   FROM TEST_DATA
   WHERE t_year = '2012'
   AND t_month = '1';
  
   INSERT INTO test_stats VALUES ('2012', '1', tmpVar);
  
   COMMIT;
END pc_test_stats;
/

이번에도 컴파일을 실행해 봅니다. 에러가 없이 통과 했다면 이제 실행을 시켜 봅시다. 토드의 Procs 탭으로 가보면 새로 작성한 프로시저가 목록에 추가되어 있는것을 볼수 있습니다. 선택하고 오른쪽 마우스를 클릭하면 메뉴가 나오는데 execute procedure 를 선택하여 실행합니다.

결과를 확인해 볼까요.

SELECT * FROM TEST_STATS;

정상적으로 작동하는걸 확인했습니다. 그럼 계속해서 이번엔 연별, 월별로 구분하는 작업을 추가해 보기로 하겠습니다.

우선은 루프문이 필요 할것 같습니다. 아마도 소스테이블의 연도와 달을 그룹으로 만든 값을 사용하면 될겠네요. 그럼 루프문은 어떻게 사용하는지 한번 적용해 볼까요.

그전에 DBMS Output 이라는 창을 열어두도록 하겠습니다. 콘솔같은거라고 생각합니다.(전문가가 아니라 저두 모릅니다--;)

View 메뉴에서 DBMS Output 을 선택해 줍시다.

아래와 같은 창이 열립니다.

반복문 (FOR, WHILE, LOOP)

다음은 루프를 적용하여 수정한 프로시져입니다.

CREATE OR REPLACE PROCEDURE pc_test_stats IS
tmpVar NUMBER;
v_year CHAR(4 byte);
v_month CHAR(2 byte);
BEGIN

   FOR year_group IN (SELECT t_year, t_month FROM TEST_DATA GROUP BY t_year, t_month) LOOP
      
       tmpVar := 0;
       v_year := year_group.t_year;
       v_month := year_group.t_month;
      
       SELECT COUNT(*) INTO tmpVar
       FROM TEST_DATA
       WHERE t_year = v_year
       AND t_month = v_month;
      
       DELETE TEST_STATS WHERE t_year = v_year AND t_month = v_month;      
       INSERT INTO test_stats VALUES (v_year, v_month, tmpVar);
      
       DBMS_OUTPUT.PUT_LINE(v_year || ':' || v_month || tmpVar);
   END LOOP;
   COMMIT;
END pc_test_stats;
/

연도와 월을 변수로 받기 위해 두개의 변수를 추가 했습니다. 컬럼명이 t_ 인데 변수를 v_ 로 했더니 눈에 잘 띄지도 않고 구분도 잘 안되서 오타때문에 컴파일 하다 오류가 몇번이나 났네요.. 구분이 잘 되게 사용하도록 합시다. ㅠㅠ

DBMS_OUPPUT.PUT_LINE 으로 표시한 부분이 DBMS Output 창에 출력됩니다. 출력이 되지 않는다면 BEGIN 다음 구문에 DBMS_OUTPUT.ENABLE; 를 입력해 보세요.

소스만 보게되면 그룹을 구성한 데이터를 선택하고 그걸 기반으로 루프를 돕니다. 한개의 row 씩 돌면서 각 변수에 값을 담고 해당하는 작업을 합니다. 기존의 데이터가 있을지 몰라서 DELETE 를 사용해서 먼저 삭제를 수행하도록 했습니다. 역시 컴파일후 프로시저를 실행시킨 후 TEST_STATS 의 데이터를 확인하니 원하는 결과값이 정상적으로 들어가 있음을 확인 할 수 있었습니다.

위의 FOR문외에도 WHILE 문처럼 사용 가능합니다. 무한루프에 빠질 위험이 큰 문장은 별로 사용하질 않는 주의라...

LOOP  <- 세미콜론이 들어가지 않습니다.

EXIT; /* 바로 빠져나갑니다. */

EXIT WHEN index > 30; /* 조건문에 만족하는 경우 순환문을 빠져나갑니다. */

END LOOP;

혹은 WHILE index < 30 LOOP 의 형태로도 사용 가능합니다. while 문의 조건이 true 인동안 동작합니다. 이하 구문은 바로위의 예제와 같습니다.

조건문(IF)

간단하게 알아보려 했는데 글이 계속 길어 집니다. 다음은 IF 문입니다. 사용법만 적고 예제는 만들지 않습니다.

IF 조건문 THEN

ELSEIF 조건문 THEN

ELSE

END IF;  <- 여기에만 세미콜론.

암시적커서(Implicit Cursor)

PL/SQL 내에서 SQL문이 실행되면 자동으로 커서가 생성되며, 그 속성을 사용할 수 있습니다.

BEGIN

/* SQL 이 시작되기전 커서가 자동으로 OPEN 됩니다. */

SELECT * FROM TEST_DATA;

/* SQL 이 종료 되면 커서가 자동으로 CLOSE 됩니다. */

/* 새로운 SQL 이 시작되기전 위 SQL에 대한 암시적 커서의 속성을 사용할 수 있습니다.

SQL%FOUND : 위 SQL 의 결과가 한개 이상 있는경우 true

SQL%NOTFOUND : 결과가 없는 경우 ture

SQL%ROWCOUNT : 결과 행의 수

END;

암시적 커서와 반대로 명시적커서(Explicit Cursor) 가 있는데요. 너무 깊이 가는거 같아서..-_-; 보지 않았습니다.

여기까지면 원하는 작업은 모두 마친것 같습니다.

다음은 스케쥴에 작성한 프로시저를 등록시켜 보겠습니다.

2012/12/13 - [분류 전체보기] - 오라클의 스케쥴 작업 (Jobs) 및 프로시저(procedure) 살펴보기