PL/SQL开发 触发器调用java程序

1、创建java程序

create or replace and compile java source named sms as

 

import java.io.BufferedReader;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileReader;

import java.io.FileWriter;

import java.io.IOException;

 

public class SMS

{

  public static String entry(String id)

  {

         String datas = "d:\\oracleFile\\oracle.txt";

        File data = new File(datas);

        StringBuffer backup = null;

        try {

          StringBuffer content = new StringBuffer();

          backup = new StringBuffer();

          BufferedReader san = new BufferedReader(new FileReader(data));

          String line = null;

          while ((line = san.readLine()) != null) {

            backup.append(line);

            backup.append("/n");

          }

          content.append("15 ");

          content.append(" oracle.txt");

          content.append("/n");

 

          content.append(id);

 

          FileWriter writer = new FileWriter(data, false);

          writer.write(content.toString());

          writer.flush();

 

        } catch (FileNotFoundException e) {

          e.printStackTrace();

        } catch (IOException e) {

          try {

            FileWriter writer = new FileWriter(data, false);

            writer.write(backup.toString());

            writer.flush();

        } catch (Exception ex) {

          ex.printStackTrace();

        }

      }

      return id +="OK";    

  }

}

2、创建触发器

CREATE OR REPLACE TRIGGER LOADING_TRIGGER

  AFTER INSERT OR UPDATE OR DELETE ON LOADCHANG_MAIN

  FOR EACH ROW

DECLARE

  OPT1 VARCHAR2(50);

BEGIN

  IF INSERTING THEN

    OPT1 := 'INSERT';

  ELSIF UPDATING THEN

    OPT1 := 'UPDATE';

  ELSIF DELETING THEN

    OPT1 := 'DELETE';

  ELSE

    OPT1 := 'UNKNOWN';

  END IF;

    DBMS_OUTPUT.PUT_LINE(TESTFUN(OPT1));

END LOADING_TRIGGER;

3、创建函数

CREATE OR REPLACE FUNCTION testFun(file in varchar2) return varchar2

AS LANGUAGE JAVA NAME 'SMS.entry(java.lang.String) return java.lang.String';

4、以管理员身份登陆数据库

由于要读写文件，需要对数据库用户进行文件读写授权，因此需要以管理员身份登陆数据库对用户授权

begin

dbms_java.grant_permission('GLMF','SYS:java.io.FilePermission','d:\oracleFile\*','read,write,delete');

end;

对GLMF用户授权可对D盘oracleFile文件夹下的所有文件及文件夹进行读写删除操作。

 

 ----------------------------------------8******************************************

加载java文件到数据库中，首先登陆到数据库服务器

1)加载jar到oracle数据库中

loadjava -r -f -verbose -resolve -user xmlbook/xmlbook xmlparserv2.jar

2）加载class到oracle数据库中

loadjava -r -f  -user user_name/password[@URL] oracle/AAA/bbb/CCC.class

3）移除

dropjava -r -f  -user user_name/password[@URL] [option_list] file_list

*******************************************************************************

create or replace and compile java source named send_sms as

import java.io.BufferedReader;

import java.io.DataOutputStream;

import java.io.IOException;

import java.io.InputStreamReader;

import java.io.UnsupportedEncodingException;

import java.net.HttpURLConnection;

import java.net.URL;

import java.net.URLEncoder;

 

public class SEND_SMS

{

 

  public static String entry(String mobile,String content)

  {

     String result = "false";

     try 

     {

 result = readContentFromGet("mobile="+mobile+"&message="+URLEncoder.encode(content,"utf-8"));

 } 

      catch (UnsupportedEncodingException e) 

      {

 e.printStackTrace();

       }   

       return result;

  }

  

  public static String readContentFromGet(String parm) {

String getURL = "http://IP:PORT/api/sms/send.ashx?appid=sunisco-shared&" + parm;

HttpURLConnection connection = null;

    String res = "false";

try{

URL getUrl = new URL(getURL);

connection = (HttpURLConnection) getUrl.openConnection();

connection.connect();

BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream(),"utf-8"));

      String lines;

while ((lines = reader.readLine()) != null) {

res = lines;

}

reader.close();

}catch(Exception e){

e.printStackTrace();

}finally{

if(connection != null){

connection.disconnect();

}

}

    return res;

}

 

}

 

 

 

 

exec dbms_java.grant_permission('zhym','SYS:java.net.SocketPermission','192.168.9.12:18002','connect,resolve')