db2惯用SQL

db2常用SQL
DB2常用SQL的写法(持续更新中...)-- Author: lavasoft
-- Date? :? 2006-12-14?-- 创建一个自定义单值类型
create? distinct type var_newtype
?as decimal(5,2) with comparisons;?-- var_newtype 类型名
?-- decimal(5,2) 实际的类型?-- 删除一个自定义单值类型
drop distinct type var_newtype;?-- 创建一个自定义结构数据类型
create type my_type as(
?username varchar(20),
?department integer,
?salary decimal(10,2))
?not final
?mode db2sql;
-- 修改自定义结构数据类型,我目前还没有发现删除属性的方法.
alter type my_type
add attribute hiredate date;?-- 删除自定义结构数据类型
drop type my_type;
-- 获取系统当前日期
select current date from sysibm.sysdummy1;
select current time from sysibm.sysdummy1;
select current timestamp from sysibm.sysdummy1;
?--sysibm.sysdummy1表是一个特殊的内存中的表，用它可以发现如上面演示的 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;-- 更多变态级DB2 SQL写法,AnyOneTable表示任意一个存在的表select 234 from AnyOneTable;
select distinct 234 from AnyOneTable;
select distinct 234 as 1 from AnyOneTable;?select 'DB2变态级的SQL哈哈' from AnyOneTable;
select distinct 'DB2变态级的SQL哈哈' from AnyOneTable;
select distinct 'DB2变态级的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;?--创建一个动态游标变量
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()函数的合并.
-- 定义一个游标
declare cur1 cursor with return to client for select * from dm_hy;
declare cur2 cursor for select * from dm_hy; -- 静态游标
-- 创建数据表,并添加注释,插入数据.
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 '目录描述';?-- 给数据表插入数据
insert into tbr_catalog(id, type, name, parentid, cataloglevel, description)
values (1, 0, '系统报表', 0, 0, '');
insert into tbr_catalog(id, type, name, parentid, cataloglevel, description)
values (2, 1, '用户报表', 0, 0, '');?-- 创建外键
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;?-- 去掉参数前后的空格
rtrim(dm_hy.mc);
-- 定义临时表,通过已有person表来创建
declare global temporary table gbl_temp
like person
on commit delete rows --提交时删除数据
not logged -- 不在日志中纪录
in usr_tbsp -- 选用表空间
?-- 此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表 所使用的列的名称和说明与 person 的列的名称和说明完全相同。?-- 创建有两个字段的临时表?
??? -- 定义一个全局临时表tmp_hy
declare global temporary table session.tmp_hy
??? (
?????? dm varchar(10),
?????? mc varchar(10)????????
??? )
???? with replace?-- 如果存在此临时表，则替换
???? not logged; ?-- 不在日志里纪录??? -- 给临时表插入三条数据
??? insert into session.tmp_hy values('1','1');
??? insert into session.tmp_hy values('1','1');
??? insert into session.tmp_hy values('1','1'); ?-- 通过查询批量插入数据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一次处理一个集合语义
-- （优化前） select语句中每行的过程层和数据流层之间都有一个上下文切换
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;?-- （优化后）没有过程层和数据流层之间的上下文切换
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函数分三类：列函数、标量函数、表函数
-- 列函数输入一组数据，输出单一结果。
-- 标量函数接收一个值，返回另外一个值。
-- 表函数只能用于SQL语句的from字句中，它返回一个表的列，类似于一个已创建的常规表。
-- 下面是个标量函数的例子。
create function (salary int,bonus_percent int)
returns int
language SQL contains SQL
return(
?salary * bonus_percent/100
)
-- 下面是表函数
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.deptnoexample 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;
?? endexample 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循环语句的用法?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