Neo-Oracle Home

출처:http://www.oracleclub.com/lecture/1036
########################################################################################################################
뷰의 제한 조건.
########################################################################################################################
- 테이블에 NOT NULL로 만든 컬럼들이 뷰에 다 포함이 되 있어야 한다.
- ROWID, ROWNUM, NEXTVAL, CURRVAL등과 같은 가상컬럼에 대한 참조를 포함하고 있는 뷰에는 어떤 데이터도 INSERT 할 수 없다
- WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신 할 수 없다.
- WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제,수정을 할 수 있다

########################################################################################################################
뷰 문법
########################################################################################################################
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
AS 서브쿼리
[with check option [constraint 제약조건]]
[with read only]

- FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성
- WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나 변경될 수 있음을 지정
- WITH READ ONLY : SELECT만 가능한 VIEW 생성
- 함수를 사용한 컬럼은 반드시 ALIAS를 지정해야 한다.

########################################################################################################################
뷰 변경, 재컴파일
########################################################################################################################
뷰는 쿼리에 대한 변경이 불가능하기에 AS SELECT 옵션에 작성한 SQL을 변경하고 싶다면 해당 뷰를 재생성해야 한다.

뷰에 이상이 생겨 상태가 INVALID로 된 경우 재컴파일을 통해(AS SELECT가 유효해야 한다) VALID 상태로 바꿀 수 있다.
SQL> ALTER VIEW view_name RECOMFILE;

########################################################################################################################
뷰 예제
########################################################################################################################
-- 뷰 생성   
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;

-- 뷰를 이용한 조회
SQL> SELECT * FROM name_query;

ENAME                DNAME
-------------------- ----------
SMITH                RESEARCH
JONES                RESEARCH
...

-- 뷰 제거
SQL> DROP VIEW name_query;

########################################################################################################################
WITH CHECK OPTION
########################################################################################################################
WITH CHECK OPTION
뷰의 조건식을 만족하는 데이터만 INSERT 또는 UPDATE가 가능 하도록 하는 옵션 이다.

 
-- 부서 번호 10의 데이터를 조회 하는 뷰
SQL> CREATE OR REPLACE VIEW check_option
     AS
       SELECT empno, ename, deptno
       FROM  emp
       WHERE deptno = 10
       WITH CHECK OPTION ;
 
-- 부서 번호가 10인 사원만 INSERT, UPDATE할 수 있다.
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의 조건에 위배 됩니다
########################################################################################################################
WITH READ ONLY
########################################################################################################################
SELECT만 가능한 VIEW를 생성 한다

 
-- 아래 뷰는 읽기만 가능하다.
SQL> CREATE OR REPLACE VIEW read_only
     AS
       SELECT empno, ename, deptno
       FROM  emp
       WHERE deptno = 10
       WITH READ ONLY;
########################################################################################################################
뷰의 정보 조회
########################################################################################################################
USER_VIEWS 데이터 사전을 통해서 뷰에 대한 정보를 조회 할 수 있다.
 
SQL> SELECT view_name, text
     FROM USER_VIEWS;   

#########################################################################################################################
디비링크 생성
#########################################################################################################################
create {public} database link {dblink_name}
connect to {userid}
identified by {password}
using '{connectstring}';

위의 connectstring은 tnsnames.ora 에 등록된 걸 사용할 수도 있고
아래처럼 직접 Connect String을 적을 수도 있다.

CREATE DATABASE LINK 디비링크명  
  CONNECT TO 계정명  
  IDENTIFIED BY 계정비밀번호  
  USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=호스트주소)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=서비스아이디)))';

#########################################################################################################################
디비링크 조회
#########################################################################################################################
select * from user_db_links;
select * from dba_db_links;

#########################################################################################################################
디비링크 암호 확인
#########################################################################################################################
select name,passwordx from sys.link$;

#########################################################################################################################
다른 유저를 소유자로 디비링크 생성
#########################################################################################################################
dblink는 owner.object 형식으로 만들 수가 없다.
domain 명으로 인식해버리기 때문이다.
따라서 다른 방법을 사용해야 한다.
아래처럼 프로시저를 생성해서 사용한다.

CREATE PROCEDURE otheruser.cre_db_lnk AS
BEGIN   
EXECUTE IMMEDIATE 'CREATE DATABASE LINK newlink '
            ||'CONNECT TO remoteuser IDENTIFIED BY pw '
            ||'USING ''remotetns''';
