从数据库(Oracle)触发器调用Java代码

官方教程：

https://docs.oracle.com/database/122/JJDEV/calling-Java-from-database-triggers.htm#JJDEV13286

https://docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm

参考：
http://blog.csdn.net/qiuzhuoxian/article/details/5679602

例子Demo

1、编写Java代码

Hello.java文件

public class Hello { 
  public static String world(){ 
    return "Hello world"; 
  } 
}

2、Oracle使用LOADJAVA命令加载Java程序

在$ORACLE_HOME/bin目录下有个LOADJAVA命令，使用这个命令将刚写好的JAVA程序LOAD进数据库。

loadjava -user test/test@test -o -v -f -r Main.java

如果成功的话，会打印出来信息提示成功，若程序有编译错误的话，也会提示你错误的地方。

loadjava命令用法：

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>d:

D:\>cd D:\oracle\product\11.2.0\dbhome_1\bin

D:\oracle\product\11.2.0\dbhome_1\BIN>loadjava -h
loadjava: load classes, jars, resources or properties files into Oracle.
Usage: loadjava [options] classes..jars..resources..properties...
Options:
  -d, -definer             gives the methods of the class definers rights
  -encoding <encoding>     the encoding to be used to read .java files
  -f, -force               force loading of all classes.  Normally classes
                           identical to classes already loaded are not
                           reloaded.
  -g, -grant <grants>      grant execute permission on all loaded classes
                           and resources to the users and roles
                           listed in the comma separated list <grants>.
  -genmissing              if the classes and jars refer to classes that
                           are not present, generate dummy versions of
                           the missing classes.
  -genmissingjar <jar>     In addition to the actions of -genmissing
                           create a jar containing the generated classes.
  -h, -help                print out this message.
  -nousage                 do print a usage message if there are no file
                           arguments
  -noverify                do not verify bytecodes.
  -o, -oci8                use JDBC OCI8.  The database must be an entry
                           from TNSNAMES.ORA file or a Net8 Name-Value list.
  -order                   resolve classes in "bottom up" order
  -r, -resolve             resolve all the classes after loading them.
  -nativecompile           natively compile methods in classes after loading
                           and resolving them.  This option forces loaded
                           classes to be resolved.
  -resolver <resolver>     use <resolver> as the resolver for the loaded
                           classes.  As resolvers contain special characters
                           they should be quoted on the command line.
  -schema <schema>         loads into <schema> rather than schema of user
                           schema being loaded into.
  -s, -synonym             create public synonyms for the loaded classes.
                           You must have CREATE PUBLIC SYNONYM privilege.
  -tableschema <schema>    Use <schema> to hold all tables used by loadjava
                           instead of putting the tables in the schema
                           where classes are being loaded.
  -t, -thin                use JDBC THIN.  The database must be of the form
                           <host>:<port>:<sid> or a Net8 Name-Value list.
  -user user/password@database   account and database where to load the
                            classes and resources.
  -v, -verbose             print some information as it loads the files.
                           recommended to find missing classes early.
  -proxy host:port         Host is the proxy host name or internet address.
                           Port is the proxy port number.
  -edition <edition name>  The application edition into which to load objects


D:\oracle\product\11.2.0\dbhome_1\BIN>

执行：

D:\oracle\product\11.2.0\dbhome_1\BIN>loadjava -user  user/password@database  -o -v -f
-r C:\xx\tools\xx\Hello.java
arguments: '-user' 'user/***@database' '-o' '-v' '-f' '-r' 'C:\xx\tools\xx\He
llo.java'
creating : source Hello
loading  : source Hello
created  : CREATE$JAVA$LOB$TABLE
resolving: source Hello
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

D:\oracle\product\11.2.0\dbhome_1\BIN>

成功后在PL/SQL里可以查看得到

或者通过PL/SQL创建java代码

3、修改权限

注：不对文件读写，可忽略

因为我的这个JAVA程序里涉及到对文件的读写操作，所以要先修改权限。
首先以管理员身份登录进数据库
sqlplus / as sysdba
然后执行
begin
dbms_java.grant_permission(‘TEST’,’SYS:java.io.FilePermission’,’/var/spool/cron/oracle’,’read,write,execute,delete’);
end;
执行完毕后，在数据库里执行上述JAVA程序时，就拥有读写该文件的权限了。

4、创建存储过程或函数

1、存储过程

create or replace procedure Hello as language java name 'Hello.world()';

调用：

begin
Hello;
end;

2、函数

CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS
  LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';

调用：

SELECT helloworld() FROM dual;

5、增加trigger

TRIGGER 语法

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
trigger_body;

触发器：

create or replace trigger mipush_trigger
  after update
  on T_MMS3_REMINDER_RECORD 
  for each row
declare
  -- local variables here
  val varchar2(100);
begin
    --小米推送
 SELECT helloworld() into val FROM dual;
 dbms_output.put_line(val);
end mipush_trigger;

6、测试

修改表行或插入表新行
select * from table_name for update;
修改字段时，会看到output输出Hello world