[Oracle] Oracle 응용 예제(OE)

오라클은 객체 지향 데이터 저장을 위해 새로운 데이터베이스를 만들기보다는 기존의 데이터베이스내 객체 모델을 추가함으로써 객체 지향 프로그래머의 요구 사항을 충족하는 동시에 Oracle이 가진 기존의 장점을 그대로 유지한다.

 

Oracle 객체 타입(사용자 정의 데이터 타입)은 객체를 이용하는 C++이나 JAVA 애플리케이션과 통신을 쉽게한다. 즉 애플리케이션에 이용될 객체와 대응되는 Oracle 객체 타입을 모델링하고, 프로그래밍 인터페이스를 통해 관계 혹은 객체 테이블의 데이터를 관리할 수 있다. 객체 타입을 이용하지 않는 기존 애플리케이션은 객체 뷰(Object View)를 이용할 수 있다.

 

객체 타입은 실제 개체의 추상화된 형태이다.

객체타입	설명
이름	객체 타입을 식별하기 위해 요구된다.
속성	실 세계 개체의 상태나 구조를 나타내며, built-in 타입이나 사용자 정의 타입이다.
메소드	실 세계에서 어떻게 동작하는가를 정의하며, 함수나 PL/SQL로 구현된 SP등으로 구현된다.

 

external_person과 lineitem은 Oracle이 제공하는 타입을 가지는 속성들로 구성되며, purchase_order은 실제 구입 주문서의 양식에 가까운 구조를 가진다.

 

CREATE TYPE external_person AS OBJECT
(
    name VARCHAR2(30)
   ,phone VARCHAR2(20)
);

 

계약(contract)이라는 관계 테이블 생성 시 다음과 같이 이미 생성한 객체 타입 external_person을 아래와 같이 적용할 수 있다. 사용자 정의 타입은 생성 시에는 저장 공간을 사용하지 않는다. 테이블 생성 시 NUMBER나 VARCHAR2와 같이 데이터 타입으로 사용될 때 비로소 저장 공간을 할당하고 데이터를 저장하게 된다.

 

CREATE TABLE contracts
(
    contact external_person
   ,contact_date DATE
);

 

데이터 입력 시 생성한 객체 타입 external_person 생성자(Constructor)를 사용하여 데이터를 입력한다.

 

INSERT INTO contacts VALUES(external_person('Kim Tae Geun', '000-614-9515'), SYSDATE);

 

혹은 다음과 같이 입력할 수 있다.

DECLARE
        l_external_person external_person;
BEGIN
        l_external_person := external_person('Kim Tae Geun','000-614-9515');
        
        INSERT INTO contacts VALUES(l_external_person, sysdate);
END;

 

질의할 때에는 '테이블 별칭(Alias).칼럼이름.객체타입' 형태로 질의하여 객체 내 저장된 데이터를 얻어올 수 있다.

 

SELECT c.contact.name, c.contact.phone
FROM contacts c;

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

가변 배열 VARRAY(Variable Array)

하나 이상의 칼럼이 가질 수 있는 값의 개수가 가변적인 경우가 있다.

 

위  external_person 객체 내 속성인 phone의 데이터 타입을 가변 배열로 설정해보자.

CREATE OR REPLACE TYPE phone_list_typ AS VARRAY(3) OF VARCHAR2(20);

 

ORA-02303: cannot drop or replace a type with type or table dependents

이미 생성된 사용자 정의 타입이 테이블의 칼럼으로 설정되어 있거나 다른 객체 타입에 사용 되고 있으면, 해당 테이블이나 객체 타입을 먼저 삭제해야한다.

 

DROP TABLE contacts;

DROP TABLE external_person;

CREATE TYPE external_person AS OBJECT
(
    name VARCHAR2(30)
   ,phone phone_list_typ
);

 

삭제된 계약 테이블(contacts)을 생성하고, 구조를 확인해보자.

 

CREATE TABLE contacts
(
    contact external_person
   ,contact_date DATE
);


-- 테이블 확인
DESC contacts

DESC external_person

 

생성된 contacts 테이블에 데이터 추가

INSERT INTO contacts VALUES (external_person('Kim Tae Geun', phone_list_typ('000-614-9515', '02-789-1234')), SYSDATE);

 

가변 배열을 사용하는 테이블을 검색할 때는 PL/SQL로 출력 프로그램을 작성하여 사용해야한다. SQL문에서는 하나의 요소만을 질의할 수 없기 때문이다.

SELECT c.contact.name, c.contact.phone
FROM contacts c;

CONTACT.NAME	CONTACT.PHONE
Kim Tae Geun	PHONE_LIST_TYP("000-614-9515", "02-789-1234")

SET SERVEROUTPUT ON

DECLARE
CURSOR c1 IS SELECT c.contact.phone FROM contacts c;
       v_phone_list phone_list_typ;
