Oracle 21/12/31

뷰

논리적으로 하나 이상의 테이블에 있는 데이터의 부분 집합 / 액세스 제한

사용목적
-데이터 액세스를 제한하기 위해
-복잡한 질의를 쉽게 작성하기 위해
-데이터 독립성을 제공하기 위해
-동일한 데이터로부터 다양한 결과를 얻기 위해

특징	단순 뷰	복합 뷰
테이블 수	하나	하나 이상
함수 포함	아니오	예
데이터 그룹 포함	아니오	예
뷰를 통한 DML 작업	예	불가능한 경우도 있음

단순 뷰 - 테이블의 원본 데이터를 보여줌
복합 뷰 - 테이블의 변형된 데이터를 보여줌

 

CREATE VIEW 문에 서브 쿼리를 포함

CREATE VIEW 테이블 명
(AS명)
AS 서브 쿼리

 

 

TABLE	VIEW
물리적 구조	물리적 구조 X(가짜/논리 TABLE)
저장 구조 할당해 데이터를 저장	데이터 자체 X
데이터 딕셔너리에 구조정보, 저장공간에 데이터를 저장	데이터 딕셔너리에 서브쿼리 문장의 텍스트를 저장 (문장을 재실행해서 테이블인 것 처럼 보이게한다) SELECT 문장을 잘 사용해야 VIEW를 잘 사용할 수 있다

테이블과 뷰와 JOIN 가능 / 뷰와 뷰와 조인 가능
뷰를 가상 테이블처럼 사용하기 때문에 테이블처럼 사용 가능

뷰의 장점
원본 테이블 변경 시 뷰의 값도 변경됨!!

뷰 수정
CREATE OR REPLACE VIEW 테이블명

 

복합 뷰 생성

뷰에 DML 작업을 수행하면 TABLE에 반영이 된다 => 단 뷰가 단순 뷰 상태일 때만 가능!

 

뷰 제거

DROP VIEW 뷰명;
=> 뷰는 하나의 독립된 객체이다( 뷰를 삭제해도 원본 테이블은 그대로 유지된다!)

 

인라인 뷰

FROM절에 서브 쿼리를 넣고 이름을 부여

SELECT문에 인라인 뷰를 넣을 수 있기 때문에 뷰를 새로 만들지 않아도 된다

서브 쿼리의 결과를 가상의 테이블 결과로 사용 가능

---

TOP-N 분석

ROWNUM - 출력이 된 행의 번호를 반환

주의사항

출력이 된 행에게 번호가 부여된다!

ROWNUM은 행보다 큰 항목은 출력할 수 없다

급여를 적게 받는 하위 3명의 사원의 이름과 급여를 출력

FROM절에서 인라인 뷰를 사용해야 정렬된 후의 값으로 ROWNUM을 가져온다
**FROM, WHERE 실행 순서 주의**
=> SELECT절의 LAST_NAME과 SALARY는 FROM절의 서브 쿼리 절에서 가져온다!

평균 급여가 많은 상위 3개 부서의 부서 번호와 평균 급여를 출력

=> 인라인 뷰에서 가져온 데이터에 네이밍 규칙이 적용이 된다! / 별칭 사용!

---

기타 데이터베이스 객체

인덱스

- 테이블에서 데이터를 검색할 때 검색 속도를 향상해주기 위해 만들어진 객체(전체 데이터의 5%~10% 사이의 양의 데이터를 탐색할 때 적절)

- 테이블의 컬럼 단위로 설치
- 데이터를 정렬해서 저장
- 데이터와 포인터(행의 주소 정보를 저장함)를 같이 저장
- BETWEEN A AND B를 사용할 때 탁월한 성능을 보여준다(이미 정렬이 되어있어서)
- PK, UK 설치하면 자동으로 인덱스가 설정된다!(중복된 데이터가 있을 때 인덱스는 비효율적)
- 데이터가 변경이 자주 일어나지 않은 곳에 설치하는 게 좋음
  EX_책의 목차(내용,,, PAGE)

 

시퀀스

중복이 되지 않은 고유한 번호를 생성기, 공유 가능한 객체, PK값에 사용(PK값이 숫자일 때만 사용 가능)

시퀀스 값 사이의 차이 값이 생길 수 있다.

EX) COMMIT, ROLLBACK현상 / 시퀀스는 공유가 되기 때문에 다른 유저가 사용 시 발생

