컴퓨토피아

Oracle Version 보기
SELECT banner FROM  V$VERSION;

1. View는 논리적인 가상 Table

View는 한 개 이상의 Table 질의에 대한 가상 Table입니다.

  View는 실제로 data를 포함하거나 저장하지 않는 논리적인 구조로 Base Table의 data를

  조회하거나 변경할 수 있습니다.

 

  (1) View의 문법 및 생성

      create [or replace] [force | noforce]  view  view명

      (column명1, column명2, column명3,…)

as

Select문

[with check option [constraint constraint명]]

[with read only]

      ㄱ. [or replace]는 동일한 view가 존재할 경우 기존 view를 대체(replace)시킨다.

      ㄴ. [force] option은 base table이 존재하지 않을 경우에도 강제로 view를 만든다.

         [noforce] option은 base table이 없는 경우 view가 작성되지 않게 한다.(default)

      ㄷ. [with check option]은 select문의 where절의 조건에 대한 checking을 합니다.

         [with read only]는 view를 통한 data의 변경을 금지하고 조회만 가능하게 한다.

ㄹ. emp Table에서 부서 번호가 20인 Row를 얻는 view를 생성시킨다.

SQL> create view emp_20_vu

  2  as

  3  select empno, ename, sal, deptno

  4  from   emp

  5  where  deptno = 20

  6  order  by sal;

뷰가 생성되었습니다.

ㅁ. view의 구조는 아래와 같다.

SQL> desc emp_20_vu

 이름                                          널?      유형

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

 EMPNO                                           NUMBER(4)

 ENAME                                           VARCHAR2(10)

 SAL                                               NUMBER(7,2)

 DEPTNO                                          NUMBER(2)

ㅂ. view를 이용해 자료를 조회한다.

SQL> select empno, ename, sal, deptno

  2  from   emp_20_vu;

 

    EMPNO ENAME            SAL    DEPTNO

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

     7369   SMITH             800        20

     7876   ADAMS           1100        20

     7566   JONES            2975        20

     7788   SCOTT           3000        20

     7902   FORD             3000        20

ㅅ. column alias를 이용한 view 생성

   emp Table에서 column alias를 이용해 자료를 조회한다.

 SQL> create or replace view emp_20_vu

  2  (eno, ename, salary, deptno)

  3  as

  4  select empno, ename, sal * 1.15, deptno

  5  from   emp

  6  where  deptno = 20

  7  order  by 3;

뷰가 생성되었습니다.

     o. view를 이용해 자료를 조회한다.

 SQL> select eno, ename, salary, deptno

  2  from   emp_20_vu;

 

      ENO ENAME         SALARY    DEPTNO

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

     7369 SMITH               920        20

     7876 ADAMS             1265        20

     7566 JONES           3421.25        20

     7788 SCOTT             3450        20

     7902 FORD               3450        20

  (2) view를 사용하는 장점

     ㄱ. base table을 직접 access하지 않으므로 보안에 유리합니다.

     ㄴ. 복잡한 질의를 간단하게 접근할 수 있다.

     ㄷ. View는 가상의 table이므로 삭제해도 base table에는 전혀 영향이 없다.

(3) View의 원리

   VIEW는 user_view라는 data dictionary에 SELECT문으로서 저장되어 있다.

  

   ㄱ. user_views의 구조를 살펴보자.

SQL> desc user_views

 이름                                널?      유형

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

 VIEW_NAME                     NOT NULL VARCHAR2(30)

 TEXT_LENGTH                              NUMBER

 TEXT                                        LONG

 TYPE_TEXT_LENGTH                        NUMBER

 TYPE_TEXT                                  VARCHAR2(4000)

 OID_TEXT_LENGTH                          NUMBER

 OID_TEXT                                    VARCHAR2(4000)

 VIEW_TYPE_OWNER                          VARCHAR2(30)

 VIEW_TYPE                                   VARCHAR2(30)

   ㄴ. user_views에 대해 부분 질의를 하면 아래와 같다.

SQL> select view_name, text

  2  from   user_views;

 

VIEW_NAME       TEXT

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

EMP_20_VU        select empno, ename, sal * 1.15, deptno

from   emp

where  deptno = 20

order  by 3;

     ㄷ. emp_20_vu에 대한 질의를 하면 oracle은 user_views라는 data dictionary를 통해

