Oracle PL/SQL 与 Java 集成详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

PL/SQL 与 Java 互相调用[1]：

详细见：Oracle Java 存储过程。

---

2. Java in Database

2.1 加载 Java

-- 从源代码
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Hello" AS
public class Hello {
  public static String sayHello(String name) {
    return "Hello, " + name;
  }
}
/

-- 从类
loadjava -user scott/tiger Hello.class

2.2 发布

CREATE OR REPLACE FUNCTION hello(p_name VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Hello.sayHello(java.lang.String) return java.lang.String';
/

2.3 调用

SELECT hello('Alice') FROM dual;

---

3. Java 存储过程示例

3.1 类

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Calculator" AS
public class Calculator {
  public static int add(int a, int b) {
    return a + b;
  }
  
  public static double calculate(double[] values) {
    double sum = 0;
    for (double v : values) {
      sum += v;
    }
    return sum / values.length;
  }
}
/

3.2 发布

CREATE OR REPLACE FUNCTION calc_add(a NUMBER, b NUMBER) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'Calculator.add(int, int) return int';
/

CREATE OR REPLACE FUNCTION calc_avg(p_values SYS.ODCINUMBERLIST) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'Calculator.calculate(double[]) return double';
/

3.3 调用

SELECT calc_add(1, 2) FROM dual;
SELECT calc_avg(SYS.ODCINUMBERLIST(1, 2, 3, 4, 5)) FROM dual;

---

4. 文件操作

4.1 Java

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FileUtil" AS
import java.io.*;

public class FileUtil {
  public static String readFile(String path) throws Exception {
    BufferedReader reader = new BufferedReader(new FileReader(path));
    StringBuilder sb = new StringBuilder();
    String line;
    while ((line = reader.readLine()) != null) {
      sb.append(line).append("\n");
    }
    reader.close();
    return sb.toString();
  }
  
  public static void writeFile(String path, String content) throws Exception {
    PrintWriter writer = new PrintWriter(new FileWriter(path));
    writer.print(content);
    writer.close();
  }
}
/

4.2 发布

CREATE OR REPLACE FUNCTION read_file(p_path VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'FileUtil.readFile(java.lang.String) return java.lang.String';
/

CREATE OR REPLACE PROCEDURE write_file(p_path VARCHAR2, p_content VARCHAR2)
AS LANGUAGE JAVA
NAME 'FileUtil.writeFile(java.lang.String, java.lang.String)';
/

4.3 权限

-- DBA 授权
BEGIN
  DBMS_JAVA.GRANT_PERMISSION('SCOTT', 'java.io.FilePermission', '<<ALL FILES>>', 'read,write');
END;
/

---

5. 网络

5.1 HTTP

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "HttpUtil" AS
import java.net.*;
import java.io.*;

public class HttpUtil {
  public static String get(String urlStr) throws Exception {
    URL url = new URL(urlStr);
    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
    conn.setRequestMethod("GET");
    
    BufferedReader reader = new BufferedReader(new InputStreamReader(conn.getInputStream()));
    StringBuilder sb = new StringBuilder();
    String line;
    while ((line = reader.readLine()) != null) {
      sb.append(line);
    }
    reader.close();
    return sb.toString();
  }
}
/

CREATE OR REPLACE FUNCTION http_get(p_url VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'HttpUtil.get(java.lang.String) return java.lang.String';
/

5.2 权限

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('SCOTT', 'java.net.SocketPermission', '*', 'connect,resolve');
END;
/

---

6. JDBC in Java

6.1 默认连接

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DbUtil" AS
import java.sql.*;
import oracle.jdbc.*;

public class DbUtil {
  public static int getEmployeeCount() throws Exception {
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM employees");
    rs.next();
    int count = rs.getInt(1);
    rs.close();
    stmt.close();
    conn.close();
    return count;
  }
}
/

CREATE OR REPLACE FUNCTION get_emp_count RETURN NUMBER
AS LANGUAGE JAVA
NAME 'DbUtil.getEmployeeCount() return int';
/

---

7. 数据类型映射

Oracle	Java
NUMBER	int / long / double / java.math.BigDecimal
VARCHAR2	java.lang.String
DATE	java.sql.Timestamp
TIMESTAMP	java.sql.Timestamp
CLOB	oracle.sql.CLOB
BLOB	oracle.sql.BLOB
ARRAY	oracle.sql.ARRAY
STRUCT	oracle.sql.STRUCT

---

8. 异常处理

8.1 Java

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ExTest" AS
public class ExTest {
  public static int divide(int a, int b) throws Exception {
    if (b == 0) {
      throw new Exception("Divide by zero");
    }
    return a / b;
  }
}
/

8.2 PL/SQL

CREATE OR REPLACE FUNCTION divide(a NUMBER, b NUMBER) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'ExTest.divide(int, int) return int';
/

DECLARE
  v_result NUMBER;
BEGIN
  v_result := divide(10, 0);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

---

9. loadjava / dropjava

9.1 loadjava

# 类
loadjava -user scott/tiger Hello.class

# 源
loadjava -user scott/tiger -resolve Hello.java

# JAR
loadjava -user scott/tiger lib.jar

# 解决
loadjava -user scott/tiger -resolve -verbose Hello.java

9.2 dropjava

dropjava -user scott/tiger Hello.class
dropjava -user scott/tiger lib.jar

9.3 查看

SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_type LIKE 'JAVA%';

---

10. 性能

10.1 优势

- 复杂计算
- 网络/IO
- 第三方库
- 跨平台

10.2 劣势

- 启动慢
- 内存
- 不适合频繁调用

---

11. 应用场景

11.1 复杂计算

-- 数学
-- 加密
- 复杂算法

11.2 文件

- 大文件
- 二进制
- 复杂格式

11.3 网络

- HTTP
- 邮件
- API 调用

11.4 第三方

- PDF 生成
- 图像处理
- XML 处理

---

12. 替代

12.1 UTL_HTTP

-- 内置 HTTP
SELECT UTL_HTTP.REQUEST('http://example.com') FROM dual;

详细见：Oracle PL/SQL 内置包大全详解。

12.2 UTL_FILE

-- 内置文件

12.3 PL/SQL

- 简单逻辑
- 数据处理
- 数据库操作

---

13. 常见坑与排错

13.1 ORA-29541

- Java 类无效
- 重新编译

13.2 权限

- DBMS_JAVA.GRANT_PERMISSION
- FilePermission / SocketPermission

13.3 内存

- JAVA_POOL_SIZE
- 内存不足

13.4 字符集

- NLS_CHARSET
- 转换

---

14. 最佳实践

1. 合适场景：复杂
2. 权限最小：安全
3. 错误处理：完整
4. 类型映射：正确
5. JDBC 默认：高效
6. loadjava：管理
7. 替代考虑：UTL_*
8. 测试：验证
9. 性能：监控
10. 文档化：说明

---

15. 参考资料

[1] Oracle Database Java Developer’s Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdev/