DB2常用SQL的写法(

上一篇 / 下一篇 2007-08-01 00:19:25 / 个人分类：数据库

8ioTr+\.I$RJq F;p+?0来源于网络51Testing软件测试网7o:o:f"e^J1N2W

DB2常用SQL的写法(持续更新中...)51Testing软件测试网 b{n t:jZ
-- Author: lavasoft51Testing软件测试网}k%R ]bFu
-- Date : 2006-12-14

PvV9[)@\0-- 创建一个自定义单值类型
.['n7?/K*xvr0create distinct type var_newtype
x"]~P8O+jI s&f~ [;^0 as decimal(5,2) with comparisons;
z P})l0RS0 -- var_newtype 类型名
-- decimal(5,2) 实际的类型51Testing软件测试网 \3~aZ1y_@

-I/cq-F8X3c7Rk^0-- 删除一个自定义单值类型
BqD^ |Nn0drop distinct type var_newtype;
~6{a ?y!DOso0
-- 创建一个自定义结构数据类型
create type my_type as(
:G8B4z4dV PD0 username varchar(20),
(C%g`I$X0gqk&gD0 department integer,51Testing软件测试网0|EJdK
salary decimal(10,2))
;x3LD.A!H-T+h0 not final
(p4U}U(Rx5fp0 mode db2sql;

Mr#h+b'j8cR0-- 修改自定义结构数据类型,我目前还没有发现删除属性的方法.
7@8WIx_vmGA0alter type my_type
add attribute hiredate date;
"U%TFe\+})I IHU0
.a(Z%[2^0K0-- 删除自定义结构数据类型
y V| S2fY {c){E0drop type my_type;

n7Yyc9R0-- 获取系统当前日期
select current date from sysibm.sysdummy1;
C~6Q|8W!]"R!w+y;@0select current time from sysibm.sysdummy1;
\Z#~:{(C5P6e.t'Z h0select current timestamp from sysibm.sysdummy1;

--sysibm.sysdummy1表是一个特殊的内存中的表，用它可以发现如上面演示的 DB2 寄存器的值。您也可以使用关键字 VALUES 来对寄存器或表达式求值。51Testing软件测试网@qT,u;F%?C7r
VALUES current date;51Testing软件测试网&j%EWn"E
VALUES current time;51Testing软件测试网&W cOl Rff n
VALUES current timestamp;51Testing软件测试网)vQ{'a_sh

%_w5Aea N3K@0-- VALUES的更多用法
3o/Nw&b^r;@e0VALUES 2+5;
VALUES 'hello lavasoft!';
'Kyv5H YXd"B0
R!?F*C\r0values 56
union all
values 45;
+mkE[6s)l LK o$^0
values 1,2,3,4,5,6
union all
values 7,8,9,10,11,12
order by 1;

-- 更多变态级DB2 SQL写法,AnyOneTable表示任意一个存在的表
3V.\7WsM LV0select 234 from AnyOneTable;
"}9E+]{'x~p&mx"j0select distinct 234 from AnyOneTable;51Testing软件测试网tYR} TP
select distinct 234 as 1 from AnyOneTable;
+q$y!u2O!z;qv4Xp]G0
select 'DB2变态级的SQL哈哈' from AnyOneTable;51Testing软件测试网 t,m@"uo M;E
select distinct 'DB2变态级的SQL哈哈' from AnyOneTable;
select distinct 'DB2变态级的SQL哈哈' as 1 from AnyOneTable;
     --(嘿嘿,好玩吧,你可以用任意一个表来当sysibm.sysdummy1用.不过不推荐这么做,除非你不记得sysibm.sysdummy1怎么写了,Oracle中(对应dual)也一样!哈哈哈哈!)
}2T+hB"M2qf j0 51Testing软件测试网A*J(B@/|_
-- 定义变量,还可以设定默认值,给变量赋值51Testing软件测试网7l1Z-T6KMMN
declare var1 char(2);51Testing软件测试网0ldf0~?
declare var2 int default 0;
set var1 = 'aa';
h+I kY9N/MV0set var2 =23;
U{5CU7B[0
P%C}/QQ0--创建一个动态游标变量
&t)d&T[AJHc`0declare d_cur integer;
1j y$D4e yq ck0
wzt.B"_gZ?0-- 给变量赋值的另一种方法
&x"B:^;wk _!UO!OQ1]0values expr1, expr2, expr3 into a, b, c;51Testing软件测试网p#h~n9s+z{
-- 相当于
set a = expr1;51Testing软件测试网3Fc*Z kr9H9G
set b = expr2;51Testing软件测试网f4WKL a8M
set c = expr3;
SJ^%Pk0
e5U$ldd,AGO0-- 还有一种赋值方式
set prodname = (case
)uwQ$Y hu0                  when (name is not null) then name
3z:yhK5|*FK0                  when (namestr is not null) then namestr
                  else defaultname51Testing软件测试网Zm6?9y7Y+I
                end);
%m&u!ez(~nB8B)d/k0 -- 相当于51Testing软件测试网H QO D"_p1_
set prodname = coalesce(name, namestr, defaultname);51Testing软件测试网}1hW$vH}
--这个类似oracle的decode()和nvl()函数的合并.

$B1X1NGOaV/c0-- 定义一个游标
+\RW;C7gr0declare cur1 cursor with return to client for select * from dm_hy;
Z$e"D'Xk3Evx0declare cur2 cursor for select * from dm_hy; -- 静态游标

