[ORACLE] insert / update / delete

※ insert문

 - 데이터를 입력하는 DML

   DML : 오라클 명령어중에 한 분류로, select(조회), Insert(입력), update(변경), delete(삭제) 가 있다.

 - 데이터를 입력할 때 숫자 값 이외에는 데이터를 ‘(홑따옴표)' 로 감싸야 함

 

 

▶ insert 단일행 입력 insert

 - 기존에 존재하는 테이블에 단일행으로 데이터를 입력하는 것 (행을 밑으로 하나 쌓아주는거라고 생각하면됨)

 

▷ 단일행 입력 기본형태

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

 

 

▷ 모든컬럼의 데이터 입력

  - 컬럼명 생략

insert into dept2
values (9002,'특판4팀',1006,'임시지역');

 

▷ 일부컬럼만 입력

  - not null 로 지정된 컬럼에는 반드시 데이터 입력필수. (null이라고 쓰면 수동 null 입력도 가능)

     ㄴ error : null을("DNAME") 안에 삽입할 수 없습니다

  - 입력하지 않은 컬럼에는 자동으로 null 이 입력됨

insert into dept2(decode,dname)
values (9003,'특판3팀');

 

 

▷ 날짜데이터 입력

insert into professor (hiredate)
values ('2013-03-19');

 - 자동으로 현재 날짜를 입력하려면 SYSDATE 사용

 

---

 

▶ 다중행 입력 insert

  - 컬럼에 여러행의 데이터를 입력하는 것 ( 여러행을 밑으로 쌓아주는거라고 생각하면 됨 )

 

▷ 다중행 입력 기본형태

  - 두 테이블의 칼럼의 개수와 데이터 형이 동일해야 함

  - where 조건을 사용하여 원하는 조건을 주어도 되고, 서브쿼리를 사용해도 됨

  - subquery의 select 문을 이용해서

    하나 이상의 다른 테이블로부터 가져온 여러 데이터를 대상 테이블에 여러 건의 데이터를 입력할 수 있다

  insert into 테이블명(컬럼1, 컬럼2, ...)
    select문

 

▷ 테이블 자료 합치기

  ex ) product 테이블 자료들 몇개를 가져와서 pd 테이블에 넣기

insert into pd (no,pdname,price,regdate) // 3.pd 테이블 no,pdname,price,regdate에 넣어라
select p_code, p_name, p_price, sysdate
from product                   // 1.product 에서 p_code, p_name, p_price, sysdate을 가져와서
where p_code in(102,103,104);  // 2.그중에서 p_code 가 102,103,104 인 데이터만

 - 하지만 이렇게 입력한 데이터는 메모리 상에만 입력된것으로, 이후 다른세션 (cmd창) 에서 확인해보면 안들어가있다

    ( = 데이터화되지않음, =commit 안됨 )

 

 

 ※ 트랜잭션(Transaction) 

 - 논리적인 작업단위

 -여러 가지 DML 작업들 ( insert select delete ) 을 하나의 단위로 묶어 둔 것

    - 해당 트랜잭션 내에 있는 모든 DML이 성공해야 해당 트랜잭션이 성공하는 것이고 
      만약 1개의 DML이라도 실패하면 전체가 실패하게 됨

 

Insert, update, delete 문을 실행하더라도 바로 데이터가 변경되지 않으며,

실제 데이터의 변경은 TCL을 통해 데이터 파일에 변경사항이 반영될 때 발생하게 된다.

그 전에는 변경된 데이터들은 오직 오라클 메모리 상에만 존재한다.

 

 ▶TCL (Transaction Control Language) 

    - ROLLBACK : 보류중인 모든 데이터 변경사항을 폐기

    - COMMIT : 보류중인 모든 데이터 변경사항을 영구적으로 적용

 

 

---

 

▶ 테이블을 생성하면서 데이터 입력하기 insert

create table 신규 테이블명
as
select 선택컬럼1, 선택컬럼2, ...from 기존 테이블명;

create table B
as
select colum1, colum2
from table A; // => 테이블 A처럼 colum1,colum2 가 있는 테이블 B 를 생성해라

- 신규 테이블을 만들고 동시에 다른 테이블에서 select된 컬럼과 결과 데이터를 insert시킴

