DB2數值函數簡介及使用
學習一種數據庫，最開始我們都是從其內部函數開始學習的，這些內部函
數對於我們編寫SQL有很大的幫助；
本文雖然介紹的不全，但能夠起到一個引導作用，具體信息以官網為准。
1、取絕對值的函數：abs、absval
參數個數：1
參數類型：smallint,integer,bigint數據類型；也可以為null，此時返回值也為null。
db2 => select abs(-123),abs(null),abs('-100') from sysibm.sysdummy1
1 2 3
----------- ------------------------------------------ ------------------------------------------
123 - 100
1 條記錄已選擇。
db2 => select abs(NaN)||' '||abs(sNaN)||' '||abs(Infinity) from sysibm.sysdummy1
1
----------------------
NAN SNAN INFINITY
1 條記錄已選擇。
2、向上取整：ceil、ceiling
db2 => select ceil(123.89),ceiling(123.49) from sysibm.sysdummy1
1 2
------- -------
124. 124.
1 條記錄已選擇。
如果ceil函數的參數為一個數值組成的字符串，如'123.45'，
此函數能夠將其轉換為一個浮點數，在進行取整操作。
db2 => select ceil(123.45),ceil('123.45') from sysibm.sysdummy1
1 2
------- ------------------------------------------
124. 124
1 條記錄已選擇。
3、向下取整數：floor
參數個數：1
參數類型：整數和浮點數，也可以是null，此時返回值為null。
db2 => select floor(123.56),floor(100),floor(-123.99) from sysibm.sysdummy1
1 2 3
------- ----------- -------
123. 100 -124.
1 條記錄已選擇。
db2 => select floor(null) from sysibm.sysdummy1
1
------------------------------------------
-
1 條記錄已選擇。
4、求M的n次冪：power(M,n)
返回值：
integer：若兩個參數都為integer或者smallint
bigint：若一個為bigint類型，另一個為bigint、integer或者smallint。
double：若兩個參數都為浮點數的話。
db2 => select power(3.2,2.1),power(3,2),power(3,0) from sysibm.sysdummy1
1 2 3
------------------------ ----------- -----------
+1.15031015682351E+001 9 1
1 條記錄已選擇。
5、隨機數：rand(m)函數
返回值：返回一個0和1之間的浮點數；若參數為null，結果也為null。
參數：類型為smallint或integer的數，范圍在0-2147483647之間的數，稱為種子。
db2 => select rand(),rand(500),rand(null) from sysibm.sysdummy1
1 2 3
------------------------ ------------------------ ------------------------
+2.43232520523698E-001 +5.09964293343913E-002 -
1 條記錄已選擇。
生成0-1的隨機數：
select rand() from sysibm.sysdummy1
生成0-n的隨機整數：
select cast(rand()*n as int) from sysibm.sysdummy1
db2 => values (cast(rand()*10 as int))
1
-----------
5
1 條記錄已選擇。
db2 => values (cast(rand()*10 as int))
1
-----------
1
1 條記錄已選擇。
生成n-m的隨機整數（m>n）：
select n+cast(rand()*(m-n) as int) from sysibm.sysdummy1
db2 => select 5+cast(rand()*(10-5) as int) from sysibm.sysdummy1
1
-----------
9
1 條記錄已選擇。
db2 => select 5+cast(rand()*(10-5) as int) from sysibm.sysdummy1
1
-----------
7
1 條記錄已選擇。
通過rand() 函數和fetch frist 能隨機取表中的行，從表中隨機取n行數據
（在fetch first中n必須為大於等於1的整數，否則報錯）：
select * from tb order by rand()
fetch first n rows only
db2 => select b.* from (select row_number() over () ,a.empno,
a.salary from employee a) b order by rand() fetch first 5 rows only
1 EMPNO SALARY
-------------------- ------ -----------
19 000210 68270.00
20 000220 49840.00
11 000130 73800.00
30 000320 39950.00
12 000140 68420.00
5 條記錄已選擇。
db2 => select b.* from (select row_number() over () ,a.empno,
a.salary from employee a) b order by rand() fetch first 5 rows only
1 EMPNO SALARY
-------------------- ------ -----------
18 000200 57740.00
13 000150 55280.00
17 000190 50450.00
15 000170 44680.00
33 200010 46500.00
5 條記錄已選擇。
6、四捨五入：round (m,n)
參數：m表示一個數值數據，整數或者浮點數，正負數都可以，正負數進位都向其ceil()方向。
n表示進位的數目，整數表示處理小數點後面的數，負數表示處理整數部分的數，integer類型。
實例：
db2 => values (round(873.726,2),round(873.726,1),
round(873.726,0),round(873.726,-1),round(873.726,-2),round(873.726,-3))
1 2 3 4 5 6
--------- --------- --------- --------- --------- ---------
873.730 873.700 874.000 870.000 900.000 1000.000
1 條記錄已選擇。
db2 => values (round(-745.5,-2),round(-745.5,-1))
1 2
------- -------
-700.0 -750.0
1 條記錄已選擇。
7、將數值轉換為字符串形式：digits()函數
參數：smallint，integer，bigint，decimal；
char或者varchar類型的數值字符串，在進行此函數操作之前，
字符串的值隱式轉換為decimal(31,6)。
參數可以為null，此時返回值為null。
返回值：返回數值的字符串形式，不足長度的自動在字符前補齊0。
返回固定長度的字符串，若參數為浮點數，小數點後面的也計算在內；
對於不足長度的，在整數的最前端補0，下面是實例。
employee表的結構如下，最後三個字段的長度為9，小數點位數為2：
db2 => describe table employee
數據類型 列
列名 模式 數據類型名稱 長 小數位 NULL
------------------------------- --------- ------------------- ---------- ----- ------
EMPNO SYSIBM CHARACTER 6 0 否
FIRSTNME SYSIBM VARCHAR 12 0 否
MIDINIT SYSIBM CHARACTER 1 0 是
LASTNAME SYSIBM VARCHAR 15 0 否
WORKDEPT SYSIBM CHARACTER 3 0 是
PHONENO SYSIBM CHARACTER 4 0 是
HIREDATE SYSIBM DATE 4 0 是
JOB SYSIBM CHARACTER 8 0 是
EDLEVEL SYSIBM SMALLINT 2 0 否
SEX SYSIBM CHARACTER 1 0 是
BIRTHDATE SYSIBM DATE 4 0 是
SALARY SYSIBM DECIMAL 9 2 是
BONUS SYSIBM DECIMAL 9 2 是
COMM SYSIBM DECIMAL 9 2 是
14 條記錄已選擇。
下面是digits函數的轉換情況：
select salary,digits(salary),bonus,digits(bonus),comm,digits(comm)
from employee fetch first 5 rows only
SALARY 2 BONUS 4 COMM 6
----------- --------- ----------- --------- ----------- ---------
152750.00 015275000 1000.00 000100000 4220.00 000422000
94250.00 009425000 800.00 000080000 3300.00 000330000
98250.00 009825000 800.00 000080000 3060.00 000306000
80175.00 008017500 800.00 000080000 3214.00 000321400
72250.00 007225000 500.00 000050000 2580.00 000258000
5 條記錄已選擇。
8、轉換為數值：to_number(string,format)
此函數為decfloat_format的別名。
參數：string為數值型的字符串，如：'-100.1','01234'等。
format為轉換後的數值型的呈現格式，有如下形式:
0 or 9：每一個數字代表一個字符。
MI：對於負數，將尾部的負數符號(-)移動到最前頭；
對於正數，將在其前面添加一個符號(+)或者空格。
S：如格式MI
PR：將一對尖括號(<>)包含起來的數視為負數，轉換後在其前面添加一個負數符號(-)。
$：必須是轉換字符串前面包括$符號。
，：逗號，數值分隔符
. ：點號，小數點標志。
db2 => values to_number('123.001') + 0.01
1
------------------------------------------
123.011
db2 => values to_number('123.456','000.000')
1
------------------------------------------
123.456
db2 => values to_number('123.456','000.000')
1
------------------------------------------
123.456
db2 => values to_number('987.123','999.999MI')
1
------------------------------------------
987.123
db2 => values to_number('<123.456>','000.000PR')
1
------------------------------------------
-123.456
db2 => values to_number('$123,456.78','$000,000.00')
1
------------------------------------------
123456.78
9、數值截取函數：trunc(m,n)或者truncate(m,n)
參數：m為數值型，整數或者浮點數；
n為截取長度，當n大於0，則截取小數部分，當小於0，則截取整數部分
返回值：對於指定數值m，截取n個長度的值。
db2 => values (trunc(873.726,2),trunc(873.726,1),trunc(873.726,0))
您想執行以上命令嗎？（y/n） y
1 2 3
-------- -------- --------
873.720 873.700 873.000
1 條記錄已選擇。
db2 => values (trunc(873.726,-1),trunc(873.726,-2),trunc(873.726,2),trunc(873.726,-3))
您想執行以上命令嗎？（y/n） y
1 2 3 4
-------- -------- -------- --------
870.000 800.000 873.720 0.000
1 條記錄已選擇。
10、轉換為浮點數：decfloat(m,16|34)
參數：m可以是數值型，也可以是字符串（有數值組成的字符串，如'123.98'）
返回值：將m轉換為長度為16或者34的浮點數，默認為34.
db2 => values (decfloat(123.45),decfloat('-123.45',16)+10)
1 2
------------------------------------------ ------------------------
123.45 -113.45
1 條記錄已選擇。
11、數值比較函數：compare_defloat(a,b)
參數：兩個參數都是decfloat(34)類型的浮點數，否則將會被自動轉換為decfloat(34)。
返回值：
0: a=b
1: a
compare_decfloat(decfloat(1.5),decfloat(1.50)))
1 2
------ ------
0 2
1 條記錄已選擇。
db2 => values (compare_decfloat(decfloat(1.770),decfloat(1.77)),compare_
decfloat(decfloat(NaN),decfloat(1.2)))
1 2
------ ------
1 3
1 條記錄已選擇。
官網上有下面這個實例：
COMPARE_DECFLOAT(DECFLOAT(2.17), DECFLOAT(2.17)) = 0
COMPARE_DECFLOAT(DECFLOAT(2.17), DECFLOAT(2.170)) = 2
COMPARE_DECFLOAT(DECFLOAT(2.170), DECFLOAT(2.17)) = 1
COMPARE_DECFLOAT(DECFLOAT(2.17), DECFLOAT(0.0)) = 2
COMPARE_DECFLOAT(INFINITY,INFINITY) = 0
COMPARE_DECFLOAT(INFINITY,-INFINITY) = 2
COMPARE_DECFLOAT(DECFLOAT(-2),INFINITY) = 1
COMPARE_DECFLOAT(NAN,NAN) = 3
COMPARE_DECFLOAT(DECFLOAT(-0.1),SNAN) = 3
12、normalize_decfloat(decfloat_number)
參數：能夠轉換為decfloat(16)的smallint，integer，real，double，
或者decimal(p,s)(p<=16)類型；
bigint或者decimal(p,s)(p>16)的將會被轉換為decfloat(34)。
返回值：將一個數值轉換為一個小數乘以10的n次冪的形式，
如1200轉換為1.2E3。
db2 => values (normalize_decfloat(decfloat(-1200)),normalize_
decfloat(decfloat(1.2000)))
1 2
------------------------------------------ ------------------------------------------
-1.2E+3 1.2
1 條記錄已選擇。
官網上有下面實例：
NORMALIZE_DECFLOAT(DECFLOAT(2.1)) = 2.1
NORMALIZE_DECFLOAT(DECFLOAT(-2.0)) = -2
NORMALIZE_DECFLOAT(DECFLOAT(1.200)) = 1.2
NORMALIZE_DECFLOAT(DECFLOAT(-120)) = -1.2E+2
NORMALIZE_DECFLOAT(DECFLOAT(120.00)) = 1.2E+2
NORMALIZE_DECFLOAT(DECFLOAT(0.00)) = 0
NORMALIZE_DECFLOAT(-NAN) = -NaN
NORMALIZE_DECFLOAT(-INFINITY) = -Infinity
還有很多系統函數，如數學函數，sin,cos,tan，exp等，
詳細信息參照官網：http://pic.dhe.ibm.com/infocenter/
db2luw/v9r7/index.jsp
位置：數據庫基礎 -> SQL -> Functions
