일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
Tags
- ReactJS
- Spring
- devops
- Spring Batch
- JVM
- jenkins
- Gradle
- Spring Boot
- Web Server
- MySQL
- AWS
- 요리
- db
- 맛집
- laravel
- linux
- Git
- IntelliJ
- php
- tool
- javascript
- Design Patterns
- ubuntu
- jsp
- it
- java
- springboot
- redis
- elasticsearch
- Oracle
Archives
- Today
- Total
아무거나
[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;
반응형
'Data Store > DB' 카테고리의 다른 글
[Oracle] sysdate가 년월일만 표시되는 현상 (0) | 2020.03.21 |
---|---|
데이터 표준화 (0) | 2020.03.20 |
[mysql] limit와 offset 순서 차이 (0) | 2019.12.22 |
[mysql] 컬럼 생성시에 unsigned 선언의 의미 (0) | 2019.12.22 |
Isolation Level (0) | 2019.12.22 |
Comments