[국비지원] KH 정보교육원 47일차 (1/2)

kh day 047

 

 SQL도 종반을 향해 달려가고 있다. 아마 이번주 안으로 끝나지 않을까 싶다. 이제 프론트로 곧 넘어갈텐데, 프론트도 3주가량 지나면 배울건 다 배운 셈이다. 이제 파이널 프로젝트를 준비할텐데 잘 할 수 있을까? 나 자신에게 물어보자. 확신에 찬 답이 나오지 않는다면 더 노력해야한다...

 

------------------------------------------------------
제약조건 활성화/비활성화

가. 기존 테이블의 제약조건을 필요에 의해 Enable/Disable 가능
나. 제약조건은 데이터의 무결성은 보장받을 수 있으나, 성능은 떨어뜨림
다. 예: 데이터의 무결성이 보장되는 방대한 데이터를, 테이블에 저장시 사용 
------------------------------------------------------
ALTER TABLE 테이블명
DISABLE | ENABLE CONSTRAINT 제약조건명 [CASCADE];

* ENABLE: 제약조건 활성화
* DISABLE: 제약조건 비활성화
* CASCADE: 해당 제약조건과 관련된 모든 제약조건을 연쇄적으로 비활성화
------------------------------------------------------

-- PK 제약조건 비활성화
ALTER TABLE emp05
DISABLE CONSTRAINT emp05_empno_pk;

-- PK 제약조건 활성화
ALTER TABLE emp05
ENABLE CONSTRAINT emp05_empno_pk;

-- 데이터사전: PK 제약조건 비활성화 확인
SELECT
    table_name,
    constraint_type,
    constraint_name,
    status
FROM
    user_constraints
WHERE
    table_name IN ('EMP05');


------------------------------------------------------
데이터 사전(Data Dictionary View) 3종류

(1) DBA_XXXX : 데이터베이스 관리자만 접근가능한 객체 등의 정보조회
(2) ALL_XXXX : 자신계정 소유 또는 권한을 부여받은 객체 등에 관한 정보조회
(3) USER_XXXX: 자신의 계정이 소유한 객체 등에 관한 정보조회
------------------------------------------------------
* 데이터 사전(Data Dictionary View)에 저장되는 정보 *
------------------------------------------------------
가. DB의 물리적 구조 또는 객체의 논리적 구조
나. Oracle 사용자와 스키마 객체명
다. 각 사용자에게 부여된 권한과 롤(role)
라. 무결성 제약조건
마. 컬럼 기본값
바. 스키마 객체에 할당된 영역의 크기와 현재 사용중인 영역의 크기
사. DB 이름/버전/생성날짜/시작모드/인스턴스명 같은 일반정보
------------------------------------------------------


------------------------------------------------------
View

가. 물리적인 테이블 or 다른 View를 기반으로 하는 논리적인 테이블
나. 물리적인 테이블처럼, 실제 데이터를 저장하지 않음
다. 사용자는 마치 진짜 테이블을 사용하는 것과 동일하게 사용
라. 기본테이블(Base table) : 뷰의 기반의 되는 물리적인 테이블
마. 목적
 a. 데이터를 선택적으로 보여줄 수 있음
 b. 데이터에 대한 접근을 제한할 수 있음(접근제한)
 c. 테이블 컬럼 중, 보안과 관련된 민감한 데이터를 가진 컬럼들에 대한 접근을 제한하여 보안강화
 d. 검색위한 복잡한 쿼리를 단순쿼리로 변경 - 일반적으로 조인쿼리문은 복잡성을 가짐
    매번 필요시, 같은 조인쿼리를 사용하지 않고, 뷰로 작성
------------------------------------------------------
CREATE [OR REPLACE] VIEW 뷰이름 [ (alias1, alias2, ...) ]
AS
Sub-query
[ WITH CHECK OPTION [ CONSTRAINT 제약조건명 ] ]
[ WITH READ ONLY [ CONSTRAINT 제약조건명 ] ] ;

