DB 연습하기 - 11

오늘·2021년 5월 5일
0

DB

목록 보기
11/14

with check option

컬럼 값을 변경을 못하게 한다

뷰를 정의하는 서브 쿼리문에 where절을 추가해 기본 테이블에서 정보가 추출되는 조건을 제시하게 되는데, 여기에 with check option을 기술하면 조건 제시를 위해 사용한 컬럼값이 뷰를 통해 변경되지 못한다.

-- deptno가 30인 것들만 추출해 뷰 만들기
create or replace view view_check30
as
select * from emp where deptno=30;

-- 확인
select * from view_check30;

-- 값 삽입하기
insert into view_check30 (empno, ename, job, deptno)
            values(1222, '홍길동', '과장', 20);

-- emp 안에도 그 값이 들어갔나요?
select * from emp; -- 네

-- 논리적 테이블인 view_check30 안에도 삽입 되어 있나요?
select * from view_check30; -- 아니요. 뷰 조건은 부서번호 30이고, 이건 20으로 만들었으니까요

-- 한예슬의 급여 +200 해주세요
update view_check30 set sal = sal+200 where ename='한예슬';
select * from view_check30;
select * from emp;

-- 한예슬의 deptno를 10번으로 변경
update view_check30 set deptno=10 where ename='한예슬';
select * from view_check30;
select * from emp;

여기까지는 오류없이 진행되고, 뷰의 내부가 변경된다.
그럼 이제 with check option을 붙여보자.

-- 뷰를 생성하는데 뷰의 조건에 위배되는 것을 삽입/삭제 하지 못하도록 생성
create or replace view view_check30 as
    select * from emp_copy where deptno=30 with check option;
    
-- 뷰의 조건에 위배되는 값을 삽입해보자 : 오류
insert into view_check30 (empno, ename, job, deptno)
            values(2222, '김자바', '과장', 20);
            
-- 30의 오지호를 10으로 변경 가능한가? : 오류
update view_check30 set deptno=10 where ename='오지호';

-- 조건에 위배되지 않는 다른 값 수정해보기 : 급여 +200 해주기
update view_check30 set sal=sal+200 where ename='오지호';  -- 업데이트 성공

with read only : 읽기 전용

삽입, 삭제 등이 불가능하고. 오직 읽는 것만 가능하게 하는 옵션

-- 서울에 사는 부서만 뽑아서 뷰로 만들기
create or replace view view_loc_seoul as
    select * from dept where loc='서울' with read only;
-- 확인
select * from view_loc_seoul;

-- insert 가능한가? : 아뇨 / 삭제 가능한가? : 아뇨 / update 가능한가? : 아뇨
insert into view_loc_seoul values (20, '인사부', '서울');
delete from view_loc_seoul;
update view_loc_seoul set deptno=30 where loc='서울';

Top-N

조건에 맞는 값을 순위대로 찾아보기

rownum 으로 Top-N 구하기

-- rownum : 오라클에서 자동으로 만들어주는 컬럼 명
select rownum, empno, ename, hiredate from emp order by hiredate asc;
-- null 값 지워주기
delete from emp where sal is null;



-- 사원 중 입사일이 빠른 순서대로 5명 찾기
-- 1) 입사일 순서대로 정렬해 뷰로 만든다
create or replace view emp_v as
    select empno, ename, hiredate from emp order by hiredate asc;
select * from emp_v; -- 확인

-- 2) 뷰로 된 것을 검색하는 rownum 을 붙인다
select rownum, empno, ename, hiredate from emp_v;

-- 3) rownum 을 조건으로 검색한다
select rownum, empno, ename, hiredate from emp_v
    where rownum <= 5;
    

-- 나이가 많은 순으로 10면 뽑기
-- 급여를 많이 받는 순으로 3명 찾기
-- 1) 급여 순으로 정렬해 뷰로 만든다
create or replace view emp_sal_v as
    select empno, ename, sal from emp order by sal desc;
select * from emp_sal_v; -- 확인

-- 2) 자동으로 순서를 붙인 fownum 으로 확인하기
select rownum, empno, ename, sal from emp_sal_v;

-- 3) rownum 을 조건으로 검색
select rownum, empno, ename, sal from emp_sal_v
    where rownum <= 3;



select * from student;
-- 나이가 null 인 사람 지우기
delete from student where age is null;

-- 1) 나이 순으로 정렬해 뷰로 만든다
create or replace view age_v as
    select stu_id, stu_name, age from student order by age desc;
-- 2) rownum 으로 확인하기
select rownum, stu_id, stu_name, age from age_v;
-- 3) rownum을 조건으로 검색
select rownum, stu_id, stu_name, age from age_v
    where rownum <= 10;

인라인 뷰로 Top-N 구하기

-- 사원 중 입사일이 빠른 순서대로 5명 찾기
select rownum, empno, ename, hiredate
    from (select empno, ename, hiredate from emp order by hiredate desc)
        where rownum <= 5;
        
-- 급여를 많이 받는 순으로 3명 찾기
select rownum, empno, ename, sal
    from (select empno, ename, sal from emp order by sal desc)
        where rownum <= 3;


-- 나이가 많은 순으로 10면 뽑기
select rownum, stu_id, stu_name, age
    from (select stu_id, stu_name, age from student order by age desc)
        where rownum <= 10;

0개의 댓글