数据导入文档

1、档案数据入库操作一、文书数据入库删除原有表格内容选中表T_WS_DOCINDEX内全部内容进行删除导入mdb索引也可能是excle文件1）翻开PLSQL，工具ODBC导入器：2）ODBC导入器详细设置：“来自ODBC的数据”：选择“MSAccessDatabase；”输入数据库登岸信息；点击connect，选择需要导入的mdb文件；选择详细的表。以下列图所示填写选择并导入正确的索引表“到Oracle的数据”：输入用户与导入的数据库表名；指定字段对应关系以及字段种类；点击import导入。以下列图所示填写选择。字段填补（1）填补前：判断能否存在同样的档案：selectcount(*)FROMTA

2、_ARCHIVEwherearchivetype=3andarchivenoin(SELECTdistinctbatchidFROMT_WS_DOCINDEX);查问不一样的batchid共43条，所以导入后将增添文书档案43条；SELECTcount(distinctbatchid)FROMT_WS_DOCINDEX因pdf命名与mdb表中的docname存在进出，履行以下语句使其保持一致select*fromT_WS_DOCINDEXwswherelength(docname)3;updatet_ws_docindexsetdocname=lpad(docname,3,0)wherelen

3、gth(docname)3暂时表办理selectws.*fromt_ws_docindexwsorderbyws.batchid,ws.docname（2）字段填补：更新分类号为“A”updatet_ws_docindexwssetws.flh=A;updatet_ws_docindexwssetws.bmh=substr(ws.batchid,0,2);更新到目录号updatet_ws_docindexwssetws.mlh=substr(ws.batchid,instr(ws.batchid,.,1,1)+1,instr(ws.batchid,-,1,2)-instr(ws.batchid

4、,.,1,1)-1)selectws.*fromt_ws_docindexwswherews.mlhisnull-updatearchivetypeupdatet_ws_docindexwssetws.archivetype=3;-updateclassidupdatet_ws_docindexwscls.classcode=ws.bmh);setws.classid=(selectcls.classidfromta_classdefclswhere-updatecatalognoupdatet_ws_docindexwssetws.catalogno=ws.mlh;-updateajhupd

5、atet_ws_docindexwssetws.ajh=substr(ws.batchid,instr(ws.batchid,-,1,2)+1);select*fromt_ws_docindexwswherews.ajhisnull-updatendselectsubstr(ws.mlh,instr(ws.mlh,-,1,1)+1)fromt_ws_docindexws;updatet_ws_docindexwssetws.nd=substr(ws.mlh,instr(ws.mlh,-,1,1)+1);查找不重复的编目号、目录号-导入到ta_catalognoinsertintota_cata

6、lognocno(cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status)(selectdistinctws.mlh,cdf.classid,1,1,1fromt_ws_docindexwsjointa_classdefcdfoncdf.classcode=ws.bmh);档案入库（1）工具应用：PDF源文件路径选择目标文件夹和日记文件夹自行成立Oracle数据库有关数据填写选择相应的索引数据表名点击导入按钮进行数据导入,详细参数以下列图所示：3）将目标文件夹中生成的文件复制到F:SouthArchiveFTPArch

7、iveFTPme_official文件夹中。二、其余数据入库其余数据入库的方式流程与文书数据一致，可是在删除的表格也要选择与之相般配的表格内容，使用ODBC导入器时应注意将要导入的索引文件的格式并选择相应的“用户/系统”。下边是各数据字段填补的脚本文件。督查检查batchid与目录名能否对应判断能否已经有同样batchid的档案SELECTcount(*)FROMTA_ARCHIVEwherearchivenoin(selectbatchidfromt_jc_docindex)如为0时持续往下操作；办理t_jc_docindex履行脚本“索引数据办理_t_jc_docindex.sql”sel

8、ectcount(*)fromt_jc_docindexjcorderbyjc.ajhdescselectjc.*fromt_jc_docindexjcorderbyjc.ajhdescselectjc.*fromt_jc_docindexjcorderbyjc.batchid,jc.docname更新到分类号FLH字段，为“F”updatet_jc_docindexjcsetjc.flh=F;-更新到编目号BMH字段updatet_jc_docindexjcsetjc.bmh=F;更新到目录号MLH字段BATCHID的年度updatet_jc_docindexjcsetjc.mlh=subs

9、tr(jc.batchid,instr(jc.batchid,-,1,1)+1,instr(jc.batchid,-,1,2)-instr(jc.batchid,-,1,1)-1)-设置索引表的ARCHIVETYPECLASSIDCATALOGNOupdatet_jc_docindexjcsetjc.archivetype=7;updatet_jc_docindexjcsetjc.classid=31;updatet_jc_docindexjcsetjc.catalogno=jc.mlh;三个的值-更新模卷号，取batchid第二个-后的部分updatet_jc_docindexjcsetjc

10、.ajh=substr(jc.batchid,instr(jc.batchid,-,1,2)+1)能否存在空的模卷号select*fromt_jc_docindexjcwherejc.ajhisnull更新档案件数，同一个文档包中的件数selectjc.batchid,count(*)fromt_jc_docindexjcgroupbyjc.batchid-t_batchinfo作业单位供给的，假如没有供给就没有，页数就不可以计算了updatet_jc_docindexjcsetjc.jianshu=(selectb.docnumfromt_batchinfobwhereb.batchid=j

11、c.batchid);updatet_jc_docindexjcsetjc.zys=(selectb.papernumfromt_batchinfobwhereb.batchid=jc.batchid)select*fromt_jc_docindexjcwherejc.jianshuisnull查找不重复的目录号selectdistinctjc.mlhfromt_jc_docindexjcorderbyjc.mlh;-导入到ta_catalognoinsertintota_catalognocno(cno.catalogno,cno.classid,cno.curboxno,cno.recor

12、design,cno.status)(selectdistinctjc.mlh,cdf.classid,1,1,1fromt_jc_docindexjcjointa_classdefcdfoncdf.classcode=jc.bmh);交易出让、划拨出让划拨数据selectcount(*)fromt_jyclht_docindexjcorderbyjc.ajhdesc;selectjc.*fromt_jyclht_docindexjcorderbyjc.batchid,jc.docname;更新到分类号FLH字段，为“JE”updatet_jyclht_docindexjcsetjc.flh=

13、JE;更新到编目号BMH字段(出让JE1，划拨JE2)updatet_jyclht_docindexjcsetjc.bmh=JE1wheresubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,1)=1;updatet_jyclht_docindexjcsetjc.bmh=JE2wheresubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,1)=2;更新到目录号MLH字段BATCHID的年度updatet_jyclht_docindexjcsetjc.mlh=substr(jc.batchid,instr(jc.batchi

14、d,-,1,1)+1,instr(jc.batchid,-,1,2)-instr(jc.batchid,-,1,1)-1)-设置索引表的ARCHIVETYPECLASSIDCATALOGNO三个的值updatet_jyclht_docindexjcsetjc.archivetype=5;updatet_jyclht_docindexjcsetjc.classid=19WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,1)=1;updatet_jyclht_docindexjcsetjc.classid=20WHEREsubstr(jc.batch

15、id,instr(jc.batchid,.,1,2)+1,1)=2;updatet_jyclht_docindexjcsetjc.catalogno=jc.mlh;-更新模卷号，取batchid第二个-后的部分updatet_jyclht_docindexjcsetjc.ajh=substr(jc.batchid,instr(jc.batchid,-,1,2)+1)-能否存在空的模卷号select*fromt_jyclht_docindexjcwherejc.ajhisnull-更新档案件数，同一个文档包中的件数selectjc.batchid,count(*)fromt_jyclht_doc

