深圳专业建站公司,网站制作全包多少钱,全球搜 建设网站,南京网站建设 w-- 创建一个自定义单值类型create distinct type var_newtypeas decimal(5,2) with comparisons;-- var_newtype 类型名-- decimal(5,2) 实际的类型-- 删除一个自定义单值类型drop distinct type var_newtype;-- 创建一个自定义结构数据类型create type my_type as(username …-- 创建一个自定义单值类型create distinct type var_newtypeas 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 finalmode db2sql;-- 修改自定义结构数据类型,我目前还没有发现删除属性的方法.alter type my_typeadd 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的更多用法VALUES25;VALUES hello lavasoft!;values 56union allvalues 45;values 1,2,3,4,5,6union allvalues 7,8,9,10,11,12order 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 (casewhen (name is not null) then namewhen (namestr is not null) then namestrelse defaultnameend);-- 相当于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_storageadd constraint fk_tbr_storageforeign 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_templike personon commit delete rows --提交时删除数据not logged -- 不在日志中纪录in usr_tbsp -- 选用表空间-- 此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表 所使用的列的名称和说明与 person 的列的名称和说明完全相同。-- 创建有两个字段的临时表-- 定义一个全局临时表tmp_hydeclare 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, :h4from empwhere empno 528671;-- 语句的流程控制if() thenopen cur1fetch cur1 into t_equipid;while(at_end1)do......set t_temp0;end while;close cur1;else......end if;-- 外连接select empno,deptname,projnamefrom (emplyoeeleft outer join projecton respempempon)left outer join departmenton mgrnoempno;-- in、like、order by(... ASC|DESC)的用法select * from book twhere t.name like %J_编程%and t.code in(J565333,J565222);order by t.name asc-- 汇总表(概念复杂难以理解不常用)create summary table sumy_stable1as (select workdept,count(*) as reccount,sum(salary) as salary,sum(bonus) as bonusfrom employee group by workdept)data initially deferredrefresh immediate;-- 使用SQL一次处理一个集合语义-- (优化前) select语句中每行的过程层和数据流层之间都有一个上下文切换declare cur1 cursor for col1,col2 from tab_comp;open cur1;fetch cur1 into v1,v2;while SQLCODE 100 doif (v120) theninsert into tab_sel values(20,v1);elseinsert 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 doinsert into tab_sel(select (casewhen col120 then 20else col1end),col2from tab_comp);fetch cur1 into v1,v2;end while;-- DB2函数分三类列函数、标量函数、表函数-- 列函数输入一组数据输出单一结果。-- 标量函数接收一个值返回另外一个值。-- 表函数只能用于SQL语句的from字句中它返回一个表的列类似于一个已创建的常规表。-- 下面是个标量函数的例子。create function (salary int,bonus_percent int)returns intlanguage SQL contains SQLreturn(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 DATAreturnselect cid,number,scorefrom test_takenwhere 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 doublelanguage sqlcontains sqlno external actiondeterministicreturn 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 sqlcontains sqlno external actiondeterministicreturn 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 sqlreads sql datano external actiondeterministicreturnselect empno, lastname, firstnmefrom employeewhere 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 sqlbegin atomicdeclare revstr, reststr varchar(4000) default ;declare len int;if instr is null thenreturn null;end if;set (reststr, len) (instr, length(instr));while len 0 doset (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 sqlmodifies sql datano external actiondeterministicbegin atomicinsert into auditvalues (user,table: employee prd: deptno concat deptno);returnselect empno, lastname, firstnmefrom employeewhere employee.workdept deptemployees.deptnoend-- for循环语句的用法begin atomicdeclare fullname char(40);for vl asselect firstnme, midinit, lastname from employeedoset fullname lastname concat ,concat firstnme concat concat midinit;insert into tnames values (fullname);end forend-- leave的用法create procedure leave_loop(out counter integer)language sqlbegindeclare 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 forselect firstnme, midinit, lastnamefrom employee;declare continue handler for not_foundset at_end 1;set v_counter 0;open c1;fetch_loop:loopfetch 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 sqlbegindeclare not_found condition for sqlstate 02000;declare exit handler for not_foundset rating -1;if rating 1then update employeeset salary salary * 1.10, bonus 1000where empno employee_number;elseif rating 2then update employeeset salary salary * 1.05, bonus 500where empno employee_number;else update employeeset salary salary * 1.03, bonus 0where empno employee_number;end if;end-- loop的用法create procedure loop_until_space(out counter integer)language sqlbegindeclare v_counter integer default 0;declare v_firstnme varchar(12);declare v_midinit char(1);declare v_lastname varchar(15);declare c1 cursor forselect firstnme, midinit, lastnamefrom employee;declare continue handler for not foundset counter -1;open c1;fetch_loop:loopfetch c1 into v_firstnme, v_midinit, v_lastname;if v_midinit thenleave 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 0fail: return -200end
