ORACLE存储过程使用数组参数
ORACLE存储过程使用数组参数
分类： Oracle 2009-07-13 20:39 2174人阅读 评论(1) 收藏 举报
http://blog.csdn.net/louie520/archive/2008/11/18/3326892.aspx，这是原文的地址。

环境：Eclipse+Oracle9.0.2+Tomcat5.5

功能：采用存储过程、type组合来实现批量入库，以节省系统开销，提高效率。

    *

      sql脚本+测试代码：
    * 1)create or replace type t_cableLine_point as object
      (
        ID          NUMBER(10),
        CABLELINEID NUMBER(10),
        ROADPOINTID NUMBER(10),
        ORDERNUM    NUMBER(10),
        REMARK      NUMBER(10)
      )
    * 2）CREATE OR REPLACE TYPE ARRAY_cableLine_point AS table OF t_cableLine_point
    * 3)create table RSC_CABLELINE_POINT
      (
        ID          NUMBER(10) not null,
        CABLELINEID NUMBER(10) not null,
        ROADPOINTID NUMBER(10) not null,
        ORDERNUM    NUMBER(10),
        REMARK      NUMBER(10)
      )
    * 4)create or replace procedure batch_cableline_point(i_object in ARRAY_cableLine_point) is
      begin
        insert into RSC_CABLELINE_POINT
          (ID, CABLELINEID, ROADPOINTID, ORDERNUM, REMARK)
          select ID, CABLELINEID, ROADPOINTID, ORDERNUM, REMARK
            from the (select cast(i_object as ARRAY_cableLine_point) from dual);
      end batch_cableline_point;
    * 5)测试代码：
         public class Test {
       
             public static void main(String[] args){
                 try{
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    String url="jdbc:oracle:thin:@192.168.1.2:1521:***";
                    Connection con = DriverManager.getConnection(url, "***","***");
                    PreparedStatement  pstmt = null;
                    String sql = "{call batch_cableline_point(?)}";
                  
                    pstmt = con.prepareCall(sql);
                    Object[][]  object1=new Object[10][5];