'Database'에 해당되는 글 25건

이벤트에 참여해서 Altibase HDB 완벽가이드 책을 받았는데(2012/01/26)

이벤트 기한은 2012/01/27까지 란다....헐~~ 오늘 책을 받았는데

회사일도 해야되고 어쩌나.....~~~ㅎㅎ

시간도 짧고 해서 기능들을 많이는 테스트 해보지 못했습니다.

잛은시간에 디비의 성능을 간단히 테스트 해보고자

다른 디비들과 백만건 정도 되는 데이터에 단순 SELECT 쿼리만 날려 보았습니다.

의외로 국산디비 성능이 다른 외산 디비에 비해 괜찮은 속도를 낸것같아..~~

앞으로 개발사에서 공격적인 마케팅과 서비스 지원이 이루어 진다면

충분이 시장에서 경쟁력이 있지 않을까라는 개인적인 생각을 해봅니다.

단순 SELECT 쿼리만 테스트 했고 테스트한 모든 디비는 같은 O/S, H/W에

테스트 해보았고, 모든 설치환경은 디폴트이고 따로 튜닝은 하지 않았습니다.
(MySQL은 속도가 안나와서 my.cnf점 수정함).
(elapsed time은 따로 기록하지 않았습니다. 간단한 리뷰를 쓰기위해  결과만 확인했으니 필요하다면 직접 해보시길..)

## H/W
CPU : i7-950/3.06Ghz/Quad Core
MEM : DDR3 SDRAM 2Gb*6(12Gb)
HDD : SATA3(7200rpm/64M)

## O/S
CentOS 5.6 64bit(Linux XXXX 2.6.18-238.12.1.el5xen #1 SMP Tue May 31 14:02:29 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux)

의외인것은 Altibase, CUBRID 반응속도가 다른 DB에 비해 상당히 빨랐음
그룹처리   : ORACLE(상용), CUBRID(오픈, 국산), Altibase(국산), PostgreSQL(오픈) 속도가 MySQL보다 빨랐음(1~2초 내외)
페이징처리 : ORACLE(상용), Altibase(국산), CUBRID(오픈, 국산) 상대적으로 빨랐음
             PostgreSQL, MySQL(MariaDB ??)은 상댁적으로 느렸음

FirebirdSQL(오픈, 구 InterBase) 시간이 없어 테스트 못함 ㅎㅎㅎ

일반 개인 및 공개 사이트 개발은 MySQL(MariaDB ??) 보단 CUBRID

상용 사이트 Oracle 디비를 마이그레이션 하거나 국산 디비를 적용해야되는 정부기관에는 Altibase

Altibase의 장점은 Oracle의 쿼리와 비슷하여 마이그레이션 하는데 다른 디비에
비하여 괜찮은 장점인것 같습니다.

Altibase를 좀더 사용해바야 알겠지만 단순 설치와 쿼리/인덱스/힌트/테이블스페이스등 구문이나 실행방법이
오라클 클론인것 같습니다.
PostgreSQL의 상용버전인 http://www.enterprisedb.com/ (Oracle 구문 동일 또는 유사함)와도 비슷한것 같기도하고
아무튼 이점 넘 마음에 듭니다.ㅎㅎ

Altibase(페이징속도 빠름) = CUBRID(그룹핑 속도 빠름) >= ORACLE > MySQL(MyISAM) >= PostgreSQL >= MySQL(InnoDB)

리뷰가 넘 허접하네...ㅎㅎ

Altibase 설치(다른 디비는 시간되면~~~올리도록 하겠습니다. 시간이 될라나..ㅋㅋ)
( http://atc.altibase.com/download/install_guide.jsp  참조)

http://atc.altibase.com/
해당 OS에 설치할 제품을 다운로드

gcc 버전확인
#gcc -v
Target: x86_64-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-libgcj-multifile --enable-languages=c,c++,objc,obj-c++,java,fortran,ada --enable-java-awt=gtk --disable-dssi --disable-plugin --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre --with-cpu=generic --host=x86_64-redhat-linux
Thread model: posix
gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)
 
해당 gcc버전을 확인하고 버전에 맞는 패키지를 다운받는다.
설치용 altibase-XEON_LINUX_redhat_Enterprise_release5-64bit-5.3.3.12-release-GCC4.1.2.tgz
패치용 altibase-server-5.5.1.2-LINUX-X86-64bit-patch_0_0_2_5.run

계정생성
#useradd -G dba -g dba altibase

계정변경
#su - altibase

환경변수 설정(추가함, 패치 적용하고 삭제함)
$vi .bash_profile
ALTIBASE_HOME=$HOME/altibase_home; export ALTIBASE_HOME
PATH=$ALTIBASE_HOME/bin:$PATH ;export PATH
LD_LIBRARY_PATH=$ALTIBASE_HOME/lib:$LD_LIBRARY_PATH ;export LD_LIBRARY_PATH

패키지 압축풀기
$unzip altibase-XEON_LINUX_redhat_Enterprise_release5-64bit-5.3.3.12-release-GCC4.1.2.tgz

패키지 설치(디폴트 적용)
$./altibase_install.sh

license적용(복사함, http://atc.altibase.com/download/license_guide.jsp 신청후 적용)
$ALTIBASE_HOME/conf/license

패치파일 적용(디폴트 적용)
$chmod 755 altibase-server-5.5.1.2-LINUX-X86-64bit-patch_0_0_2_5.run
$./altibase-server-5.5.1.2-LINUX-X86-64bit-patch_0_0_2_5.run

환경변수 설정
$vi .bash_profile
삭제함 ALTIBASE_HOME=$HOME/altibase_home; export ALTIBASE_HOME
삭제함 PATH=$ALTIBASE_HOME/bin:$PATH ;export PATH
삭제함 LD_LIBRARY_PATH=$ALTIBASE_HOME/lib:$LD_LIBRARY_PATH ;export LD_LIBRARY_PATH

패치적용하고 추가된부분
. /home/altibase/altibase_home/conf/altibase_user.env

환경파일 적용
$source /home/altibase/.bash_profile

서버 시작(종료:$server stop)
$server start

시스템 계정 로그인
$isql -u sys -p manager -sysdba

테스트용 테이블스페이스 생성
iSQL(sysdba)>
    CREATE TABLESPACE "TS_TEST"
    DATAFILE '/home/altibase/altibase_home/dbs/ts_test.dbf'
    SIZE 100M
    AUTOEXTEND ON NEXT 100M
    MAXSIZE UNLIMITED;
   
iSQL(sysdba)>
    CREATE MEMORY TABLESPACE "TS_TEST_MEM"
    SIZE 131072K
    AUTOEXTEND ON NEXT 131072K
    MAXSIZE UNLIMITED
    CHECKPOINT PATH '/home/altibase/altibase_home/dbs'
    SPLIT EACH 1048576K;

테스트용 계정 생성
iSQL(sysdba)>
    CREATE USER test IDENTIFIED BY test
    TEMPORARY TABLESPACE "SYS_TBS_DISK_TEMP"
    DEFAULT TABLESPACE "TS_TEST"
    ACCESS "TS_TEST_MEM" ON;
   

데이터 건수 : 1,118,003
#################################### Altibase 5.6.1 ####################################
-- Altibase
-- @/path/USE_DETAIL.SQL;
-- $isql -u username -p passwd -f /path/USE_DETAIL.SQL
CREATE TABLE USE_DETAIL
(
     ud_seq             NUMBER(20,0)                NOT NULL
    ,rc_seq             VARCHAR2(10)
    ,ma_seq             VARCHAR2(20)
    ,fd_seq             NUMBER(20,0)
    ,ui_id              VARCHAR2(20)
    ,tm_seq             VARCHAR2(10)                NOT NULL
    ,ud_type            CHAR(1)         DEFAULT 'G' NOT NULL
    ,ud_weight          NUMBER(10,3)
    ,ud_usedate         VARCHAR2(20)    DEFAULT ''  NOT NULL
    ,ud_dayofweek       NUMBER(2,0)
    ,ud_cdate           VARCHAR2(20)
    ,ud_useunixtime     NUMBER(15,0)
    ,CONSTRAINT PK_USE_DETAIL PRIMARY KEY(ud_seq)
);

CREATE UNIQUE INDEX PK_USE_DETAIL ON USE_DETAIL(ud_seq);
CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);

접    속 : isql -u username -p passwd
실행시간 : iSQL> set timing on;
종    료 : iSQL> exit;

그룹핑 테스트 SQL :
       
        SELECT  X1.ud_seq, X1.ma_seq, X1.tm_seq
        FROM    USE_DETAIL X1,
        (
            SELECT  MAX(ud_seq) AS ud_seq, ma_seq, tm_seq
            FROM    USE_DETAIL X
            WHERE   ud_usedate BETWEEN '2011-07-01' AND '2011-08-01'
            AND     tm_seq  IN('00101','00102','00103','00104','00105','00106','00107','00108')
            AND     ud_type = 'G'
            GROUP   BY ma_seq, tm_seq
        ) X2
        WHERE X1.ud_seq = X2.ud_seq;
       
페이징 테스트 SQL :
       
        SELECT  *
        FROM
        (
            SELECT  ROWNUM rnum, X1.*
            FROM
            (
                SELECT   /*+ INDEX_DESC(X pk_use_detail) */ ud_seq, ma_seq, tm_seq, ud_usedate
                FROM    USE_DETAIL X
                WHERE   ud_type    = 'G'
            ) X1
            WHERE ROWNUM  <= 900051
        )
        WHERE   rnum BETWEEN 900000 AND 900050;
또는
        SELECT * FROM use_detail LIMIT 900000, 50;

인덱스 생성방벙 :
    DROP INDEX IDX_01;
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);
    OR
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate DESC, tm_seq, ud_type);
   
   
  
