[ORACLE] 컬럼 관련 쿼리

 

 

컬럼 추가

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE() DATA OPTIONS CONSTRAINT_OPTION

-EX)
ALTER TABLE EMP ADD BONUS NUMBER(3) DEFAULT '50'

 

컬럼 삭제

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME

 

 

 

컬럼 데이터 타입, 길이 변경

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE( ) ;

 

컬럼명 변경

ALTER TABLE TABLE_NAME RENAME COLUMN 현재컬럼명 TO 새로운 컬럼명

 

 

컬럼 DEFAULT, NOT NULL 변경

DEFAULT

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE() DEFAULT '1000'

--EX)
ALTER TABLE EMP MODIFY BOUNS NUMBER(10) DEFAULT '100'

 

 

NOT NULL 

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE() NOT NULL

 

NULL 

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE() NULL

 

기본값 + NOT NULL

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATA_TYPE() DEFAULT '1000' NOT NULL

 

 

컬럼명 가져오기

SELECT COLUMN_NAME
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME =  'TABLE_NAME' 
   AND OWNER = 'USERID' 

--EX)

SELECT COLUMN_NAME
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME =  'EMP' 
   AND OWNER = 'SCOTT' 

 

디폴트 값을 VARCHAR2 (=텍스트)로 받아오는 방법

SELECT 'N'  AS DML_CHK                       ,  
       'N'  AS DML_ADJ                       ,  
       CL.COLUMN_NAME    AS COLUMN_NAME      ,  
       CL.COLUMN_ID         AS COLUMN_ID     ,  
       CL.DATA_TYPE          AS DATA_TYPE    ,  
       CL.DATA_LENGTH      AS DATA_LENGTH    ,  
       CL.NULLABLE            AS NULLABLE    ,  
       EXTRACTVALUE ( DBMS_XMLGEN.GETXMLTYPE('SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ''' || CL.TABLE_NAME || ''' AND COLUMN_NAME = ''' || CL.COLUMN_NAME || '''' ), '//text()' ) AS DATA_DEFAULT,    
       CC.COMMENTS         AS DS_COMMENTS    , 
       CL.LAST_ANALYZED    AS LAST_ANALYZED
 FROM ALL_COL_COMMENTS CC , COLS CL 
WHERE CC.TABLE_NAME = 'TABLE_NAME'
    AND CC.OWNER =  'USERID'
    AND CC.TABLE_NAME = CL.TABLE_NAME  
    AND CC.COLUMN_NAME = CL.COLUMN_NAME 
 ORDER BY CL.COLUMN_NAME

EXTRACTVALUE ( DBMS_XMLGEN.GETXMLTYPE('SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ''' || CL.TABLE_NAME || ''' AND COLUMN_NAME = ''' || CL.COLUMN_NAME || '''' ), '//text()' ) AS DATA_DEFAULT,

 

>해당 SELECT 문을 GETXMLTYPE을 통해 XML 타입으로 변환하고 변환한것을 EXTRACTVALUE 를 사용해 text로 다시 변환해주면 varchar2가 된다.