一区二区三区日韩精品-日韩经典一区二区三区-五月激情综合丁香婷婷-欧美精品中文字幕专区

分享

DB2常用SQL的寫法(持續(xù)更新中...)

 dawn001 2014-09-26
DB2常用SQL的寫法(持續(xù)更新中...)
-- Author: lavasoft
-- Date  :  2006-12-14
 
-- 創(chuàng)建一個自定義單值類型
create  distinct type var_newtype
 as decimal(5,2) with comparisons;
 -- var_newtype 類型名
 -- decimal(5,2) 實際的類型
 
-- 刪除一個自定義單值類型
drop distinct type var_newtype;
 
-- 創(chuàng)建一個自定義結構數(shù)據(jù)類型
create type my_type as(
 username varchar(20),
 department integer,
 salary decimal(10,2))
 not final
 mode db2sql;

-- 修改自定義結構數(shù)據(jù)類型,我目前還沒有發(fā)現(xiàn)刪除屬性的方法.
alter type my_type
add attribute hiredate date;
 
-- 刪除自定義結構數(shù)據(jù)類型
drop type my_type;

-- 獲取系統(tǒng)當前日期
select current date from sysibm.sysdummy1;
select current time from sysibm.sysdummy1;
select current timestamp from sysibm.sysdummy1;

 --sysibm.sysdummy1表是一個特殊的內(nèi)存中的表,用它可以發(fā)現(xiàn)如上面演示的 DB2 寄存器的值。您也可以使用關鍵字 VALUES 來對寄存器或表達式求值。
VALUES current date;
VALUES current time;
VALUES current timestamp;
 
-- VALUES的更多用法
VALUES 2+5;
VALUES 'hello lavasoft!';
 
values 56
union all
values 45;
 
values 1,2,3,4,5,6
union all
values 7,8,9,10,11,12
order by 1;
-- 更多變態(tài)級DB2 SQL寫法,AnyOneTable表示任意一個存在的表
select 234 from AnyOneTable;
select distinct 234 from AnyOneTable;
select distinct 234 as 1 from AnyOneTable;
select 'DB2變態(tài)級的SQL哈哈' from AnyOneTable;
select distinct 'DB2變態(tài)級的SQL哈哈' from AnyOneTable;
select distinct 'DB2變態(tài)級的SQL哈哈' as 1 from AnyOneTable;
     --(嘿嘿,好玩吧,你可以用任意一個表來當sysibm.sysdummy1用.不過不推薦這么做,除非你不記得sysibm.sysdummy1怎么寫了,Oracle中(對應dual)也一樣!哈哈哈哈!)
 
-- 定義變量,還可以設定默認值,給變量賦值
declare var1 char(2);
declare var2 int default 0;
set var1 = 'aa';
set var2 =23;
 
--創(chuàng)建一個動態(tài)游標變量
declare d_cur integer;
 
-- 給變量賦值的另一種方法
values expr1, expr2, expr3 into a, b, c;
 -- 相當于
set a = expr1;
set b = expr2;
set c = expr3;
 
-- 還有一種賦值方式
set prodname = (case
                  when (name is not null) then name
                  when (namestr is not null) then namestr
                  else  defaultname
                end);
 -- 相當于
set prodname = coalesce(name, namestr, defaultname);
 --這個類似oracle的decode()和nvl()函數(shù)的合并.

-- 定義一個游標
declare cur1 cursor with return to client for select * from dm_hy;
declare cur2 cursor for select * from dm_hy; -- 靜態(tài)游標

-- 創(chuàng)建數(shù)據(jù)表,并添加注釋,插入數(shù)據(jù).
CREATE TABLE tbr_catalog (
  id bigint  not null  generated by default as identity,
  type smallint not null,
  name varchar(255),
  parentid bigint,
  cataloglevel bigint,
  description varchar(255),
  PRIMARY KEY  (id)
);
 comment on table tbr_catalog is 'Birt報表目錄表';
 comment on column tbr_catalog.ID is '標識';
 comment on column tbr_catalog.type is '目錄類型';
 comment on column tbr_catalog.name is '目錄名稱';
 comment on column tbr_catalog.parentid is '目錄父標識';
 comment on column tbr_catalog.cataloglevel is '目錄層次';
 comment on column tbr_catalog.description is '目錄描述';
 -- 給數(shù)據(jù)表插入數(shù)據(jù)