-- 创建数据表,并添加注释,插入数据.
Y6HY'O9x8}M0CREATE TABLE tbr_catalog (51Testing软件测试网2t@4H!Np
id bigint not null generated by default as identity,
_3A9iw)n0 type smallint not null,
q(aU)\Osh0 name varchar(255),
parentid bigint,
cataloglevel bigint,
)\ }^in/D:E `q0 descrīption varchar(255),
Z6F;C)HD;OI'u mr0 PRIMARY KEY (id)51Testing软件测试网!G$}}y2v5z[
);
*DFc S\ L0 comment on table tbr_catalog is 'Birt报表目录表';
m L"e I`6SE0 comment on column tbr_catalog.ID is '标识';
comment on column tbr_catalog.type is '目录类型';
c2Zu'N D0 comment on column tbr_catalog.name is '目录名称';
Nxsn~#HR0 comment on column tbr_catalog.parentid is '目录父标识';
,o#`:ZyE0 comment on column tbr_catalog.cataloglevel is '目录层次';
h4T&M_3C&E0 comment on column tbr_catalog.descrīption is '目录描述';51Testing软件测试网4\ [ UB$]Gd
-- 给数据表插入数据
OC9RVY3si0insert into tbr_catalog(id, type, name, parentid, cataloglevel, descrīption)
8@@1ba.Dt0values (1, 0, '系统报表', 0, 0, '');
z+J `)i:pq0F"l7\0insert into tbr_catalog(id, type, name, parentid, cataloglevel, descrīption)
r3E cd\0values (2, 1, '用户报表', 0, 0, '');51Testing软件测试网4v?8Fh"K3}

e'ZA:MyJ0-- 创建外键51Testing软件测试网n?8pd r
alter table tbr_storage51Testing软件测试网!RqkTjoC
add constraint fk_tbr_storage51Testing软件测试网Yz%Ve.a{h
foreign key (catalogid)
/^1aqk;c0 references tbr_catalog(id);51Testing软件测试网'U4j iw};ji&|

3jO;i8g/r&{U9r"F0-- 更改表,添加列51Testing软件测试网/fFh]6c2ip
alter table aaa add sex varchar(1);

:cR!}/Z9`0L0-- 更改表,删除列51Testing软件测试网9C#Hxh"Db
alter table aaa drop column sex;
,q8Y&v[C*^0
jbo;]#\~/u0-- 去掉参数前后的空格
Y2sJK5j6s8P0rtrim(dm_hy.mc);51Testing软件测试网/GJ;bU*]ls

