나의 향기에 젖다...

DML절에 With Check option절을 주어 View에서 생성된 조건에 대한 값을 반환과

동시에 DML에서 check 제약조건을 걸수 있다. 

ex_) 

INSERT INTO  (  SELECT employee_id, last_name, email, hire_date, job_id, salary

                                FROM    empl3

                                WHERE  department_id = 50

                                WITH CHECK OPTION)

Values (9999, 'kkm', 'kyungmo', sysdate, 'kim', 5000);

View안에 where절을 보면 departmen_id값이 50인 값만을 반환한다. 동시에

department_id값에 check 제약조건을 50으로 걸어둔 것이다 .

지금 예문은

ORA-01402 : view WITH CHECK OPTION where-clauses violation

이렇게 에러가난다. 이유는 위에 설명과 같다.

insert되는 department값이 50값만 들어갈수 있는데. department_id 컬럼에 대한 명시는

insert절에 되어 있지 않다. 그래서 check 제약조건에 걸린다.

이걸 맞는 구문으로 바꾼다면 

INSERT INTO  (  SELECT employee_id, last_name, email, hire_date, job_id, 

                                               salary,  department_id

                                FROM    empl3

                                WHERE  department_id = 50

                                WITH CHECK OPTION)

Values (9999, 'kkm', 'kyungmo', sysdate, 'kim', 5000, 50);

이렇게 바꿔주면 check 제약조건에 걸리지 않는 쿼리가 된다.

- Synonym은 실질적으로 그 자체가 Object가 아니라 Object에 대한 직접적인 참조 입니다.

- Synonym을 사용하는 이유는...

1) 데이터베이스의 투명성을 제공하기 위해서 사용 한다고 생각하면 됩니다.

    Synonym은 다른 유저의 객체를 참조할 때 많이 사용을 합니다.

2) 만약에 실무에서 다른 유저의 객체를 참조할 경우가 있을때 Synonym을 생성해서 사용을 하면은 추후에 참조하고 있는 오브젝트가 이름을 바꾸거나 이동할 경우 객체를 사용하는 SQL문을 모두 다시 고치는 것이 아니라 Synonym만 다시 정의하면 되기 때문에 매우 편리 합니다.

3) 객체의 긴 이름을 사용하기 편한 짧은 이름으로 해서 SQL코딩을 단순화 시킬 수 있습니다.

4) 또한 객체를 참조하는 사용자의 오브젝트를 감출 수 있기 때문에 이에 대한 보안을 유지할 수 있습니다. Synonym을 사용하는 유저는 참조하고 있는 객체에 대한 사용자의 오브젝트의 소유자, 이름, 서버이름을 모르고 Synonym 이름만 알아도 사용할 수 있습니다.

** Synonyms를 사용하는 경우 **

- 오브젝트의 실제 이름과 소유자 그리고 위치를 감춤으로써 database 보안을 개선하는데 사용 됩니다.

- object에의 Public Access를 제공 합니다.

- Remote Database의 Table, View, Program Init를 위해 투명성을 제공 합니다.

- Database 사용자를 위해 SQL문을 단순화 할 수 있습니다.

Synonym에는 두가지 종류가 있습니다.

1) Private Synonym

 - 전용 Synonym은 특정 사용자만 이용할 수 있습니다.

2) Public Synonym

 - 공용 Synonym은 고용 사용자 그룹이며 소유하며 그 Database에 있는 모든 사용자가 공유

** Synonym 생성 문법 **

**[ Syntax ]*************************************************

* CREATE [PUBLIC] SYNONYM synonym_name

* FOR object_name

*************************************************************

- Public : 모든 사용자가 접근 가능한 시노님을 생성 합니다.

              Public Synonym의 생성 및 삭제는 DBA만이 할 수 있습니다.

※ scott USER의 emp테이블을 test USER가 사용하는 예제

1. 먼저 scott/tiger USER로 접속해서 test USER에게 emp테이블을 조작할 권한을 부여합니다.

SQL> GRANT ALL ON emp TO test;

         권한이 부여되었습니다.

test user에 대하여 scott의 emp테이블을 조작할 수 있는 권한을 부여 합니다.

권한이 있어야 select하거나 update, insert할 수 있습니다.

2. test USER로 접속해 동의어를 생성합니다.

SQL> connect test/test

SQL> CREATE SYNONYM scott_emp FOR scott.emp;

시노님이 생성되었습니다.

scott USER가 소유하고 있는 emp테이블에 대해 scott_emp라는 일반시노님을 생성 했습니다.

scott 사용자의 emp테이블을 test사용자가 scott_emp라는 동의어로 사용 합니다.

-- 시노님을 이용한 쿼리