* (alias1, alias2, ...):
  a. 서브쿼리가 반환한 컬럼들에 대한 별칭(Alias) 지정
  b. 생략가능:
     기본테이블(Base tables)의 컬럼명 또는 서브쿼리의 SELECT절의 컬럼별칭 사용

* Sub-query:
  a. Join, Set, Complicated DQL문장 등의 정의 가능
  b. 정렬을 위한 ORDER BY 절은 사용불가 (정말?? -> 가능함!)
  c. 정렬 필요시, View 이용 검색쿼리에서 기술해야 함 (정말?? -> 가능함!)

* View 의 수정:
  a. 테이블처럼, ALTER 문을 사용하지 않음
  b. 새로운 View를 재생성하여, 기존 View를 덮어쓰는 방식으로 처리
  c. CREATE OR REPLACE 명령 사용
     기존 View가 존재하면 덮어쓰고, 없으면 새로이 생성 의미

* WITH READ ONLY 제약조건
  a. Read-only mode (읽기전용) 모드로 변경
  b. View 데이터에 대한 DML 작업불가

* WITH CHECK OPTION 제약조건
  a. 특정 조건과 일치해야 동작하게 함
------------------------------------------------------

------------------------------------------------------
1-1. View 가 필요한 이유 예시
------------------------------------------------------

(1) 20번 부서에 속한 사원들의 정보검색을 위한 복잡한 Join 쿼리문
    매번 사원정보 검색을 위해, 아래의 Join 쿼리를 수행해야 함

-- ANSI JOIN (조인조건과 체크조건 구분 쉬움)
SELECT
    empno,
    ename,
    d.dname,
    d.deptno
FROM
    emp e INNER JOIN dept d
    ON e.deptno = d.deptno     -- 조인조건
WHERE
    e.deptno = 20;                  -- 체크조건



(2) View 는 위의 복잡한 쿼리를, 매우 단순한 SQL문으로 처리할 수 있도록 지원 
    (위의 Join쿼리를 View로 정의)

CREATE OR REPLACE VIEW emp_view AS
SELECT
    empno,
    ename,
    d.dname,
    d.deptno
FROM
    emp e JOIN dept d
    ON e.deptno = d.deptno
WHERE
    e.deptno = 20;


-- 컬럼별칭, ORDER BY절도 사용 가능!
CREATE OR REPLACE VIEW emp_view (col1, col2, col3, col4) AS
SELECT
    empno as eno,            -- 컬럼별칭(column alias) 사용가능? Yes!!
    ename,
    d.dname,
    d.deptno
FROM
    emp e JOIN dept d
    ON e.deptno = d.deptno
WHERE
    e.deptno = 20
ORDER BY                     -- ORDER BY 절이 사용가능한가? Yes!!!
    1 DESC;

-- 이 경우 컬럼명은 col1, col2, col3, col4으로 보임


(3) 보안강화 : 데이터에 대한 접근제한을 위한 View
 기본테이블(Base table)에 저장된 특정 컬럼의 데이터를 보호할 목적으로 View 사용가능 (***)

-- emp 테이블에서 월급(sal) 컬럼은 민감한 정보 저장 -> 접근제한필요
-- view를 생성할 때 sal 컬럼을 제외시킴

CREATE OR REPLACE VIEW emp_view2 AS
SELECT
    empno,
    ename,
    job,
    mgr,
    hiredate,
    comm,
    deptno
FROM
    emp;

-----------------------------------------------------
1-2. View 객체정보 관리 데이터 딕셔너리(Data Dictionary)

View 를 사용한 SELECT 문 실행시, USER_VIEWS 데이터 사전에
저장된 해당 View 객체의 Sub-query 문(TEXT 컬럼)이 실행되어 결과값 반환
------------------------------------------------------

-- USER_VIEWS 데이터 사전
DESC user_views;


------------------------------------------------------
1-3. View 객체의 수정(ALTER)

가. 테이블 객체의 수정 -> ALTER TABLE 문 사용
나. View 객체의 수정 -> CREATE OR REPLACE 문 사용
    기존에 View 가 존재하면 Overwrite, 없으면 Create
