오라클 가상컬럼(Oracle Virtual Columns)

 

오라클 가상컬럼(Oracle Virtual Columns)

오라클 11g에서 새로 소개된 가상 컬럼(Virtual Column)은 다른 컬럼 값 또는 다른 표현식을 사용하여 값이 자동으로 계산되는 테이블의 가상 컬럼 입니다. 다른 컬럼의 값들을 이용하여 새로운 값을 만들어 내는 경우에 유용하게 사용될 수 있습니다.

[기본형식]

COLUMN_NAME [DATATYPE] [GENERATED ALWAYS] AS [EXPRESSION] [VIRTUAL] COLUMN_NAME : 가상 컬럼의 이름 DATATYPE: 가상 컬럼의 데이터 유형을 지정합니다. 생략하면 가상 컬럼은 표현식 결과의 데이터 유형을 사용합니다. AS EXPRESSION: AS 키워드뒤에 표현식을 기술 합니다. 이 식의 값이 가상컬럼에 대입됩니다. GENERATED ALWAYS, VIRTUAL : 구문의 명확성을 위해 기술 합니다. 생략가능

일반 컬럼과 유사하지만 다음과 같은 차이가 있습니다.

- 표현식등에 의해 정의.(사원 테이블에서 급여와 수당 컬럼을 이용하여 연봉을 계산)

- DB에 저장되지는 않으며 실행 중에 계산됩니다..

- UPDATE, DELETE의 WHERE절에 나타날 수 있지만 컬럼값 자체에 대한 변경은 불가능 합니다.

실습

가상컬럼을 이용하여 테이블을 생성하고 생성된 가상칼럼을 확인해 보겠습니다.

다음과 같은 조건으로 EMP10 테이블을 생성하세요. 사번(empno) number, primary key 성명(ename) varchar2(50) 급여(pay) number 연봉(annaul_income) : pay * 10 표현식을 기반으로 가상컬럼으로.

CREATE TABLE EMP10 (

  EMPNO NUMBER CONSTRAINTS PK_EMP10 PRIMARY KEY,

  ENAME VARCHAR2(50),

  PAY NUMBER,

  ANNUAL_INCOME NUMBER GENERATED ALWAYS AS (PAY * 12) VIRTUAL

);

<실행결과>

Table EMP10이(가) 생성되었습니다.

생성된 EMP10 테이블의 컬럼들을 조회해 보겠습니다.

SELECT  COLUMN_NAME

              , DATA_TYPE

              , DATA_LENGTH

              , DATA_DEFAULT

              , VIRTUAL_COLUMN

 FROM    USER_TAB_COLS

 WHERE TABLE_NAME = 'EMP10';

\<실행결과>

	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_DEFAULT	VIRTUAL_COLUMN
1	EMPNO	NUMBER	22		NO
2	ENAME	VARCHAR2	50		NO
3	PAY	NUMBER	22		NO
4	ANNUAL_INCOME	NUMBER	22	PAY*12	YES

실습

생성한 EMP10 테이블에 EMP 테이블의 컬럼 및 데이터를 근간으로 값을 입력합니다. 단 annual_income 컬럼에는 값을 입력하지 않습니다. 다른 컬럼값(pay)에 의해 자동으로 생성되니까요. INSERT 후 EMP10 테이블의 데이터를 조회 합니다.

EMP10 테이블에 데이터를 입력합니다. EMP 테이블의 empno, ename, sal 컬럼을 EMP10 테이블의 empmno, ename, pay 컬럼으로 입력 합니다.

 INSERT INTO EMP10 (EMPNO, ENAME, PAY)

 SELECT EMPNO, ENAME, SAL FROM EMP;

<실행결과>

14개 행 이(가) 삽입되었습니다.

EMP10 테이블의 전체 데이터를 조회 합니다. annual_income 컬럼에 값이 생성 되어 있는것을 확인 하세요.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME	PAY	ANNUAL_INCOME
1	7369	SMITH	800	9600
...	...	...	...	...
13	7902	FORD	3000	36000
14	7934	MILLER	1300	15600

실습

EMP10 테이블에 가상컬럼을 추가해 보겠습니다. 컬럼명 뒤에 AS 온다는 사실 항상 기억하세요. 가상컬럼의 데이터타입은 별도로 가술하지 않으면 표현식의 데이터타입을 따라갑니다.

EMP10 테이블에 급여등급(grade) 칼럼을 가상컬럼으로 추가 하세요. pay<= 1190 dlaus ‘Low’, pay> 1200 AND pay<= 2500 이면 ‘Normal’, pay> 2500 이면 ‘High’ 값을 갖는 컬럼 입니다.

ALTER TABLE EMP10 ADD

(

    GRADE AS (

            CASE 

                WHEN PAY <= 1190 THEN 'Low' 

                WHEN PAY > 1200 AND PAY <= 2500 THEN 'Normal'

                WHEN PAY > 2500 THEN 'High'

            END)

);

<실행결과>

Table EMP10이(가) 변경되었습니다.

EMP10 테이블의 전체 데이터를 조회 합니다. grade컬럼의 값을 확인 하세요.

SELECT * FROM EMP10;

<실행결과>

	EMPNO	ENAME	PAY	ANNUAL_INCOME	GRADE
1	7369	SMITH	800	9600	Low
...	...	...	...	...
13	7902	FORD	3000	36000	High
14	7934	MILLER	1300	15600	Normal

 

#가상컬럼, #가상칼럼, #Virtual컬럼, #VirtualColumn, #Oracle강좌, #ORACLE교육