END cre_db_lnk;
/ 

#########################################################################################################################
시퀀스 생성
#########################################################################################################################
문법
CREATE SEQUENCE sequence_name
 [INCREMENT BY n]
 [START WITH n]
 [MAXVALUE n | NOMAXVALUE]
 [MINVALUE n | NOMINVALUE]
 [CYCLE | NOCYCLE]
 [CACHE n | NOCACHE];

INCREMENT BY n: 증가값으로 2라고 하면 초기값에서 2만큼씩 증가한다. 음수를 쓸 수도 있으며 -1로 하면 1씩 감소한다. 기본값 1
START WITH n: 초기값을 의미한다.
MAXVALUE n: 최대값
NOMAXVALUE: 최대 10의 27승만큼 할당 가능하다.(거의 무제한)
MINVALUE n: 최소값. START WITH n의 우선순위가 더 높으며 만약 START WITH n 값이 MINVALUE n 값보다 낮다면 시퀀스 생성이 안된다.
            ORA-04006: START WITH cannot be less than MINVALUE <-- 이 에러가 난다.
NOMINVALUE: 최소값이며 -10의 26승까지 가능하다. 단, 시퀀스 생성시 start with n을 설정하지 않는다고 nominvalue의 최소값인
            -10의 26승이 사용되는 건 아니다. 에러난다.(테스해봄)
CYCLE | NOCYCLE: MAXVALUE가 설정된 상황에서 최대값에 도달할 경우 다시 START WITH n 값으로 순환할지 말지 결정한다.
                 기본값은 NOCYCLE
CACHE n| NOCACHE: CACHE 옵션 사용시 n개만큼 미리 메모리에 생성시켜 놓고 해당 시퀀스를 호줄하게 되면 메모리에서 할당하게 된다.
    기본값은 CACHE 20이다.(11g, 10g 다 테스트해봄)

예제)
SQL> create sequence t_seq2
  2  increment by 1
  3  start with 100
  4  nomaxvalue
  5  minvalue 50
  6  ;

Sequence created.

SQL> select t_seq2.nextval from dual;

   NEXTVAL
----------
       100

SQL> create sequence t_seq
  2  increment by 1
  3  start with 50
  4  nomaxvalue
  5  minvalue 100;
create sequence t_seq
*
ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE

기본값으로 생성 예제)
create sequence t_seq4 start with 1;

확인해보면 아래처럼 cache_size는 20, maxvalue는 nomaxvalue로, minvalue는 start with 값을 따라가고, increment by 는 1이 되었다.
그리고 nocycle이다.

SQL> SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_NAME like 'T_SEQ%';

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE  MAX_VALUE INCREMENT_BY  C O CACHE_SIZE LAST_NUMBER
-------------- ------------- ---------- ---------- ------------ - - ---------- -----------
SYS            T_SEQ4        1          1.0000E+27 1            N N         20          21

#########################################################################################################################
시퀀스 변경
#########################################################################################################################
문법
ALTER SEQUENCE sequence_name
 [INCREMENT BY n]
 [MAXVALUE n | NOMAXVALUE]
 [MINVALUE n | NOMINVALUE]
 [CYCLE | NOCYCLE]
 [CACHE n | NOCACHE];

create랑 비슷하지만 start with n 이 없다. 즉 start with는 변경 불가능하다.

예제
alter sequence t_seq
 increment by 2
 maxvalue 20000
 cache 10
 nocycle;

#########################################################################################################################
시퀀스 삭제
#########################################################################################################################
DROP SEQUENCE sequence_name;

#########################################################################################################################
시퀀스 정보 확인
#########################################################################################################################
select * from dba_sequnces;

이중에서 몇개만 설명한다.

CYCLE_FLAG: cycle nocycle 여부이다.
ORDER_FLAG: 시퀀스 증가시 오름차순인지 내림차순인지 여부...인데 increment by -1로 바꿔도 변경되지 않음을 확인했다. 좀 더 확인 필요.
LAST_NUMBER: 디스크에 내려써진 마지막 시퀀스 넘버. 만약 캐쉬를 사용한다면 이 값은 CURRVAL + CACHE_SIZE 일 것이다.
#########################################################################################################################
시퀀스 사용
#########################################################################################################################
NEXTVAL과 CURRVAL이 존재한다.

