210531 oracle - CONSTRAINT, SEQUENCE

강의목표

1.VIEW의 개념을 이해하고 기술할 수 있다.
2. PL/SQL의 문법을 이해하고 실행할 수 있다.

============================================================================
뷰
  - 다른 테이블이나 뷰에 포함된 데이터의 맞춤 표현 Tailored Presentation

  - STORED QUERY VIRTUAL TABLE로 간주되는 DB객체

  - 하나 또는 하나 이상의 테이블 / 뷰에 포함된 데이터 부분 집합을 나타내는 논리적인 객체
        -> 선택적인 정보만 제공 가능

  - 자체적인 데이터를 포함하지 않는다.

  - 베이스테이블 : 뷰를 통해 보여지는 데이터를 포함하고 있는 실제 테이블

============================================================================
사용목적 및 장점
  1. Restricted data access
        뷰에 접근하는 사용자는 미리 정의된 결과만 볼 수 있음
        ==> 데이터 접근을 제한함으로써 중요한 데이터를 보호할 수 있다.

  2. Hide data complexity
        여러 테이블을 조인하는 등 복잡한 SQL 구문을 사용하는 경우
        자세한 SQL 구문의 내용을 숨길 수 있다.

  3. Simplify statement for the user
        복잡한 SQL 구문을 모르는 사용자라도 SELECCT 구문만으로 결과를 조회할 수 있다.

  4. Present the data in different perspective
        뷰에 포함된 컬럼은 참조 대상 테이블에 영향을 주지 않고 다른 이름으로 참조 가능하다.

  5. Isolate applications from changes in definitions of base tables
        베이스테이블에 포함된 여러 개의 컬럼 중 일부만 사용하도록 뷰를 생성한 경우, 
        뷰가 참조하지 않는 나머지 컬럼이 변경되어도 뷰를 사용하는 다른 프로그램들은 영향X

  6. Save complex queries
        복잡한 SQL 구문을 뷰 형태로 저장해서 반복적으로 사용이 가능하다.

============================================================================
생성구문
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name[ (alias ,,, ) ]
AS subquery
[WITH CHECH OPTION [CONSTRAINT constraint_name] ]
[WITH READ ONLY [CONSTRAINT constraint_name] ]

  - CREATE [OR REPLACE] : 지정한 뷰가 없으면 새로 생성, 동일 이름이 존재하면 수정
  - FORCE : 베이스테이블이 존재하지 않아도 뷰 생성 가능
  - NOFORCE : 베이스테이블이 존재해야만 뷰 생성 가능

  - alias : 뷰에서 사용되는 컬럼명, 생략하게 되면 subquery가 사용한 컬럼명을 사용한다.
             subquery에서 사용한 SELECT LIST 개수와 일치
  - 제약조건 : 제약조건으로 간주되므로 이름 지정 가능
      - WITH CHECH OPTION : 뷰를 통해서 접근 가능한 데이터에 대해서만 DML 작업 허용
                                   WHERE절에 안맞는 데이터가 베이스에 추가됐을 때 뷰에 반영할지 여부
      - WITH READ ONLY : 뷰를 통해 DML 작업을 허용 안함

============================================================================
VIEW - 데이터 조회 절차
VIEW를 사용한 SQL 구문해석
  - 데이터 딕셔너리 "USER_VIEWS"에서 뷰 정의 검색
  - SQL 구문을 실행한 계정이 관련된 베이스테이블에 접근하여 SELECT 할 수 있는 권한이 있는지 확인
  - 뷰 대신 베이스테이블을 기반으로 하는 동등한 작업으로 변환
  - 베이스테이블을 대상으로 데이터 조회

============================================================================
PL/SQL (Procedual Language / SQL / Oracle's Procedural Language extension to SQL)의 약어
  - 오라클 DB환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어

  - PL/SQL에서는 프로그램 단위를 BLOCK 이라고 부르며 애플리케이션 로직들을 작성

  - 변수정의, 조건처리, 반복문, Procedure Language 이다.

  - 모듈화 PL/SQL의 장점
<< label >> (optional)
DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, & subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions (errors) raised in executable part]
END;

CMD에서 하려면 SET SERVEROUTPUT ON을 쳐줘야한다.
CMD에서 선언이후 실행은 / 이다.

=================================================================

예제
Q1) system 로그인해서 MYDB / admin1234 생성
create user MYDB identified by admin1234;


Q2) 권한은 도움말에서 그대로
grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, - 
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to MYDB;


Q3) 로그인해서 VIEW 생성
CONN MYDB/admin1234

CREATE OR REPLACE VIEW V_EMP
AS 
SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 30;


Q4) VIEW 생성 정보를 확인해보자. USER_TAB_COLS
SELECT COLUMN_NAME, DATA_TYPE, NULLABLE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'V_EMP';


Q5) 조인으로 뷰를 만들어보자
CREATE OR REPLACE VIEW V_EMP_DEPT
AS 
SELECT ENAME, DNAME, JOB
FROM EMP LEFT JOIN DEPT USING (DEPTNO);

SELECT *
FROM V_EMP_DEPT;