*한번 추출된 값은 다시 추출하지 않음*

 

 

CREATE SEQUENCE

-INCREMENT BY => 증가 값

-START WITH => 첫 번째 생성이 되는 시퀀스 넘버

-MAXVALUE => 가장 마지막에 생성된 시퀀스 넘버

-NOCACHE(기본값) 생략 가능

-NOCYCLE(기본값) 생략 가능/ CYCLE 옵션 사용 시 9999 번호 도달했을 때 다시 120부터 시작

 

 

시퀀스 생성

NEXTVAL - 시퀀스에서 값을 추출

CURRVAL - 추출된 값을 저장

 

 

시퀀스 수정

=> START WITH값은 절대로 변경 불가

 

---

DCL

 

보안

1. 권한이 없는 사람이 DB에 접근 방지(외부)

2. 권한이 있는 사람이 불법 행위 방지(내부) => DCL언어를 통해 권한 부여/회수

 

DB 사용자

일반 사용자(응용프로그램을 통해)

개발자(DML)

DBA(모니터링) => DCL사용 가능

 

권한

시스템 권한
:데이터베이스를 액세스
-100가지 이상의 권한
새 사용자 생성, 사용자 제거, 테이블 제거, 테이블 백업

명령어

유저 생성

계정 잠금

계정 잠금해제

권한 부여
부여, 권한 이름, 부여 사용자

**CONNECT, RESOURCE**

권한 회수

 

롤 -권한을 모아놓은 집합(롤 생성 -> 롤 권한 부여 -> 사용자에게 롤을 부여)

 

객체 권한 - 데이터베이스 객체의 내용 관련
- 객체를 만든 소유주는 모든 객체에 대한 권한을 가지고 있음
- 내가 소유한 객체를 다른 유저에게 접근할 수 있도록 권한을 주는 것

GRANT 권한명
ON TABLE명
TO 사용자 계정명

WITH GRANT OPTION => 다른 사용자에게 받은 객체 권한을 또 다른 사용자에게 권한 이행을 할 수 있음

 

---

마지막 수업 과제

DROP TABLE MEMBER CASCADE CONSTRAINTS;
DROP TABLE TITLE CASCADE CONSTRAINTS;
DROP TABLE TITLE_COPY CASCADE CONSTRAINTS;
DROP TABLE RENTAL CASCADE CONSTRAINTS;
DROP TABLE RESERVATION CASCADE CONSTRAINTS;
DROP SEQUENCE MEMBER_ID_SEQ;
DROP SEQUENCE TITLE_ID_SEQ;
DROP VIEW TITLE_AVAIL;

--테이블 생성
CREATE TABLE MEMBER
(MEMBER_ID NUMBER(10),
 LAST_NAME VARCHAR2(25) NOT NULL,
 FIRST_NAME VARCHAR2(25),
 ADDRESS VARCHAR2(100),
 CITY VARCHAR2(30),
 PHONE VARCHAR2(15),
 JOIN_DATE DATE DEFAULT SYSDATE NOT NULL,
 CONSTRAINT MEMBER_MID_PK PRIMARY KEY(MEMBER_ID));

CREATE TABLE TITLE
(TITLE_ID NUMBER(10),
 TITLE VARCHAR2(60) NOT NULL,
 DESCRIPTION VARCHAR2(400) NOT NULL,
 RATING VARCHAR2(4),
 CATEGORY VARCHAR2(20),
 RELEASE_DATE DATE,
 CONSTRAINT TITLE_TID_PK PRIMARY KEY(TITLE_ID),
 CONSTRAINT TITLE_RAT_CK CHECK(RATING IN('G', 'PG', 'R', 'NC17', 'NR')),
 CONSTRAINT TITLE_CTG_CK CHECK(CATEGORY IN('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMEN', 'TARY')));

CREATE TABLE TITLE_COPY
(COPY_ID NUMBER(10),
 TITLE_ID NUMBER(10),
 STATUS VARCHAR2(15) NOT NULL,
 CONSTRAINT TITCP_CTID_PK PRIMARY KEY(COPY_ID, TITLE_ID),
 CONSTRAINT TITCP_TID_FK FOREIGN KEY(TITLE_ID) REFERENCES TITLE(TITLE_ID),
 CONSTRAINT TITCP_STTS_CK CHECK(STATUS IN('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED')));