BEGIN
       OPEN c1;
       LOOP
            FETCH c1 INTO v_phone_list;
            EXIT WHEN C1%NOTFOUND;
            
            FOR i IN 1 .. v_phone_list.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(v_phone_list(i));
            END LOOP;
       END LOOP;
       CLOSE c1;
END;

000-614-9515

02-789-1234

PL/SQL procedure successfully completed.

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

중첩 테이블(NESTED TABLE)

중첩 테이블은 칼럼 데이터 타입이 테이블인 칼럼으로, 칼럼 값으로 다른 테이블을 삽입할 수 있다.

CREATE TYPE lineitem AS OBJECT (
 item_name VARCHAR2(30)
,quantity NUMBER
,unit_price NUMBER(12,2)
);

AS TABLE OF 절을 사용하여 중첩 테이블로 사용할 객체 타입을 생성한다.

CREATE TYPE lineitem_table AS TABLE OF lineitem;

lineitem_table 객체 타입을 칼럼의 데이터 타입으로 가지는 테이블을 생성한다.

Lineitems 칼럼은 중첩 테이블(nested table)을 데이터 타입으로 가지며, users 테이블 스페이스에 저장됨을 나타낸다.

CREATE TABLE purchase_order(
 id NUMBER
,contact external_person
,lineitems lineitem_table
)
NESTED TABLE lineitems STORE AS users;

데이터를 추가할 때는 앞서 생성한 lineitem_table() 과 lineitem() 생성자를 이용하여 중첩 테이블에 데이터를 저장한다.

INSERT INTO purchase_order VALUES (1,external_person('Kim Tae Geun', phone_list_typ('000-614-9515', '02-789-1234')), lineitem_table(lineitem('item1', 10, 100)));

SELECT *
FROM purchase_order;

ID	CONTACT(NAME,PHONE)	LINEITEMS(ITEM_NAME, QUANTITY, UNIT_PRICE)
1	EXTERNAL_PERSON("Kim Tae Geun", PHONE_LIST_TYP("000-614-9515", '02-789-1234"))	LINEITEM_TABLE(LINEITEM("item1", 10, 100))

이상과 같이 오라클이 제공하는 객체 기술은 개발자가 데이터 타입을 정의하고, 정의한 객체의 속성과 기능을 정의함으로써 다양해진 데이터 타입을 구현 가능하도록 지원한다.

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

 

객체 뷰(Object View)

CREATE TYPE orders_typ AS ObJECT(
 order_id NUMBER(12)
,order_status NUMBER(2)
,order_mode VARCHAR2(8)
,order_total NUMBER(8,2)
,order_date TIMESTAMP WITH LOCAL TIME ZONE
);

앞서 생성한 객체 타입 orders_typ를 이용하여 Object View를 생성한다.

CREATE OR REPLACE VIEW orders_vw OF orders_typ WITH OBJECT OID (order_id)
AS
SELECT order_id, order_status, order_mode, order_total, order_date
FROM orders;

WITH OBJECT OID는 Object View를 위한 객체 식별자를 지정한다. 객체 식별자 order_id는 Object View 각각의 행에 대한 유일한 값을 가진다.

DESC orders_vw

Name	Null?	Type
ORDER_ID		NUMBER(12)
ORDER_STATUS		NUMBER(2)
ORDER_MODE		VARCHAR2(8)
ORDER_TOTAL		NUMBER(8,2)
ORDER_DATE		TIMESTAMP(6) WITH LOCAL TIME ZONE

생성된 View는 USERS_VIEWS 데이터 사전을 통해 View의 정보를 확인할 수 있다.

SELECT text, view_type
FROM user_views
WHERE view_name = 'ORDERS_VW';

TEXT	VIEW_TYPE
SELECT order_id, order_status, order_mode, order_total, order_date FROM orders	ORDERS_TYP

VIEW_TYPE이 ORDER_TYP이므로 View로부터 객체 타입이 반환됨을 알 수 있다.

 

SELECT order_id, order_date, order_total
FROM orders_vw
WHERE TO_CHAR(order_date, 'YYYY-MM') = '1999-09';

ORDER_ID	ORDER_DATE	ORDER_TOTAL
2440	99/09/01	70576.9
2435
2455
2434
2436
2432
2433
2438	99/09/02	5451

Oracle의 객체-관계 기술은 관계 구조의 상단에 객체 층(Layer)을 추가할 수 있도록 한다. 즉 객체 층 아래 데이터는 관계 테이블에 저장되지만, Oracle은 이 데이터를 객체 타입으로 캡슐화(Encapsulation)할 수 있도록 해 주게 된다.

 

Object View 사용함으로써 Oracle은 관계 데이터를 객체 단위로 네트워크를 통해 전송하므로 네트워크의 부하를 줄일 수 있다는 것이 장점이다.

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