Bohemian life

Cannot Remove a Row on Complex View

        = Complex View에 Delete DML을 사용할 수 없는 경우

       그. 디(대). 로

  SQL> create or replace view test_vu

       as select distinct deptno

       from emp;

View created.

  SQL> select * from test_vu;

   DEPTNO

----------

       30

       20

       10

  SQL> delete from test_vu

       where deptno = 10;

delete from test_vu

           *

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL> create or replace view d10vu (emp_no, name, ann_sal, job, d_no)

       as select empno, ename, sal*12, job, deptno

       from emps

       where deptno = 10

from emps

    *

ERROR at line 3:

ORA-00942: table or view does not exist

  SQL> create or replace view d10vu (emp_no, name, ann_sal, job, d_no)

       as select empno, ename, sal*12, job, deptno

       from emp

       where deptno = 10

View created.

  SQL> desc d10vu

Name                                                              Null?    Type

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

EMP_NO                                                            NOT NULL NUMBER(4)

NAME                                                                       VARCHAR2(10)

ANN_SAL                                                                    NUMBER

JOB                                                                        VARCHAR2(9)

D_NO                                                                       NUMBER(2)

  SQL> create or replace view d20vu

       as select empno, ename, sal*12, job, deptno

       from emp

       where deptno = 20

as select empno, ename, sal*12, job, deptno

                          *

ERROR at line 2:

ORA-00998: must name this expression with a column alias

  SQL> create or replace view d20vu

       as select empno, ename, sal*12 as ann_sal, job, deptno

       from emp

       where deptno = 20

View created.

  SQL> desc d20vu

Name                                                              Null?    Type

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

EMPNO                                                             NOT NULL NUMBER(4)

ENAME                                                                      VARCHAR2(10)

ANN_SAL                                                                    NUMBER

JOB                                                                        VARCHAR2(9)

DEPTNO                                                                     NUMBER(2)

  SQL> select empno as emp_no, ename as name, sal*12 as ann_sal, job, deptno

       from emp

       where deptno = 10;

   EMP_NO NAME          ANN_SAL JOB           DEPTNO

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

     7782 CLARK           29400 MANAGER           10

     7839 KING            60000 PRESIDENT         10

     7934 MILLER          15600 CLERK             10

  SQL> select *

       from d10vu;

   EMP_NO NAME          ANN_SAL JOB             D_NO

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

     7782 CLARK           29400 MANAGER           10

     7839 KING            60000 PRESIDENT         10

     7934 MILLER          15600 CLERK             10

  SQL> select * from d20vu;

    EMPNO ENAME         ANN_SAL JOB           DEPTNO

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

     7369 SMITH            9600 CLERK             20

     7566 JONES           35700 MANAGER           20

     7788 SCOTT           36000 ANALYST           20

     7876 ADAMS           13200 CLERK             20

     7902 FORD            36000 ANALYST           20

*Updatable Join View

1. 데이터 접근 제한(제어)
2. 복잡한 쿼리 간단하게 ,쉽게
3. 데이터 독립성
4. one-source multi-use

- a logical table (no storage)

- a named select

- one-source multi-use 

1) 모든 테이터와 테이블 구조 삭제

2) 저장공간은 모두 반환됩니다

3) DDL이므로 Tx 종료(commit)

4) 테이블의 모든 인덱스(자동생성된 인덱스)와 제약조건도 같이 삭제

5) ROLLBACK으로 데이터를 살릴 수 없다.

단 flashback technology를 이용하면 복구 가능

flashback table member2 to before drop;

단, purge 옵션으로 drop하면 flashback도 불가능 --> 데이터베이스 복구

6) FK에 의해 참조되고 있다면 삭제되지 않습니다.

7) FK에 의해 참조되고 있어도 삭제하고 싶다면

  CASCADE CONSTRAINTS 옵션을 사용하세요.

↓ ↓ ↓ 

SQL> select * from d1;

   DEPTNO DNAME                DLOC

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

       20 Executives           Seoul

SQL> select * from e1;

    EMPNO ENAME                    DEPTNO        SAL

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

     1000 James                                 2000

     3000 King                         20       1100

SQL> drop table d1;

drop table d1

          *

ERROR at line 1:

ORA-02449: unique/primary keys in table referenced by foreign keys

==> e1 테이블이 d1 테이블을 참조하므로 d1 테이블을 삭제할 수 없습니다.