-- 定义临时表,通过已有person表来创建51Testing软件测试网o-cx2J1t3a-z
declare global temporary table gbl_temp
like person
on commit delete rows --提交时删除数据
8p+N{*u~Gu0not logged -- 不在日志中纪录
` d yvCih-T8itK0in usr_tbsp -- 选用表空间
)tm*}k/^-{q0 -- 此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表所使用的列的名称和说明与 person 的列的名称和说明完全相同。51Testing软件测试网(N,{{j!T/\

-- 创建有两个字段的临时表
1HNl"y^d|h0    -- 定义一个全局临时表tmp_hy51Testing软件测试网&Hm2M%]%^)c4K
declare global temporary table session.tmp_hy
X']-qEd0    (
KYUs"X0       dm varchar(10),51Testing软件测试网#or%v"H6a
       mc varchar(10)
ATVHz@(}0    )51Testing软件测试网l1Vm\7r,DlP
     with replace -- 如果存在此临时表，则替换
     not logged; -- 不在日志里纪录
    -- 给临时表插入三条数据
    insert into session.tmp_hy values('1','1');51Testing软件测试网%_7GDD'm6] m
    insert into session.tmp_hy values('1','1');51Testing软件测试网/p*w@#|$qC
    insert into session.tmp_hy values('1','1');
,F'pc uZ`7|SuPw;F0
NI,n$~5{S F&{+E0-- 通过查询批量插入数据
inster into tab_bk(select code,name from table book);
*m!E:E7[W-x#U/^sd$NA0
Y:V5g Ihg0-- select ... into的用法
5`$EByWO_N%Fc0select * into :h1, :h2, :h3, :h4
'x*I;w4pG,Q9{`4W[v0     from emp
S#o:_-MF#es&py0     where empno = '528671';
\#V!Ah5Cg*Wn0
-- 语句的流程控制
_0^*c)t's^}0if() then
open cur151Testing软件测试网j-[ rK]
fetch cur1 into t_equipid;
yl*W4qY0 while(at_end<>1)do
Z4`D6U3}g)X0 ......
7vOddihAS0 set t_temp=0;
end while;
close cur1;
else51Testing软件测试网M)M-?f6_H
......
*KGNPcu!uO0end if;51Testing软件测试网K'\6_*?3J#Y9]

ej(s{kRu8D0-- 外连接51Testing软件测试网 sz&id$?jy
select empno,deptname,projname51Testing软件测试网S1]sBM;N&?
from (emplyoee
sc:qHj9v)JK0 left outer join project51Testing软件测试网^,l+f*I2AB
on respemp=empon)51Testing软件测试网zV^.lg]]
left outer join department
Uh2P'_N0 on mgrno=empno;51Testing软件测试网 |:_Cx4M
51Testing软件测试网1a8kVXpJ#N
-- in、like、order by(... ASC|DESC)的用法
select * from book t51Testing软件测试网jL$]#Z$Iw
where t.name like '%J_编程%'
MF-x dALF(GD{T0and t.code in（'J565333','J565222');51Testing软件测试网0[-t(R'|O%X#i
order by t.name asc51Testing软件测试网S@7{ ?O)u+n

ZAyP&\)k:w*`0-- 汇总表(概念复杂，难以理解，不常用）51Testing软件测试网hi4{a(r+\ p
create summary table sumy_stable1
Qb0cP/?+_l7R0 as (select workdept,
y2h'x:@9k-z2W0d0    count(*) as reccount,51Testing软件测试网mf"oYo
    sum(salary) as salary,51Testing软件测试网)x`3cC s3I
    sum(bonus) as bonus
+VF4F2b3G#d` x;R? n0 from employee group by workdept)
&Da)jGV0 data initially deferred
s#z5GQO*G0 refresh immediate;51Testing软件测试网0vP4]4[cl?

-- 使用SQL一次处理一个集合语义51Testing软件测试网7XYs0G7h `
-- （优化前） select语句中每行的过程层和数据流层之间都有一个上下文切换51Testing软件测试网f|1R;|u\
declare cur1 cursor for col1,col2 from tab_comp;
r0Dn'og7_0open cur1;
$rZIh ttN0fetch cur1 into v1,v2;
]P3yh_7]0while SQLCODE<> 100 do
if (v1>20) then51Testing软件测试网}J_U2\ mk
insert into tab_sel values(20,v1);
o'H&jJ}D N0 else
3aJ'd:w\2X9r0 insert into tab_sel values(v1,v2);51Testing软件测试网cG {]Zru
end if;51Testing软件测试网,LR xG RQ3rl
fetch cur1 into v1,v2;51Testing软件测试网/?d*t{X^E
end while;
X \$N*lZX}l0
-- （优化后）没有过程层和数据流层之间的上下文切换
declare cur1 cursor for col1,col2 from tab_comp;
open cur1;51Testing软件测试网!jfn?$w.S
fetch cur1 into v1,v2;
IuV C-b]%\0while SQLCODE<> 100 do
)m{w,p-|%}0 insert into tab_sel(select (case
NQV^n:d(o0         when col1>20 then 20
            else col151Testing软件测试网B4D$Tn!cp
        end),
4NF v0m.Kj_Q;p4v0        col251Testing软件测试网A"`7];TN9}e
      from tab_comp);
t:[M4ox1M0 fetch cur1 into v1,v2;
%k#p,^`3_f^(_7]0end while;51Testing软件测试网H6Hzv'ZG

-- DB2函数分三类：列函数、标量函数、表函数
~,`8{9_hq5}#BD0-- 列函数输入一组数据，输出单一结果。
,SJ;BXc0-- 标量函数接收一个值，返回另外一个值。
-- 表函数只能用于SQL语句的from字句中，它返回一个表的列，类似于一个已创建的常规表。51Testing软件测试网x$Y6R,}v bk

-- 下面是个标量函数的例子。
%_ezTfn0create function (salary int,bonus_percent int)
returns int
"v7JC0I+i,d.l;K)H0v0language SQL contains SQL
]"Z&Mq"f-~7_l0return(
wdk*eRD N0 salary * bonus_percent/100
)

2Ev!gT(d5nb"t)[L"v,d0-- 下面是表函数
*LN,Cg'Y0create function get_marks(begin_range int,end_range int)51Testing软件测试网8v%}0etyv#OE
returns table(cid candidate_id,
       number test_id,
9^m/D0\Q'E;t0       score score)51Testing软件测试网k#I#]*bL,oT
language SQL reads SQL DATA51Testing软件测试网-i[!GwCVj
return51Testing软件测试网L;g5a?M$W
    select cid,number,score
(o,RB\8P:o4qxLR0    from test_taken
8{"B t"Zvn4l0    where salary between (begin_range) and score(end_range)
51Testing软件测试网S B*| S-] T

L(d8^1Mt;P z0example 1: define a scalar function that returns the tangent of a value using the existing sine and cosine functions.51Testing软件测试网C5vV1E)Rf
   create function tan (x double)51Testing软件测试网9x S:nW7Umj$g
     returns double
+VsyF1l0     language sql
     contains sql51Testing软件测试网rSd8_z'S8c
     no external action
3f!V&DZ9UFV0     deterministic
VZ$]7en/n(S!E:]&I0     return sin(x)/cos(x)

b0FiL?-h4r:T0example 2: define a transform function for the structured type person.
qA*c*h2lfA}e,lF1m9^8f0    51Testing软件测试网-L#P$OS3D'L3R
   create function fromperson (p person)51Testing软件测试网/UcLTkF8~W
     returns row (name varchar(10), firstname varchar(10))
y#W/QY$I7{ K0     language sql51Testing软件测试网*F9My9\4|#p7aF
     contains sql
*xhI-I Tz0     no external action
O2Z`Cz!~)M0     deterministic
3Zc j1w%|0     return values (p..name, p..firstname)

example 3: define a table function that returns the employees in a specified department number.
x]w0XgqX0
3GYr7k~'Z%GS0W!u0   create function deptemployees (deptno char(3))51Testing软件测试网&kQfz#DGs)T
     returns table (empno char(6),
                    lastname varchar(15),
                    firstname varchar(12))51Testing软件测试网)y0g&M8pSv"@
     language sql51Testing软件测试网3[@E1Tz,r
     reads sql data51Testing软件测试网dLT g\8^1T
     no external action51Testing软件测试网&MuV9K3Vq#ZY
     deterministic
%u%d fd"b N2Q;e'p:pok?F0     return
4YC2AfO G0       select empno, lastname, firstnme51Testing软件测试网5k-l5j+N)K%r N
         from employee51Testing软件测试网on"Vco
         where employee.workdept = deptemployees.deptno

