JAVA Source

有的时候，我们需要在Oracle的Prodedure、Function中调用JAVA代码来实现功能，比方说下面的这个例子，调用JAVA代码来实现大小写金额的转换，如果这个代码用PL实现，难度还是有的，那如果我们先写成JAVA代码，然后通过调用，来实现，还是可以的。

CREATE or replace and compile java source named js_translater  
AS  
package 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 VARCHAR2  
AS  
LANGUAGE 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, TIMESTAMP  
FROM   user_objects  
WHERE  (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;  

工具查看