SQL> select constraint_name, constraint_type from user_constraints

 2* where table_name = 'E1'

CONSTRAINT_NAME                C

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

E1_ENAME_NN                    C

E1_SAL_CK                      C

E1_EMPNO_PK                    P

E1_DEPTNO_FK                   R

SQL> drop table d1 cascade constraints;

==> CASCADE CONSTRAINTS 옵션 사용

Table dropped.

SQL> select constraint_name, constraint_type from user_constraints

 2  where table_name = 'E1';

CONSTRAINT_NAME                C

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

E1_ENAME_NN                    C

E1_SAL_CK                      C

E1_EMPNO_PK                    P

oj_SQL.txt

 create table e4

as

select * from e1;

select table_name, constraint_name, search_condition 

from user_constraints

where table_name in ('E1', 'E4');

==> CTAS로 테이블을 생성하면 NOT NULL 제약조건만 복사됩니다.

                    따라서 다른 제약조건은 새로 생성해 주어야 합니다.

   (alter table e4 modify (...) 이용)

# 반드시 테이블 레벨 제약조건을 사용해야하는 경우?

create table member1 (

 name varchar2(20) not null,

 birth number(6) unique,

 jcode number(7) unique);

insert into member1 values ('James', 120111, 3234567);

insert into member1 values ('Smith', 120111, 4234567);

==> 오류: ORA-00001: 무결성 제약 조건(MVP20.SYS_C0023754)에 위배됩니다

==> 엉터리: 생년월일이 같은 이성이 가입할 수 없음

create table member2 (

 name varchar2(20) not null,

 birth number(6),

 jcode number(7),

   constraint memeber2_jumin_uk unique(birth, jcode));

insert into member2 values ('James', 120111, 3234567);

insert into member2 values ('Smith', 120111, 4234567);

select * from member2;

==> 제대로: 생년월일이 같거나 주민코드 7자리가 같아도 가입할 수 있음

 * ON DELETE CASCADE

delete from d1

where deptno = 30;

==> 오류: ORA-02292: 무결성 제약조건(MVP20.E1_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다

alter table e1

drop constraint e1_deptno_fk;

alter table e1

modify (deptno constraint e1_deptno_fk references d1(deptno)

on delete cascade);

delete from d1

where deptno = 30;

select * from d1;

select * from d2;

  * ON DELETE SET NULL

delete from d2

where deptno = 30;

==> 오류: ORA-02292: 무결성 제약조건(MVP20.E2_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다

alter table e2

drop constraint e2_deptno_fk;

alter table e2

modify (deptno constraint e2_deptno_fk references d2(deptno)

on delete set null);

delete from d2

where deptno = 30;

select * from d2;

select * from e2;

  * 종류

1) NN: NOT null

2) UK: 중복 불허

3) PK: = NN + UK, 테이블 당 한 개만

4) FK: null 또는 referenced column에 존재하는 값만 허용

5) CK: 조건

  * 정의: 테이블 생성시

1) column-level definition

2) table-level definition

drop table d1 purge;

drop table d2 purge;

create table d1 (

 deptno number(2)    primary key,

 dname  varchar2(20) not null,

 dloc   varchar2(20));

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

  * 제약조건에 대한 정보는 USER_CONSTRAINTS 뷰를 질의하면 알 수 있습니다.

SQL> desc user_constraints

SQL> select constraint_name, constraint_type, table_name

 2  from user_constraints

 3  where table_name = 'D1';

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

create table d2 (

 deptno number(2),

 dname  varchar2(20),

 dloc varchar2(20),

   primary key (deptno));

drop table d2 purge;

create table d2 (

 deptno number(2) constraint d2_deptno_pk primary key,

 dname  varchar2(20),

 dloc varchar2(20));

select constraint_name, constraint_type, table_name

from user_constraints

where table_name = 'D2';

drop table d2 purge;

create table d2 (

 deptno number(2),

 dname  varchar2(20),

 dloc   varchar2(20),

   constraint d2_deptno_pk primary key (deptno),

   constraint d2_dname_nn  not null (dname));

6행에 오류:

==> ORA-00904: : 부적합한 식별자

==> NOT NULL 제약조건은 Table-Level에서 정의할 수 없습니다.

                       컬럼 레벨에서 정의하거나

무슨 일이 있어도 테이블 레벨에서 정의하고 싶다면 check 제약조건을 사용해야 합니다.

