28강 오라클 SQL insert, update, delete DML(1)

-- 1. 테이블에 새로운 행을 추가하는 INSERT문
-- 2. 다중 테이블에 다중 행 입력하기.
-- 3. PIVOTING에 의해 다중 테이블에 다중 행 입력하기

-- 01. 테이블에 새로운 행을 추가하는 INSERT 문
-- insert 문은 테이블에 새로운 데이터를 입력하기 위해 사용하는 데이터 조작어입니다.
-- 다음은 insert 문의 기본 형식 입니다.
-- insert into table_name (column_name, ...) values(column_value, ...);

-- <실습하기> INSERT 문을 위한 실습에 사용할 테이블 생성하기
-- 1. 이미 사용하던 테이블과 동일한 이름의 테이블을 새로 만들려면 DROP TABLE 명령어로
-- 테이블을 삭제한 후에 기존에 있던 부서 테이블(DEPT)과 동일한 구조를 갖는 테이블을 생성합니다.
drop table dept01; -- 테이블 삭제

create table dept01 -- 테이블 생성
(deptno number(2),
dname varchar2(14),
loc varchar2(13));

-- 1.1 테이블의 구조만 복사하기.
-- 1. DEPT에 데이터를 추가합니다.
insert into dept01(deptno, dname, loc)
values( 10, 'ACCOUNTING', 'NEW YORK');

-- 행 삭제
delete from dept01 where dname= 'ACCOUNTING';

-- 1.2 INSERT 구문에서 오류 발생의 예
-- 칼럼 명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호 안에
-- 기술한 값의 개수가 적으면 에러가 발생합니다.
insert into dept01(deptno, dname, loc)
values( 10, 'ACCOUNTING');

-- 칼럼 명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호에 기술한
-- 값의 개수가 많으면 에러가 발생합니다.
insert into dept01(deptno, dname, loc)
values( 10, 'ACCOUNTING', 'NEW YORK', 20);

-- 칼럼 명이 잘못 입력되었을 때에도 에러가 발생합니다.
insert into dept01(num, dname, loc)
values( 10, 'ACCOUNTING', 'NEW YORK');

-- 데이터 타입이 맞지 않을 경우에도 에러가 발생.
insert into dept01(num, dname, loc)
values( 10, ACCOUNTING, 'NEW YORK');

-- 1.3 칼럼 명을 생략한 insert 구문
-- 테이블에 로우를 추가할 때 모든 칼럼에 모든 자료를 입력하는 경우에는
-- 굳이 칼럼 목록을 기술하지 않아도 됩니다.

-- 칼럼 목록이 생략되면 VALUES 절 다음의 값들이 테이블의 기본 칼럼
-- 순서대로 입력됩니다.

-- 테이블의 컬럼 순서는 CREATE TABLE로 테이블을 생성할 때의 순서를
-- 따릅니다.

-- 테이블의 기본 칼럼 순서는 DESC 문으로 조회했을 때 보여지는
-- 순서입니다.

insert into dept01
values( 20, 'RESEARCH', 'DALLAS');

-- 1.4 NULL 값 삽입하는 다양한 방법
-- 데이터를 입력하는 시점에서 해당 컬럼 값을 모르거나 확정되지 않았을
-- 경우에는 NULL 값을 입력해야 합니다.

-- NULL 값 삽입은 암시적인 방법과 명시적인 방법이 있습니다.

-- 암시적 방법은 칼럼 명 리스트에 칼럼을 생략하는 것입니다.

-- 즉, 다른 칼럼은 값을 입력하지만 이렇게 생략한 칼럼에는 암시적으로
-- NULL 값이 할당되는 것입니다.

-- 명시적 방법은 VALUES 리스트에 명시적으로 NULL을 입력합니다.

-- 부서 테이블에 컬럼이 NULL 값을 허용하는지 살펴보기 위해서 DESC 명령을 실행합니다.

-- DEPT 테이블의 DEPTNO 컬럼은 NOT NULL 제약조건이 지정되어 있습니다.

-- NOT NULL 제약조건이 지정된 DEPTNO 컬럼에 널값을 허용하지 못하도록, 오라클 내부에서 이미 컬럼에 제약조건을 지정해 놓은 상태입니다.

-- 컬럼에 널값을 허용하지 못하도록 하려면 커럼에 제약조건을 지정해야 합니다.

-- 1.5 암시적으로 NULL 값의 삽입
-- 다음은 지역명이 결정되지 않은 30번 부서명만 입력하려고 합니다.
-- 저장할 값을 명확하게 알고 있는 컬럼 명만 명시적으로 기술한 후에 그에
-- 매칭되는 값을 VALUES 절 다음에 기술합니다.
insert into dept01 (deptno, dname) values (30, 'SALES');

-- 1.6 명시적으로 NULL 값의 삽입
-- 컬럼명을 명시적으로 기술하지 않으면 테이블이 갖고 있는 모든 컬럼에
-- 값을 지정해야 합니다.
insert into dept01
values(40, 'OPERAIIONS', NULL);

