oracle存储过程

FJJJ8

浏览: 151132 次

最近访客更多访客>>

confused9090

阿里里

north0808

peacenik

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (112)

社区版块

存档分类

java oracle

本文为摘录学习笔记.

一：无返回值的存储过程

存储过程为：

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS

BEGIN

   INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2);

END TESTA;

然后呢，在java里调用时就用下面的代码：

package com.hyq.src;

import java.sql.*;

import java.sql.ResultSet;

public class TestProcedureOne {

public TestProcedureOne() {

}

public static void main(String[] args ){

    String driver = "oracle.jdbc.driver.OracleDriver";

    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    CallableStatement cstmt = null;

    try {

      Class.forName(driver);

      conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");

      CallableStatement proc = null;

      proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");

      proc.setString(1, "100");

      proc.setString(2, "TestOne");

      proc.execute();

    }

    catch (SQLException ex2) {

      ex2.printStackTrace();
    }

    catch (Exception ex2) {

      ex2.printStackTrace();
    }

    finally{
      try {
        if(rs != null){

          rs.close();

          if(stmt!=null){

            stmt.close();
          }

          if(conn!=null){

            conn.close();
          }

        }
      }

      catch (SQLException ex1) {
      }
    }
}
}

当然了，这就先要求要建张表TESTTA,里面两个字段（I_ID，I_NAME）。

二：有返回值的存储过程（非列表）

存储过程为：

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS

BEGIN

   SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;

END TESTB;

在java里调用时就用下面的代码：

package com.hyq.src;

public class TestProcedureTWO {

public TestProcedureTWO() {

}

public static void main(String[] args ){

    String driver = "oracle.jdbc.driver.OracleDriver";

    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    try {

      Class.forName(driver);

      conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");

      CallableStatement proc = null;

      proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");

      proc.setString(1, "100");

      proc.registerOutParameter(2, Types.VARCHAR);

      proc.execute();

      String testPrint = proc.getString(2);

      System.out.println("=testPrint=is="+testPrint);

    }

    catch (SQLException ex2) {

      ex2.printStackTrace();

    }

    catch (Exception ex2) {

      ex2.printStackTrace();

    }

    finally{

      try {

        if(rs != null){

          rs.close();

          if(stmt!=null){

            stmt.close();

          }

          if(conn!=null){

            conn.close();

          }

        }

      }

      catch (SQLException ex1) {

      }

    }

}

}

}

注意，这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。

三：返回列表

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，

1，建一个程序包。如下：

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

2，建立存储过程，存储过程为：

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS

BEGIN

    OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;

END TESTC;

可以看到，它是把游标（可以理解为一个指针），作为一个out 参数来返回值的。

在java里调用时就用下面的代码：

package com.hyq.src;

import java.sql.*;

import java.io.OutputStream;

import java.io.Writer;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import oracle.jdbc.driver.*;

public class TestProcedureTHREE {

public TestProcedureTHREE() {

}

public static void main(String[] args ){

    String driver = "oracle.jdbc.driver.OracleDriver";

    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

    Statement stmt = null;

    ResultSet rs = null;

    Connection conn = null;

    try {

      Class.forName(driver);

      conn = DriverManager.getConnection(strUrl, "hyq", "hyq");

      CallableStatement proc = null;

      proc = conn.prepareCall("{ call hyq.testc(?) }");

      proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

      proc.execute();

      rs = (ResultSet)proc.getObject(1);

      while(rs.next())

      {

          System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");

      }

    }

    catch (SQLException ex2) {

      ex2.printStackTrace();

    }

    catch (Exception ex2) {

      ex2.printStackTrace();

    }

    finally{

      try {

        if(rs != null){

          rs.close();

          if(stmt!=null){

            stmt.close();

          }

          if(conn!=null){

            conn.close();

          }
        }
      }
      catch (SQLException ex1) {

      }
    }
}
}

在这里要注意，在执行前一定要先把oracle的驱动包放到class路径里，否则会报错的。

使用oracle存储过程分页的例子
1，建一个程序包。如下：

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

2，建立存储过程，存储过程为：

create or replace procedure TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR,lowerNum in numeric,higherNum in numeric) is
begin
OPEN p_CURSOR FOR select * from ( select row_.*, rownum rownum_ from (select id,title,status from HYQTEST) row_ where rownum <= higherNum) where rownum_ >lowerNum;
end TESTC;

