0810_Oracle : 가상컬럼 Virtual Column

가상컬럼 Virtual Column ?

• 디스크에 저장되지 않는다.
• 가상 컬럼은 쿼리, DML, DDL 등을 사용할 수 있다.
• 가상 컬럼에는 INSERT, UPDATE 할 수 없다.
• 다른 테이블의 컬럼을 이용한 가상 컬럼은 생성이 불가하다.
• 가상 컬럼에는 SYSDATE 등 동적인 결과를 출력하는 함수는 사용할 수 없다.

다음 테이블을 생성한다.

-- test2 테이블

hak 문자(30) PRIMARY KEY

name 문자(30) NOT NULL

kor NUMBER(3) NOT NULL

eng NUMBER(3) NOT NULL

mat NUMBER(3) NOT NULL

tot NUMBER(3) 가상컬럼(kor+eng+mat)

ave NUMBER(4, 1) 가상컬럼(kor+eng+mat) / 3

reg_date DATE

CREATE TABLE test2 (
    hak VARCHAR2(30) PRIMARY KEY, -- 숫자로 들어가도 VARCHAR2로 줄 것! 유의
    name VARCHAR2(30) NOT NULL,
    kor NUMBER(3) NOT NULL,
    eng NUMBER(3) NOT NULL,
    mat NUMBER(3) NOT NULL,
    tot NUMBER(3) GENERATED ALWAYS AS (kor + eng + mat) VIRTUAL,
    ave NUMBER(4, 1) GENERATED ALWAYS AS ( (kor + eng + mat) / 3) VIRTUAL,
    -- 총 4자리인데 1은 소수점 한자리
    reg_date DATE
)

SELECT * FROM tab;

INSERT INTO test2(hak, name, kor, eng, mat, reg_date)
    VALUES('11', '호호호', 100, 90, 85, SYSDATE);
SELECT * FROM test2;

INSERT INTO test2 VALUES('22', '너너너', 100, 90, 85, SYSDATE);
    -- 에러. 테이블 뒤에 모든 컬럼이 들어가 있으므로 컬럼과 값의 개수가 일치하지 않는다.
INSERT INTO test2 VALUES('22', '너너너', 90, 90, 90, 270, 90, SYSDATE);
    -- 에러. 가상컬럼은 값을 추가할 수 없다.

다음 테이블을 작성한다.

-- test3 테이블

eno VARCHAR2(30) PRIMARY KEY

name VARCHAR2(30) NOT NULL

pay NUMBER(10) NOT NULL

-- tax는 pay가 3000000이상 일 때 0.03%,

   2000000이상 일 때 0.02%이고 1의 자리에서 절삭한다.

CREATE TABLE test3 (
    eno VARCHAR2(30) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    pay NUMBER(10) NOT NULL,
    tax NUMBER(10) GENERATED ALWAYS AS ( 
    CASE 
        WHEN (pay >= 3000000) then TRUNC(pay * 0.03, -1)
        WHEN (pay >= 2000000) then TRUNC(pay * 0.02, -1)
        ELSE 0 
    END
    ) VIRTUAL
); -- 실무에서는 세금은 매년마다 바뀌기 때문에 이렇게 컬럼으로 추가하면 안된다.

INSERT INTO test3 (eno, name, pay) VALUES('111', '가가가', 3500000);
SELECT * FROM test3; -- 확인

INSERT INTO 테이블명 (컬럼, 컬럼) VALUES (값, 값); 은 한번에 하나의 테이블에 하나의 행만 추가가 가능하다.

서브쿼리를 이용하여 한 번에 여러 행을 추가할 수 있다.

 

테스트를 위해 새롭게 테이블을 만들어 보자.

--구조만 복사, 데이터는 복사되지 않음.
CREATE TABLE emp1 AS
    SELECT empNo, name, dept, pos FROM emp WHERE 1= 0;

DESC emp1;
SELECT * FROM emp1;

SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';

WHERE 절에 1 = 0; 을 줌으로써 항상 false 이므로 값은 가져오지 않고 테이블의 구조만 복사한 것이다. 여기에 밑에 쿼리로 뽑은 개발부의 인원만 추가해 보자.

-- 서브쿼리를 이용하여 한번에 여러행을 추가
INSERT INTO emp1
    SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';

COMMIT;
SELECT * FROM emp1;

SELECT를 이용해서 개발부만 뽑은 문장을 INSERT INTO 추가하고싶은테이블명 다음에 넣어서 간단하게 추가할 수 있다.

서브 쿼리를 이용해서 한 번에 여러 행을 추가할 수 있었다.

 

그럼 한 번에 두 개 이상의 테이블에 여러 행을 추가하려면 어떻게 해야할까 ?

-- 연습할 테이블을 만들어보자
CREATE TABLE emp2 AS
    SELECT empNo, name, dept, pos FROM emp WHERE 1 = 0;
CREATE TABLE emp3 AS
    SELECT empNo, sal, bonus FROM emp WHERE 1 = 0;
-- 구조를 복사해서 테이블을 만들어 줌.

DESC emp2;
DESC emp3;
-- 테이블 구조 확인

-- INSERT ALL 을 이용해서 두 개 이상의 테이블에 여러 행에 추가
INSERT ALL
    INTO emp2 VALUES(empNo, name, dept, pos)
    -- INTO emp2(empNo, name, dept, pos) VALUES(empNo, name, dept, pos)
    INTO emp3 VALUES(empNo, sal, bonus)
    -- INTO emp3(empNo, sal, bonus) VALUES(empNo, name, bonus)
SELECT * FROM emp WHERE dept = '개발부';

-- 개발부의 사람들의 값을 테이블에 넣는다.

COMMIT;

SELECT * FROM emp2;
SELECT * FROM emp3;

INSERT ALL을 이용해서 다음의 데이터를 emp2, emp3에 추가해보자.

empNo '9999' name '너자바' dept '개발부' pos '과장' sal 2500000, bonus 100000

INSERT ALL
    INTO emp2  (empNo, name, dept, pos) VALUES ('9999', '너자바', '개발부', '과장')
    INTO emp3  (empNo, sal, bonus) VALUES ('9999', 2500000, 100000)
SELECT * FROM dual;

SELECT '9999', '너자바', '개발부', '과장', 2500000, 100000 FROM dual;  라고 생각하면 되는 건가! 하고 생각 중 . . .

 

다른 예. . .emp4 에는 남자들만 넣고 emp5 에는 여자들만 넣어보자.

CREATE TABLE emp4 AS
    SELECT empNo, rrn, name, dept, pos FROM emp WHERE 1 = 0;
CREATE TABLE emp5 AS
    SELECT empNo, rrn, name, dept, pos FROM emp WHERE 1 = 0;

INSERT ALL
    WHEN MOD(SUBSTR(rrn, 8, 1), 2) = 1 THEN
        INTO emp4(empNo, rrn, name, dept, pos) VALUES (empNo, rrn, name, dept, pos)
    ELSE
        INTO emp5(empNo, rrn, name, dept, pos) VALUES (empNo, rrn, name, dept, pos)
SELECT * FROM emp;

COMMIT;

SELECT * FROM emp4;
SELECT * FROM emp5;

WHEN ELSE 를 써서 남자만 emp4에 넣고, 여자만 emp5에 넣는다.