database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 11307 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 11307
Oracle 기초강좌 (11)
작성자
정재익(advance)
작성일
2002-07-11 12:20
조회수
17,994

Oracle8의 sql3에 대해서

 

4.1.1 Oracle8의 SQL3 개요

 

Oracle8은 기존의 강력한 Oracle7을 확장한 오브젝트-릴레이션널(object relational) 데이터베이스이다. Oracle8은 오브젝트 기능이라 할수있는 사용자 정의 타입 , 중첩테이블 , 가변배열, 참조타입등을 지원하는것을 특징으로 하며 , Oracle8은 오브젝트 타입 정의와 오브젝트 모델링 기술을 위해 SQL3 표준안을 따른다. SQL3는 오브젝트 타입 생성및 수정 , 오브젝트 식별자(OID) 생성 및 저장, 오브젝트에 관한 레퍼런스 생성 및 저장 등을 제공한다.

 

4.1.2 Oracle8의 SQL3

 

객체지향 데이터베이스 애플리케션의 개발을 지원하려면, 데이터베이스 설계에 Oracle8을 사용하여 객체 타입을 만들어 사용하여야 한다. Oracle8에서 사용할수 있는 각각의 객체 타입에 관하여, SQL3의 사용법을 단위별로 소개하면서 배워보자.

 

4.1.2.1 Oracle8에서의 객체 타입

 

우선 Oracle8에서 사용할수 있는 객체 타입의 세 가지 사용법에 대한 의의를 찾아보자.

. 관계형 데이터베이스 모델 만드는 걸 강화하는 사용자 정의 데이터 형 만들기

. 관계형 데이터베이스 테이블 안에 중첩 테이블 만들기

. 관계형 데이터베이스 설계보다는 객체지향 데이터베이스 설계를 구현하는 객체 테이블 만들기

 

새로운 객체 타입을 만들때는 새로운 객체 타입에 나타낼수 있는 두가지의 기능이 있다.

. Header(명세부분)

. Body( 본체부분)

header(명세부분)에 대한 선언은 create (or replace) type으로 선언한다. Body(본체부분)에 대한 선언은 create (or replace) type body로 선언한다. Body는 header(명세부분)에서 하나 이상의 메소드를 선언할때 객체 타입에 대한 body를 만들어 주어야 한다.

 

4.1.2.2 관계형 데이터베이스 모델을 강화하는 사용자 정의 데이터 형 만들기

 

관계형 데이터베이스 객체를 보다 쉽게 구축하는데 사용할수 있는 사용자 정의 데이터형을 만들어보자. 우선 간단한 예제로 표현하면, 우리가 일반적을 공통적으로 사용할수 있는 우편번호(address_t)에 대한 예제를 보자

 

CREATE OR REPLACE TYPE address_type AS OBJECT (
  street1              varchar2(50)     ,
  street2              varchar2(50)     ,
  city                   varchar2(50)     ,
  state                 varchar2(25)      ,
  zipcode            varchar2(10)      ,
  country            varchar2(50) );

 

개발자들이 관계형 데이터베이스 테이블에서 주소를 선언할 필요가 있을때 ,

address_t 를 이용하여 사용할수 있다.

 

  Create table customers( 
                id                       integer               primary key   ,
                last_name          varchar2(50)                            ,
                first_name         varchar2(50)                            ,
                company_name varchar2(50)                            ,
                address              address_t 
    );

 

객체 타입을 이용하여 선언된 관계형 테이블에 있는 열로 작업을 하려면 , DML 명령은 특별한 구문으로 열 속성을 참조하여야 한다.

우선 객체 타입을 이용하여 선언된 관계형 테이블에 자료를 삽입하기 위해서는 Oracle8에서는 객체 타입의 선언에서 사용된 명칭과 동일한 생성자를 이용하여야 한다. 생성자는 객체 타입을 선언할때 , oracle8에서 자동적으로 동일한 이름으로 생성된다.

 

 Insert into customers 
 values( 1  , ‘Ellison’ , ‘Lawrence ‘ , ‘Oracle Corporation’ ,
              address_type( ‘500 Oracle parkway’ , ‘Box 6595101’ , ‘Redwood Shores’ ,
                                     ‘CA’                            , ‘95045’             , ‘IUSAI’ )
            );

 

