java调用oracle含输入和输出数组的存储过程

﹏ヽ暗。殇╰゛Y 2022-06-09 03:37 140阅读 0赞

package procedure;

import java.sql.CallableStatement;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;

import oracle.sql.ArrayDescriptor;  
import util.DbUtil;

/\*\*  
 \* 测试 java调用oracle数据库的存储过程3   输入、输出数组  
 \* @author sweetanan  
 \*  
 \*/  
public class TestProcedure3 \{

/\*\*  
  \* @param args  
  \* @throws ClassNotFoundException  
  \*/  
 public static void main(String\[\] args) throws Exception \{  
  /\*

此部分为oracle操作

create table test1(  
   name varchar2(20),  
   n1 number(9,2)  
   );  
     
    
   --1、创建数组  
   CREATE OR REPLACE TYPE TEST\_ARRAY IS VARRAY(50000) OF NUMBER(9) --定长  
   CREATE OR REPLACE TYPE TEST\_ARRAY2 AS TABLE OF NUMBER(9,2) --不定长  
   --2、开始分页存储过程  
   create or replace procedure testArr(name in varchar2, arrRows in TEST\_ARRAY2, res out TEST\_ARRAY2) Authid Current\_User is  
        begin  
        res := TEST\_ARRAY2(); --千万不能少  
        for i in 1 .. arrRows.COUNT loop  
        insert into test1(name, n1) values(name||arrRows(i), arrRows(i));  
        res.EXTEND; --数组扩展长度，第一次loop时，COUNT=1,以后依次+1;  
        res(res.COUNT) := arrRows(i) \* 2;  
        --res(i) := arrRows(i) \* 2;  
        end loop;  
        end;  
   /  
   \*/

Connection con = DbUtil.getConnection();  
  CallableStatement cs = con.prepareCall("\{call TESTARR(?, ?, ?)\}");  
    
  ArrayDescriptor aDes = new ArrayDescriptor("TEST\_ARRAY2", con);  
  double\[\] pras = \{1.1, 2.2, 3.3, 4.5, 5, 6, 7, 8.8, 9, 9.5\};  
  oracle.sql.ARRAY arr = new oracle.sql.ARRAY(aDes, con, pras);  
    
  cs.setString(1, "anan");  
  cs.setArray(2, arr);  
    
  cs.registerOutParameter(3, oracle.jdbc.OracleTypes.ARRAY, "TEST\_ARRAY2");  
    
  cs.execute();  
    
  oracle.sql.ARRAY res = (oracle.sql.ARRAY)cs.getArray(3);  
    
  Object\[\] strs = (Object\[\])res.getArray();  
    
  for (Object str : strs)  
   System.out.println(str);  
    
  DbUtil.close(con, cs, null);  
 \}

数据库连接工具类

package util;

import java.sql.\*;

public class DbUtil \{  
   
 public static Connection getConnection()\{  
  Connection conn = null;  
  try \{  
   //Class.forName("com.mysql.jdbc.Driver");  
   //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp?useUnicode=true&characterEncoding=utf8","root","123456");  
     
   Class.forName("oracle.jdbc.driver.OracleDriver");  
   String url = "jdbc:oracle:thin:@127.0.0.1:1521:myora";  
   String userName = "scott";  
   String password = "m123";

conn = DriverManager.getConnection(url, userName, password);  
  \} catch (Exception e) \{  
   // TODO Auto-generated catch block  
   e.printStackTrace();  
  \}  
  return conn;  
 \}  
   
 public static void close(Connection conn)\{  
  try \{  
   conn.close();  
  \} catch (SQLException e) \{

e.printStackTrace();  
  \}  
 \}  
   
 public static void close(Connection conn, Statement sta, ResultSet res)\{  
  try \{  
   if (res != null)  
    res.close();  
   if (sta != null)  
    sta.close();  
   if (conn != null)  
    conn.close();  
  \} catch (SQLException e) \{

e.printStackTrace();  
  \}  
 \}  
   
\}