NEXTVAL: 다음 번호
CURRVAL: 현재 번호

주의할 점은 NEXTVAL을 조회할 때마다 INCREMENT BY n 만큼 바로 증가한다는 점이다.
또한 시퀀스 생성 후 맨 처음에는 CURRVAL을 조회할 수 없다. 한번은 NEXTVAL을 조회해야 CURRVAL 조회가 가능하다.

NEXTVAL과 CURVAL을 사용할 수 있는 조건
- 서브쿼리가 아닌 SELECT 문의 SELECT LIST에 사용
- INSERT절에 있는 서브쿼리의 SELECT LIST에 사용
- INSERT절의 VALUES 절에 사용
- UPDATE절의 SET절에 사용

사용할 수 없는 조건
- 뷰의 SELECT LIST
- DISTINCT가 있는 SELECT문
- GROUP BY 또는 ORDER BY 가 있는 SELECT문
- 서브쿼리

사용예시
SELECT 시퀀스이름.CURRVAL FROM DUAL;
SELECT 시퀀스이름.NEXTVAL FROM DUAL;
#########################################################################################################################
시퀀스 실무
#########################################################################################################################
스퀀스 공백이 발생하는 경우가 흔하다. 이유는 아래와 같다
- 시퀀스를 호출한 후 해당 번호를 사용하지 않았을 경우
- DB 인스턴스가 비정상 종료되었을 경우

예를 들어 시퀀스 호출 후 커밋하지 않고 롤백한다면 해당 번호는 영원히 사용하지 못하게 된다.
CACHE 옵션을 10으로 설정했다면 메모리에 항상 10개의 시퀀스 번호를 할당해 놓는데 DB 인스턴스가 비정상 종료된다면 해당 10개의
번호는 다시 할당받을 수 없다.

따라서 번호 공백이 해당 업무에 영향을 미칠 수 있다면 시퀀스 생성시 nochache 옵션으로 설정해야 하며, 하나의 번호도 공백이
없어야 한다면 시퀀스 사용을 자제하고 인덱스나 번호 할당 테이블 등을 이요하는 것도 고려해야 한다.

* 인덱스를 사용한 부서별 번호 할당
부서별로 사원번호를 할당해야 한다고 하자. 시퀀스를 이용한다면 부서 개수만큼 시퀀스가 존재해야만 한다. 해당 회사에 50개의
부서가 있다면 시퀀스는 50개가 생성되어 부서별로 서로 다른 시퀀스를 이용해야만 할 것이다.
이 경우 다음과 시퀀스 관리 어려움 등의 문제가 발생할 수 있다.

부서번호+번호로 인덱스를 생성한다면 다음과 같이 SQL을 수행함으로써 각 부서별 다음에 할당될 번호를 추출할 수 있다.

select /*+ index_desc(사원,사원 인덱스) */
 nvl(max(번호),0)+1
 from 사원 where 부서번호='10' and rownum=1;

#########################################################################################################################
시퀀스 공백 테스트

#########################################################################################################################

SQL> @1

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            T_SEQ4                                  1 1.0000E+28            1 N N         20           1
SYS                            T_SEQ2                                  1 1.0000E+28           -1 N N         20          79
SYS                            T_SEQ3                                  1 1.0000E+28            1 N N          0           1

SQL> select t_seq2.currval from dual;

   CURRVAL
----------
        97

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  409194496 bytes
Fixed Size                  2228864 bytes
Variable Size             385879424 bytes
Database Buffers           16777216 bytes
Redo Buffers                4308992 bytes
Database mounted.
Database opened.
SQL> @1

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            T_SEQ4                                  1 1.0000E+28            1 N N         20           1
SYS                            T_SEQ2                                  1 1.0000E+28           -1 N N         20          79
SYS                            T_SEQ3                                  1 1.0000E+28            1 N N          0           1

SQL> select t_seq2.currval from dual;
select t_seq2.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence T_SEQ2.CURRVAL is not yet defined in this session

SQL> select t_seq2.nextval from dual;
   NEXTVAL
----------
        79

SQL> @1
SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            T_SEQ4                                  1 1.0000E+28            1 N N         20           1
SYS                            T_SEQ2                                  1 1.0000E+28           -1 N N         20          59
SYS                            T_SEQ3                                  1 1.0000E+28            1 N N          0           1

97~80 의 공백이 생겼다.