在oracle里用java写存储过程

来源：互联网发布：天猫客服数据在哪里看编辑：程序博客网时间：2024/05/17 06:37

1、在oracle的 Java sources里面创建如下java程序

create or replace and compile java source named senddate as
package org.bromon.oracle;
public class SendDate
{
public static void Send(String da_tel,String dt_bh,String dt_da)
{
    //System.out.println("Hello");

    java.sql.Connection con = null;
    java.sql.Statement stem = null;
    java.sql.ResultSet rs   = null;

    String sql = "insert into gdcq08dxdxsj(da_tel,dt_bh,dt_da,dt_date) values('"+da_tel+"','"+dt_bh+"','"+dt_da+"',sysdate)";
    String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //sqlserver2005
    String URL = "jdbc:sqlserver://localhost:1433;databaseName=name";
    String USER = "user";
    String PASSWORD = "password";

    try {
      Class.forName(DRIVER).newInstance(); ;
      con = java.sql.DriverManager.getConnection(URL,USER,PASSWORD);

      stem = con.createStatement();
      int ret = stem.executeUpdate(sql);
      System.out.println("ret = "+ret);
    } catch (Exception e) {
      System.out.println("数据库连接失败!");
      e.printStackTrace();
    }
    finally{
       try {
       stem.close();
       stem = null;
       con.close();
       con = null;
       } catch (java.sql.SQLException e) {
         System.out.println("数据库关闭失败!");
         e.printStackTrace();
       }
    }

}

}

2、写oracle存储过程

//packages

procedure myProc(da_tel varchar2,dt_bh varchar2,dt_da varchar2);

//package bodies

procedure myProc(da_tel varchar2,dt_bh varchar2,dt_da varchar2) as
language java name 'org.bromon.oracle.SendDate.Send(java.lang.String,java.lang.String,java.lang.String)';

现在可以直接调用 myProc 了，如果是在Command Window 下测试的话先执行

SET SERVEROUTPUT ON;
CALL dbms_java.set_output(2000);

这样才能看到System.out.println的输出。