Transcript 제 4 장 객체-관계 SQL (Object

제 4 장 객체-관계 SQL
(Object-Relational SQL)
4.1 개요
4.2 객체와 테이블
4.3 컬렉션(collection) 타입
4.4 절차적 SQL, 사용자 정의 함수와 메소드
4.5 외부 함수와 패키지 사용자 정의 타입
1
4.1 개요
 ORSQL
– 정의
• 대부분의 ORDBMS SQL이 가지는 공통적인 특징을 포함하는 SQL 언어
– 기능
• 복합 구조화 타입(composite structured type) 지원
 복합 구조화 타입
– 여러 타입의 데이터 집합인 객체(object) 또는 테이블 내에 행(row)와
같은 데이터 정의
– 종류
• ORACLE : 객체 타입(object type)
• INFORMIX : 행 타입(row type)
• DB2 UDB : 사용자 정의 타입(user-defined type)
Page 2
4.1 개요
 객체-관계 모델의 특징
– 컬럼값으로 컬렉션(collection) 타입의 데이터 허용
• 집합(set)이나 배열(array)
– 내포 테이블(table nesting) 허용
• 단일 컬럼값이 또 다른 테이블
– 관계형 모델의 제 1 정규형 규칙 위반
• 다중값이나 구조화된 데이터값을 갖는 컬럼 허용
 메소드(method)
– 하나의 특별한 객체를 대상으로 호출되는 함수
– 객체 지향 환경에서 객체의 데이터에 접근하는 유일한 방법
– 단, ORDBMS의 모든 객체 데이터는 ORSQL을 이용해 접근 가능
 사용자 정의 함수(user-defined function, UDF)
– 특정 객체에 속해 있지 않지만 다수의 서로 다른 객체들을 처리
Page 3
4.1 개요
 객체 지향 데이터베이스 시스템(OODBS)
– 1980년대 중반부터 개발 시작
– 객체 지향 프로그래밍 언어와 상호 작용 가능
– 배열이나 클래스와 같은 복합 객체 사용 가능
 객체-관계 모델 개발 동기
– 제 1 정규형의 불필요성 부각
– 내포 테이블을 지원하는 모델에 대한 연구
– 기존의 관계형 애플리케이션에 대한 재구축 지양
 객체-관계 데이터베이스 시스템 개발의 역사
– POSTGRES
• 초기 객체-관계 모델의 가장 중요한 프로토타입
 버클리 대학의 Michael Stonebraker
• 1996년 INFORMIX에 인수된 후 INFORMIX ORDBMS의 효시
Page 4
4.2 객체와 테이블
ORACLE
 ORACLE에서의 객체 타입(Object Type)
– 다양한 타입의 애트리뷰트들로 구성된 객체 데이터 정의
– Create Object Type 구문을 이용해 생성
--- 객체 타입 생성 및 삭제 구문 형식 --CREATE TYPE typename AS OBJECT
(attrname datatype { , attrname datatype …});
DROP TYPE typename;
[예제 4.1] 객체 타입 생성 예제
“사람의 이름을 나타내는 객체 타입 name_t를 선언해보자. 이름은 first name,
middle name, last name으로 구성되어 있다.”
create type name_t as object
(
fname varchar(10),
mi
char(2),
lname varchar(10),
);
/
 ORACLE의 인터렉티브 SQL 환경인 SQL*Plus에서 사용되도록 작성
Page 5
4.2 객체와 테이블
ORACLE
 객체 생성자(object constructor)
– 객체_타입_이름( ) 형식
– 객체 타입을 구성하는 애트리뷰트의 값으로 객체 생성
형식 : typename(argument [,argument ...])
[예제 4.2] 객체 타입의 컬럼을 가지는 테이블 생성 예
“세 개의 컬럼(column) : tid(정수형으로 선언된 선생님 식별자), tname(name_t 타입으로 선언된
선생님 이름), 그리고 room(정수형으로 선언된 선생님 연구실 번호)로 구성된 teachers 테이블을
생성하라.”
create table teachers
(
tid
int,
tname
name_t,
room
int
);
 teachers 테이블에 튜플을 삽입하는 구문 예
insert into teachers values (1234, name_t('김', '은', '영'), 120);
Page 6
4.2 객체와 테이블
ORACLE
 Select 구문에서 객체의 애트리뷰트를 접근하는 경우
– “dot” 형식 사용
– 테이블의 별칭(alias) 사용
< teachers 테이블의 컬럼에 대한 기본적인 SQL 질의문 예제>
“123번 연구실을 사용하는 선생님의 ID와 이름을 검색하라.”
select t.tid, t.tname.fname, t.tname.mi, t.tname.lname
from teachers t where t.room = 123;
 올바르지 않은 표현
select tid, tname.fname, tname.mi, tname.lname
from teachers where room = 123; **실행 안됨.
Page 7
4.2 객체와 테이블
ORACLE
 종속성(dependency)
– person_t 타입은 name_t 타입에 종속적
– 타입을 삭제하기 전 모든 종속되는 타입을 삭제해야 함
[예제 4.3] 객체 타입의 애트리뷰트를 가지는 객체 타입을 정의한 예
“개인의 주민등록번호, 이름, 나이 정보를 포함하는 객체 타입 person_t 를 생성하라.”
drop type person_t;
create type person_t as object (
ssno
int,
pname
name_t,
age
int
);
 기존에 정의된 person_t 타입을 제거하기 위해 Drop Type 구문 사용
Page 8
4.2 객체와 테이블
ORACLE
 객체 테이블(object table)
– 행이 객체 타입인 테이블
– Create Object Table 구문 이용해 생성
• 객체 타입과 기본키만을 정의
--- 객체 테이블 생성 및 삭제 구문 형식 --CREATE TABLE tablename OF typename
( [attrname NOT NULL] {, attrname NOT NULL …}
[,PRIMARY KEY (attrname {,attrname …})]);
DROP TABLE tablename;
Page 9
4.2 객체와 테이블
ORACLE
 객체 테이블(cont’d)
[예제 4.4] 객체 테이블 생성의 예
“person_t 타입의 객체[행(row)]를 포함하는 people 객체 테이블을 생성하라.
주민등록번호를 기본키로 사용하라.”
create table people of person_t
(
primary key(ssno)
);
행 객체(row object) : 테이블의 행에 해당하는 객체
컬럼 객체(column object) : 테이블의 컬럼에 해당하는 객체
people 테이블의 예제는 그림 4.2를 참조
Page 10
4.2 객체와 테이블
ORACLE
 전체 행 객체를 표현하는 방법
– Select 절에서 “*” 이용
< 객체 테이블의 전체 행 객체를 출력하는 질의 예 – * 사용>
“people 테이블에서 나이가 25살 이상인 사람들에 대한 모든 정보를 검색하라.”
select * from people p where p.age > 25;
ssno
--------------
pname(lname, fname, mi)
----------------------------------
age
------
245882134
023894455
name_t(‘김’, ‘경’, ‘은’)
name_t(‘박’, ‘경’, ‘조’)
59
30
그림 4.3 Select * 질의문의 결과
Page 11
4.2 객체와 테이블
ORACLE
 전체 행 객체를 표현하는 방법 (cont’d)
– VALUE() 형식 이용
< 객체 테이블의 전체 행 객체를 출력하는 질의 예 – Value() 사용>
“people 테이블에서 나이가 25살 이상인 사람들에 대한 모든 정보를 검색하라.”
select value(p) from people p where age > 25;
Value(p)
--------------
(ssno,
pname(lname, fname, mi)
--------------- -------------------------------
age
------
person_t
person_t
(245882134 , name_t(‘김’, ‘경’, ‘은’),
(023894455 , name_t(‘박’, ‘경’, ‘조’),
59
30
그림 4.4 Select value(p) 질의문의 결과
Page 12
4.2 객체와 테이블
ORACLE
 객체 생성자 형식 사용 예
– 애트리뷰트값으로부터 객체를 생성하기 위해
– 객체의 출력 형식 위해
[예제 4.5] 객체 생성자의 또 다른 사용 예제
“박경조라는 이름을 가지는 주민등록번호와 나이를 포함하는 person_t 객체의
값을 검색하라.”
select value(p) from people p
where p.pname = name_t('박','경','조');
Page 13
4.2 객체와 테이블
ORACLE
 테이블 별칭과 관련한 질의 예
– Select p.pname from people p;
• 테이블 별칭 사용, 공식적으로 맞음, 동작함
– Select pname from people;
• 테이블 별칭 없음, 공식적으로 틀림, 동작함
– Select pname.fname from people;
• 최상위 애트리뷰트 아님, 동작하지 않음, 별칭을 사용해야 함
– Select people.pname.fname from people;
• 동작하지 않음, 별칭을 사용해야 함
[예제 4.6] 컬럼 객체의 애트리뷰트에 접근하는 질의 예
“first name이 K로 시작하고 나이가 50세 이상인 사람들의 이름과 나이를 검색하라.”
select p.pname, p.age from people p
where p.pname.fname like 'K%' and p.age > 50;
Page 14
4.2 객체와 테이블
ORACLE
 Insert 문과 Update 문
– 새로운 행 객체를 생성하기 위해 객체 생성자를 이용해 값을 삽입
[예제 4.7] 행 객체의 삽입 및 갱신 예
“객체 타입 name_t의 scientists 테이블을 생성하고, 그 안에 이은범 행 객체를 삽입하라.”
create table scientists of name_t;
insert into scientists valus ( '이', '은', '범' );
“people 테이블에 ‘이’씨 성의 이름을 가지는 객체를 삽입하라.”
insert into people values (123441998, name_t('이', '은', '범') , 100);
“people 테이블 안의 모든 name_t 객체를 scientists 테이블로 삽입하라.”
insert into scientists select p.pname from people p;
“scientists 테이블 내의 이은범이란 이름을 박경범으로 교체하라.”
update scientists s set s = name_t('박' , '경' , '범')
where value(s) = name_t('이' , '은' , '범');
Page 15
4.2 객체와 테이블
ORACLE
 Insert 문과 Update 문(cont’d)
[예제 4.8] 널(null)값을 이용한 삽입 및 갱신 예
“ssno 애트리뷰트값이 321341223이고, pname과 age 애트리뷰트값이 널값인 행을 people
테이블에 삽입하라.”
insert into people values (321341223, null, null);
insert into people (ssno) values (32134223);
“마지막으로 삽입된 행의 널값을 가지는 last name과 first name을 ‘오’, ‘규’로 바꾼다.”
update people p set p.name = nam_t( '오' , null, '규' )
where ssno = 321341223;
“바로 위에서 삽입한 행에 middle name 애트리뷰트값을 널값에서 ‘병’으로 교체한다.”
update people p set p.pname.mi = '병' where ssno =
321341223;
“주민등록번호 321341223을 1332341223으로 교체하라.”
update people p set p = person_t(332341223,
name_t( '이' , '애', '규' ), 55) where ssno = 321341223;
Page 16
4.2 객체와 테이블
ORACLE
 REF 내부 데이터 타입
– 다른 객체에 대한 참조 정의
• 테이블의 컬럼(또는 애트리뷰트)은 다른 객체 테이블의 행 객체 참조 가능
• 컬럼 객체에 대한 참조는 불가능
[예제 4.9] REF 데이터 타입 사용 예
“객체 테이블 customers, agents, products, orders를 사용하여 그림 2.2의 CAP 데이터베이스를
생성하라. 각 테이블에서 모든 예전 컬럼값은 그대로 유지하고, orders 테이블 내에 각 주문이
customer, agent, product 행 객체를 참조하도록 REF를 추가한다.”
create
create
create
create
(
);
type
type
type
type
customer_t as object (...);
agent_t as object (...);
product_t as object (...);
order_t as object
ordno
int,
month
char(3),
cid
char(4),
aid
char(3),
pid
char(3),
qty
int,
wons
double precision,
ordcust ref customer_t,
ordagent
ref agent_t,
ordprod ref product_t
Page 17
4.2 객체와 테이블
ORACLE
 REF 내부 데이터 타입(cont’d)
[예제 4.9] REF 데이터 타입 사용 예(cont’d)
create table customers of customer_t (primary key (cid));
create table products of product_t (primary key (pid));
create table agents of agent_t (primary key (aid));
create table orders of order_t
(
primary key (ordno),
scope for (ordcust) is customers,
scope for (ordagent) is agents,
vscope for (ordprod) is products
);
Page 18
4.2 객체와 테이블
ORACLE
 REF 내부 데이터 타입(cont’d)
– 복잡한 조인 질의 대신 간단한 질의 가능
– 결과값이 간단하고 좀 더 효과적인 접근 가능
– 셀프 조인을 이용하는 질의문의 경우에는 질의를 간소화 시킬 수 없음
[예제 4.10] REF 데이터 타입을 이용한 간단한 질의문의 예
“[예제 3.4]에서 처럼 판매자를 통해 주문하는 모든 고객의 이름과 판매자 이름
쌍 (cname, aname)을 검색하라.”
select distinct o.ordcust.cname, o.ordagent.aname from orders o;
[예제 4.11] 셀프 조인이 필요한 질의문의 예
“[예제 3.7]과 같이 최소한 두 명 이상의 고객에게 주문을 받은 제품의 pid값을 검색하라.”
select distinct x1.pid
from orders x1, orders x2
where x1.pid = x2.pid and x1.ordcust < x2.ordcust;
Page 19
4.2 객체와 테이블
ORACLE
 REF 내부 데이터 타입(cont’d)
– REF( ) 함수 : 객체를 REF 타입 값으로 변환
– DEREF( ) 함수 : REF 값을 통해 참조되는 객체 반환
[예제 4.12] REF() 함수를 이용한 질의문의 예
“[예제 3.20]과 같이 판매자(agent) 'a05'를 통해 주문하지 않는 고객(customers)의 고객 이름
(cname)을 검색하라.”
select c.cname from customers c
where not exists (select * from orders x
where x.ordcust = ref(c) and x.aid = 'a05');
[예제 4.13]
“[예제 3.25]에서처럼, 부산에 살고 있는 모든 판매자(agent)에게 주문한 고객(customer)의
고객번호(cid) 값을 검색하라.”
select c.cid from customers c where
not exists (select * from agents a
where a.city = '부산' and not exists (select * from orders x
where x.ordcust = ref(c) and x.ordagent = ref(a)));
Page 20
4.2 객체와 테이블
ORACLE
 REF 내부 데이터 타입(cont’d)
– dangling REF
• 삭제 등의 이유로 더 이상 참조할 수 없는 행을 참조하는 REF
[예제 4.14]
“주문 테이블 내의 REF 값이 dangling REF인 모든 고객의 고객 번호를 검색하라.”
select o.cid from orders o where o.orecust is dangling;
또 다른 질의 표현
select o.cid from orders o where o.ordcust <>
(select ref(c) from customers c whrer c.cid = o.cid);
 REF 종속성
– 순환적 참조 관계를 가지는 객체 타입 삭제 시
• DROP TYPE typename FORCE;
Page 21
4.2 객체와 테이블
ORACLE
 REF 내부 데이터 타입(cont’d)
– 동일한 타입의 또 다른 객체를 참조하는 REF 사용 가능
[예제 4.15]
“경찰관(police officer) 객체를 위한 타입을 생성하라. 경찰관의 파트너 애트리뷰트는
또 다른 경찰관 객체에 대해 REF 값으로 표현한다. 또한, 경찰관들을 위한 객체 테이블을
생성하라.”
create type police_officer_t as object (
pol_person
person_t,
badge_number
interger,
partner
ref police_officer_t );
create table police_officers of police_officer_t(
primary key (badge_number),
scope for (partner) is police_officers );
[예제 4.16]
“60세 이상의 파트너를 가지고 있는 모든 경찰관의 last name을 검색하라.”
select value(p), deref(p.pertner) from police_officers p;
Page 22
4.2 객체와 테이블
ORACLE
[예제 4.17] REF 참조값 갱신 예
“orders 테이블 내의 현재 REF 값을 customers, agents, products 테이블에 대한 적합한 REF 참조
으로 변경하라.”
update orders o set
ordcust = ( select ref(c) from customers c where c.cid = o.cid),
ordagent = ( select ref(a) from agents a whrer a.aid = o.aid),
ordprod = ( select ref(p) from products p where p.pid = o.pid);
[예제 4.18] REF 참조값 갱신 예
“people 테이블에서 033224445 주민등록번호를 가지는 사람에 대한 정보를 배지(badge)번호
1000과 함께 [예제 4.15]의 테이블에 삽입하라. 새로운 경찰관의 파트너는 이미 police_officers
테이블 내에 990번 배지 번호를 가진 경관으로 가정한다.”
insert into police_officers
select value(p), 1000, ref(p0) from people p, police_officers p0
where p.ssno = 033224445 and p0.badge_number = 990;
update police_officers set p.partner = ( select ref(p0)
from police_officers p0
where p0.badge_number = 1000 ) where badge_number = 990;
Page 23
4.2 객체와 테이블
INFORMIX
 INFORMIX에서의 행 타입
– 필드(field)로 구성된 객체 데이터 정의
– ORACLE의 객체 타입과 유사
– Create Row Type 구문을 이용해 생성
• NOT NULL인 필드값 정의
--- 행 타입 생성 및 삭제 구문 형식 --CREATE ROW TYPE rowtype
(fieldname datatype [NOT NULL] {, fieldname datatype [NOT NULL] …});
DROP ROW TYPE rowtype RESTRICT;
[예제 4.20] 행 타입 생성 예제
“개인의 주민등록번호(ssno), 이름(pname), 나이(age)를 포함하는 person_t 행 타입을
생성하라. 먼저, 이미 존재하는 같은 이름의 행 타입을 삭제하라.”
drop row type person_t restrict;
create row type person_t
(
ssno
int,
pname
name_t,
age
int
);
Page 24
4.2 객체와 테이블
INFORMIX
 타입 정의된 테이블(typed table)
– 행이 행 타입으로 선언되어 있는 테이블
– ORACLE의 객체 테이블과 유사
– Create Typed Table 구문을 이용해 생성
--- 타입 정의된 테이블 생성 및 삭제 구문 형식 ---
CREATE TABLE tablename OF TYPE rowtype
( [ PRIMARY KEY ( column {,column …})]);
DROP TABLE tablename;
[예제 4.21] 타입 정의된 테이블 생성 예제
“person_t 타입의 객체로 이루어진 people 테이블을 생성하라. 기본 키는 주민등록번호를
사용한다.”
create table people of type person_t
(
primary key(ssno)
);
Page 25
4.2 객체와 테이블
INFORMIX
 Select 구문에서 객체의 애트리뷰트를 접근하는 경우
– “dot” 표현 이용
– 반드시 테이블 별칭을 이용할 필요 없음
< people 테이블의 컬럼에 대한 기본적인 SQL 질의문 예제>
“주민등록번호가 123550123인 사람의 나이를 검색하라.”
select age from people where ssno = 123550123;
“나이가 25살보다 많은 사람의 이름을 검색하라.”
select pname from people where age > 25;
select q.pname from people q where q.age > 25;
[예제 4.23] 컬럼 내의 필드에 접근하는 질의문의 예
“first name이 "K"로 시작하고, 50살이 넘은 모든 사람들의 이름과 나이를 검색하라.”
select pname, age from people
where pname.fname like 'K%' and age > 50;
Page 26
4.2 객체와 테이블
INFORMIX
 행 생성자(row constructor)
– ROW( ) 형식
– 생성 직후 반드시 적절한 행 타입을 cast( )를 이용하여 정의해야 함
타입이 정의되어 있지 않은 행 객체 생성자
ROW(expression {, expression …})
 타입이 정의되어 있는 행 객체 생성자
CAST ( ROW ( expression {, expression ... } ) AS rowtype )
[예제 4.22] 행 생성자와 cast( )를 이용한 질의문의 예
“박경조라는 이름을 가진 person_t 객체를 검색하라.”
select p from people p
where p.pname = cast(row('박' , '경' , '조') as name_t);
cast()는 row_expression::typename 형태로 바꾸어 표현 가능
select value(p) from people p
where p.pname = row('박' , '경' , '조')::name_t;
Page 27
INFORMIX
4.2 객체와 테이블
 전체 행 객체를 표현하는 방법
– Select 절에서 “*” 이용
< 타입 정의된 테이블에서 전체 행 객체를 출력하는 질의 예 – * 사용>
“people 테이블에서 나이가 25살 이상인 사람들에 대한 모든 정보를 검색하라.”
select * from people p where p.age > 25;
ssno
--------------
pname
----------------------------------
age
------
245882134
023894455
row(‘김’, ‘경’, ‘은’)
row(‘박’, ‘경’, ‘조’)
59
30
그림 4.7 Select * 질의문의 결과
Page 28
4.2 객체와 테이블
INFORMIX
 전체 행 객체를 표현하는 방법(cont’d)
– 테이블의 별칭을 이용하는 방법
< 객체 테이블의 전체 행 객체를 출력하는 질의 예 –별칭사용>
“people 테이블에서 나이가 25살 이상인 사람들에 대한 모든 정보를 검색하라.”
select p from people p where age > 25;
p
row(245882134 , row(‘김’, ‘경’, ‘은’), 59
row(023894455 , row(‘박’, ‘경’, ‘조’), 30
그림 4.9 테이블 별칭을 이용한 질의문의 결과
Page 29
4.2 객체와 테이블
INFORMIX
 행 생성자를 이용한 삽입 및 갱신
[예제 4.24] 행 객체의 삽입 및 갱신 예
“주민등록번호가 123441998이고 100살인 '이은범'을 위한 행 객체를 people 테이블에 삽입하라.”
insert into people values (123441998, cast(row('이', '은', '범') as
name_t), 100);
“주민등록번호는 있지만 이름과 나이는 널 값을 가지는 행 객체를 삽입하라.”
insert into people values (321341223, null, null);
또는 insert into people (ssno) values (321341223);
“’오', '규'를 마지막으로 삽입한 행 객체의 null인 이름을 '오', '규'로 변경하라.”
update people set pname = cast(row('오', null, '규') as
name_t) where ssno = 321341224;
“주민등록번호가 321341224인 사람의 middle name을 널 값에서 '병'으로 교체하라..”
update people set pname = cast(row(pname.lname, pname.fname, '병')
as name_t) where ssno = 321341224;
Page 30
4.2 객체와 테이블
INFORMIX
 행 생성자를 이용한 삽입 및 갱신(cont’d)
[예제 4.24] 행 객체의 삽입 및 갱신 예 (cont’d)
“name_t 타입의 scientists 테이블을 생성하고, people 테이블 내에 있는 이름들을 scientists 테이블에
삽입하라.”
create table scientists of type name_t;
insert into scientists
select pname.fname, pname.minitial, pname.lname from people;
Page 31
4.2 객체와 테이블
INFORMIX
 타입 상속(inheritance)
– 서브 타입(subtype)
• 상위 타입(supertype)의 메소드와 데이터 상속 가능
• 새로운 메소드나 데이터 추가 가능
person_t 의 서브 타입인 employee_t 정의
employee_t의 서브 타입인 employee_t 정의
create type emploee_t
(
eid
int,
salary double precision,
mgrid
int
)
under person_t;
create type manager_t
(
budget
double precision,
groupname
varchar(30)
)
under emplyee_t;
상속 개념을 적용한 테이블 선언
create table people of type person_t;
create table employees of type employee_t under people;
create table managers of type manager_t under employees;
Page 32
4.2 객체와 테이블
INFORMIX
 테이블 상속과 관련한 SQL의 기능
manager_t 타입의 테이블을 독립적으로 생성하는 것
vs.
employees 테이블의 서브 테이블로서 managers 테이블을 생성하는 것
select pname.fname, pname.lname, eid from employees
where salary > 50000000;
select pname.fname, pname.lname, eid from managers
where salary > 50000000;
select pname.fname, pname.lname, eid from only (employees)
where salary > 50000000;
Page 33
4.2 객체와 테이블
 ORACLE vs. INFORMIX
ORACLE
INFORMIX
 복합 객체 (행) 타입 정의 가능
 일반적인 데이터 타입처럼 객체 타입 사용 가능
유사성
 객체 내에 또 다른 객체를 내포 가능
 다중 내포 허용, “dot” 표현을 이용해 객체 내 애트리뷰트(필드)에 접근 가능
 하나의 객체가 테이블 내에서 하나의 행 역할
 객체 데이터의 갭슐화 지원하지 않음
차이점
 REF 데이터 타입 사용 가능
 REF 데이터 타입 사용 불가능
 타입의 계층과 상속 허용하지 않음
 타입의 계층과 상속 허용
Page 34
4.3 컬렉션 타입
 컬렉션(collection) 타입
– 동일한 타입의 요소(element)로 구성된 컬렉션 표현
• 요소 타입(element type) : 일반적인 데이터 타입이나 객체 타입
– ORACLE
• 테이블 타입, 배열 타입
• 요소 타입으로 컬렉션 타입은 사용 불가
– INFORMIX
• 집합, 리스트
– 다중 값(multi-value)을 갖는 애트리뷰트 표현
사원번호(eid)
사원명(ename)
직위(position)
부양가족(dependents)
e001
홍정화
관리인
홍정수
홍수연
e002
김수창
감독관
김영길
e003
최종민
관리인
최수영
최길수
최창훈
그림 4.11 다중값 애트리뷰트 부양가족(dependents)을 갖는 사원(employees) 테이블
Page 35
4.3 컬렉션 타입
ORACLE
 ORACLE에서의 컬렉션 타입
– 테이블 타입
--- Nested Table 구문을 가지는 Create Relational Table 문 --CREATE TABLE tablename (columnname datatype [NOT NULL]
{, columnname datatype [NOT NULL] . . .}
[, PRIMARY KEY (columnname {, columnname . . .})])
[NESTED TABLE columnname STORE AS tablename
{, NESTED TABLE columnname STORE AS tablename . . .}];
Page 36
4.3 컬렉션 타입
ORACLE
[예제 4.25] 테이블 타입 생성 및 테이블 타입 애트리뷰트 정의 예
“person_t 타입의 객체로 이루어진 테이블을 표현하는 dependent_t 테이블 타입을 생성하라..”
create type dependents_t as table of person_t;
“eid, eperson, dependents 세 가지 컬럼을 가지는 사원(employees) 테이블을 생성하고, dependent
컬럼은 다중 값을 갖도록 정의하라. eid를 기본키로 사용한다.”
create table employees
(
eid
int,
eperson
person_t,
dependents
dependents_t,
primary key
(eid)
) nested table dependents store as dependents_tab;
 위의 Create Table 구문을 통해 사원(employees) 테이블과 dependents_tab 테이블 생성
Page 37
4.3 컬렉션 타입
ORACLE
 내포 테이블
– 테이블 각 행의 컬럼에 존재하는 개념적인 테이블
– 다단계 내포 가능
– 자식 테이블(내포된 테이블) / 부모 테이블(테이블을 포함하는 테이블)
eid
eperson
dependents
101
person_t(123897766, name_t(‘홍’, ‘정’, ‘화’), 45)
person_t(322456776, name_t
(‘홍’, ‘정’, ‘수’), 8)
person_t(123822332, name_t
(‘홍’, ‘수’, ‘연’), 12)
102
person_t(432112233, name_t(‘김’, ‘수’, ‘창’), 32)
person_t(565534555, name_t
(‘김’, ‘영’, ‘길’), 3)
그림 4.12 내포 테이블 타입의 dependents 컬럼을 갖는 사원 테이블
Page 38
4.3 컬렉션 타입
ORACLE
 내포 테이블에 대한 질의
– 자식 테이블에 대한 질의는 반드시 부모 테이블을 통해야 함
• 단, TABLE( ) 형식을 이용하면 내포된 테이블에 직접 질의 가능
– 내포된 테이블 간의 “=“ 연산을 이용한 비교 연산 지원하지 않음
[예제 4.26] 내포된 테이블에 대한 질의문의 예
“사원 테이블에서 사원번호(eid)가 '101' 인 사원의 모든 부양가족 정보를 갖는 내포된 테이블을
검색하라.”
select dependents from employees where eid = 101;
dependents ( ssno,pname (fname,minitial,lname), age)
-----------------------------------------------------------------dependents_t ( person_t ( 322456776, name_t ( '홍' , '정' , '수' ), 8 ),
person_t ( 123822332, name_t ( '홍' , '수' , '연' ), 12))
그림 4.14 [예제 4.26]의 결과 테이블
[예제 4.27] 내포된 테이블에 대한 질의문의 예
“사원들의 모든 부양가족(dependents)에 대한 내포된 테이블을 검색하라.”
select dependents from employees;
Page 39
4.3 컬렉션 타입
ORACLE
 TABLE( ) 구문
– 형식 : FROM TABLE(collection_expr)
– 가장 바깥쪽의 Select 문의 FROM 절에서 사용할 수 없음
[예제 4.28] TABLE( )을 이용한 질의문의 예
“부양가족이 6명 이상인 경우의 사원에 대한 사원번호(eid)를 검색하는 질의어를 구성하라.”
select eid from employees e
where 6 < (select count(*) from table(e.dependents));
[예제 4.29] TABLE( )을 이용한 질의문의 예
“주민등록번호가 3451112222인 부양가족(dependents)을 가지는 사원의 사원번호(eid)를
검색하라.”
select eid from employees e
where 3451112222 in (select d.ssno from table(e.dependents) d);
 올바르지 못한 표현
select ssno from table(employees.dependents);
Page 40
4.3 컬렉션 타입
ORACLE
[예제 4.30] TABLE( )을 이용한 질의문의 예
“사원번호가 101인 사원의 부양가족이 몇 명인지 검색하라.”
select count(*) from
table (select e.dependents from employees e where e.eid = 101);
올바르지 못한 표현
select count(*) from
(select e.dependents from employees e where e.eid = 101);
- - ** UNEXPECTED RESULT
select count(*) from
(select table(e.dependents) from employees e where e.eid = 101);
- - ** BAD SYNTAX
select count(e.dependents) from employees e where e.eid = 101;
[예제 4.31] TABLE( )을 이용한 질의문의 예
“[예제 4.30]에서 부양가족(dependents)의 인원수를 계산하는 대신에 부양가족 모두의
주민등록번호를 검색하는 질의문을 작성하라.”
select d.ssno from
table(select e.dependents from employees e where e.eid = 101) d;
Page 41
4.3 컬렉션 타입
ORACLE
 테이블 안에 존재하는 테이블 검색
– 테이블 프로덕트
• 테이블의 각 행과 그 행의 컬렉션 컬럼값을 테이블로 변환했을 때의 각 행을 조합
사원(employees) 테이블과 부양가족(dependents)에 대한 table product
select e.eid,d.ssno from employees e,table(e.dependents) d;
부양가족이 없는 사원을 결과값으로 반환하기 위한 table product
select e.eid,d.ssno from employees e,table(e.dependents)(+)d;
Page 42
4.3 컬렉션 타입
ORACLE
 테이블 안에 존재하는 테이블 검색(cont’d)
– 내포형 커서
• CURSOR() : 외곽 Select문에서 반복처리의 대상이 되는 각 행에 존재하는 내포된
테이블에 대한 반복처리제어
cursor expression : := cursor(Subquery)
[예제 4.32] 테이블 내의 테이블을 검색하는 질의문의 예
“사원 테이블에서 모든 사원의 사원번호를 검색하고, 각 사원의 부양가족 중 16세 이하인
부양가족의 주민등록번호를 검색하라.”
테이블 프로덕트를 이용한 질의문
 검색 결과는 그림 4.16 참조
select e.eid d.ssno as dep_sso from employees e, table(e.dependents) d
where d.age < 16;
내포형 커서를 이용한 질의문
 검색 결과는 그림 4.17 참조
select e.eid, cursor(select d.ssno as dep_ddno
from table(e.dependents) d where d.age < 16) dep_tab
from employees e;
Page 43
4.3 컬렉션 타입
ORACLE
[예제 4.33] 테이블 내의 테이블을 검색하는 질의문의 예
“가장 나이가 많은 부양가족의 first name과 함께 사원번호를 검색하라.”
select eid, cursor(select d1.pname.fname from table
(employees.dependents) d1
where d1.age = (select max(d2.age) from
table(employees.dependents) d2)) from emplyees;
 TABLE() 형식만을 이용한 동일한 질의문 표현
select eid, d.pname.fname from employees e, table(e.dependents) d1
where d1.age = (select max(d2.age) from table(e.dependents) d2));
 올바르지 못한 표현
select fname from table(employees.dependents) d1
Page 44
4.3 컬렉션 타입
ORACLE
[예제 4.34] 테이블 내의 테이블을 검색하는 질의문의 예
“각각의 사원에 속해 있는 부양가족의 수를 검색하라. 결과 출력은 다중 행 형태가 되도록 하라.”
select eid, cursor(select count(*) from table(e.dependents))
from employees e;
select eid, (delect count(*) from table(e.dependents))
from employees e;
select eid, count(*) from employees e, table(e.dependents))
group by eid;
 전체 사원의 모든 부양가족의 수를 검색하라.
select count(*) from employees e, table(e.dependents);
Page 45
4.3 컬렉션 타입
ORACLE
 ORACLE에서의 컬렉션 타입
– 배열 타입
• VARRAY(varying-length array)로 선언
 배열의 이름, 구성요소의 타입, 최대 포함할 수 있는 구성요소의 개수 포함
• 구성요소사이에 순서 존재
[예제 4.35] 배열 타입의 선언 예
“간단한 전화번호부를 테이블로 생성하라. 개개인은 4개까지의 내선 번호를 가질 수 있다.
단, 내선번호의 순서가 중요하다.”
create type extensions_t as varray(4) of int;
create table phonebook
(
phperson
person_t,
extensions
extensions_t
);
Page 46
4.3 컬렉션 타입
ORACLE
phonebook
pherson
extensions
person_t(123897766, name_t(‘홍’, ‘정’, ‘화’), 45)
extensions_t(345,989)
person_t(432112233, name_t(‘김’, ‘수’, ‘창’), 32)
extensions_t(123)
그림 4.19 ORACLE에서 VARRAY타입의 컬럼을 가지는 phonebook 테이블
[예제 4.36] 간단한 질의문 예
“주민등록번호(ssno)가 123897766인 사람의 성(first name)과 내선 번호를 구하기 위한 VARRY
객체를 검색하라.”
select pb.phperson.fname, pb.extensions from phonebook pb
where pb.phperson.ssno = 123897766;
 검색 결과는 그림 4.20 참조
단, 배열의 구성요소에 대해서는 프로그래밍 언어를 이용하여야만 접근이 가능
Page 47
4.3 컬렉션 타입
ORACLE
[예제 4.37] VARRAY 값에 대해 TABLE() 연산을 이용한 질의문 예
“phonebook 테이블에서 주민등록번호가 123440011인 사람의 내선 번호 개수를 검색하라.”
select (select count(*) from table(pb.extensions)) from phonebook pb
where pb.person.ssno = 123440011;
 테이블 프로덕트를 이용한 표현
select count(*) from phonebook pb, table(pb.extensions)
where pb.phperson.ssno = 123440011;
[예제 4.38] VARRAY 값에 대해 TABLE() 연산을 이용한 질의문 예
“phonebook 테이블에서 내선 번호 104를 가지는 사람을 검색하라.”
select phperson from phonebook pb
where 104 in (select * from table(pb.extensions));
Page 48
4.3 컬렉션 타입
ORACLE
 CAST
– 하나의 컬렉션 타입에서 다른 컬렉션 타입으로, 또는 행들의 집합에서
컬렉션 타입으로의 변환
 행 집합(row set)
– 관계형 질의에 의해 반환되는 행들의 집합
collection_cast_expression : : =
CAST(colloction_valued_expr AS collection_type)
|CAST((collection_valued_subquery) AS collection_type)
|CAST(MULTISET(rowset_valued_subquery) AS collection_type)
 행 집합에 대한 CAST 예
“예제 3.32를 모든 부양가족의 주민등록번호를 컬렉션 타입으로 정의하도록 변경하라.”
create type ssno_set as table of int;
select e.eid, cast(multiset(select d.ssno from table(e.dependents) d
where d.age < 16) as ssno_set) as dep_ssno
from employees e;
Page 49
4.3 컬렉션 타입
ORACLE
 FROM TABLE(collection expression)
– 새로운 질의 구문 형식
– 컬렉션 표현을 행 집합으로 변환
 내포 테이블을 포함하는 질의 구문
tableref ::= {tablename | (rowset_valued_subquery)
| TABLE (collection_expr)} -- collection-valued subquery,
[corr_name] -- "AS" keyword not a allowed in ORACLE
etc.
그림 4.22 ORACLE의 tableref 구문
 컬렉션 표현
– collection-valued 서브 질의문
• 예) select e.dependents from employees e where eid = 101
– 컬럼이나 애트리뷰트 표현
• 예) e.dependents
– 컬렉션값으로 변환하기 위한 CAST 표현
– 컬렉션값을 반환하는 함수
Page 50
4.3 컬렉션 타입
ORACLE
 컬렉션 생성자
– 내포 테이블 생성자
– VARRAY 생성자
[예제 4.39] 컬렉션 생성자를 이용한 삽입과 갱신의 예
“그림 4.12에서처럼 사원(employees) 테이블에 '홍정화'와 관련한 사원 정보를 삽입하라.”
insert into employees values (101, person_t(123897766,
name_t('홍' , '정' , '화'), 45),
dependents_t(person_t(322456776,name_t('홍','정','수'), 8),
person_t(123822332, name_t('홍','수','연'),12)));
“그림 4.19에서 처럼 phonebook 테이블에 '홍정화'에 대한 정보를 삽입하라.”
insert into phonebook values (person_t(123897766,
name_t('홍' , '정' , '화'), 45), extensions_t(345,989));
“phonebook 테이블에서 '홍정화'의 내선 번호를 345와 999로 변경하라.”
update phonebook set extensions = extensions_t(345,999) where eid=101;
Page 51
4.3 컬렉션 타입
ORACLE
[예제 4.40] TABLE(nested_table_subquery) 구문을 이용한 질의문 예
“홍정화 사원의 부양가족에 새로 출생한 아이를 추가하라.”
insert into table(select e.dependents from emloyees e
where e.eperson.ssno = 123897766)
values (344559112, name_t('홍', '연', null), 0);
“모든 부양가족의 last name을 대문자로 바꾸어라”
update table(select e.dependents from employees e where e.eid = 101) d
set d.pname.lname = upper(d.pname.lname);
[예제 4.41] 서브 질의문을 이용한 삽입의 예
“people 테이블의 모든 행을 '홍정화'의 부양 가족(dependents) 테이블에 삽입하라.”
insert into table(select e.dependents from employees e
where e.eperson.ssno = 123897766)
select * from people;
Page 52
4.3 컬렉션 타입
INFORMIX
 INFORMIX에서의 컬렉션 타입
– 집합(set)
• 특별한 순서가 없는 동일한 타입의 구성 요소들의 컬렉션, 중복을 허용하지 않음
– 다중 집합(multiset)
• 중복 구성 요소 허용
– 리스트(list)
• 중복을 허용하는 순서를 가지는 구성 요소의 컬렉션
Page 53
4.3 컬렉션 타입
INFORMIX
 INFORMIX에서의 집합
[예제 4.42] 집합 타입의 컬럼을 포함하는 테이블 생성 예
“int 타입의 eid 컬럼, person_t행 타입의 eperson 컬럼, person_t타입의 구성요소 SET을 포함하는
부양가족 컬럼을 가지고 있는 사원 테이블을 관계 테이블 형태로 생성하라.”
create table employees
(
eid
eperson
dependents
);
int
person_t
set(person_t not null)
employees
eid
101
eperson
row(123897766,
row('홍', '정', '화'),45))
dependents
set{row(3224566776, row('홍','정','수
'),8),
row(123822332, row('홍', '수','연'),12)}
row(432112233,
set{row(565534555,row('김','영','길'),3)}
102
row('김', '수', '창'),32)
그림 4.23 person_t 행 타입의 구성요소로 이루어진 SET을 포함하는 부양가족 컬럼을
포함하는 employees 테이블
Page 54
4.3 컬렉션 타입
INFORMIX
[예제 4.43] 집합 타입의 컬럼을 이용한 검색 질의문의 예
“사원 번호가 101인 사원의 모든 부양가족(dependents) 집합을 검색하라.”
select dependents from employees where eid = 101;
 검색 결과는 그림 4.24 참조
[예제 4.44] cardinality( ) 를 이용한 집합의 구성요소 개수 계산 예
“사원 번호가 101인 사원의 부양가족(dependents)의 인원수를 검색하라.”
select cardinality(dependents) from employees where eid =101;
“부양가족(dependents)수가 6명 이상인 사원의 사원 번호를 검색하라.”
select eid from employees
where cardinality(employees. dependents) > 6;
[예제 4.45] “ = ” 을 이용한 질의문 예
“동일한 부양가족(dependents) 집합을 가지는 서로 다른 모든 사원(employees)을 검색하라.
검색된 사원들의 쌍을 각각 한번씩 만 출력하라.”
select e1.eid, e2.eid from employees e1, employees e2
where e1.eid < e2.eid and e1.dependents = e2.dependents;
Page 55
4.3 컬렉션 타입
INFORMIX
 IN 키워드
– 컬렉션 내에 특별한 구성요소를 검사하기 위해 사용
[예제 4.46] 행 생성자와 CAST를 이용한 질의문 예
“김영길 사원의 부양가족(dependents) 정보를 가지고 있는 사원의 사원번호를 검색하라.”
select eid from employees
where cast(row(565534555,cast(row('김','영','길') as name_t),3)
as person_t)
in employees. dependents;
 TABLE()
– 컬렉션 유도 테이블 구문
– 컬렉션의 구성요소로 구성된 행의 테이블로 변환
[예제 4.47] TABLE( )을 이용한 질의문 예
“주민등록번호가 322456776인 부양가족(dependents)을 가진 사원의 사원 번호를 검색하라.”
select eid from employees e where 322456776 in
(select d.ssno from table(e.dependents) d);
Page 56
4.3 컬렉션 타입
INFORMIX
 TABLE()을 이용한 질의문 (cont’d)
[예제 4.48]
“사원 번호가 101인 사원에 속해있는 모든 부양가족(dependents)의 주민등록번호를 검색하라.”
select d.ssno from
table (select e. dependents from employees e where e,eid = 101) d;
[예제 4.49]
“employees 테이블에서 모든 사원 행의 사원번호(eid)를 검색하고, 각각의 사원번호(eid)를 가진
사원별로 16세미만의 부양가족(dependents) 인원수를 검색하라.”
select e.eid, (select count(*) from table(e.dependents) d
where d.age < 16)
from employees s;
 사원 테이블에서 모든 사원의 사원번호를 검색하고, 각 사원의 부양가족 중 16세 이하인
부양가족의 주민등록번호를 검색하라.(그림 4.21을 이용한 ORACLE 질의문 참조 pp. 206)
select e.eid, multiset(select item ssno from table(e.dependents)
where age < 16)
from employees e;
Page 57
4.3 컬렉션 타입
INFORMIX
 INFORMIX에서의 리스트
– IN, CARDINALITY 키워드, TABLE() 구문 사용 가능
• 단, TABLE() 구문을 이용하면 리스트의 순서가 무의미해짐
– ORACLE의 VARRAY와 유사
[예제 4.50] 리스트 타입의 컬럼을 이용한 테이블 생성 예
“[예제 4.35]와 같이 간단한 전화번호(phonebook) 테이블을 만든다. 그 테이블에서는 개개인마다
내선번호의 LIST를 가지고 그 내선번호간의 순서는 중요하다.”
create table phonebook
(
phperson
person_t
extensions
list(int not null)
);
phonebook
phperson
extensions
row(123897766), row('홍', '정', '화'), 45)
list{345, 989}
row(432112233, row('김', '수', '창'),32)
list{123}
그림 4.25 정수형 리스트값을 갖는 컬럼 extensions을 포함한 전화번호부 테이블
Page 58
4.3 컬렉션 타입
INFORMIX
 INFORMIX에서의 리스트 (cont’d)
[예제 4.51] 리스트 타입의 컬럼을 이용한 검색 질의문 예
“30세 이상인 모든 사람(persons)의 내선번호의 리스트와 성(first name)을 검색하라.”
select phperson.pname.fname, extensions from phonebook
where phperson.age > 30; 검색 결과는 그림 4.26 참조
[예제 4.52] “ = ” 을 이용한 질의문 예
“주민등록번호가 123897766인 사람이 갖고 있는 내선 번호의 개수를 검색하라.”
select cardinality(extensions) from phonebook
where phperson.ssno = 123897766;
“내선번호 123을 갖고 있는 사람을 검색하라.”
select phperson from phonebook
where 123 in extension;
“동일한 내선번호 리스트를 갖고 있는 사람의 쌍을 출력하기 위한 질의어을 구성하라.”
select pb1.phperson, pb2.phperson from phonebook pb1, phonebook pb2
where pb1.extensions = pb2.extensions and pb1.phperson <> pb2.phperson;
중복된 내용을 제거하기 위해서는 pb1.phperson < pb2.phperson 이용
Page 59
4.3 컬렉션 타입
INFORMIX
 INFORMIX에서의 리스트 (cont’d)
[예제 4.53] “ = ” 을 이용한 질의문 예
“내선번호로 123만을 갖는 사람을 검색하기 위한 질의어를 구성하라.”
select phperson from phonebook
where phperson.extension = list{123};
 INFORMIX에서의 컬렉션값 표현
collvexpr
예제
1. var : 상수 또는 변수
“set{‘a’, ‘b’}”, “list{1, 2}” : citylist
2. 컬렉션 생성자 expr
set{‘a’, ‘b’ || ‘c’}, list{1+5, 2*age}
3. colname
Dependents
4. 도트 표현식
ph.extensions, a.b.mycollection
5. 컬렉션 하나를 반환하는
서브질의어
(select dependents from employee where eid = 101)
6. case expr
case when cardinality(extensions) > 0
Extensions else list {1000}
Page 60
4.3 컬렉션 타입
INFORMIX
 컬렉션 표현을 위해 확장된 SQL 형태
CARDINALITY(colname) -- an extension to numvexpr,
IN COLLVEXPR
-- an extension to the IN predicate,
TABLE(collvexpr)
-- in place of a tablename in FROM ...
그림 4.28 INFORMIX에서의 컬렉션을 위한 SQL문의 일반적인 형태
[예제 4.54]
“전화번호 테이블에서 중복된 행을 가지는 사람을 검색하는 질의어를 작성하라.”
select pb.phperson form phonebook pb
group by pb.phperson
having count(*) > 1;
Page 61
4.3 컬렉션 타입
INFORMIX
 INFORMIX에서의 삽입과 갱신
– 컬렉션 값을 테이블로 변환하기 위해 컬렉션 생성자 사용
[예제 4.55]
“그림 4.25에 나타난 홍정화의 정보를 전화번호부 테이블에 삽입하는 질의문을 작성하라.”
insert into phonebook values
(cast(row(123897766,cast(row('홍','정','화') as name_t), 45) as
person_t),
list{345,989});
“그림 4.23에 나타난 홍정화의 정보를 사원(employees) 테이블에 삽입하는 질의문을 작성하라.”
insert into employees values (1,
cast(row(123897766, cast(row('홍','정','화') as name_t), 45) as
person_t),
set{row(322456776,row('홍','정','수'),8), -- NOTE NO CASTS HERE
row(123822332,row('홍','수','연'),12)});
“전화번호부 테이블에서 사원번호 101을 갖는 홍정화의 내선번호를 345와 999로 변경하라.”
update phonebook set extensions = list345,999 where eid = 101;
Page 62
4.3 컬렉션 타입
INFORMIX
 ORACLE vs. INFORMIX
ORACLE
INFORMIX
 [관계규칙1]을 따르지 않음
유사성
 컬렉션 타입 지원
 컬렉션 타입 값을 테이블 형태로 변환하여 대화식 질의 가능
 내포 테이블, VARRAY
 집합, 리스트, 다중 집합
 내포 테이블 내에 내포 테이블의
 TABLE()을 이용해 컬렉션 값에 질의
사용 불가
차이점
 사용자가 컬렉션 타입에 특별한 이름
지정 가능
 CURSOR()를 이용해 컬렉션의 모든
 다른 컬렉션 타입의 내포 허용
 컬렉션 타입에 특별한 이름 대신
SET(element type)과 같은 형식 이용
 컬렉션의 각 구성 요소에 접근 불가
구성 요소 검색 가능
 좋은 데이터베이스 설계
– 중요한 객체를 컬렉션 내에 저장하지 않음
– 실제적인 객체는 독립적인 객체 테이블에 저장하고 REF값을 이용해 컬렉션 내에서 참조
Page 63
4.3 절차적 SQL, 사용자 정의 함수와 메소드
 절차적 언어(procedural language)
– 특정 작업을 수행하기 위한 일련의 명령을 순서에 따라 작성
 비절차적 언어(non-procedural language)
– 실행-요청된 작업을 한번에 모두 기술
 SQL의 중요한 특징 : 비절차성
– 장점 : 비전문적인 프로그래머가 쉽게 사용
– 단점 : 연산 표현의 완전성(computational completeness) 부족
– 해결책
• 절차적 SQL 언어
Page 64
4.3 절차적 SQL, 사용자 정의 함수와 메소드
 절차적 SQL
– 메모리 상주 변수, 조건부와 반복 구문, 프로시저와 함수, 프로그램 내
에서 SQL 구문 실행
– 클라이언트-서버 어플리케이션 성능 향상
– 대표적 제품
• SYBASE : T-SQL(transactional SQL)
• INFORMIX : SPL
• ORACLE : PL/SQL
– stored procedure
• 서버에서 실행되도록 절차적 SQL로 작성된 함수
• Create Function 문을 이용해 카탈로그 테이블에 기록
– SQL-99/PSM(Persistent Stored Module)로 표준화
Page 65
PL/SQL : ORACLE의 절차적 SQL 언어
 PL/SQL
– 절차적 과정을 위한 대화식 SQL의 확장
– 기능
• 메모리 변수를 선언하고 반복문과 조건문을 실행하면서 절차적 처리 방법을 제공
• SQL 문을 통해 데이터베이스에 접근
– 프로그램 블록
• DECLARE 부분
 메모리 변수 선언
• BEGIN-END 부분
 실행문 리스트
• EXCEPTION 부분
 모든 실행문 다음과 END 문 전에 나타남
Page 66
PL/SQL : ORACLE의 절차적 SQL 언어
 PL/SQL(cont’d)
– 간단한 계산을 수행하고 result 테이블에 결과를 저장하는 프로그램
[예제 4.56] “1부터 100까지 정수의 합계를 구하는 PL/SQL의 프로그램을 작성하라.”
declare
i
total
integer;
integer := 0;
-- 초기 값을 갖지 않는 지역 변수
-- 초기 값을 갖는 지역 변수
begin
for i in 1..100 loop
total := total + i;
end loop;
insert into result(rvalue) values (total);
-- 데이터베이스 테이블에 결과 삽
end;
 result 테이블은 정수형 타입의 rvalue 컬럼만을 가지고 있다고 가정
 성공적으로 실행되면 “PL/SQL procedure successfully completed” 메시지 출력
FOR counter IN [REVERSE] lower_bound..higher_bound LOOP
-- sequence of statement
END LOOP
< 반복문 형식 >
Page 67
PL/SQL : ORACLE의 절차적 SQL 언어
 PL/SQL(cont’d)
– 임의 조건에 따라 반복수행을 종료하는 반복문을 이용한 프로그램
[예제 4.56] “1부터 100까지 정수의 합계를 구하는 PL/SQL의 프로그램을 작성하라.”
declare
i
total
begin
loop
-- 초기 값을 갖는 지역 변수
-- 초기 값을 갖는 지역 변수
integer:=0;
integer:=0;
i := i + 1;
total := total + i;
exit when i >= 100;
end loop;
insert into result(rvalue) value (total);
end;
-- 데이터베이스 테이블에 결과 삽입
LOOP
-- sequence of statements
-- exit loop at any point with: EXIT WHEN condition;
END LOOP
-- flow of control resumes here after EXIT WHEN occurs within loop
< 반복문 형식 >
Page 68
PL/SQL : ORACLE의 절차적 SQL 언어
 사용자 정의 함수
– 함수 생성
[예제 4.57] “정수의 합을 구하기 위한 PL/SQL 함수를 작성하라.”
create function sum_n(n integer) return integer is
i
integer;
total
integer:=0;
begin
for i in 1..n loop
total := total + I;
end loop;
return total;
-- SQL 또는 PL/SQL로 결과를 반환
end;
 원래 DECLARE 부분은 키워드 DECLARE 없이 Create Function문 다음에 위치
 성공하면 “Function created” 메시지 출력
Page 69
PL/SQL : ORACLE의 절차적 SQL 언어
 사용자 정의 함수(cont’d)
– SQL에서 함수 호출
• 집합 함수 sum() : 값의 그룹을 모두 입력받아 하나의 값을 반환
• sum_n() : 각 행에서 하나의 값을 입력받아 단 하나의 값을 반환
select sum_n(10) from orders;
 컬럼 형태로 다중값을 결과로 반환
select sun_n(10) from dual;
 dual 테이블 : 단일 행과 단일 컬럼을 갖는 테이블이
필요할 때 사용하는 특별한 테이블
select qty, sum_n(qty), 2*sum_n(qty*qty) from orders where aid = 'a04 ';
 함수의 argument로 실제 컬럼값 사용
• 데이터베이스를 변화시키기 위한 것이 아닌 단지 값을 계산하기 위한 함수
Page 70
PL/SQL : ORACLE의 절차적 SQL 언어
 사용자 정의 함수(cont’d)
– SQL에서 함수 호출 (cont’d)
[예제 4.58] “고객(customers) 테이블에서 주어진 고객번호(cid)를 갖는 고객명(cname)과
거주지(city)를 검색하고 '김동길(서울)' 과 같은 형식으로 결과를 반환하도록
하는 함수를 작성하라.”
create function namecity(custid char) return char is
custname char(20);
-- maximum size, get trimmed back below
custcity char(20);
begin
select cname, city into custname, custcity from customers where
cid = custid;
return rtrim(custname) || "(" || rtrim(custcity) || ")" ; -- trim extra space at right
end;
 첫 번째 라인에서 custid char와 return char에 대한 char 타입의 길이를 정하는 것은 불필요
Page 71
PL/SQL : ORACLE의 절차적 SQL 언어
 사용자 정의 함수(cont’d)
create function increment(x int) return int is
begin
x := x + 1;
-- 매개변수 x 값의 수정 **ERROR -WON'T COMPILE**
return x;
-- (we should have written "return x+1")
end;
– 매개변수를 수정하는 프로그램은 정상적 컴파일이 되지 않음
• 컴파일 시 “Function created with compilation errors” 에러 메시지
• 호출 시 “Package or function INCREMENT is in an invalid state” 에러 메시지
– 매개변수의 갱신이 가능한 경우
• Increment(x int) 대신 increment(x in out int)을 Create Function문에서 사용
• 또 다른 에러 발생
– 읽기 전용 매개변수가 적절
• 중간 결과값이나 최종 결과값은 지역 변수를 이용해 저장
Page 72
PL/SQL : ORACLE의 절차적 SQL 언어
 사용자 정의 함수(cont’d)
– 지역변수를 이용한 함수 생성 프로그램
[예제 4.59] “person_t 객체의 나이에 1을 더하는 함수를 작성하라. 매개변수 x에서 1이 증가된
나이를 갖는 person_t 객체 타입의 지역변수를 반환값으로 정한다.”
create function inc_age(person_t x) return person_t is
nper person_t := person_t(x.ssno, x.name, x.age);
-- clone x to local object nper
begin
nper.age := x.age + 1;
-- 매개변수가 아닌 지역변수에서 나이의 수정
return nper;
end;
 person_t( )와 같은 생성자에는 애트리뷰트가 독립적으로 표현되어야 함
 갱신된 나이 정보를 갖는 people 테이블 내의 모든 person_t 객체를 검색하라.
select inc_age(value(p)) from people p;
 나이 증가는 단지 출력 결과에만 적용
증가된 나이의 데이터값을 테이블에 실제로 적용하라.
update people p set p = inc_age(value(p)) where p.age < 40;
Page 73
PL/SQL : ORACLE의 절차적 SQL 언어
 사용자 정의 함수(cont’d)
FOR counter IN [REVERSE] lower_bound..higher_bound
-- statements
END LOOP
LOOP
-- statements
-- 반복 수행중에 EXIT WHEN 조건을 만나는 경우 반복문을 벗어남
END LOOP
-- 제어 흐름은 EXIT WHEN가 반복문 내에서 발생된 후에 다시 시작.
IF condition THEN
statements
[ELSE
statements...]
END IF
그림 4.29 ORACLE의 PL/SQL에서의 반복문과 IF문 형식
Page 74
PL/SQL : ORACLE의 절차적 SQL 언어
 메소드
– 특정 객체 타입에 속하는 함수
– 메소드가 속해 있는 객체 타입으로 선언된 특정 객체를 참조
• 참조되는 객체 : self
[예제 4.60] “포인트(점의 위치값)를 표현하는 객체 타입 point_t와 사각형을 표현하는 객체타입 rectangle_t를
정의하라. rectangle_t 타입에 속한 area()와 inside(point_t) 메소드를 선언하라. area() 메소드는
인수를 필요로 하지 않으며 사각형 객체의 면적을 계산하고, inside(point_t) 메소드는 point_t
타입의 인수가 사각형의 안쪽에 있다면 1을 그렇지 않으면 0을 반환한다.”
create type point_t as object
 Create Type 문은 메소드의 실제 실행 코드를 호출할 수 있도록
하는 정보만 포함
(
x int,
 실제 실행 코드는 Create Type Body 문에서 따로 정의
y int
);
create type rectangle_t as object
(
메소드 이름
pt1 point_t,
pt2 point_t,
멤버 함수 
member function inside(p point_t) 메소드 매개변수
rect.inside(pt1)
return int,
멤버 함수 
member function area
rect.area( )
return int
메소드 반환 타입
);
Page 75
PL/SQL : ORACLE의 절차적 SQL 언어
 메소드 (cont’d)
[예제 4.61] “point_t와 rectangle_t 타입의 객체 테이블을 생성하고 몇 개의 값을 각 테이블에
삽입하라.”
create table points of point_t (primary key (x, y));
create table rects of rectangle_t (primary key (pt1.x, pt1.y, pt2.x, pt2.y));
insert into rects values (point_t(1,2), point_t(3,4));
insert into rects values (point_t(1,1), point_t(6,6));
insert into points values (2,3);
insert into points values (1,4);
insert into points values (4,4);
[예제 4.62] “rects 테이블에서 모든 사각형의 면적을 검색하기 위한 Select 질의문”
point (4, 2)가 사각형 내에 존재하는지 결정하기 위한 질의문을 작성하라.”
select value(x), x.area() from rects x;
select value(x), x.inside(point_t(4, 2)) from rects x;
 value(x)는 rects 테이블의 전체 행 객체를 객체 생성자 형태로 반환
“rects 테이블의 특정 사각형 내부에 존재하는 points 테이블의 모든 point에 대한 위치 값을
검색하라.”
select distinct p.x, p.y from points p, rects r
where r.inside(value(p)) > 0;
Page 76
PL/SQL : ORACLE의 절차적 SQL 언어
 메소드 (cont’d)
[예제 4.62] (cont’d)
“ 모든 사각형 내부에 존재하는 point p를 검색하라.”
select value(p) from points p where not exists
(select * from rects r where r.inside(p) = 0);
“24보다 작은 면적을 갖는 모든 사각형에 대해서 pt2의 x와 y 값을 1씩 증가시키는
Update문을 작성하라.”
update rects r set pt2 = point_t(r.pt2.x+1, r.pt2.y+1)
where r.area() < 24
– MEMBER FUNCTION절이 추가된 Create Type 문 형식
CREATE TYPE typename AS OBJECT
(attrname datatype {, attrname datatype …}
MEMBER FUNCTION methodname [(param type {, param type …})]
RETURN datatype,
{, MEMBER FUNCTION methodname [(param type {, param type …})]
RETURN datatype …});
그림 4.30 ORACLE의 메소드를 갖는 Create Object Type 문의 일반적 형식
Page 77
PL/SQL : ORACLE의 절차적 SQL 언어
 메소드 (cont’d)
– Create Type Body 문
• Create Type 문에서 선언된 메소드에 대해 구현된 코드 제공
CREATE [OR REPLACE] TYPE BODY type {AS|IS}
MEMBER FUNCTION methodname [(param type [{, param type, …}])]
RETURN type IS
BEGIN
-- BEGIN PL/SQL 문장
implementation statements
END;
-- END PL/SQL 문장
{MEMBER FUNCTION methodname [(param type [{, param type, …}])]
RETURN type IS
BEGIN
-- BEGIN PL/SQL 문장
statements
END;
-- END PL/SQL 문장
…}
END;
그림 4.31 ORACLE에서의 Create Type Body 문
Page 78
PL/SQL : ORACLE의 절차적 SQL 언어
– Create Type Body 문 (cont’d)
[예제 4.63] “[예제 4.60]에서 선언된 rectangle_t 객체 타입의 메소드 area()과 inside(point_t)를
실제로 구현하라.”
create type body rectangle_t as
member function area return int is -- 프로그램 구조는 PL/SQL을 이용한다.
begin
return (self.pt2.x-self.pt1.x)*(self.pt2.y-self.pt1.y);
-- 계산된 면적
end;
member function inside(p in point_t) return int is
begin
if (p.x >= self.pt1.x) and (p.x <= self.pt2.x) and
-- x 좌표
(p.y >= self.pt1.y) and (p.y <= self.pt2.y) then
-- y 좌표
return 1; -- p는 사각형 내부(경계선 포함)에 존재
else
return 0; -- p는 사각형 외부에 존재
end if;
end;
end;
 self 객체 : 멤버 함수의 숨겨진 파라미터
Page 79
PL/SQL : ORACLE의 절차적 SQL 언어
– 메소드와 관련한 간단한 질의문 예
• ORSQL에서는 메소드를 이용한 테이블 접근 뿐 아니라 내부 컬럼에 대한 직접적
접근 허용
[예제 4.64] “각 포인트가 속하는 사각형 중 최소읭 면적을 구하는 질의문을 구성하라.”
select p.x, p.y, min(r.area()) from rects r, point p
where r.inside(value(p)) > 0
group by p.x, p.y;
 group by 절은 한 번에 포인트 집합 전체가 아닌 한 포인트에 대해서만 min( )함수 적용
또는
select value(p), value(r), r.area( ) from rects r, point p
where r.area() =
(select min(r1.area()) from rects r1 -- 최소 면적을 갖는 사각형
where r1.inside(value(p)) > 0) -- p 포인트를 포함
and r.inside(value(p))
order by p.x, p.y;
 객체지향적 특성을 갖는 질의문
Page 80
PL/SQL : ORACLE의 절차적 SQL 언어
– 메소드를 포함하는 객체 타입의 생성 예
[예제 4.65] “단어의 내포테이블(예를들어, 키워드의 집합)을 표현하는 객체 타입 wordset_t를
생성하는 방법을 살펴보자. [예제 4.66]의 documents 테이블 내에 이 객체 타입으로
선언된 애트리뷰트는 키워드 집합의 부분 집합을 통해 해당 문서를 찾아낼 수
있도록 한다.”
=> Page 238의 프로그램 코드 참조
 멤버 함수(메소드) : wordcount, subset
- 내부 정의 메소드의 사용 : ( ) 생략
예) self.words.count
- 내포 테이블의 구성 요소 참조
예) self.words(1), self.words(2)
Page 81
PL/SQL : ORACLE의 절차적 SQL 언어
– 메소드를 포함한 객체 타입을 컬럼으로 갖는 테이블 생성 예
[예제 4.66] “각 문서를 식별하기 위한 docid, 문서의 이름을 표현하는 docname, 문서의 작성자를
나타내는 person_t 타입의 docauthor, 키워드 집합 객체를 표현하는 wordset_t 타입의
keywords 구성요소를 갖는 document_t 객체 타입을 생성하라. document_t 타입의
객체를 포함하는 document 객체 테이블을 생성하라. 기본 키는 docid가 된다.”
drop table documents;
-- 기존에 정의된 documents 테이블과 객체 타입을 삭제
create or replace type document_t as object
(
docid
int,
-- 문서의 유일한 id
docname
varchar(40),
-- 문서 제목
docauthor
person_t, -- 문서 작가
keywords
wordset_t
-- 문서에 대한 키워드 집합
);
create table documents of document_t
-- 문서의 객체 테이블
(
primary key(docid)
) nested table keywords store as keywords_tab; -- 문서에 대한 키워드의 내포테이블
Page 82
PL/SQL : ORACLE의 절차적 SQL 언어
– documents 테이블에서 특정 키워드를 검색하는 질의문 예
[예제 4.67] “boat라는 키워드를 포함하는 문서를 검색하는 질의문을 작성하라.”
select docid from document d
where 'boat' in (select * from table(d.keywords.words));
“모든 문서의 전체 키워드 개수를 검색하라.”
select sum(d.keyword.wordcount( )) from document d;
[예제 4.68] 세 개의 키워드 : 'boat', 'Atlantic', 'trip'을 모두 가지고 있는 문서들을 검색하기 위한
질의문을 작성하라.
select docid, docname from documents d
where d.keywords.superset(wordset_t(wordtbl_t('boat', 'Atlantic', 'trip')))>0;
“정확하게 세 개의 키워드 'boat', 'Atlantic'. 'trip‘를 포함하는 문서를 검색하기 위한 질의문”
select docid, docname from documents d
where d.keywords.superset(wordset_t(wordtbl_t('boat', 'Atlantic', 'trip'))) > 0
and d.keywords.subset(wordset_t(wordtbl_t('boat', 'Atlantic', 'trip'))) > 0;
 컬럭션값을 가지기 때문에 다음과 같은 표현은 사용할 수 없음