lO,O3L,l,M0| |6RP0example 4: define a scalar function that reverses a string.
"~3pf#nXe7WE0   create function reverse(instr varchar(4000))
&g&?ib$[]0     returns varchar(4000)
y*G1H2~"QG0     deterministic no external action contains sql
     begin atomic
     declare revstr, reststr varchar(4000) default '';
/r8xSf:q%z7g)|+_X0     declare len int;
     if instr is null then
n{A`(b1N.z!L0     return null;51Testing软件测试网 kvaD3AE
     end if;51Testing软件测试网+Ej!K)oG0U
     set (reststr, len) = (instr, length(instr));
     while len > 0 do
     set (revstr, reststr, len)51Testing软件测试网p]5[1D;US
       = (substr(reststr, 1, 1) concat revstr,51Testing软件测试网c+s*`N5P
       substr(reststr, 2, len - 1),
zl%Xhi|}y0       len - 1);
8P5aRo;r"K0     end while;51Testing软件测试网^OWN ne/j
     return revstr;
t rz G{ }v$b$oYc0   end

R)l/J5r})gQo9Od.U0example 4: define the table function from example 4 with auditing.
M@.c*HV0   create function deptemployees (deptno char(3))51Testing软件测试网Zg-`8tb]!P
     returns table (empno char(6),
Hm!pZU+s$^{0                    lastname varchar(15),
X#V3dt?g k0                    firstname varchar(12))51Testing软件测试网Vh#u!rP!D
     language sql
     modifies sql data
     no external action51Testing软件测试网S_;O| p
     deterministic
6[~.vjvc!v0     begin atomic
,o$Y)rSd0       insert into audit51Testing软件测试网"xh/ko7rZ;r
       values (user,
               'table: employee prd: deptno = ' concat deptno);
cU*Ce9tW+feE0       return
         select empno, lastname, firstnme51Testing软件测试网7b@,]f"X2I1B"M
           from employee
{x5Xu0P I8x?O0           where employee.workdept = deptemployees.deptno
     end
ty_g*h|Y0
;Qkv0cX!F_0-- for循环语句的用法
2E&sC"Raz_-jHW;?0begin atomic51Testing软件测试网)S'x)V4Y5@
declare fullname char(40);
,IF G;](QB0 for vl as51Testing软件测试网7s[rR,f
   select firstnme, midinit, lastname from employee51Testing软件测试网1F't9d|/c
do
(y{)sC#W1j }d9t0 set fullname = lastname concat ','51Testing软件测试网L;q p$H^
    concat firstnme concat ' ' concat midinit;
zbQ xs$t0 insert into tnames values (fullname);
`d&kc"Aqu0 end for
end
51Testing软件测试网PKutf5YB
-- leave的用法
create procedure leave_loop(out counter integer)
.rh K R H[8E,r0 language sql
begin
V%FXou;X6v:T0   declare v_counter integer;51Testing软件测试网!u\"j$`Ak
   declare v_firstnme varchar(12);
0W/|a)GX.C.sY%P6\y0   declare v_midinit char(1);
   declare v_lastname varchar(15);
   declare at_end smallint default 0;51Testing软件测试网tgI-f$D
   declare not_found condition for sqlstate '02000';
zVV)u s0C:vZ4y0   declare c1 cursor for
select firstnme, midinit, lastname51Testing软件测试网 @$t'~BX0FUR
    from employee;51Testing软件测试网igNP3A;v(h
   declare continue handler for not_found51Testing软件测试网!c8Yh'Ua&q
set at_end = 1;51Testing软件测试网?8f6xvd6ie
   set v_counter = 0;
   open c1;
9nZ3@U+PPs4m4i0   fetch_loop:
   loop
R!v1GF+v,m"P0 fetch c1 into v_firstnme, v_midinit, v_lastname;51Testing软件测试网$J#bK] ] dR
if at_end <> 0 then leave fetch_loop;51Testing软件测试网1o kN"U"x
end if;51Testing软件测试网%MRSg8A pM
set v_counter = v_counter + 1;51Testing软件测试网X%| `(\ ?;U
   end loop fetch_loop;
:S(Ii*^;fq1u8`l0   set counter = v_counter;
   close c1;51Testing软件测试网L#]2Y jr\
end

3I B1q"@&Mr2Ra0-- if语句的用法
   create procedure update_salary_if51Testing软件测试网EV)ji0y
     (in employee_number char(6), inout rating smallint)
     language sql51Testing软件测试网.y k,OK7HM_
     begin
5Guw x |"\,{C7qO3R0       declare not_found condition for sqlstate '02000';
       declare exit handler for not_found