데이터 건수 : 1,118,003
#################################### MySQL 5.1.4 ###################################
-- MySQL(MariaDB ??)
-- mysql>\. /path/USE_DETAIL.SQL;
-- #>mysql -uusername -p dbname < /path/USE_DETAIL.SQL
CREATE TABLE USE_DETAIL
(
     ud_seq             BIGINT(20)                  NOT NULL
    ,rc_seq             VARCHAR(10)
    ,ma_seq             VARCHAR(20)
    ,fd_seq             BIGINT(20)
    ,ui_id              VARCHAR(20)
    ,tm_seq             VARCHAR(10)                 NOT NULL
    ,ud_type            CHAR(1)                     NOT NULL DEFAULT 'G'
    ,ud_weight          DOUBLE(10,3)
    ,ud_usedate         VARCHAR(20)                 NOT NULL DEFAULT ''
    ,ud_dayofweek       TINYINT(2)
    ,ud_cdate           VARCHAR(20)
    ,ud_useunixtime     INT(15)
    ,PRIMARY KEY(ud_seq)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE UNIQUE INDEX PK_USE_DETAILON USE_DETAIL(ud_seq);
CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);

접속 : mysql -uusername -ppasswd
디비 : mysql> use dbname;
종료 : mysql> exit; or \q

그룹핑 테스트 SQL :

        SELECT  X1.ud_seq, X1.ma_seq, X1.tm_seq
        FROM    USE_DETAIL X1,
        (
            SELECT  MAX(ud_seq) AS ud_seq, ma_seq, tm_seq
            FROM    USE_DETAIL X USE INDEX FOR GROUP BY(IDX_01)
            WHERE   ud_usedate BETWEEN '2011-07-01' AND '2011-08-01'
            AND     tm_seq  IN('00101','00102','00103','00104','00105','00106','00107','00108')
            AND     ud_type = 'G'
            GROUP   BY ma_seq, tm_seq
        ) X2
        WHERE X1.ud_seq = X2.ud_seq;

페이징 테스트 SQL :

        SELECT  ud_seq, ma_seq, tm_seq, ud_usedate
        FROM    USE_DETAIL X FORCE INDEX(IDX_01)
        WHERE   ud_type = 'G'
        ORDER   BY ud_seq DESC
        LIMIT   900000, 50;

인덱스 생성방벙 : 쿼리문에 흰트사용함, my.cnf도 버퍼용량 증가시킴
    ALTER TABLE USE_DETAIL DROP INDEX IDX_01;
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);

기타 : MyISAM, InnoDB Engine 둘다 테스트함
 

데이터 건수 : 1,118,003
#################################### ORACLE 10g ####################################
-- ORACLE
-- SQL>@/path/USE_DETAIL.SQL;
-- $sqlplus username/passwd @/path/USE_DETAIL.SQL
CREATE TABLE USE_DETAIL
(
     ud_seq             NUMBER(20,0)                NOT NULL
    ,rc_seq             VARCHAR2(10)
    ,ma_seq             VARCHAR2(20)
    ,fd_seq             NUMBER(20,0)
    ,ui_id              VARCHAR2(20)
    ,tm_seq             VARCHAR2(10)                NOT NULL
    ,ud_type            CHAR(1)         DEFAULT 'G' NOT NULL
    ,ud_weight          NUMBER(10,3)
    ,ud_usedate         VARCHAR2(20)    DEFAULT ''  NOT NULL
    ,ud_dayofweek       NUMBER(2,0)
    ,ud_cdate           VARCHAR2(20)
    ,ud_useunixtime     NUMBER(15,0)
    ,CONSTRAINT PK_USE_DETAIL PRIMARY KEY(ud_seq)
);

CREATE UNIQUE INDEX PK_USE_DETAIL ON USE_DETAIL(ud_seq);
CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);

접    속 : sqlplus username/passwd
실행시간 : SQL> set timing on
종    료 : SQL> exit;

그룹핑 테스트 SQL :
       
        SELECT  X1.ud_seq, X1.ma_seq, X1.tm_seq
        FROM    USE_DETAIL X1,
        (
            SELECT  MAX(ud_seq) AS ud_seq, ma_seq, tm_seq
            FROM    USE_DETAIL X
            WHERE   ud_usedate BETWEEN '2011-07-01' AND '2011-08-01'
            AND     tm_seq  IN('00101','00102','00103','00104','00105','00106','00107','00108')
            AND     ud_type = 'G'
            GROUP   BY ma_seq, tm_seq
        ) X2
        WHERE X1.ud_seq = X2.ud_seq;
       
페이징 테스트 SQL :
       
        SELECT  *
        FROM
        (
            SELECT  ROWNUM rnum, X1.*
            FROM
            (
                SELECT   /*+ INDEX_DESC(X pk_use_detail) */ ud_seq, ma_seq, tm_seq, ud_usedate
                FROM    USE_DETAIL X
                WHERE   ud_type    = 'G'
            ) X1
            WHERE ROWNUM  <= 900051
        )
        WHERE   rnum BETWEEN 900000 AND 900050;

인덱스 생성방벙 :
    DROP INDEX IDX_01;
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);
    OR
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate DESC, tm_seq, ud_type);
   

데이터 건수 : 1,118,003
################################# CUBRID 2008 R4.0##################################
-- CUBRID
-- csql>;REA /path/USE_DETAIL.SQL
-- csql>;RU
-- #csql demodb -i /path/USE_DETAIL.SQL
CREATE TABLE USE_DETAIL
(
     ud_seq             INTEGER                     NOT NULL
    ,rc_seq             VARCHAR(10)
    ,ma_seq             VARCHAR(20)
    ,fd_seq             INTEGER
    ,ui_id              VARCHAR(20)
    ,tm_seq             VARCHAR(10)                 NOT NULL
    ,ud_type            CHAR(1)         DEFAULT 'G' NOT NULL
    ,ud_weight          NUMERIC(10,3)
    ,ud_usedate         VARCHAR(20)     DEFAULT ''  NOT NULL
    ,ud_dayofweek       SHORT
    ,ud_cdate           VARCHAR(20)
    ,ud_useunixtime     INTEGER
    ,CONSTRAINT PK_USE_DETAIL PRIMARY KEY(ud_seq)
);

CREATE UNIQUE INDEX PK_USE_DETAIL ON USE_DETAIL(ud_seq);
CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);

접    속 : csql -C demodb
실행시간 : csql> ;time on 
종    료 : csql> ;ex

그룹핑 테스트 SQL :

        SELECT  X1.ud_seq, X1.ma_seq, X1.tm_seq
        FROM    USE_DETAIL X1,
        (
            SELECT  MAX(ud_seq) AS ud_seq, ma_seq, tm_seq
            FROM    USE_DETAIL X
            WHERE   ud_usedate BETWEEN '2011-07-01' AND '2011-08-01'
            AND     tm_seq  IN('00101','00102','00103','00104','00105','00106','00107','00108')
            AND     ud_type = 'G'
            GROUP   BY ma_seq, tm_seq
        ) X2
        WHERE X1.ud_seq = X2.ud_seq;