where d.keyword = wordset_t(wordtbl_t('boat', 'Atlantic', 'trip'))
Page 83
PL/SQL : ORACLE의 절차적 SQL 언어
 갱신 메소드
– 갱신에 의해 발생할 수 있는 side effect 방지해야 함
– 단, 각 메소드의 self 객체는 변경할 수 없음
– 예) [예제 4.60]에서 pt2 값을 변화시켜 사각형을 확대하거나 축소하기 위한
rectangle_t 객체 타입의 메소드
• member function resize(npt2 point_t) return..
 pt2가 pt1보다 아래에 있거나 왼편에 존재하는 경우와 같은 가능성을 배제해야 함
[예제 4.69] “위에서 설명한 resize 메소드를 작성해 보자. 다음은 rectangle_t 타입을 위한
Create or Replace Type Body문의 한 부분이다.”
member function resize(p point_t) return rectangle_t is
newrect rectangle_t := self; -- 지역 객체의 동일한 self
begin
if p.x>self.pt1.x and p.y > self.pt1.y then --새로운 사각형이 유효한지를 검사
newrect.pt2 := p;
-- 사각형 반환에 대해서 pt2 갱신
end if;
return newrect;
pt2가 비합법적이면 수정되지 않음.
end;
Page 84
PL/SQL : ORACLE의 절차적 SQL 언어
 갱신 메소드(cont’d)
