oracle 11g PL/SQL Programming学习十六

最新推荐文章于 2024-01-24 13:46:21 发布

Cryking

最新推荐文章于 2024-01-24 13:46:21 发布

阅读量2.8k

点赞数

分类专栏： oracle PLSQL学习系列

本文链接：https://blog.csdn.net/edcvf3/article/details/9125119

版权

oracle 同时被 2 个专栏收录

92 篇文章 3 订阅

订阅专栏

PLSQL学习系列

26 篇文章 13 订阅

订阅专栏

----------------------------------------------------------------------------
-----------------PL/SQL学习笔记系列 By Cryking-----------------
------------------------转载请注明出处,谢谢!------------------------

第15章 JAVA库
oracle 11g可以让你使用函数、存储、包来扩展你的应用.
你也可以在java编程语言里使用这些存储程序.

oracle 11g JVM新特性
oracle 11g JVM目前已经很成熟了.在11g,它有几点新特性:
1.oracle JVM内部兼容java 5标准版.
2.Oracle JVM得到增强.
包括支持了loadjava URL,dropjava基于列表的操作,使用ojvmmtc工具来解决外部类引用,增强了工具ojvmjava的功能.
3.引进了驻留数据库的JAR文件.
这意味着你当你加载JAR文件时,你现在可以选择创建一个代表JAR文件的对象.
4.在用户自定义类之间共享元数据的能力.
5.可以出现两重的java会话状态.
6.可以重定向输出流从数据库到外部.
7.可以设置在数据库服务器之间传播的系统属性
8.JIT编译器的交互减少了java字节流直接转换到特定代码,通过消除解释阶段来提高性能.

JAVA架构
oracle 11g提供了一个强健的架构来开发服务端或内部java程序组件.
JAVA和PL/SQL都是解释性语言,所以都需要JIT编译.
有三种方式将java class放到数据库实例里:
1.一个两步走的过程:a,使用JAVAC编译JAVA源文件,生成一个java字节代码程序.
b,使用loadjava工具将文件放入数据库实例.
2.一步走的过程:使用loadjava工具编译并把java类文件放入到数据库实例
3.一步走的过程:使用DDL来建立并编译java源文件作为java存储类.

java存储单元就像一般的PL/SQL程序单元.
当你有一个main()方法在java的资源类里,你只能通过ojvmjava工具来访问它,
内部java资源文件有两种表现:存储程序体和可实例化类.

java资源存储
java类资源以明文存储,是java字节代码,并且是压缩的java档案(也就是JAR文件).
JAR文件在11g里可以存储在数据库内部,也可以存储在数据库外.

java安全和许可
操作的资源是受限的.你只能使用SYSDBA权限来更改这些.
使用DBMS_JAVA包和GRANT_PERMISSION存储来打开受限的操作资源,像文件I/O.

java线程
java线程与oracle内部java类的工作方式是不同的.
oracle JVM使用的是非优先线程模型,也就是所有的线程都跑在一个操作系统的线程上面.
oracle 11g现在支持类加载,可以使你创建优先线程方案.

oracle java连接类型
oracle实现了三种java数据库连接方式以满足三种不同的需要.
它们是thin连接,thick连接,缺省连接.它们分别对应客户端驱动,中间件驱动,服务端驱动.
1.JDBC thin 驱动
oracle薄连接可能是java应用的最多的一种方式.JSP和EJB都使用这种方式.
它提供了很多方便,而不用直接访问oracle库文件.
乐观连接:乐观连接是使用HTTP协议的临时传输连接,它被限制在一个15秒的TCP套接字连接管道.
         常用在JSP应用,它的缺点是必须为每个通信建立连接.
悲观连接:悲观连接是通常的连接方式,它使用一个可感知的TCP套接字在整个连接的过程中.
         常用在使用多线程java servlets来创建和维护的数据库连接池中.
         Java servlets能实现2层,甚至多层解决方案.
