oracle数据库中,用SQL Select直接訪問訪問存儲在blob中的excel數據

原文地址：oracle数据库中,用SQL Select直接訪問訪問存儲在blob中的excel數據 作者：gangjh

 在我的工作中,经常需要访问Excel数据或者导入到oracle数据库中

常用的做法是:
   在前端程序中是逐行访问,读取数据.
   在oracle后端也可以处理.还更加方便

我封装了一个通用的package utl_excel.用来用SQL直接查询.

範例1:  excel数据存放在exceldata的blob_data栏位
   select  *
    from  exceldata a,
    table( utl_excel.get_xls_table(blob_data) ) xx
    where a.name='MSOFFICE.EXCEL';

範例2:  直接读取http或者ftp上的excel文件,    utl_lob是另外一个通用package
  select  *
    from    table(
             utl_excel.get_xls_table(utl_lob.url2blob('http://192.168.1.11/data/test.xls')  ) )


原理:
  java使用 jxl.jar 訪問excel ,用pl/sql封装成package

oracle 封装代碼如下,

点击(此处)折叠或打开

1. CREATE OR REPLACE PACKAGE utl_excel AS
   
2. /****************************************************************************
   
3.    Ver Date Author Description
   
4.    --------- ---------- --------------- --------------------------------------------
   
5.    1.0 2013-11-05 gangjh 1. 使用java jxl.jar 訪問blob的excel 2000數據.
   
6.                                                返回table類型數據.
   
7.                                                不支持xlsx,和ods格式
   
8. *******************************************************************************/
   
9. 
   
10.   SUBTYPE ctxHandle IS binary_integer;
    
11.   subtype celltype is varchar2(4000 byte) ; -- limit for oracle 10g
    
12. 
    
13.   type cell_val is record (
    
14.   sheet int,
    
15.   row_idx int,
    
16.   col_idx int,
    
17.   data celltype
    
18.   ) ;
    
19.   type xls_data is table of cell_val ;
    
20.   c_max_cols constant pls_integer := 20;
    
21.   type xls_row is record (
    
22.     sheet int ,
    
23.     row_idx int ,
    
24.     col0 celltype,
    
25.     col1 celltype,
    
26.     col2 celltype,
    
27.     col3 celltype,
    
28.     col4 celltype,
    
29.     col5 celltype,
    
30.     col6 celltype,
    
31.     col7 celltype,
    
32.     col8 celltype,
    
33.     col9 celltype,
    
34.     col10 celltype,
    
35.     col11 celltype,
    
36.     col12 celltype,
    
37.     col13 celltype,
    
38.     col14 celltype,
    
39.     col15 celltype,
    
40.     col16 celltype,
    
41.     col17 celltype,
    
42.     col18 celltype,
    
43.     col19 celltype
    
44.   );
    
45.   
    
46.   type xls_table is table of xls_row ;
    
47.  
    
48. 
    
49. 
    
50. /****************************************
    
51.  with xt as
    
52. ( select a.id,a.name ,xx.*
    
53.     from xmldata a,
    
54.     table( utl_excel.get_xls_val(blob_temp) ) xx
    
55.     where a.name='MSOFFICE.EXCEL'
    
56. )
    
57. 
    
58. select id,name,sheet,row_idx,
    
59. max(decode(col_idx,0,data,'')) col0,
    
60. max(decode(col_idx,1,data,'')) col1,
    
61. max(decode(col_idx,2,data,'')) col2
    
62. from xt
    
63. group by id,name,sheet,row_idx;
    
64. ************************/
    
65.   
    
66.   function get_xls_val(data in blob) RETURN xls_data PIPELINED ;
    
67. 
    
68. 
    
69. /*********************************************
    
70. ex1:
    
71.   select a.id,xx.*,a.name
    
72.     from xmldata a,
    
73.     table( utl_excel.get_xls_table(blob_temp) ) xx
    
74.     where a.name='MSOFFICE.EXCEL' and id=23 and sheet=4
    
75. 
    
76. ex2:
    
77. select * from table(utl_excel.get_xls_table(utl_lob.url2blob('ftp://172.17.2.43/pub/test001.xls')))
    
78.     
    
79. ************************************/
    
80. 
    
81. -- p_sheet := -1 : select all sheet
    
82.   function get_xls_table(p_data in blob, p_sheet int default -1 ) RETURN xls_table PIPELINED ;
    
83. 
    
84.   function p_test(p_data in blob, p_sheet int, p_row int, p_col int) return clob ;
    
85. 
    
86. 
    
87. END utl_excel;
    
88. /
    
89. CREATE OR REPLACE PACKAGE body utl_excel AS
    
