oracle中自定义type、以及java中传递list到过程中的例子

在java开发过程中有时候为了处理数据的速度更快，会把要处理的数据组装成list，把list作为过程的一个参数，在过程中批量处理，下面就以一个例子做简单的阐述，以此谨记。

--药品目录智能审核

--说明：药品目录上传首先是保存在DB1建的临时表中ka19_temp，最终经过调用webservice接口调用DB2的过程PKG_DIRECTROYBUSINESS.WSBS_VALIDATE_DIRECTROY

--创建type //这里的type可以等同于创建一个table

CREATE OR REPLACE TYPE "KA19MX" AS OBJECT(
        AKB020 VARCHAR2(20),--定点医疗机构编号
        SKE534 VARCHAR2(100),--定点医疗机构药品编号
        SKE535 VARCHAR2(100),--定点机构药品名
        AKE001 VARCHAR2(100),--社保三大目录统一编码
        AKC225 NUMBER(10,4),--单价
        AAE011 VARCHAR2(20),--经办人
        AKA070 VARCHAR2(100),--剂型
        AKA077 VARCHAR2(100),--规格
        AKA083 VARCHAR2(100), --药厂名称
        AKA067 VARCHAR2(100) --药品剂量单位
)

CREATE OR REPLACE TYPE "KA19MXLIST" AS TABLE OF KA19MX  //这里相当于创建上面的一个记录，即表的记录

--上面type创建好之后在java层调用过程就可以对应使用--以下是配置webservice接口例子

    /**
     * 验证要保存的药品目录信息
     * @param akb020 定点医疗机构编号
     * @param aae011 经办人姓名
     * @param jdbctemplate_2 网办数据源
     * @param jdbctemplate_1 核三业务库数据源
     * @return
     * @author xhw
     * @date 2018-01-03
     * 备注：由于医保中心并没有给明确的需求 现在只是按照最早的上传药品目录信息进行开发，后期需求确定后在修改程序
     */
    public String validateMuLu(String akb020,String aae011){
        Connection conn=null;
        CallableStatement cs=null;
        String result="";
        String msg="";
        try {
            //根据传递过来的定点医疗机构编号到网办ka19_temp中查询已经经过初步验证保存成功的数据
            String sql="select  * from ka19_temp where akb020='"+akb020+"' and iserror='001'";
            List<Ka19DTO> list=jdbctemplate_2.query(sql.toString(), new BeanPropertyRowMapper(Ka19DTO.class));
            if(list.size()<=0){
                return ReaderSoapXmlOut.BuildSoapXMlError("3000", "没有正确数据！");
            }
            List<Object[]> ka19mxlist = new ArrayList<Object[]>();
            //组装以数组形式的list
            for(int i=0;i<list.size();i++){
                Object[] objs = new Object[10];
                objs[0] = list.get(i).getAkb020();//定点医疗机构编号
                objs[1] = list.get(i).getSke534();//定点医疗机构药品编号
                objs[2] = list.get(i).getSke535();//药品商品名
                objs[3] = list.get(i).getAke001();//社保三大目录统一编码
                objs[4] = list.get(i).getAkc225();//单价
                objs[5] = list.get(i).getAae011();//经办人
                objs[6] = list.get(i).getAka070();//剂型
                objs[7] = list.get(i).getAka077();//规格
                objs[8] = list.get(i).getAka083();//药厂名称/生产厂家
                objs[9] = list.get(i).getAka067();//药品剂量单位
                ka19mxlist.add(objs);
            }
            //调用核三自动审核过程
            
            String sql2="call PKG_DIRECTROYBUSINESS.WSBS_VALIDATE_DIRECTROY(?,?)";
            conn=jdbctemplate_1.getDataSource().getConnection();
            cs=conn.prepareCall(sql2);
            //根据数据库中自定义的type把list组装成array
            ARRAY array = OraUtil.getArray(conn, "KA19MX", "KA19MXLIST", ka19mxlist);//KA19MX、KA19MXLIST即上面在oracle中创建的type
            //设置过程入参类型
            cs.setArray(1, array);
            //设置过程出参类型
            cs.registerOutParameter(2, Types.VARCHAR);
            //执行
            cs.execute();
            msg=cs.getString(2);
            if(!msg.equals("OK")){
                return ReaderSoapXmlOut.BuildSoapXMlError("3000", msg);
            }
            //保存成功后 删除临时表中所有该定点医疗机构的数据  --测试暂时不删除 正式上线的时候要全部删掉
            //String sql3="delete from ka19_temp where akb020='"+akb020+"'";
            //jdbctemplate_2.execute(sql3);
            return ReaderSoapXmlOut.BuildSoapSuccessXMl();
        } catch (Exception e) {
            e.printStackTrace();
            String errMsg=e.getMessage();
            errMsg=errMsg.replaceAll(""", "");
            errMsg=errMsg.replaceAll(";", "");
            result=ReaderSoapXmlOut.BuildSoapXMlError("3000", errMsg);
        }finally{
            try {
                if (cs != null)
                    cs.close();
                if(null != conn)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                String errMsg = e.getMessage();
                errMsg = errMsg.replaceAll(""", "");
                errMsg = errMsg.replaceAll(";", "");
                return ReaderSoapXmlOut.BuildSoapXMlError("3000", errMsg);
            }
    }
        return result;
    }

    /**
     * 根据数据库中你的type将List组装成Array
     * @param con
     * @param OracleObj
     * @param Oraclelist
     * @param objlist
     * @return
     * @throws Exception
     */
    public static ARRAY getArray(Connection con, String OracleObj,String Oraclelist, List<Object[]> objlist) throws Exception {    
        ARRAY array=null;
          C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();  
          OracleConnection connection = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(con);  

        
        if (objlist != null && objlist.size() > 0) {
            StructDescriptor structdesc = new StructDescriptor(OracleObj, connection);
            STRUCT[] structs = new STRUCT[objlist.size()];
            for (int i = 0; i < objlist.size(); i++) {
                Object[] result= (Object[]) objlist.get(i);
                structs[i] = new STRUCT(structdesc, connection, result);
            }
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,connection);
            array = new ARRAY(desc, connection, structs);
        }
        
        return array;
    }

