Bohemian life

- scope에 따라

oj_SQL.txt

 - information about database, objects

  - private synonym은 다른 오브젝트 이름과 같을 수 없습니다.

  - package에 포함된 오브젝트에 대해서는 synonym을 만들 수 없습니다.

테이블 삭제 - 인덱스와 제약조건은 같이 삭제.

              - 뷰와 시퀀스는 남아있음.

  - START WITH 옵션만은 변경할 수 없습니다.

  - 시퀀스의 시작값을 변경하려면?

    Drop sequence seq1;

    Create sequence seq1

      start with 200;

- Gap이 발생할 수 있습니다.

     - rollback

     - system crashes

     - used in another table

  1) Gap이 발생하는 경우: 시퀀스를 공유할 때

  SQL> drop table t1;

  SQL> create table t1 (no number(3), name varchar2(30));

  SQL> drop table t2;

drop table t2

          *

ERROR at line 1:

ORA-00942: table or view does not exist

  SQL> create table t2 (no number(4), title varchar2(30));

  SQL> create sequence t_seq;

  SQL> insert into t1 values (t_seq.nextval, 'JAMES');

  SQL> insert into t2 values (t_seq.nextval, 'I Love You');

  SQL> insert into t1 values

         select t_seq.nextval, ename

         from emp

         where deptno = 20;

  SQL> insert into t2 values (t_seq.nextval, 'Gone With The Wind');

  SQL> select * from t1;

       NO NAME

---------- ------------------------------

        2 JAMES ==> 시퀀스 값이 2번부터 나온 이유: deferred segment creation

        5 SMITH    다음 포스트를 참고하세요 (http://gseducation.blog.me/20145825527)

        6 JONES

        7 SCOTT

        8 ADAMS

        9 FORD

6 rows selected.

  SQL> select * from t2;

       NO TITLE

---------- ------------------------------

        4 I Love You ==> 역시 deferred segment creation에 의해 3이 아닌 4가 시퀀스 값으로 나옴

       10 Gone With The Wind

  2) Gap이 발생하는 경우: DML 시 에러 발생

  SQL> insert into t2 values (t_seq.nextval, 'Harry Potter');

  SQL> insert into t2 values (t_seq.nextval, 'Harry Potter and Sorceror''s Stone');

insert into t2 values (t_seq.nextval, 'Harry Potter and Sorceror''s Stone')

                                     *

ERROR at line 1:

ORA-12899: value too large for column "JAVA30"."T2"."TITLE" (actual: 33, maximum: 30)

  SQL> insert into t2 values (t_seq.nextval, 'Harry Potter Sorceror''s Stone');

  SQL> select * from t2;

       NO TITLE

---------- ------------------------------

        4 I Love You

       10 Gone With The Wind

       11 Harry Potter

       13 Harry Potter Sorceror's Stone

  3) Gap이 발생하는 경우: ROLLBACK

  SQL> commit;

  SQL> insert into t2 values (t_seq.nextval, 'Ironman');

  SQL> insert into t2 values (t_seq.nextval, 'Steve Jobs the Chronicle');

  SQL> rollback;

  SQL> insert into t2 values (t_seq.nextval, 'Steve Jobs and the Apples');

  SQL> select * from t2;

       NO TITLE

---------- ------------------------------

        4 I Love You

       10 Gone With The Wind

       11 Harry Potter

       13 Harry Potter Sorceror's Stone

       16 Steve Jobs and the Apples

http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns002.htm

  create sequence seq1

    start with 10

    increment by 3

    maxvalue 20

    cycle

    nocache;

  select seq1.currval from dual;

1행에 오류:

ORA-08002: 시퀀스 SEQ1.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다

  select seq1.nextval from dual;

SEQ1.NEXTV

----------

       10

  select seq1.currval from dual;

  select seq1.nextval from dual;

  select seq1.nextval from dual;

  select seq1.nextval from dual;

  select seq1.nextval from dual;

  NEXTVAL