90. excel_parsing_error EXCEPTION;
    
91.     pragma EXCEPTION_INIT(excel_parsing_error, -29532);
    
92. /**************** java implement ****************************/
    
93.   FUNCTION newContext(data IN blob) RETURN ctxHandle
    
94.   as LANGUAGE JAVA NAME
    
95.    'corp.excel.Excelobj.newcontext(oracle.sql.BLOB) return int';
    
96. 
    
97.   PROCEDURE closeContext(ctx IN ctxHandle)
    
98.   as LANGUAGE JAVA NAME
    
99.    'corp.excel.Excelobj.closecontext(int)';
    
100. 
     
101.   function getSheets(ctx IN ctxHandle) return binary_integer
     
102.   as LANGUAGE JAVA NAME
     
103.    'corp.excel.Excelobj.getSheets(int) return int';
     
104. 
     
105.   function getRows(ctx IN ctxHandle, sheet binary_integer) return binary_integer
     
106.   as LANGUAGE JAVA NAME
     
107.    'corp.excel.Excelobj.getRows(int, int) return int';
     
108. 
     
109.   function getColumns(ctx IN ctxHandle, sheet binary_integer) return binary_integer
     
110.   as LANGUAGE JAVA NAME
     
111.    'corp.excel.Excelobj.getColumns(int, int) return int';
     
112. 
     
113.  -- for cell data length >4000 byte
     
114.   procedure getClob(ctx IN ctxHandle, sheet binary_integer, p_row in binary_integer, p_col in binary_integer, p_clob in out nocopy clob)
     
115.   as LANGUAGE JAVA NAME
     
116.    'corp.excel.Excelobj.getClob(int, int, int, int, oracle.sql.CLOB[])';
     
117. 
     
118. -- Oracle has a limit of 4000 bytes for a VARCHAR2, >4000 will by trim
     
119.   FUNCTION getString(ctx IN ctxHandle, sheet binary_integer, p_row in binary_integer, p_col in binary_integer) RETURN varchar2
     
120.   as LANGUAGE JAVA NAME
     
121.    'corp.excel.Excelobj.getString(int, int, int, int) return java.lang.String';
     
122. 
     
123.   function getSheetCount(ctx IN ctxHandle) return binary_integer
     
124.   as LANGUAGE JAVA NAME
     
125.    'corp.excel.Excelobj.getSheetCount(int) return int';
     
126. 
     
127. /*********************************************/
     
128. 
     
129. function get_xls_val(data in blob) RETURN xls_data PIPELINED
     
130. is
     
131. ctx utl_excel.ctxHandle ;
     
132. r int ;
     
133. sheet_cnt pls_integer ;
     
134. row_cnt pls_integer;
     
135. col_cnt pls_integer;
     
136. cell cell_val ;
     
137. begin
     
138.   ctx := newcontext(data) ;
     
139.   
     
140.   sheet_cnt := getSheets(ctx) ;
     
141.   for s in 0.. sheet_cnt-1 loop --多個excel sheet
     
142.     cell.sheet := s;
     
143.     row_cnt := getrows(ctx, s) ;
     
144.     col_cnt := getColumns(ctx, s) ;
     
145.     for r in 0..row_cnt-1 loop --sheet中抓每一行
     
146.       cell.row_idx :=r ;
     
147.       for c in 0..col_cnt-1 loop --每行的每一列
     
148.         cell.col_idx := c;
     
149.         cell.data := getString(ctx,s, r,c) ;
     
150.         pipe row (cell);
     
151.       end loop;
     
152.     end loop;
     
153.   end loop;
     
154.   closeContext(ctx) ;
     
155.   return;
     
156.   
     
157.  exception
     
158.     when NO_DATA_NEEDED then
     
159.         utl_excel.closeContext(ctx) ;
     
160. end get_xls_val;
     
161. 
     
162. 
     
163. procedure get_xls_row_val(p_row in out xls_row, ctx ctxHandle, sheet pls_integer,row_idx pls_integer, maxcol pls_integer) is
     
164.   v_cellval celltype ;
     
165. begin
     
166.   for c in 0..maxcol-1 loop
     
167.     v_cellval := getString(ctx, sheet, row_idx, c) ;
     
168.     case
     
169.       when c=0 then p_row.col0 := v_cellval ;
     
170.       when c=1 then p_row.col1 := v_cellval ;
     
171.       when c=2 then p_row.col2 := v_cellval ;
     
172.       when c=3 then p_row.col3 := v_cellval ;
     
173.       when c=4 then p_row.col4 := v_cellval ;
     