insert into tbr_catalog(id, type, name, parentid, cataloglevel, description)
values (1, 0, '系統(tǒng)報表', 0, 0, '');
insert into tbr_catalog(id, type, name, parentid, cataloglevel, description)
values (2, 1, '用戶報表', 0, 0, '');
 
-- 創(chuàng)建外鍵
alter table tbr_storage
 add constraint fk_tbr_storage
 foreign key (catalogid)
 references tbr_catalog(id);
 
-- 更改表,添加列
alter table aaa add sex varchar(1);

-- 更改表,刪除列
alter table aaa drop column sex;
 
-- 去掉參數(shù)前后的空格
rtrim(dm_hy.mc);

-- 定義臨時表,通過已有person表來創(chuàng)建
declare global temporary table gbl_temp
like person
on commit delete rows --提交時刪除數(shù)據(jù)
not logged -- 不在日志中紀錄
in usr_tbsp -- 選用表空間
 -- 此語句創(chuàng)建一個名為 gbl_temp 的用戶臨時表。定義此用戶臨時表 所使用的列的名稱和說明與 person 的列的名稱和說明完全相同。
 
-- 創(chuàng)建有兩個字段的臨時表 
    -- 定義一個全局臨時表tmp_hy
declare global temporary table session.tmp_hy
    (
       dm varchar(10),
       mc varchar(10)        
    )
     with replace -- 如果存在此臨時表,則替換
     not logged;  -- 不在日志里紀錄
    -- 給臨時表插入三條數(shù)據(jù)
    insert into session.tmp_hy values('1','1');
    insert into session.tmp_hy values('1','1');
    insert into session.tmp_hy values('1','1');
 
-- 通過查詢批量插入數(shù)據(jù)
inster into tab_bk(select code,name from table book);
 
-- select ... into的用法
select * into :h1, :h2, :h3, :h4
     from emp
     where empno = '528671';
-- 語句的流程控制
if() then
 open cur1
 fetch cur1 into t_equipid;
 while(at_end<>1)do
  ......
 set t_temp=0;                                                           
 end while;
 close cur1;
else
 ......
end if; 

-- 外連接
select empno,deptname,projname
  from (emplyoee
  left outer join project
  on respemp=empon)
  left outer join department
  on mgrno=empno;
-- in、like、order by(... ASC|DESC)的用法
select * from book t
where t.name like '%J_編程%'
and t.code in('J565333','J565222');
order by t.name asc
-- 匯總表(概念復雜,難以理解,不常用)
create summary table sumy_stable1
  as (select workdept,
    count(*) as reccount,
    sum(salary) as salary,
    sum(bonus) as bonus
  from employee group by workdept)
 data initially deferred
 refresh immediate;
-- 使用SQL一次處理一個集合語義
-- (優(yōu)化前) select語句中每行的過程層和數(shù)據(jù)流層之間都有一個上下文切換
declare cur1 cursor for col1,col2 from tab_comp;
open cur1;
fetch cur1 into v1,v2;
while SQLCODE<> 100 do
 if (v1>20) then
  insert into tab_sel values(20,v1);
 else
  insert into tab_sel values(v1,v2);
 end if;
 fetch cur1 into v1,v2;
end while;
-- (優(yōu)化后)沒有過程層和數(shù)據(jù)流層之間的上下文切換
declare cur1 cursor for col1,col2 from tab_comp;
open cur1;
fetch cur1 into v1,v2;
while SQLCODE<> 100 do
 insert into tab_sel(select (case
         when col1>20 then 20
            else col1
        end),
        col2
      from tab_comp);
 fetch cur1 into v1,v2;
