[Oracle] 오라클 테이블 가상컬럼 추가 방법 (Virtual Column)

Oracle 11g부터 가상 컬럼( Virtual Column )을 사용할 수 있다. 실제 데이터를 저장하지 않고, 다른 컬럼의 값을 기반으로 계산된 값을 조회 시에 자동으로 생성해 주는 컬럼다. 실제로 INSERT나 UPDATE로 값을 입력할 수 없고, 읽기 전용으로만 사용가능 하다.

 

목차 가상컬럼 추가, 삭제 방법 (ALTER TABLE) 테이블 생성 시 가상컬럼 추가 방법 (CREATE TABLE) 가상컬럼 사용 시 주의사항

 

가상컬럼 추가, 삭제 방법 (ALTER TABLE)

ALTER TABLE emp ADD (total_sal NUMBER GENERATED ALWAYS AS (sal + nvl(comm, 0)));

 

 

위의 예제는 급여(sal)와 커미션(comm) 컬럼의 값을 합쳐서 total_sal 컬럼으로 가상 컬럼을 추가하는 예제이다.

가상 컬럼은 INSERT, UPDATE는 할 수 없고, SELECT 할 때 조회용도로만 사용가능 하다.

 

ALTER TABLE emp
ADD (manager_yn VARCHAR2(1)
       GENERATED ALWAYS AS (CASE WHEN job = 'MANAGER' THEN 'Y' ELSE 'N' END));

 

위의 예제는 직업이 "MANAGER"인 경우 manager_yn 가상 컬럼에 "Y" or "N"을 표시한다.

 

 

위의 예제에서 추가한 가상 컬럼 TOTAL_SAL, MANAGER_YN 컬럼의 값이 조회되는 것을 확인할 수 있다.

 

-- 가상컬럼 삭제
ALTER TABLE emp DROP COLUMN total_sal;

 

ALTER TABLE [테이블명] DROP COLUMN [가상컬럼명] 명령으로 가상 컬럼을 삭제할 수 있다.

 

테이블 생성 시 가상컬럼 추가 방법 (CREATE TABLE)

CREATE TABLE emp 
( 
    empno       NUMBER(4)	NOT NULL,
    ename       VARCHAR2(10),
    job         VARCHAR2(9),
    mgr         NUMBER(4),
    hiredate    DATE,
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(2),	
    total_sal   NUMBER GENERATED ALWAYS AS (sal + nvl(comm, 0)) --가상컬럼
);

 

CREATE TABLE 구문으로 테이블을 생성할 때 가상 컬럼을 추가하여 생성할 수 있다.

 

가상컬럼 사용 시 주의사항

• DML로 직접 값을 INSERT/UPDATE 불가
• 다른 가상 컬럼을 기반으로 정의할 수 없음
• SYSDATE, SYSTIMESTAMP, SEQUENCE, CURRVAL, NEXTVAL, USER, ROWNUM 등 비결정성 함수 사용 불가
• LOB 타입 사용 불가
• 디폴트 값으로 지정 불가
• LONG, BLOB, CLOB, NCLOB 타입 사용 불가