oracle 存储过程利用数组类型type实现批量插入数据库

ゞ浴缸里的玫瑰 2022-08-14 02:39 186阅读 0赞

Oracle没有数组类型，如果要传递数组类型的参数，必须得自己定义，在oracle中定义数据类型type，代码如下：

CREATE OR REPLACE TYPE ARRAY_BARCODEID is table of varchar2(50);

不考虑数组长度的做法就是用table，如果不用table，那用 VARRAY吧

建完type后，我们再来创建一个package（因为我有输出参数，所以必须要先建立一个包），代码：

CREATE OR REPLACE PACKAGE delivery AS
    
    procedure addRecords_delivery(
      v_orderId IN varchar2,--订单号
      v_barcodeIds in array_barcodeId,--条形码集合
      v_deliveryListNo IN varchar2,--发货单号
      v_boxId IN varchar2,--箱号
      v_boxName IN varchar2,--箱名
      v_s_num out int,--新增成功的记录数
      v_returnMsg out varchar2--返回不符合插入条件的条形码字符串集，条形码之前以逗号分割
      );
    END;

package body代码：

create or replace package body delivery is
    
    procedure addRecords_delivery(
      v_orderId in varchar2,--订单号
      v_barcodeIds in array_barcodeId,--条形码集合
      v_deliveryListNo in varchar2,--发货单号
      v_boxId in varchar2,--箱号
      v_boxName in varchar2,--箱名
      v_s_num out int,
      v_returnMsg out varchar2)--返回不符合插入条件的条形码字符串集，条形码之前以逗号分割)--新增成功的记录数
      is
        v_count int;
        v_id int;
    begin
      v_s_num:=0;
      v_returnMsg:='';
      for i in 1..v_barcodeIds.Count loop
        select count(barcodeId) into v_count from ptparts where barcodeId=v_barcodeIds(i) and orderId=v_orderId;
       if v_count!=0 then
          select listid.nextval into v_id from dual;
          insert into ptdeliverylist(id,deliverylistno,barcodeid,boxid,boxname) values(v_id,v_deliverylistno,v_barcodeIds(i),v_boxid,v_boxname);
          v_s_num := v_s_num + 1;
        else
          v_returnMsg:=v_returnMsg||v_barcodeIds(i)||',';
        end if;
      end loop;
    end addRecords_delivery;
    
    end;

存储过程的主要功能是把数组中满足条件的数据插入到数据库中，不满足条件的以字符串返回。

接下来就是java代码了，我这里用的是jdbc获取的connection，不存在网上所说的获取不到oracle的connection等错误信息

public class createArrayTypeForProduce {
 	public static void main(String[] args){
 
 		 try {
 		 	Class.forName("oracle.jdbc.driver.OracleDriver");
 		 String url = "jdbc:oracle:thin:@10.112.0.222:1521:ORCL";
 		 Connection con = DriverManager.getConnection(url, "wms", "server110");
 		 CallableStatement proc = null;
 		 String sql = "{call delivery.addRecords_delivery(?,?,?,?,?,?,?)}";
 		 proc = con.prepareCall(sql);
 		 Object[] s = new Object[7];
 		 s[0] = "DFG/GSG656126412001-1001202";
 		 s[1] = "DFG/GSG656126412001-1001203";
 		 s[2] = "DFG/GSG656126412001-1001204";
 		 s[3] = "DFG/GSG656126412001-1001205";
 		 s[4] = "333";
 		 s[5] = "44";
 		 s[6] = "555";
 //		 Object[][] s = {
 {"111","111"}};
 //		 s[0][0] = "嘎嘎编号1";
 //		 s[0][1] = "嘎嘎编号2";
 		 ArrayDescriptor desc = ArrayDescriptor.createDescriptor("ARRAY_BARCODEID", con);// 大写，不然会报“无效的名称模式”
 		 ARRAY arr = new ARRAY(desc, con, s);
 		 proc.setString(1, "DFG/GSG656126412001");// 订单号
 		 proc.setObject(2, arr, Types.ARRAY);// 条形码数组
 					proc.setString(3, "20141120153305");// 发货单号
 					proc.setString(4, "箱号");// 箱号
 					proc.setString(5, "箱名");// 箱名
 					proc.registerOutParameter(6, Types.INTEGER);
 					proc.registerOutParameter(7, Types.VARCHAR);
 					proc.execute();
 					int s_num = proc.getInt(6);
 					String msg = proc.getString(7);
 					System.out.println("成功的记录数="+s_num);
 					System.out.println("未成功插入的条形码="+msg);
 					proc.close();
 					con.close();
 		 } catch (Exception e) {
 
 		 e.printStackTrace();
 
 	}
 	}
 }

有两点需要注意的地方：

1.出现错误信息：“无效的名称模式”，原因：数组type名称不正确或者没有大写，声明一下，array type必须每个字母都大写，有疑问的去看看oracle官网吧

2.加一个nls\_charset12.jar包，不然会出现插不进去数据的问题，网上有的说要加上orai18n.jar，但我好像没加也可以正常插入中英文数据，[nls\_charset12.jar包下载][nls_charset12.jar]

推荐一个大神的文章 [ http://blog.csdn.net/hzw2312/article/details/8444462][http_blog.csdn.net_hzw2312_article_details_8444462] 写的超详细

[nls_charset12.jar]: http://download.csdn.net/detail/u011054048/8187971
[http_blog.csdn.net_hzw2312_article_details_8444462]: http://blog.csdn.net/hzw2312/article/details/8444462