emp_20_vu를 작성할 때 사용한 질의를 찾아서 실행합니다.

(4) 복잡한 View의 실습

    ㄱ. s_dept와 s_emp에서 부서명과 평균급여액, 최고급여액을 구하는 view이다.

SQL> create view dept_avgmax_vu

  2  (dept_name, avg_sal, max_sal)

  3  as select d.name, avg(e.salary), max(e.salary)

  4  from s_dept d, s_emp e

  5  where d.id = e.dept_id

  6  group by d.name;

    ㄴ. 생성된 view를 생성하면 아래와 같다.

SQL> select dept_name, avg_sal, max_sal

  2  from   dept_avgmax_vu;

 

DEPT_NAME             AVG_SAL   MAX_SAL

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

Administration                 2025      2500

Finance                        1450      1450

Operations                   1086.8      1450

Sales                     1367.8571      1525

(5) View를 통한 data 변경

   ㄱ. View가 하나의 Table을 사용하고 변경할 권한이 있으면, View를 참조하는

      base Table을 변경할 수 있다.

   ㄴ. View가 여러 개의 Table을 사용한다면 View를 통한 data변경은 힘들다.

   ㄷ. dept Table을 이용해 view를 생성하고, insert문을 이용해 data를 입력한다.

 SQL> create view dept_vu

  2  as

  3  select * from dept;

 

뷰가 생성되었습니다.

 

SQL>  insert into dept_vu (deptno, dname, loc)

  2   values (50, 'MANAGE', 'KOREA');

 

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

     ㄹ. 실제 dept Table을 확인해 보자.

 SQL> select * from dept;

 

   DEPTNO DNAME          LOC

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

       10 ACCOUNTING     NEW YORK

       20 RESEARCH       DALLAS

       30 SALES          CHICAGO

       40 OPERATIONS     BOSTON

50 MANAGE         KOREA

 

(6) View의 삭제

     drop  view  view명;

 

2. Sequence

  Sequence는 Table에서 유일한 번호를 자동으로 만들어 주는 oracle object입니다.

 

(1) 문법

    create sequence  sequence명

    [increment by n]             è default 1

    [start with n]                è default 1

    [maxvalue n]                è default 10의 27승

    [minvalue n]                 è maxvalue일때 시작값

    [cycle]                      è default nocycle

    [cache n | nocache]         è cache n의 default 20

 

ㄱ. increment by는 sequence를 증가할 때 증가분이다.

ㄴ. start with는 sequence의 최초값이다.

ㄷ. maxvalue는 10의 27승, minvalue는 –10의 26승까지 값을 가진다.

ㄹ. cache 다음의 수는 oracle이 미리 할당하고 유지할 값의 수이다.

ㅁ. cycle은 maxvalue나 minvalue까지 값이 모두 생성되었을 때 계속 sequence가

생성되도록 하는 option이다.

     ㅂ. cycle option은 기본키(PK)로 정의된 column에는 사용하지 않습니다.

(2) sequence의 값 구하기

ㄱ.    현재값 구하기

sequence명.currval

ㄴ.    다음값 구하기

sequence명.nextval

(3) sequence 변경

alter sequence  sequence명

    [increment by n]                 

    [maxvalue n]               

    [minvalue n]                

    [cycle]                     

    [cache n | nocache]        

 

    여기서 주의할 것은 start with는 변경할 수 없다는 것이다.

(4) sequence 삭제

   drop  sequence  sequence명;

(5) sequence에 대한 data 조회

   sequence에 대한 data 조회는 user_sequences data dictionary를 사용합니다.

 

ㄱ. user_sequences의 구조를 살펴보자.

SQL> desc user_sequences

 이름                                                  널?      유형

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

 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)

 MIN_VALUE                                               NUMBER

 MAX_VALUE                                              NUMBER

 INCREMENT_BY                               NOT NULL NUMBER

 CYCLE_FLAG                                             VARCHAR2(1)

 ORDER_FLAG                                             VARCHAR2(1)

 CACHE_SIZE                                  NOT NULL NUMBER

 LAST_NUMBER                               NOT NULL NUMBER

ㄴ. 현재 생성된 sequence를 살펴보자.

SQL> select sequence_name, min_value, max_value, increment_by, last_number

  2  from   user_sequences;

 

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER

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

