Oracle alter table add column 예제

 

alter table

   table_name

add

   (

   column1_name column1_datatype column1_constraint,  

   column2_name column2_datatype column2_constraint

   );

 

alter table

   tb_cust

add

   cust_type  varchar2(2) NOT NULL;

 

ALTER TABLE 

   cust_table 

ADD 

   (

      cust_type            char(2) NOT NULL,

      cust_num             number

   );

 

SQL> alter table author add (last_update_date date);

 

 

SELECT A1.TABLE_COMMENTS TABLE_COMMENTS      , A1.TABLE_NAME TABLE_NAME      , A1.COLUMN_COMMENTS COLUMN_COMMENTS      , A1.COLUMN_NAME COLUMN_NAME      , (CASE            WHEN B1.CONSTRAINT_TYPE = 'P'               THEN 'Y'         END) PK_FLAG      , (CASE            WHEN B1.CONSTRAINT_TYPE = 'R'               THEN 'Y'         END) FK_FLAG      , A1.NULL_FLAG      , A1.DATA_TYPE      , A1.DATA_LENGTH   FROM (SELECT B.COMMENTS TABLE_COMMENTS              , A.TABLE_NAME TABLE_NAME              , C.COMMENTS COLUMN_COMMENTS              , A.COLUMN_NAME COLUMN_NAME              , (CASE A.NULLABLE                    WHEN 'Y'                       THEN 'Y'                 END) NULL_FLAG              , A.DATA_TYPE DATA_TYPE              , (CASE                    WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')                       THEN '(' || A.DATA_LENGTH || ')'                    WHEN A.DATA_TYPE = 'NUMBER'                    AND A.DATA_SCALE = 0                    AND A.DATA_PRECISION IS NOT NULL                       THEN '(' || A.DATA_PRECISION || ')'                    WHEN A.DATA_TYPE = 'NUMBER'                    AND A.DATA_SCALE <> 0                       THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE                            || ')'                 END                ) DATA_LENGTH              , A.COLUMN_ID           FROM USER_TAB_COLUMNS A              , USER_TAB_COMMENTS B              , USER_COL_COMMENTS C          WHERE (A.TABLE_NAME = B.TABLE_NAME)            AND (    A.TABLE_NAME = C.TABLE_NAME                 AND A.COLUMN_NAME = C.COLUMN_NAME                )) A1      , (SELECT A.TABLE_NAME              , A.COLUMN_NAME              , B.CONSTRAINT_TYPE           FROM USER_CONS_COLUMNS A              , USER_CONSTRAINTS B          WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)            AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1 WHERE (    A1.TABLE_NAME = B1.TABLE_NAME(+)         AND A1.COLUMN_NAME = B1.COLUMN_NAME(+)) ORDER BY A1.TABLE_NAME, A1.COLUMN_ID

SELECT X.TABLE_NAME          ,X.TABLE_COMMENTS          ,X.COLUMN_NAME          ,X.COLUMN_COMMENTS          ,X.PK_FLAG          ,X.FK_FLAG          ,X.NULL_FLAG          ,X.DATA_TYPE || X.DATA_LENGTH as COLUMN_DATA_TYPE FROM      (SELECT    A1.TABLE_NAME TABLE_NAME                     ,A1.TABLE_COMMENTS TABLE_COMMENTS                     ,A1.COLUMN_NAME COLUMN_NAME                     ,A1.COLUMN_COMMENTS COLUMN_COMMENTS                     ,(CASE                         WHEN B1.CONSTRAINT_TYPE = 'P' THEN 'Y'                       END)                        PK_FLAG                     ,(CASE                         WHEN B1.CONSTRAINT_TYPE = 'R' THEN 'Y'                       END)                        FK_FLAG                     ,A1.NULL_FLAG                     ,A1.DATA_TYPE                     ,A1.DATA_LENGTH                     ,A1.COLUMN_ID            FROM      (SELECT    B.COMMENTS TABLE_COMMENTS                                ,A.TABLE_NAME TABLE_NAME                                ,C.COMMENTS COLUMN_COMMENTS                                ,A.COLUMN_NAME COLUMN_NAME                                ,(CASE A.NULLABLE                                    WHEN 'Y' THEN 'Y'                                  END)                                   NULL_FLAG                                ,A.DATA_TYPE DATA_TYPE                                ,(CASE                                    WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')                                    THEN                                      '(' || A.DATA_LENGTH || ')'                                    WHEN A.DATA_TYPE = 'NUMBER'                       AND               A.DATA_SCALE = 0                       AND               A.DATA_PRECISION IS NOT NULL                                    THEN                                      '(' || A.DATA_PRECISION || ')'                                    WHEN A.DATA_TYPE = 'NUMBER'                       AND               A.DATA_SCALE <> 0                                    THEN                                      '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'                                  END)                                   DATA_LENGTH                                ,A.COLUMN_ID                       FROM      USER_TAB_COLUMNS A                                ,USER_TAB_COMMENTS B                                ,USER_COL_COMMENTS C                       WHERE     (A.TABLE_NAME = B.TABLE_NAME)                       AND       (A.TABLE_NAME = C.TABLE_NAME                       AND        A.COLUMN_NAME = C.COLUMN_NAME --                     AND       A.TABLE_NAME like 'DIM%'  /* [조건] 원하는 테이블만 조회 한다. */                                 )) A1                     ,(SELECT    A.TABLE_NAME, A.COLUMN_NAME, B.CONSTRAINT_TYPE                       FROM      USER_CONS_COLUMNS A                                ,USER_CONSTRAINTS B                       WHERE     (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)                       AND       B.CONSTRAINT_TYPE IN ('P', 'R')) B1            WHERE     (A1.TABLE_NAME = B1.TABLE_NAME(+)            AND        A1.COLUMN_NAME = B1.COLUMN_NAME(+))) X ORDER BY  X.TABLE_NAME, X.COLUMN_ID 출처: https://estenpark.tistory.com/334 [DATA 전문가로 가는 길]