16、indexjcgroupbyjc.batchid-t_batchinfo作业单位供给的，假如没有供给就没有，页数就不可以计算了updatet_jyclht_docindexjcsetjc.jianshu=(selectb.docnumfromt_batchinfobwhereb.batchid=jc.batchid);updatet_jyclht_docindexjcsetjc.zys=(selectb.papernumfromt_batchinfobwhereb.batchid=jc.batchid)select*fromt_jyclht_docindexjcwherejc.jianshui

17、snull查找不重复的目录号selectdistinctjc.mlhfromt_jyclht_docindexjcorderbyjc.mlh;-导入到ta_catalognoinsertintota_catalognocno(cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status)(selectdistinctjc.mlh,cdf.classid,1,1,1fromt_jyclht_docindexjcjointa_classdefcdfoncdf.classcode=jc.bmh);检查档案号能否重复SELECTco

18、unt(*)FROMTA_ARCHIVEwherearchivenoin(selectJE|substr(jc.batchid,instr(jc.batchid,.,1,2)+1,2)|jc.mlh|-|jc.ajhfromt_jyclht_docindexjc)交易（转让、转拍、挂牌、转挂、矿出、其余、转让旧、转挂旧）种类分类表记编目表记编目原数据量转让521JE32450转拍522JE410挂牌523JE5198转挂524JE6249矿出525JE711其余526JE80转让旧527JE918转挂旧530JE108暂时表数据办理selectcount(*)fromT_JYZRHT_DOCI