페이징 테스트 SQL :

        SELECT  ud_seq, ma_seq, tm_seq, ud_usedate
        FROM    USE_DETAIL
        WHERE   ud_type = 'G'
        ORDER   BY ud_seq DESC FOR ORDERBY_NUM() BETWEEN 900000 AND 900050;
       
       
인덱스 생성방벙 :
    DROP INDEX IDX_01;
    OR
    DROP REVERSE INDEX IDX_01;
   
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);
    OR
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate DESC, tm_seq, ud_type);
    OR
    CREATE REVERSE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);
   

데이터 건수 : 1,118,003
################################# PostgreSQL 8.4.7 ##################################
-- PostgreSQL
-- demodb=# \i /path/USE_DETAIL.SQL;
-- $psql -U username dbname < /path/USE_DETAIL.SQL
CREATE TABLE USE_DETAIL
(
     ud_seq             INTEGER                     NOT NULL
    ,rc_seq             VARCHAR(10)
    ,ma_seq             VARCHAR(20)
    ,fd_seq             INTEGER
    ,ui_id              VARCHAR(20)
    ,tm_seq             VARCHAR(10)                 NOT NULL
    ,ud_type            CHAR(1)         DEFAULT 'G' NOT NULL
    ,ud_weight          NUMERIC(10,3)
    ,ud_usedate         VARCHAR(20)     DEFAULT ''  NOT NULL
    ,ud_dayofweek       SMALLINT
    ,ud_cdate           VARCHAR(20)
    ,ud_useunixtime     INTEGER
    ,CONSTRAINT PK_USE_DETAIL PRIMARY KEY(ud_seq)
);

CREATE UNIQUE INDEX PK_USE_DETAIL ON USE_DETAIL(ud_seq);
CREATE INDEX IDX_01 ON USE_DETAIL1(ud_usedate, tm_seq, ud_type);

접    속 : psql -U postgres demodb
실행시간 : demodb=# \timing on
종    료 : demodb=# \q

그룹핑 테스트 SQL :

        SELECT  X1.ud_seq, X1.ma_seq, X1.tm_seq
        FROM    USE_DETAIL X1,
        (
            SELECT  MAX(ud_seq) AS ud_seq, ma_seq, tm_seq
            FROM    USE_DETAIL X
            WHERE   ud_usedate BETWEEN '2011-07-01' AND '2011-08-01'
            AND     tm_seq  IN('00101','00102','00103','00104','00105','00106','00107','00108')
            AND     ud_type = 'G'
            GROUP   BY ma_seq, tm_seq
        ) X2
        WHERE X1.ud_seq = X2.ud_seq;

페이징 테스트 SQL 1 :

        SELECT  ud_seq, ma_seq, tm_seq, ud_usedate
        FROM    USE_DETAIL
        WHERE   ud_type = 'G'
        ORDER   BY ud_seq DESC
        LIMIT   50 OFFSET 900000;

페이징 테스트 SQL 2 :
        SELECT  *
        FROM
        (
            SELECT  ROW_NUMBER() OVER (ORDER BY ud_usedate DESC) rnum, ud_seq, ma_seq, tm_seq, ud_usedate
            FROM    USE_DETAIL
            WHERE   ud_type = 'G'
        ) X2
        WHERE rnum BETWEEN 900000 AND 900050;

인덱스 생성방벙 :
    DROP INDEX IDX_01;
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate, tm_seq, ud_type);
    OR
    CREATE INDEX IDX_01 ON USE_DETAIL(ud_usedate DESC, tm_seq, ud_type);
   

####테스트 자료####
USE_DETAIL.7z

* Physical 모드

* Logical 모드

* Logical 모드의 Attribute 명을 Physical 모드의 코멘트로 변환 (별도로 코멘트를 등록했다면 이부분은 넘어가도됨!!!)
  Physical 모드 메뉴에서 Model > Domain Dictionary 선택
  Tab 메뉴에서 Comment 선택하고 Comment Inherited by Col:* 에 %AttName  입력하고 OK 버튼 클릭

 다시 Physical 모드에서 모델(테이블)을 선택한후 우클릭해서 Columns 선택한후 Reset 버튼 클릭함

 All columns of table [table name](선택 테이블만 적용) 또는 All columns in model(전체적용) 체크하고
 Select Properties to Reset: 에서 Comment 만 체크함(중요) OK 버튼을 클릭함. 

첨부한 파일을 특정 디렉토리에 복사한뒤 파일을 적용하고 Preview 버튼을 클릭해서 생성 스크립트를 확인해 본다.

* 실제 적용한 스크립트 내용임

MySQL2.fet

SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('1234nextguide'))) FROM dual;
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW('MTIzNG5leHRndWlkZQ=='))) FROM dual;

SELECT UTL_ENCODE.TEXT_ENCODE('1234nextguide', 'WE8ISO8859P1', 1) FROM dual;
SELECT UTL_ENCODE.TEXT_DECODE('MTIzNG5leHRndWlkZQ==', 'WE8ISO8859P1', 1) FROM dual;