使用plsql测试：
declare
lowerNum integer;
higherNum integer;
id varchar2(10);
title varchar2(500);
status numeric;
c testpackage.Test_CURSOR;
rownum_ integer;
begin
lowerNum:=1;
higherNum:=10;
TESTC(c,lowerNum,higherNum);
LOOP
FETCH c INTO id,title,status,rownum_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('=行号='||rownum_||'=='||id||'==='||title||'===='||status||'==');
END LOOP;
CLOSE c;

end;

1.插入，无返回值 oracle 的 procedure 为
create or replace procedure add_project(proid      varchar2,
                                        proversion number,
                                        proname    varchar2,
                                        customID varchar2 default null) is
begin
  insert into T_LPROMIS_XMGL_GBBXMXX pro
    (xmid, xmbbh, xmmc,pro.khid)
  values
    (proid, proversion, proname,customID);
end;

2.查询，有一个out varchar的返回值oracle 的 procedure 为
create or replace procedure query_project
(proname in varchar2, proid out varchar2)
is 
begin
  select pro.xmid into proid from T_LPROMIS_XMGL_GBBXMXX pro
where pro.xmmc = proname;
end;

3.查询，有一个out ref cursor的返回值oracle 的 procedure 为
create or replace package query_project_pck
as 
type pro_ref_cursor_type is ref cursor;
type pro_table is table of varchar(100);
type pro_record is record(name varchar(100),id varchar(100));
procedure pro_procedure(name varchar,id varchar);
end query_project_pck; -- define package

create or replace procedure query_pro(pro_result out query_project_pck.pro_ref_cursor_type) is
begin
  open pro_result for
    select pro.xmid, pro.xmmc,pro.updatetime from T_LPROMIS_XMGL_GBBXMXX pro return pro_record_type;
end; --create procedure using ref cursor


下面就是jdbc call procedure 了:
public static void test() throws ClassNotFoundException, SQLException{
		System.out.println("=====Test.test=====");
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.32.227:1521:orcl","use","password");
		//Connection conn = new Conn().getConnection();
		CallableStatement cstmt = null;
		ResultSet rs = null;
		try{
			
			// 过程调用，无返回值
			cstmt = conn.prepareCall("call add_project(?,?,?)");
			cstmt.setString(1,"testproject1");
			cstmt.setInt(2, 0);
			cstmt.setString(3, "super man project");
			cstmt.execute();
			cstmt.setString(1, "testproject2");
			cstmt.setInt(2, 0);
			cstmt.setString(3, "a worker project");
			cstmt.execute();
			cstmt.setString(1, "testproject3");
			cstmt.setInt(2, 0);
			cstmt.setString(3, "a user project");
			cstmt.execute();
			System.out.println("插入成功");

			//过程调用，普通类型的返回值，如第二个参数为字符串类型的返回值
			cstmt = conn.prepareCall("call query_project(?,?)");
			cstmt.registerOutParameter(2,Types.VARCHAR);//设置第二个参数为字符串类型返回值
			cstmt.setString(1, "项目");
			cstmt.execute();
			System.out.println(cstmt.getString(2));//获取返回值

                       //过程调用，索引类型的返回值
			cstmt = conn.prepareCall("call query_pro(?)");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);//设置第一个参数为索引类型返回值
			cstmt.execute();
			rs = (ResultSet) cstmt.getObject(1);//获得此索引返回的集合
			while(rs.next())
			{
				
				System.out.println("id= "+rs.getString(1));
				System.out.println("name="+rs.getString(2));
				System.out.println("msg="+rs.getString(3));
			}
			
			if(!rs.isClosed())
				rs.close();
			cstmt.close();
			conn.close();
		}catch(Exception e){
			System.out.println("=====Test.test=====\n操作失败");
			e.printStackTrace();
		}
	}

package bing.oracleprocedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import oracle.jdbc.OracleTypes;

/**
 * 练习存储过程的调用
 * @author bing
 * @version 2011-07-09
 */