--上面调用过程 list作为过程的一个参数 在过程中遍历的例子

CREATE OR REPLACE PACKAGE BODY PKG_DIRECTROYBUSINESS IS
    /************************************************************************************************
     ||过程名称：WSBS_VALIDATE_DIRECTROY
     ||功能描述：网办系统药品目录申报在保存的时候把经过初步验证的信息保存到核三ka19中来，
     并且调用自动审核过程，报审核结果同步更新ka19中
     ||参数：上传目录组成的集合 ka19mxlist
     ||作者：XuHW
     ||日期：2018-01-04 10:03:21
  **************************************************************************************************/
  PROCEDURE WSBS_VALIDATE_DIRECTROY(
      ka19mxlist IN KA19MXLIST,--申报目录list
      po_msg     OUT VARCHAR2  --错误信息 OK成功 其余全是失败
  )AS
   ROW_KA19 AHSICP3.KA19%ROWTYPE;--目录明细
   ROW_KA20 AHSICP3.KA20%ROWTYPE;--目录库信息
   PC0000   VARCHAR2(20);--序列
   LS_CODE  VARCHAR2(20);--信息编码
   LS_ERRMSG VARCHAR2(500);--自定义错误信息
   LE_ERROR  EXCEPTION;--异常

   BEGIN
   --初始化
    LS_CODE:='1';
    LS_ERRMSG:='';
    po_msg:='OK';
     --处理提交的目录list
     for i IN 1..KA19MXLIST.count loop
       --根据社保统一编码查询到ka20的明细
       SELECT * INTO ROW_KA20 FROM AHSICP3.KA20 WHERE SKC033='001' AND  AKE001=KA19MXLIST(I).AKE001;

       row_ka19.oae001:=ahsiyb.seq_ka19_oae001.nextval;--唯一标识
       row_ka19.akb020:=KA19MXLIST(i).akb020;--定点医疗机构编号
       row_ka19.ske534:=KA19MXLIST(i).ske534;--定点医疗机构药品编码
       row_ka19.ske535:=KA19MXLIST(i).ske535;--药品商品名
       row_ka19.aka067:=KA19MXLIST(i).aka067;--药品剂量单位
       row_ka19.aka073:='';--用法
       row_ka19.aka071:='';--每次用量
       row_ka19.aka072:='';--使用频次
       row_ka19.aka070:=KA19MXLIST(i).aka070;--剂型
       row_ka19.aka076:='';--单位
       row_ka19.aka077:=KA19MXLIST(i).aka077;--规格
       row_ka19.aka078:=NULL;--限定天数
       row_ka19.aka081:=ROW_KA20.aka020;--拼音助记码
       row_ka19.aka082:=ROW_KA20.aka021;--五笔助记码
       row_ka19.aka083:='';--药厂名称
       row_ka19.aka084:='';--自制药品标志
       row_ka19.aka064:='';--处方药标志
       row_ka19.ake001:=KA19MXLIST(i).ake001;--社保三大目录统一编码
       row_ka19.akc175:='000';--审批标志 未审核
       row_ka19.aae011:=KA19MXLIST(i).aae011;--经办人
       row_ka19.aae036:=sysdate;--经办日期
       row_ka19.aae013:='数据来源网办申报';--备注
       row_ka19.oae300:=NULL;
       row_ka19.OAE301:=NULL;
       row_ka19.AKA085:='';--国药准字
       row_ka19.aka063:=ROW_KA20.aka063;--收费类别
       row_ka19.akc225:=KA19MXLIST(i).akc225;--价格
       row_ka19.skd053:='';--批准文号
       
       --把目录明细添加到ka19中
       INSERT INTO AHSICP3.KA19 VALUES row_ka19;
       SELECT ahsiyb.seq_kbadls_pc0000.nextval INTO PC0000 FROM dual;
       --调用药品目录自动审核过程
       ahsiyb.SP_CATALOG_AUTOREVIEW(row_ka19.oae001,'20170901','99991231',PC0000,LS_CODE,LS_ERRMSG);
       IF LS_CODE=2 THEN
        RAISE LE_ERROR;
       END IF;
     end loop;
     COMMIT;
     EXCEPTION
       WHEN LE_ERROR THEN
         po_msg:=LS_ERRMSG;
         ROLLBACK;
       WHEN OTHERS THEN
         po_msg:='调用自动审核过程出错,请联系系统管理员：'||SUBSTR(SQLERRM,1,400);
         ROLLBACK;
  END WSBS_VALIDATE_DIRECTROY;
END PKG_DIRECTROYBUSINESS;