create table d2 (

 deptno number(2),

 dname  varchar2(20),

 dloc   varchar2(20),

   constraint d2_deptno_pk primary key (deptno),

   constraint d2_dname_nn  check (dname is not null));

테이블이 생성되었습니다.

desc d1

이름                                                  널?      유형

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

DEPTNO                        NOT NULL NUMBER(2)

DNAME                         NOT NULL VARCHAR2(20)

DLOC                                   VARCHAR2(20)

desc d2

이름                                                  널?      유형

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

DEPTNO                        NOT NULL NUMBER(2)

DNAME                                  VARCHAR2(20)

DLOC                                   VARCHAR2(20)

==> dname 행에 NOT NULL이 없습니다.

insert into d1 values (10, null, 'Busan');

==> ORA-01400: NULL을 ("JAVA30"."D1"."DNAME") 안에 삽입할 수 없습니다

insert into d2 values (10, null, 'Busan');

==> ORA-02290: 체크 제약조건(JAVA30.D2_DNAME_NN)이 위배되었습니다

alter table d2 modify (dname not null);

desc d2;

insert into d1 values (10, 'SALES', 'Busan');

insert into d1 values (20, 'Executives', 'Seoul');

insert into d1 values (30, 'Advertisement', null);

insert into d2 values (10, 'SALES', 'Busan');

insert into d2 values (20, 'Executives', 'Seoul');

insert into d2 (deptno, dname) values (30, 'Advertisement');

commit;

select * from d1;

select * from d2;

drop table e1 purge;

drop table e2 purge;

create table e1 (

 empno  number(4)    constraint e1_empno_pk  primary key,

 ename  varchar2(20) constraint e1_ename_nn  not null,

 deptno number(2)    constraint e1_deptno_fk references d3(deptno));

==> 오류: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

create table e1 (

 empno  number(4)    constraint e1_empno_pk  primary key,

 ename  varchar2(20) constraint e1_ename_nn  not null,

 deptno number(2)    constraint e1_deptno_fk references d1(deptno),

 sal    number(8, 2));

create table e2 (

 empno  number(4),

 ename  varchar2(20),

 deptno number(2),

 sal    number(8, 2),

    constraint e2_empno_pk  primary key (empno),

    constraint e2_ename_nn  check(ename is not null),

    constraint e2_deptno_fk foreign key (deptno) references d2(deptno),

    constraint e2_sal_ck    check(sal > 1000));

alter table e1 modify (sal constraint e1_sal_ck check (sal > 1000));

insert into e1 values (1000, 'James', null, 2000);

==> null 값은 parent table의 값과 관계없이 insert 가능

insert into e1 values (2000, 'Adams', 40, 2500);