2.OCI驱动或中间层Thick驱动
OCI驱动和Oracle C/C++库紧密联系.
如果你使用ORACLE JDBC调用接口,你需要确保PATH、CLASSPATH、LD_LIBRARY_PATH环境变量设置正确.
OCI驱动能通过Java servlets来维护一个持久的连接池.
3.Oracle服务端内部驱动或服务层Thick驱动
Oracle服务端内部驱动也依赖Oracle C/C++库.
在服务端没有别的选择,只有用该驱动来建立JAVA程序作为存储对象.
它使用DriverManger类的getConnection()方法来连接到数据库.
Oracle服务端内部驱动要比Oracle JDBC thin驱动快,因为库就在本地,不用产生网络调用.

在Oracle数据库里建立java类库
有两种部署方法来建立java类库:
1.建立中间层调用接口驱动
    调用接口库就像包含apache服务器的服务端.它们必须复制到所有的apache服务器节点,
    然后使用web服务的负载均衡工具进行管理.
2.服务端java类库
    服务端java类库基于Oracle JVM作为对象存储在数据库里.
    本章主要讲这种方式的java类库.

java的配置及编译、运行见相关书籍,这里不详细介绍
注意:java的类名是大小写敏感的,并且文件名应当和类名一致

例:创建java文件HelloWorld1.java,内容如下:

public class HelloWorld1 {
public static void main(String args[]) {
System.out.println("Hello World."); }
}

环境变量配置OK后,使用 javac HelloWorld1.java 命令来编译java文件HelloWorld1.
C:\Documents and Settings\Administrator>javac.exe E:\HelloWorld1.java
编译后会在源java文件目录下产生一个HelloWorld1.class类文件.
你可以通过命令 JAVA HelloWorld1来测试输出结果.
C:\Documents and Settings\Administrator>cd /d e:

E:\>java.exe HelloWorld1
Hello World.

建立和访问服务端java class文件分三步:
1.建立并编译java文件
2.使用loadjava工具将编译后的class文件加载到服务器
3.建立PL/SQL包装器来包装java calss库。
一个完整的例子(需要配置好JAVA环境变量):
创建java文件HelloWorld2.java,内容如下:

import oracle.jdbc.driver.*;
// Class definition.
public class HelloWorld2 {
public static String hello() {
 return "Hello World."; }
public static String hello(String name) {
 return "Hello " + name + "."; }
public static void main(String args[]) {
  System.out.println(HelloWorld2.hello());
  System.out.println(HelloWorld2.hello("Larry")); }
}

编译该java文件
C:\Users\Administrator>javac C:\Users\Administrator\Desktop\HelloWorld2.java

C:\Users\Administrator>cd /d C:\Users\Administrator\Desktop

C:\Users\Administrator\Desktop>java HelloWorld2
Hello World.
Hello Larry.

注意:编译时可能会报找不到import oracle.jdbc.driver.*;包的错误,此时需要在环境变量CLASSPATH里加上classes12.jar
classes12.jar在oracle目录$ORACLE_HOME/oui/jlib下找到.

--使用loadjava工具将生成的class文件加载到oracle JVM
C:\Users\Administrator\Desktop>loadjava -r -f -o -user scott/tiger@orcl HelloWorld2.class

--建立pl/sql包来使用HelloWorld2中的方法hello
22:21:14 SCOTT@orcl> CREATE OR REPLACE PACKAGE hello_world2 AS
23:07:41   2    FUNCTION hello RETURN VARCHAR2;
23:07:41   3    FUNCTION hello(who VARCHAR2) RETURN VARCHAR2;
23:07:41   4  END hello_world2;
23:07:41   5  /

Package created.

Elapsed: 00:00:00.16
23:07:41 SCOTT@orcl> CREATE OR REPLACE PACKAGE BODY hello_world2 AS
23:07:41   2    FUNCTION hello RETURN VARCHAR2 IS
23:07:41   3      LANGUAGE JAVA NAME 'HelloWorld2.hello() return String';
23:07:41   4    FUNCTION hello(who VARCHAR2) RETURN VARCHAR2 IS
23:07:41   5      LANGUAGE JAVA NAME 'HelloWorld2.hello(java.lang.String) return String';
23:07:42   6  END hello_world2;
23:07:42   7  /

Package body created.

Elapsed: 00:00:00.03
23:14:39 SCOTT@orcl> SELECT hello_world2.hello('Cryking!') FROM dual;

HELLO_WORLD2.HELLO('CRYKING!')
--------------------------------------------
Hello Cryking!.

