pl/sql java_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版本：

[oracle@centos6 bin]$ cd $ORACLE_HOME/jdk/bin/[oracle@centos6 bin]$ javac-version

javac1.5.0_17

javac的版本是1.5，那么导入的jar包不能是1.6及以上版本编译的。好吧，经过不懈努力，我下载了3.0.1版本的POI的jar包，

POI的历史版本可以根据下面的链接下载：

二、导入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/tiger@cat poi-3.0.1-FINAL-20070705.jar

loadjava -r -f -o -user scott/tiger@cat poi-contrib-3.0.1-FINAL-20070705.jar

loadjava -r -f -o -user scott/tiger@cat poi-scratchpad-3.0.1-FINAL-20070705.jar

如图所示，这么老的版本导入的时候还是会报错，不过没有关系，报错的都是本例用不着的类，要是用现在最新版本的话，就没这么幸运了╮(╯▽╰)╭

我们可以用SQL查询一下导入的情况：

SELECT OBJECT_NAME,

DBMS_JAVA.LONGNAME(OBJECT_NAME) JAVA_CLASS_NAME,

O.STATUSFROMUSER_OBJECTS OWHERE OBJECT_TYPE LIKE 'JAVA CLASS';

通过拼写SQL的方式，生成无效的类的删除语句：

SELECT 'execute immediate''drop JAVA CLASS "' || OBJECT_NAME || '"'';'

FROMUSER_OBJECTS OWHERE 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 asimportjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.SQLException;importjava.sql.Statement;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;public classCreateExcel {private staticHSSFWorkbook wb;private staticHSSFSheet 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 voidCreateExcel2003(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= newHSSFWorkbook();

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 = newHSSFCell[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= newHSSFCell[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= newFileOutputStream(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) ASLANGUAGE 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();打印的信息：