DB2可以用递归函数吗 db2 递归查询

DB2—03（DB2中常见基础操作 ➕ DB2实现递归查询 ➕ DB2中自定义递归函数）

• 1. 前言

• 1.1 DB2中常用操作——修改字段、约束等
• 1.2 附：oracle和mysql相关

• 2. db2中的"dual"

• 2.1 SYSIBM.SYSDUMMY1
• 2.2 使用VALUES
• 2.3 SYSIBM.SYSDUMMY1 "变" dual

• 3. db2中常用函数

• 3.1 nvl()、value()、COALESCE()
• 3.2 NULLIF() 函数
• 3.3 LISTAGG() 与 xml2clob()、xmlagg()

• 4. DB2中自定义函数

• 4.1 简单入门函数

• 4.1.1 语法结构
• 4.1.2 例子

• 4.1.2.1 例子1——求两数和
• 4.1.2.2 例子2——自定义等差数列的n项和

• 4.2 返回table的自定义函数
• 4.3 自定义递归查询函数

• 5. DB2中使用with实现递归查询
• 6. 解决 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的可以看下面的文章：

• Oracle 常用简单sql操作（insert into、merge into、start with connect by prior以及 regexp_substr等各种函数用法详解）.
• Oracle自定义函数、Oracle存储过程多种用法讲解以及动态创建表的存储过程.
• Oracle中分割字符串的方法.
• oracle递归查询（start with connect by prior）以及 树形统计connect_by_root（子节点汇总到父节点）.
• Oracle自定义函数实现递归查询（用自定义函数替换connect_by_root）.
• 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 表名')