19、NDEXjcorderbyjc.ajhdescselectjc.*fromT_JYZRHT_DOCINDEXjcorderbyjc.ajhdescselectjc.*fromT_JYZRHT_DOCINDEXjcorderbyjc.batchid,jc.docname更新到分类号FLH字段，为“JE”updateT_JYZRHT_DOCINDEXjcsetjc.flh=JE;更新到编目号BMH字段(转让JE3，转拍JE4，挂牌JE5，转挂JE5，矿出JE7，其余JE8，转让旧JE9，转挂旧JE10)updateT_JYZRHT_DOCINDEXjcsetjc.bmh=JE|substr(jc.

20、batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1);更新到目录号MLH字段BATCHID的年度updateT_JYZRHT_DOCINDEXjcsetjc.mlh=substr(jc.batchid,instr(jc.batchid,-,1,1)+1,instr(jc.batchid,-,1,2)-instr(jc.batchid,-,1,1)-1)-设置索引表的ARCHIVETYPECLASSIDCATALOGNOupdateT_JYZRHT_DOCINDEXjcsetjc.

21、archivetype=5;三个的值updateT_JYZRHT_DOCINDEXjcsetjc.classid=21WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)=3;updateT_JYZRHT_DOCINDEXjcsetjc.classid=22WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1

22、)=4;updateT_JYZRHT_DOCINDEXjcsetjc.classid=23WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)=5;updateT_JYZRHT_DOCINDEXjcsetjc.classid=24WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)=6;updateT_JY

23、ZRHT_DOCINDEXjcsetjc.classid=25WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)=7;updateT_JYZRHT_DOCINDEXjcsetjc.classid=26WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)=8;updateT_JYZRHT_DOCINDEXj

24、csetjc.classid=27WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)=9;updateT_JYZRHT_DOCINDEXjcsetjc.classid=30WHEREsubstr(jc.batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)=10;updateT_JYZRHT_DOCINDEXjcsetjc.catalo

25、gno=jc.mlh;更新模卷号，取batchid第二个-后的部分updateT_JYZRHT_DOCINDEXjcsetjc.ajh=substr(jc.batchid,instr(jc.batchid,-,1,2)+1)能否存在空的模卷号select*fromT_JYZRHT_DOCINDEXjcwherejc.ajhisnull更新档案件数，同一个文档包中的件数selectjc.batchid,count(*)fromT_JYZRHT_DOCINDEXjcgroupbyjc.batchid-t_batchinfo作业单位供给的，假如没有供给就没有，页数就不可以计算了updateT_JYZ

