728x90

1. View는 논리적인 가상 Table

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

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

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

 

  (1) View의 문법 및 생성

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

      (column1, column2, column3,)

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, minvalue10 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

'데이터베이스 > 오라클' 카테고리의 다른 글

오라클 NLS_LANG 설정변경  (0) 2010.08.16
Oracle 시스템정보보기 관련 쿼리  (0) 2010.08.16
오라클 데이터사전  (0) 2010.08.16
인덱스 저장위치 설정  (0) 2010.08.16
P/L SQL PACKAGE  (0) 2008.08.18

+ Recent posts