1. 前言

1.1 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
  BEGINdeclare 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 bigintLANGUAGE SQLBEGINDECLARE loop_start bigint;DECLARE total_sum bigint;SET loop_start = start_num;SET total_sum = 0;WHILE loop_start <= end_num DOSET 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
  BEGINDECLARE 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;--如果本身就是公司，直接返回，如果是部門循環找上級直到找到公司IDSET loop_dept_id = v_dept_id;WHILE dept_level >= 2 DOSELECT t1.PARENT_ID ,t2.DEPT_LEVEL into loop_dept_id,dept_level FROM sys_company_dept t1LEFT 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-表 處于暫掛狀態

• 有時當對表數據進行操作時，表鎖了，處于暫掛狀態，如果其他解決方法不能解決的話可以嘗試用以下語句進行解鎖，命令語句如下：

  call sysproc.admin_cmd('reorg table 表名')