/*------------------------------------------------------------------------------
-- 개체명 : B2C
-- 생성일 : 2010-03-22 오후 3:03:32
-- 최종수정일 : 2010-03-22 오후 3:03:39
-- 상태 : VALID
------------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION B2C(B IN BLOB) RETURN CLOB -- TYPECASTS BLOB TO CLOB (BINARY CONVERSION)
IS
    pos       PLS_INTEGER  := 1;
    buffer    VARCHAR2( 32767 );
    res       CLOB;
    lob_len   PLS_INTEGER  := DBMS_LOB.getLength(b);
BEGIN
    DBMS_LOB.createTemporary(res, TRUE);
    DBMS_LOB.OPEN(res, DBMS_LOB.LOB_ReadWrite);

    LOOP
        buffer := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, 16000, pos));

        IF LENGTH( buffer ) > 0
        THEN
            DBMS_LOB.writeAppend( res, LENGTH( buffer ), buffer );
        END IF;

        pos := pos + 16000;
        EXIT WHEN pos > lob_len;
    END LOOP;

    RETURN res; -- res is OPEN here
END b2c;

/*------------------------------------------------------------------------------
-- 개체명 : C2B
-- 생성일 : 2010-03-22 오후 3:03:32
-- 최종수정일 : 2010-03-22 오후 3:03:39
-- 상태 : VALID
------------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION C2B(C IN CLOB) RETURN BLOB -- TYPECASTS CLOB TO BLOB (BINARY CONVERSION)
IS
	pos       PLS_INTEGER  := 1;
    buffer    RAW( 32767 );
    res       BLOB;
    lob_len   PLS_INTEGER  := DBMS_LOB.getLength(c);
BEGIN
    DBMS_LOB.createTemporary(res, TRUE);
    DBMS_LOB.OPEN(res, DBMS_LOB.LOB_ReadWrite);

    LOOP
        buffer := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(c, 16000, pos));

        IF UTL_RAW.LENGTH(buffer) > 0
        THEN
            DBMS_LOB.writeAppend(res, UTL_RAW.LENGTH(buffer), buffer);
        END IF;

        pos := pos + 16000;

        EXIT WHEN pos > lob_len;
    END LOOP;

    RETURN res; -- res is OPEN here
END c2b;

/*------------------------------------------------------------------------------
-- 개체명 : BASE64_B2C
-- 생성일 : 2010-03-22 오후 3:03:32
-- 최종수정일 : 2010-03-22 오후 3:03:39
-- 상태 : VALID
------------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION BASE64_B2C(B IN BLOB) RETURN CLOB
IS
	v_buffer_size	INTEGER  := 4096;
	v_buffer_raw	RAW(4096);
	v_offset		INTEGER DEFAULT 1;
	v_clob			CLOB;
BEGIN
	DBMS_LOB.createTemporary(v_clob, FALSE, DBMS_LOB.CALL);

	LOOP
		BEGIN
			DBMS_LOB.READ(b, v_buffer_size, v_offset, v_buffer_raw);
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
            EXIT;
        END;

        v_offset := v_offset + v_buffer_size;
        DBMS_LOB.APPEND(v_clob, to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(v_buffer_raw))));
    END LOOP;

    RETURN v_clob;
END BASE64_B2C;

/*------------------------------------------------------------------------------
-- 개체명 : BASE64_C2B
-- 생성일 : 2010-03-22 오후 3:03:32
-- 최종수정일 : 2010-03-22 오후 3:03:39
-- 상태 : VALID
------------------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION BASE64_C2B(C IN CLOB) RETURN BLOB
IS
	v_blob				BLOB;
	v_offset			INTEGER;
	v_buffer_varchar	VARCHAR2(4096);
	v_buffer_raw		RAW(4096);
	v_buffer_size		BINARY_INTEGER := 4096;
BEGIN
	IF c IS NULL
	THEN
        RETURN NULL;
    END IF;

    DBMS_LOB.createTemporary(v_blob, TRUE);
    v_offset := 1;

    LOOP
        BEGIN
            DBMS_LOB.READ(c, v_buffer_size, v_offset, v_buffer_varchar);
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
            EXIT;
        END;

        v_buffer_raw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(v_buffer_varchar));
        dbms_lob.writeAppend(v_blob, UTL_RAW.LENGTH(v_buffer_raw), v_buffer_raw);
        v_offset := v_offset + v_buffer_size;
    END LOOP;

    RETURN v_blob;

END BASE64_C2B;

[참조] smartsql.tistory.com

sqlplus 실행시 @를 붙여 별도의 sql문을 실행하면서 해도 되지만
파일을 별도로 관리해야 되는 번거로움 때문에 쉘스크립트상에서 실행하기로 했음

crontab 에 등록시
4 19 * * * /oracle/shell/call_proc_profit_dist.sh  >> /oracle/shell/log/proc_profit_dist.log

file: call_proc_profit_dist.sh

#!/bin/bash
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/10.2.0
export ORACLE_OWNER=oracle
export ORACLE_SID=kumc
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export NLS_LANG=KOREAN_KOREA.KO16MSWIN949
export NLS_NCHAR=KOREAN_KOREA.UTF8
export NLS_DATE_FORMAT=YYYYMMDD
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/demo:$ORAClE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
export LDPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export PATH=$ORACLE_HOME/JRE/bin:$ORACLE_HOME/bin:.:$PATH
export TEMPDIR=/tmp
export THREADS_FLAG=native
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/oracm/bin
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export EDITOR=vi
export LANG=ko_KR.eucKR
export PATH ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_SID  TNS_ADMIN NLS_LANG LD_LIBRARY_PATH ORA_NLS33  PATH LD_ASSUME_KERNEL TEMPDIR THREADS_FLAG PATH CLASSPATH EDITOR LANG NLS_DATE_FORMAT DISABLE_HUGETLBFS LDPATH


sqlplus -s kumc_cost/kumc_cost << __END__
	WHENEVER SQLERROR EXIT 1
	WHENEVER OSERROR EXIT 1
	SET TIMING ON
	SET SERVEROUTPUT ON
	SET PAGESIZE 0
	SET FEEDBACK OFF
	SET VERIFY OFF
	SET HEADING OFF
	SET ECHO OFF

	DECLARE
		vMsg	VARCHAR2(4000)	:= NULL;
		vSQL	VARCHAR2(4000)	:= NULL;
		vCheck	BOOLEAN			:= TRUE;
		vSeq	NUMBER;
	BEGIN
		DBMS_OUTPUT.ENABLE('10000000000');

		SELECT	cpi_seq
		INTO	vSeq
		FROM	COST_PROCESS_INFO
		WHERE	cpi_status = 'P';

		PROC_PROFIT_DIST(vCheck, vMsg);
		--vSQL := 'CALL PROC_PROFIT_DIST(:vCheck, :vMsg)';
		--EXECUTE IMMEDIATE vSQL USING IN vCheck, OUT vMsg;
		--DBMS_OUTPUT.PUT_LINE(vMsg);
		
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.PUT_LINE('진행중인 절차가 없습니다.!!');
	END;
	/
__END__


EXITCODE=$?
export EXITCODE

if [ "$EXITCODE" -ne 0 ]
then
	echo "Error: SQL*Plus exit code : $EXITCODE"
	echo ""
	echo "===== Procedure run aborted at `date` !!! ===="
	echo ""
	exit 1
fi

echo ""
echo "===== Procedure Finished at `date` !!! ===="
echo ""

call_proc_profit_dist.sh

[출처] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

By Mike Hillyer

Introduction

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:

These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model.

The Adjacency List Model

Typically the example categories shown above will be stored in a table like the following (I'm including full CREATE and INSERT statements so you can follow along):

CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);


INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.

Retrieving a Full Tree

The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

Finding all the Leaf Nodes

We can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query:

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;


+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Retrieving a Single Path

The self-join also allows us to see the full path through our hierarchies:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)

The main limitation of such an approach is that you need one self-join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity.

Limitations of the Adjacency List Model

Working with the adjacency list model in pure SQL can be difficult at best. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process (delete the portable electronics category and all of its children are orphaned). Some of these limitations can be addressed through the use of client-side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes without orphaning entire sub-trees by promoting one child element and re-ordering the remaining children to point to the new parent.

The Nested Set Model

What I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model. In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers. Try picturing our electronics categories this way:

Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left and right values to represent the nesting of our nodes:

CREATE TABLE nested_category (
 category_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(20) NOT NULL,
 lft INT NOT NULL,
 rgt INT NOT NULL
);


INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);


SELECT * FROM nested_category ORDER BY category_id;


+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

We use lft and rgt because left and right are reserved words in MySQL, see http://dev.mysql.com/doc/mysql/en/reserved-words.html for the full list of reserved words.

So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right:

This design can be applied to a typical tree as well:

When working with a tree, we work from left to right, one layer at a time, descending to each node's children before assigning a right-hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm.

Retrieving a Full Tree

We can retrieve the full tree through the use of a self-join that links parents with nodes on the basis that a node's lft value will always appear between its parent's lft and rgt values:

SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;


+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+

Unlike our previous examples with the adjacency list model, this query will work regardless of the depth of the tree. We do not concern ourselves with the rgt value of the node in our BETWEEN clause because the rgt value will always fall within the same parent as the lft values.

Finding all the Leaf Nodes

Finding all leaf nodes in the nested set model even simpler than the LEFT JOIN method used in the adjacency list model. If you look at the nested_category table, you may notice that the lft and rgt values for leaf nodes are consecutive numbers. To find the leaf nodes, we look for nodes where rgt = lft + 1:

SELECT name
FROM nested_category
WHERE rgt = lft + 1;


+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Retrieving a Single Path

With the nested set model, we can retrieve a single path without having multiple self-joins:

SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

Finding the Depth of the Nodes

We have already looked at how to show the entire tree, but what if we want to also show the depth of each node in the tree, to better identify how each node fits in the hierarchy? This can be done by adding a COUNT function and a GROUP BY clause to our existing query for showing the entire tree:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+

We can use the depth value to indent our category names with the CONCAT and REPEAT string functions:

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

Of course, in a client-side application you will be more likely to use the depth value directly to display your hierarchy. Web developers could loop through the tree, adding <li></li> and <ul></ul> tags as the depth number increases and decreases.

Depth of a Sub-Tree

When we need depth information for a sub-tree, we cannot limit either the node or parent tables in our self-join because it will corrupt our results. Instead, we add a third self-join, along with a sub-query to determine the depth that will be the new starting point for our sub-tree:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
	nested_category AS parent,
	nested_category AS sub_parent,
	(
		SELECT node.name, (COUNT(parent.name) - 1) AS depth
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		AND node.name = 'PORTABLE ELECTRONICS'
		GROUP BY node.name
		ORDER BY node.lft
	)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
	AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
	AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;


+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

This function can be used with any node name, including the root node. The depth values are always relative to the named node.

Find the Immediate Subordinates of a Node

Imagine you are showing a category of electronics products on a retailer web site. When a user clicks on a category, you would want to show the products of that category, as well as list its immediate sub-categories, but not the entire tree of categories beneath it. For this, we need to show the node and its immediate sub-nodes, but no further down the tree. For example, when showing the PORTABLE ELECTRONICS category, we will want to show MP3 PLAYERS, CD PLAYERS, and 2 WAY RADIOS, but not FLASH.

This can be easily accomplished by adding a HAVING clause to our previous query:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
	nested_category AS parent,
	nested_category AS sub_parent,
	(
		SELECT node.name, (COUNT(parent.name) - 1) AS depth
		FROM nested_category AS node,
		nested_category AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		AND node.name = 'PORTABLE ELECTRONICS'
		GROUP BY node.name
		ORDER BY node.lft
	)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
	AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
	AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

If you do not wish to show the parent node, change the HAVING depth <= 1 line to HAVING depth = 1.

Aggregate Functions in a Nested Set

Let's add a table of products that we can use to demonstrate aggregate functions with:

CREATE TABLE product(
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
category_id INT NOT NULL
);


INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);

SELECT * FROM product;

+------------+-------------------+-------------+
| product_id | name              | category_id |
+------------+-------------------+-------------+
|          1 | 20" TV            |           3 |
|          2 | 36" TV            |           3 |
|          3 | Super-LCD 42"     |           4 |
|          4 | Ultra-Plasma 62"  |           5 |
|          5 | Value Plasma 38"  |           5 |
|          6 | Power-MP3 128mb   |           7 |
|          7 | Super-Shuffle 1gb |           8 |
|          8 | Porta CD          |           9 |
|          9 | CD To go!         |           9 |
|         10 | Family Talk 360   |          10 |
+------------+-------------------+-------------+

Now let's produce a query that can retrieve our category tree, along with a product count for each category:

SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
nested_category AS parent,
product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;


+----------------------+---------------------+
| name                 | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS          |                  10 |
| TELEVISIONS          |                   5 |
| TUBE                 |                   2 |
| LCD                  |                   1 |
| PLASMA               |                   2 |
| PORTABLE ELECTRONICS |                   5 |
| MP3 PLAYERS          |                   2 |
| FLASH                |                   1 |
| CD PLAYERS           |                   2 |
| 2 WAY RADIOS         |                   1 |
+----------------------+---------------------+

This is our typical whole tree query with a COUNT and GROUP BY added, along with a reference to the product table and a join between the node and product table in the WHERE clause. As you can see, there is a count for each category and the count of subcategories is reflected in the parent categories.

Adding New Nodes

Now that we have learned how to query our tree, we should take a look at how to update our tree by adding a new node. Let's look at our nested set diagram again:

If we wanted to add a new node between the TELEVISIONS and PORTABLE ELECTRONICS nodes, the new node would have lft and rgt values of 10 and 11, and all nodes to its right would have their lft and rgt values increased by two. We would then add the new node with the appropriate lft and rgt values. While this can be done with a stored procedure in MySQL 5, I will assume for the moment that most readers are using 4.1, as it is the latest stable version, and I will isolate my queries with a LOCK TABLES statement instead:

LOCK TABLE nested_category WRITE;


SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';



UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

We can then check our nesting with our indented tree query:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;


+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

If we instead want to add a node as a child of a node that has no existing children, we need to modify our procedure slightly. Let's add a new FRS node below the 2 WAY RADIOS node:

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = '2 WAY RADIOS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;

In this example we expand everything to the right of the left-hand number of our proud new parent node, then place the node to the right of the left-hand value. As you can see, our new node is now properly nested:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;


+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

Deleting Nodes

The last basic task involved in working with nested sets is the removal of nodes. The course of action you take when deleting a node depends on the node's position in the hierarchy; deleting leaf nodes is easier than deleting nodes with children because we have to handle the orphaned nodes.

When deleting a leaf node, the process if just the opposite of adding a new node, we delete the node and its width from every node to its right:

LOCK TABLE nested_category WRITE;


SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';


DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;


UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

And once again, we execute our indented tree query to confirm that our node has been deleted without corrupting the hierarchy:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;


+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

This approach works equally well to delete a node and all its children:

LOCK TABLE nested_category WRITE;


SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';


DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;


UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

And once again, we query to see that we have successfully deleted an entire sub-tree:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;


+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

The other scenario we have to deal with is the deletion of a parent node but not the children. In some cases you may wish to just change the name to a placeholder until a replacement is presented, such as when a supervisor is fired. In other cases, the child nodes should all be moved up to the level of the deleted parent:

LOCK TABLE nested_category WRITE;


SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';


DELETE FROM nested_category WHERE lft = @myLeft;


UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

UNLOCK TABLES;

In this case we subtract two from all elements to the right of the node (since without children it would have a width of two), and one from the nodes that are its children (to close the gap created by the loss of the parent's left value). Once again, we can confirm our elements have been promoted:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;


+---------------+
| name          |
+---------------+
| ELECTRONICS   |
|  TELEVISIONS  |
|   TUBE        |
|   LCD         |
|   PLASMA      |
|  CD PLAYERS   |
|  2 WAY RADIOS |
|   FRS         |
+---------------+

Other scenarios when deleting nodes would include promoting one of the children to the parent position and moving the child nodes under a sibling of the parent node, but for the sake of space these scenarios will not be covered in this article.

Final Thoughts

While I hope the information within this article will be of use to you, the concept of nested sets in SQL has been around for over a decade, and there is a lot of additional information available in books and on the Internet. In my opinion the most comprehensive source of information on managing hierarchical information is a book called Joe Celko's Trees and Hierarchies in SQL for Smarties, written by a very respected author in the field of advanced SQL, Joe Celko. Joe Celko is often credited with the nested sets model and is by far the most prolific author on the subject. I have found Celko's book to be an invaluable resource in my own studies and highly recommend it. The book covers advanced topics which I have not covered in this article, and provides additional methods for managing hierarchical data in addition to the Adjacency List and Nested Set models.

In the References / Resources section that follows I have listed some web resources that may be of use in your research of managing hierarchal data, including a pair of PHP related resources that include pre-built PHP libraries for handling nested sets in MySQL. Those of you who currently use the adjacency list model and would like to experiment with the nested set model will find sample code for converting between the two in the Storing Hierarchical Data in a Database resource listed below.

#!/usr/bin/perl

use DBI;
use Time::Local;
use Error qw(:try);
use Archive::Zip qw(:ERROR_CODES :CONSTANTS);

BEGIN
{
	$ENV{ORACLE_OWNER}		= "oracle9";
	$ENV{ORACLE_BASE}		= "/home/$ENV{ORACLE_OWNER}";
	$ENV{ORACLE_HOME}		= "$ENV{ORACLE_BASE}/product/9i";
	$ENV{TNS_ADMIN}			= "$ENV{ORACLE_HOME}/network/admin";
	$ENV{ORA_NLS33}			= "$ENV{ORACLE_HOME}/ocommon/nls/admin/data";
	$ENV{ORACLE_SID}		= "NEXTBSC";
	$ENV{ORACLE_TNSNAME}	= "NEXTBSC";
	$ENV{NLS_LANG}			= "KOREAN_KOREA.KO16MSWIN949";
	$ENV{NLS_NCHAR}			= "KOREAN_KOREA.UTF8";
	$ENV{LD_LIBRARY_PATH}	= "$ENV{ORACLE_HOME}/lib:$ENV{ORACLE_HOME}/lib32:$ENV{ORACLE_HOME}/rdbms/demo:$ENV{ORACLE_HOME}/oracm/lib:/lib:/usr/lib:/usr/local/lib";
	$ENV{LD_RUN_PATH}		= "$ENV{ORACLE_HOME}/lib";
	$ENV{NLS_DATE_FORMAT}	= "YYYYMMDD";
	$ENV{TEMPDIR}			= "/tmp";
	$ENV{THREADS_FLAG}		= "native";
}

=comment
foreach my $key(keys %ENV)
{
	print $key."=". $ENV{$key}."\n";
}
=cut

##-------------------------------------------------------------------------------------------------------
## 삭제할 파일 일자 정의(3일전)
##-------------------------------------------------------------------------------------------------------
($year, $month, $day) = (localtime(time - (60*60*24*3)))[5,4,3];
my $rmdate			  = sprintf("%04d%02d%02d", $year+1900, $month+1, $day);


##-------------------------------------------------------------------------------------------------------
##백업할 파일 일자 정의(당일)
##-------------------------------------------------------------------------------------------------------
($year, $month, $day) = (localtime(time))[5,4,3];
my $date			  = sprintf("%04d%02d%02d", $year+1900, $month+1, $day);
#my $date			  = sprintf("%02d", $day);


##-------------------------------------------------------------------------------------------------------
## 기본 변수 선언. 
##-------------------------------------------------------------------------------------------------------
my $user		= "qes_bsc";
my $bakdir		= "/data1/oracle9i_dmp_files/";
my $fullpath	= $bakdir.$date		."_". $user ."_full.zip";
my $onepath		= $bakdir.$date		."_". $user ."_one.zip";
my $rmfullpath	= $bakdir.$rmdate	."_". $user ."_full.zip";
my $rmonepath	= $bakdir.$rmdate	."_". $user ."_one.zip";
my @nums		= (0..30);


#-------------------------------------------------------------------------------------------------------
# FULL 백업 ....
#-------------------------------------------------------------------------------------------------------
print "\nFULL 백업중.....\n";
my	$command  = "$ENV{ORACLE_HOME}/bin/exp $user/$user\@$ENV{ORACLE_TNSNAME} grants=y ";
	$command .= " file=(";
	for(@nums)
	{
		$command .= $bakdir . $user ."_full_". sprintf("%02s", $_) .".dmp,";
	}
	$command  = substr($command, 0, length($command)-1);
	$command .= " )";
	$command .= " filesize=2G";
	$command .= " log=". $bakdir . $user .".log";

system $command;


##-------------------------------------------------------------------------------------------------------
## 테이블 단위 백업....
##-------------------------------------------------------------------------------------------------------
try
{
	print "\n테이블 단위 백업중.....\n";
	my $conn	= DBI->connect("DBI:Oracle:NEXTBSC", $user, $user, {AutoCommit => 0, RaiseError => 1, PrintError => 0 }) || die "Error :$DBI::errstr";
	my $SQL		= "SELECT tname FROM tab WHERE tname NOT LIKE 'BIN%' ";
	my $stmt	= $conn->prepare($SQL);
	$stmt->execute();

	while(@row = $stmt->fetchrow_array)
	{
		my $command = "";

		if(@row["TNAME"] == "NG01_REPORT_INFO")
		{
			$command  = "$ENV{ORACLE_HOME}/bin/exp qes_bsc/qes_bsc\@$ENV{ORACLE_TNSNAME} grants=y ";
			$command .= " tables=". @row["TNAME"];
			$command .= " file=(";
			for(@nums)
			{
				$command .= $bakdir . @row["TNAME"] ."_one_". sprintf("%02s", $_) .".dmp,";
			}
			$command  = substr($command, 0, length($command)-1);
			$command .= " )";
			$command .= " filesize=2G";
		}
		else
		{
			$command  = "$ENV{ORACLE_HOME}/bin/exp qes_bsc/qes_bsc\@$ENV{ORACLE_TNSNAME} grants=y ";
			$command .= " tables=". @row["TNAME"];
			$command .= " file=". $bakdir . @row["TNAME"] ."_one_". ".dmp";
		}
		system $command;
	}

	$stmt->finish;
	$conn->disconnect;
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## FULL 백업 압축....
##-------------------------------------------------------------------------------------------------------
try
{
	opendir(DIR, $bakdir);
	@files = grep(/_full_\d{2}/, readdir(DIR));
	print $bakdir.@files;
	closedir(DIR);

	if(@files > 0)
	{
		my $zip		= Archive::Zip->new();
		my $comp	= $zip->addDirectory($date);
		$comp->desiredCompressionMethod(COMPRESSION_DEFLATED);
		$comp->desiredCompressionLevel(COMPRESSION_LEVEL_BEST_COMPRESSION);
		print "FULL 백업을 압축중입니다.....!!!\n";

		foreach my $file(sort @files)
		{
			print $file. "\n";
			$comp	= $zip->addFile($bakdir . $file, $date ."/". $file) || die 'write error';
			$check	= true;
		}
		$zip->writeToFileNamed($fullpath) == AZ_OK;
		system "split -b 680m ". $fullpath ." ". $fullpath.".";
	}
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## 테이블 단위 압축....
##-------------------------------------------------------------------------------------------------------
try
{
	opendir(DIR, $bakdir);
	@files = grep(/_one_(\d{2})?/, readdir(DIR));
	closedir(DIR);
	print $bakdir.@files;

	if(@files > 0)
	{
		my $zip		= Archive::Zip->new();
		my $zip		= Archive::Zip->new();
		my $comp	= $zip->addDirectory($date);
		$comp->desiredCompressionMethod(COMPRESSION_DEFLATED);
		$comp->desiredCompressionLevel(COMPRESSION_LEVEL_BEST_COMPRESSION);
		print "\n테이블 단위 백업을 압축중입니다.....!!!\n";

		foreach my $file(sort @files)
		{
			print $file. "\n";
			$comp	= $zip->addFile($bakdir . $file, $date ."/". $file) || die 'write error';
		}
		$zip->writeToFileNamed($onepath) == AZ_OK;
		system "split -b 680m ". $onepath ." ". $onepath.".";
	}
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## 삭제할 파일
##-------------------------------------------------------------------------------------------------------
system "rm -f ". $rmfullpath;
system "rm -f ". $rmonepath;
system "rm -f ". $bakdir ."*.dmp";


__END__
##-------------------------------------------------------------------------------------------------------
## 실행전에 필용한 팩키지 목록들....
##-------------------------------------------------------------------------------------------------------
##perl -e "use Archive::Zip"
##perl -MCPAN -e "install DBD::Oracle"
##perl -MCPAN -e "install Archive::Zip"
##perl -MCPAN -e shell
##cpan>i /archive::zip/
##cpan>install Archive::Zip
##cpan>quit
##windows --> copy /b XXXX_full_zip.aa + XXXX_full_zip.ab  XXXX_full.zip
##solaris --> cat XXXX_full_zip.?? >  XXXX_full.zip
##linux   --> XXXX_full_zip.* >  XXXX_full.zip

##__author__  = "park17@gmail.com"
##__version__ = "0.1"
##__date__    = "2007-09-29"
##__file__    = "ora_backup.pl"

#!/usr/bin/perl

use strict;
use Archive::Zip qw(:ERROR_CODES);

my $zip = Archive::Zip->new();
my $zipName = shift(@ARGV);

if($zip->read($zipName) != AZ_OK)
{
    die "Read of $zipName failed\n";
}
else
{
    foreach my $member($zip->members())
    {
        print $member->fileName(), ", ", $member->uncompressedSize(), ":", $member->compressedSize(), "\n";
        $zip->extractMember($member->fileName());
    }
}

SET SERVEROUTPUT ON SIZE 1000000
SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON PAGES 0 LINES 512
SET TERMOUT OFF
SET TERMOUT ON
!rm -rf './proc_backup';
!mkdir './proc_backup';
PROMPT
PROMPT PROCEDURES export Start...!!!!
PROMPT
SET TERMOUT OFF
SPOOL procedures_exp.sql
PROMPT SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF PAGES 0
--PROMPT SET LINESIZE 512;
--LINES 512

DECLARE
    TYPE objectElements IS VARRAY(5) OF VARCHAR2(20);
    VObjectType objectElements	:= objectElements('PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'TYPE');
    vObjectExt  objectElements	:= objectElements('prc', 'spc', 'bdy', 'fnc', 'tps');
	vObjectName	VARCHAR2(50)	:= '';
	vExportDir	VARCHAR2(20)	:= './proc_backup/';
	--vFileHandle	UTL_FILE.FILE_TYPE;
    
    CURSOR cur_procedures(v_object_type VARCHAR2)
    IS
        SELECT  name  --, DBMS_METADATA.GET_DDL(v_object_type, name) text
        FROM    USER_SOURCE
        WHERE   TYPE = v_object_type
        GROUP   BY name;

	--
	-- DBA 권한시 수정하여 사용.(procedures_dump.sql 파일도 수정) 
	--
	/*
    CURSOR cur_procedures(v_owner VARCHAR2, v_object_type VARCHAR2)
    IS
		SELECT  object_name name --, DBMS_METADATA.GET_DDL(v_object_type, object_name, owner) 
		FROM	dba_objects
		WHERE	owner	 	= v_owner
		AND		object_type = v_object_type
		ORDER BY owner
	*/
        