CREATE TABLE RENTAL
(BOOK_DATE DATE DEFAULT SYSDATE,
 MEMBER_ID NUMBER(10),
 COPY_ID NUMBER(10),
 ACT_RET_DATE DATE,
 EXP_RET_DATE DATE DEFAULT SYSDATE +2,
 TITLE_ID NUMBER(10),
 CONSTRAINT RET_BKD_PK PRIMARY KEY(BOOK_DATE, MEMBER_ID, COPY_ID, TITLE_ID),
 CONSTRAINT RET_MID_FK FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),
 CONSTRAINT RET_CID_FK FOREIGN KEY(COPY_ID,TITLE_ID) REFERENCES TITLE_COPY(COPY_ID, TITLE_ID));

CREATE TABLE RESERVATION
(RES_DATE DATE,
 MEMBER_ID NUMBER(10),
 TITLE_ID NUMBER(10),
 CONSTRAINT RES_RESD_PK PRIMARY KEY(RES_DATE, MEMBER_ID, TITLE_ID),
 CONSTRAINT RES_MID_FK FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID),
 CONSTRAINT RES_TID_FK FOREIGN KEY(TITLE_ID) REFERENCES TITLE(TITLE_ID));

--시퀀스 생성
CREATE SEQUENCE MEMBER_ID_SEQ
INCREMENT BY 1
START WITH 101
MAXVALUE 99999;

CREATE SEQUENCE TITLE_ID_SEQ
INCREMENT BY 1
START WITH 92
MAXVALUE 99999;

--데이터 삽입
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
INSERT INTO TITLE
VALUES(TITLE_ID_SEQ.NEXTVAL, 'Willie and Christmas Too', 'All of Wille`s friends make a Christmas list for Santa, but Willie has yet to add his own wish list.', 'G', 'CHILD', TO_DATE('05-OCT-1995', 'DD-MON-YYYY'));
INSERT INTO TITLE
VALUES(TITLE_ID_SEQ.NEXTVAL, 'Alien Again', 'Yet another installation of science fiction history. Can the heroine save the planet from the alien life form?', 'R', 'SCIFI', TO_DATE('19-MAY-1995', 'DD-MON-YYYY'));
INSERT INTO TITLE
VALUES(TITLE_ID_SEQ.NEXTVAL, 'The Glob', 'A meteor crashes near a small American town and unleashes carnivorous goo in this classic.', 'NR', 'SCIFI', TO_DATE('12-AUG-1995', 'DD-MON-YYYY'));
INSERT INTO TITLE
VALUES(TITLE_ID_SEQ.NEXTVAL, 'My Day Off', 'With a little luck and a lot of ingenuity, a teenager skips school for a day in New York.', 'PG', 'COMEDY', TO_DATE('12-JUL-1995', 'DD-MON-YYYY'));
INSERT INTO TITLE
VALUES(TITLE_ID_SEQ.NEXTVAL, 'Miracles on Ice', 'A six-year-old has doubts about Santa Claus, but she discovers that miracles really do exist.', 'PG', 'DRAMA', TO_DATE('12-SEP-1995', 'DD-MON-YYYY'));
INSERT INTO TITLE
VALUES(TITLE_ID_SEQ.NEXTVAL, 'Soda Gang', 'After discovering a cache of drugs, a young couple find themselves pitted against a vicious gang.', 'NR', 'ACTION', TO_DATE('01-JUN-1995', 'DD-MON-YYYY'));

INSERT INTO MEMBER
VALUES(MEMBER_ID_SEQ.NEXTVAL, 'Carmen', 'Velasquez', '283 King Street', 'Seattle', '206-899-6666', TO_DATE('08-MAR-1990', 'DD-MON-YYYY'));
INSERT INTO MEMBER
VALUES(MEMBER_ID_SEQ.NEXTVAL, 'LaDoris', 'Ngao', '5 Modrany', 'Bratislava', '586-355-8882', TO_DATE('08-MAR-1990', 'DD-MON-YYYY'));
INSERT INTO MEMBER
VALUES(MEMBER_ID_SEQ.NEXTVAL, 'Midori', 'Nagayama', '68 Via Centrale', 'Sao Paolo', '254-852-5764', TO_DATE('17-JUN-1991', 'DD-MON-YYYY'));
INSERT INTO MEMBER
VALUES(MEMBER_ID_SEQ.NEXTVAL, 'Mark', 'Quick-to-See', '6921 King Way', 'Lagos', '63-559-7777', TO_DATE('07-APR-1990', 'DD-MON-YYYY'));
INSERT INTO MEMBER
VALUES(MEMBER_ID_SEQ.NEXTVAL, 'Audry', 'Ropeburn', '86 Chu Street', 'Hong Kong', '41-559-87', TO_DATE('18-JAN-1991', 'DD-MON-YYYY'));
INSERT INTO MEMBER
VALUES(MEMBER_ID_SEQ.NEXTVAL, 'Molly', 'Urguhart', '3035 Laurier', 'Quebec', '418-542-9988', TO_DATE('18-JAN-1991', 'DD-MON-YYYY'));

