220906 - 오라클 - MERGE문, 뷰

DML 복습

○ 사원번호 7369 와 업무 동일한 모든사원 부서번호를 사원 7369의 현재 부서번호로 갱신

update emp_copy set dno=(select dno from emp_copy where eno=7369) where job=(select job from emp_copy where eno=7369);

 

○ dept_copy 테이블 삭제, department 테이블 구조 내용 복사, dept_copy 테이블 재생성

drop table dept_copy;
create table dept_copy as select * from department;

 

○ dept_copy 테이블에서 부서명이 'research'인 부서 삭제 (행)

delete from dept_copy where dname='RESEARCH';

 

○ dept_copy에서 부서번호가 10 이거나 40인 부서 삭제

delete from dept_copy where dno=10 or dno=40;

 

 

 

 

MERGE문

: 조건 비교하여 테이블에 해당 조건에 맞는 데이터 없으면 INSERT / 있으면 UPDATE 수행

 

MERGE INTO 테이블명
  USING 비교할 테이블/서브쿼리 ON 조건
  WHEN MATCHED THEN
    UPDATE SET 컬럼1=값1,...
  WHEN NOT MATCHED THEN
    INSERT (컬럼명1,..) VALUES(값1,..) WHERE 조건;

 

 

 

• 보너스테이블 중 직속상관이 7839인 직원만 급여의 30%를 보너스로, 그외는 새로 추가해 급여 20% 보너스로 설정 (단 급여 2500이하)

merge into emp_bonus eb using (select eno, ename, salary, manager from employee where manager=7839) e on (eb.eno=e.eno) //using) 매니저가 7839인 데이터 뽑아오고 on) 해당 데이터들끼리 매칭
  2  when matched then
  3  update set bonus=e.salary*0.3 //매니저 7839인 데이터
  4  when not matched then 
  5  insert (eb.eno, eb.ename, eb.bonus) values (e.eno, e.ename, e.salary*0.2) where e.salary<2500; //그 외


※ alter table emp_bonus modify bonus invisible;  //bonus열 숨기기
=> 숨기고 보여주면서 컬럼 순서 바꿀 수 있음

 

 

 

뷰

: 하나 이상의 테이블이나 다른 뷰를 이용해 생성하는 가상 테이블 (virtual table)

자료가 없어 디스크 공간 할당되지 않음 => 쿼리문만 저장되어 있음 (테이블 존재 X)
보안, 편의성 高

CREATE (OR REPLACE) VIEW 뷰명 AS SELECT 문장; 
=> or replace : 중복된 뷰명 있을 때에도 생성 가능 (대체됨)

 

 

 

• 뷰 목록 검색

select view_name, text from user_views;

 

 

 

 

• 뷰 생성

• 업무가 'salesman'인 직원의 사원번호, 사원이름, 부서번호, 담당업무 컬럼만 갖는 뷰 생성

create view v_emp_sales(사원번호, 사원이름, 부서번호, 담당업무) as select eno, ename, dno, job from employee where job='SALESMAN';

 

 

• 사원번호, 사원명, 급여, 부서번호, 부서명, 지역명

create view v_emp_complex as select e.eno, e.ename, e.salary, dno, d.dname, d.loc from employee e natural join department d;

=>


       ENO ENAME                    SALARY        DNO DNAME                        LOC
---------- -------------------- ---------- ---------- ---------------------------- --------------------------
      7782 CLARK                      2450         10 ACCOUNTING                   NEW YORK
      7839 KING                       5000         10 ACCOUNTING                   NEW YORK
      7934 MILLER                     1300         10 ACCOUNTING                   NEW YORK
      7566 JONES                      2975         20 RESEARCH                     DALLAS
      7902 FORD                       3000         20 RESEARCH                     DALLAS
      7876 ADAMS                      1100         20 RESEARCH                     DALLAS
      7369 SMITH                       800         20 RESEARCH                     DALLAS
      7788 SCOTT                      3000         20 RESEARCH                     DALLAS
      7521 WARD                       1250         30 SALES                        CHICAGO
      7844 TURNER                     1500         30 SALES                        CHICAGO
      7499 ALLEN                      1600         30 SALES                        CHICAGO
      7900 JAMES                       950         30 SALES                        CHICAGO
      7698 BLAKE                      2850         30 SALES                        CHICAGO
      7654 MARTIN                     1250         30 SALES                        CHICAGO

 

 

