Oracle 정리 - Chapter.07

[ VIEW ]

 

- 다른 테이블이나 뷰에 포함된 맞춤표현(virtual table)

join하는 테이블의 수가 늘어나거나 질의문이 길고 복잡해지면 작성이 어려워지고 유지보수가 어려울 수 있다.

이럴 때는 스크립트를 만들어두거나 stored query를 사용해서 데이터베이스 서버에 저장해두면 필요할 때 마다 호출해서 사용할 수 있다

 

- 뷰와 테이블의 차이는 '뷰'는 실제로 데이터를 저장하고 있지 않다는 점이다.

- 베이스테이블(Base table) : 뷰를 통해 보여지는 실제테이블

- 선택적인 정보만 제공 가능

 

[형식]

create [or replace][force|noforce] view 뷰이름 [(alias [,alias,.....)]
as 서브쿼리
[with check option [constraint 제약조건이름]]
[with read only [constraint 제약조건이름]]

 

- create or replace

 : 지정한 이름의 뷰가 없으면 새로 생성, 동일이름이 있으면 수정

 

- force | noforce

  force   : 베이스테이블이 존재하는 경우에만 뷰 생성가능

  noforce : 베이스테이블이 존재하지 않아도 뷰 생성가능

 

- alias

  뷰에서 생성할 표현식 이름(테이블의 컬럼 이름의미)

  생략하면 서브쿼리의 이름 적용

  alias의 개수는 서브쿼리의 개수와 동일해야 함

 

- 서브쿼리 : 뷰에서 표현하는 데이터를 생성하는 select구문

 

여기서 잠깐!

'제약조건'은 시험에 단골로 출제되는 문제이므로 반드시 외우고 넘어가자!

 

 

- 제약조건

  ① with check option

  : 뷰를 통해 접근가능한 데이터에 대해서만  DML작업가능

 

  ② with read only

  : 뷰를 통해 DML작업 안됨

    제약조건으로 간주되므로 별도의 이름지정가능

 

[뷰 - 인라인(inline)개념]

  : 별칭을 사용하는 서브쿼리 (일반적으로 from절에서 사용)

 

[뷰 - Top N분석]

  Top N분석 : 조건에 맞는 최상위(최하위) 레코드를 N개 식별해야 하는 경우에 사용

    예) 최상위 소득자3명

         최근 6개월동안 가장 많이 팔린 제품3가지

         실적이 가장 좋은 영업사원 5명

  

   오라클에서 Top N분석원리

      - 원하는 순서대로 정렬

      - rownum 이라는 가상의 컬럼을 이용하여 순서대로 순번부여

      - 부여된 순번을 이용하여 필요한 수만큼 식별

      - rownum값으로 특정행을 선택할 수 없음

        (단, Result Set  1st  행(rownum=1)은 선택가능)

 

ex1) 사원테이블에서 부서가 90인 사원들을 v_view1으로 뷰테이블을 만드시오

     (사원ID, 사원이름, 급여, 부서ID만 추가)

 

create or replace view v_view1

as select employee_id, last_name, salary, department_id from employees

where department_id=90;

 

select * from v_view1;

 

delete from v_view1;

- error

ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found

 

 

[문제1] 사원테이블에서 급여가 5000 이상 10000 이하인 사원들만 v_view2으로 뷰를 만드시오.

(사원ID, 사원이름, 급여, 부서ID)

 

 

[문제1 - 정답]

 

 

ex2) v_view2 테이블에서 103사원의 급여를 9000.00에서 12000.00으로 수정하시오

 

select * from v_view2;

update v_view2 set salary=12000 where employee_id=103;

 

select * from v_view2; ← 103사원이 빠졌음(범위를 벗어남)

select * from employees where employee_id=103; -- 원본인 employees 의 내용도 바뀌었다

=> as로 인해 employee_id가 사원ID로 변경되었으므로 employee_id라고 쓰면 안된다.

원본이 바뀌면 view도 같이 바뀐다

원본을 다시 9000으로 변경하면 v_view2의 내용도 바뀌어서 103사원이 다시 들어온 것을 확인할 수 있다.

Update employees set salary=9000 where employee_id=103;

 

select * from employees where employee_id=103;

select * from v_view2; -- 103 사원이 조건에 맞아서 다시 들어왔다.

 

[문제2] 사원테이블과 부서테이블에서 사원번호, 사원명, 부서명을 v_view3로 뷰 테이블을 만드시오

조건1) 부서가 10, 90인 사원만 표시하시오