다. 기존의 View 객체 수정시, CREATE OR REPLACE VIEW 문을 사용하지 않고, 
     CREATE VIEW 문을 사용하면 오류발생
------------------------------------------------------


------------------------------------------------------
1-4. View 의 종류 (2가지)
------------------------------------------------------
가. 단순 View (= Simple View)
 a. 하나의 기본 테이블(Base table) 에 대해 정의한 View
 b. By defaujlt, View 에 대해 DML 문장 실행 가능
 c. View 에 대한 DML 문장의 처리결과는 실제 기본테이블(Base table)에 반영됨
 d. 새로 생성되는 View에 대해서 별칭(Alias)를 사용하지 않으면, 
    기본테이블(Base table)의 컬럼명을 상속하거나 
    서브쿼리(Sub-query)의 SELECT절에 기술된 컬럼별칭(column alias) 상속
나. 복합 View (= Complex View)
 a. 2개 이상의 기본테이블(Base table)에 대해 정의한 뷰
 b. 2개 이상의 테이블을 Join해서 사용할 경우, 뷰로 생성
-- ------------------------------------------------------


(1) Simple View 생성

-- 20번 부서의 사원들의 정보를 가진 View 객체 생성

-- View 이름 뒤에, 컬럼별칭 목록을 지정하면, 지정된 별칭으로 View 의 컬럼명이 지정됨
-- 기본 테이블의 컬럼명을 상속받지 않고, 명시한 컬럼별칭(alias)으로 출력

CREATE OR REPLACE VIEW emp_view3 (사원번호, 이름, 월급) AS   -- Alias 사용
SELECT
    empno,
    ename,
    sal
FROM
    emp
WHERE
    deptno = 20;


SELECT    *
FROM    emp_view3;

(1-1) 예시: 부서별 급여총합 계산 (SUM 집계함수 사용)

(*주의*) View 생성시, 함수를 사용하는 경우 컬럼별칭(Column Alias) 를 지정하지 않으면, 오류발생

CREATE OR REPLACE VIEW emp_view4 AS
SELECT
    deptno,
    sum(sal) AS 총합        -- 별칭 지정 하지 않으면 오류발생
FROM
    emp
GROUP BY
    deptno;


(1-2) Simple View 데이터에 대한 DML 수행

가. Simple View 객체로 생성시, 데이터에 대한 DML 가능
나. 실제로는 기본테이블(Base table)에 DML 변경반영
다. DML 수행이 불가능한 경우: (******)
 a. Simple View 에, GROUP BY / 집계함수 / DISTINCT 키워드가 사용된 경우에는, DML 변경 불가!!!! (책)
 b. DISTINCT 키워드 사용 테스트결과 ->  DML 변경가능!!!( 실제 테스트 **)



CREATE OR REPLACE VIEW emp_view5 AS
SELECT 
    DISTINCT        -- DISTINCT 키워드 사용!!!
    empno,
    ename,
    sal,
    deptno
FROM
    emp;


-- 단순 뷰(Simple View) 데이터에 대한 DELETE 문
DELETE FROM emp_view5
WHERE deptno = 20;


-- emp_view5 뷰(view)에서 데이터 검색
SELECT    count(*)
FROM    emp_view5
WHERE    deptno = 20;



(2) Complex View 생성

CREATE OR REPLACE VIEW complex_view AS
SELECT
    t2.department_name AS 부서명,
    count(t1.employee_id) AS 인원수
FROM
    employees t1,
    departments t2
WHERE
    t1.department_id = t2.department_id             -- 조인조건
GROUP BY
    t2.department_name;



(3) WITH CHECK OPTION 제약조건

가. 테이블은 무결성을 유지하기 위해, PK/NN 등의 제약조건 설정
나. 마찬가지로, View도 WHERE 조건을 만족하는 데이터만, INSERT/UPDATE 가 가능하도록 제약조건 설정가능
다. 뷰에 대한 DML 작업이 수행되는 것을, WHERE 조건에 일치하는 데이터만 변경가능하도록 제약하는 방법!!!


