oracle調用java

其它語言的函數的調用

java函數調用

 

在oracle資料庫建立一個java資源，也可以用loadjava命令裝載其它的java類或者jar

 

create or replace and compile java sourcenamed mytestjava as
public class Factorial {
public static int calcFactorial (int n) {
if (n == 1) return 1;
else return n * calcFactorial (n - 1) ;
}
}

 

建立一個映射函數

CREATE OR REPLACE FUNCTION plstojavafac_fun
(N NUMBER)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME 'Factorial.calcFactorial (int) return int';

 

selectplstojavafac_fun(4) from dual

----

24

 

這是一個極其簡單的例子，但是有了這樣的功能，你可以調用外部的任何外部命令，也可以與其他任何外部資料庫資料檔案進行通訊了

 

 

下面一個例子，實現在資料庫內調用任何外部命令的功能

 

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" ASimport java.io.*;public class Host {  public static void executeCommand(String command) {    try {      String[] finalCommand;      if (isWindows()) {        finalCommand = new String[4];        // Use the appropriate path for your windows version.        finalCommand[0] = "C:\\windows\\system32\\cmd.exe";  // Windows XP/2003        //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";  // Windows NT/2000        finalCommand[1] = "/y";        finalCommand[2] = "/c";        finalCommand[3] = command;      }      else {        finalCommand = new String[3];        finalCommand[0] = "/bin/sh";        finalCommand[1] = "-c";        finalCommand[2] = command;      }        final Process pr = Runtime.getRuntime().exec(finalCommand);      pr.waitFor();      new Thread(new Runnable(){        public void run() {          BufferedReader br_in = null;          try {            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));            String buff = null;            while ((buff = br_in.readLine()) != null) {              System.out.println("Process out :" + buff);              try {Thread.sleep(100); } catch(Exception e) {}            }            br_in.close();          }          catch (IOException ioe) {            System.out.println("Exception caught printing process output.");            ioe.printStackTrace();          }          finally {            try {              br_in.close();            } catch (Exception ex) {}          }        }      }).start();        new Thread(new Runnable(){        public void run() {          BufferedReader br_err = null;          try {            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));            String buff = null;            while ((buff = br_err.readLine()) != null) {              System.out.println("Process err :" + buff);              try {Thread.sleep(100); } catch(Exception e) {}            }            br_err.close();          }          catch (IOException ioe) {            System.out.println("Exception caught printing process error.");            ioe.printStackTrace();          }          finally {            try {              br_err.close();            } catch (Exception ex) {}          }        }      }).start();    }    catch (Exception ex) {      System.out.println(ex.getLocalizedMessage());    }  }    public static boolean isWindows() {    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)      return true;    else      return false;  }};
 
 
在調用外部程式之前，必須授權給資料庫使用者相應的許可權
 
-- Created on 2007-9-13 by Tiwen 
declare 
begin
DBMS_JAVA.grant_permission('TIWEN','java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute,delete');
Dbms_Java.Grant_Permission('TIWEN','SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
Dbms_Java.Grant_Permission('TIWEN','SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
end;
 
建立映射過程
CREATE OR REPLACE PROCEDUREhost_command (p_command  IN  VARCHAR2)
AS LANGUAGEJAVA 
NAME'Host.executeCommand (java.lang.String)';
 
測試
DECLARE
 l_output DBMS_OUTPUT.chararr;
 l_lines  INTEGER := 1000;
BEGIN
 DBMS_OUTPUT.enable(1000000);
 DBMS_JAVA.set_output(1000000);
 host_command('dir g:\');  --執行顯示目錄的命令
 DBMS_OUTPUT.get_lines(l_output, l_lines);
 FOR i IN 1 .. l_lines LOOP
   DBMS_OUTPUT.put_line(l_output(i));
    NULL;
  END LOOP;
END;
 
 原創文章，如果轉載，請標註作者：田文  CSDN地址：http://blog.csdn.net/tiwen818