[Oracle] Oracle 응용 예제(OE) with XML

오늘날 인터넷에서 일어나는 모든 정보 교환의 표준으로서 XML의 역활은 점점 더 커지고 있다.

XML은 이미 데이터 교환 양식으로서 애플리케이션 개발에 적용되고 있다. 이는 서로 다른 애플리케이션 간의 데이터 교환이 가능했기 떄문이다. XML을 데이터베이스에서 사용할 수 있고, 기존 애플리케이션에 XML 데이터를 통합할 수 있는 기능은 이제 필수적인 요구사항이다.

 

일반적으로 XML 문서 또한 하나의 데이터로 생각할 수 있다. 따라서 데이터를 데이터베이스에 저장하고 질의하는 기존의 방법은 XML 문서에도 적용되어야 한다. 또한 인덱싱(Indexing), 무결성(Integrity) 등도 XML 문서에 활용될 수 있어야 한다.

 

Oracle이 지원하는 XML의 새로운 기능으로 XMLType이 있다. XMLType은 XML 문서를 데이터처럼 저장하는 데이터 타입이다. 내부적으로 CLOB 형태로 XML 문서 전체를 저장하며, XPath 구문을 사용하여 SQL 문으로 저장된 XML 문서를 질의할 수 있게 한다.

 

XMLType은 시스템 정의 타입(System Defined Type)으로, 함수의 인자로 사용하거나 테이블이나 뷰의 칼럼으로 사용될 수 있다. 사용자가 테이블에 XMLType의 데이터 타입을 가지는 칼럼을 생성하면, Oracle은 이 칼럼에 저장되는 XML 데이터를 CLOB 타입으로 실제 저장한다. CLOB 타입으로 저장된 XML 문서의 갱신(update)은 전체 문서에 대해 이루어진다.

 

다음은 info라는 테이블에 address 칼럼의 데이터 타입을 'XMLTYPE'로 하여 테이블을 생성하고 있는 예이다. address 칼럼에 전화,이메일,주소 등의 정보를 가지고 있는 XML 문서를 저장하고 있다.

 

XMLTYPE 데이터 타입에 XML 문서를 생성하기 위

해서는 createXML() 메소드를 이용한다.

INSERT문을 보면 sys.xmltype.createdXML()을 사용하여 XML 문서를 생성한다. XMLType은 저장 프로시저의 파라미터와 반환된 값, 그리고 테이블의 칼럼에 데이터 타입으로 제공된다.

 

info 테이블 생성

CREATE TABLE info(
 id VARCHAR2(20)
,name VARCHAR2(20)
,address SYS.XMLTYPE
);

address 칼럼에 XML 문서 추가