jv y9S[0         set rating = -1;
s6uG O1By0NF&z0       if rating = 1
         then update employee
         set salary = salary * 1.10, bonus = 1000
4\1|8M1|6JN5V0         where empno = employee_number;51Testing软件测试网MpJ R7uaP
       elseif rating = 2
         then update employee
ttr*R6e/O'L\$A0         set salary = salary * 1.05, bonus = 500
         where empno = employee_number;
S*~gf:U [%lE0       else update employee51Testing软件测试网2P8y4{(t.Q*{
         set salary = salary * 1.03, bonus = 0
*Gr+J%gd,x D0         where empno = employee_number;
8~Tlh QYE0       end if;51Testing软件测试网2Yj,yA}
     end51Testing软件测试网*TW b6p-{

9E7db5@Z)Q~"@0-- loop的用法51Testing软件测试网y"e?\*}
   create procedure loop_until_space(out counter integer)
'SFNoD0     language sql
D u"_&v.v0     begin
TLC5F/c N:Tc"O0       declare v_counter integer default 0;
       declare v_firstnme varchar(12);
ThP&cm;Mx(f(R0       declare v_midinit char(1);51Testing软件测试网2L&VIq`&opV
       declare v_lastname varchar(15);
       declare c1 cursor for51Testing软件测试网+z"MG'\'z(}E
         select firstnme, midinit, lastname
           from employee;51Testing软件测试网Z(jX` Zri
       declare continue handler for not found
~.v4?b:Css0         set counter = -1;
_]8xN7G0       open c1;
       fetch_loop:
e:Y#?oa.O0       loop
]P#uo/N0V/G2b0         fetch c1 into v_firstnme, v_midinit, v_lastname;51Testing软件测试网 I&{:}Yar;P^#B
         if v_midinit = ' ' then
           leave fetch_loop;
TSs,I9iE @0         end if;
"j:nl-HM+c0         set v_counter = v_counter + 1;
XN!w;J7z1V[0       end loop fetch_loop;
       set counter = v_counter;
!j-fn4P:`4O0       close c1;
2d h{ B.z7K0     end
p!AbJbj,W1r0
U"HH#DP"c0-- return的用法51Testing软件测试网+A9A/q4q(B%d
   begin51Testing软件测试网(wh0z,~5dbL
   ...
?1? O4qT&T k3E0     goto fail
   ...
     success: return 0
qPlb/Rh`S0     fail: return -200
u9F5SA9NF9v'NZ*d0   end
K3^W5VZ0 51Testing软件测试网/} EP5N,w
-- set变量的用法
hu1Egq0set new_var.salary = 10000, new_var.comm = new_var.salary;
or:
_3w_#g8G:O0set (new_var.salary, new_var.comm) = (10000, new_var.salary);51Testing软件测试网j7jm v7B%}&G
set (new_var.salary, new_var.comm)51Testing软件测试网&Obk&s2P4q7P
= (select avg(salary), avg(comm)
    from employee e51Testing软件测试网,yFK4}2aF+G
    where e.workdept = new_var.workdept);51Testing软件测试网0Gyu8nvc3E n
51Testing软件测试网v/CFw-u/cC Q
-- whenever的用法51Testing软件测试网 |;S KN7M
   exec sql whenever sqlerror goto handlerr;
a4haer#|5l|0   exec sql whenever sqlwarning continue;
   exec sql whenever not found go to enddata;
r WLQD+w|,o0 51Testing软件测试网"|a,NV&~F/y
-- while的用法51Testing软件测试网G9bCx2Zn7Q
   create procedure dept_median
     (in deptnumber smallint, out mediansalary double)
     language sql51Testing软件测试网V`q4N9F0\
     begin
       declare v_numrecords integer default 1;
(v0atWl wyzq0l7r+E0       declare v_counter integer default 0;
       declare c1 cursor for
         select cast(salary as double)
'C#Tp^'i,O/^?o0           from staff51Testing软件测试网!@1NK/[2J
           where dept = deptnumber
yQ+P'S q ]0           order by salary;51Testing软件测试网1v(I#zy,k!\k
       declare exit handler for not found51Testing软件测试网X2Y b@T"V
         set mediansalary = 6666;51Testing软件测试网y1E)B vd
       set mediansalary = 0;51Testing软件测试网/E!]7{|)d_OO
       select count(*) into v_numrecords51Testing软件测试网"R.t;d_YxV
         from staff
         where dept = deptnumber;
       open c1;
wey4~n-@*dj2j8hU0       while v_counter < (v_numrecords / 2 + 1) do
;W x0i)iA|m1S;l0         fetch c1 into mediansalary;
B{-T,wM%Y w;J;G&|!k0         set v_counter = v_counter + 1;
       end while;
8h$h1V.`o+k+L2rd0       close c1;51Testing软件测试网#t]%oDx4O
     end
1m^Y` CLK(C*g0 51Testing软件测试网L0KZ)~x*t2Xh
-- set schema的用法
set schema rick
w:j(@6n!{6g"Z0
n4Fv9s&r2s0-- DB2保留关键字
Pue8F A0add                deterministic leave         restart51Testing软件测试网;ZnI!B+Xk
after              disallow       left          restrict51Testing软件测试网GX-m#B(L2ET
alias              disconnect     like          result
i*[ wq'AD0all                distinct       linktype      result_set_locator
7zxB;C!yS7C:_h0allocate           do             local         return
k+K;C0H:kt(U0allow              double         locale        returns
alter              drop           locator       revoke
(~FdEJ1k"T Z z0and                dsnhattr       locators      right
&]'Ro$i/gZ0any                dssize         lock          rollback
]._d4f!}0application        dynamic        lockmax       routine51Testing软件测试网\U D!wfR
as                 each           locksize      row
5Mp(s'X+~.x#L0associate          editproc       long          rows
asutime            else           loop          rrn
6eb3`&J9P!^~J8T]~0audit              elseif         maxvalue      run
authorization      encoding       microsecond   savepoint51Testing软件测试网"O'n#R:q$Ow:_
aux                end            microseconds schema51Testing软件测试网DgvRe
auxiliary          end-exec       minute        scratchpad
UUP-~!D6k7@yK0before             end-exec1      minutes       second
xO,TD1Y.FQTr0begin              erase          minvalue      seconds
between            escape         mode          secqty
g?;HW:D_N@JL0binary             except         modifies      security
m,H:hw:L;l$g0bufferpool         exception      month         select
6@dr?P'Q,Cx0by                 excluding      months        sensitive
.R3z4|Z;al:a0cache              execute        new           set
call               exists         new_table     signal
lq_b6qFlk0called             exit           no            simple
-_,O7Ot@7U1V*T tK0capture            external       nocache       some
?+gVsuN5?:g0cardinality        fenced         nocycle       source51Testing软件测试网.d!w;F"Z)JMGV
cascaded           fetch          nodename      specific
]-M$W_Ga2}9fLC0case               fieldproc      nodenumber    sql
r C)g(bc'L0cast               file           nomaxvalue    sqlid
n|j`#q'?0ccsid              final          nominvalue    standard51Testing软件测试网#K ]1UV m8x
char               for            noorder       start
-fd'k q#k!T/p`^0character          foreign        not           static
check              free           null          stay
}:s![1J&R];EB,@0close              from           nulls         stogroup51Testing软件测试网t[N6s:I+cuy
cluster            full           numparts      stores
'A+i)s#K:q}0collection         function       obid          style51Testing软件测试网5Ns Ej(}(aw
collid             general        of            subpages
vHx;U|/J:Sf0column             generated      old           substring51Testing软件测试网p-eX8gO*{
comment            get            old_table     synonym
commit             global         on            sysfun
^+ML*eW^s6l*q0concat             go             open          sysibm
sIt`X+x+IzIhg[0condition          goto           optimization sysproc51Testing软件测试网*e+v,J*\*JH{
connect            grant          optimize      system
*a$t2O2\] ~i0connection         graphic        option        table51Testing软件测试网0a0d&p*L-uc3K
constraint         group          or            tablespace
UHO'g@iT9U@ j p0contains           handler        order         then51Testing软件测试网3J-Q2s/d&O{
continue           having         out           to
5C]]5btb7h2K0count              hold           outer         transaction
count_big          hour           overriding    trigger
oR\4O%s0B2oP0create             hours          package       trim51Testing软件测试网^ |:S~8WM
cross              identity       parameter     type
/z rl3l X,n*Z0current            if             part          undo
#PUu7f1c"E WM \S0current_date       immediate      partition     union
2iH8_-L3O&n.T)b|0current_lc_ctype   in             path          unique
current_path       including      piecesize     until51Testing软件测试网ZrP.fO
current_server     increment      plan          update
)|6|H_*f9Yt/HhfG]I0current_time       index          position      usage
4\H-Bg/`q'b.JM5X'T1}{0current_timestamp indicator      precision     user
current_timezone   inherit        prepare       using
U;Vw5@2B5A5sq rj0current_user       inner          primary       validproc51Testing软件测试网,\H"@,y2cb3uM
cursor             inout          priqty        values
cycle              insensitive    privileges    variable
8L'hRe1O.`Z5^_A0data               insert         procedure     variant51Testing软件测试网j#\-j*h:]+C
database           integrity      program       vcat
5S-gvk&Fub8}yt0day                into           psid          view51Testing软件测试网1u:{/hvd
days               is             queryno       volumes
db2general         isobid         read          when
c+X&\tjC"Gd0db2genrl           isolation      reads         where51Testing软件测试网.V6v2gDo
db2sql             iterate        recovery      while
;vs^v h'S"ih0dbinfo             jar            references    with
b swD$X_0declare            java           referencing   wlm
cX!d.BH3Z0K0default            join           release       write
defaults           key            rename        year
Rf;lG9P"I(r#I v#j0definition         label          repeat        years
-W:W7i3V] I0delete             language       reset51Testing软件测试网E$| x#OK"_&I
descrīptor         lc_ctype       resignal
D%PcSd7I/|\0
jh \7B8`\3ZY6V0-- SQL99关键字51Testing软件测试网2O-vjof/q+C
absolute       describe        module      session
-P*i[#[7Rx0action         destroy         names       session_user51Testing软件测试网ZDL hV"tk
admin          destructor      national    sets
aggregate      diagnostics     natural     size
!rrO.A't#X&H$^H0are            dictionary      nchar       smallint
array          domain          nclob       space
r+tNafF0asc            equals          next        specifictype
assertion      every           none        sqlexception
l~?H,ELH0at             exec            numeric     sqlstate
{-\|K y(b0bit            false           object      sqlwarning51Testing软件测试网n|S+~#q^
blob           first           off         state
boolean        float           only        statement
q~/C/J2i&H F2W |0both           found           operation   structure51Testing软件测试网I'g3{f${
breadth        grouping        ordinality system_user51Testing软件测试网 K"B@a+eh_
cascade        host            output      temporary
L'}{Wq VcY0catalog        ignore          pad         terminate51Testing软件测试网6B'z B9\Q7EB
class          initialize      parameters than51Testing软件测试网}-R1d0D4K
clob           initially       partial     time
_8r:E^*F(I4Fs0collate        input           postfix     timestamp
collation      int             prefix      timezone_hour
[1bb"l p&X$JR k0completion     integer         preorder    timezone_minute
0A&\ O&D1LL0{y%X#L0constraints    intersect       preserve    trailing
constructor    interval        prior       translation51Testing软件测试网h'd x(n8_H
corresponding large           public      treat
_*?6fp%VI&_9f)B0cube           last            real        true
r,g_1z/[dI P0current_role   lateral         recursive   under51Testing软件测试网6}[4Ilv
date           leading         ref         unknown
deallocate     less            relative    unnest51Testing软件测试网[ T9sKs2C!z
dec            level           role        value
0A%fg.@+V!J![0decimal        limit           rollup      varchar
x-[vR+T#r-T kWp0deferrable     localtime       scope       varying51Testing软件测试网!~0C,zu_B(r[
deferred       localtimestamp scroll      whenever
C$Z ]CX"d0depth          map             search      without51Testing软件测试网uz[fIq0y
deref          match           section     work
+Im0mRT\*{9t0desc           modify          sequence    zone
a)Y0F9nYT.b:D!B0
"Z]g)K+k0--create type (结构化的)用法51Testing软件测试网vrA0V&F?g
   create type dept as
uN1]Frul_8z'Z~ I%Y0      (dept name     varchar(20),51Testing软件测试网v j9l;zv
         max_emps int)
!yC,ge`F&n+O0         ref using int
1`%ax/J%z'| ]L x0      mode db2sql

   create type emp as51Testing软件测试网Yz'St"OqL
     (name      varchar(32),51Testing软件测试网!m]/Xx-Qo;W(z
     serialnum int,
     dept      ref(dept),
#S4W3h [ |yK1Eer0     salary    decimal(10,2))51Testing软件测试网-R)m WP m @
     mode db2sql
ipI0~%MdH${N,eh0
4k.Bq2nH#K&|.qT _0   create type mgr under emp as
     (bonus     decimal(10,2))51Testing软件测试网f2}M(TaRB
     mode db2sql

)` i$Q8ufO9x~0   create type address_t as
     (street     varchar(30),
     number     char(15),
pM7Z7WR*YC"t0     city       varchar(30),
Wa.pg)mL)l0     state      varchar(10))
g9TG%p9{~l u0     not final51Testing软件测试网f%?yz ZZ;yn
     mode db2sql51Testing软件测试网5`-g!k4o:vv
       method samezip (addr address_t)51Testing软件测试网(\e%Yk-yS @e
       returns integer
       language sql51Testing软件测试网I*~R1d0or%i
       deterministic
M/YXxJN3U]Q0       contains sql51Testing软件测试网 ~GBbGH?6A
       no external action,51Testing软件测试网b?^+U?J;{z
       method distance (address_t)51Testing软件测试网 zH3f?+syJ'n
       returns float
2G J-a:D(O3XM#QAl1n0       language c
       deterministic
9`8B~Y!GB0       parameter style sql
[1X~]P0       no sql
       no external action
i`6_1hS$Y `0
Qa:Uo2uBGh0   create type germany_addr_t under address_t as
     (family_name varchar(30))
2}K1b SE7\wy0     not final
5F1z2Vw)D!^Yh0     mode db2sql

   create type us_addr_t under address_t as