public class Test {
	public static void test(){
		System.out.println("=====Test.test=====");
		Connection conn = new Conn().getConnection(); // 获得数据连接对象
		CallableStatement cstmt = null;
		ResultSet rs = null;
		try{
			/*
			 -- 创建练习用的表t_test
			create table t_test(
			  t_id number(4) not null,
			  t_name varchar2(20),
			  t_msg varchar2(100)
			);
			alter table t_test add constraint pk_t_test primary key(t_id);
			-- 编写练习用的过程up_insert_test 
			-- 插入数据到表t_test
			create or replace procedure up_insert_test(v_id in number, v_name in varchar2, v_msg in varchar2)
			is
			begin
			  insert into t_test(t_id,t_name,t_msg) values(v_id,v_name,v_msg);
			end up_insert_test;
			/
			 */
			// 过程调用，无返回值
			cstmt = conn.prepareCall("call up_insert_test(?,?,?)");
			cstmt.setInt(1, 1);
			cstmt.setString(2, "bing");
			cstmt.setString(3, "super man");
			cstmt.execute();
			cstmt.setInt(1, 2);
			cstmt.setString(2, "admin");
			cstmt.setString(3, "a worker");
			cstmt.execute();
			cstmt.setInt(1, 3);
			cstmt.setString(2, "user");
			cstmt.setString(3, "a user");
			cstmt.execute();
			System.out.println("插入成功");
			
			/*
		 	-- 编写练习用的存储过程up_select_test_1 
			-- 输入id，输出name，查询t_test中是单条记录
			create or replace procedure up_select_test_1
			(v_id in number, v_name out varchar2)
			is
			begin
			  select t_name into v_name from t_test where t_id = v_id;
			end up_select_test_1;
			/
			 */
			// 过程调用，返回单条记录
			// oracle过程没有返回值，所有返回值都是通过out参数来替代的
			cstmt = conn.prepareCall("call up_select_test_1(?,?)");
			cstmt.setInt(1, 1);
			cstmt.registerOutParameter(2, Types.VARCHAR);// 注册out参数，注意序号对应过程的参数序号
			cstmt.execute();
			String name = cstmt.getString(2);// 获取out参数，注意序号对应过程的参数序号
			System.out.println("查询成功");
			System.out.println("name = " + name);
			
			/*
			-- 编写包upk_select_test,为存储过程up_select_test_2准备 --
			create or replace package upk_select_test
			as type uc_test is ref cursor;
			end upk_select_test;
			/
			-- 编写存储过程up_select_test_2 
			-- 查询表t_test中的所有记录
			create or replace procedure up_select_test_2
			(uc_result out upk_select_test.uc_test)
			is
			begin
			  open uc_result for select * from t_test;
			end up_select_test_2;
			/
			 */
			// 过程调用，返回多条记录
			// 集合不能用一般的参数，必须要用pagkage，从上面的注释可以看到游标作为out参数，过程返回的是一个游标
			cstmt = conn.prepareCall("call up_select_test_2(?)");
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.execute();
			rs = (ResultSet) cstmt.getObject(1);
			System.out.println("查询成功");
			while(rs.next()){
				System.out.println("id = " + rs.getString(1) 
						+ "  name = " + rs.getString(2) 
						+ "  msg = " + rs.getString(3));
			}
			
			rs.close();
			cstmt.close();
			conn.close();
		}catch(Exception e){
			System.out.println("=====Test.test=====\n操作失败");
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		new Test().test();
		/* 控制台输出：
		   =====Test.test=====
			插入成功
			查询成功
			name = bing
			查询成功
			id = 3  name = user  msg = a user
			id = 1  name = bing  msg = super man
			id = 2  name = admin  msg = a worker
		 */
	}
}

实现 JAVA中的LIST输入参数

首先创建两个ORACLE数据类型，目的就是为了存储结果集数据(数组等)：
 
  CREATE OR REPLACE TYPE CDW_OBJECT AS OBJECT(
    FISCAL_MONTH                 VARCHAR2(10),
    CUSTOMER_NUMBER              VARCHAR2(10),
    CUSTOMER_NAME                VARCHAR2(50)
    );
  CREATE OR REPLACE TYPE CDW_TABLE AS TABLE OF CDW_OBJECT;

表结果及数据：
 
SQL> DESC CDW_AR_SA_EXPOSURE_T;
Name            Type         Nullable Default Comments 
--------------- ------------ -------- ------- -------- 
FISCAL_MONTH    VARCHAR2(10) Y                         
CUSTOMER_NUMBER VARCHAR2(10) Y                         
CUSTOMER_NAME   VARCHAR2(50) Y                         
SQL> DESC CDW_AR_SA_EXPOSURE_FACT;
Name            Type         Nullable Default Comments 
--------------- ------------ -------- ------- -------- 
FISCAL_MONTH    VARCHAR2(10) Y                         
CUSTOMER_NUMBER VARCHAR2(10) Y                         
CUSTOMER_NAME   VARCHAR2(50) Y                         
SQL> 
 
表结构及测试数据代码：
CREATE TABLE CDW_AR_SA_EXPOSURE_FACT (FISCAL_MONTH VARCHAR2(10),CUSTOMER_NUMBER VARCHAR2(10),CUSTOMER_NAME VARCHAR2(50));
 
 
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200708','1001','XIEFENG');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200705','1002','MANTISXF');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200706','2001','CHENGUOZHENG');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200808','3001','XIAOFANG');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200809','1056','ZIMING');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200810','7701','BOSHI');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200811','1821','BOGE');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200903','2431','FEIZAI');
 
 
 INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200811','3301','BOSHI_HAOREN');
 INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200812','1921','BOGE_HAOREN');
 INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200905','6666','FEIZAI_HAOREN');
 
 COMMIT;  
 
