readme.md

테이블 PK 함께 추가

		CREATE TABLE HRM_IMSL_BASE_MNGM
		(
		  DIV1_CD VARCHAR2(20) NOT NULL,
		  DIV2_CD VARCHAR2(20) NOT NULL,
		  DIV3_CD VARCHAR2(20) NOT NULL,
		  VAL1 VARCHAR2(20),
		  VAL2 VARCHAR2(20),  
		  USE_YN VARCHAR2(20),
		  INS_ID VARCHAR2(20),
		  INS_DT DATE,
		  UPT_ID VARCHAR2(20),
		  UPT_DT DATE
		, CONSTRAINT HRM_IMSL_BASE_MNGM_PK PRIMARY KEY
		  (
		    DIV1_CD,
		    DIV2_CD,
			DIV3_CD
		  )
		  ENABLE
		);

인덱스(index) 생성

	
		예)
		  CREATE INDEX "IBS"."IDX_HRM_TRP_MST_01" ON "IBS"."HRM_TRP_MST" ("LCTR_RQST_NO") 
		  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
		  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
		  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
		  TABLESPACE "TS_IBS_IDX" 
		  ;

테이블 컬럼 추가

		ALTER TABLE 테이블명 ADD (컬럼명 자료형)
		ALTER TABLE HRM_EXPT_PRQ ADD (ADVC_RQST VARCHAR(10))

테이블 컬럼 명 변경

		ALTER TABLE HRM_OUTS_PRUS_RQST RENAME COLUMN OUTS_HPWR_NO TO OSDR_NO

테이블 컬럼 코멘트 조회

		SELECT T.COLUMN_NAME, T.COLUMN_ID, C.COMMENTS
		FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C
		WHERE T.TABLE_NAME = C.TABLE_NAME
		AND T.COLUMN_NAME = C.COLUMN_NAME
		AND T.TABLE_NAME = 'COM_STD_MGT'
		ORDER BY T.COLUMN_ID ASC

테이블 컬럼 크기 수정

		ALTER TABLE USERS MODIFY(NAME2 VARCHAR2(40));   -- 컬럼 수정할시 크기확인--

테이블 컬럼 삭제

		ALTER TABLE USERS DROP COLUMN NAME3;

TIMESTAMP

		SELECT * FROM AS OF TIMESTAMP(SYSDATE - INTERVAL '1' MINUTE)
		  FROM 테이블명

오라클 테이블 복사

 -- 테이블 복사(데이터 포함)
			CREATE TABLE 생성할 테이블명 AS SELECT * FROM 복사할 테이블명(WHERE 절)
 -- 테이블 구조만 복사
			CREATE TABLE 생성할 테이블명 AS SELECT * FROM 복사할 테이블명 WHERE 1 = 2

오라클 PK 삭제

		ALTER TABLE TABLE명 DROP CONSTRAINT PK명

오라클 PK 추가

		ALTER TABLE TABLE명 ADD CONSTRAINT PK명 PRIMARY KEY(COLUMN 명 )

오라클 FK 추가

		ALTER TABLE 테이블명
		ADD CONSTRAINT FK_테이블명
		FOREIGN KEY(FK)
		REFERENCES 참조테이블명(FK)

오라클 COLUMN 추가 시간

		SELECT * FROM USER_TAB_COLUMNS A
		WHERE A.TABLE_NAME = '테이블명'

오라클 테이블 수정 시간 LAST DDL TIME

		SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME = '테이블명'

오라클 SELECT UPDATE / MERGE INTO

		UPDATE
		/*+ bypass_ujvc */
		(SELECT A.RQST_EMP_NO AS RQST_EMP_NO,
		              A.RQST_DEPT_CD AS RQST_DEPT_CD,
		              A.RQST_GRD_CD AS RQST_GRD_CD,
		              A.RQST_JOB_POST AS RQST_JOB_POST,
		              A.RQST_TEL_NO AS RQST_TEL_NO,
		              A.INS_ID AS INS_ID,
		              FN_GET_DEPT_CD(INS_ID) AS DEPT_CD,
		              FN_GET_GRD_CD(INS_ID) AS GRD_CD,
		              (SELECT OFFC_TEL_NO FROM VI_SYS_USER
		              WHERE USER_ID = A.INS_ID) AS OFFC_TEL_NO,
		              (SELECT JOB_POST FROM VI_SYS_USER
		              WHERE USER_ID = A.INS_ID) AS JOB_POST
		    FROM  HRM_EXTR_LCTR_MST_20170828_01 A
		  WHERE A.RQST_EMP_NO <> A.INS_ID
		    )
		SET RQST_EMP_NO = INS_ID
		     , RQST_DEPT_CD = DEPT_CD
		     , RQST_GRD_CD = GRD_CD
		     , RQST_JOB_POST = JOB_POST
		     , RQST_TEL_NO = OFFC_TEL_NO
		     
     
     
		MERGE INTO HRM_EXTR_LCTR_MST_20170629 A
		USING (
		        SELECT AA.RQST_NO, BB.EMP_NO, BB.GRD_CD, BB.DEPT_CD, BB.JOB_POST, CC.OFFC_TEL_NO
		          FROM HRM_EXTR_LCTR_MST_20170629 AA
		          JOIN HRM_BAS_DTL BB ON AA.INS_ID = BB.EMP_NO
		          JOIN VI_SYS_USER CC ON AA.INS_ID = CC.USER_ID
		         WHERE AA.RQST_EMP_NO <> AA.INS_ID
		      ) B
		ON (A.RQST_NO = B.RQST_NO)
		WHEN MATCHED THEN
		  UPDATE SET A.RQST_EMP_NO = B.EMP_NO
		           , A.RQST_DEPT_CD = B.DEPT_CD
		           , A.RQST_GRD_CD = B.GRD_CD
		           , A.RQST_JOB_POST = B.JOB_POST
		           , A.RQST_TEL_NO = B.OFFC_TEL_NO
		; 

컬럼 코멘트와 데이터 조회

		SELECT MAX(COL1) AS RQST_NO,
		MAX(COL2) AS RQST_EMP_NO,
		MAX(COL3) AS RQST_DEPT_CD
		 FROM (
		        SELECT
		DECODE(T.COLUMN_NAME,'RQST_NO',C.COMMENTS, '') AS COL1
		,DECODE(T.COLUMN_NAME,'RQST_EMP_NO',C.COMMENTS, '') AS COL2
		,DECODE(T.COLUMN_NAME,'RQST_DEPT_CD',C.COMMENTS, '') AS COL3
		        FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C
		        WHERE T.TABLE_NAME = C.TABLE_NAME
		        AND T.COLUMN_NAME = C.COLUMN_NAME
		        AND T.TABLE_NAME = 'HRM_EXTR_LCTR_MST'
		        ORDER BY T.COLUMN_ID ASC)  A
		UNION ALL
		SELECT RQST_NO    ,
		RQST_EMP_NO    ,
		RQST_DEPT_CD
		 FROM HRM_EXTR_LCTR_MST