[Web_DBMS] 12

● VIEW

- 기존의 테이블은 그대로 놔둔 채 필요한 컬럼들 및 새로운 컬럼을 만든 가상 테이블

- 실제 데이터가 저장되는 것은 아니지만 VIEW를 통해서 데이터를 관리할 수 있다.

 

1. 독립성

- 다른 곳에서 접근하지 못하도록 하는 성질

 

2. 편리성

- 긴 쿼리문을 짧게 만드는 성질

 

3. 보안성

- 기존의 쿼리문이 보이지 않는다.

 

 

 

● VIEW 문법

CREATE VIEW VIEW이름 AS 쿼리문;

 

 

---

 

● 시퀀스

- 원하는 시작점을 설정할 수 있고, N씩 증가하는 값이다.

- 기본 시작점은 1이고, 기본 증가량은 1이다.

- 시퀀스명.NEXTVAL : 다음 시퀀스를 가져올 때

 

 

 

---

 

실습(DCL)

1. HR 계정의 DEPT 테이블을 새로 만든 계정으로 복사, 복사한 테이블에서 LOC별 평균 급여 검색

※ 쿼리 작성 전에 CMD에서 권한 부여를 해준다.

SQL> conn hr/hr
SQL> grant select on dept to baby;
SQL> grant select on emp to baby;

 

 

- 테이블 복사

CREATE TABLE COPY_DEPT AS SELECT * FROM HR.DEPT;

 

 

- LOC별 평균 급여 검색

CREATE TABLE SALARY_OF_LOCATION AS 
(SELECT LOC, ROUND(AVG(SAL), 2) "AVG"
FROM COPY_DEPT D JOIN HR.EMP E
ON D.DEPTNO = E.DEPTNO
GROUP BY LOC);

 

 

 

 

실습(VIEW)

※ 조회한 결과 테이블은 ORDER BY를 작성해서는 안된다.

※ CMD에서 VIEW 생성 권한을 부여한 후 진행한다.(grant create view to hr;)

 

1. PLAYER 테이블에서 나이 컬럼을 추가한 뷰 만들기

/*SYSDATE : 현재 시각*/
CREATE VIEW VIEW_PLAYER
AS
(SELECT TRUNC((SYSDATE - BIRTH_DATE) / 365) AGE, PLAYER.* FROM PLAYER);

 

 

※ 확인

SELECT * FROM VIEW_PLAYER;

 

 

 

2. EMPLOYEES 테이블에서 사원 이름과 그 사원의 매니저 이름이 있는 VIEW 만들기

※ FIRST_NAME과 LAST_NAME은 CONCATENATION을 사용하여 연결해준다.

CREATE VIEW VIEW_EMPLOYEES AS
(
   SELECT 
      E1.FIRST_NAME || ' ' || E1.LAST_NAME EMPLOYEE_NAME,
      E2.FIRST_NAME || ' ' || E2.LAST_NAME MANAGER_NAME,
      J.JOB_TITLE
   FROM EMPLOYEES E1, EMPLOYEES E2, JOBS J 
   WHERE E1.MANAGER_ID = E2.EMPLOYEE_ID AND E2.JOB_ID = J.JOB_ID
);

 

 

 

3. PLAYER 테이블에 TEAM_NAME 컬럼을 추가한 VIEW 만들기

CREATE VIEW VIEW_PLAYER AS
(SELECT T.TEAM_NAME, P.* FROM PLAYER P JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID);

 

 

 

 

실습(시퀀스)

1. 시퀀스 생성

CREATE SEQUENCE SEQ_USER;

 

 

 

2. 테이블 생성

CREATE TABLE TBL_USER(
	USER_NUMBER NUMBER PRIMARY KEY,
	USER_ID VARCHAR2(1000) UNIQUE,
	USER_PW VARCHAR2(1000),
	USER_NAME VARCHAR2(1000),
	USER_AGE NUMBER,
	USER_PHONE_NUMBER VARCHAR2(1000),
	STATUS NUMBER DEFAULT 0
);

 

 

 

3. 데이터 추가

INSERT INTO TBL_USER(USER_NUMBER, USER_ID, USER_PW, USER_NAME, USER_AGE, USER_PHONE_NUMBER)
VALUES(SEQ_USER.NEXTVAL, 'hong1234', '1234', '홍길동', '20', '01012341234');

 

 

 

4. 결과 확인

SELECT * FROM TBL_USER;