-- 지역명이 결정되어 지지 않았더라도 반드시 값 3개를 지정해야 하기
-- 때문에 명시적으로 VALUES 리스트에서 지역명에 NULL을 입력해야 합니다.

-- NULL 값을 갖는 컬럼을 추가하기 위해서 NULL 대신 ''를 사용할 수
-- 있습니다.
insert into dept01 values(50, '','CHICAGO');

-- 이번에는 지역명이 아닌 부서명이 결정되지 않아 부서명에 NULL 값을
-- 입력한 예입니다.

-- 1.7 서브 쿼리로 데이터 삽입하기
-- INSERT INTO 다음에 VALUES 절을 사용하는 대신에 서브 쿼리를 사용할 수 있습니다.

-- 이렇게 하면 기존의 테이블에 있던 여러 행을 복사해서 다른 테이블에 삽입할 수 있습니다.

-- 이 때 주의할 점은 INSERT 명령문에서 지정항 컬럼의 개수나 데이터 타입이 서브 쿼리를 수행한 결과와 동일해야 한다는 점입니다.

-- <실습하기> 서브쿼리로 데이터를 삽입하는 예제
-- 1. 테이블 구조만을 복사해서 내용을 갖지 않는 테이블에 서브 쿼리로 로우를 입력해봅시다.
insert into dept01 
-- dept01 테이블안에
select * from dept;
-- dept 테이블의 데이터를 넣어라.

select * from dept01;

-- 02. 다중 테이블에 다중 행 입력하기
-- INSERT ALL문을 사용하면 서브 쿼리의 결과를 조건 없이 여러 테이블에
-- 동시에 입력할 수 있습니다.

-- 사원번호, 사원명, 입사일자로 구성된 EMP_HIR 테이블과 사원번호,
-- 사원명, 해당관리자(상관)로 구성된 EMP_MGR 테이블이 빈테이블로 존재한다고 합시다.

-- 사원 테이블(EMP)에서 부서 번호가 20인 사원들을 검새갛여 EMP HIR
-- 테이블에는 사원 번호, 사원 명, 급여를 EMP_MGR 테이블에는 사원
-- 번호, 사원 명, 해당관리자(상관)를 입력하려면 어떻게 해야 할까요?

-- INSERT ALL 명령문을 사용하면 두 번의 쿼리문을 수행하지 않고도
-- 하나의 쿼리문으로 두 개의 테이블에 원하는 컬럼 값을 삽입할 수 있습니다.

-- INSERT ALL문을 사용하면 서브 쿼리의 결과를 조건 없이 여러 테이블에 동시에 입력할 수 있는데,
-- 주의할 점은 서브 쿼리의 컬럼명과 데이터가 입력되는 테이블의 컬럼명이 동일해야 한다는 점입니다.
insert all
into emp_hir values( empno, ename, hiredate)
into emp_mgr values( empno, ename, mgr)
select empno, ename, hiredate, mgr from emp
where deptno=20;

select * from emp_hir;
select * from emp_mgr;

-- 2.1 조건(WHEN)에 의해 다중 테이블에 다중 행 입력하기(각각의 제약조건)
-- INSERT ALL 명령문에 WHEN절을 추가해서 조건을 제시하여 조건에 맞는 행만 추출하여 테이블에 추가합니다.
-- EMP_HIR02 테이블에는 1982년 01월 01일 이후에 입사한 사원들의 번호, 사원 명, 입사일을 추가합니다.
-- EMP_SAL 테이블에는 급여가 2000 이상인 사원들의 번호, 사원 명, 급여를 추가합니다.
INSERT ALL
WHEN HIREDATE > '1982/01/01' THEN
-- 각각의 제약조건 1
INTO EMP_HIR02 VALUES(EMPNO, ENAME, HIREDATE)
WHEN SAL > 2000 THEN
-- 각각의 제약조건 2
INTO EMP_SAL VALUES (EMPNO, ENAME, SAL)
SELECT EMPNO, ENAME, HIREDATE, SAL FROM EMP;

select * from emp_hir02;
select * from emp_sal;

-- <실습하기> PIVOTING에 의해 다중 테이블에 다중 행 입력하기.
-- 01. PIVOTING INSERT문을 실습하기 위해 한 주차 월요일부터 금요일까지 매일 매일
-- 의 판매 실적을 기록하는 테이블을 생성해 봅시다.
CREATE TABLE SALES(
SALES_ID NUMBER(4),
WEEK_ID NUMBER(4),
MON_SALES NUMBER(8, 2),
TUE_SALES NUMBER(8, 2),
WED_SALES NUMBER(8, 2),
THU_SALES NUMBER(8, 2),
FRI_SALES NUMBER(8, 2));

-- 02. PIVOTING INSERT문의 결과를 저장할 SALES)DATA 테이블을 생성합시다.

-- 03. 새롭게 생성된 SALES 테이블에 주간 판매 실적을 추가합시다.

-- 04. 이를 각 요일을 구분할 수 있는 컬럼을 추가하여 매일 매일의 판매 실적을 기록해봅시다.