Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

DB2常用SQL的写法(三)

Posted on 2010-03-18 22:35 Prayer 阅读(469) 评论(0)  编辑 收藏 引用 所属分类: DB2
--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

只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理