在Oracle中调用Java Source 的方法

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED OracleJavaProc
AS
public class OracleJavaProc
{
  public static void main(String[] args) 
	{
		System.out.println("Hello World!");
	}
}

CREATE OR REPLACE PROCEDURE testoraclejava
AS
LANGUAGE JAVA
NAME 'OracleJavaProc.main(java.lang.String [])';

begin
  dbms_java.set_output(2000); ---设置Java输出缓冲区大小，否则无法输出数据
  testoraclejava;             ---调用存储过程
end;

SQL> create or   replace   directory   test_dir   as  'd:/oracle';  
 
目录已创建。  
 
SQL> create or replace java class using bfile(test_dir, 'OracleJavaProc.class')  
  2  /  
 
Java 已创建。 

import java.sql.*;  
import oracle.jdbc.*;  
 
public class OracleJavaProc {  
   //Insert a record to the database.  
   public static void insertRecord(int id, String name, int age ,String xman_id) {  
      System.out.println("Inserting new records for EMP...");  
      try 
	  {
		  Connection conn = DriverManager.getConnection("jdbc:default:connection:");  
          String sql = "INSERT INTO emp " +  
					   "(ID,NAME,SALARY,XMAN_ID) " +  
					   "VALUES(?,?,?,?)";
		  PreparedStatement pstmt = conn.prepareStatement(sql);  
		  pstmt.setInt(1,id);
		  pstmt.setString(2,name);
		  pstmt.setInt(3,age);
		  pstmt.setString(4,xman_id);
		  pstmt.executeUpdate();  
		  pstmt.close();
	  } 
	  catch(SQLException e) 
	  {
		  System.err.println("ERROR! Inserting record: " + e.getMessage());
	  }  
   }
   //Select a reords from database
   public static boolean recordQuery(int id) {
	   System.out.println("Querying records from EMP...");
	   try
	   {
		   Connection conn = DriverManager.getConnection("jdbc:default:connection:");
		   String sql = "SELECT * FROM emp WHERE id = ?";
		   PreparedStatement pstmt = conn.prepareStatement(sql);
		   pstmt.setInt(1,id);
		   ResultSet rs = pstmt.executeQuery();

		   while(rs.next()) {
			   System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+
								  rs.getInt(3)+"\t"+rs.getString(4));
		   }
		   return true;
	   }
	   catch (SQLException e)
	   {
		   System.err.println("ERROR! Querying record: " + e.getMessage());
		   return false;
	   }
   }
} 

create or replace procedure INSERT_RECORD(V_ID      number,
                                          V_NAME    varchar2,
                                          V_SALARY  number,
                                          V_XMAN_ID varchar2) as
language java name 'OracleJavaProc.insertRecord(int,java.lang.String,int,java.lang.String)';

create or replace function QUERY_TABLE(v_id number) return number 
as
  language java name 'OracleJavaProc.recordQuery(int) return int';

SQL> set serveroutput on size 2000  
SQL> call dbms_java.set_output(2000);  
 
调用完成。  
 
SQL> execute add_salgrade(29, 'Charles', 5000, '029');  
Inserting new records for EMP...
 
PL/SQL 过程已成功完成。