存储过程代码：
 
CREATE OR REPLACE PROCEDURE SP_CARC_UPLOAD_FILE_TEST(OUT_CURSOR OUT SYS_REFCURSOR, V_ARRAY IN CDW_TABLE) AS
    -- DECLARE THE VARIABLE AND CURSOR TYPE
    V_STEP VARCHAR2(100);
    VCOUNTS NUMBER;
  
  
  BEGIN
    V_STEP := 'INSERT RECORDS FROM ARRAY';
    FOR I IN 1..V_ARRAY.COUNT LOOP
    INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES
    (
     V_ARRAY(I).FISCAL_MONTH,
     V_ARRAY(I).CUSTOMER_NUMBER,
     V_ARRAY(I).CUSTOMER_NAME
    );
    
    DBMS_OUTPUT.PUT_LINE('FISCAL_MONTH: '||V_ARRAY(I).FISCAL_MONTH);
    DBMS_OUTPUT.PUT_LINE('CUSTOMER_NUMBER: '||V_ARRAY(I).CUSTOMER_NUMBER);
    DBMS_OUTPUT.PUT_LINE('CUSTOMER_NAME: '||V_ARRAY(I).CUSTOMER_NAME);
        
    IF MOD(I,1000) = 0 THEN
      COMMIT;
    END IF;
    END LOOP;
    COMMIT;
    
    V_STEP := 'GET THE INVALID CUSTOMER COUNT';
      SELECT COUNT(1)
        INTO VCOUNTS
        FROM CDW_AR_SA_EXPOSURE_T STG
       WHERE NOT EXISTS (SELECT 1
                FROM CDW_AR_SA_EXPOSURE_FACT FACT
               WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER
                 AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH);
    
    --FETCH CUR_MATCH_USERID INTO RT_CUR_MATCH_USERID;
    
    IF VCOUNTS = 0 THEN
      V_STEP := 'UPDATE USER_INFORMATION';
          DBMS_OUTPUT.PUT_LINE('UPDATED SQL COUNT: ' || SQL%ROWCOUNT);
          OPEN OUT_CURSOR FOR SELECT DUMMY AS CUSTOMER_NUMBER FROM DUAL WHERE ROWNUM < 1;
          
    ELSE
          V_STEP     := 'RETURN CURSOR WHICH STORED ALL INVILD CUSTOMER_DETAILS';
          OPEN OUT_CURSOR FOR 
          SELECT CUSTOMER_NUMBER
            FROM CDW_AR_SA_EXPOSURE_T STG
           WHERE NOT EXISTS (SELECT 1
                    FROM CDW_AR_SA_EXPOSURE_FACT FACT
                   WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER
                     AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH);
    END IF;
    
    V_STEP := 'DELETE DATA FROM TABLE CDW_AR_SA_EXPOSURE_T';
    /*DELETE FROM CDW.CDW_AR_SA_EXPOSURE_T;
    COMMIT;*/
    
  EXCEPTION
    WHEN OTHERS THEN
      BEGIN
        ROLLBACK;
        --EXECUTE IMMEDIATE 'TRUNCATE TABLE CDW_AR_SA_EXPOSURE_T REUSE STORAGE';
        DBMS_OUTPUT.PUT_LINE('Error at :' || V_STEP);
        DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM);
      END;
    
  END SP_CARC_UPLOAD_FILE_TEST;
  
JAVA 代码:
 
import oracle.sql.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import java.util.*;
public class ArrayTest {
 static public Connection conn;
 static public OracleCallableStatement stmt = null;
 public Connection getConnectionDB()throws SQLException, ClassNotFoundException{
  Class.forName("oracle.jdbc.driver.OracleDriver");
     // B. 创新新数据库连接
     conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oraxf", "scott", "tiger");
     DatabaseMetaData md = conn.getMetaData();
     System.out.println("数据库版本：");
     System.out.println("------------------------------------------------");
     System.out.println(md.getDatabaseProductVersion());
     System.out.println();
     System.out.println("驱动程序名称与版本：");
     System.out.println("------------------------------------------------");
     System.out.print(md.getDriverName() + " " + md.getDriverVersion());
     
     return conn;
     
 }
 