조건2) 타이틀은 사원번호, 사원명, 부서명으로 출력하시오e

조건3) 사원번호로 오름차순 정렬하시오

 

[문제2 - 정답]

 

[문제3] 부서ID가 10, 90번 부서인 모든 사원들의 부서위치를 표시하시오

 조건1) v_view4로 뷰 테이블을 만드시오

 조건2) 타이틀을 사원번호, 사원명, 급여, 입사일, 부서명, 부서위치(city)로 표시하시오

 조건3) 사원번호 순으로 오름차순 정렬하시오

 조건4) 급여는 백 단위 절삭하고, 3자리 마다 콤마와 '원'을 표시하시오

 조건5) 입사일은  '2004년 10월 02일' 형식으로 표시하시오

 

[문제3 - 정답]

 

 

 

?오류가 발생한다. 왜일까?

별칭을 붙이지 않으면 error가 발생한다

 

- 단순 VIEW에서 그룹함수를 사용하기 위해서는 그룹함수가 실제 필드가 아닌 '가상 필드' 이기 때문에 반드시 그룹함수에 '별칭' 을 지정해야 한다

 

 

 

 

 

 

 

 

 

ex3) 뷰에 제약조건달기

사원테이블에서 업무ID  'IT_PROG'인 사원들의 사원번호, 이름, 업무ID만 v_view5 뷰 테이블을 작성하시오.

단, 수정 불가의 제약조건을 추가 하시오

create or replace view v_view5
as select employee_id, last_name, job_id
from employees
where job_id='IT_PROG'
with read only;

select * from v_view5;

 

delete from v_view5;

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

42399.0000 - "cannot perform a DML operation on a read-only view"

 

ex4) 뷰에 제약조건 달기

사원테이블에서 업무ID 'IT_PROG'인 사원들의 사원번호, 이름, 이메일, 입사일, 업무ID만 v_view6 뷰 테이블을 작성하시오,

단 업무ID가 'IT_PROG'인 사원들만 추가, 수정할 수 있는 제약조건을 추가하시오

create or replace view v_view6
as select employee_id, last_name, email, hire_date, job_id
from employees
where job_id='IT_PROG'
with check option;

select * from v_view6;

 

insert into v_view6(employee_id, last_name, email, hire_date, job_id)

values(500,'kim','candy','2004-01-01','Sales');

→ 에러:with check option제약조건에 위배

 

update v_view6 set job_id='Sales' where employee_id=103;

→ 에러:with check option제약조건에 위배

 

insert into v_view6(employee_id, last_name, email, hire_date, job_id)

values(500,'kim','candy','2004-01-01','IT_PROG');

 

select * from v_view6;

 

delete from v_view6;

ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found

 

[문제4]

테이블명 : bookshop

isbn      varchar2(10)  -- 기본키 (제약조건명 : PISBN)

title       varchar2(50)  -- 널값 허용X (제약조건명 : CTIT) --책제목

author    varchar2(50)  -- 저자

price      number      -- 금액

company  varchar2(30)  -- 출판사

 

데이터

is001   자바3일완성   김자바   25000   야메루출판사

pa002  JSP달인되기   이달인  28000   공갈닷컴

or003  오라클무작정따라하기   박따라   23500   야메루출판사

 

테이블명 : bookorder

idx   number        primary key    -- 일련번호        

isbn  varchar2(10)   FKISBN         -- bookshop의 isbn의 자식키

qty   number                           -- 수량

 

시퀀스명 : idx_seq  증가값: 1  시작값 1  NOCACHE  NOCYCLE

 

데이터

1   is001      2

2   or003     3

3   pa002     5

4   is001      3

5   or003     10

↑

시퀀스객체 이용

 

뷰 명 : bs_view

책제목        저자      총판매금액

-----------------------------------------

조건1) 총판매금액은 qty * price로 하시오

조건2) 수정불가의 제약조건을 추가하시오

 

 

[문제4 - 정답]

 

테이블명 : bookshop

isbn      varchar2(10)  -- 기본키 (제약조건명 : PISBN)

title       varchar2(50)  -- 널값 허용X (제약조건명 : CTIT) --책제목

author    varchar2(50)  -- 저자

price      number      -- 금액

company  varchar2(30)  -- 출판사

create table bookshop(
isbn VARCHAR2(10) constraint PISBN primary key,
title varchar2(50) constraint CTIT not null,
author varchar2(50),
price number,
company varchar2(30));

 

데이터

is001   자바3일완성   김자바   25000   야메루출판사

pa002  JSP달인되기   이달인  28000   공갈닷컴