1rb6w bk0     (zip varchar(10))
     not final
     mode db2sql

   create type project as
;PX"dXwZP7@4c0     (proj_name varchar(20),
Wup-iViqg2h0      proj_id    integer,
@ Ek.Jv&u(u9m0      proj_mgr   mgr,
      proj_lead emp,
      location   addr_t,51Testing软件测试网F*L!o5BT3a.]X
      avail_date date)
      mode db2sql

SvivU%DV!V8@0 51Testing软件测试网C#W)A6`G1z*]
-- create type mapping的用法51Testing软件测试网(U_ K l6nCZb
create type mapping my_oracle_date
Hjl8~7I)SK0 from local type sysibm.date
to server type oracle51Testing软件测试网:U:ab,Uv%u4T
remote type date
5Mt2@)Ww&`_^0
;QP!`#h1l!X|6F0create type mapping my_oracle_dec
/Q m`~+]f*?B-f%\0 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()51Testing软件测试网4hjR hp6h
to server oracle1
remote type char()

u:\m_f`T0create type mapping my_oracle_dec
to local type sysibm.decimal(10,2)
x l0ZX2b3b-u0 from server oracle251Testing软件测试网"|&t*UfT2No
remote type number(10,2)
;?6l)fhsu.j0 51Testing软件测试网1v.~&N/X)P6D
-- create user mapping的用法
y9L5y+{d0create user mapping for rspalten
server server39051Testing软件测试网.Z8N,xtP:t
options
*F xtxNuI0 (remote_authid 'system',
remote_password 'manager')51Testing软件测试网 KCN;?3dJ*~

B\/U9U(T| h0create user mapping for marcr51Testing软件测试网SfW*X3Kz
server oracle151Testing软件测试网7W4r;j'Fz0j
options
(remote_password 'nzxczy')

8r3E3] r.q+R0-- case的用法
W4|pV2?/xy0case v_workdept51Testing软件测试网+SR2i(OJ
when'a00'
.H_H3Ga5Ev0    then update department
    set deptname = 'data access 1';51Testing软件测试网#q {oBJ
when 'b01'
},N~u,e0    then update department
    set deptname = 'data access 2';
Z+r$P,VR^0G0 else update department
    set deptname = 'data access 3';
B],N'\'p*w0end case