- select문의 테이블과 컬럼의 제약조건은 복제되지 않기 때문에 신규 테이블에 대해 테이블과 컬럼 제약조건을 정의해야 함 ( pk(primary key)값도 생성하지 않음 ) 

 

 

응용예제 ) employees, departments 테이블을 조인한 결과를 imsi_tbl을 만들면서 입력

 - create table에서 컬럼명을 지정하면

   신규 테이블에 컬럼 리스트가 정의되면서 select문을 통해 필요한 데이터가  insert됨

create table imsi_tbl2(emp_id, name, hiredate, pay, dept_id, dept_name)
as
select e.EMPLOYEE_ID, e.first_name || '-' || e.last_name,
    e.HIRE_DATE, nvl2(commission_pct, salary+salary*commission_pct, salary)
        , e.DEPARTMENT_ID, d.DEPARTMENT_NAME
from employees e join departments d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID;

 => insert문에 컬럼 리스트가 없는 상태에서 select문 컬럼 리스트에 함수가 적용됐다면 별칭을 써서 insert되는 데이터의 컬럼명을 지정해줘야 함 그렇지 않으면 에러남

 

---

▶ insert All

   - 한번에 여러테이블에 여러행 입력하기

select * from p_01;
select * from p_02;
	    
insert all into p_01 values(1, 'AA')
            into p_02 values(2, 'BB')
select * from dual;

 - 다른 테이블의 데이터를 가져와서 입력하기

insert all
        when profno between 1000 and 1999 then
            into p_01 values(profno, name)
        when profno between 2000 and 2999 then
            into p_02 values(profno, name)
select profno, name from professor;

select * from p_01;
select * from p_02;

 - 다른 테이블에 동시에 같은 데이터 입력하기

insert all into p_01 values(profno, name)                    
            into p_02 values(profno, name)
select profno, name from professor
where profno between 3000 and 3999;

select * from p_01;
select * from p_02;

 

 

 

---

※ update문

 - 기존 데이터를 다른 데이터로 변경할 때 사용하는 방법

 

▷ update 기본형태

update 테이블
set 컬럼명1=값1, 컬럼명2=값2, ... 
where 조건

 

 

예제 ) 차범철교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250만원이 안 되는 교수들의 급여를 15% 인상

 

  - 단일행서브쿼리를 이용하여 update  <= 제일 쉬운거

update professor
set pay = pay*1.15
where position = (select position from professor
where name = '차범철')
and pay < 250;

 

 

▷ exists를 이용한 다중 건의 update

 

  - 여러개의 데이터을 한번에 업데이트하는데, 업데이트를 할지 안할지 여부를 결정할 수 있다

  - 서브쿼리의 컬럼값이 존재하는지 여부를 체크

  - 존재여부만 체크하기 때문에 존재하면 true, 존재하지 않으면 false를 리턴함

  - true가 리턴되면 set컬럼의 update를 진행시키고 false가 리턴되면 update는 진행되지 않음

 

 - 다중건의 update를 하기 위해서는 기본적인 update문의 폼을 사용하고

      subquery로 추출한 데이터를 setting하려는  컬럼의 데이터값으로 사용함 <= 이게 무슨말이냐면 ▽

 

 

예제 ) 삭제된 코드가 panmae 테이블에 있다면 새 코드로 update하기

   - 삭제된 코드들은 del_yn 컬럼에 'Y' 로 표기되어있음

update panmae a 
set p_code = (select p_code_new from product b
                where b.p_code = a.p_code and del_yn ='Y')
	where exists (select 1 from product b
                where b.p_code = a.p_code and del_yn ='Y'); -- 존재한다면 update해

select * from panmae
order by p_code desc;

 

 

▷ case 를 이용한 다중 건의 update

 

예제 ) emp에서 comm은 기존값보다 100인상하고, sal은 job이 CLERK이면 2배, MANAGER이면 3배,

         나머지는 4배로 수정

update emp
set comm=comm+100, 
    sal = case job when 'CLERK' then sal*2
                    when 'MANAGER' then sal*3
                    else sal*4 end;

 

 

---

 