or003  오라클무작정따라하기   박따라   23500   야메루출판사

insert into bookshop(isbn, title, author, price, company)
values('is001', '자바3일완성', '김자바', 25000, '야메루출판사' );

insert into bookshop(isbn, title, author, price, company)
values('pa002', 'JSP달인되기','이달인',28000,'공갈닷컴' );

insert into bookshop(isbn, title,author, price, company)
values('or003', '오라클무작정따라하기 ','박따라  ',23500,'야메루출판사' );
commit;

 

테이블명 : bookorder

idx   number        primary key    -- 일련번호        

isbn  varchar2(10)   FKISBN         -- bookshop의 isbn의 자식키

qty   number                           -- 수량

create table bookorder(
idx number primary key ,
isbn varchar2(10) constraint FKISBN references bookshop(isbn),
qty number);
-------------------------- 첫 번째 방법

create table bookorder(
idx number primary key,
isbn varchar2(10),
qty number,
constraint FKISBN foreign key(isbn) references bookshop);
-------------------------- 두 번째 방법

 

시퀀스명 : idx_seq  증가값 1  시작값 1  NOCACHE  NOCYCLE

 

데이터

1   is001      2

2   or003     3

3   pa002     5

4   is001      3

5   or003     10

↑

시퀀스객체 이용

create sequence idx_seq nocache nocycle;

insert into bookorder(idx, isbn, qty) values(idx_seq.nextval, 'is001', 2);
insert into bookorder(idx, isbn, qty) values(idx_seq.nextval, 'or003', 3);
insert into bookorder(idx, isbn, qty) values(idx_seq.nextval, 'pa002', 5);
insert into bookorder(idx, isbn, qty) values(idx_seq.nextval, 'is001', 3);
insert into bookorder(idx, isbn, qty) values(idx_seq.nextval, 'or003', 10);
commit;

select * from bookshop;
select * from bookorder;

 

뷰 명 : bs_view

책제목        저자      총판매금액

-----------------------------------------

조건1) 총판매금액은 qty * price로 하시오

조건2) 수정불가의 제약조건을 추가하시오

create or replace view bs_view(책제목, 저자, 총판매금액)
as select title, author, sum(price*qty)
   from bookshop
   join bookorder using(isbn)
   group by (title, author)
with read only;

select * from bs_view;

 

ex5) 뷰 - 인라인

사원테이블을 가지고 부서별 평균급여를 뷰(v_view7)로 작성하시오

조건1) 반올림해서 100단위까지 구하시오

조건2) 타이틀은 부서ID, 부서평균

조건3) 부서별로 오름차순 정렬 하시오

조건4) 부서ID가 없는 경우 5000으로 표시하시오

 

create or replace view v_view7("부서ID", "부서평균")
as select nvl(department_id, 5000), round(avg(salary), -3)
from employees
group by department_id
order by department_id asc;

select * from v_view7;

[구문 해석 - 뷰 객체 이용]

as select nvl(department_id, 5000) → 만약 값이 비어있을 경우(null), '5000'으로 채워라.(아무 숫자나 넣어도 상관없음)

round(avg(salary), -3) → 급여의 평균을 구한 다음, 백의 자리에서 반올림 시켜라.

 

----------------------------------------

select 부서ID, 부서평균
from (select nvl(department_id, 5000) "부서ID", round(avg(salary), -3) "부서평균"
from employees
group by department_id
order by department_id asc);

[구문 해석 - 인라인(sub쿼리) 이용] (**인라인은 from 다음에 바로 select문이 나온다. 여기선 select가 테이블 역할을 하는 셈.)

위 뷰 객체를 이용한 구문과 같은 결과값을 얻는다.

인라인은 sub쿼리에 해당하는데, 그 특징을 살짝 알고 넘어가자.

 

 

 

 

[문제5]

      1. 부서별 최대급여를 받는 사원의 부서명, 최대급여를 출력하시오

      2. 1번 문제에 최대급여를 받는 사원의 이름도 구하시오

 

[문제5 - 정답]

 

1.

1번 결과창

2.

2번 결과창

 

 

ex6) Top N분석(인라인 이용)

급여를 가장 많이 받는 사원 3명의 이름, 급여를 표시 하시오

select rownum, last_name, salary
from (select last_name, nvl(salary,0) as salary from employees order by 2 desc)
where rownum<=3;

- from절 뒤에 select가 왔다는 건 inline으로 취급되고 있다는 뜻

- 안쪽에 있는 select를 먼저 처리한다.