----------

        1

         ==> CYCLE은 MINVALUE에서 MAXVALUE를 순환합니다.

             MINVALUE를 지정하지 않았으므로 기본값인 NOMINVALUE(1 - ascending)가 지정되었습니다.

  select seq1.nextval from emp;

  select seq1.currval from emp;

  - 유일값 자동생성

  - 공유 객체

  - primary key 생성에 보통 사용됨

  - 캐시 기능을 이용하면 시퀀스 액세스 속도 향상

SQL> create or replace view d20vu

  2  as select empno, ename, sal, job, deptno

  3  from c_emp

  4  where deptno = 20;

View created.

SQL> select * from d20vu;

     EMPNO ENAME             SAL JOB           DEPTNO

---------- ---------- ---------- --------- ----------

      7369 SMITH             800 CLERK             20

      7566 JONES            2975 MANAGER           20

      7788 SCOTT            3000 ANALYST           20

      7876 ADAMS            1100 CLERK             20

      7902 FORD             3000 ANALYST           20

==> d20vu 뷰를 통해 입력한 데이터를 d20vu 뷰로 질의할 수 없습니다. 그래서 문제죠.

    WITH CHECK OPTION을 이용하면 해결할 수 있습니다.

SQL> create or replace view d20vu

  2  as select empno, ename, sal, job, deptno

  3  from c_emp

  4  where deptno = 20

  5* with check option constraint d20vu_ck

View created.

SQL> select * from d20vu;

     EMPNO ENAME             SAL JOB           DEPTNO

---------- ---------- ---------- --------- ----------

      7369 SMITH             800 CLERK             20

      7566 JONES            2975 MANAGER           20

      7788 SCOTT            3000 ANALYST           20

      7876 ADAMS            1100 CLERK             20

      7902 FORD             3000 ANALYST           20

SQL> insert into d20vu values (8888, 'Kimberly', 1100, 'SALESMAN', 30);

insert into d20vu values (8888, 'Kimberly', 1100, 'SALESMAN', 30)

            *

ERROR at line 1:

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

SQL> insert into d20vu values (8888, 'Kimberly', 1100, 'SALESMAN', 20);

1 row created.

------------------------------------

drop table t1 purge;

create table t1

as

select empno, ename, decode(gubun, '정규', sal) as month_sal, decode(gubun, '비정규', round(sal/4)) as week_sal, gubun, job, deptno

from (select empno, ename, sal

      , decode(mod(empno, 3), 0, '정규', 1, '비정규', '비정규') as gubun

      , job, deptno

      from emp);

create or replace view emp_regular

as

select empno, ename, month_sal, gubun, job, deptno

from t1

where gubun = '정규';

create or replace view emp_temporary

as

select empno, ename, week_sal, gubun, job, deptno

from t1

where gubun = '비정규';

insert into emp_regular (empno, ename, gubun, job, deptno)

values (9999, 'Jang', '비정규', 'MANAGER', 20);

select * from t1;

select * from emp_regular;

==> 방금 전 입력한 Jang의 데이터가 t1 테이블에는 있지만 emp_regular 뷰에는 안 보입니다.

create or replace view emp_regular

as

select empno, ename, month_sal, gubun, job, deptno

from t1

where gubun = '정규'

with check option constraint v_emp_regular;

insert into emp_regular (empno, ename, gubun, job, deptno)

values (9998, 'Kim', '비정규', 'MANAGER', 20);

1행에 오류:

ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

select * from t1;

commit;

# 뷰 제약조건 확인하기

  SQL> select table_name, constraint_name, constraint_type

       from user_constraints

       where constraint_type = 'V';

TABLE_NAME                     CONSTRAINT_NAME                C

------------------------------ ------------------------------ -

EMP_REGULAR                    V_EMP_REGULAR                  V

D20VU                          D20VU_CK                       V

Update DML을 사용할 수 없는 경우 + 뷰 컬럼 외의 컬럼에 NOT NULL 제약 조건 있는 경우

   (예외: 그 컬럼에 default 값이 정의되어 있으면 INSERT 가능)

Delete DML을 사용할 수 없는 경우(그. 디(대). 로) + 수식으로 정의된 컬럼