自定义暂时表结构导入数据
自定义临时表结构导入数据
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;
-- 摘自就业信息管理 的派遣信息导入