[oracle] 기본 테이블 설계

간단한 로그를 저장하는 테이블을 설계해보자.

 

1. SEQUENCE

-- MySQL과 달리 Oracle에서는 Auto Increment 선언을 직접 해줘야 한다. 그것을 SEQUENCE 라는 것을 생성하여 공통객체로 관리한다.
CREATE SEQUENCE LOG_NO_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999;
ALTER SEQUENCE LOG_NO_SEQ CYCLE;

-- SEQUENCE 증가 (YYYYMMDD + 14자리)
-- 최대한 중복을 방지하기 위하여 아래와 같은 형식으로 CYCLE을 돌게함.
SELECT TO_NUMBER(CONCAT(TO_CHAR(SYSDATE, 'YYYYMMDD'), LPAD(LOG_NO_SEQ.NEXTVAL, 14, 0))) AS LOG_NO_SEQ FROM DUAL;

 

2. TABLE

-- 테이블 생성문
-- TABLESPACE는 TEST로 가정하고 추가함
CREATE TABLE TB_TEST (
    LOG_NO NUMBER NOT NULL,
    SVC_NM VARCHAR2(200) DEFAULT NULL,
    DB_NM VARCHAR2(200) NOT NULL,
    TBL_NM VARCHAR2(200) DEFAULT NULL,
    PROCDR_NM VARCHAR2(200) DEFAULT NULL,
    CALL_URL VARCHAR2(2000) NOT NULL,    
    CALL_MTHD_SP_VAL VARCHAR2(15) NOT NULL,    
    CALL_PARA_VAL VARCHAR2(2000) DEFAULT NULL,    
    EXEC_TME NUMBER DEFAULT 0,        
    LOG_DESC VARCHAR2(4000) DEFAULT NULL, 
    INPT_DM DATE DEFAULT SYSDATE
)
LOGGING
TABLESPACE TEST;

-- 테이블 코멘트 추가
COMMENT ON COLUMN "TB_TEST"."LOG_NO" IS '로그번호';
COMMENT ON COLUMN "TB_TEST"."SVC_NM" IS '서비스명';
COMMENT ON COLUMN "TB_TEST"."DB_NM" IS 'DB명';
COMMENT ON COLUMN "TB_TEST"."TBL_NM" IS '테이블명';
COMMENT ON COLUMN "TB_TEST"."PROCDR_NM" IS '프로시져명';
COMMENT ON COLUMN "TB_TEST"."CALL_URL" IS '호출URL';
COMMENT ON COLUMN "TB_TEST"."CALL_MTHD_SP_VAL" IS '호출방법구분값';
COMMENT ON COLUMN "TB_TEST"."CALL_PARA_VAL" IS '호출매개변수값';
COMMENT ON COLUMN "TB_TEST"."EXEC_TME" IS '실행시간 (단위:m/s)';
COMMENT ON COLUMN "TB_TEST"."LOG_DESC" IS '로그설명';
COMMENT ON COLUMN "TB_TEST"."INPT_DM" IS '입력일시';

-- 테스트 데이터 INSERT 문
INSERT INTO "TB_TEST" (LOG_NO, SVC_NM, DB_NM, TBL_NM, CALL_URL, CALL_MTHD_SP_VAL, CALL_PARA_VAL, EXEC_TME, LOG_DESC) VALUES (TO_NUMBER(CONCAT(TO_CHAR(SYSDATE, 'YYYYMMDD'), LPAD(OOADM.LOG_NO_SEQ.NEXTVAL, 14, 0))), 'oyez', 'OYOOPRD1', 'TB_ST_STR_STK_SCRP_L', 'http://test.com/api/tests', 'POST', '{"productDisposalList":[{"strCd":"DB67","scrpYmd":"20200121","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉2222","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1},{"strCd":"DB67","scrpYmd":"20200120","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1}],"gUserId":999999}', '1200', '등록');
INSERT INTO "TB_TEST" (LOG_NO, SVC_NM, DB_NM, PROCDR_NM, CALL_URL, CALL_MTHD_SP_VAL, CALL_PARA_VAL, EXEC_TME, LOG_DESC) VALUES (TO_NUMBER(CONCAT(TO_CHAR(SYSDATE, 'YYYYMMDD'), LPAD(OOADM.LOG_NO_SEQ.NEXTVAL, 14, 0))), 'oyez', 'OYOOPRD1', 'TB_ST_STR_STK_SCRP_L', 'http://test.com/api/tests', 'POST', '{"productDisposalList":[{"strCd":"DB67","scrpYmd":"20200121","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉2222","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1},{"strCd":"DB67","scrpYmd":"20200120","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1}],"gUserId":999999}', '1200', '등록');

 

 

3. SYNONYM

-- SYNONYM 지정
-- TESTDB.TB_TEST 를 TB_TEST로만 호출이 가능하다.
CREATE SYNONYM TB_TEST FOR TESTDB.TB_TEST;

 

4. Primary Key

-- PK 생성
CREATE UNIQUE INDEX PK_CM_OP_L ON TB_CM_OP_L (LOG_NO)
LOGGING
TABLESPACE TEST_UNIQUE_INDEX;

CREATE INDEX IX_CM_OP_L_01 ON TB_CM_OP_L(INPT_DM)
LOGGING
TABLESPACE TEST_INDEX;

 

 

5. GRANT

-- 권한추가 필요
GRANT SELECT ON TB_CM_OP_L TO USER;
GRANT INSERT ON TB_CM_OP_L TO USER;
GRANT DELETE ON TB_CM_OP_L TO USER;