BEGIN
	--DBMS_OUTPUT.ENABLE('10000000000');
     
	FOR i IN VObjectType.FIRST .. VObjectType.LAST
    LOOP
        FOR list_procedures IN cur_procedures(VObjectType(i))
        LOOP
			vObjectName := LOWER(list_procedures.name || '.' || vObjectExt(i));
			DBMS_OUTPUT.put_line('SET TERMOUT ON');
			DBMS_OUTPUT.put_line('PROMPT ' || VObjectType(i) || ' -->> ' || vExportDir || vObjectName);
			DBMS_OUTPUT.put_line('SET TERMOUT OFF');
			DBMS_OUTPUT.put_line('spool ' || vExportDir || vObjectName);
			DBMS_OUTPUT.put_line('@procedures_dump ' || VObjectType(i) || ' ' || list_procedures.name);
			DBMS_OUTPUT.put_line('spool off');

			--vFileHandle := UTL_FILE.fopen(vExportDir, list_procedures.name || '.' || vObjectExt(i), 'W'); 
			--UTL_FILE.put_line(vFileHandle, list_procedures.text); 
			--UTL_FILE.fclose(vFileHandle); 
        END LOOP;
	END LOOP;
	
/*
EXCEPTION 
	WHEN NO_DATA_FOUND THEN 
		DBMS_OUTPUT.put_line('NO_DATA_FOUND');
		UTL_FILE.fclose(vFileHandle); 
		NULL;
	WHEN UTL_FILE.INVALID_PATH THEN 
		DBMS_OUTPUT.put_line('UTL_FILE.INVALID_PATH'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN UTL_FILE.READ_ERROR THEN 
		DBMS_OUTPUT.put_line(' UTL_FILE.READ_ERROR'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN UTL_FILE.WRITE_ERROR THEN 
		DBMS_OUTPUT.put_line('UTL_FILE.WRITE_ERROR'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN OTHERS THEN 
		DBMS_OUTPUT.put_line('OTHER STUFF'); 
		UTL_FILE.fclose(vFileHandle); 
*/
END;
/
SPOOL OFF;
@procedures_exp.sql;

