DB2—03(DB2中常见基础操作 ➕ DB2实现递归查询 ➕ DB2中自定义递归函数)
1. 前言
1.1 DB2中常用操作——修改字段、约束等
- 常见语句如下:
--1. 添加字段(需要注意的是:添加非空字段时要指定默认值) --语法:ALTER TABLE 表名 ADD 新字段名 字段类型及长度; ALTER TABLE dog_2 ADD dog_sex varchar(2); ALTER TABLE dog_2 ADD dog_sex varchar(2) DEFAULT '0' NOT null; --2. 添加注释 --2.1 表注释 COMMENT ON TABLE dog_2 IS 'dog备份表'; --2.2 字段注释 COMMENT ON COLUMN dog_2.dog_sex IS '狗狗性别'; --3. 修改字段 --语法:ALTER TABLE 表名 ALTER 要修改的字段名 SET DATA TYPE 要修改的字段类型及长度; ALTER TABLE dog_2 ALTER dog_sex SET DATA TYPE CHAR(1); --4. 删除字段 ALTER TABLE dog_2 DROP COLUMN dog_sex; --5. 添加非空约束(注意:只有表中没有任何数据 或者 该字段没有非空数据时,才可以给该字段设置 not null 约束) ALTER TABLE dog_2 ALTER dog_sex SET NOT NULL; --6. 给字段添加默认值(注意:字段不用设置not null也可以直接设置默认值) ALTER TABLE dog_2 ALTER COLUMN dog_sex SET DEFAULT '1'; --7. 删除非空约束(注:并不删除默认值设置) ALTER TABLE dog_2 ALTER dog_sex drop NOT NULL; --8. 删除默认值 ALTER TABLE dog_2 ALTER dog_sex drop DEFAULT; --9. 添加主键 --9.1 添加主键——一个字段(注意:添加时数据库里此字段不能有重复的值) ALTER TABLE dog_2 ADD CONSTRAINT pk_dog_2_id PRIMARY key(dog_id); --9.2 添加主键——关联主键(注意:关联主键的两个字段前提必须都有非空约束,没有的话,先添加非空约束) ALTER TABLE dog_2 ADD CONSTRAINT pk_dog_2_id PRIMARY key(dog_id,dog_name); --10. 删除主键 ALTER TABLE dog_2 DROP PRIMARY KEY;
1.2 附:oracle和mysql相关
- 部分语法和oracle、mysql都大同小异、关于oracle和mysql的可以看下面的文章:
2. db2中的"dual"
2.1 SYSIBM.SYSDUMMY1
- db2中没有这个表,在db2中类似dual表的是
SYSIBM.SYSDUMMY1
,效果如下:SELECT 1+1 FROM SYSIBM.SYSDUMMY1; SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;--查询当前日期
2.2 使用VALUES
- 如果嫌 SYSIBM.SYSDUMMY1 麻烦,可以使用
VALUES
命令获取结果,如下:VALUES (3+2); values length('abc'); values CURRENT DATE;
2.3 SYSIBM.SYSDUMMY1 “变” dual
- 如果你习惯了dual,那就为
SYSIBM.SYSDUMMY1
创建别名dual,如下:CREATE ALIAS dual FOR SYSIBM.SYSDUMMY1; --创建别名 SELECT 5+7 FROM dual;
3. db2中常用函数
3.1 nvl()、value()、COALESCE()
- 先说一下
nvl()
nvl()
,语法如下:nvl(val1,val2)
- 功能:
如果val1为空(注意:这里的空是null
,不算空字符串),则返回val2,否则返回val1本身,例子如下:SELECT nvl(null,0),nvl(234,0),nvl('','aa') FROM SYSIBM.SYSDUMMY1;
- 注意:
其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。 value()
、COALESCE()
用法同nvl()
- 再说一下
nvl2()
SELECT nvl2(null,0,1),nvl2(234,0,1),nvl2('','不空','空') FROM SYSIBM.SYSDUMMY1;
- 接着说一下
value()
、COALESCE()
value()
、COALESCE()
就简单,因为用法同nvl()
,给两个语句,自己下去测测看:SELECT value(null,0),value(56,0),value('','bb') FROM SYSIBM.SYSDUMMY1; SELECT COALESCE(null,0),COALESCE(56,0),COALESCE('','bb') FROM SYSIBM.SYSDUMMY1;
- 最后选哪个,个人觉得还是用
nvl()
和nvl2()
吧,除了熟悉之外还有就是以后换数据库的话也好移植。
3.2 NULLIF() 函数
- 如果相同返回NULL,否则返回第一个参数,如下:
3.3 LISTAGG() 与 xml2clob()、xmlagg()
- 关于这三个函数的使用,如下:
DB2中实现数据字段的拼接(LISTAGG() 与 xml2clob、xmlagg).
4. DB2中自定义函数
4.1 简单入门函数
4.1.1 语法结构
4.1.2 例子
4.1.2.1 例子1——求两数和
- 代码如下(end后面不用结束符合):
create or replace function fun_sum_number(num1 bigint,num2 bigint) returns bigint BEGIN declare v_result bigint; SET v_result = num1 + num2; return v_result; END
- 测试如下:
values(fun_sum_number(1,5));
4.1.2.2 例子2——自定义等差数列的n项和
- 求
最小数、最大数以及步长
确定的等差数列的n项和,实现代码如下:CREATE OR REPLACE FUNCTION fun_all_num_sum(start_num bigint, end_num bigint, step_num bigint) RETURNS bigint LANGUAGE SQL BEGIN DECLARE loop_start bigint; DECLARE total_sum bigint; SET loop_start = start_num; SET total_sum = 0; WHILE loop_start <= end_num DO SET total_sum = total_sum + loop_start; --step_num 步长 SET loop_start = loop_start + step_num; END WHILE; RETURN total_sum; END
- 效果如下:
SELECT fun_all_num_sum(1,3,1),fun_all_num_sum(1,4,1),fun_all_num_sum(2,8,2) FROM SYSIBM.SYSDUMMY1 ;
4.2 返回table的自定义函数
- 代码如下:
CREATE OR REPLACE FUNCTION fun_query_dog_by_id(dogId varchar(10)) RETURNS TABLE( DOG_ID varchar(10), dog_name varchar(10), dog_kind varchar(10) ) RETURN SELECT DOG_ID, dog_name, dog_kind FROM dog WHERE dog.DOG_ID = fun_query_dog_by_id.dogId; --或者直接 WHERE dog.DOG_ID = dogId; --WHERE dog.DOG_ID = dogId;
- 测试如下:
- 原表数据
- 使用函数查询
SELECT * FROM table(fun_query_dog_by_id('A10001'));
- 原表数据
4.3 自定义递归查询函数
- 先看原始数据结构
- 根据部门ID找公司ID,函数实现如下:
CREATE OR REPLACE FUNCTION fun_get_company_id_by_dept_id(v_dept_id varchar(10)) RETURNS varchar(10) LANGUAGE SQL BEGIN DECLARE dept_level bigint; DECLARE loop_dept_id varchar(10); DECLARE result_company_id varchar(10); select t.DEPT_LEVEL into dept_level from sys_company_dept t where t.dept_id = v_dept_id; --如果本身就是公司,直接返回,如果是部门循环找上级直到找到公司ID SET loop_dept_id = v_dept_id; WHILE dept_level >= 2 DO SELECT t1.PARENT_ID ,t2.DEPT_LEVEL into loop_dept_id,dept_level FROM sys_company_dept t1 LEFT JOIN sys_company_dept t2 ON t1.PARENT_ID = t2.DEPT_ID where t1.dept_id = loop_dept_id; END WHILE; SET result_company_id = loop_dept_id; RETURN result_company_id; END
- 效果如下:
- 递归查询部门及对应的公司列表,如下:
SELECT temp.*,t2.DEPT_NAME AS company_name FROM ( SELECT t1.*,fun_get_company_id_by_dept_id(t1.DEPT_ID) AS company_id FROM sys_company_dept t1 )temp LEFT JOIN sys_company_dept t2 ON temp.company_id = t2.DEPT_ID
5. DB2中使用with实现递归查询
- 如果嫌自定义递归函数麻烦的话,也可以直接写sql实现递归,如下,要查
B001及其下的所有部门
,使用with实现递归查询如下:WITH temp(dept_id,dept_name,parent_id) AS ( SELECT dept_id,dept_name,parent_id FROM SYS_COMPANY_DEPT WHERE dept_id='B001' UNION ALL SELECT t1.dept_id,t1.dept_name,t1.parent_id FROM SYS_COMPANY_DEPT AS t1, temp AS t2 WHERE t1.parent_id=t2.dept_id ) SELECT dept_id,dept_name,parent_id FROM temp;
6. 解决 db2-表 处于暂挂状态
- 有时当对表数据进行操作时,表锁了,处于暂挂状态,如果其他解决方法不能解决的话可以尝试用以下语句进行解锁,命令语句如下:
call sysproc.admin_cmd('reorg table 表名')