1 row selected.

Elapsed: 00:00:00.05

注意你使用的返回类型为varchar2,对应java中的类型string.这里需要写全(使用完全限定名):java.lang.String
还要注意HelloWorld2的名称以及方法hello的名称都是大小写敏感的,写错了,包也能正常创建,只是在使用包方法的时候会报错.
如我将上面'HelloWorld2.hello() return String'的内容改为 'HelloWorld2.HELLO() return String'
此时再使用会报错ORA-29531:

23:07:43 SCOTT@orcl> SELECT hello_world2.hello('Cryking!') FROM dual;
SELECT hello_world2.hello('Cryking!') FROM dual
                                           *
ERROR at line 1:
ORA-29531: no method Hello in class HelloWorld2


Elapsed: 00:00:00.04

在sql文件中创建java源
语法:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED <java_class_name> AS
<java_source>
/
如：

23:14:41 SCOTT@orcl> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED HelloWorldSQL AS
23:21:20   2  public class HelloWorldSQL {
23:21:20   3  public static String hello() {
23:21:20   4   return "Hello World."; }
23:21:20   5  public static String hello(String name) {
23:21:20   6   return "Hello " + name + "."; }
23:21:20   7  }
23:21:25   8  /

Java created.

Elapsed: 00:00:02.72

然后我们可以像上面一样,创建包来使用这个创建好的java源.
这个感觉比上面的loadjava方法要快捷许多.

23:21:29 SCOTT@orcl> CREATE OR REPLACE PACKAGE hello_world_sql AS
23:24:05   2    FUNCTION hello RETURN VARCHAR2;
23:24:05   3    FUNCTION hello(who VARCHAR2) RETURN VARCHAR2;
23:24:05   4  END hello_world_sql;
23:24:05   5  /

Package created.

Elapsed: 00:00:00.08
23:24:05 SCOTT@orcl> CREATE OR REPLACE PACKAGE BODY hello_world_sql AS
23:24:05   2    FUNCTION hello RETURN VARCHAR2 IS
23:24:05   3      LANGUAGE JAVA NAME 'HelloWorldSQL.hello() return String';
23:24:05   4    FUNCTION hello(who VARCHAR2) RETURN VARCHAR2 IS
23:24:05   5      LANGUAGE JAVA NAME 'HelloWorldSQL.hello(java.lang.String) retu
rn String';
23:24:05   6  END hello_world_sql;
23:24:05   7  /

Package body created.

Elapsed: 00:00:00.03
23:24:08 SCOTT@orcl> SELECT hello_world_sql.hello('Cryking!') FROM dual;

HELLO_WORLD_SQL.HELLO('CRYKING!')
---------------------------------
Hello Cryking!.

1 row selected.

Elapsed: 00:00:00.03
23:24:48 SCOTT@orcl> SELECT hello_world_sql.hello FROM dual;

HELLO
-------------
Hello World.

1 row selected.

Elapsed: 00:00:00.01

建立服务端内部java存储过程
建立存储过程和建立上面的包内函数类似,不过应当注意的是存储过程有IN,IN OUT,OUT模式.
当在存储中使用了java方法时,不能使用IN OUT模式.

再看一个例子(在存储中使用java方法)
建立java文件HelloWorld3.java,内容如下:

import java.sql.*;
import oracle.jdbc.driver.*;
// Class definition.
public class HelloWorld3 {
public static void doDML(String statement
,String name) throws SQLException {
// Declare an Oracle connection.
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
// Declare prepared statement, run query and read results.
PreparedStatement ps = conn.prepareStatement(statement);
ps.setString(1,name);
ps.execute(); }
public static String doDQL(String statement) throws SQLException {
// Define and initialize a local return variable.
String result = new String();
// Declare an Oracle connection.
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
// Declare prepared statement, run query and read results.
PreparedStatement ps = conn.prepareStatement(statement);
ResultSet rs = ps.executeQuery();
while (rs.next())
result = rs.getString(1);
return result; }
}

该java类有2个静态方法:1个是doDML,用来执行各种DML语句;另一个是doDQL,用来执行各个查询语句.
注意该java类没有main方法,不能直接测试,可以自己加个main来测试是否正常运行.
(我这里就不测试了)
C:\Users\Administrator\Desktop>javac C:\Users\Administrator\Desktop\HelloWorld3.java

