oracle调用java方法的例子

 

在oracle中调用java程序,注意:java方法必须是static类型的,如果想在JAVA中使用system.out/err输出log.

需要在oracle 中执行"call dbms_java.set_output(5000);".
一、helloWord
1 编写JAVA程序,也是在SQL/PLUS中写,并执行.

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

2 发布JAVA程序

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

3 使用发面的JAVA程序

select hello( ' world! ' ) from dual;
HELLO( ' world! ' )
-- -------------
Hello world!

    
二、在JAVA程序中使用SQL
1 编写JAVA程序

create or replace and compile java source named test as
import java.sql. * ;
import java. text .DateFormat;
import java. text .SimpleDateFormat;
import java.util. * ;
import java.util.Date;

import com.ccit.wcsmp.comm.vo.daovo.MBTResultVO;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class Test
{
   static public String   message(String name) {
        String sql = " select sysdate as dd from dual";
        String tmpStr = null ;
        PreparedStatement pstmt = null ;
        ResultSet rs = null ;
        Connection conn = null ;
         try
         {
              conn = DriverManager.getConnection("jdbc: default :connection:");
              pstmt = conn.prepareStatement(sql);
              rs = pstmt.executeQuery();
            
              if (rs. next ())
           tmpStr = rs.getString("dd");
         else
           tmpStr = name;
          }
         catch(SQLException e)
         {
             System.err.println(e.getMessage());
         }
         finally
         {
             try
             {
                 rs. close ();
                 pstmt. close ();
                 /**/ /* if((conn!=null)||!conn.isClosed())
                     conn.close(); */
             }
             catch(SQLException e)
             {
                 System.err.println(e.getMessage());
             }
         }
        return tmpStr;
    }
}
/


2 发布JAVA程序

create or replace function test (name varchar2 ) return varchar2
as language java name
' Test.message(java.lang.String) return java.lang.String ' ;
/

3 使用发面的JAVA程序

select    test( ' xx ' )   from    dual ;