B fI-\f%M0case
(^6J0g%Dn_w0 when v_workdept = 'a00'
&v{C/r~.t4u9n0    then update department
&Ar~me?Z6H!x#Y;p0    set deptname = 'data access 1';51Testing软件测试网Sr$QL Ir
when v_workdept = 'b01'51Testing软件测试网wD E!QS2r1y
    then update department
UJ0bSt/]hR.V3b4g,J0    set deptname = 'data access 2';
Ko V~0]v!F0 else update department
    set deptname = 'data access 3';
gEtiLA0end case51Testing软件测试网8v^C,\%GOKi

-- create trigger的用法
)Qw,j*c*v I0create trigger new_hired
after insert on employee51Testing软件测试网d(Z^9u:D
for each row
qB8l4g;N}0 update company_stats set nbemp = nbemp + 1
L4T}4P P7v'Lt0
p{a u%A7q]0create trigger former_emp
after delete on employee51Testing软件测试网b"t$e:CM8g/`*|
for each row
update company_stats set nbemp = nbemp - 151Testing软件测试网:Uu[ a ]S"e
51Testing软件测试网X4N_8w^9xv/V
create trigger reorder
after update of on_hand, max_stocked on parts
referencing new as n
for each row
#](SdE&T3N X0 when (n.on_hand < 0.10 * n.max_stocked)
9K-Eq&K vT0 begin atomic
G&sKYZi0 values(issue_ship_request(n.max_stocked - n.on_hand, n.partno));51Testing软件测试网8X QKxv~
end51Testing软件测试网.S#B|)f/S