INSERT INTO info
VALUES('20061101001', '김태근',
        sys.xmltype.createXML('<addr_spec>
                                     <phone>000-614-9515</phone>
                                     <email>for.econet@gmail.com</email>
                                     <address>서울시 영등포구</address>
                               </addr_spec>')
      );

저장된 XML 문서를 질의

SET LONG 400
SELECT *
FROM info;

ID	NAME	ADDRESS
20061101001	김태근	<addr_spec> <phone>000-614-9515</phone> <email>for.econet@gmail.com</email> <address>서울시 영등포구</address> </addr_spec>

전체 문서에서 일부의 값을 선택하기 위해서는 XML 문서의 특정 node에 접근하기 위해 Extract() 함수 내의 인자(argument)에 도큐먼트 Element(위 예에서는 addr_spec이 Document Element가 된다.)로부터 원하는 값이 있는 node까지 경로를 명시해 주면 된다. 이 node로부터 주소값을 얻기 위해서는 getStringVal()라는 함수를 이용한다.

SELECT name, i.address.extract('/addr_spec/address/text()').getStringVal() address
FROM info i;

NAME	ADDRESS
김태근	서울시 영등포구

 

XMLType을 지원하는 함수의 목록

XMLType 함수	설명
getClobVal()	CLOB 타입의 문서 반환
isFragment()	Fragment 여부 확인
getStringVal()	XML Node에서 문자열 값을 얻기
getNumVal()	XML Node에서 숫자값을 얻기
extract()	XPath를 이용하여 문서의 일부분 추출
ExistNode()	XPath 질의에 해당하는 노드 존재 확인
YourFunction()	확장가능

 Oracle의 확장성은 Oracle 텍스트 인덱싱을 포함하는 XML 문서에 대한 특별한 인덱싱을 가능하게 한다. 만약 XML 문서 내 특정 Node를 반복해서 검색해야 한다면 인덱스(INDEX)를 설정함으로써 검색 속도를 향상시킬 수 있다.

 

다음은 위에서 INSERT한 XML 문서에서 주소(address) Element에 인덱스를 생성한 예시이다.

CREATE INDEX address_idx ON info (address.extract('address_spec/address').getStringVal());

 

XMLTYPE은 Oracle에서 제공하는 XML 문서를 저장하기 위한 데이터 타입이다. 내부적으로 CLOB의 형태로 XML 문서 전체를 저장하며, XPath를 이용하여 문서의 내용에 접근할 수 있다. 그뿐만 아니라 문서에 대한 인덱싱을 통해 검색할 때 좋은 성능을 보장하고 있다.

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

 

OE에 활용된 Oracle 기술

REM ============================
REM grants from hr schema
REM ============================

CONNECT hr/&passhr;
GRANT REFERENCES, SELECT ON employees TO oe;
..
GRANT SELECT ON departments TO oe;

객체 타입이 테이블에 대해 SELECT(질의)와 REFERENCES(참조) 권한을 부여했다.

 

REFERENCES 권한은 해당 테이블에 제약 조건(Constraints)를 추가할 수 있으나, ROLE에 이 권한을 부여하지 못한다.

따라서 사원 테이블(employees)에 REFERENCES 권한을 부여받은 OE 사용자는 사원 테이블의 사원번호(employee_id)에 PK나 Unique를 설정하여 고객(CUSTOMER)이나 주문(ORDER) 테이블이 이를 참조함으로써 데이터 무결성(Integrity)을 보장 받을 수 있다. 참조키(Foreign Key)를 설정하기 위해서는 참조하고자 하는 테이블의 칼럼이 반드시 Primary Key나 Unique로 지정되어야만 가능하기 떄문이다.

 

오라클은 버전 8부터 Scalar Type의 데이터 타입 이외에도 개발자가 타입을 정의하여 사용할 수 있도록 객체 기술을 지원하고 있다.

CREATE TYPE cust_address_typ OID '82A4AF6A4CD1656DE034080020E0EE3D'
AS OBJECT(
 street_address VARCHAR2(40)
,postal_code VARCHAR2(10)
,city VARCHAR2(30)
,state_province VARCHAR2(10)
,country_id CHAR(2)
);

 

cust_address_typ 객체는 stress_address 칼럼에서 country_id에 이르는 다섯 개의 칼럼으로 구성되어 있으며, 고객의 주소를 저장하는 객체 타입이다.

DESC cust_address_typ

Name	Null?	Type
STREET_ADDRESS		VARCHAR2(40)
POSTAL_CODE		VARCHAR2(10)
CITY		VARCHAR2(30)
STATE_PROVINCE		VARCHAR2(10)
COUNTRY_ID		CHAR(2)

 

아래 예는 가변 문자열 값을 최대 5개 저장할 수 있는 VARRAY 타입의 객체를 phone_list_typ으로 정의한 것이다. 즉 고객의 전화번호와 관련된 정보를 저장할 수 있도록 지정해 놓았는데 대게 집 전화나 회사 전화 이외 핸드폰 전화번호 등을 지정된 타입에 저장할 수 있도록 하고 있다.

=========================================================================
REM Create phone_list_typ varray to be varray column in customers table.
REM =====================================================================

CREATE TYPE phone_list_typ OID '82A4AF6A4CD2656DE034080020E0EE3D'
AS VARRAY(5) OF VARCHAR2(25);

VARRAY 타입은 정의할 떄 공간이 할당되는 것이 아니라 테이블에 칼럼으로 사용될때 할당이 된다.

 

고객 테이블( CUSTOMERS)을 생성할 때 cust_address 칼럼과 phone_numbers 칼럼의 데이터 타입으로 각각 사용되고 있다.

CREATE TABLE customers(
 customer_id NUMBER(6)
..
,cust_address cust_address_typ
,phone_numbers phone_list_typ
..
);

 

OE 사용자 소유의 생성 테이블 중 상품의 재고 목록을 관리하고 있는 창고 테이블(WAREHOUSES)을 보면 warehouse_spec 칼럼의 값을 XML 데이터로 받아들이고 있음을 알 수 있다.

CREATE TABLE warehouses(
 warehouse_id NUMBER(3)
,warehouse_spec SYS.XMLTYPE
,warehouse_name VARCHAR2(35)
,location_id NUMBER(4)
,wh_geo_location MDSYS.SDO_GEOMETRY
);

 

INSERT INTO warehouses VALUES (1, NULL, 'Southlake, Texax', 1400,
MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(-103.00195, 36.500374, NULL), NULL, NULL));

UPDATE warehouses SET warehouse_spec = sys.xmltype.createxml(
'<?xml version = "1.0"?>
 <Warehouse>
 <Building>Owned</Building>
 <Area>25000</Area>
 <Docks>2</Docks>
 <DockType>Rear load</DockType>
 <WaterAccess>Y</WaterAccess>
 <RailAccess>N</RailAccess>
 <Parking>Street</Parking>
 <VClearance>10ft</VClearance>
 M/Warehouse>'
) WHERE warehouse_id = 1;