C:\Users\Administrator\Desktop>loadjava -r -f -o -user scott/tiger@orcl HelloWorld3.class

导入到数据库之后,我们建立相关的包进行测试,注意doDML方法是使用存储过程来调用的

23:25:30 SCOTT@orcl> CREATE OR REPLACE PACKAGE hello_world3 AS
23:37:08   2    PROCEDURE doDML(dml VARCHAR2, input VARCHAR2);
23:37:08   3    FUNCTION doDQL(dql VARCHAR2) RETURN VARCHAR2;
23:37:08   4  END hello_world3;
23:37:08   5  /

Package created.

Elapsed: 00:00:00.03
23:37:08 SCOTT@orcl> CREATE OR REPLACE PACKAGE BODY hello_world3 AS
23:37:08   2    PROCEDURE doDML(dml VARCHAR2, input VARCHAR2) IS
23:37:08   3      LANGUAGE JAVA NAME 'HelloWorld3.doDML(java.lang.String,java.lang.String)';
23:37:08   4    FUNCTION doDQL(dql VARCHAR2) RETURN VARCHAR2 IS
23:37:08   5      LANGUAGE JAVA NAME 'HelloWorld3.doDQL(java.lang.String) return String';
23:37:08   6  END hello_world3;
23:37:08   7  /

Package body created.

Elapsed: 00:00:00.09
23:37:08 SCOTT@orcl> --建立测试表
23:37:58 SCOTT@orcl> CREATE TABLE mytable(character VARCHAR2(100));

Table created.

Elapsed: 00:00:00.06
23:38:49 SCOTT@orcl> --使用包hello_world3的java方法来插入和查询数据
23:44:45 SCOTT@orcl> exec hello_world3.doDML('INSERT INTO MYTABLE VALUES(?)','Hello,Cryking!');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
23:47:45 SCOTT@orcl> SELECT hello_world3.doDQL('SELECT CHARACTER FROM MYTABLE')
a from dual;

A
--------------
Hello,Cryking!

1 row selected.

Elapsed: 00:00:00.02

建立服务器内部java对象
需要使用SQLData接口来实例化存储的java对象.
主要注意数据类型的转换.
示例:
建立java文件HelloWorld4.java,内容如下:

import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.oracore.*;
// Class definition.
public class HelloWorld4 implements SQLData {
// Define or declare SQLData components.
private String className = new String("HelloWorld4.class");
private String instanceName;
private String qualifiedName;
private String sql_type;
public HelloWorld4() {
String user = new String();
try {
user = getUserName(); }
catch (Exception e) {}
qualifiedName = user + "." + className; }
// Define a method to return a qualified name.
public String getQualifiedName() throws SQLException {
// Declare return variable.
return this.qualifiedName + "." + instanceName; }
// Define a method to return the database object name.
public String getSQLTypeName() throws SQLException {
// Returns the UDT map value or database object name.
return sql_type; }
// Define getUserName() method to query the instance.
public String getUserName() throws SQLException {
String userName = new String();
String getDatabaseSQL = "SELECT user FROM dual";
// Declare an Oracle connection.
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
// Declare prepared statement, run query and read results.
PreparedStatement ps = conn.prepareStatement(getDatabaseSQL);
ResultSet rs = ps.executeQuery();
while (rs.next())
userName = rs.getString(1);
return userName; }
// Implements readSQL() method from the SQLData interface.
public void readSQL(SQLInput stream, String typeName) throws SQLException {
// Define sql_type to read input and signal overloading signatures.
sql_type = typeName;
// Pass values into the class.
instanceName = stream.readString(); }
// Implements writeSQL() method from the SQLData interface with a placeholder.
public void writeSQL(SQLOutput stream) throws SQLException {
// You pass a value back by using a stream function.
/* stream.writeString(‘variable_name’); */ }
}

然后我们不再使用javac来编译该java文件,而是用loadjava工具把该java文件直接加载到oracle里.
此时loadjava工具会帮我们完成编译java文件的工作.
C:\Users\Administrator\Desktop>loadjava -r -f -o -user scott/tiger@orcl HelloWorld4.java