SET TERMOUT ON
PROMPT
PROMPT PROCEDURES export End...!!!!
PROMPT
SET TERMOUT OFF

QUIT;

SET SERVEROUTPUT ON SIZE 1000000
SET HEAD OFF VERIFY OFF
SET LONG 1000000
COL TEXT FORMAT A132;
	SELECT  TRIM(DBMS_METADATA.GET_DDL('&1', name)) text
	FROM    USER_SOURCE
	WHERE   type = '&1'
	AND		name = '&2'
	GROUP   BY name;

/*
	SELECT  TRIM(DBMS_METADATA.GET_DDL('&2', object_name, '&1')) text
	FROM    dba_objects
	WHERE   owner       = '&1'
	AND     object_type = '&2'
	ORDER BY owner
*/
PROMPT /

procedures_backup.zip

begin
	dbms_scheduler.create_program
	(
		program_name   => 'test_scheduler',
		program_action => '/export/home/work/aaa.exe',
		program_type   => 'executable'
	);
end;
/

begin
	dbms_scheduler.drop_program
	(
		program_name   => 'test_scheduler',
		force          => false
	);
end;
/

begin
	dbms_scheduler.create_schedule
	(
		program_name   => 'hourly_schedule',
		start_date     => systimestamp,
		end_date       => systimestamp + interval '30' day,
		repeat_interval => 'freq=hourly; interval=4',
		comments        => 'every 4 hours'
	); 