  public static void main(String[] my)throws SQLException, ClassNotFoundException{
   try{
      
   ArrayTest at = new ArrayTest();
   at.getConnectionDB();
   
   ArrayList myArray = new ArrayList();
   String[][] values = {
     {"200811","3301","BOSHI_HAOREN"},
     {"200812","1921","BOGE_HAOREN"},
     {"200905","6666","FEIZAI_HAOREN"}
   };
   myArray.add(values);
      ARRAY array = getArray("CDW_OBJECT","CDW_TABLE",myArray);
      stmt = (OracleCallableStatement)conn.prepareCall("begin SP_CARC_UPLOAD_FILE_TEST(?,?); end;");
      stmt.registerOutParameter(1, OracleTypes.CURSOR,"OUT_CURSOR");
      stmt.setArray(2, array);
      stmt.execute();
      
      ResultSet rs = (ResultSet)stmt.getObject(1);
      
      while(rs.next()){
       String CUSTOMER_NUMBER = rs.getString("CUSTOMER_NUMBER");
       System.out.println("CUSTOMER_NUMBER: "+CUSTOMER_NUMBER);
      }
      
      at.closeConnectionDB();
   }
   catch(Exception e){
    e.printStackTrace();
   }
  }
  
  public static ARRAY getArray(String OracleObj,String OracleTbl, ArrayList alist) throws Exception{
   
   // my code
   ARRAY list = null;
   
   if (alist != null && alist.size() > 0){
    StructDescriptor structdesc = new StructDescriptor(OracleObj,conn);
    //STRUCT[] structs = new STRUCT[alist.size()];
    Object[] result = null;
    //String[][] myArray = new String[alist.size()][3];
    //alist.toArray(myArray);
   /* for(int i=0;i<alist.size();i++){
     String obj[] = (String [])alist.get(i);
     result = new Object[3];
     result[0] = obj[0].toString();
     result[1] = obj[1].toString();
     result[2] = obj[2].toString();
     
     structs[i] = new STRUCT(structdesc,conn,result);
    }
    for(int i=0;i<alist.size();i++){
     ResultSet rs = (ResultSet)alist.get(i);
     for(int j=0;j<3;j++){
      result = new Object[3];
      result[0] = rs.getObject(1).toString();
      result[1] = rs.getObject(2).toString();
      result[2] = rs.getObject(3).toString();
     }
     structs[i] = new STRUCT(structdesc,conn,result);
    }*/
    
    Object[] o1 = new Object[]{"200811","3301","BOSHI_HAOREN"};
    Object[] o2 = new Object[]{"200812","1921","BOGE_HAOREN"};
    Object[] o3 = new Object[]{"200905","6666","FEIZAI_HAOREN"};
    
    STRUCT s1 = new STRUCT(structdesc, conn, o1);
    STRUCT s2 = new STRUCT(structdesc, conn, o2);
    STRUCT s3 = new STRUCT(structdesc, conn, o3);
        
    STRUCT[] structs = {s1,s2,s3};
    /*
    for(int i=0;i<alist.size();i++){
     result = new Object[3];
     result[0] = myArray[i][0].toString();
     result[1] = myArray[i][1].toString();
     result[2] = myArray[i][2].toString();
     
     structs[i] = new STRUCT(structdesc,conn,result);
    }*/
    ArrayDescriptor arraydesc = new ArrayDescriptor(OracleTbl,conn);
    list = new ARRAY(arraydesc,conn,structs);
   }
   return list;
   
  }
  
  public void closeConnectionDB()throws SQLException{
   conn.close();
  }
}
 
输出结果：
-- JAVA 控制台
数据库版本：
------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
驱动程序名称与版本：
------------------------------------------------
Oracle JDBC driver 10.1.0.2.0CUSTOMER_NUMBER: 6666
CUSTOMER_NUMBER: 1921
CUSTOMER_NUMBER: 3301
-- 数据库SQLPLUS
SQL> SELECT * FROM CDW_AR_SA_EXPOSURE_T;
FISCAL_MONTH CUSTOMER_NUMBER CUSTOMER_NAME
------------ --------------- --------------------------------------------------
200811       3301            BOSHI_HAOREN
200812       1921            BOGE_HAOREN
200905       6666            FEIZAI_HAOREN
SQL> 

在跑JAVA代码时可能会出现相关问题：
java程序中使用Oracle的对象：
StructDescriptor structdesc = new StructDescriptor(OracleObj,conn);
出现这个错误：
java.sql.SQLException: Fail to construct descriptor: Invalid arguments
java.sql.SQLException: 无法构造描述符: Invalid arguments
原因为数据库连接dbConn为空，可是这种Exception着实有点让人迷糊。切记：友好的提示信息对问题的定位和解决非常重要。

分享到：