-- 30번 부서의 사원정보출력 Simple View
CREATE OR REPLACE VIEW emp_view6 AS
SELECT
    empno,
    ename,
    sal,
    deptno
FROM
    emp
WHERE
    deptno = 30;


-- emp_view6 뷰(view)에서 데이터 검색
SELECT    *
FROM    emp_view6;

-- 7499 사원의 부서정보 변경(UPDATE)

UPDATE emp_view6
SET
    deptno = 40
WHERE
    empno = 7499;


-- 7499 사원의 부서정보가 30 -> 40으로 변경되었으므로, 
-- 기존 View 에서 나타나지 않음 (기본 테이블에 변경반영됨)

SELECT    *
FROM    emp_view6
WHERE    empno=7499;

-- 30번 부서의 사원정보출력 Simple view with check option
-- 30번 부서의 사원만 변경(DML) 가능하도록 제약조건 설정

-- WITH CHECK OPTION 이용한 뷰(view) 생성

CREATE OR REPLACE VIEW emp_view6 AS
SELECT
    empno,
    ename,
    sal,
    deptno
FROM
    emp
WHERE
    deptno = 30
WITH CHECK OPTION;


(4) WITH READ ONLY 제약조건

가. 뷰에 대한 DML 작업을 불가능하게 함
    View를 통한, 어떠한 기본 테이블(Base table) 변경도 불가능
나. 읽기전용(read-only) 상태로 View 생성
다. **제약조건의 제거** --> 다시 새로운 View 를 생성하여 대체


-- WITH READ ONLY 이용한 뷰(view) 생성
CREATE OR REPLACE VIEW emp_view6 AS
SELECT
    empno,
    ename,
    sal,
    deptno
FROM
    emp
WITH READ ONLY;

------------------------------------------------------
1-5. View 의 삭제

가. 기본 테이블(Base table)에는 영향을 주지 않음
나. 기본 테이블(Base table) 손실 없이 삭제
다. View의 삭제 : USER_VIEWS 데이터 사전에 저장된 TEXT 컬럼에 저장되어 있는, 서브쿼리의 삭제
------------------------------------------------------
DROP VIEW <삭제할 View객체의 이름>;
------------------------------------------------------

-- View 삭제
DROP VIEW emp_view6;



------------------------------------------------------
2. Sequence 객체 : 순차번호(Sequential number) 생성기

가. 호출될 때마다, 자동으로 숫자를 생성하는 Oracle 객체
나. 테이블의 특정 컬럼의 값을 Numbering 할 때 사용
    예: 웹 게시판의 글 번호
다. 만일 시퀀스를 사용하지 않는다면, 직접 명시적으로 현재 컬럼의 가장 큰값(max) + 1 씩,
     더 크게 설정하기 위한 추가 작업이 필요(응용프로그램에서)
------------------------------------------------------
Basic syntax: 아래 지정 옵션의 순서는 무관(But 가장 일반적)

CREATE SEQUENCE 시퀀스명
[ START WITH n ]
    순차번호의 시작값 지정. 생략시 1부터 시작
[ INCREMENT BY n ]
    연속적인 순차번호의 증가치 지정. 
    음수도 가능 (감소치), 생략시 1씩 증가
[ MAXVALUE n | NOMAXVALUE ]
    시퀀스 객체가 생성할 수 있는 순차번호의 최대값 지정
[ MINVALUE n | NOMINVALUE ]
    시퀀스 객체가 생성할 수 있는 순차번호의 최소값 지정
    아래 CYCLE 옵션이 지정된 경우, 새로 시작하는 값 역할
[ CYCLE | NOCYCLE ]
    시퀀스 객체가 최대값(MAXVALUE) 까지 증가한 경우,
    START WITH 값 부터 재시작 하는 것이 아니라, MINVALUE 값 부터 재시작.

