oracle PL/SQL调用Java生成Excel
现在有个需求， 要求编写oracle存储过程生成Excel文件到指定目录， 但是oracle自己的API貌似不太给力， 所以只能通过另一种更强大的语言来实现了 ——Java。有一个Java框架叫POI,处理Excel起来非常好用，现在我把过程记录下来：
一、下载POI的jar包
我的测试数据库的版本是11g， 所以下载的jar包不能太新，否则导入jar包的时候会报版本错误， 但又产生了一个问题，旧版本的jar包当然功能不是很全，包括我之前比较看重的给Excel加密的功能。之前为了这个功能试了很多版本，都不理想，只能放弃了，不知道oracle 12c版本会怎样，以后再试吧。。。
步入正题，我们的jar包尽可能要新一些，那么，我们要先看看oracle 11g的Java版本：
[[email protected] bin]$ cd $ORACLE_HOME/jdk/bin/ [[email protected] bin]$ javac -version javac 1.5.0_17
javac的版本是1.5，那么导入的jar包不能是1.6及以上版本编译的。好吧，经过不懈努力，我下载了3.0.1版本的POI的jar包，
POI的历史版本可以根据下面的链接下载：
https://archive.apache.org/dist/poi/release/bin/
二、导入POI的jar包到数据库
1.把jar包上传到服务器的一个目录，我这里是/java/目录：
2.给用户授权：
普通oracle用户要操作Java，必须通过sys授予JAVASYSPRIV权限：
GRANT JAVASYSPRIV TO SCOTT;
3.cd到上传目录，通过loadjava 命令导入oracle数据库：
loadjava的基本语法：
loadjava {-user | -u} username/password[@database]
[option ...] filename [filename] ...
具体选项请参考其他资料，这里不再赘述。
我分别用如下命令导入：
loadjava -r -f -o -user scott/[email protected] poi-3.0.1-FINAL-20070705.jar
loadjava -r -f -o -user scott/[email protected] poi-contrib-3.0.1-FINAL-20070705.jar
loadjava -r -f -o -user scott/[email protected] poi-scratchpad-3.0.1-FINAL-20070705.jar
如图所示，这么老的版本导入的时候还是会报错，不过没有关系，报错的都是本例用不着的类，要是用现在最新版本的话，就没这么幸运了╮(╯▽╰)╭
我们可以用SQL查询一下导入的情况：
SELECT OBJECT_NAME, DBMS_JAVA.LONGNAME(OBJECT_NAME) JAVA_CLASS_NAME, O.STATUS FROM USER_OBJECTS O WHERE OBJECT_TYPE LIKE 'JAVA CLASS';
通过拼写SQL的方式，生成无效的类的删除语句：
SELECT 'execute immediate ''drop JAVA CLASS "' || OBJECT_NAME || '"'';' FROM USER_OBJECTS O WHERE OBJECT_TYPE LIKE 'JAVA CLASS' AND O.STATUS <> 'VALID';
把生成的语句执行，就删除了没用的而且无效的类了：
BEGIN EXECUTE IMMEDIATE 'drop JAVA CLASS "/5e316ace_CommonsLogger"'; EXECUTE IMMEDIATE 'drop JAVA CLASS "/eb16769d_HSSFCellUtil"'; EXECUTE IMMEDIATE 'drop JAVA CLASS "/ff8d85a1_HSSFRegionUtil"'; END;
三、编写Java生成Excel程序，创建JAVA SOURCE
在这里我写了一个通用的Java类，只要对方法传入SQL字符串和文件路径及名称组成的字符串这两个参数就可以完成功能，代码如下，并把它放到
PL/SQL Developer中创建Java Source：
create or replace and compile java source named createexcel as import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class CreateExcel { private static HSSFWorkbook wb; private static HSSFSheet sheet; private static String connStr = "jdbc:oracle:thin:@192.168.*.*:1521:cat"; private static String userStr = "scott"; private static String pwdStr = "tiger"; //@SuppressWarnings("deprecation") public static void CreateExcel2003(String sql, String fileDir) { Connection conn = null; Statement stmt = null; ResultSet rs = null; int rowNum = 0; try { Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection(connStr, userStr, pwdStr); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); //创建题头 if (rs.next()) { wb = new HSSFWorkbook(); sheet = wb.createSheet("Result");// 建立新的sheet对象 // 题头字体样式 HSSFFont font = wb.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 12);// 设置字体大小 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(font); HSSFRow row = sheet.createRow(rowNum); // 获取列数和列名 ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); //System.out.println(numberOfColumns); HSSFCell[] cells = new HSSFCell[numberOfColumns]; // 根据列数获取列名题头 for (int i = 0; i < numberOfColumns; i++) { cells[i] = row.createCell((short) i); cells[i].setCellValue(rsMetaData.getColumnName(i+1)); cells[i].setCellStyle(cellStyle); } } //数据行样式 HSSFCellStyle cellStyle1 = wb.createCellStyle(); //创建数据行 while(rs.next()){ rowNum ++; HSSFRow row = sheet.createRow(rowNum);// 建立新行 // 获取列数 ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); HSSFCell[] cells = new HSSFCell[numberOfColumns]; // 获取数据 for (int i = 0; i < numberOfColumns; i++) { cells[i] = row.createCell((short) i); cells[i].setCellValue(rs.getString(i+1)); cells[i].setCellStyle(cellStyle1); // 自动调整列宽 sheet.autoSizeColumn((short) i); } // for (int i = 0; i < numberOfColumns; i++) { // // 自动调整列宽 // sheet.autoSizeColumn((short) i); // } } FileOutputStream fileOut = new FileOutputStream(fileDir); wb.write(fileOut); fileOut.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } } }
四、创建调用Java代码的存储过程
定义的存储过程开头是一样的，唯一不同的就是 “AS LANGUAGE ”后面的语法：
{IS | AS} LANGUAGE JAVA
NAME 'method_fullname (java_type[, java_type]...)
[return java_type]';
我这里是：
CREATE OR REPLACE PROCEDURE CREATE_EXCEL(P_SQL VARCHAR2, P_PATH VARCHAR2) AS LANGUAGE JAVA NAME 'CreateExcel.CreateExcel2003(java.lang.String,java.lang.String)';
第一个参数是SQL的字符串，第二个参数是文件路径+文件名称。
这里需要注意一下，单引号里写的是”Java的类名全称.方法名(数据类型,...)”，区分大小写的。
五、调用程序：
输入相应参数：
begin -- Call the procedure create_excel(p_sql => 'select * from emp', p_path => '/share/emp.xls'); end;
程序运行成功！
看看文件目录：
文件已经生成，从服务器下载后，打开看看：
ok~~
假如程序有错误，而且是Java代码的错误，我们怎样获取错误信息呢？这里只需要启用oracle的DBMS_JAVA.SET_OUTPUT();这个过程，
此过程的语法是：
PROCEDURE DBMS_JAVA.SET_OUTPUT(buffersize NUMBER);
具体使用方法：
SQL> SET serveroutput ON size 1000000; SQL> call dbms_java.set_output(1000000);
如果Java代码有问题，我们就可以获得Java的打印信息，包括System.out.println();打印的信息：