end while;
-- DB2函數(shù)分三類:列函數(shù)、標量函數(shù)、表函數(shù)
-- 列函數(shù)輸入一組數(shù)據(jù),輸出單一結果。
-- 標量函數(shù)接收一個值,返回另外一個值。
-- 表函數(shù)只能用于SQL語句的from字句中,它返回一個表的列,類似于一個已創(chuàng)建的常規(guī)表。

-- 下面是個標量函數(shù)的例子。
create function (salary int,bonus_percent int)
returns int
language SQL contains SQL
return(
 salary * bonus_percent/100
)

-- 下面是表函數(shù)
create function get_marks(begin_range int,end_range int)
 returns table(cid candidate_id,
       number test_id,
       score score)
 language SQL reads SQL DATA
 return
    select cid,number,score
    from test_taken
    where salary between (begin_range) and score(end_range)
example 1: define a scalar function that returns the tangent of a value using the existing sine and cosine functions.
   create function tan (x double)
     returns double
     language sql
     contains sql
     no external action
     deterministic
     return sin(x)/cos(x)              
example 2: define a transform function for the structured type person.
    
   create function fromperson (p person)
     returns row (name varchar(10), firstname varchar(10))
     language sql
     contains sql
     no external action
     deterministic
     return values (p..name, p..firstname)
example 3: define a table function that returns the employees in a specified department number.
    
   create function deptemployees (deptno char(3))
     returns table (empno char(6),
                    lastname varchar(15),
                    firstname varchar(12))
     language sql
     reads sql data
     no external action
     deterministic
     return
       select empno, lastname, firstnme
         from employee
         where employee.workdept = deptemployees.deptno
example 4: define a scalar function that reverses a string.
   create function reverse(instr varchar(4000))
     returns varchar(4000)
     deterministic no external action contains sql
     begin atomic
     declare revstr, reststr varchar(4000) default '';
     declare len int;
     if instr is null then
     return null;
     end if;
     set (reststr, len) = (instr, length(instr));
     while len > 0 do
     set (revstr, reststr, len)
       = (substr(reststr, 1, 1) concat revstr,
       substr(reststr, 2, len - 1),
       len - 1);
     end while;
     return revstr;
   end
example 4: define the table function from example 4 with auditing.
   create function deptemployees (deptno char(3))
     returns table (empno char(6),
                    lastname varchar(15),
                    firstname varchar(12))
     language sql
     modifies sql data
     no external action
     deterministic
     begin atomic
       insert into audit
       values (user,
               'table: employee prd: deptno = ' concat deptno);
       return
         select empno, lastname, firstnme
           from employee
           where employee.workdept = deptemployees.deptno
     end
-- for循環(huán)語句的用法 
begin atomic
 declare fullname char(40);
 for vl as
   select firstnme, midinit, lastname from employee
  do
  set fullname = lastname concat ','
    concat firstnme concat ' ' concat midinit;
  insert into tnames values (fullname);
 end for
end
-- leave的用法
create procedure leave_loop(out counter integer)
 language sql
 begin
   declare v_counter integer;
   declare v_firstnme varchar(12);
   declare v_midinit char(1);
   declare v_lastname varchar(15);
   declare at_end smallint default 0;
   declare not_found condition for sqlstate '02000';
   declare c1 cursor for
  select firstnme, midinit, lastname
    from employee;
   declare continue handler for not_found
  set at_end = 1;
   set v_counter = 0;
   open c1;
   fetch_loop:
   loop
  fetch c1 into v_firstnme, v_midinit, v_lastname;
  if at_end <> 0 then leave fetch_loop;
  end if;
  set v_counter = v_counter + 1;
   end loop fetch_loop;
   set counter = v_counter;
   close c1;
 end
