自定义临时表结构导入数据

1、先自定义表结构；

2、循环读取excel表的每行数据存到一个字符串数组中；

3、把每个字符串数组放到自定义表；

4、存储过程直接操作该自定义表即可。

这样就不需要每次都在数据库中建立一张临时表。

注意：在存储过程中使用自定义表时写法为：table(p_table)

 

    private void paseExcelPqxx(UploadedFile excelFile) throws Exception {
        try {
            CallableStatement stmt = null;
            Connection conn = null;
            JyglAMImpl jyglAMImpl = (JyglAMImpl) ADFUtils.getApplicationModuleForDataControl("JyglAMDataControl");
            
            conn = jyglAMImpl.getDBTransaction().createStatement(1).getConnection();
            StructDescriptor structdesc = new StructDescriptor("IMPORT_JYXX_TYPE", conn);
            
            try {
                InputStream inp = excelFile.getInputStream();
                Workbook wb = WorkbookFactory.create(inp);
                Sheet sheet = wb.getSheetAt(0); //读取Excel中第一个sheet的数据
                Row dataRow = null;
                int rowNum = 1;
                int lastNum = sheet.getLastRowNum();
                STRUCT[] structs = new STRUCT[lastNum - rowNum+1];
                //执行操作
                for(int n=1;n<=lastNum;n++){
                    dataRow = sheet.getRow(n);
                    String[] result = new String[22]; //定义一个字符串数组，该数组的顺序必须和'IMPORT_JYXX_TYPE'这里面的顺序相同
                    result[0] = getCellStrValue(dataRow.getCell(9));
                    result[1] = getCellStrValue(dataRow.getCell(11));
                    result[2] = getCellStrValue(dataRow.getCell(23));
                    result[3] = getCellStrValue(dataRow.getCell(25));
                    result[4] = getCellStrValue(dataRow.getCell(28));
                    result[5] = getCellStrValue(dataRow.getCell(29));
                    result[6] = getCellStrValue(dataRow.getCell(31));
                    result[7] = getCellStrValue(dataRow.getCell(34));
                    result[8] = getCellStrValue(dataRow.getCell(35));
                    result[9] = getCellStrValue(dataRow.getCell(38));
                    result[10] = getCellStrValue(dataRow.getCell(39));
                    result[11] = getCellStrValue(dataRow.getCell(40));
                    result[12] = getCellStrValue(dataRow.getCell(42));
                    result[13] = getCellStrValue(dataRow.getCell(43));
                    result[14] = getCellStrValue(dataRow.getCell(44));
                    result[15] = getCellStrValue(dataRow.getCell(51));
                    result[16] = getCellStrValue(dataRow.getCell(52));
                    result[17] = getCellStrValue(dataRow.getCell(53));
                    result[18] = getCellStrValue(dataRow.getCell(54));
                    result[19] = getCellStrValue(dataRow.getCell(55));
                    result[20] = getCellStrValue(dataRow.getCell(71));
                    result[21] = getCellStrValue(dataRow.getCell(72));
                    structs[n-1] = new STRUCT(structdesc, conn, result);
                }
                
                executeProJyxxPqdr(jyglAMImpl, structs,conn);

                inp.close();
            } finally {
                if (stmt != null) {
                    stmt.close();
                }

            }
        } catch (Exception e) {
            log.severe(e);
            e.printStackTrace();
        }
    }



    public void executeProJyxxPqdr(JyglAMImpl am, STRUCT[] structs,Connection conn) throws SQLException {
        String stmt = "BEGIN JYGL_DR_PKG.MAIN_JYGL(?, ?, ?); END;";
       // String stmt = "BEGIN jygl_syncpqxx_temp(?,?, ?); END;";
        
        java.sql.CallableStatement st = null;
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("JYXX_TABLE_TYPE", conn);
        ARRAY array = new ARRAY(desc, conn, structs);
        try {
            st = am.getDBTransaction().createCallableStatement(stmt, 0);
            st.setArray(1, array);
            st.registerOutParameter(2, Types.VARCHAR); //第一个返回值
            st.registerOutParameter(3, Types.VARCHAR); //第二个返回值
            st.execute(); 
            //返回结果的处理
//            if ("E".equals(st.getString(2))) {
//                inputValue.setValue(st.getString(1));
//            } else {
//                inputValue.setValue(st.getString(1) + " 共导入" + st.getBigDecimal(3));
//            }
            inputValue.setValue(st.getString(2));

        } catch (java.sql.SQLException s) {
            s.printStackTrace();
            throw new oracle.jbo.JboException(s);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (java.sql.SQLException s2) {
                throw new oracle.jbo.JboException(s2);
            }
        }
    }

 

 

1、自定义表结构

create or replace type IMPORT_JYXX_TYPE as object
(
  dah      VARCHAR2(50),
  xh       VARCHAR2(50),
  pqxz     VARCHAR2(50),
  dwlxdm   VARCHAR2(50),
  zgdwdm   VARCHAR2(50),
  jtjsdw   VARCHAR2(150),
  bddzdm   VARCHAR2(50),
  dwsshydm VARCHAR2(50),
  dwdqdm   VARCHAR2(50),
  syytdm   VARCHAR2(50),
  dwlxr    VARCHAR2(50),
  dwlxdh   VARCHAR2(50),
  zylxdm   VARCHAR2(50),
  xc       VARCHAR2(50),
  bdzh     VARCHAR2(100),
  jtlxdz   VARCHAR2(200),
  jtlxdh   VARCHAR2(50),
  jtyzbm   VARCHAR2(150),
  yddh     VARCHAR2(50),
  dzyj     VARCHAR2(50),
  byqx     VARCHAR2(50),
  dwzzjgdm VARCHAR2(50)
)

 

 2、自定义表