V,G\&GXoA(T pT0create trigger raise_limit
v;D#L&J`7f7B0 after update of salary on employee
w\A&aw2d0 referencing new as n old as o51Testing软件测试网 uxJ`!Q
for each row
(@dlM6O0 when (n.salary > 1.1 * o.salary)
q/u_%p1_*S&Wa0         signal sqlstate '75000' set message_text='salary increase>10%'51Testing软件测试网1HewFiN6a

BFL;Q3A|6g;t0create trigger stock_status
no cascade before update of quote on currentquote51Testing软件测试网J{Z3_sQ5_K
referencing new as newquote old as oldquote
for each row
2ZKM*|dM0 begin atomic
(?&~pG$n#qp@Q0     set newquote.status =
5vp f}:K#f*E0       case
SW'B5N'P.K0          when newquote.quote >51Testing软件测试网/P8fb$P0sY
                (select max(quote) from quotehistory51Testing软件测试网$mRT.n#u'K gi
                where symbol = newquote.symbol
                and year(quote_timestamp) = year(current date) )
6h'V6Irv\0             then 'high'
/gl'}+{&s_0sL0          when newquote.quote < (select min(quote) from quotehistory
                where symbol = newquote.symbol
3@8~/CcG0                and year(quote_timestamp) = year(current date) )
             then 'low'
*l%dJ4O5k'A:m!}0          when newquote.quote > oldquote.quote
             then 'rising'
"bR)}nI/D0          when newquote.quote < oldquote.quote
7HE*u8DM-D7}G0             then 'dropping'51Testing软件测试网'S E?VB3k']
          when newquote.quote = oldquote.quote51Testing软件测试网 A8N(cS:VFo
             then 'steady'51Testing软件测试网 k:r&D"`FQ:G
       end;
!vX4a._s"C-WR1k0 end
k-I`$s:YF!f!XK0
create trigger record_history
3\T+|O#\0 after update of quote on currentquote
(J[1I4O5zx0 referencing new as newquote
/X;wy^AW|0 for each row
begin atomic
8z!d2{x'R yN ~0    insert into quotehistory51Testing软件测试网%y2{4iv.s Qn
      values (newquote.symbol, newquote.quote, current timestamp);51Testing软件测试网.v N%t%?2a
end

-- create tablespace 的用法
S2[}]u([{0create tablespace payroll51Testing软件测试网(As+T#q.x.br
managed by database
U$`*} Zv&yyw#}0 using (device'/dev/rhdisk6' 10000,
Ck[0~6b&}(? ~*l0 device '/dev/rhdisk7' 10000,51Testing软件测试网D,s zis!z
device '/dev/rhdisk8' 10000)
tQNQU'eJ4k0 overhead 12.6751Testing软件测试网J'V HG[;l^S
transferrate 0.18
/X-zFS/E,yM$t0
k,Be]X}OV\4n~/w0create tablespace accounting
r4s_ z3ccGZ0 managed by system
using ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
{Q0H7lx)^9\y0 extentsize 64
6\? T%U4v~0 prefetchsize 32

create tablespace plans
1vJ&h"t:l0 managed by database
using (device '/dev/rhdisk0' 10000, device '/dev/rn1hd01' 40000)
F&EdXp0 on dbpartitionnum (1)
using (device '/dev/rhdisk0' 10000, device '/dev/rn3hd03' 40000)
on dbpartitionnum (3)51Testing软件测试网SS#\ z-Z
using (device '/dev/rhdisk0' 10000, device '/dev/rn5hd05' 40000)51Testing软件测试网v\;s"P;p7Ni
on dbpartitionnum (5)51Testing软件测试网it+G~o3j

)sO/Xo,n F!}u/K0 51Testing软件测试网uS/X'M [esI
-- 带case查询条件语句
;b5G"B t2wZ0select (case b.organtypecode
.CquVzMi+\M5|O0         when 'D' then
%Y/L@)e-F\8VJ WRpi(rk0          b.parent51Testing软件测试网l]-M-k,o.t r
         when 'S' then
rZG/Xl8F;q _Z0          b.parent
         else
-U\KVV@e(M0          b.id
7}7[2}|/xgi0       end),
       b.name51Testing软件测试网9SbGnk X)I
from A_ORGAN b
%f.z.V*[(g2mb0 where b.id = 999

51Testing软件测试网8hKmgS-u2f

用户菜单

我的栏目

标题搜索

日历

数据统计

访问量: 40829
日志数: 75
图片数: 1
建立时间: 2007-03-24
更新时间: 2009-12-29

DB2常用SQL的写法(

相关阅读:

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