从数据库(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\binD:\oracle\product\11.2.0\dbhome_1\BIN>loadjava -hloadjava: 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 objectsD:\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.javaarguments: '-user' 'user/***@database' '-o' '-v' '-f' '-r' 'C:\xx\tools\xx\Hello.java'creating : source Helloloading  : source Hellocreated  : CREATE$JAVA$LOB$TABLEresolving: source HelloClasses Loaded: 0Resources Loaded: 0Sources Loaded: 1Published Interfaces: 0Classes generated: 0Classes skipped: 0Synonyms Created: 0Errors: 0D:\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()';

调用：

beginHello;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_nameAFTER | 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 rowdeclare  -- 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