oracle通过存储过程上传list保存功能

一、创建oracle 需要保存的数据类型tyPE和存储过程PRoduce

create TYPE "AL01TYPE"                                                                                                                                                                                                                                  as object(-- 描述 : 档案批量转出-- 作者  : dt-- 时间 : 2021-05-10-- 版本 ：dev-1.0.1   aac003       NVArchAR2(100),   aac002       NVARCHAR2(50),   aat001       NVARCHAR2(50),   aat002       NVARCHAR2(50),   aat013       NVARCHAR2(20),   aae011       NVARCHAR2(20),   aae036       NVARCHAR2(20),   aah002       NVARCHAR2(100) );
     create type AL01TYPELIST as table of AL01TYPE;
     -- auto-generated definITioncreate PROCEDURE SP_HFSZHDA_DOUPLOADAL01(LIST   IN   AL01TYPELIST,                                       po_message OUT VARCHAR) IS                                       --描述：档案转出excel上传                                       --作者：dt                                       --时间：2021-05-10                                       --版本：dev-1.0.1  v_object    AL01TYPE;
      le_error EXCEPTION;
      P_renum number(20);
       v_aah002  VARCHAR(100);
      ls_count number;
      ls_aaf025 VARCHAR(50);
     BEgin  P_renum  := 0;
     --初始化   FOR I IN 1 ..  LIST.count LOOP    P_renum := 1 + P_renum;
           v_object := LIST(I);
             select   replace(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_object.aah002)),unistr('\0000'))  into  v_aah002 From   dual;
           begin        Select count(0) into ls_count from az03 where aat001 = v_object.aat001 AND aat012 = '1';
            if ls_count=0 then           po_message := '号：'||v_object.aat001||' 状态异常请核对后再上传！';
               RaiSE le_error;
               end if;
              Select count(0) into ls_count From AL01 where aaj022='1' and aat001=v_object.aat001 and aah002=v_aah002;
             if ls_count =0 then        -- 开始插入信息      select  SQ_AAF025.nextval into ls_aaf025 from dual;
           insert into AL01(      aaf025,      aat012,      aat001,      aac003,      aac002,      aat002,      aat013,      aaj022,      aaj026,      aae011,      aae036,      aah002)values(                 ls_aaf025,                 '1',                 v_object.aat001,                 v_object.aac003 ,                 v_object.aac002 ,                 v_object.aat002 ,                 v_object.aat013,                 '1',                 'excel上传数据',                 v_object.aae011,                 v_object.aae036,                 v_aah002            );
                            end if;
         IF P_renum >
    1000 THEN            commit;
                P_renum:=0;
            END IF;
          po_message :='ok';
    EXCEPTION                   WHEN le_error THEN                   NULL;
                       WHEN OTHERS THEN                   ROLLBACK;
                       po_message := '上传失败' || SQLCODE || SQLERRM;
            end;
        END LOOp;
      COMMIT;
     END SP_HFSZHDA_DOUPLOADAL01;
    

二、通过过程上传list

package com.COMinfo.eleCFile.utils;
     import oracle.jdbc.OracleConnection;
    import oracle.sql.ARRAY;
    import oracle.sql.ArrayDescriptor;
    import oracle.sql.STRUCT;
    import oracle.sql.StructDescriptor;
    import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;
     import java.sql.Connection;
    import java.util.List;
 /** * 描述 * * @Auther: dt * @Date: 2021/5/10 0027 09:00 */public class OracleUtil {
        /**     * 根据数据库中你的type将List组装成Array     * @param con     * @param OracleObj     * @param Oraclelist     * @param objlist     * @return     * @throws Exception     */    public static ARRAY getArray(Connection con, String OracleObj, String Oraclelist, ListObject[]>
 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;
    }
 }
    

 ListObject[]>
     arrList = new ArrayList>
    ();
            //解析数据datamap        for (MapString, String>
 dataMap : dataMaps) {
            //创建保存对象            Object[] objects =new Object[]{
                     dataMap.get("aac003"),                    dataMap.get("aac002"),                    dataMap.get("aat001").trim(),                    dataMap.get("aat002"),                    dataMap.get("aat013"),                    'admin',                    DateUtil.getcurrentTimeStr(),                    'ec-20210510-wcdedgk2091',             }
    ;
                arrList.add(objects);
         }
       //开始调用过程        long startTime=System.currentTimeMillis();
            Connection connection = null;
            CallableStatement sqlres = null;
            String sql = "call SP_HFSZHDA_DOUPLOADAL01(?,?)";
            String msg = "";
        try {
                connection = dataSource.getConnection();
                ARRAY paramArr = OracleUtil.getArray(connection,"AL01TYPE","AL01TYPELIST",arrList);
                 sqlres = connection.prepareCall(sql);
                sqlres.setArray(1, paramArr);
                sqlres.registerOutParameter(2, Types.VARCHAR);
                sqlres.execute();
                msg = sqlres.getString(2);
                 long endTime=System.currentTimeMillis()-startTime;
                System.out.println("上传后获取的返回参数为："+msg+"||耗时："+endTime/1000+"秒");
         }
 catch (SQLException e) {
                e.printStackTrace();
        }
 catch (Exception e) {
                e.printStackTrace();
        }
finally {
            try {
                if (sqlres != null) {
                        sqlres.close();
                }
                if (connection != null) {
                        connection.close();
                }
            }
 catch (SQLException e) {
                    e.printStackTrace();
            }
            if (!"ok".equals(msg)){
                    throw new BusinessException("上传失败！"+msg);
            }
        }
    

到此这篇关于oracle通过存储过程上传list保存功能的文章就介绍到这了,更多相关oracle保存list内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

• oracle 存储过程返回 结果集 table形式的案例
• 解决PL/SQL修改Oracle存储过程编译就卡死的问题
• 关于Oracle存储过程和调度器实现自动对数据库过期数据清除的问题
• Oracle如何批量将表中字段名全转换为大写(利用简单存储过程)
• Spring boot调用Oracle存储过程的两种方式及完整代码
• oracle调试存储过程的过程详解
• Oracle如何使用PL/SQL调试存储过程
• Oracle存储过程案例详解

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！