end;
/ 

begin
	dbms_scheduler.create_job
	(
		job_name   => 'my_emp_job1',
		job_type   => 'PLSQL_BLOCK',
		job_action => 'insert into emp values(7987, "SALLY","ANALYST",
		null,null,null,null,null);',
		start_date     => '09/07/23 17:16:51.927000+09:00',
		repeat_interval => 'freq=daily; interval=2',
		comments        => 'my new job'
	); 
end;
/ 

begin
	dbms_scheduler.drop_job
	(
		job_name   => 'my_emp_job1',
		force      => false
	);
end;
/ 

create or replace procedure check_test
IS
	v_no     number(4);
	v_name   varchar2(13) := null;
BEGIN
	select	empno, ename into v_no, v_name from scott.emp
	where	empno =7934;
	dbms_output.put_line(v_no||'  '||v_name);
END;
/

BEGIN
	dbms_scheduler.create_job
	(
		job_name    => 'my_emp_job2',
		job_type    => 'PLSQL_BLOCK',
		job_action  => 'BEGIN check_test(); END;',
		schedule_name => 'hourly_schedule'
	);
END;
/
execute dbms_scheduler.enable('my_emp_job2');

execute dbms_scheduler.run_job('my_emp_job2');

BEGIN
	DBMS_SCHEDULER.DROP_JOB
	(
		job_name   => 'my_emp_job2';
		force      => false
	);
END;
/

begin
	dbms_scheduler.create_job
	(
		job_name     => 'my_emp_job3',
		job_type     => 'executable',
		job_action   => '/export/home/work/aaa',
		start_date   => systimestamp,
		repeat_interval => 'freq=daily; ingterval=2',
		end_date     => systimestamp + interval '30' day
	);
end;
/

execute dbms_scheduler.enable ('my_emp_job3');
execute dbms_scheduler.run_job('my_emp_job3');
BEGIN
dbms_scheduler.drop_job
(
	job_name => 'my_emp_job3',
	force    => false
);
END;
/

begin
	dbms_scheduler.create_window
	(
		window_name   =>'my_window100',
		schedule_name => 'hourly_schedule',
		resource_plan => 'internal_plan',
		duration      => interval '160' minute,
		comments      => 'My window'
	);
END;
/

execute dbms_scheduler.enable ('my_wondow100');
execute dbms_scheduler.run_job('my_emp_job2');

col comments format a35
col table_name format a35

select	table_name, comments from dictionary
where	table_name like 'DBA%SCHEDULER%'
order	by table_name;

오라클_스케줄러를_이용한_백업정책.pdf

DBMS_JOB.SUBMIT (
     job       OUT BINARY_INTEGER,
     what      IN  VARCHAR2,
     next_date IN  DATE DEFAULT sysdate,     -- 실행할 시각
     interval  IN  VARCHAR2 DEFAULT 'null',  -- 다음 실행될 시점을 계산할 수식
     no_parse  IN  BOOLEAN DEFAULT FALSE,
     instance  IN  BINARY_INTEGER DEFAULT any_instance,
     force     IN  BOOLEAN DEFAULT FALSE);

select trunc(sysdate, 'D') + 7 from dual;

...
next_date=>to_date('2007102701','YYYYMMDDHH24'),
interval=>'sysdate + 7'
...

-- 매월1일 새벽 0시 작업 실행
...
next_date=>add_months(trunc(sysdate,'MM'),1),
interval=>'add_months(trunc(sysdate,''MM''),1)'


-- 매월 말일 밤 11시에 작업 실행
...
next_date=>last_day(trunc(sysdate))+23/24,
interval=>'last_day(trunc(sysdate)+1)+23/24'  -- 말일+1일은 다음달 1일
...

...
interval=>'trunc(sysdate) + decode(to_char(sysdate,''D''), ''6'', 3, ''7'', 2, 1) + 22/24'
...

VARIABLE jobno NUMBER 
BEGIN 
	DBMS_JOB.SUBMIT
	(
		:jobno, 
		'SP_IN_EMP_SAL;', 
		SYSDATE, 
		'SYSDATE + 1'
	); 
COMMIT; 
END; 
/ 

 
BEGIN 
	DBMS_JOB.REMOVE(14443); 
END; 
/  

 
BEGIN 
 DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3'); 
 END; 
 / 

 
BEGIN 
 DBMS_JOB.WHAT(14144, 
     'SP_IN_EMP_SAL;'); 
END; 
/ 

 
BEGIN 
 DBMS_JOB.NEXT_DATE(14144, TRUNC(SYSDATE,'MI') + 4); 
END; 
/ 

 
BEGIN 
 DBMS_JOB.INTERVAL(14144, TRUNC(SYSDATE,'MI')+30/1440); 
END; 
/ 

 
BEGIN 
 DBMS_JOB.BROKEN(14144, TRUE); 
END; 
/

 
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN 
FROM DBA_JOBS; 