위의 예제에서 생성된 자료를 쿼리하기 위해서는 select문에 확장된 표준 도트 기호법 형식을 이용하여 address_type열에 있는 속성을 참조해야 한다.

 

Select id , last_name , first_name ,

a.address.street1 , a.address.street2 , a.address.city

from customers a ;

 

마찬가지로 객체 타입을 이용하여 선언된 관계형테이블의 열을 갱신할때에도 확장된 도트 기호법을 사용하여 속성을 변경할수가 있다.

 

Update customers a

set a.address.zipcode = ‘94065’

where id = 1 ;

 

이곳에서 주의해야 할것은 자료를 생성(insert) 할때는 객체 타입의 생성자를 이용하여 자료를 생성하여야 하고 , 반면 select , update , delete문에 대한 DML문장에 대해서는 표준 도트 표기법을 이용하여 문장을 수행하지만 , table명에 대해서는 alices(예명)을 사용하여 처리하여야만 가능하다.

 

사용자 정의 데이터형을 이용하여 객체 지향의 데이터베이스 설계로 완전히 이동하지 않고도 단지 관계형 데이터베이스의 개발을 단순화하기 위해서 Oracle8로 객체 타입을 사용할수 있는 방법을 보여주었다

 

4.1.2.3 관계형 데이터베이스 테이블 안에 중첩 테이블 만들기

 

이번에는 객체 타입을 이용하여 하나의 테이블을 또다른 테이블 안에 중첩하는 방법을 배워보자.

자신의 페어런트 테이블 안에다 차일드 테이블을 넣을때 , 오라클은 페어런트 테이블의 각 행과 관련 중첩 테이블 행 사이에 구축된 관계를 자동으로 관리한다.

중첩 테이블의 사용은 관계형 테이블에서의 자료를 찾기위한 join의 사용을

피하므로 data의 처리 퍼포먼스가 향상될뿐만 아니라, resource의 사용을 줄이기도 한다.

중첩 테이블의 사용은 master-detail관계용으로 사용된 관계형 데이터베이스에서의 사용에 적합한 형태이다. 어찌보면 중첩테이블의 사용으로 관계형테이블에서의 cluster의 개념과 비슷한면도 있다.

 

다음의 예제는 전형적인 order(주문) 테이블안에 item(주문상품) 테이블을 중첩하는데 쓰일수 있는 형태를 나타내어준다.

 

Create or replace type item_type as object (
    item_id                       integer          ,
    quantity                      integer            );

create or replace type item_list as table of item_type ;

위에서 선언한 type을 이용하여 중첩table형태의 type으로 다시 설정한다.

Create table orders (
  id                      integer                primary key   ,
  order_date        date              ,
  ship_date          date              ,
  line_items         item_list     )
nested table line_items store as items;

<< 다른 형태의 예제 >>
create or replace type address_table as table of address_type;

create table imsi (
       id                       integer          primary key ,
       line_items         item_list         ,
       address             address_table )
nested table line_items store as item_table
nested table address store as address_tab;

 

테이블을 또다른 테이블 안에 넣을때, 오라클은 데이터를 하나의 물리적인 데이터 세그먼트안에 저장하지만, 데이터 사전에는 두개의 논리적인 테이블이 만들어진다. 앞의 예제에서 오라클은 order 테이블과 관련된 중첩 테이블 orders는 하나의 물리적인 데이터 세그먼트이지만 , 논리적으로 분리된 두개의 테이블로 만들었다.

 

이곳에서 주의할것은 논리적으로 나타난 중첩테이블의 detail부분에 해당하는부분은 개별적인 테이블의 내용을 보기위한 description(desc)는 작동이 원할하게 된다.

( 예 )

SQL> desc items;
이름                            Null?    유형
------------------------------- -------- ----
ITEM_ID                                  NUMBER(38)
QUANTITY                                 NUMBER(38)

 

반면에 논리적으로 나누어졌다고 하더라도 해당 중첩테이블의 detail에 해당하는 테이블의 개별적인 쿼리의 형태는 다음과 같은 error를 유발한다.

 