S_CUSTOMER_ID            1      9999999                1         216

S_DEPT_ID                  1      9999999                1          51

S_EMP_ID                   1      9999999                1          26

S_ORD_ID                   1      9999999                1         113

S_PRODUCT_ID             1      9999999                1       50537

S_REGION_ID               1      9999999                1            6

S_WAREHOUSE_ID          1      9999999                1       10502

 

7 개의 행이 선택되었습니다.

 

 

 

 

(6) sequence를 이용한 실습

ㄱ. tb_seq라는 table을 생성하자.

SQL> create table tb_seq

  2  (

  3   id   number(7) not null,

  4   name varchar2(10)

  5  );

 

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

ㄴ. tb_seq_id_seq 라는 sequence를 생성하여 tb_seq의 id라는 column에 이용하자.

SQL> create sequence tb_seq_id_seq

  2  increment by 1

  3  start with 1

  4  maxvalue 9999999

  5  cycle;

 

주문번호가 생성되었습니다.

ㄷ. tb_seq table에 insert시 sequence를 이용하자.

SQL> insert into tb_seq (id, name)

  2  values (tb_seq_id_seq.nextval, '박대환');

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

ㄹ. 실제 data를 확인하자.

SQL> select id, name from tb_seq;

       ID NAME

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

        1   박대환

ㅁ. 현재 sequence의 값을 알아보자.

SQL> select tb_seq_id_seq.currval from dual;

 

  CURRVAL

---------

        1

 

 

 

 

3. Synonym

  Synonym은 DB object(Table, View, Procedure, Function)등에 대한 별명입니다.

 

(1) Synonym 생성 문법

   Create [public] synonym synonym명

   For owner.object명;

 

ㄱ. public은 모든 사용자가 접근 가능하도록 만든 것이다.

ㄴ. owner는 object를 소유한 oracle 사용자명이다.

   ㄷ. emp_20_vu라는 view에 대한 synonym emp20을 생성하자.

 SQL> create synonym emp20

  2  for    emp_20_vu;

 

동의어가 생성되었습니다.

(2) Synonym 삭제 문법

   Drop [public] synonym synonym명;

(3) 현 사용자가 속한 private synonym을 조회하려면 user_synonyms data dictionary를 사용한다.

ㄱ. user_synonyms의 구조를 살펴보자.

SQL> desc user_synonyms

 이름                                         널?      유형

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

 SYNONYM_NAME                        NOT NULL VARCHAR2(30)

 TABLE_OWNER                                     VARCHAR2(30)

 TABLE_NAME                           NOT NULL VARCHAR2(30)

 DB_LINK                                            VARCHAR2(128)

ㄴ. 현 사용자에 속한 synonym을 조회하자.

SQL> select synonym_name, table_owner, table_name

  2  from   user_synonyms;

 

SYNONYM_NAME     TABLE_OWNER     TABLE_NAME

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

EMP20                  SCOTT            EMP_20_VU

 

 

 

ㄷ. synonym을 이용해 data를 조회하자.

SQL> select * from emp20;

      ENO  ENAME       SALARY    DEPTNO

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

     7369  SMITH              920          20

     7876  ADAMS            1265          20

     7566  JONES          3421.25          20

     7788  SCOTT            3450          20

     7902  FORD             3450           20

 

4. Index

Index는 Table의 Row를 더 빨리 access 하기 위해 사용하는 Object이다.

Index를 적절히 생성하면 Oracle은 Application의 속도는 빨라질 것이다.

 

(1) Index 생성

TABLE 생성시 Primary Key나 Unique Constraint를 지정하면 Unique Index가 자동적으로 만들어진다.

 

ㄱ. 문법

CREATE [UNIQUE] INDEX index명

    ON table명(column명[, column명...]) ;

ㄴ. s_emp Table에서 name column을 index로 생성해보자.

SQL> create index s_emp_name_idx

  2  on s_emp (name);

 

인덱스가 생성되었습니다.

 

  (2) Index 삭제

     INDEX는 수정할 수 없으며, 수정하려면 삭제하고 다시 생성한다.

 

ㄱ. 문법

DROP INDEX index명 ;

    ㄴ. index s_emp_name_idx을 삭제해보자.

 SQL> drop index s_emp_name_idx;

 

