浅谈利用PLSQL的多线程处理机制，加快处理大数据表的效率
我们在处理大数据表的时候经常会感觉的处理速度不够快，效率不够高，那么今天下面我就来简单实现下PLSQL的多线程编程处理数据：
我模拟一个简单的场景，把某一张表中的数据(当然这张表的数据非常大)同步到目的表中去
1、需要同步的数据表结构：WSFMDJ
1 -- Create table 2 create table WSFMDJ 3 ( 4 newid VARCHAR2(32) not null, 5 reghospital VARCHAR2(8), 6 hospitalid VARCHAR2(20), 7 regname VARCHAR2(20), 8 regaac002 VARCHAR2(20), 9 regmz VARCHAR2(6), 10 regblood VARCHAR2(6), 11 regage NUMBER(10), 12 regspousename VARCHAR2(50), 13 regphone VARCHAR2(20), 14 regjsjh VARCHAR2(20), 15 regcsjh VARCHAR2(20), 16 regaddres VARCHAR2(100), 17 regyunci VARCHAR2(10) default 1, 18 regchanci VARCHAR2(10) default 1, 19 regyunzhou VARCHAR2(10), 20 redeverydate NUMBER(10), 21 regcheckcount VARCHAR2(10), 22 regtaiershu VARCHAR2(10), 23 regtaifangw VARCHAR2(50), 24 regtaifangws VARCHAR2(50), 25 yangshuiqkxz VARCHAR2(50), 26 yangshuiqkl VARCHAR2(50), 27 yangshuiqk VARCHAR2(100), 28 regjdqkms VARCHAR2(8), 29 regjdrjzs NUMBER(10), 30 regjdlong NUMBER(10,2), 31 regpomo VARCHAR2(50), 32 regpomodes VARCHAR2(100), 33 regfmfs VARCHAR2(6), 34 regccfirstd NUMBER(10) default 0, 35 regccfirstm NUMBER(10) default 0, 36 regccsecondd NUMBER(10) default 0, 37 regccsecondm NUMBER(10) default 0, 38 regccthreed NUMBER(10) default 0, 39 regccthreem NUMBER(10) default 0, 40 regchanchen VARCHAR2(6) default 0, 41 regchanchenm NUMBER(10), 42 reghyqk VARCHAR2(6), 43 reghyqkdes VARCHAR2(100), 44 reggjqk VARCHAR2(100), 45 regtaipan VARCHAR2(50), 46 regtaipanwzqk VARCHAR2(50), 47 regtaipanxz VARCHAR2(50), 48 regtaipanqt VARCHAR2(100), 49 regcscxl NUMBER(10,2), 50 regchcxl NUMBER(10,2), 51 reglcgwpd VARCHAR2(50), 52 regchansfmys VARCHAR2(50), 53 regyuncqbfz VARCHAR2(100), 54 birthdate DATE, 55 regyeqksex VARCHAR2(4), 56 regyeqkbody NUMBER(10,2), 57 regyeqkheight NUMBER(10,2), 58 regyeqkpf VARCHAR2(8), 59 regyeqkpfs VARCHAR2(8), 60 regyeqkpft VARCHAR2(8), 61 regyeqkfmjg VARCHAR2(50), 62 regjkzk VARCHAR2(6), 63 regyeqkcsqx VARCHAR2(50), 64 regyeqkfmsw VARCHAR2(8), 65 regyeqkswyy VARCHAR2(50), 66 regdeathtime DATE, 67 regyeqkfmqj VARCHAR2(8), 68 regyeqkbfz VARCHAR2(50), 69 regzjc VARCHAR2(8), 70 regzjcsj DATE, 71 regzysj VARCHAR2(8), 72 regzysjdate DATE, 73 regqydss VARCHAR2(50), 74 regqydsssj DATE, 75 regsfcjdx VARCHAR2(8), 76 regcompjys VARCHAR2(8), 77 regcfjys VARCHAR2(8), 78 regjsff VARCHAR2(6), 79 regjsys VARCHAR2(100), 80 regsyz VARCHAR2(100), 81 regremark VARCHAR2(100), 82 regtime DATE, 83 regauthor VARCHAR2(50), 84 altertime DATE, 85 alterauthor VARCHAR2(50) 86 ) 87 tablespace WSJDB 88 pctfree 10 89 initrans 1 90 maxtrans 255 91 storage 92 ( 93 initial 64K 94 next 8K 95 minextents 1 96 maxextents unlimited 97 ); 98 -- Add comments to the table 99 comment on table WSFMDJ 100 is '分娩登记信息'; 101 -- Add comments to the columns 102 comment on column WSFMDJ.newid 103 is '序列号'; 104 comment on column WSFMDJ.reghospital 105 is '所属医院编码'; 106 comment on column WSFMDJ.hospitalid 107 is '住院号'; 108 comment on column WSFMDJ.regname 109 is '产妇姓名'; 110 comment on column WSFMDJ.regaac002 111 is '产妇身份证号'; 112 comment on column WSFMDJ.regmz 113 is '民族'; 114 comment on column WSFMDJ.regblood 115 is '血型'; 116 comment on column WSFMDJ.regage 117 is '年龄'; 118 comment on column WSFMDJ.regspousename 119 is '配偶姓名'; 120 comment on column WSFMDJ.regphone 121 is '联系电话'; 122 comment on column WSFMDJ.regjsjh 123 is '计生证号'; 124 comment on column WSFMDJ.regcsjh 125 is '出生证号'; 126 comment on column WSFMDJ.regaddres 127 is '现住地址'; 128 comment on column WSFMDJ.regyunci 129 is '孕次'; 130 comment on column WSFMDJ.regchanci 131 is '产次'; 132 comment on column WSFMDJ.regyunzhou 133 is '孕周'; 134 comment on column WSFMDJ.redeverydate 135 is '天数'; 136 comment on column WSFMDJ.regcheckcount 137 is '在本院检查次数'; 138 comment on column WSFMDJ.regtaiershu 139 is '胎儿数'; 140 comment on column WSFMDJ.regtaifangw 141 is '胎方位一级'; 142 comment on column WSFMDJ.regtaifangws 143 is '胎方位二级'; 144 comment on column WSFMDJ.yangshuiqkxz 145 is '羊水性质'; 146 comment on column WSFMDJ.yangshuiqkl 147 is '羊水量概况'; 148 comment on column WSFMDJ.yangshuiqk 149 is '羊水情况量描述(ml)毫升'; 150 comment on column WSFMDJ.regjdqkms 151 is '脐带情况描述(附注)'; 152 comment on column WSFMDJ.regjdrjzs 153 is '脐带绕颈周数'; 154 comment on column WSFMDJ.regjdlong 155 is '脐带长度（cm)'; 156 comment on column WSFMDJ.regpomo 157 is '破膜方式'; 158 comment on column WSFMDJ.regpomodes 159 is '破膜描述'; 160 comment on column WSFMDJ.regfmfs 161 is '分娩方式'; 162 comment on column WSFMDJ.regccfirstd 163 is '产程一用时'; 164 comment on column WSFMDJ.regccfirstm 165 is '产程一用分'; 166 comment on column WSFMDJ.regccsecondd 167 is '产程二用时'; 168 comment on column WSFMDJ.regccsecondm 169 is '产程二用分'; 170 comment on column WSFMDJ.regccthreed 171 is '产程三用时'; 172 comment on column WSFMDJ.regccthreem 173 is '产程三用分'; 174 comment on column WSFMDJ.regchanchen 175 is '累计产程用时'; 176 comment on column WSFMDJ.regchanchenm 177 is '累计产程用分'; 178 comment on column WSFMDJ.reghyqk 179 is '开口方式'; 180 comment on column WSFMDJ.reghyqkdes 181 is '会阴情况描述(cm)'; 182 comment on column WSFMDJ.reggjqk 183 is '宫颈情况'; 184 comment on column WSFMDJ.regtaipan 185 is '胎盘脱落方式'; 186 comment on column WSFMDJ.regtaipanwzqk 187 is '胎盘完整情况'; 188 comment on column WSFMDJ.regtaipanxz 189 is '胎盘性质'; 190 comment on column WSFMDJ.regtaipanqt 191 is '胎盘重量G'; 192 comment on column WSFMDJ.regcscxl 193 is '产时出血量/术中(ml)'; 194 comment on column WSFMDJ.regchcxl 195 is '产后2小时出血量（ml)'; 196 comment on column WSFMDJ.reglcgwpd 197 is '临产高危评定'; 198 comment on column WSFMDJ.regchansfmys 199 is '产时分娩因素/主要手术指征'; 200 comment on column WSFMDJ.regyuncqbfz 201 is '孕产期并发症'; 202 comment on column WSFMDJ.birthdate 203 is '出生时间'; 204 comment on column WSFMDJ.regyeqksex 205 is '婴儿性别'; 206 comment on column WSFMDJ.regyeqkbody 207 is '婴儿体重'; 208 comment on column WSFMDJ.regyeqkheight 209 is '婴儿身高'; 210 comment on column WSFMDJ.regyeqkpf 211 is '婴儿评分1MIN'; 212 comment on column WSFMDJ.regyeqkpfs 213 is '婴儿评分5MIN'; 214 comment on column WSFMDJ.regyeqkpft 215 is '婴儿评分10MIN'; 216 comment on column WSFMDJ.regyeqkfmjg 217 is '分娩结果'; 218 comment on column WSFMDJ.regjkzk 219 is '健康状况'; 220 comment on column WSFMDJ.regyeqkcsqx 221 is '婴儿出生缺陷'; 222 comment on column WSFMDJ.regyeqkfmsw 223 is '新生儿死亡'; 224 comment on column WSFMDJ.regyeqkswyy 225 is '婴儿死亡原因'; 226 comment on column WSFMDJ.regdeathtime 227 is '死亡时间'; 228 comment on column WSFMDJ.regyeqkfmqj 229 is '是否婴儿插管抢救'; 230 comment on column WSFMDJ.regyeqkbfz 231 is '新生儿并发症'; 232 comment on column WSFMDJ.regzjc 233 is '是否早接触'; 234 comment on column WSFMDJ.regzjcsj 235 is '早接触时间'; 236 comment on column WSFMDJ.regzysj 237 is '是否早吸吮'; 238 comment on column WSFMDJ.regzysjdate 239 is '早吸吮时间'; 240 comment on column WSFMDJ.regqydss 241 is '是否取阴道塞纱'; 242 comment on column WSFMDJ.regqydsssj 243 is '取阴道塞纱时间'; 244 comment on column WSFMDJ.regsfcjdx 245 is '是否采脐带血'; 246 comment on column WSFMDJ.regcompjys 247 is '是否落实绝育术'; 248 comment on column WSFMDJ.regcfjys 249 is '绝育术类型'; 250 comment on column WSFMDJ.regjsff 251 is '接生方法'; 252 comment on column WSFMDJ.regjsys 253 is '接生医生'; 254 comment on column WSFMDJ.regsyz 255 is '护婴者'; 256 comment on column WSFMDJ.regremark 257 is '备注'; 258 comment on column WSFMDJ.regtime 259 is '登记时间/数据写入时间'; 260 comment on column WSFMDJ.regauthor 261 is '登记人员'; 262 comment on column WSFMDJ.altertime 263 is '修改时间'; 264 comment on column WSFMDJ.alterauthor 265 is '修改人员'; 266 -- Create/Recreate primary, unique and foreign key constraints 267 alter table WSFMDJ 268 add constraint PK_WSFMDJ primary key (NEWID) 269 using index 270 tablespace USERS 271 pctfree 10 272 initrans 2 273 maxtrans 255 274 storage 275 ( 276 initial 64K 277 next 1M 278 minextents 1 279 maxextents unlimited 280 );
2、创建数据同步目的表：WSFMDJBAK
1 -- Create table 2 create table WSFMDJBAK 3 ( 4 newid VARCHAR2(32) not null, 5 reghospital VARCHAR2(8), 6 hospitalid VARCHAR2(20), 7 regname VARCHAR2(20), 8 regaac002 VARCHAR2(20), 9 regmz VARCHAR2(6), 10 regblood VARCHAR2(6), 11 regage NUMBER(10), 12 regspousename VARCHAR2(50), 13 regphone VARCHAR2(20), 14 regjsjh VARCHAR2(20), 15 regcsjh VARCHAR2(20), 16 regaddres VARCHAR2(100), 17 regyunci VARCHAR2(10) default 1, 18 regchanci VARCHAR2(10) default 1, 19 regyunzhou VARCHAR2(10), 20 redeverydate NUMBER(10), 21 regcheckcount VARCHAR2(10), 22 regtaiershu VARCHAR2(10), 23 regtaifangw VARCHAR2(50), 24 regtaifangws VARCHAR2(50), 25 yangshuiqkxz VARCHAR2(50), 26 yangshuiqkl VARCHAR2(50), 27 yangshuiqk VARCHAR2(100), 28 regjdqkms VARCHAR2(8), 29 regjdrjzs NUMBER(10), 30 regjdlong NUMBER(10,2), 31 regpomo VARCHAR2(50), 32 regpomodes VARCHAR2(100), 33 regfmfs VARCHAR2(6), 34 regccfirstd NUMBER(10) default 0, 35 regccfirstm NUMBER(10) default 0, 36 regccsecondd NUMBER(10) default 0, 37 regccsecondm NUMBER(10) default 0, 38 regccthreed NUMBER(10) default 0, 39 regccthreem NUMBER(10) default 0, 40 regchanchen VARCHAR2(6) default 0, 41 regchanchenm NUMBER(10), 42 reghyqk VARCHAR2(6), 43 reghyqkdes VARCHAR2(100), 44 reggjqk VARCHAR2(100), 45 regtaipan VARCHAR2(50), 46 regtaipanwzqk VARCHAR2(50), 47 regtaipanxz VARCHAR2(50), 48 regtaipanqt VARCHAR2(100), 49 regcscxl NUMBER(10,2), 50 regchcxl NUMBER(10,2), 51 reglcgwpd VARCHAR2(50), 52 regchansfmys VARCHAR2(50), 53 regyuncqbfz VARCHAR2(100), 54 birthdate DATE, 55 regyeqksex VARCHAR2(4), 56 regyeqkbody NUMBER(10,2), 57 regyeqkheight NUMBER(10,2), 58 regyeqkpf VARCHAR2(8), 59 regyeqkpfs VARCHAR2(8), 60 regyeqkpft VARCHAR2(8), 61 regyeqkfmjg VARCHAR2(50), 62 regjkzk VARCHAR2(6), 63 regyeqkcsqx VARCHAR2(50), 64 regyeqkfmsw VARCHAR2(8), 65 regyeqkswyy VARCHAR2(50), 66 regdeathtime DATE, 67 regyeqkfmqj VARCHAR2(8), 68 regyeqkbfz VARCHAR2(50), 69 regzjc VARCHAR2(8), 70 regzjcsj DATE, 71 regzysj VARCHAR2(8), 72 regzysjdate DATE, 73 regqydss VARCHAR2(50), 74 regqydsssj DATE, 75 regsfcjdx VARCHAR2(8), 76 regcompjys VARCHAR2(8), 77 regcfjys VARCHAR2(8), 78 regjsff VARCHAR2(6), 79 regjsys VARCHAR2(100), 80 regsyz VARCHAR2(100), 81 regremark VARCHAR2(100), 82 regtime DATE, 83 regauthor VARCHAR2(50), 84 altertime DATE, 85 alterauthor VARCHAR2(50), 86 options VARCHAR2(6) 87 ) 88 tablespace WSJDB 89 pctfree 10 90 initrans 1 91 maxtrans 255 92 storage 93 ( 94 initial 64K 95 next 8K 96 minextents 1 97 maxextents unlimited 98 ); 99 -- Add comments to the table 100 comment on table WSFMDJBAK 101 is '分娩登记信息备份表'; 102 -- Add comments to the columns 103 comment on column WSFMDJBAK.newid 104 is '序列号'; 105 comment on column WSFMDJBAK.reghospital 106 is '所属医院编码'; 107 comment on column WSFMDJBAK.hospitalid 108 is '住院号'; 109 comment on column WSFMDJBAK.regname 110 is '产妇姓名'; 111 comment on column WSFMDJBAK.regaac002 112 is '产妇身份证号'; 113 comment on column WSFMDJBAK.regmz 114 is '民族'; 115 comment on column WSFMDJBAK.regblood 116 is '血型'; 117 comment on column WSFMDJBAK.regage 118 is '年龄'; 119 comment on column WSFMDJBAK.regspousename 120 is '配偶姓名'; 121 comment on column WSFMDJBAK.regphone 122 is '联系电话'; 123 comment on column WSFMDJBAK.regjsjh 124 is '计生证号'; 125 comment on column WSFMDJBAK.regcsjh 126 is '出生证号'; 127 comment on column WSFMDJBAK.regaddres 128 is '现住地址'; 129 comment on column WSFMDJBAK.regyunci 130 is '孕次'; 131 comment on column WSFMDJBAK.regchanci 132 is '产次'; 133 comment on column WSFMDJBAK.regyunzhou 134 is '孕周'; 135 comment on column WSFMDJBAK.redeverydate 136 is '天数'; 137 comment on column WSFMDJBAK.regcheckcount 138 is '在本院检查次数'; 139 comment on column WSFMDJBAK.regtaiershu 140 is '胎儿数'; 141 comment on column WSFMDJBAK.regtaifangw 142 is '胎方位一级'; 143 comment on column WSFMDJBAK.regtaifangws 144 is '胎方位二级'; 145 comment on column WSFMDJBAK.yangshuiqkxz 146 is '羊水性质'; 147 comment on column WSFMDJBAK.yangshuiqkl 148 is '羊水量概况'; 149 comment on column WSFMDJBAK.yangshuiqk 150 is '羊水情况量描述(ml)毫升'; 151 comment on column WSFMDJBAK.regjdqkms 152 is '脐带情况描述(附注)'; 153 comment on column WSFMDJBAK.regjdrjzs 154 is '脐带绕颈周数'; 155 comment on column WSFMDJBAK.regjdlong 156 is '脐带长度（cm)'; 157 comment on column WSFMDJBAK.regpomo 158 is '破膜方式'; 159 comment on column WSFMDJBAK.regpomodes 160 is '破膜描述'; 161 comment on column WSFMDJBAK.regfmfs 162 is '分娩方式'; 163 comment on column WSFMDJBAK.regccfirstd 164 is '产程一用时'; 165 comment on column WSFMDJBAK.regccfirstm 166 is '产程一用分'; 167 comment on column WSFMDJBAK.regccsecondd 168 is '产程二用时'; 169 comment on column WSFMDJBAK.regccsecondm 170 is '产程二用分'; 171 comment on column WSFMDJBAK.regccthreed 172 is '产程三用时'; 173 comment on column WSFMDJBAK.regccthreem 174 is '产程三用分'; 175 comment on column WSFMDJBAK.regchanchen 176 is '累计产程用时'; 177 comment on column WSFMDJBAK.regchanchenm 178 is '累计产程用分'; 179 comment on column WSFMDJBAK.reghyqk 180 is '开口方式'; 181 comment on column WSFMDJBAK.reghyqkdes 182 is '会阴情况描述(cm)'; 183 comment on column WSFMDJBAK.reggjqk 184 is '宫颈情况'; 185 comment on column WSFMDJBAK.regtaipan 186 is '胎盘脱落方式'; 187 comment on column WSFMDJBAK.regtaipanwzqk 188 is '胎盘完整情况'; 189 comment on column WSFMDJBAK.regtaipanxz 190 is '胎盘性质'; 191 comment on column WSFMDJBAK.regtaipanqt 192 is '胎盘重量G'; 193 comment on column WSFMDJBAK.regcscxl 194 is '产时出血量/术中(ml)'; 195 comment on column WSFMDJBAK.regchcxl 196 is '产后2小时出血量（ml)'; 197 comment on column WSFMDJBAK.reglcgwpd 198 is '临产高危评定'; 199 comment on column WSFMDJBAK.regchansfmys 200 is '产时分娩因素/主要手术指征'; 201 comment on column WSFMDJBAK.regyuncqbfz 202 is '孕产期并发症'; 203 comment on column WSFMDJBAK.birthdate 204 is '出生时间'; 205 comment on column WSFMDJBAK.regyeqksex 206 is '婴儿性别'; 207 comment on column WSFMDJBAK.regyeqkbody 208 is '婴儿体重'; 209 comment on column WSFMDJBAK.regyeqkheight 210 is '婴儿身高'; 211 comment on column WSFMDJBAK.regyeqkpf 212 is '婴儿评分1MIN'; 213 comment on column WSFMDJBAK.regyeqkpfs 214 is '婴儿评分5MIN'; 215 comment on column WSFMDJBAK.regyeqkpft 216 is '婴儿评分10MIN'; 217 comment on column WSFMDJBAK.regyeqkfmjg 218 is '分娩结果'; 219 comment on column WSFMDJBAK.regjkzk 220 is '健康状况'; 221 comment on column WSFMDJBAK.regyeqkcsqx 222 is '婴儿出生缺陷'; 223 comment on column WSFMDJBAK.regyeqkfmsw 224 is '新生儿死亡'; 225 comment on column WSFMDJBAK.regyeqkswyy 226 is '婴儿死亡原因'; 227 comment on column WSFMDJBAK.regdeathtime 228 is '死亡时间'; 229 comment on column WSFMDJBAK.regyeqkfmqj 230 is '是否婴儿插管抢救'; 231 comment on column WSFMDJBAK.regyeqkbfz 232 is '新生儿并发症'; 233 comment on column WSFMDJBAK.regzjc 234 is '是否早接触'; 235 comment on column WSFMDJBAK.regzjcsj 236 is '早接触时间'; 237 comment on column WSFMDJBAK.regzysj 238 is '是否早吸吮'; 239 comment on column WSFMDJBAK.regzysjdate 240 is '早吸吮时间'; 241 comment on column WSFMDJBAK.regqydss 242 is '是否取阴道塞纱'; 243 comment on column WSFMDJBAK.regqydsssj 244 is '取阴道塞纱时间'; 245 comment on column WSFMDJBAK.regsfcjdx 246 is '是否采脐带血'; 247 comment on column WSFMDJBAK.regcompjys 248 is '是否落实绝育术'; 249 comment on column WSFMDJBAK.regcfjys 250 is '绝育术类型'; 251 comment on column WSFMDJBAK.regjsff 252 is '接生方法'; 253 comment on column WSFMDJBAK.regjsys 254 is '接生医生'; 255 comment on column WSFMDJBAK.regsyz 256 is '护婴者'; 257 comment on column WSFMDJBAK.regremark 258 is '备注'; 259 comment on column WSFMDJBAK.regtime 260 is '登记时间/数据写入时间'; 261 comment on column WSFMDJBAK.regauthor 262 is '登记人员'; 263 comment on column WSFMDJBAK.altertime 264 is '修改时间'; 265 comment on column WSFMDJBAK.alterauthor 266 is '修改人员'; 267 comment on column WSFMDJBAK.options 268 is '操作类型(0/正常)(1/已经修改)(2/无效)(3/新增数据)'; 269 -- Create/Recreate primary, unique and foreign key constraints 270 alter table WSFMDJBAK 271 add constraint PK_WSFMDJBAK primary key (NEWID) 272 using index 273 tablespace USERS 274 pctfree 10 275 initrans 2 276 maxtrans 255 277 storage 278 ( 279 initial 64K 280 next 1M 281 minextents 1 282 maxextents unlimited 283 );
3、创建多线程处理日志记录表：
1 -- Create table 2 create table PROCESS_JOB 3 ( 4 process_id VARCHAR2(32) not null, 5 proce_name VARCHAR2(100), 6 process_des VARCHAR2(100), 7 process_code NUMBER(10), 8 process_mesg VARCHAR2(100), 9 process_date DATE 10 ) 11 tablespace USERS 12 pctfree 10 13 initrans 1 14 maxtrans 255 15 storage 16 ( 17 initial 64K 18 next 8K 19 minextents 1 20 maxextents unlimited 21 ); 22 -- Add comments to the table 23 comment on table PROCESS_JOB 24 is '过程日志记录表'; 25 -- Add comments to the columns 26 comment on column PROCESS_JOB.process_id 27 is '记录线程日志的主键ID'; 28 comment on column PROCESS_JOB.proce_name 29 is '存取过程名称'; 30 comment on column PROCESS_JOB.process_des 31 is '日志描述'; 32 comment on column PROCESS_JOB.process_code 33 is '执行信息代码(100001/存在执行的线程|100002/存取过程出错|100003/记录动作说明)'; 34 comment on column PROCESS_JOB.process_mesg 35 is '错误信息'; 36 comment on column PROCESS_JOB.process_date 37 is '记录日志时间'; 38 -- Create/Recreate primary, unique and foreign key constraints 39 alter table PROCESS_JOB 40 add constraint PK_PROCESS_JOB primary key (PROCESS_ID) 41 using index 42 tablespace USERS 43 pctfree 10 44 initrans 2 45 maxtrans 255 46 storage 47 ( 48 initial 64K 49 next 1M 50 minextents 1 51 maxextents unlimited 52 );
4、创建多线程处理分组表：
-- Create table create table PROCESS_GROUPS ( xh VARCHAR2(32), table_name VARCHAR2(50), sign_code VARCHAR2(3) default '0', start_time DATE, end_time DATE, thread_num VARCHAR2(3), err_mesg VARCHAR2(200), pro_code VARCHAR2(5), spare1 VARCHAR2(50), spare2 VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the table comment on table PROCESS_GROUPS is '多线程处理分组表'; -- Add comments to the columns comment on column PROCESS_GROUPS.xh is '序号(被处理数据的主键ID)'; comment on column PROCESS_GROUPS.table_name is '被处理的表名称'; comment on column PROCESS_GROUPS.sign_code is '执行标志(0未执行，1正在执行，2执行成功，3执行失败)'; comment on column PROCESS_GROUPS.start_time is '开始时间'; comment on column PROCESS_GROUPS.end_time is '终止时间'; comment on column PROCESS_GROUPS.thread_num is '线程号'; comment on column PROCESS_GROUPS.err_mesg is '错误信息'; comment on column PROCESS_GROUPS.pro_code is '错误代码'; comment on column PROCESS_GROUPS.spare1 is '备用字段1'; comment on column PROCESS_GROUPS.spare2 is '备用字段2'; -- Create/Recreate indexes create index IND_PROCESS_GROUPS_SIGN_CODE on PROCESS_GROUPS (SIGN_CODE) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); create index IND_PROCESS_GROUPS_TABLENAME on PROCESS_GROUPS (TABLE_NAME) tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
5、创建多线程分组处理备份表：
1 -- Create table 2 create table PROCESS_GROUPS_BAK 3 ( 4 xh VARCHAR2(32), 5 table_name VARCHAR2(50), 6 sign_code VARCHAR2(3), 7 start_time DATE, 8 end_time DATE, 9 thread_num VARCHAR2(3), 10 err_mesg VARCHAR2(200), 11 pro_code VARCHAR2(5), 12 spare1 VARCHAR2(50), 13 spare2 VARCHAR2(50) 14 ) 15 tablespace USERS 16 pctfree 10 17 initrans 1 18 maxtrans 255 19 storage 20 ( 21 initial 64K 22 next 1M 23 minextents 1 24 maxextents unlimited 25 );
上述全部初始化工作已经完成，下面编写PLSQL 上干货，完成多线程处理
新建立PLSQL程序包和体
1 CREATE OR REPLACE PACKAGE PAK_多线程同步数据 IS 2 3 DFF_OK CONSTANT NUMBER := 0; -- 成功 4 DFF_ERR CONSTANT NUMBER := -1; -- 系统错误(不能定义大于0的值) 5 TYPE MY_CURSOR IS REF CURSOR; 6 7 PROCEDURE PRC_多线程_日志记录(PRM_PROCE_NAME IN VARCHAR2, 8 PRM_PROCE_DES IN VARCHAR2, 9 PRM_PROCE_CODE IN VARCHAR2, 10 PRO_PROCE_MESG IN VARCHAR2, 11 PRM_APPCODE OUT NUMBER, 12 PRM_ERRMSG OUT VARCHAR2); 13 14 PROCEDURE PRC_多线程_执行的线程(PRM_BZ OUT NUMBER, 15 PRM_APPCODE OUT NUMBER, 16 PRM_ERRMESG OUT VARCHAR2); 17 18 PROCEDURE PRC_多线程_入口(PARAMETERS_ID IN VARCHAR2, 19 PRM_APPCODE OUT NUMBER, 20 PRM_ERRMESG OUT VARCHAR2); 21 22 PROCEDURE PRC_WSFMDJBAK_数据同步(PARAMETERS_ID IN VARCHAR2, 23 PRM_APPCODE OUT NUMBER, 24 PRM_ERRMESG OUT VARCHAR2); 25 26 PROCEDURE PRC_多线程调用(PARAMETERS_ID IN VARCHAR2, 27 PRM_APPCODE OUT NUMBER, 28 PRM_ERRMESG OUT VARCHAR2); 29 30 PROCEDURE PRC_单线调用接口(PRM_线程号 IN VARCHAR2, 31 PRM_APPCODE OUT NUMBER, 32 PRM_ERRMESG OUT VARCHAR2); 33 34 PROCEDURE PRC_数据同步(PRM_NEWID IN VARCHAR2, 35 PRM_APPCODE OUT NUMBER, 36 PRM_ERRMESG OUT VARCHAR2); 37 38 PROCEDURE PRC_读线程分组表数据(PRM_线程号 IN VARCHAR2, 39 RID OUT ROWID, 40 PRM_APPCODE OUT NUMBER, 41 PRM_ERRMESG OUT VARCHAR2); 42 43 PROCEDURE GET_CURSOR_WSFMDJ(PARAMETERS_ID IN VARCHAR2, 44 CURSOR_WSFMDJ OUT MY_CURSOR); 45 END PAK_多线程同步数据;
编写具体的PLSQL函数功能实现：
1 CREATE OR REPLACE PACKAGE BODY PAK_多线程同步数据 IS 2 /*------------------------------------------------------------------------------------- 3 ** 过程名称 ：PRC_多线程_日志记录 4 ** 功能描述 ：多线程调用处理，日志记录功能 5 ** 参数描述 ：参数标识 名称 输入输出 类型 6 ** -------------------------------------------------------------------------------- 7 ** PRM_PROCE_NAME 调用的存取过程名称 输入 VARCHAR2 8 ** PRM_PROCE_DES 日志描述 输入 VARCHAR2 9 ** PRM_PROCE_CODE 日志记录代码 输入 VARCHAR2 10 ** PRO_PROCE_MESG 日志记录错误信息 输入 VARCHAR2 11 ** PRM_APPCODE 执行返回代码 输出 NUMBER 12 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 13 ||-----------------------------------------------------------------------------------*/ 14 PROCEDURE PRC_多线程_日志记录(PRM_PROCE_NAME IN VARCHAR2, 15 PRM_PROCE_DES IN VARCHAR2, 16 PRM_PROCE_CODE IN VARCHAR2, 17 PRO_PROCE_MESG IN VARCHAR2, 18 PRM_APPCODE OUT NUMBER, 19 PRM_ERRMSG OUT VARCHAR2) IS 20 BEGIN 21 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 22 INSERT INTO PROCESS_JOB 23 (PROCESS_ID, 24 PROCE_NAME, 25 PROCESS_DES, 26 PROCESS_CODE, 27 PROCESS_MESG, 28 PROCESS_DATE) 29 VALUES 30 (PROCESS_SEQ.NEXTVAL, 31 PRM_PROCE_NAME, 32 PRM_PROCE_DES, 33 PRM_PROCE_CODE, 34 PRO_PROCE_MESG, 35 SYSDATE); 36 COMMIT; 37 EXCEPTION 38 WHEN OTHERS THEN 39 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 40 PRM_ERRMSG := '插入线程日子记录表出错' || SQLERRM; 41 END PRC_多线程_日志记录; 42 43 /*------------------------------------------------------------------------------------- 44 ** 过程名称 ：PRC_多线程_执行的线程 45 ** 功能描述 ：判断是否存在未完成的线程还在执行 46 ** 参数描述 ：参数标识 名称 输入输出 类型 47 ** -------------------------------------------------------------------------------- 48 ** PRM_BZ 是否存在标志 输出 NUMBER 49 ** PRM_APPCODE 执行返回代码 输出 NUMBER 50 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 51 ||-----------------------------------------------------------------------------------*/ 52 PROCEDURE PRC_多线程_执行的线程(PRM_BZ OUT NUMBER, 53 PRM_APPCODE OUT NUMBER, 54 PRM_ERRMESG OUT VARCHAR2) IS 55 N_COUN NUMBER(10) := 0; 56 BEGIN 57 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 58 SELECT COUNT(1) 59 INTO N_COUN 60 FROM USER_SCHEDULER_JOBS A 61 WHERE A.JOB_NAME LIKE 'THREAD_FMDJ_%'; 62 63 IF N_COUN > 0 THEN 64 PRM_BZ := 1; 65 ELSE 66 PRM_BZ := 0; 67 END IF; 68 69 EXCEPTION 70 WHEN OTHERS THEN 71 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 72 PRM_ERRMESG := '判断是否存在执行的线程出错'||SQLERRM; 73 RETURN; 74 END PRC_多线程_执行的线程; 75 76 /*------------------------------------------------------------------------------------- 77 ** 过程名称 ：PRC_多线程_入口 78 ** 功能描述 ：多线程调用入口 79 ** 参数描述 ：参数标识 名称 输入输出 类型 80 ** -------------------------------------------------------------------------------- 81 ** PARAMETERS_ID 需要同步的主键字符串(以'，'间隔) 输入 VARCHAR2 82 ** PRM_APPCODE 执行返回代码 输出 NUMBER 83 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 84 ||-----------------------------------------------------------------------------------*/ 85 PROCEDURE PRC_多线程_入口(PARAMETERS_ID IN VARCHAR2, 86 PRM_APPCODE OUT NUMBER, 87 PRM_ERRMESG OUT VARCHAR2) IS 88 N_BZ NUMBER(2); 89 V_CODE NUMBER(3); 90 V_ERRMESG VARCHAR2(500); 91 BEGIN 92 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 93 --判断是否存在执行的线程 94 PRC_多线程_执行的线程(N_BZ,V_CODE,V_ERRMESG); 95 IF N_BZ > 0 THEN 96 PRC_多线程_日志记录('PRC_多线程_入口', 97 '存在执行的线程，不能继续执行', 98 '100001', 99 '存在执行的线程', 100 V_CODE, 101 V_ERRMESG); 102 PRM_ERRMESG := '存在执行的线程，不能继续执行,请联系管理员！'; 103 RETURN; 104 ELSE 105 PRC_WSFMDJBAK_数据同步(PARAMETERS_ID,PRM_APPCODE,PRM_ERRMESG); 106 IF PRM_APPCODE = PAK_多线程同步数据.DFF_ERR THEN 107 PRC_多线程_日志记录('PRC_多线程_入口', 108 PRM_ERRMESG, 109 '100002', 110 '数据同步出错', 111 V_CODE, 112 V_ERRMESG); 113 RETURN; 114 END IF; 115 END IF; 116 EXCEPTION 117 WHEN OTHERS THEN 118 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 119 PRM_ERRMESG := '多线程入口出错：'||PRM_ERRMESG|| SQLERRM; 120 RETURN; 121 END PRC_多线程_入口; 122 123 /*------------------------------------------------------------------------------------- 124 ** 过程名称 ：PRC_WSFMDJBAK_数据同步 125 ** 功能描述 ：数据同步操作入口 126 ** 参数描述 ：参数标识 名称 输入输出 类型 127 ** -------------------------------------------------------------------------------- 128 ** PARAMETERS_ID 需要同步的主键字符串(以'，'间隔) 输入 VARCHAR2 129 ** PRM_APPCODE 执行返回代码 输出 NUMBER 130 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 131 ||-----------------------------------------------------------------------------------*/ 132 PROCEDURE PRC_WSFMDJBAK_数据同步(PARAMETERS_ID IN VARCHAR2, 133 PRM_APPCODE OUT NUMBER, 134 PRM_ERRMESG OUT VARCHAR2) IS 135 --N_TEMP NUMBER(8) := 0; 136 BEGIN 137 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 138 --日志记录开始同步数据 139 PRC_多线程_日志记录('PRC_WSFMDJBAK_数据同步', 140 '同步分娩登记信息备份表数据开始', 141 '100003', 142 '', 143 PRM_APPCODE, 144 PRM_ERRMESG); 145 PRC_多线程调用(PARAMETERS_ID,PRM_APPCODE,PRM_ERRMESG); 146 IF PRM_APPCODE = PAK_多线程同步数据.DFF_ERR THEN 147 RETURN; 148 END IF; 149 150 /*WHILE (TRUE) LOOP 151 --延时10秒后再次查询结果情况 152 DBMS_LOCK.SLEEP(2); 153 BEGIN 154 SELECT COUNT(1) 155 INTO N_TEMP 156 FROM PROCESS_GROUPS A 157 WHERE A.SIGN_CODE IN ('0', '1') 158 AND A.TABLE_NAME = 'WSFMDJ'; 159 EXCEPTION 160 WHEN NO_DATA_FOUND THEN 161 N_TEMP := 0; 162 END; 163 EXIT WHEN N_TEMP = 0; 164 END LOOP;*/ 165 166 --日志记录开始同步数据 167 PRC_多线程_日志记录('PRC_WSFMDJBAK_数据同步', 168 '同步分娩登记信息备份表数据结束', 169 '100003', 170 '', 171 PRM_APPCODE, 172 PRM_ERRMESG); 173 EXCEPTION 174 WHEN OTHERS THEN 175 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 176 PRM_ERRMESG := 'PRC_WSFMDJBAK_数据同步存取过程执行出错'||SQLERRM; 177 RETURN; 178 END PRC_WSFMDJBAK_数据同步; 179 180 /*------------------------------------------------------------------------------------- 181 ** 过程名称 ：PKG_多线程调用 182 ** 功能描述 ：调用多线程进行数据处理 183 ** 参数描述 ：参数标识 名称 输入输出 类型 184 ** -------------------------------------------------------------------------------- 185 ** PARAMETERS_ID 需要同步的主键字符串(以'，'间隔) 输入 VARCHAR2 186 ** PRM_APPCODE 执行返回代码 输出 NUMBER 187 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 188 ||-----------------------------------------------------------------------------------*/ 189 PROCEDURE PRC_多线程调用(PARAMETERS_ID IN VARCHAR2, 190 PRM_APPCODE OUT NUMBER, 191 PRM_ERRMESG OUT VARCHAR2) IS 192 N_THREADS PLS_INTEGER := 5; --启用的线程数量 193 CURSOR_WSFMDJ MY_CURSOR; --分娩登记信息表的游标变量 194 REC_FMDJ WSFMDJ%ROWTYPE; 195 STR_JOB VARCHAR2(500); 196 197 BEGIN 198 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 199 200 BEGIN 201 --备份线程分组表的历史数据 202 INSERT INTO PROCESS_GROUPS_BAK SELECT * FROM PROCESS_GROUPS; 203 --删除线程分组表的历史数据 204 DELETE FROM PROCESS_GROUPS A WHERE A.TABLE_NAME='WSFMDJ'; 205 --拿到需要同步操作的数据游标 206 GET_CURSOR_WSFMDJ(PARAMETERS_ID,CURSOR_WSFMDJ); 207 208 --生成线程分组表主键数据 209 PRC_多线程_日志记录('PRC_多线程调用', 210 '生成线程分组表主键数据', 211 '100003', 212 '', 213 PRM_APPCODE, 214 PRM_ERRMESG); 215 216 LOOP 217 FETCH CURSOR_WSFMDJ INTO REC_FMDJ; 218 EXIT WHEN CURSOR_WSFMDJ%NOTFOUND OR REC_FMDJ.NEWID IS NULL; 219 220 -- 生成线程分组表的数据信息 221 INSERT INTO PROCESS_GROUPS 222 (XH, 223 TABLE_NAME, 224 SIGN_CODE, 225 START_TIME, 226 END_TIME, 227 THREAD_NUM, 228 ERR_MESG, 229 PRO_CODE) 230 VALUES 231 (REC_FMDJ.NEWID, 232 'WSFMDJ', 233 '0', 234 NULL, 235 NULL, 236 NULL, 237 NULL, 238 NULL); 239 -- 提交 240 COMMIT; 241 END LOOP; 242 CLOSE CURSOR_WSFMDJ; 243 EXCEPTION 244 WHEN OTHERS THEN 245 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 246 PRM_ERRMESG := '生成线程分组表数据出错！错误原因：' || SQLERRM; 247 RETURN; 248 END; 249 250 --产生多个线程进行数据处理 251 PRC_多线程_日志记录('PRC_多线程调用', 252 '产生多个线程进行数据处理', 253 '100003', 254 '', 255 PRM_APPCODE, 256 PRM_ERRMESG); 257 --产生5个线程调用处理数据 258 FOR X IN 0 .. N_THREADS - 1 LOOP 259 STR_JOB := 'DECLARE 260 PRM_APPCODE NUMBER(3); 261 PRM_ERRMESG VARCHAR2(200); 262 BEGIN 263 PAK_多线程同步数据.PRC_单线调用接口(''' || X || ''', PRM_APPCODE, PRM_ERRMESG); 264 END;'; 265 266 DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'THREAD_FMDJ_' || X, 267 JOB_TYPE => 'PLSQL_BLOCK', 268 JOB_ACTION => STR_JOB, 269 ENABLED => FALSE, 270 AUTO_DROP => TRUE, 271 COMMENTS => 'PRC_WSFMDJBAK_数据同步' || X); 272 DBMS_SCHEDULER.ENABLE(NAME => 'THREAD_FMDJ_' || X); 273 END LOOP; 274 EXCEPTION 275 WHEN OTHERS THEN 276 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 277 PRM_ERRMESG := 'PRC_多线程调用存取过程执行出错,'||SQLERRM; 278 RETURN; 279 END PRC_多线程调用; 280 281 /*------------------------------------------------------------------------------------- 282 ** 过程名称 ：PRC_单线调用接口 283 ** 功能描述 ：单线程处理接口入口，进行数据处理 284 ** 参数描述 ：参数标识 名称 输入输出 类型 285 ** -------------------------------------------------------------------------------- 286 ** PRM_线程号 线程号 输入 VARCHAR2 287 ** PRM_APPCODE 执行返回代码 输出 NUMBER 288 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 289 ||-----------------------------------------------------------------------------------*/ 290 PROCEDURE PRC_单线调用接口(PRM_线程号 IN VARCHAR2, 291 PRM_APPCODE OUT NUMBER, 292 PRM_ERRMESG OUT VARCHAR2) IS 293 RID ROWID; 294 REC_PROCESS_GROUPS PROCESS_GROUPS%ROWTYPE; 295 N_COUNT NUMBER(10); 296 BEGIN 297 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 298 LOOP 299 PRC_读线程分组表数据(PRM_线程号, RID, PRM_APPCODE, PRM_ERRMESG); 300 IF PRM_APPCODE = PAK_多线程同步数据.DFF_ERR THEN 301 --日志记录开始同步数据 302 PRC_多线程_日志记录('PRC_单线调用接口', 303 '读取线程分组表ROWID出错', 304 '100002', 305 PRM_ERRMESG, 306 PRM_APPCODE, 307 PRM_ERRMESG); 308 RETURN; 309 END IF; 310 311 IF RID IS NULL THEN 312 SELECT COUNT(1) 313 INTO N_COUNT 314 FROM PROCESS_GROUPS A 315 WHERE A.SIGN_CODE = '0' 316 AND A.TABLE_NAME = 'WSFMDJ'; 317 IF N_COUNT > 0 THEN 318 GOTO NEXTOPTION; 319 END IF; 320 END IF; 321 -- 未取得有效的数据,退出循环 322 EXIT WHEN RID IS NULL; 323 SELECT * INTO REC_PROCESS_GROUPS FROM PROCESS_GROUPS WHERE ROWID = RID; --行ID 324 --取到线程分组表中的XH 即是被处理表的主键，传入函数进行数据同步处理 325 PRC_数据同步(REC_PROCESS_GROUPS.XH, PRM_APPCODE, PRM_ERRMESG); 326 327 BEGIN 328 UPDATE PROCESS_GROUPS A 329 SET A.SIGN_CODE = DECODE(PRM_APPCODE, '0', '2', '3'), --执行标志 330 A.END_TIME = SYSDATE, --终止时间 331 A.ERR_MESG = SUBSTR(PRM_ERRMESG, 1, 200) --错误信息 332 WHERE ROWID = RID; --行ID 333 EXCEPTION 334 WHEN NO_DATA_FOUND THEN 335 GOTO NEXTOPTION; 336 END; 337 338 <<NEXTOPTION>> 339 -- 提交 340 COMMIT; 341 END LOOP; 342 343 UPDATE PROCESS_GROUPS A 344 SET A.SIGN_CODE = '3', --执行标志 345 A.END_TIME = SYSDATE, --终止时间 346 A.ERR_MESG = '系统错误，暂时无法执行' --错误信息 347 WHERE A.TABLE_NAME = 'WSFMDJ' --表序号 348 AND A.SIGN_CODE = '1' 349 AND A.THREAD_NUM = PRM_线程号; --执行标志 350 351 EXCEPTION 352 WHEN OTHERS THEN 353 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 354 UPDATE PROCESS_GROUPS A 355 SET A.SIGN_CODE = '3', --执行标志 356 A.END_TIME = SYSDATE, --终止时间 357 A.ERR_MESG = SUBSTR(PRM_ERRMESG, 1, 200) --错误信息 358 WHERE ROWID = RID; 359 END PRC_单线调用接口; 360 361 /*------------------------------------------------------------------------------------- 362 ** 过程名称 ：PRC_数据同步 363 ** 功能描述 ：进行具体的数据同步处理 364 ** 参数描述 ：参数标识 名称 输入输出 类型 365 ** -------------------------------------------------------------------------------- 366 ** NEWID 被处理表的主键 输入 VARCHAR2 367 ** PRM_APPCODE 执行返回代码 输出 NUMBER 368 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 369 ||-----------------------------------------------------------------------------------*/ 370 PROCEDURE PRC_数据同步(PRM_NEWID IN VARCHAR2, 371 PRM_APPCODE OUT NUMBER, 372 PRM_ERRMESG OUT VARCHAR2) IS 373 REC_FMDJ WSFMDJ%ROWTYPE; 374 BEGIN 375 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 376 BEGIN 377 SELECT WS.* INTO REC_FMDJ FROM WSFMDJ WS WHERE WS.NEWID = PRM_NEWID; 378 UPDATE WSFMDJBAK A 379 SET A.REGHOSPITAL = REC_FMDJ.REGHOSPITAL, 380 A.HOSPITALID = REC_FMDJ.HOSPITALID, 381 A.REGNAME = REC_FMDJ.REGNAME, 382 A.REGAAC002 = REC_FMDJ.REGAAC002, 383 A.REGMZ = REC_FMDJ.REGMZ, 384 A.REGBLOOD = REC_FMDJ.REGBLOOD, 385 A.REGAGE = REC_FMDJ.REGAGE, 386 A.REGSPOUSENAME = REC_FMDJ.REGSPOUSENAME, 387 A.REGPHONE = REC_FMDJ.REGPHONE, 388 A.REGJSJH = REC_FMDJ.REGJSJH, 389 A.REGCSJH = REC_FMDJ.REGCSJH, 390 A.REGADDRES = REC_FMDJ.REGADDRES, 391 A.REGYUNCI = REC_FMDJ.REGYUNCI, 392 A.REGYUNZHOU = REC_FMDJ.REGYUNZHOU, 393 A.REDEVERYDATE = REC_FMDJ.REDEVERYDATE, 394 A.REGCHECKCOUNT = REC_FMDJ.REGCHECKCOUNT, 395 A.REGTAIERSHU = REC_FMDJ.REGTAIERSHU, 396 A.REGTAIFANGW = REC_FMDJ.REGTAIFANGW, 397 A.REGTAIFANGWS = REC_FMDJ.REGTAIFANGWS, 398 A.YANGSHUIQKXZ = REC_FMDJ.YANGSHUIQKXZ, 399 A.YANGSHUIQKL = REC_FMDJ.YANGSHUIQKL, 400 A.YANGSHUIQK = REC_FMDJ.YANGSHUIQK, 401 A.REGJDQKMS = REC_FMDJ.REGJDQKMS, 402 A.REGJDRJZS = REC_FMDJ.REGJDRJZS, 403 A.REGJDLONG = REC_FMDJ.REGJDLONG, 404 A.REGPOMO = REC_FMDJ.REGPOMO, 405 A.REGPOMODES = REC_FMDJ.REGPOMODES, 406 A.REGFMFS = REC_FMDJ.REGFMFS, 407 A.REGCCFIRSTD = REC_FMDJ.REGCCFIRSTD, 408 A.REGCCFIRSTM = REC_FMDJ.REGCCFIRSTM, 409 A.REGCCSECONDD = REC_FMDJ.REGCCSECONDD, 410 A.REGCCSECONDM = REC_FMDJ.REGCCSECONDM, 411 A.REGCCTHREED = REC_FMDJ.REGCCTHREED, 412 A.REGCCTHREEM = REC_FMDJ.REGCCTHREEM, 413 A.REGCHANCHEN = REC_FMDJ.REGCHANCHEN, 414 A.REGCHANCHENM = REC_FMDJ.REGCHANCHENM, 415 A.REGHYQK = REC_FMDJ.REGHYQK, 416 A.REGHYQKDES = REC_FMDJ.REGHYQKDES, 417 A.REGGJQK = REC_FMDJ.REGGJQK, 418 A.REGTAIPAN = REC_FMDJ.REGTAIPAN, 419 A.REGTAIPANWZQK = REC_FMDJ.REGTAIPANWZQK, 420 A.REGTAIPANXZ = REC_FMDJ.REGTAIPANXZ, 421 A.REGTAIPANQT = REC_FMDJ.REGTAIPANQT, 422 A.REGCSCXL = REC_FMDJ.REGCSCXL, 423 A.REGCHCXL = REC_FMDJ.REGCHCXL, 424 A.REGLCGWPD = REC_FMDJ.REGLCGWPD, 425 A.REGCHANSFMYS = REC_FMDJ.REGCHANSFMYS, 426 A.REGYUNCQBFZ = REC_FMDJ.REGYUNCQBFZ, 427 A.BIRTHDATE = REC_FMDJ.BIRTHDATE, 428 A.REGYEQKSEX = REC_FMDJ.REGYEQKSEX, 429 A.REGYEQKBODY = REC_FMDJ.REGYEQKBODY, 430 A.REGYEQKHEIGHT = REC_FMDJ.REGYEQKHEIGHT, 431 A.REGYEQKPF = REC_FMDJ.REGYEQKPF, 432 A.REGYEQKPFS = REC_FMDJ.REGYEQKPFS, 433 A.REGYEQKPFT = REC_FMDJ.REGYEQKPFT, 434 A.REGYEQKFMJG = REC_FMDJ.REGYEQKFMJG, 435 A.REGJKZK = REC_FMDJ.REGJKZK, 436 A.REGYEQKCSQX = REC_FMDJ.REGYEQKCSQX, 437 A.REGYEQKFMSW = REC_FMDJ.REGYEQKFMSW, 438 A.REGYEQKSWYY = REC_FMDJ.REGYEQKSWYY, 439 A.REGDEATHTIME = REC_FMDJ.REGDEATHTIME, 440 A.REGYEQKFMQJ = REC_FMDJ.REGYEQKFMQJ, 441 A.REGYEQKBFZ = REC_FMDJ.REGYEQKBFZ, 442 A.REGZJC = REC_FMDJ.REGZJC, 443 A.REGZJCSJ = REC_FMDJ.REGZJCSJ, 444 A.REGZYSJ = REC_FMDJ.REGZYSJ, 445 A.REGZYSJDATE = REC_FMDJ.REGZYSJDATE, 446 A.REGQYDSS = REC_FMDJ.REGQYDSS, 447 A.REGQYDSSSJ = REC_FMDJ.REGQYDSSSJ, 448 A.REGSFCJDX = REC_FMDJ.REGSFCJDX, 449 A.REGCOMPJYS = REC_FMDJ.REGCOMPJYS, 450 A.REGCFJYS = REC_FMDJ.REGCFJYS, 451 A.REGJSFF = REC_FMDJ.REGJSFF, 452 A.REGJSYS = REC_FMDJ.REGJSYS, 453 A.REGSYZ = REC_FMDJ.REGSYZ, 454 A.REGREMARK = REC_FMDJ.REGREMARK, 455 A.REGTIME = REC_FMDJ.REGTIME, 456 A.REGAUTHOR = REC_FMDJ.REGAUTHOR, 457 A.ALTERTIME = REC_FMDJ.ALTERTIME, 458 A.ALTERAUTHOR = REC_FMDJ.ALTERAUTHOR, 459 A.OPTIONS = '0' 460 WHERE A.NEWID = REC_FMDJ.NEWID; 461 EXCEPTION 462 WHEN NO_DATA_FOUND THEN 463 NULL; 464 END; 465 EXCEPTION 466 WHEN OTHERS THEN 467 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 468 PRM_ERRMESG := 'PAK_多线程同步数据.PRC_数据同步,更新分娩登记信息备份表出错' || SQLERRM; 469 RETURN; 470 END PRC_数据同步; 471 472 /*------------------------------------------------------------------------------------- 473 ** 过程名称 ：PRC_读线程分组表数据 474 ** 功能描述 ：读取线程分组表中的ROWID值，并返回 475 ** 参数描述 ：参数标识 名称 输入输出 类型 476 ** -------------------------------------------------------------------------------- 477 ** PRM_线程号 线程号 输入 VARCHAR2 478 ** RID ROWID值 输出 ROWID 479 ** PRM_APPCODE 执行返回代码 输出 NUMBER 480 ** PRM_ERRMSG 出错信息 输出 VARCHAR2 481 ||-----------------------------------------------------------------------------------*/ 482 PROCEDURE PRC_读线程分组表数据(PRM_线程号 IN VARCHAR2, 483 RID OUT ROWID, 484 PRM_APPCODE OUT NUMBER, 485 PRM_ERRMESG OUT VARCHAR2) IS 486 BEGIN 487 PRM_APPCODE := PAK_多线程同步数据.DFF_OK; 488 -- 行ID 489 RID := NULL; 490 491 BEGIN 492 SELECT ROWID --行ID 493 INTO RID 494 FROM PROCESS_GROUPS A 495 WHERE A.SIGN_CODE = '0' --执行标志(0-未执行) 496 AND ROWNUM < 2 497 AND A.TABLE_NAME = 'WSFMDJ' --执行表名 498 FOR UPDATE SKIP LOCKED; --锁定记录 499 EXCEPTION 500 WHEN NO_DATA_FOUND THEN 501 GOTO ENDS; 502 END; 503 504 BEGIN 505 UPDATE PROCESS_GROUPS A 506 SET A.SIGN_CODE = '1', --执行标志(1-正在执行) 507 A.START_TIME = SYSDATE, --开始时间 508 A.THREAD_NUM = PRM_线程号 --线程号 509 WHERE ROWID = RID; --行ID 510 -- 提交 511 COMMIT; 512 EXCEPTION 513 WHEN NO_DATA_FOUND THEN 514 GOTO ENDS; 515 END; 516 517 <<ENDS>> 518 NULL; 519 EXCEPTION 520 WHEN OTHERS THEN 521 PRM_APPCODE := PAK_多线程同步数据.DFF_ERR; 522 PRM_ERRMESG := '取线程分组表中的rowid错误' || SQLERRM; 523 RETURN; 524 END PRC_读线程分组表数据; 525 526 /*------------------------------------------------------------------------------------- 527 ** 过程名称 ：GET_CURSOR_WSFMDJ 528 ** 功能描述 ：拿到分娩登记信息表的游标 529 ** 参数描述 ：参数标识 名称 输入输出 类型 530 ** -------------------------------------------------------------------------------- 531 ** PARAMETERS_ID 需要同步的主键NEWID字符串 输入 VARCHAR2 532 ** CURSOR_WSFMDJ 返回的游标 输出 MY_CURSOR 533 ||-----------------------------------------------------------------------------------*/ 534 PROCEDURE GET_CURSOR_WSFMDJ(PARAMETERS_ID IN VARCHAR2, 535 CURSOR_WSFMDJ OUT MY_CURSOR) IS 536 SQLSTR VARCHAR2(8000); 537 BEGIN 538 SQLSTR := ' SELECT A.* FROM WSFMDJ A WHERE A.NEWID IN( '; 539 FOR PARAMETERS_REC IN(SELECT TO_CHAR(strvalue) CODE 540 FROM TABLE(FN_SPLIT(PARAMETERS_ID, ','))) LOOP 541 SQLSTR := SQLSTR||''''||PARAMETERS_REC.CODE||''''||','; 542 END LOOP; 543 SQLSTR := SUBSTR(SQLSTR,0,(LENGTH(SQLSTR) - 1))||')'; 544 OPEN CURSOR_WSFMDJ FOR SQLSTR; 545 END GET_CURSOR_WSFMDJ; 546 547 END PAK_多线程同步数据;
经过上述内容，基本使用了多线程的PLSQL来处理大数据表，以提升处理效率！
简单的记录！