JOB    NEXT_DATE  NEXT_SEC  FAILURES   B 
------- ---------  --------   --------   - 
9125    01-JUN-01   00:00:00     4       N 
14144   24-OCT-01   16:35:35    0       N 
9127    01-JUN-01   00:00:00    16       Y 
3 rows selected. 

   
--수행될 잡의 목록이 들어갈 테이블 
CREATE TABLE JOB_LIST 
(
	JOB_ID VARCHAR2(2), 
	JOB_TYPE VARCHAR2(1), 
	JOB_NAME VARCHAR2(30), 
	JOB_EXEC_HOUR VARCHAR(2), 
	JOB_PARENTID VARCHAR2(2), 
	CONSTRAINTS JOB_LIST_PK PRIMARY KEY(JOB_ID) 
	USING INDEX 
	TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 32K NEXT 32K PCTINCREASE 0) 	NOLOGGING) 
	TABLESPACE CYS_DATA PCTFREE 0 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0
); 
/   

CREATE UNIQUE INDEX JOB_LIST_IDX01 ON JOB_LIST(JOB_NAME,JOB_EXEC_HOUR) 
TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 0); 
/ 

-잡 수행 히스토리 테이블로 하루에 한번씩 JOB_LIST 테이블에서 LOG테이블로 JOB_LIST가 복사된다. 
CREATE TABLE JOB_LOG 
(
	JOB_ID VARCHAR2(2), 
	JOB_EXEC_DATE VARCHAR2(8), 
	JOB_START_TIME DATE, 
	JOB_END_TIME DATE, 
	JOB_DATASTART_TIME DATE, 
	JOB_DATAEND_TIME DATE, 
	JOB_STATUS VARCHAR2(1), 
	JOB_ERR_MSG VARCHAR2(100), 
	CONSTRAINTS JOB_LOG_PK PRIMARY KEY(JOB_ID,JOB_EXEC_DATE) USING INDEX
	TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0) NOLOGGING, 
	CONSTRAINTS JOB_LIST_FK FOREIGN KEY(JOB_ID) REFERENCES JOB_LIST(JOB_ID)) 
	TABLESPACE CYS_DATA STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) 

 --JOB_ID를 부여하기 위한 시퀀스 
CREATE SEQUENCE JOB_NUM START WITH 1 INCREMENT BY 1 ;

--하루에 한번 DBMS_JOB을 이용하여 JOB_LIST의 JOB들을 JOB_LOG에 INSERT하기 위한 프로시져 
--다음날 수행할 JOB을 LOG로 넣는다. 
CREATE OR REPLACE PROCEDURE SP_IN_JOB_LOG(V_INDATE IN VARCHAR2 DEFAULT NULL) 
AS 
BEGIN 
	INSERT INTO JOB_LOG SELECT JOB_ID, 
		NVL(V_INDATE,TO_CHAR(SYSDATE+1,'YYYYMMDD')), 
		NULL, 
		NULL, 
		NULL, 
		NULL, 
		'N', 
		NULL 
		FROM JOB_LIST; 
	
	COMMIT; 
END SP_IN_JOB_LOG; 

--SP_IN_JOB_LOG 프로시저를 DBMS_JOB에 등록한다. 
DECLARE 
    JOB_NUMBER NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호 
        'SP_IN_JOB_LOG;',  --프로시저명 
         TO_DATE('20050208180000','YYYYMMDDHH24MISS'),  --NEXT_DATE 
        'TRUNC(SYSDATE,''MI'')+1');  --잡 수행 간격(매일 정각 6시) 
END; 

--JOB_LIST에 수행할 프로시져(JOB)을 등록 
--DBA_USERS테이블을 이용해서 24개의 로우를 만들어 낸 후 카테시안 프러덕을 이용 
INSERT INTO JOB_LIST 
SELECT JOB_NUM.NEXTVAL JOB_ID, 
    JOB_TYPE, 
    JOB_NAME, 
    B.CNT JOB_EXEC_HOUR, 
    NULL 
FROM( 
  SELECT NULL JOB_ID, 
      'F' JOB_TYPE, 
      'SP_IN_F_SALE_SUM' JOB_NAME, 
      NULL JOB_EXEC_HOUR, 
      NULL 
  FROM DUAL) A, 
 ( SELECT LPAD(ROWNUM-1,2,'0') CNT FROM DBA_TABLES 
     WHERE ROWNUM<25) B 

COMMIT; 

--JOB_LIST를 JOB_LOG로 INSERT(현재 SP_IN_JOB_LOG 가 다음일을 INSERT하도록 되어 있으므로 해당일을 넣어줌) 
EXEC SP_IN_JOB_LOG('20050208'); 

 --제대로 들어갔는지 확인 
SELECT * FROM JOB_LOG; 

 --SOURCE 테이블을 시간단위로 섬머리 해서 TARGET 테이블로 적재하기 위한 프로시져 
--ERROR없이 매 시간 돌 때는 파라미터 없이 SP_IN_F_SALE_SUM으로 수행 되고 수동으로 어떤 데이터의 
--범위를 적재해야 할 경우 시간의 범위를 파라미터로 넘겨줌 
CREATE OR REPLACE PROCEDURE SP_IN_F_SALE_SUM(V_STARTTIME IN VARCHAR2 DEFAULT NULL, V_ENDTIME IN VARCHAR2 DEFAULT NULL) 
AS 
  D_STARTTIME DATE; 
  D_ENDTIME DATE; 
  V_ERR_MSG VARCHAR2(100); 
BEGIN 
  
 --프로시저가 파라미터 값이 없이 수행될 경우 
 IF V_STARTTIME IS NULL AND V_ENDTIME IS NULL THEN 
  SELECT NVL(JOB_DATAEND_TIME,TRUNC(SYSDATE-1/24,'HH24')) 
  INTO D_STARTTIME 
  FROM JOB_LOG 
  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
             WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
       AND JOB_EXEC_HOUR=TO_CHAR(SYSDATE-1/24,'HH24')) 
  AND JOB_EXEC_DATE=TO_CHAR(SYSDATE,'YYYYMMDD') 
  AND JOB_STATUS='Y'; 

  D_ENDTIME:=TRUNC(SYSDATE,'HH24'); 
 ELSE 
  SELECT NVL(JOB_DATAEND_TIME,TO_DATE(V_STARTTIME,'YYYYMMDDHH24MISS')) 
  INTO D_STARTTIME 
  FROM JOB_LOG 
  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
             WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
       AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2)) 
  AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8) 
  AND JOB_STATUS='Y'; 

  D_ENDTIME:=TO_DATE(V_ENDTIME,'YYYYMMDDHH24MISS'); 
 END IF; 
    
 --수행되는 프로시저의 START시간을 찍어주고 RUNNING으로 표시 
 UPDATE JOB_LOG 
 SET JOB_START_TIME=SYSDATE, 
  JOB_STATUS='R' 
 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
         WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
      AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2)) 
 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
  
 -- DML------ 
  
  --수행되어질 INSERT문 
  
 -- DML----- 
  
 COMMIT; 
  
 --프로시저가 ERROR없이 수행이 끝나면 END 시간과 가져온 데이터의 범위를 찍어줌 
 UPDATE JOB_LOG 
 SET JOB_END_TIME=SYSDATE, 
  JOB_DATASTART_TIME=D_STARTTIME, 
  JOB_DATAEND_TIME=D_ENDTIME, 
  JOB_STATUS='Y' 
 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
         WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
      AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24')) 
 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
  
 COMMIT; 
  
EXCEPTION 
   WHEN OTHERS THEN 
      V_ERR_MSG:= SUBSTRB(SQLERRM, 1, 80); 
     
     UPDATE JOB_LOG 
     SET JOB_END_TIME=SYSDATE, 
      JOB_STATUS='E', 
      JOB_ERR_MSG=V_ERR_MSG 
           WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
                  WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
         AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24')) 
           AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
     
     COMMIT; 
END SP_IN_F_SALE_SUM; 
/ 

 --SP_IN_F_SALE_SUM 프로시저를 DBMS_JOB에 등록합니다. 
DECLARE 
    JOB_NUMBER NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호 
        'SP_IN_F_SALE_SUM;',  --프로시저명 
         TO_DATE('20050209000000','YYYYMMDDHH24MISS'),  --NEXT_DATE 
        'TRUNC(SYSDATE,''MI'')+1/24');  --잡 수행 간격(매시간 정각) 
END;