26、RHT_DOCINDEXjcsetjc.jianshu=(selectb.docnumfromt_batchinfobwhereb.batchid=jc.batchid);updateT_JYZRHT_DOCINDEXjcb.batchid=jc.batchid)setjc.zys=(selectb.papernumfromt_batchinfobwhereselect*fromT_JYZRHT_DOCINDEXjcwherejc.jianshuisnull查找不重复的目录号selectdistinctjc.mlhfromT_JYZRHT_DOCINDEXjcorderbyjc.mlh;-导入

27、到ta_catalognoinsertintota_catalognocno(cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status)(selectdistinctjc.mlh,cdf.classid,1,1,1fromT_JYZRHT_DOCINDEXjcjointa_classdefcdfoncdf.classcode=jc.bmh);转让需增添“ZRBH“扩展索引字段；检查档案号能否重复SELECTcount(*)FROMTA_ARCHIVEwherearchivenoin(selectJE|substr(jc.

28、batchid,instr(jc.batchid,.,1,2)+1,instr(jc.batchid,-,1,1)-instr(jc.batchid,.,1,2)-1)|-|jc.mlh|-|jc.ajhfromT_JYZRHT_DOCINDEXjc)假如没有重复，则可履行工具。用地导入时因为数据问题需注意：将T_YD_DOCINDEX表中YH字段长度设置为100；不然有些数据导入不进数据库导入后将部分数据改正下以下：selectbatchid,yh,length(yh)fromT_YD_DOCINDEXorderbylength(yh)desc;将batchid为红线框住的三条记录的YH字段

29、改正下，设置为空或许0；合计将增添档案792条；selectcount(distinctbatchid)fromT_YD_DOCINDEX;暂时表数据办理selectt.*fromt_yd_docindextorderbyt.batchid,t.docname更新分类号为“E”updatet_yd_docindextsett.flh=E;更新编目号updatet_yd_docindextsett.bmh=substr(t.batchid,1,instr(t.batchid,.,1,1)-1);更新到目录号updatet_yd_docindextsett.mlh=substr(t.batchid

30、,instr(t.batchid,.,1,1)+1,instr(t.batchid,-,1,2)-instr(t.batchid,.,1,1)-1)select*fromt_yd_docindextwheret.mlhisnull-设置索引表的ARCHIVETYPECLASSIDCATALOGNO三个的值updatet_yd_docindextsett.archivetype=4;updatet_yd_docindextsett.classid=(selectcls.classidfromta_classdefclswherecls.classcode=t.bmh);updatet_yd_do

31、cindextsett.catalogno=t.mlh;更新模卷号updatet_yd_docindextsett.ajh=substr(t.batchid,instr(t.batchid,-,1,2)+1)查找不重复的目录号selectdistinctt.mlhfromt_yd_docindextorderbyt.mlh;-导入到ta_catalognoinsertintota_catalognocno(cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status)(selectdistinctyd.mlh,cdf.cla

32、ssid,1,1,1fromt_yd_docindexydjointa_classdefcdfoncdf.classcode=yd.bmh);信访暂时表数据办理：selectcount(*)fromT_XF_DOCINDEXjcorderbyjc.ajhdescselectjc.*fromT_XF_DOCINDEXjcorderbyjc.ajhdescselectjc.*fromT_XF_DOCINDEXjcorderbyjc.batchid,jc.docname-更新到分类号FLH字段，为“F”updateT_XF_DOCINDEXjcsetjc.flh=F;-更新到编目号BMH字段upda

33、teT_XF_DOCINDEXjcsetjc.bmh=A9;-更新到目录号MLHupdateT_XF_DOCINDEXjcsetjc.mlh=substr(jc.batchid,instr(jc.batchid,-,1,1)+1,instr(jc.batchid,-,1,2)-instr(jc.batchid,-,1,1)-1)-设置索引表的ARCHIVETYPECLASSIDCATALOGNO三个的值updateT_XF_DOCINDEXjcsetjc.archivetype=7;updateT_XF_DOCINDEXjcsetjc.classid=18;updateT_XF_DOCINDE

34、Xjcsetjc.catalogno=jc.mlh;-更新模卷号，取batchid第二个-后的部分updateT_XF_DOCINDEXjcsetjc.ajh=substr(jc.batchid,instr(jc.batchid,-,1,2)+1)能否存在空的模卷号select*fromT_XF_DOCINDEXjcwherejc.ajhisnull更新档案件数，同一个文档包中的件数selectjc.batchid,count(*)fromT_XF_DOCINDEXjcgroupbyjc.batchid-t_batchinfo作业单位供给的，假如没有供给就没有，页数就不可以计算了updateT

35、_XF_DOCINDEXjcsetjc.jianshu=(selectb.docnumfromt_batchinfobwhereb.batchid=jc.batchid);updateT_XF_DOCINDEXjcb.batchid=jc.batchid)setjc.zys=(selectb.papernumfromt_batchinfobwhereselect*fromT_XF_DOCINDEXjcwherejc.jianshuisnull查找不重复的目录号selectdistinctjc.mlhfromT_XF_DOCINDEXjcorderbyjc.mlh;-导入到ta_catalogn

36、oinsertintota_catalognocno(cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status)(selectdistinctjc.mlh,cdf.classid,1,1,1fromT_XF_DOCINDEXjcjointa_classdefcdfoncdf.classcode=jc.bmh);宗地暂时表数据办理selectt.*fromt_dj_docindextorderbyt.batchid,t.docname更新分类号为“E”updatet_dj_docindextsett.flh=C;更新编目号

37、updatet_dj_docindexsett.bmh=substr(t.batchid,1,instr(t.batchid,.,1,1)-1);更新到目录号updatet_dj_docindextsett.mlh=substr(t.batchid,5,19);select*fromt_dj_docindextwheret.mlhisnull-设置索引表的ARCHIVETYPECLASSIDCATALOGNOupdatet_dj_docindextsett.archivetype=2;updatet_dj_docindextsett.classid=1;updatet_dj_docindext

38、sett.catalogno=t.mlh;三个的值查找不重复的目录号selectdistinctt.mlhfromt_yd_docindextorderbyt.mlh;更新docname值，需将其与附件名保持一致updatet_dj_docindexsetdocname=附件名称（很重要）；-导入到ta_catalognoselectdistinctt.mlhfromt_yd_docindextwheret.mlhisnotnull；insertintota_catalognocno(cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status)(selectdistinctyd.mlh,cdf.classid,1,1,1fromt_yd_docindexydjointa_classdefcdfoncdf.classcode=yd.bmh);7.查封deletefromt_cf_doc