SQL
<View>
- 물리적인 테이블에 근거한 논리적인 가상 테이블
- 기본테이블에 대한 하나의 쿼리문
- 긴 쿼리문을 뷰로 정의하면 접근을 단순화
(자주 사용하는 쿼리문을 간편하게 사용할 수 있음)
- 사용자에게 원하는 속성만 추출하여 데이터를 제공하므로써
보안에 유리.
<뷰 생성>
형식)
CREATE VIEW 뷰명
AS 서브쿼리;
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰명 [(alias)]
AS 서브쿼리
[WITH CHECK OPTION]
[WITH READ ONLY];
<뷰 삭제>
delete from emp_view
where empno=7900;
<뷰 입력>
insert into emp_view (empno,ename,deptno) values
(8000,'홍길동',30);
<뷰 수정>
update emp_view
set deptno=20
where empno=7654;
-- 뷰 관련 데이터딕셔너리
desc user_views
이름 널? 유형
----------------------------------------- -------- --------------
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
EDITIONING_VIEW VARCHAR2(1)
READ_ONLY VARCHAR2(1)
SQL> select view_name, text
from user_views;
VIEW_NAME
---------------------------
TEXT
---------------------------
EMP_VIEW
select empno,ename,deptno
from emp_copy
where deptno=30
or replace: 기존에 저장된 text를 overwirte하겠다.
force: 존재하지 않는 테이블이라도 앞으로 테이블을 만들 계획이므로 일단 text만 저장(강제)
<뷰에 별칭지정>
create or replace view emp_view (사원번호, 사원명, 급여, 부서번호)
as
select empno,ename,sal,deptno
from emp_copy
where deptno=30;
<그룹함수사용>
- 그룹함수는 반드시 별명과 함께 사용.
create or replace view emp_view
as
select deptno, count(*) 사원수, avg(sal) 급여평균
from emp_copy
group by deptno;
SQL> select * from emp_view;
DEPTNO 사원수 급여평균
---------- ---------- ----------
30 5 1800
20 6 2020.83333
10 3 2916.66667
delete from emp_view
where deptno=20;
ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
※ 뷰에 그룹함수를 정의했을때는 DML을 할 수 없음!
<with check option>
- check option에 정의된 조건식에 위배되는 insert,update 불가
<행삭제>
delete from emp_view
where empno=8000;
<행입력>
insert into emp_view (empno,ename,sal,deptno)
values (8001,'길라임',3000,30);
<행수정>
update emp_view
set sal = 3200
where empno=8001;
<with read only>
- 읽기(select)가능, 쓰기(delete,update,insert) DML불가능
<뷰검색>
select empno,ename,sal
from emp_view;
<뷰 입력,수정,삭제>
insert into emp_view(empno,ename,sal) values(8002,'김주원',2000);
--->ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
update emp_view set sal=5000;
--->ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
delete from emp_view;
--->ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
<rownum 테스트>
- rownum: 조회출력된 레코드에 순차적으로 부여하는 번호(1번부터~)
SQL> select rownum,empno,ename
from emp;
ROWNUM EMPNO ENAME
------ ---------- ------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
ROWNUM EMPNO ENAME
------ ---------- ------
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
<시퀀스>sequence
- 자동번호발생
- insert,update에 주로사용
create sequence test_seq;
사용법)
시퀀스명.currval - next된 현재 번호값 얻어오기
- 최초 한번이상의 nextval을 먼저사용후
currval을 할 수 있음.
시퀀스명.nextval - 발샐된 번호 뽑아오기
create sequence test_seq
start with 3
increment by 3
maxvalue 10
cycle
nocache;
3->6->9->3->6...
<nocycle>
drop sequence test_seq;
create sequence test_seq
start with 3
increment by 3
minvalue 3
maxvalue 10
nocycle --유일한 값
nocache;
3->6->9