oracle、MySQL⽇期转XX年XX⽉XX⽇⽇期格式和⾦钱转中⽂⼤写数字的⽅法
你知道的越多，你不知道的越多
点赞再看，养成习惯
如果您有疑问或者见解，欢迎指教：
企鹅：869192208
问题
1. ⼯作中遇到⼀些项⽬需要打印⽂书，出具⽂书的⽇期，客户希望做成XX年XX⽉XX⽇的格式，由于⽬前⽤的是⾃研的⽂书系统，需要
通过 sql 来实现格式的转换。
2. 有些⾦额，需要转成⼤写的数字。
解决⽅案
1. ORACLE 数据库格式化⽇期格式为XX年XX⽉XX⽇
下⾯案例的 lzcity_approve_control_info 表 begin_date 字段类型为 DATE
select to_char(a.begin_date,'yyyy')||'年'||to_char(a.begin_date,'MM')||'⽉'||to_char(a.begin_date,'dd')||'⽇' begin_date from lzcity_approve_control_info a
2. MySql 数据库格式化⽇期格式为XX年XX⽉XX⽇
select DATE_FORMAT(NOW(),'%Y年%m⽉%d⽇');
3. ORACLE 数字转⼤写⾦额
CREATE OR REPLACE FUNCTION NUMBER_TO_CHINESE(P_NUM IN NUMBER DEFAULT NULL)
RETURN NVARCHAR2 IS
/*Ver:1.0 Created By xsb on 2003-8-18 For:
将⾦额数字(单位元)转换为⼤写(采⽤从低⾄⾼算法)
数字整数部分不得超过16位，可以是负数。
Ver：1.1 Modified By xsb on 2003-8-20 For：个位数处理也放在For循环中。
Ver：1.2 Modified By xsb on 2003-8-22 For：分后不带整字。
Ver：1.3 Modified By xsb on 2003-8-28 For：完善测试⽤例。
测试⽤例:
SET HEAD OFF
SET HEAD OFF
SET FEED OFF
select '⽆参数时='||NUMBER_TO_CHINESE() from dual;
select 'null='||NUMBER_TO_CHINESE(null) from dual;
select '0='||NUMBER_TO_CHINESE(0) from dual;
select '0.01='||NUMBER_TO_CHINESE(0.01) from dual;
timidityselect '0.126='||NUMBER_TO_CHINESE(0.126) from dual;
select '01.234='||NUMBER_TO_CHINESE(01.234) from dual;
select '10='||NUMBER_TO_CHINESE(10) from dual;
select '100.1='||NUMBER_TO_CHINESE(100.1) from dual;
select '100.01='||NUMBER_TO_CHINESE(100.01) from dual;
select '10000='||NUMBER_TO_CHINESE(10000) from dual;
select '10012.12='||NUMBER_TO_CHINESE(10012.12) from dual;
select '20000020.01='||NUMBER_TO_CHINESE(20000020.01) from dual;
select '3040506708.901='||NUMBER_TO_CHINESE(3040506708.901) from dual;
select '40005006078.001='||NUMBER_TO_CHINESE(40005006078.001) from dual;
select '-123456789.98='||NUMBER_TO_CHINESE(-123456789.98) from dual;
select '123456789123456789.89='||NUMBER_TO_CHINESE(123456789123456789.89) from dual;
test
*/
RESULT NVARCHAR2(100);--返回字符串
NUM_ROUND NVARCHAR2(100) := TO_CHAR(ABS(ROUND(P_NUM,2)));--转换数字为⼩数点后2位的字符(正数) NUM_LEFT NVARCHAR2(100);--⼩数点左边的数字
NUM_RIGHT NVARCHAR2(2);--⼩数点右边的数字
STR1 NCHAR(10) :='零壹贰叁肆伍陆柒捌玖';--数字⼤写
STR2 NCHAR(16) :='元拾佰仟万拾佰仟亿拾佰仟万拾佰仟';--数字位数(从低⾄⾼)
NUM_PRE NUMBER(1) :=1;--前⼀位上的数字
NUM_CURRENT NUMBER(1);--当前位上的数字microsoft silverlight怎么删不掉
NUM_COUNT NUMBER :=0;--当前数字位数
mysql语句转oracleNUM1 NUMBER;
BEGIN
IF P_NUM IS NULL THEN
RETURN NULL;
END IF;--转换数字为null时返回null
SELECT TO_CHAR(NVL(SUBSTR(TO_CHAR(NUM_ROUND),
1,
DECODE(INSTR(TO_CHAR(NUM_ROUND),'.'),
0,
LENGTH(NUM_ROUND),
INSTR(TO_CHAR(NUM_ROUND),'.')-1)),
0))
INTO NUM_LEFT
FROM DUAL;--取得⼩数点左边的数字
SELECT SUBSTR(TO_CHAR(NUM_ROUND),
DECODE(INSTR(TO_CHAR(NUM_ROUND),'.'),
0,
LENGTH(NUM_ROUND)+1,
INSTR(TO_CHAR(NUM_ROUND),'.')+1),
2)
INTO NUM_RIGHT
FROM DUAL;--取得⼩数点右边的数字
SELECT CASE
WHEN LENGTH(NUM_LEFT)>=8THEN
TO_NUMBER(SUBSTR(TO_CHAR(NUM_LEFT),-8,4))
ELSE
TO_NUMBER(SUBSTR(TO_CHAR(NUM_LEFT),
-LENGTH(NUM_LEFT),
LENGTH(NUM_LEFT)-4))
END
INTO NUM1
FROM DUAL;---取得千、百、⼗、万位上的数字
IF LENGTH(NUM_LEFT)>16THEN
RETURN'**********';
END IF;--数字整数部分超过16位时
--采⽤从低⾄⾼的算法，先处理⼩数点右边的数字
IF LENGTH(NUM_RIGHT)=2THEN
IF TO_NUMBER(SUBSTR(NUM_RIGHT,1,1))=0THEN
RESULT :='零'||
SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT,2,1))+1,1)||'分';
ELSE
RESULT := SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT,1,1))+1,1)||'⾓'||
SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT,2,1))+1,1)||'分';小学编程教学设计脚本
END IF;
ELSIF LENGTH(NUM_RIGHT)=1THEN
RESULT := SUBSTR(STR1, TO_NUMBER(SUBSTR(NUM_RIGHT,1,1))+1,1)||'⾓整';
ELSE
RESULT :='整';
END IF;
--再处理⼩数点左边的数字
FOR I IN REVERSE 1.. LENGTH(NUM_LEFT)LOOP
--(从低⾄⾼)
NUM_COUNT := NUM_COUNT +1;--当前数字位数
NUM_CURRENT := TO_NUMBER(SUBSTR(NUM_LEFT, I,1));--当前位上的数字
IF NUM_CURRENT >0THEN
--当前位上数字不为0按正常处理
RESULT := SUBSTR(STR1, NUM_CURRENT +1,1)||
SUBSTR(STR2, NUM_COUNT,1)|| RESULT;
ELSE
--当前位上数字为0时
IF NUM_COUNT =5THEN
IF MOD(NUM_COUNT -1,4)=0AND NUM1 <>0THEN
RESULT := SUBSTR(STR2, NUM_COUNT,1)|| RESULT;
NUM_PRE :=0;--元、万，亿前不准加零 --当前位是元、万或亿时
END IF;
ELSE
IF MOD(NUM_COUNT -1,4)=0THEN
RESULT := SUBSTR(STR2, NUM_COUNT,1)|| RESULT;
NUM_PRE :=0;--元、万，亿前不准加零
END IF;
END IF;
IF NUM_PRE >0OR LENGTH(NUM_LEFT)=1THEN
--上⼀位数字不为0或只有个位时
RESULT := SUBSTR(STR1, NUM_CURRENT +1,1)|| RESULT;
END IF;
END IF;
数据库增删改查流程顺序图NUM_PRE := NUM_CURRENT;
END LOOP;
IF P_NUM <0THEN
--转换数字是负数时
RESULT :='负'|| RESULT;
END IF;
RETURN RESULT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'数字转换⼤写出现错误！'|| SQLERRM);
END;
下⾯案例的 appr_pay_order 表的 PN_AMOUNT 字段和 PN_OVERDUE_AMT 字段类型都是 NUMBER(16,2)
select NUMBER_TO_CHINESE(apo.PN_AMOUNT) PN_AMOUNT, NUMBER_TO_CHINESE(apo.PN_OVERDUE_AMT) PN_OVERDUE_AMT from appr_pa y_order apo
4. MySql 数字转⼤写⾦额
set character set utf8;
DELIMITER $$;
DROP FUNCTION IF EXISTS`to_china_num`;
CREATE FUNCTION`to_china_num`(MONEY decimal(14,2))RETURNS varchar(150)CHARSET utf8 BEGIN
declare RV_MONEY0 VARCHAR(20);
declare RV_MONEY1 VARCHAR(4);
declare RV_MONEY2 VARCHAR(4);
declare V_MONEY0 VARCHAR(28);
declare V_MONEY1 VARCHAR(4);
declare V_MONEY2 VARCHAR(4);
declare V_MONEY3 VARCHAR(4);
declare V_MONEY4 VARCHAR(4);
declare V_MONEY5 VARCHAR(4);
declare V_MONEY6 VARCHAR(4);
declare V_MONEY7 VARCHAR(4);
declare V_MONEY8 VARCHAR(4);
declare V_MONEY9 VARCHAR(4);
declare V_MONEY10 VARCHAR(4);
declare V_MONEY11 VARCHAR(4);
declare V_MONEY12 VARCHAR(4);
declare RPV_MONEY1 VARCHAR(4);
declare RPV_MONEY2 VARCHAR(4);
declare PV_MONEY0 VARCHAR(14);
declare PV_MONEY1 VARCHAR(4);
declare PV_MONEY2 VARCHAR(4);
declare PV_MONEY3 VARCHAR(4);
declare PV_MONEY4 VARCHAR(4);
declare PV_MONEY5 VARCHAR(4);
declare PV_MONEY6 VARCHAR(4);
declare PV_MONEY7 VARCHAR(4);
declare PV_MONEY8 VARCHAR(4);
declare PV_MONEY9 VARCHAR(4);
declare PV_MONEY10 VARCHAR(4);
declare PV_MONEY11 VARCHAR(4);
declare PV_MONEY12 VARCHAR(4);
declare Z_MONEY VARCHAR(28);
set Z_MONEY=FLOOR(MONEY);
set V_MONEY0=LTRIM(RTRIM(Z_MONEY));
set Z_MONEY=FLOOR(MONEY*100);
set RV_MONEY0=LTRIM(RTRIM(Z_MONEY));
set RV_MONEY1=SUBSTR(RV_MONEY0,LENGTH(RV_MONEY0),1);
IF LENGTH(RV_MONEY0)>1THEN
set RV_MONEY2=SUBSTR(RV_MONEY0,LENGTH(RV_MONEY0)-1,1);
ELSE
set RV_MONEY2='0';
END IF;
END IF;
set V_MONEY1=SUBSTR(V_MONEY0,LENGTH(V_MONEY0),1);
IF LENGTH(V_MONEY0)-1>0THEN
set V_MONEY2=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-1,1);
ELSE
set V_MONEY2='0';
END IF;
IF LENGTH(V_MONEY0)-2>0THEN
set V_MONEY3=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-2,1);
ELSE
set V_MONEY3='0';
END IF;
IF LENGTH(V_MONEY0)-3>0THEN
set V_MONEY4=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-3,1);
ELSE
set V_MONEY4='0';
END IF;
IF LENGTH(V_MONEY0)-4>0THEN
set V_MONEY5=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-4,1);
ELSE
set V_MONEY5='0';
END IF;
IF LENGTH(V_MONEY0)-5>0THEN
set V_MONEY6=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-5,1);
ELSE
set V_MONEY6='0';
END IF;
IF LENGTH(V_MONEY0)-6>0THEN
set V_MONEY7=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-6,1);
ELSE
set V_MONEY7='0';
END IF;
IF LENGTH(V_MONEY0)-7>0THEN
set V_MONEY8=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-7,1);
ELSE
set V_MONEY8='0';
END IF;
IF LENGTH(V_MONEY0)-8>0THEN
set V_MONEY9=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-8,1);
ELSE
set V_MONEY9='0';
END IF;
IF LENGTH(V_MONEY0)-9>0THEN
set V_MONEY10=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-9,1);
ELSE
set V_MONEY10='0';
END IF;
IF LENGTH(V_MONEY0)-10>0THEN
set V_MONEY11=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-10,1);
ELSE
set V_MONEY11='0';
END IF;
IF LENGTH(V_MONEY0)-11>0THEN
set V_MONEY12=SUBSTR(V_MONEY0,LENGTH(V_MONEY0)-11,1);网页页面
ELSE
set V_MONEY12='0';
END IF;
select if(RV_MONEY1='0','零分',if(RV_MONEY1='1','壹分',if(RV_MONEY1='2','贰分',if(RV_MONEY1='3','叁分', if(RV_MONEY1='4','肆分',if(RV_MONEY1='5','伍分',if(RV_MONEY1='6','陆分',if(RV_MONEY1='7','柒分',if(
RV_MONEY1='8','捌分','玖分')))))))))into rpv_money1;
select if(RV_MONEY2='0','零⾓',if(RV_MONEY2='1','壹⾓',if(RV_MONEY2='2','贰⾓',if(RV_MONEY2='3','叁⾓', if(RV_MONEY2='4','肆⾓',if(RV_MONEY2='5','伍⾓',if(RV_MONEY2='6','陆⾓',if(RV_MONEY2='7','柒⾓',if(
RV_MONEY2='8','捌⾓','玖⾓')))))))))into rpv_money2;
select if(V_MONEY1='0','元',if(V_MONEY1='1','壹元',if(V_MONEY1='2','贰元',if(V_MONEY1='3','叁元',
if(V_MONEY1='4','肆元',if(V_MONEY1='5','伍元',if(V_MONEY1='6','陆元',if(V_MONEY1='7','柒元',if(
V_MONEY1='8','捌元','玖元')))))))))into pv_money1;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论