SQL> SELECT empno, ename FROM scott_emp;

-- 일반 테이블을 쿼리

SQL> SELECT empno, ename FROM scott.emp;

이 두쿼리의 결과는 같습니다.

   EMPNO ENAME
-------- ---------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
          15 개의 행이 선택되었습니다.

** 동의어 삭제 **

SQL> DROP SYNONYM scott_emp;

시노님이 삭제 되었습니다.

SQL> SELECT empno, ename FROM scott_emp;

라인 1에 오류:

ORA - 00942 : 테이블 또는 뷰가 존재하지 않습니다.

** 뷰란? **

- 뷰는 하나의 가상 테이블이라 생각 하시면 됩니다.

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

- 뷰는 복잡한 query를 통해 얻을 수 있는 결과를 간단한 query를 써서 구할 수 있게 합니다.

- 한개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있습니다.

- 특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스 할 수 있도록 합니다.

** 뷰의 제한 조건 **

- 테이블에 NOT NULL로 만든 컬럼들이. 뷰에 다 포함이 되 있어야 됩니다.

- 그리고 ROWID, ROWNUM, NEXTVAL, CURRVAL 등과 같은 가상컬럼에 대한 참조를 포함하고 있는 뷰에는 어떤 데이터도 Insert할 수 없습니다.

- WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신할 수 없습니다.

- WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제, 수정을 할 수 있습니다.

**[ Syntax ]*******************************************************************

* CREATE[OR REPLACE] [FORCE|NOFORCE] VIEW view_name

* AS subquery

* [WITH CHECK OPTION [CONSTRAINT 제약조건]]

* [WITH READ ONLY]

*******************************************************************************

- FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성

- WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나 변경될 수 있음을 지정 합니다.

- WITH READ ONLY : SELECT만 가능한 VIEW의 생성

- VIEW를 정의 하는 질의어에는 ORDER BY절을 사용할 수 없습니다.

- 함수를 사용한 컬럼은 반드시 ALIAS를 지정해야 합니다.

SQL> CREATE OR REPLACE VIEW Name_Query

          AS SELECT a.ename, b.dname

               FROM emp a, dept b

               WHERE a.deptno = b.deptno

                    AND b.deptno = 20

view created

이렇게 뷰를 생성해 놓고 뷰를 통해 검색을 하면 됩니다.

SQL> SELECT * FROM Name_Query;

ENAME                DNAME
-------------------- ----------
SMITH                RESEARCH
JONES                RESEARCH
SCOTT               RESEARCH
ADAMS               RESEARCH
FORD                 RESEARCH

** WITH CHECK OPTION **

view의 조건식을 만족하는 데이터만 INSERT 또는 UPDATE가 가능하도록 하는 옵션 입니다.

SQL> CREATE OR REPLACE VIEW Check_Option

          AS SELECT empno, ename, deptno

               FROM emp

               WHERE deptno = 10

          WITH CHECK OPTION

view create

SQL> INSERT INTO Check_Option(empno, ename, deptno)

          VALUES (10005, 'jain', 30);

INSERT INTO Check_Option(empno, ename, deptno)
            *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
부서 번호가 10인 사원만 INSERT, UPDATE할 수 있습니다.

** VIEW에 대한 DML문 사용 규칙 **

- VIEW가 다음 사항을 포함하는 경우 행의 삭제 불가

 -> JOIN조건, 그룹함수, GOUP BY절, DISTINCT명령

- VIEW가 다음 사항을 포함하는 경우 데이터 수정 불가

 -> JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼(EX SAL * 0.1)

- VIEW가 다음 사항을 포함하는 경우 데이터 추가 불가

 -> JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼, VIEW에 선택되지 않는 NOT NULL컬럼

** 뷰의 정보 조회 **

USER_VIEWS 데이터 사전을 통해서 뷰에 대한 정보를 조회 할 수 있습니다.

SQL> SELECT view_name, text

          FROM USER_VIEWS;

** 뷰의 삭제 **

SQL> DROP VIEW Read_Only;

view dropped.

Chr(13) : 캐리지 리턴(carriage return)

Chr(10) : 라인피드(new line)

Chr(13)은 동일한 줄의 첫번째 자리에 커서를 위치 (캐리지 리턴)

Chr(10)은 현재 커서가 위치한 곳에서 아래로 한줄 내리는 기능 (라인피드)

예제) replace(text,'\n', chr(13)||chr(10)) -> text에서 \n을 보면 엔터키로 역할 변경

SELECT한 내용을 INSERT 하기

INSERT INTO CODELIBRARY3 (ID,TYPE,PARENT_ID,NAME,USE_YN,DEL_YN,FLAG) 
SELECT * FROM CODELIBRARY T1

테이블 복사