--创建java对象类型
23:48:02 SCOTT@orcl> CREATE OR REPLACE TYPE hello_world4 AS OBJECT
00:04:04   2    EXTERNAL NAME 'HelloWorld4' LANGUAGE JAVA
00:04:04   3  USING SQLData
00:04:04   4  (instanceName VARCHAR2(100) EXTERNAL NAME 'java.lang.String'
00:04:04   5  , CONSTRUCTOR FUNCTION hello_world4
00:04:04   6    RETURN SELF AS RESULT
00:04:04   7  , MEMBER FUNCTION getQualifiedName
00:04:04   8    RETURN VARCHAR2 AS LANGUAGE JAVA
00:04:04   9    NAME 'HelloWorld4.getQualifiedName() return java.lang.String'
00:04:04  10  , MEMBER FUNCTION getSQLTypeName
00:04:04  11    RETURN VARCHAR2 AS LANGUAGE JAVA
00:04:04  12    NAME 'HelloWorld4.getSQLTypeName() return java.lang.String')
00:04:04  13  INSTANTIABLE FINAL;
00:04:04  14  /

Type created.

Elapsed: 00:00:00.68

注意上面使用了USING SQLData子句,这样该对象类型不用再建立对象类型体了.
SQLData提供了存储对象的实例化接口.
在你定义了该java对象类型之后,你可以看到该对象类型和对象类型体都已经注册到oracle实例元数据里.

00:10:41 SCOTT@orcl> COL object_name FORMAT A30
00:11:04 SCOTT@orcl> COL object_type FORMAT A12
00:11:04 SCOTT@orcl> COL status FORMAT A7
00:11:05 SCOTT@orcl> SELECT object_name, object_type, status
00:11:10   2    FROM user_objects
00:11:10   3   WHERE object_name = 'HELLO_WORLD4';

OBJECT_NAME                    OBJECT_TYPE  STATUS
------------------------------ ------------ -------
HELLO_WORLD4                   TYPE         VALID
HELLO_WORLD4                   TYPE BODY    VALID

2 rows selected.

Elapsed: 00:00:00.01
--直接使用该对象类型
00:11:11 SCOTT@orcl> DECLARE
00:13:21   2    -- Define and instantiate an object instance.
00:13:21   3    my_obj1 hello_world4 := hello_world4('Adam');
00:13:21   4    my_obj2 hello_world4 := hello_world4('Eve');
00:13:21   5  BEGIN
00:13:21   6    -- Test class instance.
00:13:21   7    dbms_output.put_line('Item #1: [' || my_obj1.getQualifiedName || ']');
00:13:21   8    dbms_output.put_line('Item #2: [' || my_obj2.getQualifiedName || ']');
00:13:21   9    dbms_output.put_line('Item #3: [' || my_obj1.getSQLTypeName || ']');
00:13:21  10    dbms_output.put_line('Item #4: [' || my_obj1.getSQLTypeName || ']');
00:13:21  11    -- Test metadata repository with DBMS_JAVA.
00:13:21  12    dbms_output.put_line('Item #5: [' || user || '.' ||
00:13:21  13                         dbms_java.longname('HELLO_WORLD4') || ']');

00:13:21  14  END;
00:13:21  15  /
Item #1: [SCOTT.HelloWorld4.class.Adam]
Item #2: [SCOTT.HelloWorld4.class.Eve]
Item #3: [SCOTT.HELLO_WORLD4]
Item #4: [SCOTT.HELLO_WORLD4]
Item #5: [SCOTT.HELLO_WORLD4]

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

这里你不能直接使用dropjava删除HelloWorld4.class,因为你loadjava的时候是加载的java文件.
--删除HelloWorld3.class是正常的,因为loadjava加载的是HelloWorld3.class
C:\Users\Administrator\Desktop>dropjava -u scott/tiger@orcl HelloWorld3.class

--直接删除报错
C:\Users\Administrator\Desktop>dropjava -u scott/tiger@orcl HelloWorld4.class
Error while dropping HelloWorld4
ORA-29537: 不能直接创建或删除类或资源
--这样才正确删除
C:\Users\Administrator\Desktop>dropjava -u scott/tiger@orcl HelloWorld4.java