Oracle中怎样使用JAVA函数及自定义函数(转)

环境：windows2000 + oracle9.2

1、loadjava

2、直接在Oracle中使用Java源，Oracle内置JVM，对java支持很好

(1). 建立 java source

create or replace and compile java source named js_output_test as
public class js_test
{
public static String js_desc()
{
return "this is java source test.";
}
}

(2). 发布java source

CREATE OR REPLACE FUNCTION fn_test_js RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'js_test.js_desc() return java.lang.String';

(3). 测试.

SQL> set serveroutput on;
SQL> var v_test varchar2(25);
SQL> call fn_test_js() into :v_test;
调用完成。
SQL> print v_test;
V_TEST
--------------------------------
this is java source test.

注意：发布时参数中的java.lang.String一定要写全，返回类型可以用number代替int

====================================================================
create or replace and compile java source named hello as
public class Hello {
static public String Message(String yname) {
return "Hello, " + yname;
}
}

create or replace function hello (Name VARCHAR2) return VARCHAR2
as language java name
'Hello.Message (java.lang.String) return java.lang.String';

Select hello('every body!') From dual;
====================================================================
create or replace and compile java source named sea as
public class Sea{
public static String myReplaceAll(String src,String name1,String name2) {
int a=0; int n=name1.length(); int l=src.indexOf(name1,a);
while(l>0){
src = src.substring(0,l) + src.substring(l+n);
a=a+n;
l=src.indexOf(name1,a);
}
return src;
}

public static String getSameCompany(String name11,String name22){
if(name11==null || name22==null){
return "false";
}

String s1=myReplaceAll(name11,"公司",""); String s2=myReplaceAll(name22,"公司","");
s1=myReplaceAll(s1,"有限",""); s2=myReplaceAll(s2,"有限","");
s1=myReplaceAll(s1,"责任",""); s2=myReplaceAll(s2,"责任","");
s1=myReplaceAll(s1,"市",""); s2=myReplaceAll(s2,"市","");
s1=myReplaceAll(s1,"股份",""); s2=myReplaceAll(s2,"股份","");

if(s1.indexOf(s2)>=0 || s2.indexOf(s1)>=0){
return "true";
}else{
return "false";
}
}
}

CREATE OR REPLACE Function same (name1 Varchar2, name2 Varchar2) Return Varchar2
As Language Java Name
'Sea.getSameCompany(java.lang.String,java.lang.String) return java.lang.String';

====================================================================
CREATE OR REPLACE Function SAME_COMPANY_NAME(Name1 In Varchar2, Name2 In Varchar2) Return Integer Is
Nam1 Varchar2(255); Nam2 Varchar2(255);
Begin
Nam1 := Replace(Name1, '公司', ''); Nam2 := Replace(Name2, '公司', '');
Nam1 := Replace(Nam1, '有限', ''); Nam2 := Replace(Nam2, '有限', '');
Nam1 := Replace(Nam1, '责任', ''); Nam2 := Replace(Nam2, '责任', '');
Nam1 := Replace(Nam1, '市', ''); Nam2 := Replace(Nam2, '市', '');
Nam1 := Replace(Nam1, '股份', ''); Nam2 := Replace(Nam2, '股份', '');
If (Instr(Nam1, Nam2) > 0 Or Instr(Nam2, Nam1) > 0) Then Return 0; Else Return 1; End If;
End SAME_COMPANY_NAME;
====================================================================