개발새발, 써놓기

UNIQUE 제약 조건 ?

• 유일성 제약 조건
• 하나의 테이블에 두 개 이상 만들 수 있다.
• 두 개 이상의 컬럼으로 UNIQUE를 만들 수 있다.
• NULL을 허용하면 NULL추가가 가능하다.

예시를 통해 알아보자.

CREATE TABLE test1 (
    id VARCHAR2(50),
    pwd VARCHAR2(100) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    email VARCHAR2(50),
    CONSTRAINT pk_test1_id PRIMARY KEY(id),
    CONSTRAINT uq_test1_email UNIQUE(email)
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST1';
    -- P : PRIMARY KEY, U : UNIQUE (유일성 제약 조건)
SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST1';

예시용 테이블을 생성하고, 기본키와 UNIQUE 제약조건을 주었다.

CONSTRAINT [제약조건명] 을 통해 기본키와 UNIQUE에 이름을 주어서 SELECT * FROM USER_CONSTRAINT WHERE table_name = 'TEST1'; 을 통해 알아볼 수 있었다. 이 명령어를 통해 보면 CONSTRAINT_TYPE에 P가 PRIMARY KEY를 나타내고, U가 UNIQUE를 나타낸다. C는 CHECK 제약조건.

이 테이블에 데이터를 추가해보자.

INSERT INTO test1(id, pwd, name, email) VALUES ('1','1','1','1');
INSERT INTO test1(id, pwd, name, email) VALUES ('2','2','2','1');
    -- 에러. ORA-00001 : email 유일성(UNIQUE) 제약 위반
INSERT INTO test1(id, pwd, name, email) VALUES ('2','2','2',NULL);
    -- UNIQUE 이어도 NULL을 허용하면 NULL 추가 가능하고 NULL은 중복 가능

email 컬럼에 UNIQUE를 주었기 때문에 위에 추가된 id 1이 가지고 있는 이메일과 중복되어 에러가 뜨고 데이터는 추가되지 않는다.

email에 컬럼에 NOT NULL을 주지 않았기 때문에 NULL은 추가가 가능하다. 

NOT NULL 제약 조건 ?

• NULL을 허용하지 않는다.
• '(길이가 0 인 문자열)' 도 NULL 로 취급한다.

존재하는 테이블에 NOT NULL 제약 조건을 설정하려면

ALTER TABLE 테이블명 MODIFY 컬럼 NOT NULL;

ALTER TABLE 테이블명 ADD [ CONSTRAINT 제약조건 이름] CHECK(컬럼 IS NOT NULL);

ALTER TABLE 명령어를 이용한다.

NOT NULL 제약 조건을 삭제하려면

ALTER TABLE 테이블명 MODIFY 컬럼 NULL;

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름;

을 이용한다.

새롭게 테이블을 만들어서 실습해보자.

CREATE TABLE test1(
    id VARCHAR2(30),
    pwd VARCHAR2(30),
    name VARCHAR2(30),
    email VARCHAR2(30)
);

-- test1 테이블 id에 기본키 추가

ALTER TABLE test1 ADD PRIMARY KEY ( id );
ALTER TABLE test1 ADD CONSTRAINT pk_test1_id PRIMARY KEY ( id );
-- 두 개 다 가능. 제약조건에 이름을 주는 것이 나중에 확인이 편하다.

-- test1 테이블의 email에 UNIQUE 추가

ALTER TABLE test1 ADD UNIQUE (email);
ALTER TABLE test1 ADD CONSTRAINT uq_test1_email UNIQUE (email);

-- test1 테이블의 pwd, name, email  에 NOT NULL 제약 추가

ALTER TABLE test1 MODIFY pwd NOT NULL;
ALTER TABLE test1 MODIFY name NOT NULL;
ALTER TABLE test1 MODIFY email NOT NULL;

ALTER TABLE test1 MODIFY (pwd NOT NULL, name NOT NULL, email NOT NULL);

--test1 테이블 pwd 폭을 100으로 변경

ALTER TABLE test1 MODIFY pwd VARCHAR2(100);

--test1 테이블 email의 NOT NULL 제약 제거

ALTER TABLE test1 MODIFY email NULL;
ALTER TABLE test1 DROP CONSTRAINT uq_test1_email;

어제 두 개의 컬럼으로 기본키를 부여한 것으로 실습을 해보자.

-- 두 개의 컬럼으로 기본키 부여
CREATE TABLE test5 (
    id VARCHAR2(30),
    code NUMBER,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    CONSTRAINT pk_test5_id PRIMARY KEY( id, code )
    -- 복합키로 기본키를 구성할 때는 꼭 테이블 레벨로 작성해야함
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST5';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST5';

INSERT INTO test5(id, code, pwd, name) VALUES('a', 1, '1', '1');
SELECT* FROM test5;

INSERT INTO test5(id, code, pwd, name) VALUES('a', 1, '2', '2');
    -- 에러 : ORA-00001 기본키 제약 위반
INSERT INTO test5(id, code, pwd, name) VALUES('a', 2, '2', '2');
SELECT* FROM test5;

INSERT INTO test5(id, code, pwd, name) VALUES('b', NULL, '3', '3');
    -- 에러 : ORA-01400 기본키는 NULL이 될 수 없다.
    -- 기본키는 NOT NULL을 안줘도 NOT NULL

UPDATE test5 set code = 3 WHERE id = 'a' AND code = 2; 
-- 이럴때 95퍼 이상 WHERE은 기본키로 준다.
-- 제약 조건을 위반하지 않으면 기본키 수정 가능
SELECT* FROM test5;

기본키가 없는 테이블을 생성해서 생성된 테이블에 기본키를 추가해보자.

CREATE TABLE test6 (
    id VARCHAR2(30),
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL
);
SELECT * FROM tab;

INSERT INTO test6(id, pwd, name) VALUES ('1', '1', '1');
INSERT INTO test6(id, pwd, name) VALUES ('1', '2', '2');
SELECT * FROM test6;

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST6';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST6';
-- 기본키가 없음을 확인할 수 있다.

테이블 생성 및 자료 추가

존재하는 테이블에 기본키 추가

ALTER TABLE test6 ADD CONSTRAINT pk_test6_id PRIMARY KEY ( id );
    -- 에러 : ORA-02437. 기본키 위반의 데이터가 존재하므로 추가할 수 없다.

ID를 기본키로 하려고 했으나 이미 들어가 있는 테이블에 ID가 중복적인 것이 존재하므로 기본키 제약조건 위반으로 설정할 수 없다. 따라서 테이블 생성 -> 기본키 추가 -> 테이블에 자료 추가 로 해야함.

여기서는 테이블 생성 -> 테이블에 자료 추가 를 먼저 했기 때문에 자료를 다 삭제하고 기본키를 주어야한다.

-- 모든 데이터 삭제
DELETE FROM test6;
COMMIT;
SELECT* FROM test6;

ALTER TABLE test6 ADD CONSTRAINT pk_test6_id PRIMARY KEY ( id );
-- 다시 기본키 추가
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST6';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST6';
-- 기본키가 추가되었음을 알 수 있다.

생성된 테이블에 기본키를 추가할 때는 ALTER TABLE 테이블명 ADD [ CONSTRAINT 제약조건명] PRIMARY KEY(기본키 설정하고 싶은 컬럼명); 으로 준다.

기본키 제약 조건을 삭제하려면 ?

ALTER TABLE test6 DROP PRIMARY KEY;
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST6';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST6';
-- 지워졌음을 알 수 있다.

ALTER TABLE 테이블명 DROP PRIMARY KEY; 로 삭제할 수 있다.

기본키(PRIMARY KEY)에 대해 알아보자.

• 테이블의 각 행을 구분하는 유일성 제약 조건이다.
• 테이블에 하나만 만들 수 있다.
• 두 개 이상의 컬럼을 조합해서 하나의 기본키를 만들 수 있다.
• 중복값을 가질 수 없으며, NULL을 가질 수 없다.

컬럼 레벨 방식으로 기본키를 부여해보자.

컬럼 레벨 방식으로 기본키를 부여하면 하나의 컬럼으로만 기본키를 만들 수 있다.

CREATE TABLE test1 (
    id VARCHAR2(30) PRIMARY KEY,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST1';

을 통해 제약 조건을 확인해보자

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST1';

이 명령어를 통해서는 어떤 컬럼에 제약 조건이 있는지는 확인이 불가능하고 제약조건이 있다는 것만 알 수 있다. 이 명령어를 통해 나타나는 항목 CONSTRAINT_NAME는 이름을 부여하지 않으면 SYS_...로 표기된다. CONSTRAINT_TYPE은 제약 조건의 타입을 알 수 있다. P : 기본키, C : NOT NULL 등의 check 제약을 나타낸다.

SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST1';

을 통해 제약조건이름과 컬럼명을 확인해보자.

SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST1';

이 명어를 통해서는 제약조건이름(CONSTRAINT_NAME)과 컬럼명(COLUMN_NAME)을 확인할 수 있으나, 어떤 제약 조건인지는 확인이 불가능하다.

제약조건 및 컬럼을 확인하기 위해서는 어떻게 해야할까?

-- 제약조건 및 컬럼 확인
SELECT u1.table_name, column_name, constraint_type, u1.constraint_name, search_condition
FROM user_constraints u1
JOIN user_cons_columns u2 ON u1.constraint_name = u2.constraint_name
WHERE u1.table_name = UPPER('TEST1');

이 명령어를 통해 제약조건 및 컬럼을 확인할 수 있다. 이 명령어의 동작 원리는 다음시간에 자세히 알아보도록 하겠다.

컬럼 방식 기본키를 통해 테이블을 만들어 보자. ( 제약조건명 부여)

-- 컬럼 방식 기본키, 제약조건명 부여. 제약조건명은 중복적인 이름을 가질 수 없다.
CREATE TABLE test2 (
    id VARCHAR2(30) CONSTRAINT pk_test2_id PRIMARY KEY,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST2';

id 에 PRIMARY KEY를 주고 제약조건명을 pk_test2_id 라고 주었다. 이러면 다음에 확인할 때 pk (PRIMARY KEY의 Acronym)이고 테이블명 컬럼을 주었다. 이것을 USER_CONSTRAINTS 명령어를 입력했을 때 CONSTRAINT_NAME에서 확인할 수 있어서 어떤 컬럼인지 원래는 알 수 없으나 이름을 주었기 때문에 컬럼명으로 유추할 수 있다.

테이블 레벨 방식으로 기본키를 부여해보자. 

-- 테이블 레벨 방식으로 기본키 부여(제약조건이름은 부여하지 않음)
CREATE TABLE test3 (
    id VARCHAR2(30),
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    PRIMARY KEY( id )
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST3';

-- 테이블 레벨 방식으로 기본키 부여(제약조건이름 부여)
CREATE TABLE test4 (
    id VARCHAR2(30),
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    CONSTRAINT pk_test4_id PRIMARY KEY( id )
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST4';

-- 두 개의 컬럼으로 기본키 부여
CREATE TABLE test5 (
    id VARCHAR2(30),
    code NUMBER,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    CONSTRAINT pk_test5_id PRIMARY KEY( id, code )
    -- 복합키로 기본키를 구성할 때는 꼭 이렇게 작성해야함
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST5'; -- 확인용
SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST5'; -- 확인용

마지막에 PRIMARY KEY(컬럼명)을 기술함으로서 id에 PRIMARY KEY를 부여해주었다.

두 개 이상의 컬럼으로 기본키를 부여할 때는 꼭 테이블 레벨 방식으로 기본키를 부여해야한다. 

USER_CONSTRAINTS 명령어로는 제약조건을 확인할 수 있으나컬럼의 이름을 알 수 없다. 하지만 CONSTRAINT_NAME을 주어서 ID컬럼이 기본키임을 유추할 수 있다.USER_CON_COLUMNS명령어를 통해 컬럼의 이름을 확인 할 수 있다. code도 PRIMARY KEY를 구성하는 컬럼임을 유추할 수 있다.

MERGE를 통해 다른 테이블에 있는 것을 원하는 테이블에 넣어보자.

실습용 테이블 생성

CREATE TABLE emp1 AS
    SELECT empNo, name, city, dept, sal FROM emp WHERE city ='인천';

CREATE TABLE emp2 AS
    SELECT empNo, name, city, dept, sal FROM emp WHERE dept ='개발부';

SELECT * FROM emp1; 
SELECT * FROM emp2;

emp1 에는 인천사람들의 empNo, name, city, dept, sal을 뽑아서 만들고 emp2에는 개발부서 사람들의 empNo, name, city, dept, sal를 뽑아 새로운 테이블들을 만들어 주었다.

MERGE 

MERGE INTO emp1 e1
        USING emp2 e2
        ON (e1.empNo = e2.empNo)
        WHEN MATCHED THEN
            UPDATE SET e1.sal = e1.sal + e2.sal
        WHEN NOT MATCHED THEN
            INSERT (e1.empNo, e1.name, e1.city, e1.dept, e1.sal) 
            VALUES (e2.empNo, e2.name, e2.city, e2.dept, e2.sal);

SELECT * FROM emp1;
SELECT * FROM emp2;
COMMIT;

테이블명에 e1, e2로 별명을 주었다. e1과 e2테이블의 empNo이 같으면 sal을 더해서 넣고, 다르면 값들을 새로 넣어주었다. 확일을 해보면 emp1에 emp2의 데이터들이 들어간 것이고 emp2는 그대로 인 것을 볼 수 있다.

DELETE 는 자료를 삭제할 때 쓰는 명령어이다.

예시를 통해 알아보자.

-- emp_score1 테이블에서 empNo = '1001' 인 자료 삭제
DELETE FROM emp_score1 WHERE empNo = '1001';
SELECT * FROM emp_score1; -- 확인용
COMMIT;

DELETE FROM 테이블명 WHERE 조건; 조건에 해당하는 자료를 삭제한다.

실습용 테이블 생성

CREATE TABLE emp1 AS
    SELECT * FROM emp;

CREATE TABLE emp_score1 AS
    SELECT * FROM emp_score;

emp_score1 테이블에서 남자만 삭제해보자.

-- 남자만 뽑기
SELECT empNo FROM emp1 WHERE MOD( SUBSTR(rrn, 8, 1), 2) = 1;

DELETE FROM emp_score1 WHERE empNo IN
( SELECT empNo from emp1 WHERE MOD( SUBSTR(rrn, 8, 1), 2) = 1);
SELECT * FROM emp_score1; -- 확인용
COMMIT;

먼저 남자만 뽑고, emp_score1에서 삭제할 때는 IN 뒤에 남자만 뽑은 서브쿼리를 줘서 같은 값을 가지고 있는 empNo들을 삭제하도록 했다.

테이블의 모든 자료 삭제 시, 구조는 삭제가 되지 않는다.

DELETE와 TRUNCATE를 이용해 모든 레코드를 삭제할 수 있다.

차이

• TRUNCATE는 DELETE보다 더 빠르다. 
• TRUNCATE는 자동 COMMIT이 된다.

DELETE FROM emp1;
SELECT * FROM emp1; -- 다 삭제되었는지 확인용
COMMIT;
DESC emp1; -- 구조 확인

실습용 테이블 삭제

DROP TABLE emp_score1 PURGE;
DROP TABLE emp1 PURGE;

PURGE를 써야 휴지통에 담기지 않고 삭제된다.

PURGE 안 썼을 시 휴지통 비우기 >

DELETE를 이용해 실수로 삭제하거나 수정한 경우 복구 방법에 대해 알아보자.

단, 오래전에 삭제한 자료는 불가

DELETE FROM emp WHERE city = '서울';
COMMIT;
SELECT * FROM emp;

실수로 서울에 사는 사람들의 정보를 삭제했다.

-- 30분전의 emp 테이블
SELECT * FROM emp
AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '30' MINUTE);

30분전의 emp 테이블에는 서울에 사는 사람들도 있었기 때문에 30분 전으로 했다. ( 실수로 삭제한 시간보다 전이면 됨)

이 쿼리를 실행하면 삭제 전의 테이블이 나온다.

30분전의 emp 테이블로 삭제된 데이터를 복구시켜보자.

-- 30분전의 emp 테이블로 삭제된 데이터 복구
SELECT * FROM emp
AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '30' MINUTE)
WHERE city='서울';

INSERT INTO emp (
    SELECT * FROM emp
    AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '30' MINUTE)
    WHERE city='서울'
);

SELECT * FROM emp; -- 데이터가 잘 들어갔는지 확인용
COMMIT; -- 트랙잭션 완료

내가 삭제한 서울사람들만 추가할 필요가 있으므로 30분 전의 emp 테이블에서 내가 삭제한 자료들만 먼저 출력해보았다. 그 후 INSERT INTO 테이블 ( 값을 가진 컬럼들 ); 을 통해 다시 넣어서 복구시켜주었다.

UPDATE 를 이용해 데이터를 수정해보자. 

SELECT * FROM emp_score;
UPDATE emp_score SET com=90, excel=95;
-- 조건없이 데이터를 수정하면 모두 수정됨.

SELECT * FROM emp_score;

ROLLBACK; -- 수정 취소

UPDATE 테이블명 SET 컬럼 = 값; 을 이용해서 수정한다. WHERE이 없으면 컬럼에 모든 행에 값을 대입하므로 그냥 WHERE이 있다고 생각해두고 외우자.

UPDATE emp_score SET com=90, excel=95 WHERE empNo = '1002';
SELECT * FROM emp_score;
COMMIT;

ALTER TABLE emp_score ADD(
    tot NUMBER(3) GENERATED ALWAYS AS (com+excel+word) VIRTUAL,
    ave NUMBER(4, 1) GENERATED ALWAYS AS ( ROUND( (com+excel+word) / 3, 1 ) ) VIRTUAL
);

WHERE empNo 에 1002를 줘서 해당 행의 com컬럼과 excel컬럼의 값을 90과 95로 바꾸어 주었다.

ALTER을 통해 해당 테이블에 없던 tot와 ave를 가상컬럼으로 추가해주었다.

다음 문제를 풀어보자. ( UPDATE와 관련 없음 )

SELECT empNo, com, excel, word, tot, ave, 
    (CASE
        WHEN com >= 40 AND excel >= 40 AND word >=40 AND ave >=60 THEN '합격'
        WHEN ave>=60 THEN '과락'
        ELSE '불합격'        
    END) grade 
FROM emp_score;

개발부 사람들의 excel 점수를 100점 더해주기

SELECT empNo FROM emp WHERE dept='개발부';
SELECT * FROM emp_score;

UPDATE emp_score SET excel = excel + 100
WHERE empNo IN (SELECT empNo FROM emp WHERE dept='개발부');

SELECT * FROM emp_score; -- 잘 들어 갔는지 확인

ROLLBACK;

개발부인 사람들만 먼저 뽑아보았다. 그 후 UPDATE에서 WHERE ~ IN 서브쿼리를 통해 값을 수정했다.

UPDATE 테이블명 SET 컬럼 = (값) WHERE 조건 IN (이 곳에 있는 컬럼들과 조건이 부합하면 값을 수정한다.)

UPDATE 사용시

• 제약조건을 위반하는 값으로 변경할 수 없다.
• 데이터 타입이 다르거나 데이터 폭보다 큰 데이터로 수정할 수 없다.

예시

UPDATE emp_score SET empNo = '1002' WHERE empNo = '1001';
-- 에러 : ORA-00001. 기본키는 중복값을 가질 수 없다.

empNo은 PRIMARY KEY로 중복값을 가질 수 없다. 1002는 이미 존재하는 값이기 때문에 1001의 값을 1002로 바꿀 수 없다.

가상컬럼 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에 넣는다. 

데이터 조작언어 ( DATA MANIPULATION LANGUAGE, DML) 에 해당하는 INSERT에 대해 알아보자.

INSERT 후 COMMIT 또는 ROLLBACK으로 트랜잭션을 완료 해야 한다.

COMMIT 트랜잭션 완료 (테이블에 저장 완료)ROLLBACK 트랜잭션 취소 (테이블 저장 취소)

데이터 조작언어를 실행 후 DDL(Data Definition Language : CREATE, ALTER, DROP)명령을 실행하면 자동 COMMIT 된다.

예를 들어 INSERT 후 CREATE 명령을 실행하면 INSERT 후 CREATE 명령을 실행하면 INSERT는 COMMIT 된다. 

제약조건을 위반하거나 데이터 타입이 일치하지 않거나, 데이터 폭이 적으면 INSERT는 실행되지 않고 에러가 발생된다.

※ 자바 등 응용프로그램에서 INSERT 하면 기본적으로 자동 COMMIT 된다.

위험한 상황이 발생될 수 있으므로 VS Code를 사용한다면 자동으로 COMMIT 되지 않도록 설정을 해야한다.

SET AUTOCOMMIT OFF;

CREATE TABLE test1 (
    num NUMBER PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    birth DATE NOT NULL,
    memo VARCHAR2(1000)
);

INSERT INTO test1 VALUES(1, '홍길동', '2000-10-10', '테스트입니다'); 
-- VSCODE 에서는 에러. 년-월-일이 미국식으로 되어있기 때문에 TO_DATE를 써줘야한다.
-- sqldeveloper 에서는 가능. 

INSERT INTO test1 VALUES(1, '홍길동', TO_DATE('2000-10-10', 'YYYY-MM-DD'), '테스트입니다');

SELECT * FROM test1; 
-- 제대로 INSERT가 되었는지 확인

INSERT INTO test1 VALUES(2, '김자바', TO_DATE('2000-11-11', 'YYYY-MM-DD'), '테스트2');
COMMIT; -- 트랜잭션 완료

INSERT INTO test1 VALUES(3, '이이이', TO_DATE('2000-11-11', 'YYYY-MM-DD') );
-- 에러. 컬럼의 개수와 값의 개수가 다름

-- 컬럼명을 명시해서 데이터 추가
INSERT INTO test1 (num, name, birth) VALUES(3, '이이이', TO_DATE('2000-11-11', 'YYYY-MM-DD') );
-- memo는 제약조건이 없기 때문에 NULL이어도 추가가 가능하다. 
-- 하지만 테이블명 뒤에 컬럼명을 명시하지 않으면
-- 가지고 있는 모든 컬럼에 값을 넣는 다는 의미이므로 컬럼의 개수와 값의 개수를 맞춰주어야 한다.

INSERT INTO test1 (num, name, birth, memo) 
    VALUES(1, '나나나', TO_DATE('2000-11-11', 'YYYY-MM-DD'), '테스트' );
-- 에러 ORA-00001 제약조건 위반(기본키 제약 조건 위반)
-- num 은 PRIMARY KEY이므로 중복값을 가질 수 없다.

INSERT INTO test1 (num, name) VALUES(4, '나나나');
-- 에러 ORA-01400: cannot insert NULL into ("SKY"."TEST1"."BIRTH")
-- birth는 NOT NULL로 null을 추가할 수 없다.
INSERT INTO test1 (num, name, birth) VALUES(4, '나나나', '05/05/80' );
-- 에러 : ORA-01843: not a valid month 날짜 형식 오류

INSERT INTO test1 (num, name, birth) VALUES(4, '나나나', TO_DATE('05/05/80', 'MM/DD/RR') );
SELECT * FROM test1; -- INSERT가 잘 되었는지 확인

INSERT INTO test1 (num, name, birth,memo) 
    VALUES(5, '다다다', TO_DATE('90/05/05', 'RR/MM/DD/'), '');
    -- ''는 NULL 임. 문자열 길이가 0임.
    
INSERT INTO test1 (num, name, birth,memo) 
    VALUES(6, '로로로', TO_DATE('90/05/05', 'RR/MM/DD/'), NULL);

INSERT INTO test1 (num, name, birth,memo) 
    VALUES(7, '가나다라마바사아자차파', TO_DATE('90/05/05', 'RR/MM/DD/'), NULL);
    -- 에러 : ORA-12899 입력 문자열길이가 폭보다 크다. 
    -- 한글은 1글자에 3byte를 차지하므로 VARCHAR2에서 30을 줬으므로 10글자만 가능하다.

ROLLBACK; -- 트랜잭션 취소(테이블에 저장되지 않음)

SELECT * FROM test1;
-- 따라서 확인해보면 위에서 COMMIT을 했었던 홍길동과 김자바만 들어가 있는 것을 확인 할 수 있다.

-- test1 테이블에 다음의 컬럼을 추가한다.
-- 컬럼명 : reg_date 타입 : TIMESTAMP
ALTER TABLE test1 ADD ( reg_date TIMESTAMP);

-- test1 테이블에 다음의 데이터를 추가한다.
-- num : 3, name : 너너너, birth : 950101, reg_date : 2021 08 10 10 10 10 777
INSERT INTO test1 ( num, name, birth, reg_date)
    VALUES( 3, '너너너', TO_DATE('950101', 'RRMMDD'),
        TO_TIMESTAMP('20210810101010777', 'YYYYMMDDHH24MISSFF3'));

COMMIT;

SELECT * FROM test1;