-- if語句的用法
   create procedure update_salary_if
     (in employee_number char(6), inout rating smallint)
     language sql
     begin
       declare not_found condition for sqlstate '02000';
       declare exit handler for not_found
         set rating = -1;
       if rating = 1
         then update employee
         set salary = salary * 1.10, bonus = 1000
         where empno = employee_number;
       elseif rating = 2
         then update employee
         set salary = salary * 1.05, bonus = 500
         where empno = employee_number;
       else update employee
         set salary = salary * 1.03, bonus = 0
         where empno = employee_number;
       end if;
     end
-- loop的用法
   create procedure loop_until_space(out counter integer)
     language sql
     begin
       declare v_counter integer default 0;
       declare v_firstnme varchar(12);
       declare v_midinit char(1);
       declare v_lastname varchar(15);
       declare c1 cursor for
         select firstnme, midinit, lastname
           from employee;
       declare continue handler for not found
         set counter = -1;
       open c1;
       fetch_loop:
       loop
         fetch c1 into v_firstnme, v_midinit, v_lastname;
         if v_midinit = ' ' then
           leave fetch_loop;
         end if;
         set v_counter = v_counter + 1;
       end loop fetch_loop;
       set counter = v_counter;
       close c1;
     end
-- return的用法
   begin
   ...
     goto fail
   ...
     success: return 0
     fail: return -200
   end
-- set變量 的用法
set new_var.salary = 10000, new_var.comm = new_var.salary;
or:
set (new_var.salary, new_var.comm) = (10000, new_var.salary);
set (new_var.salary, new_var.comm)
  = (select avg(salary), avg(comm)
    from employee e
    where e.workdept = new_var.workdept);
-- whenever的用法
   exec sql whenever sqlerror goto handlerr;
   exec sql whenever sqlwarning continue;
   exec sql whenever not found go to enddata;
-- while的用法
   create procedure dept_median
     (in deptnumber smallint, out mediansalary double)
     language sql
     begin
       declare v_numrecords integer default 1;
       declare v_counter integer default 0;
       declare c1 cursor for
         select cast(salary as double)
           from staff
           where dept = deptnumber
           order by salary;
       declare exit handler for not found
         set mediansalary = 6666;
       set mediansalary = 0;
       select count(*) into v_numrecords
         from staff
         where dept = deptnumber;
       open c1;
       while v_counter < (v_numrecords / 2 + 1) do
         fetch c1 into mediansalary;
         set v_counter = v_counter + 1;
       end while;
       close c1;
     end
-- set schema的用法
set schema rick
 
-- DB2保留關鍵字
add                deterministic  leave         restart
after              disallow       left          restrict
alias              disconnect     like          result
all                distinct       linktype      result_set_locator
allocate           do             local         return
allow              double         locale        returns
alter              drop           locator       revoke
and                dsnhattr       locators      right
any                dssize         lock          rollback
application        dynamic        lockmax       routine
as                 each           locksize      row
associate          editproc       long          rows
asutime            else           loop          rrn
audit              elseif         maxvalue      run
authorization      encoding       microsecond   savepoint
aux                end            microseconds  schema
auxiliary          end-exec       minute        scratchpad
before             end-exec1      minutes       second
begin              erase          minvalue      seconds
between            escape         mode          secqty
binary             except         modifies      security
bufferpool         exception      month         select
by                 excluding      months        sensitive
cache              execute        new           set
call               exists         new_table     signal
called             exit           no            simple
capture            external       nocache       some
cardinality        fenced         nocycle       source
cascaded           fetch          nodename      specific
case               fieldproc      nodenumber    sql
cast               file           nomaxvalue    sqlid
ccsid              final          nominvalue    standard
char               for            noorder       start
character          foreign        not           static
check              free           null          stay
close              from           nulls         stogroup
cluster            full           numparts      stores
collection         function       obid          style
collid             general        of            subpages
column             generated      old           substring
comment            get            old_table     synonym
commit             global         on            sysfun
concat             go             open          sysibm
condition          goto           optimization  sysproc
connect            grant          optimize      system
connection         graphic        option        table
constraint         group          or            tablespace
contains           handler        order         then
continue           having         out           to
count              hold           outer         transaction
count_big          hour           overriding    trigger
create             hours          package       trim
cross              identity       parameter     type
current            if             part          undo
current_date       immediate      partition     union
current_lc_ctype   in             path          unique
current_path       including      piecesize     until
current_server     increment      plan          update
current_time       index          position      usage
current_timestamp  indicator      precision     user
current_timezone   inherit        prepare       using
current_user       inner          primary       validproc
cursor             inout          priqty        values
cycle              insensitive    privileges    variable
data               insert         procedure     variant
database           integrity      program       vcat
day                into           psid          view
days               is             queryno       volumes
db2general         isobid         read          when
db2genrl           isolation      reads         where
db2sql             iterate        recovery      while
dbinfo             jar            references    with
declare            java           referencing   wlm
default            join           release       write
defaults           key            rename        year
definition         label          repeat        years
delete             language       reset
descriptor         lc_ctype       resignal
 
