--create type (結構化的)用法
create type dept as
(dept name varchar(20),
max_emps int)
ref using int
mode db2sql
create type emp as
(name varchar(32),
serialnum int,
dept ref(dept),
salary decimal(10,2))
mode db2sql
create type mgr under emp as
(bonus decimal(10,2))
mode db2sql
create type address_t as
(street varchar(30),
number char(15),
city varchar(30),
state varchar(10))
not final
mode db2sql
method samezip (addr address_t)
returns integer
language sql
deterministic
contains sql
no external action,
method distance (address_t)
returns float
language c
deterministic
parameter style sql
no sql
no external action
create type germany_addr_t under address_t as
(family_name varchar(30))
not final
mode db2sql
create type us_addr_t under address_t as
(zip varchar(10))
not final
mode db2sql
create type project as
(proj_name varchar(20),
proj_id integer,
proj_mgr mgr,
proj_lead emp,
location addr_t,
avail_date date)
mode db2sql
-- create type mapping的用法
create type mapping my_oracle_date
from local type sysibm.date
to server type oracle
remote type date
create type mapping my_oracle_dec
from local type sysibm.decimal(10,2)
to server oracle1
remote type number([10..38],2)
create type mapping my_oracle_char
from local type sysibm.varchar()
to server oracle1
remote type char()
create type mapping my_oracle_dec
to local type sysibm.decimal(10,2)
from server oracle2
remote type number(10,2)
-- create user mapping的用法
create user mapping for rspalten
server server390
options
(remote_authid 'system',
remote_password 'manager')
create user mapping for marcr
server oracle1
options
(remote_password 'nzxczy')
-- case的用法
case v_workdept
when'a00'
then update department
set deptname = 'data access 1';
when 'b01'
then update department
set deptname = 'data access 2';
else update department
set deptname = 'data access 3';
end case
case
when v_workdept = 'a00'
then update department
set deptname = 'data access 1';
when v_workdept = 'b01'
then update department
set deptname = 'data access 2';
else update department
set deptname = 'data access 3';
end case
-- create trigger的用法
create trigger new_hired
after insert on employee
for each row
update company_stats set nbemp = nbemp + 1
create trigger former_emp
after delete on employee
for each row
update company_stats set nbemp = nbemp - 1
create trigger reorder
after update of on_hand, max_stocked on parts
referencing new as n
for each row
when (n.on_hand < 0.10 * n.max_stocked)
begin atomic
values(issue_ship_request(n.max_stocked - n.on_hand, n.partno));
end
create trigger raise_limit
after update of salary on employee
referencing new as n old as o
for each row
when (n.salary > 1.1 * o.salary)
signal sqlstate '75000' set message_text='salary increase>10%'
create trigger stock_status
no cascade before update of quote on currentquote
referencing new as newquote old as oldquote
for each row
begin atomic
set newquote.status =
case
when newquote.quote >
(select max(quote) from quotehistory
where symbol = newquote.symbol
and year(quote_timestamp) = year(current date) )
then 'high'
when newquote.quote < (select min(quote) from quotehistory
where symbol = newquote.symbol
and year(quote_timestamp) = year(current date) )
then 'low'
when newquote.quote > oldquote.quote
then 'rising'
when newquote.quote < oldquote.quote
then 'dropping'
when newquote.quote = oldquote.quote
then 'steady'
end;
end
create trigger record_history
after update of quote on currentquote
referencing new as newquote
for each row
begin atomic
insert into quotehistory
values (newquote.symbol, newquote.quote, current timestamp);
end
-- create tablespace 的用法
create tablespace payroll
managed by database
using (device'/dev/rhdisk6' 10000,
device '/dev/rhdisk7' 10000,
device '/dev/rhdisk8' 10000)
overhead 12.67
transferrate 0.18
create tablespace accounting
managed by system
using ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
extentsize 64
prefetchsize 32
create tablespace plans
managed by database
using (device '/dev/rhdisk0' 10000, device '/dev/rn1hd01' 40000)
on dbpartitionnum (1)
using (device '/dev/rhdisk0' 10000, device '/dev/rn3hd03' 40000)
on dbpartitionnum (3)
using (device '/dev/rhdisk0' 10000, device '/dev/rn5hd05' 40000)
on dbpartitionnum (5)
-- 帶case查詢條件語句
select (case b.organtypecode
when 'D' then
b.parent
when 'S' then
b.parent
else
b.id
end),
b.name
from A_ORGAN b
where b.id = 999