XMLTYPE() 함수의 createXML() 메소드를 호출하여 지정된 칼럼에 XML 문서를 저장한다.

 

SELECT order_date, customer_id, order_total
FROM orders
WHERE order_id = '2365';

ORDER_DATE	CUSTOMER_ID	ORDER_TOTAL
99/08/23	146	27455.3

같은 주문 번호 2365에 대해 라인 항목의 정보를 보면 12개의 라인에 각각 주문된 제품의 단가와 수량 정보를 저장하고 있습니다. 즉 주문이 발생하면 각 제품을 각각의 라인에서 처리하고 있으며, 그 정보를 ORDER_ITEMS 테이블에서 관리하고 있음을 알 수 있다.

SELECT *
FROM order_items
WHERE order_id = '2365'
ORDER BY line_item_id;

이들 전체 라인을 대상으로 수량과 단가를 곱하여 각 라인마다 제품에 대한 주문 금액을 얻고 이를 합하면 발생한 주문번호 2365번에 대한 총 주문금액과 위 주문 테이블 주문금액의 합 칼럼에 해당 주문에 대한 결과가 같음을 알 수 있다.

SELECT SUM (unit_price * quantity)
FROM order_items
WHERE order_id = '2365';

새로운 주문이 발생하면 주문 아이디(:new.order_id)값이 ORDER_ITEMS 테이블의 order_id 칼럼의 값과 같다면(기존 주문 아이디와 같으면) 주문의 라인 항목(LINE_ITEM_ID)의 최대갑을 얻어, 그 값을 동일 주문의 새로운 라인 항목 값으로 설정하도록 지정하고있다. 즉 추가 주문에 대해서 기존 라인 항목에 새로운 라인을 추가하여 주문을 처리하고 있음을 보여주고 있다.

CREATE OR REPLACE TRIGGER insert_ord_line
BEFORE INSERT ON order_items
FOR EACH ROW
DECLARE
    new_line number;
BEGIN
    SELECT (NVL(MAX(line_item_id),0)+1) 
    INTO new_line
    FROM order_items
    WHERE order_id = :new.order_id;
    
    :new.line_item_id := new_line
END;

테이블 생성 시 사용된 데이터 타입을 통해 Oracle에서 제공하는 새로운 타입들을 접할 수 있다.

CREATE TABLE orders(
 order_id NUMBER(12)
,order_date TIMESTAMP WITH LOCAL TIME ZONE
..
)

CREATE TABLE product_information(
 product_id NUMBER(6)
..
,warranty_period INTERVAL YEAR TO MONTH
..
)

'TIMESTAMP WITH LOCAL TIME ZONE'은 현지 지역 시간을 기준으로 시간 정보를 참조할 때 사용한다.

CREATE TABLE TEST_TIMESTAMP (
 T1 TIMESTAMP WITH TIME ZONE
,T2 TIMESTAMP WITH LOCAL TIME ZONE
);

INSERT INTO TEST_TIMESTAMP VALUES(SYSDATE, SYSDATE);

T1	T2
06/12/02 20:48:56.000000 +09:00	06/12/02 20:48:56.000000

 

INTERVAL YEAR TO MONTH는 정의된 년과 월만큼의 간격을 나타낸다.

아래 경우 제품의 무상 보증기간을 나타낼 때 이 데이터 타입을 사용한다.

SELECT product_id, warranty_period
FROM product_information
WHERE product_id = '2252';

PRODUCT_ID	WARRANTY_PERIOD
2252	+01-06

NVARCHAR2 타입은 다양한 언어의 무나 값을 저장할 수 있으며, 최대 크기는 4,000Bytes 이다.

실제 해당 테이블에 여러 국가 언어로 설정된 제품의 이름들을 읽어온다.

CREATE TABLE product_descriptions(
 product_id NUMBER(6)
,language_id VARCHAR2(3)
,translated_name NVARCHAR2(50)
..
)

CREATE SYNONYM countries FOR hr.countries;

SYNONYM의 사전적인 의미는 동의어 내지 유사어를 의미한다.

 

문법

CREATE SYNONYM 동의어이름 FOR 소유자이름.객체이름;

 

즉 다른 소유자의 테이블에 접근할 수 있는 권한을 가지고 있다면 질의할때 매번 '소유자이름.테이블이름'으로 접근해야 하는 번거로움이 있다. SYNONYM을 설정하면 마치 자기 소유의 테이블 처럼 접근할 수 있다.

CONN oe/oe
DESC countriess --라고 해도 DESC hr.countries 와 같은 결과를 얻을 수 있다.