데이터 조작어(DML == Data Manuplation Language)

   --- DML 문은 기본적으로 수동 commit 이다.
   --- 즉, DML 문을 수행한 다음에는 바로 디스크(파일)에 적용되지 않고 commit 해야만 적용된다.
   --- 그래서 DML 문을 수행한 다음에 디스크(파일)에 적용치 않고자 한다라면 rollback 하면 된다.

 

   1. insert  --> 데이터 입력
   2. update  --> 데이터 수정
   3. delete  --> 데이터 삭제
   4. merge   --> 데이터 병합 

 

   insert 는 문법이
   insert into 테이블명(컬럼명1,컬럼명2,...) values(값1,값2,...); 
   이다.

 

   ※ Unconditional insert all  -- ==>조건이 없는 insert 
   [문법] insert all 
         into 테이블명1(컬럼명1, 컬럼명2, ....)
         values(값1, 값2, .....)
         into 테이블명2(컬럼명3, 컬럼명4, ....)
         values(값3, 값4, .....)
           SUB Query문;

 

   ※ Conditional insert all -- ==> 조건이 있는 insert 
   조건(where절)에 일치하는 행들만 특정 테이블로 찾아가서 insert 하도록 하는 것이다.        

 

 

---

tbl_emp1 테이블과 tbl_emp1_backup 백업테이블 생성

    create table tbl_emp1
   (empno            number(6)
   ,ename            varchar2(50)
   ,monthsal         number(7)
   ,gender           varchar2(6)
   ,manager_id       number(6)
   ,department_id    number(4)
   ,department_name  varchar2(30)
   );  
   -- Table TBL_EMP1이(가) 생성되었습니다.
    
    
    create table tbl_emp1_backup
   (empno            number(6)
   ,ename            varchar2(50)
   ,monthsal         number(7)
   ,gender           varchar2(6)
   ,manager_id       number(6)
   ,department_id    number(4)
   ,department_name  varchar2(30)
   );  
   -- Table TBL_EMP1_BACKUP이(가) 생성되었습니다.

tbl_emp1

    select employee_id
        , first_name || ' ' || last_name AS ename 
        , nvl(salary + (salary * commission_pct), salary) AS month_sal
        , case when substr(jubun,7,1) in('1','3') then '남' else '여' end AS gender
        , E.manager_id
        , E.department_id
        , department_name
   from employees E LEFT JOIN departments D 
   on E.department_id = D.department_id
   order by E.department_id asc, employee_id asc;

tbl_emp1 테이블과 tbl_emp1_backup 백업테이블 조회 - 아무것도 없음

   select count(*)
   from tbl_emp1; -- 0
   
   select count(*)
   from tbl_emp1_backup; -- 0

tbl_emp1 테이블과 tbl_emp1_backup 백업테이블에 사원정보 삽입

   insert all 
         into tbl_emp1(empno, ename, monthsal, gender, manager_id, department_id, department_name)
         values(employee_id, ename, month_sal, gender||'성', manager_id, department_id, department_name)
         into tbl_emp1_backup(empno, ename, monthsal, gender, manager_id, department_id, department_name)
         values(employee_id, ename, month_sal, gender||'성', manager_id, department_id, department_name)
   select employee_id
        , first_name || ' ' || last_name AS ename 
        , nvl(salary + (salary * commission_pct), salary) AS month_sal
        , case when substr(jubun,7,1) in('1','3') then '남' else '여' end AS gender
        , E.manager_id
        , E.department_id
        , department_name
   from employees E LEFT JOIN departments D 
   on E.department_id = D.department_id
   order by E.department_id asc, employee_id asc;
   -- 214개 행 이(가) 삽입되었습니다.
   
   commit;

tbl_emp1 테이블과 tbl_emp1_backup 백업테이블의 사원정보 조회 - 각 107명의 사원정보 등록됨

   select count(*)
   from tbl_emp1; -- 107
   
   select count(*)
   from tbl_emp1_backup; -- 107
   
   
   select *
   from tbl_emp1;
   
   select *
   from tbl_emp1_backup;

 

 

---

tbl_emp_dept30, tbl_emp_dept50, tbl_emp_dept80 테이블 생성

   create table tbl_emp_dept30
   (empno            number(6)
   ,ename            varchar2(50)
   ,monthsal         number(7)
   ,gender           varchar2(4)
   ,manager_id       number(6)
   ,department_id    number(4)
   ,department_name  varchar2(30)
   );
   -- Table TBL_EMP_DEPT30이(가) 생성되었습니다.

   create table tbl_emp_dept50
   (empno            number(6)
   ,ename            varchar2(50)
   ,monthsal         number(7)
   ,gender           varchar2(4)
   ,manager_id       number(6)
   ,department_id    number(4)
   ,department_name  varchar2(30)
   );
   -- Table TBL_EMP_DEPT50이(가) 생성되었습니다.

   create table tbl_emp_dept80
   (empno            number(6)
   ,ename            varchar2(50)
   ,monthsal         number(7)
   ,gender           varchar2(4)
   ,manager_id       number(6)
   ,department_id    number(4)
   ,department_name  varchar2(30)
   ); 
   -- Table TBL_EMP_DEPT80이(가) 생성되었습니다.

부서번호가 30, 50, 80 번인 사원들의 정보를 각 테이블에 삽입

   insert all 
         when department_id = 30 then
         into tbl_emp_dept30(empno, ename, monthsal, gender, manager_id, department_id, department_name)
         values(employee_id, ename, month_sal, gender, manager_id, department_id, department_name)
         
         when department_id = 50 then
         into tbl_emp_dept50(empno, ename, monthsal, gender, manager_id, department_id, department_name)
         values(employee_id, ename, month_sal, gender, manager_id, department_id, department_name)
         
         when department_id = 80 then
         into tbl_emp_dept80(empno, ename, monthsal, gender, manager_id, department_id, department_name)
         values(employee_id, ename, month_sal, gender, manager_id, department_id, department_name)
         
   select employee_id
        , first_name || ' ' || last_name AS ename 
        , nvl(salary + (salary * commission_pct), salary) AS month_sal
        , case when substr(jubun,7,1) in('1','3') then '남' else '여' end AS gender
        , E.manager_id
        , E.department_id
        , department_name
   from employees E LEFT JOIN departments D 
   on E.department_id = D.department_id
   where E.department_id in (30,50,80)
   order by E.department_id asc, employee_id asc;
   
   -- Table TBL_EMP_DEPT30이(가) 생성되었습니다.
   -- Table TBL_EMP_DEPT50이(가) 생성되었습니다.
   -- Table TBL_EMP_DEPT80이(가) 생성되었습니다.
   -- 85개 행 이(가) 삽입되었습니다.

    commit;

   select *
   from tbl_emp_dept30;
   
   select *
   from tbl_emp_dept50;
   
   select *
   from tbl_emp_dept80;

 

---