1 --Function主体
2 CREATE FUNCTION [dbo].[FnMoneyStyle](@Number float,@strType char(1))
3 RETURNS VARCHAR(100)
4 AS
5 BEGIN
6 DECLARE @ReturnValue varchar(100)
7 SET @ReturnValue=0
8 SET @Number=ISNULL(@Number,0)
9 --千分
10 IF @strType = '1' --千分 无小数点
11 SET @ReturnValue= left(convert(varchar,convert(money,@Number),1),len(convert(varchar,convert(money,@Number),1))-3)
12 IF @strType = '2' --千分 两位小数点
13 SET @ReturnValue= convert(varchar,convert(money,@Number),1)
14 IF @strType = '5' --千分 两位小数点 去掉.00
15 SET @ReturnValue=REPLACE(convert(varchar,convert(money,@Number),1),'.00', '')
16 --非千分
17 IF @strType = '3' --四位小数
18 SET @ReturnValue= convert(varchar,convert(money,@Number),2)
19 IF @strType = '4' --去掉.00
20 SET @ReturnValue=REPLACE(convert(varchar,convert(money,@Number)),'.00', '')
21
22 RETURN @ReturnValue
23 END
24
25 --Function测试结果:
26 SELECT dbo.FnMoneyStyle(300000.00,1)--返回结果:300,000
27 SELECT dbo.FnMoneyStyle(300000.00,2)--返回结果:300,000.00
28 SELECT dbo.FnMoneyStyle(300000.00,3)--返回结果:300000.0000
29 SELECT dbo.FnMoneyStyle(300000.00,4)--返回结果:300000
1 --function主体:
2 create or replace function FnMoneyStyle
3 (
4 fNumber float,
5 strType varchar2
6 ) return varchar2
7 as
8 ReturnValue varchar2(4000):=0;
9 begin
10
11 --千分
12 IF strType = '1' THEN --千分 无小数点
13 SELECT SUBSTR
14 (
15 TRIM(DECODE(fNumber,0,'0.00',TO_CHAR(fNumber,'999,999,999,999,999.99')))
16 ,1
17 ,LENGTH(TRIM(DECODE(fNumber,0,'0.00',TO_CHAR(fNumber,'999,999,999,999,999.99'))))-3
18 ) INTO ReturnValue
19 FROM DUAL;
20 END IF;
21
22 IF strType = '2' THEN --千分 两位小数点
23 SELECT TRIM(DECODE(fNumber,0,'0.00',TO_CHAR(fNumber,'999,999,999,999,999.99'))) INTO ReturnValue
24 FROM DUAL;
25 END IF;
26
27 IF strType = '5' THEN --千分 两位小数点 去掉.00
28 SELECT REPLACE
29 (
30 TRIM(DECODE(fNumber,0,'0.00',TO_CHAR(fNumber,'999,999,999,999,999.99')))
31 ,'.00'
32 ,''
33 ) INTO ReturnValue
34 FROM DUAL;
35 END IF;
36
37 --非千分
38 IF strType = '3' THEN --四位小数
39 SELECT TRIM(DECODE(fNumber,0,'0.0000',TO_CHAR(fNumber,'9999999999999999999999999.9999'))) INTO ReturnValue
40 FROM DUAL;
41 END IF;
42
43 IF strType = '4' THEN --去掉.00
44 SELECT REPLACE
45 (
46 TRIM(DECODE(fNumber,0,'0.00',TO_CHAR(fNumber,'9999999999999999999999999.99')))
47 ,'.00'
48 ,''
49 ) INTO ReturnValue
50 FROM DUAL;
51 END IF;
52 dbms_output.put_line(ReturnValue);
53 return(ReturnValue);
54 end FnMoneyStyle;
55
56 --function测试结果:
57 SELECT FnMoneyStyle(30000.123,'1') FROM DUAL--返回结果:30,000
58 SELECT FnMoneyStyle(30000.123,'2') FROM DUAL--返回结果:30,000.12
59 SELECT FnMoneyStyle(30000.123,'3') FROM DUAL--返回结果:30000.1230
60 SELECT FnMoneyStyle(30000.123,'4') FROM DUAL--返回结果:30000.12
61 SELECT FnMoneyStyle(30000.123,'5') FROM DUAL--返回结果:30,000.12