oracle PL/SQL调用Java生成Excel_数据库/系统_互联网开发技术网_传播最新的编程技术

[导读]现在有个需求，要求编写oracle存储过程生成Excel文件到指定目录，但是oracle自己的API貌似不太给力，所以只能通过另一种更强大的语言来实现了 ——Java。

现在有个需求，要求编写oracle存储过程生成Excel文件到指定目录，但是oracle自己的API貌似不太给力，所以只能通过另一种更强大的语言来实现了 ——Java。有一个Java框架叫POI,处理Excel起来非常好用，现在我把过程记录下来：

一、下载POI的jar包

我的测试数据库的版本是11g，所以下载的jar包不能太新，否则导入jar包的时候会报版本错误，但又产生了一个问题，旧版本的jar包当然功能不是很全，包括我之前比较看重的给Excel加密的功能。之前为了这个功能试了很多版本，都不理想，只能放弃了，不知道oracle 12c版本会怎样，以后再试吧。。。

步入正题，我们的jar包尽可能要新一些，那么，我们要先看看oracle 11g的Java版本：

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权限：

3.cd到上传目录，通过loadjava 命令导入oracle数据库：

loadjava的基本语法：

具体选项请参考其他资料，这里不再赘述。

我分别用如下命令导入：

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查询一下导入的情况：

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

把生成的语句执行，就删除了没用的而且无效的类了：

三、编写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 ”后面的语法：

我这里是：

第一个参数是SQL的字符串，第二个参数是文件路径+文件名称。

这里需要注意一下，单引号里写的是”Java的类名全称.方法名(数据类型,...)”，区分大小写的。

五、调用程序：

输入相应参数：