[Oracle] 24.서브쿼리와 DML

서브쿼리를 이용하여 데이터 삽입

서브쿼리를 이용해서 데이터를 삽입하고 삭제할 수도 있는데요~

먼저, 다음과 같은 테이블 EMP01이 정의 되어있다고 합시다.

다음은 서브쿼리를 이용해 데이터를 넣는 예제입니다!

INSERT INTO emp01
    SELECT
        employee_id,
        first_name || last_name,
        job_id,
        salary
    FROM
        employees
    WHERE
        job_id = 'IT_PROG';

다중 테이블에 다중 행 입력

여러 테이블에 서브쿼리의 결과를 한꺼번에 입력할 수 있습니다!

EMP_HIR 테이블과 EMP_MGR 테이블이 있다고 합시다!

CREATE TABLE emp_hir
    AS
        SELECT
            employee_id   "사원번호",
            first_name || last_name "사원이름",
            hire_date     "입사일"
        FROM
            employees
        WHERE
        	1=0;

CREATE TABLE emp_mgr
    AS
        SELECT
            employee_id   "사원번호",
            first_name || last_name "사원이름",
            manager_id    "상사번호"
        FROM
            employees;
        WHERE
        	1=0;

다중 테이블에 데이터를 한 번에 넣기 위해서는 INSERT ALL을 사용합니다!

INSERT ALL INTO emp_hir VALUES (사원번호,사원이름,입사일) 
INTO emp_mgr VALUES (사원번호,사원이름,상사번호) 
SELECT
      employee_id   "사원번호",
      first_name || last_name "사원이름",
      hire_date     "입사일",
      manager_id    "상사번호"
  FROM
      employees;

WHEN을 이용한 다중 테이블에 다중 행 입력

다중 행을 입력할 때 조건을 줄 수도 있습니다.

입사일이 2001년 이후인 사원을 emp_hir에 상사번호가 100인 사원들을 emp_mgr에 입력

INSERT ALL 
WHEN 입사일 > '20010101' THEN
INTO emp_hir VALUES (사원번호,사원이름,입사일)

WHEN 상사번호=100 THEN
INTO emp_mgr VALUES (사원번호,사원이름,상사번호)
SELECT
    employee_id "사원번호",
    first_name || last_name "사원이름",
    hire_date "입사일",
    manager_id "상사번호"
FROM
    employees;

실습

manager인 사원을 emp_mng 테이블에, manager가 아닌 사원을 emp_notmng 테이블에 삽입

emp_mng 테이블과 emp_notmng 테이블이 다음과 같이 정의되어 있다고 합시다.

insert all
when "사원번호" in (select distinct nvl(manager_id,0) from employees) then 
into emp_mng values(사원번호,사원이름,급여,상사번호)
when "사원번호" not in(select distinct nvl(manager_id,0) from employees) then
into emp_notmng values(사원번호,사원이름,급여,상사번호)
SELECT
    nvl(employee_id,0) "사원번호",
    first_name || last_name "사원이름",
    salary "급여",
    manager_id "상사번호"
FROM
    employees;

서브쿼리를 이용한 데이터 수정

서브쿼리를 이용해 다음과 같이 수정도 가능합니다.

마케팅 부 사원의 급여를 10% 인상

update emp
set 급여=급여*1.1
where  deptno = (select deptno from department where deptname='Marketing'); 

급여 값이 null인 사원의 급여를 최대 급여로 변경

update emp_mng
set 급여=(select max(급여) from emp_mng)
where 급여 is null;

급여가 5000이상인 사원의 급여와 상사번호를 사원번호 100인 사원의 급여와 상사번호로 변경 (서브쿼리를 사용한 한꺼번에 두 개의 컬럼 값 변경)

update emp_mng
set (급여, 상사번호)=(select 급여, 상사번호 from emp_mng where 사원번호=100)
where 급여>=5000;

테이블 합병하기

create table merge01 
as
select employee_id "ENO", salary "SAL", hire_date "HD"
from employees
where to_char(hire_date,'YYYY')>='2005';

create table merge02 
as
select employee_id "ENO", salary "SAL", hire_date "HD"
from employees
where to_char(hire_date,'YYYY')>='2001';

다음 테이블 merge01 과 merge02 가 있다고 합시다.

테이블 합병은 다음과 같이 합니다.

merge02의 내용을 merge01에 추가하고 merge02와 같은 내용은 merge02의 내용으로 덮어씁니다.

merge INTO merge01
USING merge02
ON(merge01.ENO=merge02.ENO)
WHEN MATCHED THEN --겹치는 것
UPDATE SET
merge01.SAL=merge02.SAL,
merge01.HD=merge02.HD
WHEN NOT MATCHED THEN --겹치지 않는 것
INSERT VALUES(merge02.ENO,merge02.SAL,merge02.HD);