•  

create view v_emp_salary as select dno, sum(salary) as 급여합계, avg(salary) as 급여평균 from employee group by dno;

select * from v_emp_salary;

       DNO   급여합계   급여평균
---------- ---------- ----------
        30       9400 1566.66667
        10       8750 2916.66667
        20      10875       2175

 

 

 

 

• 그룹함수 => 별칭 필수

create view v_emp_salary2 as select dno, sum(salary), avg(salary) from employee group by dno; //오류 //sum, avg 별칭 추가해야 함

 

 

 

 

• 그룹함수 갖는 뷰는 DML 사용 불가

insert into v_emp_salary values(20,2000,420); //오류

 

 

 

 

• 뷰 삭제

drop view v_emp_salary;

 

 

 

• force : 테이블 유무 상관없이 무조건 뷰 생성

create or replace force view v_emp_job2 as select eno, ename, dno,job from empl where job='SALESMAN';

경고: 컴파일 오류와 함께 뷰가 생성되었습니다.

//
테이블 없더라도 미래에 생성될 수 있다면 미리 뷰 생성 가능

 

 

 

 

• 뷰에 DML 조작어 사용 => 뷰 X 원본 테이블 O에 실행됨

SQL> create or replace view v_emp_job as select eno, ename, dno, job from employee where job='MANAGER';

뷰가 생성되었습니다.

SQL> insert into v_emp_job values(9000,'김ㅊ',30,'SALESMAN');

1 개의 행이 만들어졌습니다.

SQL> select * from v_emp_job;

       ENO ENAME                       DNO JOB
---------- -------------------- ---------- ------------------
      7566 JONES                        20 MANAGER
      7698 BLAKE                        30 MANAGER
      7782 CLARK                        10 MANAGER

SQL> select * from employee;

       ENO ENAME                JOB                   MANAGER HIREDATE     SALARY COMMISSION        DNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      9000 김ㅊ                 SALESMAN                                                             30
      7369 SMITH                CLERK                    7902 80/12/17        800                    20
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30
      7566 JONES                MANAGER                  7839 81/04/02       2975                    20
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30
      7698 BLAKE                MANAGER                  7839 81/05/01       2850                    30
      7782 CLARK                MANAGER                  7839 81/06/09       2450                    10
      7788 SCOTT                ANALYST                  7566 87/07/13       3000                    20
      7839 KING                 PRESIDENT                     81/11/17       5000                    10
      7844 TURNER               SALESMAN                 7698 81/09/08       1500          0         30
      7876 ADAMS                CLERK                    7788 87/07/13       1100                    20
      7900 JAMES                CLERK                    7698 81/12/03        950                    30
      7902 FORD                 ANALYST                  7566 81/12/03       3000                    20
      7934 MILLER               CLERK                    7782 82/01/23       1300                    10

 

 

 

• with check option : 전체 뷰에 현재 조건 (where) 강제

create or replace view v_emp_job as select eno, ename, dno, job from employee where job='MANAGER' with check option;

//insert into v_emp_job values(9500,'이영희',30,'SALESMAN'); 
//ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다 

 

 

 

 

• with read only : 읽기 전용 뷰 => DML 작업 방지

SQL> create or replace view v_emp_job as select eno, ename, dno, job from employee where job='SALESMAN' with read only;

SQL> insert into v_emp_job values(9700,'q',10,'SALESMAN');

ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

 

 

 

 

데이터 무결성과 제약 조건

CREATE TABLE 테이블명 (컬럼명 타입,..,..., CONSTRAINT 유일키이름 PRIMARY KEY(컬럼명));

 

 

※ primary key(=기본키, 유일키) : 중복값 입력 불가

 

 

 

• constraint ~ primary key

create table dept_second(dno number(2) constraint pk_dept1 primary key, dname varchar2(14), loc varchar2(13));

 

 

 

 

• not null : null 입력 불가

create table customer(id varchar2(20) not null, pwd varchar2(20) not null, name varchar2(20) not null, phone varchar2(20), address varchar2(100));

 

 

 

 

• unique : 유일값만 입력 가능 (null도 가능)

create table customer2(id varchar2(20) unique, pwd varchar2(20) not null, name varchar2(20) not null, phone varchar2(20), address varchar2(100));