인덱스가 삭제되었습니다.

  (3) 현 사용자가 속한 index를 조회하려면 user_indexes data dictionary를 사용한다.

ㄱ. user_indexes의 구조를 살펴보자.

 SQL> desc user_indexes

 이름                                           널?      유형

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

 INDEX_NAME                             NOT NULL VARCHAR2(30)

 INDEX_TYPE                                         VARCHAR2(27)

 TABLE_OWNER                           NOT NULL VARCHAR2(30)

 TABLE_NAME                            NOT NULL VARCHAR2(30)

 TABLE_TYPE                                         VARCHAR2(11)

 UNIQUENESS                                         VARCHAR2(9)

 COMPRESSION                                        VARCHAR2(8)

 PREFIX_LENGTH                                      NUMBER

 TABLESPACE_NAME                                  VARCHAR2(30)

 INI_TRANS                                            NUMBER

 MAX_TRANS                                          NUMBER

 INITIAL_EXTENT                                     NUMBER

 NEXT_EXTENT                                       NUMBER

 MIN_EXTENTS                                        NUMBER

 MAX_EXTENTS                                       NUMBER

 PCT_INCREASE                                       NUMBER

 PCT_THRESHOLD                                     NUMBER

 INCLUDE_COLUMN                                    NUMBER

 FREELISTS                                            NUMBER

 FREELIST_GROUPS                                    NUMBER

 PCT_FREE                                             NUMBER

 LOGGING                                              VARCHAR2(3)

 BLEVEL                                               NUMBER

 LEAF_BLOCKS                                         NUMBER

 DISTINCT_KEYS                                       NUMBER

 AVG_LEAF_BLOCKS_PER_KEY                         NUMBER

 AVG_DATA_BLOCKS_PER_KEY                        NUMBER

 CLUSTERING_FACTOR                                 NUMBER

 STATUS                                               VARCHAR2(8)

 NUM_ROWS                                            NUMBER

 SAMPLE_SIZE                                          NUMBER

 LAST_ANALYZED                                      DATE

 DEGREE                                               VARCHAR2(40)

 INSTANCES                                           VARCHAR2(40)

 PARTITIONED                                         VARCHAR2(3)

 TEMPORARY                                          VARCHAR2(1)

 GENERATED                                          VARCHAR2(1)

 SECONDARY                                          VARCHAR2(1)

 BUFFER_POOL                                        VARCHAR2(7)

 USER_STATS                                         VARCHAR2(3)

 DURATION                                            VARCHAR2(15)

 PCT_DIRECT_ACCESS                                 NUMBER

 ITYP_OWNER                                          VARCHAR2(30)

 ITYP_NAME                                            VARCHAR2(30)

 PARAMETERS                                         VARCHAR2(1000)

 GLOBAL_STATS                                       VARCHAR2(3)

 DOMIDX_STATUS                                      VARCHAR2(12)

 DOMIDX_OPSTATUS                                   VARCHAR2(6)

 FUNCIDX_STATUS                                     VARCHAR2(8)

ㄴ. user_indexes의 내용을 살펴보자.

 SQL> select index_name, table_name

  2  from   user_indexes;

 

INDEX_NAME                         TABLE_NAME

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

EMPCOPY_EMPNO_PK                EMP_COPY

PK_DEPT                             DEPT

PK_EMP                              EMP

S_CUSTOMER_ID_PK                 S_CUSTOMER

S_DEPT_ID_PK                       S_DEPT

S_DEPT_NAME_REGION_ID_UK       S_DEPT

S_EMP_ID_PK                        S_EMP

S_EMP_USERID_UK                   S_EMP

S_INVENTORY_PRODID_WARID_PK   S_INVENTORY

S_ITEM_ORDID_ITEMID_PK           S_ITEM

S_ITEM_ORDID_PRODID_UK           S_ITEM

 

S_ORD_ID_PK                         S_ORD

S_PRODUCT_ID_PK                   S_PRODUCT

S_PRODUCT_NAME_UK               S_PRODUCT

S_REGION_ID_PK                     S_REGION

S_REGION_NAME_UK                 S_REGION

S_TITLE_TITLE_PK                   S_TITLE

S_WAREHOUSE_ID_PK                S_WAREHOUSE

 

18 개의 행이 선택되었습니다.

출처 : http://simmys.tistory.com/category/DataBase/Oracle