CREATE OR REPLACE TYPE JYXX_TABLE_TYPE AS TABLE OF IMPORT_JYXX_TYPE

 

3、存储过程

CREATE OR REPLACE PACKAGE BODY jygl_dr_pkg AS
  PROCEDURE main_jygl(p_table  in JYXX_TABLE_TYPE,
                      res_str  OUT VARCHAR2,
                      res_code OUT VARCHAR2) AS
  
    /*同步更新就业协议登记、派遣信息和报到证
    p_table：自定义表结构，用于存放导入excel表的数据
    res_str：操作信息
    res_code：返回操作代码 'S'成功,'E'失败
    */
  
  BEGIN
  
    --就业协议登记
    merge into jygl_jyxydj a
    using (SELECT xs.xh,
                  t.byqx,
                  t.dwzzjgdm,
                  t.dwsshydm,
                  t.dwlxr,
                  t.dwlxdh,
                  t.syytdm,
                  t.dwlxdm,
                  t.dwdqdm,
                  t.zylxdm,
                  t.xc,
                  t.jtjsdw,
                  t.bddzdm,
                  t.jtyzbm,
                  t.jtlxdh
             FROM table(p_table) t, xsxxgl_xsjbxx xs
            WHERE t.xh = xs.jwxh
              and t.xh IS NOT NULL) b
    on (a.xh = b.xh)
    when matched then
      update
         set a.byqx     = b.byqx,
             a.dwzzjgdm = b.dwzzjgdm,
             a.dwsshy   = b.dwsshydm,
             a.dwlxr    = b.dwlxr,
             a.dwlxdh   = b.dwlxdh,
             a.syyt     = b.syytdm,
             a.dwlx     = b.dwlxdm,
             a.dwssdq   = b.dwdqdm,
             a.zylx     = b.zylxdm,
             a.xc       = b.xc,
             a.jsdadwmc = b.jtjsdw,
             a.xqdz     = b.bddzdm,
             a.yzbm     = b.jtyzbm,
             a.lxdh     = b.jtlxdh --更新
      
    
    when not matched then
      insert
        (a.jyxydjid,
         a.xh,
         a.byqx,
         a.dwzzjgdm,
         a.sfbh,
         a.sfzydk,
         a.dwsshy,
         a.dwlxr,
         a.dwlxdh,
         a.syyt,
         a.dwlx,
         a.dwssdq,
         a.zylx,
         a.xc,
         a.sfjykn,
         a.jsdadwmc,
         a.xqdz,
         a.yzbm,
         a.lxdh,
         a.sfqbshtg,
         a.sfjrshlc,
         a.shzt,
         a.tjsj,
         a.sftj)
      values
        (xl_jygl_jyxydj.nextval,
         b.xh,
         b.byqx,
         b.dwzzjgdm,
         0,
         0,
         b.dwsshydm,
         b.dwlxr,
         b.dwlxdh,
         b.syytdm,
         b.dwlxdm,
         b.dwdqdm,
         b.zylxdm,
         b.xc,
         '0',
         b.jtjsdw,
         b.bddzdm,
         b.jtyzbm,
         b.jtlxdh,
         1,
         1,
         '导入通过',
         to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
         1); --插入
  
    --派遣信息
    merge into jygl_pqxx pq
    using (SELECT xs.xh, t.pqxz, t.zgdwdm, t.jtjsdw, t.bddzdm
             FROM table(p_table) t, xsxxgl_xsjbxx xs
            WHERE t.xh = xs.jwxh
              and t.xh IS NOT NULL) pqt
    on (pq.xh = pqt.xh)
    when matched then
      update
         set pq.zgdw   = pqt.zgdwdm,
             pq.jtpqdw = pqt.jtjsdw,
             pq.bddz   = pqt.bddzdm --更新
      
    
    when not matched then
      insert
        (pq.pqxxid,
         pq.xh,
         pq.zgdw,
         pq.jtpqdw,
         pq.bddz,
         pq.sfqbshtg,
         pq.sfjrshlc,
         pq.tjsj,
         pq.shzt,
         pq.sfbh,
         pq.pqxz)
      values
        (xl_jygl_pqxx.nextval,
         pqt.xh,
         pqt.zgdwdm,
         pqt.jtjsdw,
         pqt.bddzdm,
         1,
         1,
         to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),
         '导入通过',
         0,
         case pqt.pqxz
           when '申请暂缓就业' then
            '2'
           else
            '1'
         end); --插入
  
    --就业报到证
    merge into jygl_bdz bd
    using (SELECT xs.xh, t.bdzh
             FROM table(p_table) t, xsxxgl_xsjbxx xs
            WHERE t.xh = xs.jwxh
              and t.xh IS NOT NULL) bdt
    on (bd.xh = bdt.xh)
    when matched then
      update set bd.bdzh = bdt.bdzh --更新
      
    
    when not matched then
      insert
        (bd.bdzrkid, bd.xh, bd.bdzzt, bd.bdzh)
      values
        (xl_jygl_bdzrk.nextval, bdt.xh, 0, bdt.bdzh); --插入
  
    res_str  := '同步成功';
    res_code := 'S';
    commit;
  
  EXCEPTION
    WHEN OTHERS THEN
      res_str := dbms_utility.format_error_backtrace() || '---' || SQLERRM;
    
      /*res_str  := '执行失败';*/
      res_code := 'E';
      ROLLBACK;
  END;
END jygl_dr_pkg;

 

-- 摘自就业信息管理 的派遣信息导入