==> ORA-02291: 무결성 제약조건(JAVA30.E1_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다

insert into e1 values (2000, 'Adams', 10, 2500);

insert into e1 values (3000, 'King', 20, 1000);

==> ORA-02290: 체크 제약조건(JAVA30.E1_SAL_CK)이 위배되었습니다

insert into e1 values (4000, 'Clark', 30, 4000);

commit;

insert into e2

 (select * from e1);

update e1

set sal = 1100

where empno = 4000;

insert into e2

 (select * from e1);

==> ORA-00001: 무결성 제약 조건(JAVA30.E2_EMPNO_PK)에 위배됩니다

select * from e1;

select * from e2;

  * 제약조건 정보 확인: user_constraints, all_constraints, dba_constraints

col table_name format a10

col constraint_name format a15

col constraint_type format a5

col search_condition format a20

select table_name, constraint_name

    , constraint_type, search_condition

from user_constraints

where table_name in ('D1', 'D2', 'E1', 'E2');

  * referenced 테이블 삭제

drop table e1 purge;

drop table e2 purge;

create table e1 (

 empno  number(4)    constraint e1_empno_pk  primary key,

 ename  varchar2(20) constraint e1_ename_nn  not null,

 deptno number(2)    constraint e1_deptno_fk references d1(deptno) on delete cascade,

 sal    number(8, 2) constraint e1_sal_ck    check (sal > 1000));

create table e2 (

 empno  number(4),

 ename  varchar2(20),

 deptno number(2),

 sal    number(8, 2),

    constraint e2_empno_pk  primary key (empno),

    constraint e2_ename_nn  check(ename is not null),

    constraint e2_deptno_fk foreign key (deptno) references d2(deptno) on delete set null,

    constraint e2_sal_ck    check(sal > 1000));

insert into e1 values (1000, 'James', null, 2000);

insert into e1 values (2000, 'Adams', 10, 2500);

insert into e1 values (3000, 'King', 20, 1100);

insert into e1 values (4000, 'Clark', 30, 4000);

insert into e2

 (select * from e1);

commit;

drop table d1 purge;

==> 에러 : ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다

  * referenced 테이블에서 행 삭제

create table e3 (

 empno  number(4)    constraint e3_empno_pk  primary key,

 ename  varchar2(20) constraint e3_ename_nn  not null,

 deptno number(2)    constraint e3_deptno_fk references d1(deptno),

 sal    number(8, 2) constraint e3_sal_ck    check (sal > 1000));

insert into e3 select * from e1;

delete from e3

where empno = 2000;

select * from e3;

delete from d1

where deptno = 30;

==> ERROR at line 1:

   ORA-02292: integrity constraint (JAVA30.E3_DEPTNO_FK) violated - child record found

drop table e3 purge;

select * from d1;

select * from e1;

select * from e2;

select * from d2;

delete from d2

where deptno = 20

select * from d2;

select * from e2;

        alter session set time_zone = '+09:00';

alter session set nls_timestamp_tz_format = 'yyyy-mm-dd hh24:mi:ss';

select interval '123-2' year(3) to month from dual;

select current_timestamp

, current_timestamp + interval '123-2' year(3) to month from dual;

  - DATE - TIMESTAMP

          - TIMESTAMP WITH TIME ZONE

          - TIMESTAMP WITH LOCAL TIME ZONE

  - INTERVAL - INTERVAL YEAR TO MONTH

                  - INTERVAL DAY TO SECOND

alter session set time_zone = '+09:00';

select dbtimezone, sessiontimezone from dual;

drop table t_order purge;

create table t_orders (

 no number(4),

 customer_name varchar2(10),

 order_date1 date,

 order_date2 timestamp,

 order_date3 timestamp with time zone,

 order_date4 timestamp with local time zone);

insert into t_orders values

   (1000, 'Choi', current_timestamp, current_timestamp, 

             current_timestamp, current_timestamp);

select * from t_orders;

alter session set time_zone = '+0:0';

select * from t_orders;

alter session set time_zone = '-2:0';

select * from t_orders;

==> TIMESTAMP WITH LOCAL TIME ZONE은 입력된 시간을 

   DATABASE의 TIMEZONE에 대해 NORMALIZE하여 저장합니다.

        drop table t1 purge;

drop table t2 purge;

create table t1

 (no number,

  name varchar2(20),

  h_date date default sysdate);

desc t1;

        insert into t1 values (1000, 'James', to_date('02-01-11', 'MM-DD-YY'));

insert into t1 (no, name) values (2000, 'Clark');

select * from t1;

  -A schema is a collection of objects.

  - 스키마 이름은 사용자 이름과 똑같이 주어집니다.

 -  정리하세요

  -

  -

  -

  -

   - 다른 오브젝트와 같은 이름을 사용할 수 없음

create sequence t10;

create table t10(no number);

==> 오류: ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.

==> 오브젝트 타입이 달라도 같은 이름을 사용할 수 없습니다.

create table from (no number);

==> 오류: ORA-00903: 테이블명이 부적합합니다

==> 예약어(reserverd word), keyword는 오브젝트 이름으로 사용할 수 없습니다.

  - 키워드를 오브젝트 이름으로 사용하고 싶다면 아래와 같이 사용하세요

    (큰 따옴표 사용)

SQL> create table "insert" (no number(4));

SQL> insert into "insert" values (10);

SQL> select * from insert;

select * from insert

             *

ERROR at line 1:

ORA-00903: invalid table name

SQL> select * from "insert";

       NO

----------

       10

SQL> select * from "Insert";

select * from "Insert"

             *

ERROR at line 1:

ORA-00942: table or view does not exist

create table "from" (no number);

테이블이 생성되었습니다.

==> 큰 따옴표를 사용하면 예약어를 이름으로 사용할 수 있으나

==> 해당 오브젝트를 사용하려면 항상 큰 따옴표를 사용해야 합니다.

↓ ↓ ↓ 

insert into from values (10);

==> 오류: ORA-00903: 테이블명이 부적합합니다

insert into "from" values (10);

1 개의 행이 만들어졌습니다.

select * from from;

==> 오류: ORA-00903: 테이블명이 부적합합니다

select * from "from";

       NO

----------

       10