- (select last_name, nvl(salary,0) as salary from employees order by 2 desc) -가 하나의 테이블 역할을 한다.

- rownum은 Oracle자체에서 만든 것.

 

 

ex7) 최고급여를 받는 사원1명을 구하시오

select rownum, last_name, salary
from (select last_name, nvl(salary,0) as salary from employees order by 2 desc)
where rownum=1; ← rownum=2는 error (특정 행은 사용할 수 없음)

- 처음부터 결과를 꺼내오려면 꺼낼 수 있지만 가운데 껴있는 값들은 꺼낼 수 없다는 단점이 있다.

 

 

ex8) 급여의 순위를 내림차순 정렬 했을 때, 3개씩 묶어서 2번째 그룹을 출력하시오

     (4,5,6 순위의 사원 출력 : 페이징 처리 기법)

select * from
(select rownum , ceil(rownum/3) as page, tt.* from
(select last_name, nvl(salary,0) as salary from employees order by salary desc) tt
) where page=2; 

select * from
(select rownum rn, tt.* from
(select last_name, nvl(salary,0) as salary from employees order by 2 desc) tt
) where rn>=4 and rn<=6;

[첫 번째 구문 해석]

(select last_name, nvl(salary,0)as salary from employees order by salary desc)에게 우선권이 있다

①  꺼내온 값을 tt라고 명시한다. (tt의 모든 항목 : last_name & salary)

② (select last_name, nvl(salary,0)as salary from employees order by salary desc)로 부터 rownum을 붙여준다.

③ ceil : 소수점 이하 전부 올림.

④ tt* : tt라는 항목을 전부 꺼내와라.

⑤ select로부터 page가 2인 값을 꺼내와라

 

 

[문제6] 사원들의 연봉을 구한 후 최하위 연봉자 5명을 추출하시오

 조건1) 연봉 = 급여*12+(급여*12*커미션)

 조건2) 타이틀은 사원이름, 부서명, 연봉

 조건3) 연봉은 ￦25,000 형식으로 하시오

 

[문제6 - 정답]

 

 

[ SYNONYM ]

- Synonym은 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말한다

- Synonym은 Object가 아니라 Object에 대한 직접적인 참조이다

- 데이터베이스의 투명성을 제공하기 위해서 사용 한다

  다른 유저의 객체를 참조할 때 많이 사용 한다

- 객체의 긴 이름을 짧게 만들어 SQL 코딩을 단순화 할 수 있다

- 객체의 실제 이름, 소유자, 위치를 감추기 때문에 데이터베이스의 보안을 유지할 수 있다

 

* 종류

Private Synonym

전용 Synonym은 특정 사용자만 사용할 수 있다

 

Public Synonym

공용 Synonym은 사용자 그룹이 소유하면 그 데이터베이스에 있는 모든 사용자가 공유한다

 

[형식]

CREATE [PUBLIC] SYNONYM 시노님이름 FOR 객체이름

 

[실습]

 

1. HR 계정으로 접속해서 C##JAVA 계정에게 EMPLOYEES 테이블을 조작할 수 있는 권한 부여

 

HR 계정에서 실습

grant all on employees to c##java;

 

2. C##JAVA 계정에 접속해서 Synonym(동의어)를 생성

hr계정의 employees 테이블을 java계정에서 hr_emp 동의어로 사용한다

 

CREATE SYNONYM Synonym이름 FOR 다른 계정의 테이블명

 

C##JAVA 계정에서 실습

create synonym hr_emp for hr.employees;

ORA-01031: 권한이 불충분합니다

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a database operation without

           the necessary privileges.

*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

 

★ 먼저 SYNONYM를 생성할 수 있는 권한이 있어야 한다

   SYSTEM 계정(관리자 계정)에서 권한을 부여한다

 

SYSTEM 계정에서 실습

grant create synonym to c##java;

 

다시 C##JAVA 계정에서

create synonym hr_emp for hr.employees;

select * from user_synonyms;

 

3. 쿼리

select * from hr.employees;

이런 식으로 사용하면 SQL문이 길어질 때 테이블명이 길어서 문제가 되고 다른 스키마(계정)에 있는 객체의 위치를 알려주게 되어 보안상 안 좋다

 

select * from hr_emp; - Synonym 이용

Synonym 이름을 짧게 하여 SQL문 길이도 줄이고 보안유지도 되기 때문에 사용 한다

 

4. 삭제

DROP SYNONYM 시노님명

drop synonym hr_emp;

 

select * from user_synonyms;

Synonym 동의어가 삭제된 것을 확인할 수 있다