-- SQL99關鍵字
absolute       describe        module      session
action         destroy         names       session_user
admin          destructor      national    sets
aggregate      diagnostics     natural     size
are            dictionary      nchar       smallint
array          domain          nclob       space
asc            equals          next        specifictype
assertion      every           none        sqlexception
at             exec            numeric     sqlstate
bit            false           object      sqlwarning
blob           first           off         state
boolean        float           only        statement
both           found           operation   structure
breadth        grouping        ordinality  system_user
cascade        host            output      temporary
catalog        ignore          pad         terminate
class          initialize      parameters  than
clob           initially       partial     time
collate        input           postfix     timestamp
collation      int             prefix      timezone_hour
completion     integer         preorder    timezone_minute
constraints    intersect       preserve    trailing
constructor    interval        prior       translation
corresponding  large           public      treat
cube           last            real        true
current_role   lateral         recursive   under
date           leading         ref         unknown
deallocate     less            relative    unnest
dec            level           role        value
decimal        limit           rollup      varchar
deferrable     localtime       scope       varying
deferred       localtimestamp  scroll      whenever
depth          map             search      without
deref          match           section     work
desc           modify          sequence    zone
 
--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

 

    本站是提供個人知識管理的網(wǎng)絡存儲空間,所有內(nèi)容均由用戶發(fā)布,不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導購買等信息,謹防詐騙。如發(fā)現(xiàn)有害或侵權內(nèi)容,請點擊一鍵舉報。
    轉(zhuǎn)藏 分享 獻花(0

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多

    激情亚洲内射一区二区三区| 日韩人妻有码一区二区| 精品香蕉一区二区在线| 国产不卡免费高清视频| 免费久久一级欧美特大黄孕妇| 欧美精品亚洲精品日韩专区| 日本精品啪啪一区二区三区| 视频一区二区三区自拍偷| 都市激情小说在线一区二区三区| 福利专区 久久精品午夜| 五月天综合网五月天综合网| 色婷婷国产熟妇人妻露脸| 日韩精品免费一区二区三区| 中文字幕亚洲视频一区二区| 国产精品超碰在线观看| 国产精品亚洲欧美一区麻豆| 国产精品丝袜一二三区| 在线观看中文字幕91| 东京热加勒比一区二区三区| 国产精品香蕉在线的人| 久久国产亚洲精品成人| 无套内射美女视频免费在线观看| 国产成人亚洲综合色就色| 可以在线看的欧美黄片| 日韩一本不卡在线观看| 欧美一级黄片欧美精品| 少妇人妻精品一区二区三区 | 又黄又硬又爽又色的视频| 最近中文字幕高清中文字幕无 | 果冻传媒精选麻豆白晶晶| 国产高清精品福利私拍| 丰满人妻少妇精品一区二区三区| 丁香六月啪啪激情综合区| 大伊香蕉一区二区三区| 尤物久久91欧美人禽亚洲| 国产精品免费不卡视频| 久久精品国产亚洲av麻豆| 亚洲综合激情另类专区老铁性| 日韩偷拍精品一区二区三区| 国产精品午夜一区二区三区| 激情五月天免费在线观看|