SQL> select * from items;

select * from items

*

1행에 오류:

ORA-22812: 내포된 테이블 열 저장 테이블을 참조할 수 없습니다

 

중첩테이블을 가지고 있는 테이블의 참조는 항시 다음 문구의 형태를 참조하여

표현되는 형태를 참조해야만 한다.

 

SQL> desc orders;
이름                            Null?    유형
------------------------------- -------- ----
ID                              NOT NULL NUMBER(38)
ORDER_DATE                               DATE
SHIP_DATE                                DATE
LINE_ITEMS                               ITEM_LIST

SQL> desc item_list;
item_list TABLE OF ITEM_TYPE
이름                            Null?    유형
------------------------------- -------- ----
ITEM_ID                                  NUMBER(38)
QUANTITY                                 NUMBER(38)

 

지금의 형태와는 다르게 dictionary를 이용하여 해당 테이블에 중첩항목을 찾을수는 있으나 dictionary에 대한 참조형태는 뒷장에서 설명할것이다.

현재는 중첩테이블을 이용한 객체 타입의 생성에 관한것이다.

 

중첩 테이블을 조작하기 위해서는 역시 특별한 DML 구문의 사용이 필요하다. 우선 이전에 설명했던것처럼 객체타입을 사용한 구문에 자료를 삽입하기 위해서는 생성자를 이용하였듯이 역시 이곳에서도 생성자를 이용하여 처리한다.

 

Insert into orders

values( 1 , sysdate , sysdate + 1 , item_list(item_type(1,22) , item_type(2,100)));

 

중첩 테이블에 있는 각각의 행으로 작업하려면, SQL문장은 단조로운 서브쿼리를 이용하여 마스터 테이블에 항상 액세스해야 하며 , 그것은 특별한 SQL 표현식 THE로 표시된다.

 

 

Insert into THE( select line_items from orders where id = 1 )

values( 3 , 200 );

 

insert into THE( select line_items from orders where id = 1 )

values( item_type( 3 , 200));

 

위의 두 구문의 사용은 동일한 결과를 나타낸다.

사용하기 편한 형태를 취하여 사용하는것이 좋다.

 

또한 중첩 테이블에서 행을 검색하려면, 단조로운 서브쿼리를 사용해야 한다.

 

Select item_id , quantity

from THE( select line_items from orders where id = 1 )

order by item_id ;

 

select a.item_id , a.quantity

from THE( select line_items from orders where id = 1 ) a

order by a.item_id ;

 

위의 두 구문의 사용은 동일한 결과를 나타낸다.

사용하기 편한 형태를 취하여 사용하는것이 좋다.

 

중첩 테이블에서 행을 삭제하려면 , 역시 단조로운 서브쿼리를 사용해야 한다.

 

Delete THE( select line_items from orders where id = 1 )

where item_id = 1;

 

Delete THE( select line_items from orders where id = 1 ) a

where a.item_id = 1 ;

 

위의 두 구문의 사용은 동일한 결과를 나타낸다. 사용하기 편한 형태를 취하여 사용하는것이 좋다.

 

지금까지의 위의 사용예는 관계형 데이터베이스 객체를 만들기 위한 객체 타입의 사용법을 소개하였다. 반드시 명심하여야 할것은 관계형 데이터베이스의 추축을 보다 쉽고 , 복잡한 데이터형을 구축하기위한 설정으로 위의 객체를 만들어 사용하지만, 지금부터 공부할 객체 테이블과는 다르다는것을 알아두기 바란다.

 

4.1.2.4 객체지향 데이터베이스 를 구축하기 위하여 객체 테이블 만들기

 

객체지향의 데이터베이스 설계로 구축하고자 한다면, 객체 타입을 이용해

객체 테이블을 만들 수 있다. 객체 테이블은 관계형 열 등이 아닌, 객체 타입만 이용하여 정의하는 데이타베이스 테이블이다. 객체 테이블을 만들때 , 테이블의 열은 그 테이블을 만드는데 사용한 객체 타입의 속성에 해당한다.

 

객체 테이블에 있는 행은 그 테이블 타입의 객체이다.

