Vertica 数据库金额转大写函数

74 阅读 0 评论 49 点赞

我是靠谱客的博主无限指甲油，最近开发中收集的这篇文章主要介绍Vertica 数据库金额转大写函数，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

首先的确是得吐槽一下这个Vertical数据库。列式存储，的确很适合用来搭建数据仓库平台，但是原生自带的语法不太适合写存储过程和函数，不集成其他的语言来支持写自定义函数的话，拓展性还是不够强。我是没有找到循环结构，导致函数体很臃肿，有没有看到这里的大佬在留言处提供一波思路。

参数类型 varchar 数值格式类似0.22 转换上限万亿（可自行拓展）

其中TMP为我数据库上的一个模式名
案例：select TMP.MONEY_CHINESE_2(‘222.03’)

函数体


--参数类型 varchar 数值格式类似0.22 转换上限9万亿
--SELECT TMP.MONEY_CHINESE('800400634.08')
CREATE OR REPLACE FUNCTION TMP.MONEY_CHINESE(P_money Varchar)
RETURN Varchar
AS
BEGIN
RETURN (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
(CASE WHEN LENGTH(P_money)=4
THEN (CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'元' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=5/*拾*/
THEN (CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'拾' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,1,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=6/*佰*/
THEN (CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'佰' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END)
END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=7/*仟*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'仟' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=8/*万*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'万' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=9/*十万*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'拾' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,3,3)<>'000' AND SUBSTR(P_money,3,1)<>'0' THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END)
END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN '零' ELSE '' END)
END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN '零' ELSE '' END)
END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN SUBSTR(P_money,9,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=10/*百万*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'佰' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,4,3)<>'000' AND SUBSTR(P_money,4,1)<>'0'
THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN SUBSTR(P_money,9,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN SUBSTR(P_money,10,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=11/*千万*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'仟' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,5,3)<>'000' AND SUBSTR(P_money,5,1)<>'0'
THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN SUBSTR(P_money,10,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN SUBSTR(P_money,11,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=12/*亿元*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'亿' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,2,4)='0000' THEN ''
WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,6,3)<>'000' AND SUBSTR(P_money,6,1)<>'0'
THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN SUBSTR(P_money,9,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN SUBSTR(P_money,11,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,12,1)<>'0' THEN SUBSTR(P_money,12,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=13/*拾亿*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'拾' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'亿'
ELSE (CASE WHEN SUBSTR(P_money,3,7)<>'0000000' AND SUBSTR(P_money,3,1)<>'0'
THEN '亿零' ELSE '亿' END)
END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,4)='0000' THEN ''
WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,7,3)<>'000' AND SUBSTR(P_money,7,1)<>'0'
THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN SUBSTR(P_money,9,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN SUBSTR(P_money,10,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,12,1)<>'0' THEN SUBSTR(P_money,12,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,13,1)<>'0' THEN SUBSTR(P_money,13,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=14/*百亿*/
THEN
(CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'佰' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'亿'
ELSE (CASE WHEN SUBSTR(P_money,4,7)<>'0000000' AND SUBSTR(P_money,4,1)<>'0'
THEN '亿零' ELSE '亿' END)
END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,4)='0000' THEN ''
WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,8,3)<>'000' AND SUBSTR(P_money,8,1)<>'0'
THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN SUBSTR(P_money,9,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN SUBSTR(P_money,10,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN SUBSTR(P_money,11,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,13,1)<>'0' THEN SUBSTR(P_money,13,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,14,1)<>'0' THEN SUBSTR(P_money,14,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=15/*千亿*/
THEN (CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'仟' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'亿'
ELSE (CASE WHEN SUBSTR(P_money,5,7)<>'0000000' AND SUBSTR(P_money,5,1)<>'0'
THEN '亿零' ELSE '亿' END) END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,5,4)='0000' THEN ''
WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,9,3)<>'000' AND SUBSTR(P_money,9,1)<>'0'
THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN SUBSTR(P_money,9,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN SUBSTR(P_money,10,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN SUBSTR(P_money,11,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,12,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,12,1)<>'0' THEN SUBSTR(P_money,12,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,14,1)<>'0' THEN SUBSTR(P_money,14,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,15,1)<>'0' THEN SUBSTR(P_money,15,1)||'分' ELSE '' END)
WHEN LENGTH(P_money)=16/*万亿*/
THEN (CASE WHEN SUBSTR(P_money,1,1)<>'0' THEN SUBSTR(P_money,1,1)||'万' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,2,1)<>'0' THEN SUBSTR(P_money,2,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,3,1)<>'0' THEN SUBSTR(P_money,3,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,4,1)<>'0' THEN SUBSTR(P_money,4,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,5,1)<>'0' THEN SUBSTR(P_money,5,1)||'亿'
ELSE (CASE WHEN SUBSTR(P_money,6,7)<>'0000000' AND SUBSTR(P_money,6,1)<>'0'
THEN '亿零' ELSE '亿' END)
END)||
(CASE WHEN SUBSTR(P_money,6,1)<>'0' THEN SUBSTR(P_money,6,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,7,1)<>'0' THEN SUBSTR(P_money,7,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,8,1)<>'0' THEN SUBSTR(P_money,8,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,9,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,6,4)='0000' THEN ''
WHEN SUBSTR(P_money,9,1)<>'0' THEN SUBSTR(P_money,9,1)||'万'
ELSE (CASE WHEN SUBSTR(P_money,10,3)<>'000' AND SUBSTR(P_money,10,1)<>'0'
THEN '万零' ELSE '万' END)
END)||
(CASE WHEN SUBSTR(P_money,10,1)<>'0' THEN SUBSTR(P_money,10,1)||'仟'
ELSE (CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,11,1)<>'0' THEN SUBSTR(P_money,11,1)||'佰'
ELSE (CASE WHEN SUBSTR(P_money,12,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,12,1)<>'0' THEN SUBSTR(P_money,12,1)||'拾'
ELSE (CASE WHEN SUBSTR(P_money,13,1)<>'0' THEN '零' ELSE '' END) END)||
(CASE WHEN SUBSTR(P_money,13,1)<>'0' THEN SUBSTR(P_money,13,1)||'元' ELSE '元' END)||
(CASE WHEN SUBSTR(P_money,15,1)<>'0' THEN SUBSTR(P_money,15,1)||'角' ELSE '' END)||
(CASE WHEN SUBSTR(P_money,16,1)<>'0' THEN SUBSTR(P_money,16,1)||'分' ELSE '' END)
ELSE ' ' END),'9','玖')::varchar(100),'8','捌')::varchar(100),'7','柒')::varchar(100),'6','陆')::varchar(100),'5','伍')::varchar(100),'4','肆')::varchar(100),'3','叁')::varchar(100),'2','贰')::varchar(100),'1','壹')::varchar(100),'0','零')
);
END;