Q6) ALIAS를 이용하여 테이블 컬럼에 해당하는 VIEW 객체를 생성하자
CREATE OR REPLACE VIEW V_EMP_DEPT02(M_NAME, M_DNAME, M_JOB)
AS 
SELECT ENAME, DNAME, JOB
FROM EMP LEFT JOIN DEPT USING (DEPTNO);

SELECT *
FROM V_EMP_DEPT02;


Q7) V_EMP 뷰의 내용 확인
SELECT *
FROM V_EMP;


Q8) 베이스테이블에 값을 추가하자
INSERT INTO EMP(EMPNO, ENAME, DEPTNO) 
VALUES(111, '111', 30);


Q9) 베이스테이블의 값 변경이 뷰에 반영되었는지 객체를 확인해보자
SELECT *
FROM V_EMP;    --> 베이스테이블에 추가된 데이터를 확인할 수 있다.


Q10) VIEW에 값을 추가해보자    --> 실패
INSERT INTO V_EMP(ENAME, DEPTNO)
VALUES('222', 30);    --> EMP TABLE의 PK에 걸려서 추가되지 않는다.


Q11) VIEW의 값을 수정해보고 베이스테이블에 반영되는데 확인해보자
UPDATE V_EMP
SET DEPTNO=NULL;

SELECT *
FROM EMP;    --> VIEW에서 변경한 값이 베이스테이블에 반영된다.

ROLLBACK;    --> VIEW에도 롤백이 적용된다.


Q12) WITH READ ONLY를 이용한 제약조건을 포함하여 VIEW 생성
CREATE OR REPLACE VIEW V_EMP
AS
SELECT *
FROM EMP
WITH READ ONLY;

UPDATE V_EMP
SET DEPTNO=NULL;    --> READ ONLY라 에러 발생


Q13) 테스트용 베이스테이블 생성
CREATE TABLE EMP_TEST
AS
SELECT ENAME, SAL
FROM EMP
WHERE 1=0;

ALTER TABLE EMP_TEST
ADD MARRIAGE CHAR(2);

DESC EMP_TEST;

INSERT INTO EMP_TEST VALUES('111', 3000, 'Y');
INSERT INTO EMP_TEST VALUES('222', 4000, 'Y');
INSERT INTO EMP_TEST VALUES('333', 7000, 'N');
INSERT INTO EMP_TEST VALUES('444', 3000, 'Y');
INSERT INTO EMP_TEST VALUES('555', 5500, 'N');
INSERT INTO EMP_TEST VALUES('666', 10000, 'Y');


Q14) 옵션을 지정한 다음 DML을 확인하자
CREATE OR REPLACE VIEW V_EMP
AS
SELECT *
FROM EMP_TEST
WHERE MARRIAGE = 'N'
WITH CHECK OPTION;    --> WHERE 문의 조건에 맞는 값을 체크해서 DML을 수행

INSERT INTO EMP_TEST VALUES('777', 80000, 'Y');
SELECT * FROM EMP_TEST;    --> 베이스테이블은 데이터가 추가된다.
SELECT * FROM V_EMP;    --> 베이스테이블엔 안들어 간다.

INSERT INTO EMP_TEST VALUES('888', 800, 'N');
SELECT * FROM EMP_TEST;    --> 베이스테이블은 데이터가 추가된다.
SELECT * FROM V_EMP;    --> 베이스테이블에 들어간다.


Q15) USER_VIEWS
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS
WHERE VIEW_NAME = 'V_EMP';


Q16) 별칭에 "" 구현하자
CREATE OR REPLACE VIEW V_EMP_DEPT02("M_NAME", "M_DNAME", "M_JOB")
AS 
SELECT ENAME, DNAME, JOB
FROM EMP LEFT JOIN DEPT USING (DEPTNO);

SELECT M_NAME, M_JOB
FROM V_EMP_DEPT02
WHERE "M_JOB" = 'CLERK';    --> 별칭에 ""를 줬으면 조건 검색할 때 줘야할 수도 있다.


Q18) 뷰를 삭제해보자
DROP VIEW V_EMP_DEPT02;


Q19) View를 이용해서 부서별 평균 월급보다 더 많은 월급을 받는 사원과 월급을 출력해보자.
CREATE OR REPLACE VIEW V_DEPT_SALAVG(D_ID, D_AVG)
AS
SELECT NVL(DEPTNO, 10), ROUND(AVG(SAL), -1)
FROM EMP
GROUP BY DEPTNO;

SELECT * FROM V_DEPT_SALAVG;

SELECT ENAME, SAL
FROM EMP
JOIN V_DEPT_SALAVG ON (NVL(DEPTNO, 10) = D_ID)
WHERE SAL > D_AVG
ORDER BY 2 DESC;


Q20) 인라인 뷰(Inline View)를 이용해서 부서별 평균 월급보다 더 많은 월급을 받는 
       사원과 월급을 출력해보자.
SELECT ENAME, SAL
FROM (SELECT NVL(DEPTNO, 10) AS D_ID, ROUND(AVG(SAL), -1) AS D_AVG
          FROM EMP
          GROUP BY DEPTNO) V_DEPT_SALAVG
JOIN EMP ON (NVL(DEPTNO, 10) = V_DEPT_SALAVG.D_ID)
WHERE SAL > D_AVG
ORDER BY 2 DESC;