官方教程:
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