解决方案 »
- 【问题】如何求取每个部门工资前2名员工的 部门编号,姓名,工资
- SQL 按天分组查询
- 我想用group by但是不想用到聚合函数,有办法吗?
- Oracle9i 的 mangement server 为什么启动不了???
- oracle10采用jdbc的方式获取conn,并设置conn.setAutoCommit(false),不显示执行conn.commit();结果还是提交了。
- oralce如何跨域访问?
- 死锁问题
- 请问,什么语句能看到一个表都能被哪个帐户有权限有什么样的权限?
- 向老大们请教一个从数据库中返回多行结果级的问题,难点使用的游标存在于动态SQL语句
- oracle的回滚段或是撤销表空间怎么调整?寻求方案
- 求一个客户ID的高效校验方法
- 求高手帮忙,按照季度、上下半年统计数据SQL文
后面JSP怎么解释.
显示中文,可以自己写一个函数:CREATE OR REPLACE Function LqUppMoney(numMoney in Number)
RETURN VARCHAR2
IS
strMoney VARCHAR2(60);
strSign varchar2(4);
numValue number(22);
strNum Varchar2(2);
strBit Varchar2(2);
numBit number(2);
numMod Number(1);
strUppNum VARCHAR2(20):= '零壹贰叁肆伍陆柒捌玖 ';
strUppUnit VARCHAR2(34):= '分角圆拾佰仟万拾佰仟亿拾佰仟万拾佰 ';
BEGIN
if numMoney <0 then
strSign:= '[负] ';
else
strSign:= ' ';
end if;
numValue:=abs(numMoney)*100;
strMoney:= ' ';
numBit:=0;
LOOP
numMod:=Mod(numValue,10);
strNum:=substr(strUppNum,numMod+1,1);
strBit:=substr(strUppUnit,numBit+1,1);
strMoney:=strNum||strBit||strMoney;
numValue:=FLOOR(numValue/10);
numBit:=numBit+1;
exit when numValue <1 or numBit> 16;
END LOOP;
strMoney:=replace(strMoney, '零角 ', '零 ');
strMoney:=replace(strMoney, '零仟 ', '零 ');
strMoney:=replace(strMoney, '零佰 ', '零 ');
strMoney:=replace(strMoney, '零拾 ', '零 ');
strMoney:=replace(strMoney, '零零 ', '零 ');
strMoney:=replace(strMoney, '零零 ', '零 ');
strMoney:=replace(strMoney, '零分 ', '整 ');
strMoney:=replace(strMoney, '零圆 ', '圆 ');
strMoney:=replace(strMoney, '零万 ', '万 ');
strMoney:=replace(strMoney, '零亿 ', '亿 ');
strMoney:=replace(strMoney, '亿万 ', '亿 ');
strMoney:=strSign||strMoney;
RETURN(strMoney);
END;
SELECT 123456789012345 AMT FROM DUAL UNION ALL
SELECT 123456000002345 AMT FROM DUAL UNION ALL
SELECT 300000 AMT FROM DUAL
)
SELECT AMT
,XMLAGG(XMLELEMENT("nm",AMT2) ORDER BY LV).EXTRACT('//text()').GetStringVal() AS V
FROM (SELECT AMT,LV
,(SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(DECODE(SUBSTR(AMT1,LEVEL,1),'零',NULL,SUBSTR(AMT1,LEVEL,1)||DECODE(LEVEL,1,'千',2,'百',3,'十')),'/')),'/','')
FROM DUAL
CONNECT BY LEVEL <= 4)||DECODE(LV,1,'兆',2,'亿',3,'万') AS AMT2
FROM (SELECT A.AMT,B.LV
,TRIM(TRANSLATE(SUBSTR(LPAD(A.AMT,16,'0'),(B.LV*4)-3,4), '1234567890', '一二三四五六七八九零')) AMT1
FROM T A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4) B
)
WHERE AMT1 != '零零零'
)
GROUP BY AMT;谢谢楼上几位.