▶ 다중컬럼 서브쿼리 update

    - 여러개의 컬럼을 한번에 수정할 때 사용한다

          - 여러 컬럼을 서브쿼리의 결과로 한번에 update 

   

예제 ) EMP 테이블의 사원번호가 7844인 사원의 부서번호와 직무(JOB)를 사원번호가 7782인 사원과 같은 직무와 같은 부서로 배정하라

update emp
set (job, deptno) = (select job , deptno from emp
                      where emp no = 7782;)
where empno = 7844;

△ 서브쿼리로 가져온 값을 통해 job, depto 두개의 컬럼을 수정했다. 

    서브쿼리로 나온 결과값을 set 하려는 컬럼으로 넣어서 수정했다

 

 

---

※ delete문

 - 데이터를 삭제하는 구문

 - Delete 문은 해당 데이터가 사용하고 있던 파일의 저장공간(extent)은 반납하지 않 고 데이터만 삭제하는 구문

 - 데이터가 삭제되더라도 저장공간을 반납하지 않기 때문에 용량이 줄어들지 않음

 

▷ delete 문의 기본형태

DELETE FROM table
WHERE 조건;

응용예제 ) dept2 테이블에서 부서번호(dcode)가 9000번에서 9100번 사이인 데이터삭제

DELETE FROM dept2
WHERE dcode between 9000 and 9100;

 

---

▶ 서브쿼리를 이용하여 delete

    - 서브쿼리로 값 받아와서 지우기

 

 

▷ 단일행

 

예제 ) departments에서 10번 부서의 부서장을 new_employees에서 삭제

 

[1] departments 에서 10번 부서의 부서장 id 받아오기

select manager_id from departments
where department_id=10;

부서장 id : 200. 딱한개다

 

[2] new_employees 에서 10번부서의 부서장 데이터* 가져오기

  - where 서브쿼리

select * from new_employees
where employee_id = (select manager_id from departments
                        where department_id = 10);

 

[3] delete 문으로 변경해서 10번부서의 부서장 데이터 지우기

delete from new_employees
where employee_id=(select manager_id from departments
                    where department_id=10);

 

 

 

▷ 다중행

 

예제 ) departments에서 location_id가 1700인 사원들을 employees에서 삭제

 

[1] departments 에서 location_id 가 1700 인 사람들의 department_id 가져오기

select department_id from departments
where location_id=1700;

 = > 아이디 : 10,30,90 등등 여러개다. 

 

[2] new_imployees 테이블에서 id 가 같은 사람들의 데이터를 구한다

select * from new_employees
where department_id in (select department_id from departments
                        where location_id=1700);

 

[3] delete 문으로 변경해서 지워준다

 -- in

delete from new_employees
where department_id in (select department_id from departments
                        where location_id=1700);

-- exists

delete from new_employees a
where exists (select 1 from departments b
                        where b.department_id=a.DEPARTMENT_ID 
                        and location_id=1700);

 

---

 

 

▶ 다중컬럼 서브쿼리 delete

  - 서브쿼리로 값을 받아와서 지우는데, 컬럼이 여러개일경우

 

 

예제 ) employees에서 직업별(job_id) 최대급여를 받는 사원 삭제

 

[1] 다중컬럼 서브쿼리를 이용하여 job_id 별 최대연봉자구하기

select * from employees
where (job_id,salary) in (select JOB_ID,max(salary) from employees
							group by job_id);

[2] delete 문으로 변경해서 삭제

delete from employees 
where (job_id,salary) in (select job_id,max(salary) from employees
							group by job_id);

 

 

---

▶ 상호연관 서브쿼리 delete

예제 ) employees에서 자신의 job_id의 평균급여보다 많이 받는 사원 삭제

 

[1] job id 별 평균급여

select avg(nvl(salary,0))
from employees
group by job_id;

[2] 상호연관서브쿼리를 이용해 자신의 job_id 평균급여보다 많이 받는사원 구하기

select * from employees a
where salary > (select avg(nvl(salary,0)) from employees b
                    where b.job_id = a.job_id);

[3] delete 문으로 변경해서 지우기

delete from employees a
where salary > (select avg(nvl(salary,0)) from employees b
                    where b.job_id = a.job_id);