[예제 4.70] “새로 pt2의 위치 (10, 10)로 크기를 변경된 후에 rects 테이블의 모든 사각형을
출력하는 질의문을 작성하라.”
select r.resize(point_t(10,10) from rects r;
-- 크기가 수정된 사각형을 나열
 rects 테이블 내의 어떤 사각형도 실제로 변경되지 않음
[예제 4.71] “pt1이 (2,1)인 rects 테이블의 모든 사각형에 대해 pt2.x와 pt2.y를 실제로 1씩
증가시키기 위한 Update 문을 작성하라.”
update rects r
set r = r.resize(point_t(r.pt2.x + 1,r.pt2.y + 1))
where r.pt1 = point_t(2,1);
Page 85
SPL : INFORMIX의 절차적 SQL 언어
 SPL
– 절차적 관점에서 대화형 SQL을 확장한 것
– ORACLE의 메소드와 같은 기능을 수행하는 사용자 정의 함수 제공
– 모든 코드는 함수(또는 프로시저) 내에 포함
• 함수는 CREATE FUNCTION 문으로 시작/END FUNCTION 문으로 끝을 냄
 프로그램 블록
 DEFINE 문 : 메모리 변수 사용
 실행문
 하나의 함수가 여러 블록으로 구성된 경우 각 블록은 BEGIN/END 내에 포함
CREATE FUNCTION ([program [{, program …}]]) RETURNING datatype;
statements
end function;
param :: = paramname datatype
그림 4.33 SPL에서의 사용자 정의 함수를 위한 INFORMIX 구문
Page 86
SPL : INFORMIX의 절차적 SQL 언어
[예제 4.72] “어느 정수형 n에 대해 1 부터 n 까지 정수값을 더하는 SPL에서의 함수를 작성한다.”
create function sum_n(n int) returning int;
define i int;
define total int;
let total = 0;
for i = 1 to n
let total := total + i;
end for;
return total;
-- SQL 또는 SPL 호출한 곳으로 결과를 반환
end function;
함수를 실행하기 위한 INFORMIX SQL 문
execute function sum_n(10);
 올바르지 못한 결과를 반환하는 SQL
select sum_n(10) from orders;
실제의 컬럼값을 인수값으로 사용한 함수 사용
select qty, sum_n(qty), 2*sum_n(qty*qty) from orders where aid = 'a04';
• 배정문
 SPL : let target = expression
 PL/SQL : target := expression
Page 87
SPL : INFORMIX의 절차적 SQL 언어
– 반복문
FOR counter = lower_bound TO higher_bound [STEP increment]
-- 문장
-- EXIT FOR 조건을 갖는 특정 위치에서 반복문을 벗어난다.
END FOR;
--제어 흐름은 EXIT FOR가 반복문 내에서 발생된 후에 다시 시작.
WHILE condition
-- 문장
-- 특정 위치에서 반복문을 벗어난다 : EXIT WHILE 조건;
END WHILE;
-- 제어 흐름은 EXIT WHILE이 반복문 내에서 발생된 후에 다시 시작.
Page 88
SPL : INFORMIX의 절차적 SQL 언어
– [예제 4.56]의 프로그램 재작성
• 함수 실행 후 사용자는 result 테이블에서 원하는 결과를 검색하거나 Execute 문을
이용해 출력 결과를 확인 가능
• SQL에서 호출된 함수는 단지 값을 계산하는 기능을 수행해야 함
 sum_n2()는 SQL에서 올바르게 사용할 수 없음
 “Illegal SQL statement in stored procedure” 에러
create function sum_n2(n int) returning int;
define i integer;
define total integer;
let i = 0;
let total = 0;
while TRUE
-- while i <= N 로 사용 가능.
let total := total + i;
let i := i + 1;
exit while i > n;
-- while 문을 벗어남.
end while;
delete from result;
-- 데이터베이스 테이블의 모든 과거 행의 삭제
insert into result (rvalue) values (total);
-- 데이터베이스 테이블에 결과를 삽입
end function;
Page 89
SPL : INFORMIX의 절차적 SQL 언어
– 함수 작성 예
[예제 4.73] “인수 값으로 전달받은 고객번호(cid)를 가지는 고객의 이름(cname)과 거주지(city)를
검색하고 '김동길(서울)'형태로 결과를 반환하는 함수를 작성하라.”
create function namecity(custid char(10)) returning char(20)
cusname char(20);
custcity char(20);
begin
select cname, city into custname, custcity from customers where cid = custid;
return trim(custname) || '(' || trim(custcity) || ')'; -- 여분의 공간을 제거
end;
Page 90
SPL : INFORMIX의 절차적 SQL 언어
– 매개변수 값의 보호
• call-by-value 방식의 함수 사용과 함께 RETURN 문에서 매개변수를 사용할 수 있
도록 함
• call-by-reference 방식의 함수 지원이 가장 좋은 방법
• 매개변수 변경과 관련한 SPL 프로그램 규칙을 위반한 예
[예제 4.74] “문자열 매개변수를 갱신하는 함수를 작성하라.”
create function x_out(s varchar(80)) returning varchar(80);
let s[1,3] = 'xxx';
-- 매개변수를 변경 **DON'T DO THIS!!**
return s;
-- 갱신된 매개변수를 반환
end;
SQL 문에서의 호출
select x_out(cname) from customers;
갱신된 매개변수값을 결과값으로 반환할 수 없으므로 다음과 같이 함수를 재구성 해야 함
create function x_out(s varchar(80)) returning varchar(80);
define newstr varchar(80);
let newstr = s;
-- 지역 변수 newstr에 매개변수 문자열을 저장
let newstr[1,3] = 'xxx';
-- newstr의 첫 번째 세 개의 문자를 'xxx'로 변환
return newstr;
-- 갱신된 지역변수의 문자열을 반환
end;
Page 91
SPL : INFORMIX의 절차적 SQL 언어
[예제 4.75] “person_t 객체의 age에 1을 증가시키는 함수를 작성하고, 매개변수 값 x로부터 1이
증가된 age를 갖는 person_t 행 타입 지역 변수의 값으로 결과값을 반환하도록
설정하라.”
create function inc_age(x person_t) returning person_t;
define nper person_t;
-- 지역 변수 nper
let nper = cast(row(x.ssno, x.name, x.age) as person_t;
-- nper에 x를 저장
let nper.age = age + 1;
-- age 값의 변환
return nper;
-- 지역변수 nper의 반환
end;
또는 다음과 같은 표현으로 간략화할 수 있음
return cast(row(x.ssno, x.pname, x.age+1) as person_t);
-- 생성과 반환
위의 함수를 이용해 나이(age) 필드를 갱신한 people 테이블에서의 모든 person_t 객체를
출력하기 위한 질의문
select inc_age(p) from people p;
 실제로 데이터가 갱신되는 것이 아니라 결과 출력에만 영향을 미침
Page 92
SPL : INFORMIX의 절차적 SQL 언어
 SPL을 이용한 사용자 정의 함수의 구현
– SPL로 구현한 [예제 4.60]의 사용자 정의 함수 area( )와 inside( )
• 호출 형식 : area( r ), inside( r, p)
[예제 4.76] “사각형에 대한 INFORMIX 행 타입 point_t와 rectangle_t를 생성하고 이와 관련한
사용자 정의 함수 area(r)과 inside(r,p)를 구현하는 프로그램을 작성하라.”
drop function area;
drop function inside;
drop row type rectangle_t restrict;
-- 종속하는 객체들을 삭제해야한다.
drop row type point_t restrict;
-- 타입을 삭제하기 전의 타입들
create row type point_t
(
x int,
-- 포인트의 가로 좌표
y int
-- 세로 좌표
);
create row type rectangle_t
(
pt1 point_t,
-- 사각형의 좌측 최하단 위치의 포인트
pt2 point_t
-- 사각형의 우측 최상단 위치의 포인트
);
Page 93
SPL : INFORMIX의 절차적 SQL 언어
[예제 4.76] (cont’d)
create function area(r rectangle_t) returning int;
return (r.pt2.x-r.pt1.x)*(r.pt2.y-r.pt1.y);
end function;
-- 사각형 r의 면적
create function inside (r rectangle_t, p point_t) return boolean;
if (p.x >= r.pt1.x) and (p.x <= r.pt2.x) and (p.y >= r.pt1.y) and
(p.y <= r.pt2.y)
then return 't';
-- p 가 r에 포함되면 True를 반환
else
return 'f'; -- p 가 r에 포함되지 않으면 False을 반환
end if;
end function;
Page 94
SPL : INFORMIX의 절차적 SQL 언어
[예제 4.77] “사각형과 포인트를 위한 각 테이블을 생성하고 몇 개의 값을 삽입하기 위한 질의문을
작성하라.”
insert into rects values (cast(row(1,2) as point_t),cast(row(3,4) as point_t));
insert into rects values (cast(row(1,1) as point_t),cast(row(6,6) as point_t));
insert into points values (2,3);
insert into points values (1,4);
insert into points values (4,4);
“사각형의 면적을 계산하고 포인트 (4,2)가 각 사각형 내부에 존재하는지를 판단하기 위한
질의문을 작성하라.”
select area(x) from rects x;
select inside(x,cast(row(4,2) as point_t)) from rects x;
– [예제 4.65]의 키워드와 문서에 관련한 함수 생성 예
• INFORMIX는 대화형 SQL 커서 기능이 부족하지만 SPL 함수를 사용하면 여러 컬렉
션에 대한 검색, 컬렉션 구성요소에 대한 수정 등이 가능
• INFORMIX는 컬렉션을 위한 내부 정의 함수인 CARDINALITY( )를 제공하므로
wordcount 함수는 필요없음
• 사용자 정의 함수는 두 인자 중 self 객체 인수에 의한 순서를 결정할 수 없으므로
superset 함수 필요없음
Page 95
SPL : INFORMIX의 절차적 SQL 언어
– [예제 4.65]의 키워드와 문서에 관련한 함수 생성 예 (cont’d)
[예제 4.78] “wdset1이 wdset2의 subset이라면 TRUE를 반환하는 사용자 정의 함수
subset(wdset1, wdset2)를 작성하라. 여기서 wdset1과 wdset2는 varchar(30) 요소의
SET 타입으로 정의된다.
create function subset(wdset1 set(varchar(30) not null),
wdset2 set(varchar(30) not null)) returning boolean;
define matchcnt int;
-- 일치하는 단어의 개수를 저장하기 위한 지역변수
define wd1 varchar(30);
-- wdset1 집합 내의 단어에 대한 지역 변수
define wd2 varchar(30);
-- wdset2 집합 내의 단어에 대한 지역 변수
foreach cursor1 for
-- wdset1의 구성요소를 통한 반복수행; 그림 4.34 참조
select * into wd1 from table(wdset1)
-- cursor1로부터 한번에 하나의 구성요소 검색
let matchcnt = 0;
-- wdset2 집합 내에 wd1 단어의 개수를 구하기 전 matchcnt 변수 초기화
foreach cursor2 for
-- wdset2의 구성요소를 통한 반복수행
select * into wd2 from table(wdset2)
if (wd2 == wd1) then
let matchcnt = matchcnt + 1;
-- wdset2에서 wd1을 만족하는 개수 계산
end if;
end foreach;
Page 96
SPL : INFORMIX의 절차적 SQL 언어
[예제 4.78] (cont’d)
if (matchcnt==0) then
return 'f'; -- wd1이 wdset2에 존재하지 않는 경우에는 실패
end if;
end foreach;
return 't';
-- 모든 wdset1 단어를 wdset2에서 찾은 경우 True를 반환
end function;
– 커서 반복 수행
• 컬렉션 구성요소 타입의 지역변수를 선언하고, 이 지역변수를 Select Into 문에서
사용
FOREACH cursorname FOR
SELECT * INTO element_var FROM TABLE(collection_var)
statements
END FOREACH;
그림 4.34 컬렉션 변수에 대한 커서 반복 수행의 SPL 구문
Page 97
SPL : INFORMIX의 절차적 SQL 언어
– 각 문서에 존재하는 키워드 집합을 포함하는 documents 테이블을 생
성하는 예
[예제 4.79] “문서번호(docid), 문서 이름(docname), person_t 타입의 저자명(docauthor),
varchar(30)의 키워드 집합을 갖는 행 타입 document_t를 생성하라. 또한,
document_t 타입을 갖는 documents 테이블을 생성하라.”
drop table documents;
drop row type document_t restrict;
create row type document_t
(
docid
int,
docname
varchar(40),
docauthor
person_t,
keywords set (varchar(30) not null)
);
create table document of document_t
(
primary key(docid)
);
-----
문서의 유일 식별자
문서 제목
문서 작가명
문서에 대한 키워드 집합
Page 98
SPL : INFORMIX의 절차적 SQL 언어
[예제 4.80] “몇 개의 행을 삽입한 후 'boat' 키워드를 갖고 있는 문서를 검색하는
질의문을 작성하라.”
select docid from documents
where 'boat' in keyword; -- INFORMIX에서 IN은 서브 질의어를 필요로 하지 않음
“모든 문서에서 키워드의 전체 개수를 검색하라.”
select sum(cardinality(keyword)) from documents;
[예제 4.81] “boat, seoul, apple 키워드를 모두 포함하는 문서를 검색하기 위한
질의문을 작성하라.”
select docid, docname from documents
where subset(set{'boat','seoul','apple‘},keywords);
“boat, seoul, apple 키워드만을 포함하는 문서를 검색하기 위한 질의문을 작성하라.”
select docid, docname from documents d
where d.keywords = set{'boat','seoul','apple‘};
 INFORMIX에서는 집합에 대해 “=“를 이용한 일치 비교 지원
Page 99
SPL : INFORMIX의 절차적 SQL 언어
– Requests 테이블
• 각 행이 식별자인 reqid 컬럼과 문서의 키워드 집합을 저장하기 위한 wds 컬럼
(varchar(30)의 SET 타입)을 갖는 테이블
“documents 테이블과 requests 테이블이 일치하는지 알아보는 질의문”
select d.docid, r.reqid from documents d, requests r
where subset(r.wds, d.keywords);
“질의 대상이 되는 모든 키워드들을 포함하면서 최소의 키워드를 가지고 있는
문서를 검색하는 질의문”
select d.docid, r.reqid, cardinality(d.keywords) from documents d, requests r
where cardinality(d,keywords) =
(select min(cardinality(d1.keywords)) from document d1
where subset(r.wds, d1.keywords))
and subset(r.wds, d.keywords);
Page 100
SPL : INFORMIX의 절차적 SQL 언어
 갱신 함수
– 컬렉션의 각 구성요소를 변경하기 위한 사용자 정의 함수 제공
[예제 4.82] “varchar(30) 타입으로 선언된 단어들의 집합에 새로운 단어를 추가하는
addword 함수를 작성하라. 함수는 추가된 단어를 포함한 새로 구성된
집합을 반환한다.”
create function addword (wdset set(varchar(30) not null), wd varchar(30))
returning set(varchar(30) not null);
define newset set(varchar(30) not null);
let newset = wdset;
-- 반환 값에 대한 집합을 저장.
insert into table(newset) values (wd); -- 새로운 구성요소 추가.
return newset;
end function;
[예제 4.83] “documents 테이블에 문서번호(docid)가 200인 문서의 키워드 리스트에
'bird'를 추가하라.”
update documents set keywords = addword(keywords,'bird')
where docid = 200;
Page 101
SPL : INFORMIX의 절차적 SQL 언어
 갱신함수(cont’d)
[예제 4.84] “varchar(30) 타입으로 선언된 단어의 집합에서 특정 단어를 삭제하고,
새로 구성된 집합을 반환하는 delword 함수를 작성하라.”
create function delword(wdset set(varchar(30) not null),wd varchar(30))
returning set(varchar(30) not null);
define wd1 varchar(30);
-- Select into절에서 커서로 사용.
define newset set(varchar(30) not null);
let newset = wdset;
-- 절대값 반환을 위해 매개변수를 복사
foreach cursor1 for
-- 집합을 검색하여 삭제할 단어 wd를 찾는다
select * into wd1 from table(newset)
if wd1 = wd then
-- wd를 찾아 삭제한다.
delect from table(newset) where current of cursor1;
exit foreach;
-- 삭제하고 반복수행을 벗어난다.
end if;
end foreach;
return newseet;
-- 집합을 반환
end function;
 특수한 SPL 문 형식
delete from table(wdset) where current of cursor1
Page 102
Pl/SQL vs. SPL
 PL/SQL 과 SPL 간의 유사성
–
–
–
–
데이터 타입은 Create Type 또는 Create Row Type 문을 통해 정의
산술 연산은 대화형 SQL과 동일
애트리뷰트/필드에 접근하기 위해 도트 표현 사용
객체/행 타입의 지역변수를 사용하기 전에 객체 행 생성자나 Select
Into 문에 의한 초기화 필요
– RETURN 표현이 함수 내 어디에서나 사용 가능
 사용자 정의 함수
– INFORMIX의 사용자 정의 함수
– ORACLE의 메소드도 사용자 정의 함수의 일종
• ORACLE에서도 독립적인 사용자 정의 함수 작성 가능
Page 103
Pl/SQL vs. SPL
구성요소
ORACLE PL/SQL
INFORMIX SPL
지역 변수 선언
varname datatype [:= initval];
DEFINE varname datatype;
배정문
varname := expr;
LET varname = expr;
논리 동등 비교
=
=
IF
IF boolean_expr THEN
statements
[ELSE
statements]
END IF;
IF boolean_expr THEN
statements
[ELSE
statements]
END IF;
FOR LOOP
FOR var IN first..last LOOP
statements
END LOOP;
FOR var IN (first TO last)
statements
END FOR;
RETURN
RETURN expr;
RETURN expr;
I int; -- 지역변수:
...
FOR I in 1..x.count LOOP
statements using element x(i)
END LOOP;
DEFINE e elementtype; -- 지역변수
...
FOREACH cursorname FOR
SELECT * INTO e FROM
TABLE(x)
END FOREACH;
객체/행 타입
생성자
objecttype(expr.…)
row(expr, .…)
SELECT INTO
SELECT expr INTO varname
FROM …
SELECT expr INTO varname
FROM …
LOOP over
COLLECTION
x
그림 4.35 절차적 언어인 ORACLE의 PL/SQL과 INFORMIX SPL의 구문 비교
Page 104
4.5 외부 함수와 패키지 사용자 정의 타입
 UDT 패키지
– 일반적인 용도로 구현되고 판매되는 UDT
• INFORMIX 에서는 데이터 블래이드(DataBlade)
• ORACLE에서는 카트리지(Cartridge)
• DB2 UDB에서는 익스텐더(Extender)
– 절차적 SQL이나 더 효율적으로 복잡한 계산을 수행할 수 있도록 C나
Java 등으로 구현 가능
– 멀티미디어 데이터 지원
 UDT 구현 기법
– 하나의 BLOB(Binary Large Function) 타입과 여러 개의 내장된 데이
터 타입을 가지고 구성
Page 105
4.5 외부 함수와 패키지 사용자 정의 타입
 이진 데이터와 BLOB
– BLOB
• 이미지, 사운드 트랙 등과 같은 대용량 객체를 저장하기 위해서 긴 길이의 비트 문
자열로 표현된 데이터를 데이터베이스에 저장할 수 있는 기능
• 최대 BLOB의 크기
 ORACLE : 4 GB-1 바이트
 INFORMIX : 4TB(terabytes)
 DB2 UDB : 2 GB-1 바이트
• 외부 함수와 함께 사용될 때 기능을 최대로 활용
 BLOB과 관련한 SQL-99의 특징
– Basic LOB data types
• 이진 문자열 타입을 BINARY LARGE OBJECT 또는 BLOB으로 선언
– Extended LOB support
• BLOB의 부분 문자열을 SUBSTRING 이라는 이진 문자열 함수를 이용하여 SQL에
접근
• POSITION나 LIKE를 이용한 패턴 매칭을 표현할 수 있음
Page 106
4.5 외부 함수와 패키지 사용자 정의 타입
 외부 함수(external function)
– 정의
• UDT가 C나 Java와 같이 데이터베이스에서 제공되지 않는 언어로 구현된 경우
– 데이터베이스 서버에 등록하는 단계
Step1 : 함수가 구현된 후 컴파일
Step2 : Create Function 문을 통해 등록
(함수 이름, 매개변수, 반환값의 데이터 타입, 컴파일된 함수 코드의 위치 명시)
– ORACLE, INFORMIX, DB2 UDB는 모두 BLOB이나 일반적인 데이터
타입의 매개변수를 허용하는 외부 함수 지원
 캡슐화(encapsulation)
– 객체에 속해 있는 메소드나 사용자 정의 함수를 통하지 않고서는 객체
내의 데이터에 접근할 수 없음
• 예) INFORMIX의 opaque type
 Distinct 타입 기능
– 기존에 존재하는 타입을 복사한 새로운 타입 생성
Page 107
4.5 외부 함수와 패키지 사용자 정의 타입
 BLOB 객체
– 4.4절의 사각형을 위한 BLOB 객체 타입
•
•
•
•
두 개의 포인트를 4개의 정수 형태로 16바이트 BLOB에 저장
BLOB을 매개변수로 하는 외부 함수 area( ), inside( )를 C나 Java로 구현 가능
SQL에서 BLOB 컬럼을 갖는 테이블을 생성
타입 이름으로 rectangle_t 대신 타입 이름에 BLOB을 붙일 수도 있음
 INFORMIX의 데이터 블레이드
 ORACLE의 카트리지
Page 108
4.5 외부 함수와 패키지 사용자 정의 타입
 세 데이터베이스 제품의 특징 비교
ORACLE
INFORMIX
DB2 UDB
사용자 정의 타입
Yes, 구조화된 객체 타입
Yes, 구조화된 행타입, distinct
타입
Yes, 구조화된 UDT, distinct
타입
상속성
No
Yes
Yes
UDT REFs
Yes
지원될 계획이 있음
Yes
컬렉션 타입
Yes, 내포 테이블, VARRAY
Yes,
집합(set),다중집합(multiset),
리스트(list)
No
사용자 정의 함수
Yes, PL/SQL, C, 자바로 구현,
객체 타입을 위한 메소드
포함
Yes, SPL( 절 차 적 SQL), C,
자바로
구현
Yes, C 나 Java로 구현
캡슐화된 UDT
(unpackaged)
BLOB 객체
BLOB 객체, 불투명한 타입
BLOB 객체
패키지화된 UDT
카트리지
데이터 블래이드
익스텐더
4T
2G-1
opaque 타입의 부분으로 접근,
UDF를 이용한 접근
SQL이나 UDF를 이용해서
접근
BLOB의 최대 크기
BLOB 부분 문자열
4G-1
UDF를 이용하여 접근
Page 109