Oracle_PL-SQL_JavaSource_13
来源:互联网 发布:好布业软件 编辑:程序博客网 时间:2024/05/20 23:30
JAVA Source
有的时候,我们需要在Oracle的Prodedure、Function中调用JAVA代码来实现功能,比方说下面的这个例子,调用JAVA代码来实现大小写金额的转换,如果这个代码用PL实现,难度还是有的,那如果我们先写成JAVA代码,然后通过调用,来实现,还是可以的。
创建
--create or replace and compile java source named javasource_name--as --package package_name/** java code here*/CREATE or replace and compile java source named js_translaterASpackage com.yzupope.util;/** 人民币大小写金额转化*/public class XhgUtil { private static final String UNIT = "万千佰拾亿千佰拾万千佰拾元角分"; private static final String DIGIT = "零壹贰叁肆伍陆柒捌玖"; private static final double MAX_VALUE = 9999999999999.99D; public static String trans(double v) { if (v < 0 || v > MAX_VALUE) return "参数非法!"; long l = Math.round(v * 100);//round(double a):返回最接近参数的long(返回值为static long) if (l == 0) return "零元整"; String strValue = l + ""; // j用来控制单位 int j = UNIT.length() - strValue.length(); String rs = ""; boolean isZero = false; // i用来控制数 for (int i = 0; i < strValue.length(); i++, j++) { char ch = strValue.charAt(i); if (ch == '0') { isZero = true; if (UNIT.charAt(j) == '亿' || UNIT.charAt(j) == '万' || UNIT.charAt(j) == '元') { rs = rs + UNIT.charAt(j); isZero = false; } } else { if (isZero) { rs = rs + "零"; isZero = false; } rs = rs + DIGIT.charAt(ch - '0') + UNIT.charAt(j); } } if (!rs.endsWith("角") && !rs.endsWith("分")) { rs = rs + "整"; } rs = rs.replaceAll("亿万", "亿"); return rs; }}
创建引用
CREATE OR REPLACE FUNCTION xchg(v NUMBER) RETURN VARCHAR2ASLANGUAGE JAVA NAME com.yzupope.util.XhgUtil.trans(double)return java.lang.String';
函数调用
select xchg(105.91) FROM dual;
Where are java classes stored in Oracle?(如何查看呢)
SELECT object_name, object_type, status, TIMESTAMPFROM user_objectsWHERE (object_name NOT LIKE 'SYS_%' AND object_name NOT LIKE 'CREATE$%' AND object_name NOT LIKE 'JAVA$%' AND object_name NOT LIKE 'LOADLOB%') AND object_type LIKE 'JAVA %'ORDER BY object_type, object_name;
当然也可以通过工具查看
点击下载《大小写金额转化》《大小写金额转换IN Excel》