** NOCYCLE 은 최대값까지 도달하게 되면, 에러가 발생 **
[ CACHE n | NOCACHE ]
    성능향상을 위해, 메모리 상에 미리 순차번호를
    기본으로 20개까지 미리 생성하여 관리.
    (*주의사항*) DB를 종료 했다가, 재시작하면, 이전에 미리
    생성하였었던, 메모리에 있는 최대 20개의 순차번호는 재사용불가
    
    NOCACHE는 , 필요할 때 마다, 매번 순차번호 계산하여 반환
------------------------------------------------------

------------------------------------------------------
2-1. 시퀀스 객체 생성
------------------------------------------------------

-- 부서번호 자동생성 시퀀스 객체 생성
CREATE SEQUENCE dept_deptno_seq
    START WITH 10       -- 10부터 번호시작
    INCREMENT BY 10     -- 증가치가 10
    MAXVALUE 100        -- 최대값 100
    MINVALUE 5          -- 최소값 5(CYCLE시, 재시작 번호)
    CYCLE               -- 최대값 도달시, MINVALUE 부터 재시작
    NOCACHE ;           -- 최대 20개의 숫자를 메모리에 미리 생성하지 않음!


-- Error: DESCRIBE SEQUENCE is not available
DESC dept_deptno_seq;   -- 테이블 객체에만 사용가능

------------------------------------------------------
2-2. 시퀀스 객체의 NEXTVAL / CURRVAL 속성
------------------------------------------------------
가. 시퀀스 객체생성 -> 자동으로 순차번호가 생성되는 것이 아님
나. 순차번호를 얻기 위해서는, *반드시 * 시퀀스 객체를 호출해야 함
다. 시퀀스 객체의 호출형식: 
    "시퀀스객체명.NEXTVAL"  -> 다음 순차번호 생성 및 획득
    "시컨스객체명.CURRVAL"  -> 현재 생성된 순차번호 조회
라. (*주의할 점*) 
    반드시 NEXTVAL 먼저 호출, 나중에 CURRVAL 호출해야 함
------------------------------------------------------

-- 가장 간단한 순차번호 값 확인 방법 : DUAL (Dummy table) 사용
-- 증가를 위한 NEXTVAL과 CURRVAL 사용

SELECT
    dept_deptno_seq.NEXTVAL,    
    dept_deptno_seq.CURRVAL
FROM
    dual;


-- 음수값 이용한 시퀀스 생성
CREATE SEQUENCE dept_deptno_seq2
    START WITH 100
    INCREMENT BY -10
    MAXVALUE 150
    MINVALUE 10
    CYCLE
    NOCACHE;


-- 감소를 위한 NEXTVAL과 CURRVAL 사용
SELECT
    dept_deptno_seq2.NEXTVAL,
    dept_deptno_seq2.CURRVAL
FROM
    dual;

------------------------------------------------------
2-3. USER_SEQUENCES 데이터 사전

가. 시퀀스 객체 정보를 저장하는 데이터 사전
나. 돌이켜 보면,
   a. USER_TABLES  : 테이블에 대한 데이터 사전
   b. USER_VIEWS   : 뷰(View)에 대한 데이터 사전
   c. USER_SEQUENCES : 시퀀스에 대한 데이터 사전
------------------------------------------------------

-- DEPT_DEPTNO_SEQ2 시퀀스 데이터 사전 조회
SELECT
    *
FROM
    user_sequences
WHERE
    sequence_name = 'DEPT_DEPTNO_SEQ2';


-----------------------------------------------------
2-4. 시퀀스(Sequence) 객체의 수정

가. ALTER SEQUENCE 문 사용
나. 증가치/최대값/최소값/CYCLE여부/캐시값 등 변경가능
    "START WITH 시작값"은 변경불가!!! (******)
다. 시퀀스 변경후, 다음 순차번호 생성부터 적용
라. "START WITH 시작값"의 변경을 꼭 해야한다면?
    기존 시퀀스를 삭제하고 재 생성 해야함!! (**)
------------------------------------------------------
ALTER SEQUENCE 시퀀스명
[ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE n | NOCACHE ]

* START WITH 값은 수정불가!!!