INSERT INTO TITLE_COPY
VALUES(1, 92, 'AVAILABLE');
INSERT INTO TITLE_COPY
VALUES(1, 93, 'AVAILABLE');
INSERT INTO TITLE_COPY
VALUES(2, 93, 'RENTED');
INSERT INTO TITLE_COPY
VALUES(1, 94, 'AVAILABLE');
INSERT INTO TITLE_COPY
VALUES(1, 95, 'AVAILABLE');
INSERT INTO TITLE_COPY
VALUES(2, 95, 'AVAILABLE');
INSERT INTO TITLE_COPY
VALUES(3, 95, 'RENTED');
INSERT INTO TITLE_COPY
VALUES(1, 96, 'AVAILABLE');
INSERT INTO TITLE_COPY
VALUES(1, 97, 'AVAILABLE');

INSERT INTO RENTAL
VALUES(SYSDATE-3, 101, 1, SYSDATE-2, SYSDATE-1,92);
INSERT INTO RENTAL
VALUES(SYSDATE-1, 101, 2, NULL, SYSDATE+1,93);
INSERT INTO RENTAL
VALUES(SYSDATE-2, 102, 3, NULL, SYSDATE,95);
INSERT INTO RENTAL
VALUES(SYSDATE-4, 106, 1, SYSDATE-2, SYSDATE-2,97);

--뷰 생성
CREATE VIEW TITLE_AVAIL
AS
SELECT T.TITLE, TC.COPY_ID, TC.STATUS, R.EXP_RET_DATE
FROM TITLE T, TITLE_COPY TC, RENTAL R
WHERE T.TITLE_ID=TC.TITLE_ID
AND TC.TITLE_ID=R.TITLE_ID(+)
AND TC.COPY_ID=R.COPY_ID(+)
ORDER BY T.TITLE, COPY_ID;

--6번 a
INSERT INTO TITLE
VALUES(TITLE_ID_SEQ.NEXTVAL, 'Interstellar Wars', 'Futuristic interstellar action movie. Can the rebels save the humans from the evil empire?', 'PG', 'SCIFI', TO_DATE('07-JUL-1997', 'DD-MON-YYYY'));
INSERT INTO TITLE_COPY
VALUES(1, 98, 'AVAILABLE');
INSERT INTO TITLE_COPY
VALUES(2, 98, 'AVAILABLE');

--6번 b
INSERT INTO RESERVATION
VALUES(SYSDATE, 101, 98);
INSERT INTO RESERVATION
VALUES(SYSDATE, 104, 97);

--6번 c
DELETE FROM RESERVATION
WHERE MEMBER_ID=101;
INSERT INTO RENTAL
VALUES(SYSDATE, 101, 1, NULL, SYSDATE+2,98);
UPDATE  TITLE_COPY
SET STATUS='RENTED'
WHERE COPY_ID=1
AND TITLE_ID=98;

--7번 a
ALTER TABLE TITLE ADD PRICE NUMBER(8,2);

--7번 b
UPDATE TITLE
SET PRICE=25
WHERE TITLE_ID=92;
UPDATE TITLE
SET PRICE=35
WHERE TITLE_ID=93;
UPDATE TITLE
SET PRICE=35
WHERE TITLE_ID=94;
UPDATE TITLE
SET PRICE=35
WHERE TITLE_ID=95;
UPDATE TITLE
SET PRICE=30
WHERE TITLE_ID=96;
UPDATE TITLE
SET PRICE=35
WHERE TITLE_ID=97;
UPDATE TITLE
SET PRICE=29
WHERE TITLE_ID=98;

--7번 c
ALTER TABLE TITLE MODIFY PRICE NOT NULL;