객체 테이블에 있는 각 객체에는 고유의 객체 식별자(object identifier: OID)가 있는데, 오라클은 다른 모든 OID들 사이에서 그것을 자동으로 색인하여 고유한 것이 되도록 보장한다. 오라클은 객체 테이블의 OID들을 이용하여 데이터베이스에 있는 다양한 객체 테이블 사이의 관계를 정의한다.

 

다음의 예로 객체 타입과 객체 테이블을 이용한 전용적인 order-entry 스키마를 구현한다.

 

Create or replace type customer_type as object (
     id                             integer             ,
     last_name                varchar2(50)   ,
     first_name               varchar2(50)   ,
     company_name       varchar2(50)   ,
     address                     address_type );

create table customers of custmer_type ( id      primary key );

create or replace type part_type as object (
    id                             integer             ,
   description               varchar2(50)    ,
   unit_price                 number(10,2)  ,
   on_hand                   integer             ,
   reorder_point           integer );

create table parts of part_type( id        primary key );

create or replace type item_type as object (
   item_id                    integer             ,
   part            ref         part_type          ,
   quantity                  integer  );

create or replace type item_list as table of item_type;

create or replace type order_type as object (
  id                             integre              ,
 customer     ref        customer_type  ,
 order_date               date                   ,
 ship_date                 date                   ,
 line_items                item_list );
Create table orders of order_type( id       primary key )
nested table line_items store as items;

 

위의 예제는 전형적인 객체 테이블 형태이다.

예제에서 item_type의 type에서 part 속성은 part_type 타입의 객체를 참조하고 , order_type의 type에서 customer의 속성은 customer_type 타입의 객체를 참조한다. 이것은 관계형 데이터베이스 테이블에 있는 포린 키와 비슷한 것으로 , 객체를 참조할때는 OID(object identifier)를 사용하여 데이터베이스 어딘가에 있는 또다른 객체를 지시하는 하나의 객체의 속성이다.

 

이제 객체 타입으로 생성된 객체 테이블에 자료를 생성하는 DML방법을 사용하여보자.

 

Insert into parts
values( part_type( 1  , ‘Pentium 200 cpu’ , 250.00 , 1000 , 300 ));

insert into customers
values( customer_type( 1 , ‘Ellisonl’ , ‘Lawrence’ , ‘Oracle Corporation’ ,
            address_type( ‘500 oracle parkway’ , ‘box 659510’ , ‘Redwood shores’ , ‘CA’ , ‘195045’  , ‘USA’)));

 

객체 테이블이 객체 참조인 속성을 포함할때 , 속성을 포함하는 DML은 어떤 특별한 구문을 사용할 필요가 있다.

그 중에서도 특히 , SQL과 PL/SQL 문장은 REF 함수를 이용하여 객체 참조와 작업해야 한다 REF는 참조나 포인터를 특정 객체의 OID로 리턴하는 SQL함수이다. 다음의 insert문은 서브쿼리를 이용하여 새로운 객체에 대한 값 목록을 구축한다. 서브쿼리는 또한 새로운 주문에 대한 고객의 OID를 결정한다.

 

Insert into orders

select 1 , ref( c ) , sysdate , null , item_list()

from customers c

where id = 1;

 

detail에 해당하는 주문의 항목에 자료를 삽입을 하려면 , 단조로운 서브쿼리가 있는 insert 명령을 사용해야 한다.

 

Insert into THE( select line_items from orders where id = 1 )

select 1 , ref(p) , 20 from parts p

where id = 2;

 

이곳에서 잠시 pl/sql 블록을 이용하여 데이터베이스 애플리케이션이 select 와 insert, 그리고 update문 안에서 프로그램 변수를 이용하여 OID를 참조하는 방법을 살펴보자

 

Declare
     custoid    ref         customer_type ;
     partoid    ref          part_type        ;
begin
    select ref( c ) into custoid 
       from customers
      where id = 1 ;

    select ref( p ) into partoid
       from parts
      where id = 1 ;

    insert into orders
    values( order_type ( 1 , custoid , sysdate , null , item_list( item_type( 1 , partoid , 50))));
exception when 
                       no_data_found then
                           raise_application_error(-2001 , ‘no data found’);
