Oracle与Java之发布返回一个整型的方法

1. 创建一个Java文件RowCounter.java

import java.sql.*; public class RowCounter { public static int rowCount(String tabName) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); String sql = "select count(*) from " + tabName; int rows = 0; try { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { rows = rs.getInt(1); } rs.close(); stmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } return rows; } }

 

2. 编译Java文件

javac -classpath /u01/app/oracle/product/10.2.0/db_1/jdbc/lib/ojdbc14.jar RowCounter.java

 

3. 使用loadjava导入class文件

loadjava -u hr/oracle -resolve RowCounter.class

 

4.创建一个函数来调用RowCounter的方法

create or replace function row_count(tab_name varchar2) return number
as language java
name 'RowCounter.rowCount(java.lang.String) return int';

 

5. 测试结果

select row_count('employees') from dual;

 

ROW_COUNT('EMPLOYEES')
----------------------
                   107