174.       when c=5 then p_row.col5 := v_cellval ;
     
175.       when c=6 then p_row.col6 := v_cellval ;
     
176.       when c=7 then p_row.col7 := v_cellval ;
     
177.       when c=8 then p_row.col8 := v_cellval ;
     
178.       when c=9 then p_row.col9 := v_cellval ;
     
179.       when c=10 then p_row.col10 := v_cellval ;
     
180.       when c=11 then p_row.col11 := v_cellval ;
     
181.       when c=12 then p_row.col12 := v_cellval ;
     
182.       when c=13 then p_row.col13 := v_cellval ;
     
183.       when c=14 then p_row.col14 := v_cellval ;
     
184.       when c=15 then p_row.col15 := v_cellval ;
     
185.       when c=16 then p_row.col16 := v_cellval ;
     
186.       when c=17 then p_row.col17 := v_cellval ;
     
187.       when c=18 then p_row.col18 := v_cellval ;
     
188.       when c=19 then p_row.col19 := v_cellval ;
     
189.     end case;
     
190.   end loop;
     
191. end ;
     
192. 
     
193. 
     
194. 
     
195. 
     
196. function get_xls_table(p_data in blob, p_sheet int) RETURN xls_table PIPELINED
     
197. is
     
198. ctx utl_excel.ctxHandle ;
     
199. r int ;
     
200. row_cnt pls_integer;
     
201. col_cnt pls_integer;
     
202. sheet_cnt pls_integer ;
     
203. vrow xls_row ;
     
204. begin
     
205.   
     
206.   ctx := utl_excel.newcontext(p_data) ;
     
207.   sheet_cnt := getSheets(ctx) ;
     
208.   
     
209.   <<loop_sheet>>
     
210.   for s in 0.. sheet_cnt-1 loop --多個excel sheet
     
211.     --?
     
212.     if (p_sheet = -1 or p_sheet = s) then
     
213.     
     
214. 
     
215.       vrow.sheet := s ;
     
216.       
     
217.       row_cnt := getrows(ctx, s) ;
     
218.       
     
219.       col_cnt := getColumns(ctx, s) ;
     
220.       if col_cnt >c_max_cols then
     
221.         col_cnt := c_max_cols ;
     
222.       end if ;
     
223.       
     
224.       <<loop_rows>>
     
225.       for r in 0..row_cnt-1 loop --抓每一行數據
     
226.         vrow.row_idx := r;
     
227.         get_xls_row_val(vrow, ctx, s, r, col_cnt ) ;
     
228.         pipe row (vrow);
     
229.       end loop loop_rows;
     
230.     end if ;
     
231.     
     
232.   end loop loop_sheet;
     
233. 
     
234.   utl_excel.closeContext(ctx) ;
     
235.   
     
236.   exception
     
237.     when NO_DATA_NEEDED then
     
238.         utl_excel.closeContext(ctx) ;
     
239.     when excel_parsing_error then
     
240.         utl_excel.closeContext(ctx) ;
     
241.         raise_application_error(-20013, 'open excel error');
     
242. end get_xls_table;
     
243. 
     
244. 
     
245. function p_test(p_data in blob, p_sheet int, p_row int, p_col int) return clob is
     
246.   ctx utl_excel.ctxHandle ;
     
247.   cc clob ;
     
248. begin
     
249.   dbms_lob.createtemporary(cc, true, dbms_lob.call);
     
250.   ctx := newContext(p_data) ;
     
251.   getClob(ctx, p_sheet, p_row, p_col, cc) ;
     
252.   closeContext(ctx) ;
     
253.   return cc ;
     
254. end ;
     
255. 
     
256. 
     
257. 
     
258. END utl_excel;
     
259. /

java 代碼 Excelobj.java

点击(此处)折叠或打开

1. create or replace and compile java source named "corp/excel/Excelobj" as
   
2. package corp.excel;
   
3. //import sqlj.runtime.ref.DefaultContext;
   
4. //import sqlj.runtime.ConnectionContext;
   
5. import oracle.CartridgeServices.ContextManager;
   
6. import jxl.Workbook;
   
7. import jxl.Sheet;
   
8. import jxl.Cell;
   
9. 
   
10. import java.io.IOException ;
    
11. import java.sql.SQLException;
    
12. import oracle.CartridgeServices.CountException ;
    
13. import jxl.read.biff.BiffException ;
    
14. import oracle.CartridgeServices.InvalidKeyException ;
    
15. 
    
16. /**************
    
17. 2014-02-14 重新封裝jxl.Workbook
    
18. ***************/
    
19. public class Excelobj
    