end;

 

여기서 데이터를 객체 테이블로 입력하는데 필요한 단계가 해당 관련 SQL구문처럼 직관적이지 않으므로 많은 연습이 필요하다

 

반면에 객체 형태를 이용한 table을 기준으로 해당 table에 존재하는 자료의 구조를 확인하기 쉽도록 하는 간단한 script문장을 하나 소개한다. 이문장은 table의 이름을 입력하여 해당 table에 있는 column의 구조에 대한 정보를 한눈에 파악할수 있는 script이다.

 

select a.type_name , a.attr_name , a.attr_type_mod , a.attr_type_name ,

b.coll_type , b.elem_type_name

from user_type_attrs a , user_coll_types b , user_object_tables c

where a.attr_type_name = b.type_name(+)

and a.type_name = c.table_type

and c.table_name = 'ORDERS’ -- ‘안에 찾고자 하는 table명을 입력한다’

order by 1

 

이제는 간단한 예로 객체 테이블에 대한 객체 쿼리를 orders 와 customers 객체 테이블의 관계 정보를 리턴하는 문장을 작성하여보자

 

select o.id , o.customer.company_name

from orders o;

 

다음은 중첩 items 테이블과 관련된 parts 객체 테이블의 정보를 검색한다.

 

 

Select o.item_id , o.part.description , o.quantity

from THE( select line_items from orders where id = 1 ) o;

 

지금까지 우리는 객체 타입을 이용한 데이터베이스 객체와 객체 타입 사이에 dependency(종속)관계가 설치되어있다고들 생각이 들것이다. 사실적으로 오라클은 객체 타입들 간의 dependency관계를 자동으로 추적하기 때문에 사용자의 입장에서는 어떤 손상을 입거나 , 걱정을 하지 않아도 된다.

 

우리는 다음의 script의 실행으로 서로 연결이 되어있는 dependency의 관계를 파악할수가 있다.

 

Select * from user_dependencies

order by name , type;

 

이 script의 실행으로 user_dependencies의 dictionary에 있는 column중에 dependency_type 의 값이 head 나 ref로 설정이 되어있을때 , head의 값은 다른 객체를 참조하지 않는다는 것이고 , ref의 값으로 설정되어 있으면 , 그 객체의 타입은 다른 객체를 참조한다는 것을 의미한다.

 

객체 타입의 설계에서 중요한 것은 데이터베이스 객체 타입을 주의해서 계획하고, 설계해야 한다는 것이다. 객체 타입을 의존하는 다른 객체 테이블이 존재할때는 객체 타입의 수정이 가능하지 않기 때문에 종속적인 관계의 형태가 복잡해지거나 , 꼬이는수가 생길수도 있다. 물론 이것은 설계자의 파악하는것의 의미이지만 , 전체적인 혼란을 야기한다. 반드시 주의할 내용이므로 , 정확히 팍악하여야 할것이다.

 

지금까지의 내용이 객체 타입의 생성에서 method의 정의 없이 객체 타입의 생성과 그 객체 타입으로 설정된 객체 테이블의 활용을 보았다.이젠 객체 타입의 생성에서 부터 객체 타입에 대한 method의 구축으로 보다 완전한 객체 형태를 구축하는 방법을 배워보자.

이 글에 대한 댓글이 총 2건 있습니다.

 

님이 2003-04-09 14:23에 작성한 댓글입니다.

 

좋은 자료 감사합니다.
푸른달님이 2006-02-15 23:40에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
11322EXPORT받은 DUMP 파일의 CHARACTER SET 확인 방법
정재익
2002-07-12
8828
11321ORACLE 9I 에서의 NATIONAL CHARACTERSET("AL16UTF16" )
정재익
2002-07-12
12818
11320DBMS_JOB PACKAGE의 사용 방법과 예제
정재익
2002-07-12
8755
11307Oracle 기초강좌 (11) [2]
정재익
2002-07-11
17994
11306Oracle 기초강좌 (10)
정재익
2002-07-11
18554
11305Oracle 기초강좌 (9)
정재익
2002-07-11
11664
11304Oracle 기초강좌 (8)
정재익
2002-07-11
15298
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.050초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다