20. {
    
21.   public static int newcontext(oracle.sql.BLOB data)
    
22.   throws SQLException, CountException, IOException, BiffException
    
23.   {
    
24.     Workbook xls = Workbook.getWorkbook( data.getBinaryStream() ) ;
    
25.     return ContextManager.setContext(xls) ;
    
26.   }
    
27. 
    
28.   public static int newcontext(oracle.sql.BFILE data)
    
29.   throws SQLException, CountException ,IOException, BiffException
    
30.   {
    
31.     int r ;
    
32.     Workbook xls = Workbook.getWorkbook( data.getBinaryStream() ) ;
    
33.     return ContextManager.setContext(xls) ;
    
34.   }
    
35. 
    
36. 
    
37.  public static int getSheets(int ctx )
    
38.   throws SQLException, CountException, InvalidKeyException
    
39.   {
    
40.     Workbook xls = (Workbook) ContextManager.getContext(ctx) ;
    
41.     return xls.getNumberOfSheets();
    
42.   }
    
43. 
    
44. 
    
45. 
    
46.  public static int getRows(int ctx, int sheet)
    
47.   throws SQLException, CountException, InvalidKeyException
    
48.   {
    
49.     Workbook xls = (Workbook)ContextManager.getContext(ctx) ;
    
50.     Sheet xls_sheet = xls.getSheet(sheet) ;
    
51.     return xls_sheet.getRows();
    
52.   }
    
53. 
    
54.  public static int getColumns(int ctx, int sheet)
    
55.   throws SQLException, CountException, InvalidKeyException
    
56.   {
    
57.     Workbook xls = (Workbook)ContextManager.getContext(ctx) ;
    
58.     Sheet xls_sheet = xls.getSheet(sheet);
    
59.     return xls_sheet.getColumns() ;
    
60.   }
    
61. 
    
62. 
    
63.   public static void getClob(int ctx, int sheet, int row, int col, oracle.sql.CLOB[] outval)
    
64.   throws SQLException, CountException, InvalidKeyException
    
65.   {
    
66.     Workbook xls = (Workbook)ContextManager.getContext(ctx) ;
    
67.     Sheet xls_sheet = xls.getSheet(sheet);
    
68.     Cell vcell = xls_sheet.getCell(col,row) ;
    
69.     outval[0].setString(1, vcell.getContents()) ;
    
70.   }
    
71.   
    
72. /*****************************************
    
73.     Oracle has a limit of 4000 bytes for VARCHAR2
    
74. ******************************************/
    
75.   public static String getString(int ctx, int sheet, int row, int col)
    
76.   throws SQLException, CountException, InvalidKeyException
    
77.   {
    
78.     Workbook xls = (Workbook)ContextManager.getContext(ctx) ;
    
79.     Sheet xls_sheet = xls.getSheet(sheet);
    
80.     
    
81.     String v_str = xls_sheet.getCell(col,row).getContents() ;
    
82.     
    
83.     byte[] v_bytes = v_str.getBytes() ;
    
84.     
    
85.     if (v_bytes.length <= 4000) {
    
86.         return v_str;
    
87.     } else {
    
88.         byte[] buf = new byte[4000];
    
89.         System.arraycopy(v_bytes, 0, buf, 0, 4000);
    
90.         return new String(buf) ;
    
91.     }
    
92.   }
    
93.   
    
94. 
    
95. 
    
96.   public static int getcnt(int ctx)
    
97.   throws SQLException, CountException, InvalidKeyException
    
98.   {
    
99.     return ContextManager.count ;
    
100.   }
     
101. 
     
102. 
     
103.   public static int closecontext(int ctx)
     
104.   throws SQLException, CountException, InvalidKeyException
     
105.   {
     
106.     Workbook xls = (Workbook) ContextManager.clearContext(ctx) ;
     
107.     xls.close() ;
     
108.     xls = null;
     
109. 
     
110.     return ContextManager.count ;
     
111.   }
     
112. }
     
113. /

注意事项:
  1. oracle 10g的jdk是1.4
  jxl版本不能用最新的.  我用的是jxl -2 .6 .jar

2.  装入jar请用 loadjava
  loadjava -u user/pass  -v -f jxl-2.6.jar

3. pb装入excel到blob请用updateblob

4. Excel 2007可以用as_xlsx_read读取,我的思路参考as_xlsx_read后些出来的
  http://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/
  select * from table ( as_read_xlsx . read ( as_read_xlsx . file2blob ( 'DOC' , 'Book1.xlsx' ) ) );


欢迎和大家一起交流oracle技术.

2014/02/24修改
當長度>4000時截斷
                                                   阿飞
                                              2014/02/21