我们在处理大数据表的时候经常会感觉的处理速度不够快，效率不够高，那么今天下面我就来简单实现下PLSQL的多线程编程处理数据：
我模拟一个简单的场景，把某一张表中的数据(当然这张表的数据非常大)同步到目的表中去
1、需要同步的数据表结构：WSFMDJ
-- Create table
create table WSFMDJ
(
newid VARCHAR2(32) not null,
reghospital VARCHAR2(8),
hospitalid VARCHAR2(20),
regname VARCHAR2(20),
regaac002 VARCHAR2(20),
regmz VARCHAR2(6),
regblood VARCHAR2(6),
regage NUMBER(10),
regspousename VARCHAR2(50),
regphone VARCHAR2(20),
regjsjh VARCHAR2(20),
regcsjh VARCHAR2(20),
regaddres VARCHAR2(100),
regyunci VARCHAR2(10) default 1,
regchanci VARCHAR2(10) default 1,
regyunzhou VARCHAR2(10),
redeverydate NUMBER(10),
regcheckcount VARCHAR2(10),
regtaiershu VARCHAR2(10),
regtaifangw VARCHAR2(50),
regtaifangws VARCHAR2(50),
yangshuiqkxz VARCHAR2(50),
yangshuiqkl VARCHAR2(50),
yangshuiqk VARCHAR2(100),
regjdqkms VARCHAR2(8),
regjdrjzs NUMBER(10),
regjdlong NUMBER(10,2),
regpomo VARCHAR2(50),
regpomodes VARCHAR2(100),
regfmfs VARCHAR2(6),
regccfirstd NUMBER(10) default 0,
regccfirstm NUMBER(10) default 0,
regccsecondd NUMBER(10) default 0,
regccsecondm NUMBER(10) default 0,
regccthreed NUMBER(10) default 0,
regccthreem NUMBER(10) default 0,
regchanchen VARCHAR2(6) default 0,
regchanchenm NUMBER(10),
reghyqk VARCHAR2(6),
reghyqkdes VARCHAR2(100),
reggjqk VARCHAR2(100),
regtaipan VARCHAR2(50),
regtaipanwzqk VARCHAR2(50),
regtaipanxz VARCHAR2(50),
regtaipanqt VARCHAR2(100),
regcscxl NUMBER(10,2),
regchcxl NUMBER(10,2),
reglcgwpd VARCHAR2(50),
regchansfmys VARCHAR2(50),
regyuncqbfz VARCHAR2(100),
birthdate DATE,
regyeqksex VARCHAR2(4),
regyeqkbody NUMBER(10,2),
regyeqkheight NUMBER(10,2),
regyeqkpf VARCHAR2(8),
regyeqkpfs VARCHAR2(8),
regyeqkpft VARCHAR2(8),
regyeqkfmjg VARCHAR2(50),
regjkzk VARCHAR2(6),
regyeqkcsqx VARCHAR2(50),
regyeqkfmsw VARCHAR2(8),
regyeqkswyy VARCHAR2(50),
regdeathtime DATE,
regyeqkfmqj VARCHAR2(8),
regyeqkbfz VARCHAR2(50),
regzjc VARCHAR2(8),
regzjcsj DATE,
regzysj VARCHAR2(8),
regzysjdate DATE,
regqydss VARCHAR2(50),
regqydsssj DATE,
regsfcjdx VARCHAR2(8),
regcompjys VARCHAR2(8),
regcfjys VARCHAR2(8),
regjsff VARCHAR2(6),
regjsys VARCHAR2(100),
regsyz VARCHAR2(100),
regremark VARCHAR2(100),
regtime DATE,
regauthor VARCHAR2(50),
altertime DATE,
alterauthor VARCHAR2(50)
)
tablespace WSJDB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table WSFMDJ
is '分娩登记信息';
-- Add comments to the columns
comment on column WSFMDJ.newid
is '序列号';
comment on column WSFMDJ.reghospital
is '所属医院编码';
comment on column WSFMDJ.hospitalid
is '住院号';
comment on column WSFMDJ.regname
is '产妇姓名';
comment on column WSFMDJ.regaac002
is '产妇身份证号';
comment on column WSFMDJ.regmz
is '民族';
comment on column WSFMDJ.regblood
is '血型';
comment on column WSFMDJ.regage
is '年龄';
comment on column WSFMDJ.regspousename
is '配偶姓名';
comment on column WSFMDJ.regphone
is '联系电话';
comment on column WSFMDJ.regjsjh
is '计生证号';
comment on column WSFMDJ.regcsjh
is '出生证号';
comment on column WSFMDJ.regaddres
is '现住地址';
comment on column WSFMDJ.regyunci
is '孕次';
comment on column WSFMDJ.regchanci
is '产次';
comment on column WSFMDJ.regyunzhou
is '孕周';
comment on column WSFMDJ.redeverydate
is '天数';
comment on column WSFMDJ.regcheckcount
is '在本院检查次数';
comment on column WSFMDJ.regtaiershu
is '胎儿数';
comment on column WSFMDJ.regtaifangw
is '胎方位一级';
comment on column WSFMDJ.regtaifangws
is '胎方位二级';
comment on column WSFMDJ.yangshuiqkxz
is '羊水性质';
comment on column WSFMDJ.yangshuiqkl
is '羊水量概况';
comment on column WSFMDJ.yangshuiqk
is '羊水情况量描述(ml)毫升';
comment on column WSFMDJ.regjdqkms
is '脐带情况描述(附注)';
comment on column WSFMDJ.regjdrjzs
is '脐带绕颈周数';
comment on column WSFMDJ.regjdlong
is '脐带长度（cm)';
comment on column WSFMDJ.regpomo
is '破膜方式';
comment on column WSFMDJ.regpomodes
is '破膜描述';
comment on column WSFMDJ.regfmfs
is '分娩方式';
comment on column WSFMDJ.regccfirstd
is '产程一用时';
comment on column WSFMDJ.regccfirstm
is '产程一用分';
comment on column WSFMDJ.regccsecondd
is '产程二用时';
comment on column WSFMDJ.regccsecondm
is '产程二用分';
comment on column WSFMDJ.regccthreed
is '产程三用时';
comment on column WSFMDJ.regccthreem
is '产程三用分';
comment on column WSFMDJ.regchanchen
is '累计产程用时';
comment on column WSFMDJ.regchanchenm
is '累计产程用分';
comment on column WSFMDJ.reghyqk
is '开口方式';
comment on column WSFMDJ.reghyqkdes
is '会阴情况描述(cm)';
comment on column WSFMDJ.reggjqk
is '宫颈情况';
comment on column WSFMDJ.regtaipan
is '胎盘脱落方式';
comment on column WSFMDJ.regtaipanwzqk
is '胎盘完整情况';
comment on column WSFMDJ.regtaipanxz
is '胎盘性质';
comment on column WSFMDJ.regtaipanqt
is '胎盘重量G';
comment on column WSFMDJ.regcscxl
is '产时出血量/术中(ml)';
comment on column WSFMDJ.regchcxl
is '产后2小时出血量（ml)';
comment on column WSFMDJ.reglcgwpd
is '临产高危评定';
comment on column WSFMDJ.regchansfmys
is '产时分娩因素/主要手术指征';
comment on column WSFMDJ.regyuncqbfz
is '孕产期并发症';
comment on column WSFMDJ.birthdate
is '出生时间';
comment on column WSFMDJ.regyeqksex
is '婴儿性别';
comment on column WSFMDJ.regyeqkbody
is '婴儿体重';
comment on column WSFMDJ.regyeqkheight
is '婴儿身高';
comment on column WSFMDJ.regyeqkpf
is '婴儿评分1MIN';
comment on column WSFMDJ.regyeqkpfs
is '婴儿评分5MIN';
comment on column WSFMDJ.regyeqkpft
is '婴儿评分10MIN';
comment on column WSFMDJ.regyeqkfmjg
is '分娩结果';
comment on column WSFMDJ.regjkzk
is '健康状况';
comment on column WSFMDJ.regyeqkcsqx
is '婴儿出生缺陷';
comment on column WSFMDJ.regyeqkfmsw
is '新生儿死亡';
comment on column WSFMDJ.regyeqkswyy
is '婴儿死亡原因';
comment on column WSFMDJ.regdeathtime
is '死亡时间';
comment on column WSFMDJ.regyeqkfmqj
is '是否婴儿插管抢救';
comment on column WSFMDJ.regyeqkbfz
is '新生儿并发症';
comment on column WSFMDJ.regzjc
is '是否早接触';
comment on column WSFMDJ.regzjcsj
is '早接触时间';
comment on column WSFMDJ.regzysj
is '是否早吸吮';
comment on column WSFMDJ.regzysjdate
is '早吸吮时间';
comment on column WSFMDJ.regqydss
is '是否取阴道塞纱';
comment on column WSFMDJ.regqydsssj
is '取阴道塞纱时间';
comment on column WSFMDJ.regsfcjdx
is '是否采脐带血';
comment on column WSFMDJ.regcompjys
is '是否落实绝育术';
comment on column WSFMDJ.regcfjys
is '绝育术类型';
comment on column WSFMDJ.regjsff
is '接生方法';
comment on column WSFMDJ.regjsys
is '接生医生';
comment on column WSFMDJ.regsyz
is '护婴者';
comment on column WSFMDJ.regremark
is '备注';
comment on column WSFMDJ.regtime
is '登记时间/数据写入时间';
comment on column WSFMDJ.regauthor
is '登记人员';
comment on column WSFMDJ.altertime
is '修改时间';
comment on column WSFMDJ.alterauthor
is '修改人员';
-- Create/Recreate primary, unique and foreign key constraints
alter table WSFMDJ
add constraint PK_WSFMDJ primary key (NEWID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
2、创建数据同步目的表：WSFMDJBAK
-- Create table
create table WSFMDJBAK
(
newid VARCHAR2(32) not null,
reghospital VARCHAR2(8),
hospitalid VARCHAR2(20),
regname VARCHAR2(20),
regaac002 VARCHAR2(20),
regmz VARCHAR2(6),
regblood VARCHAR2(6),
regage NUMBER(10),
regspousename VARCHAR2(50),
regphone VARCHAR2(20),
regjsjh VARCHAR2(20),
regcsjh VARCHAR2(20),
regaddres VARCHAR2(100),
regyunci VARCHAR2(10) default 1,
regchanci VARCHAR2(10) default 1,
regyunzhou VARCHAR2(10),
redeverydate NUMBER(10),
regcheckcount VARCHAR2(10),
regtaiershu VARCHAR2(10),
regtaifangw VARCHAR2(50),
regtaifangws VARCHAR2(50),
yangshuiqkxz VARCHAR2(50),
yangshuiqkl VARCHAR2(50),
yangshuiqk VARCHAR2(100),
regjdqkms VARCHAR2(8),
regjdrjzs NUMBER(10),
regjdlong NUMBER(10,2),
regpomo VARCHAR2(50),
regpomodes VARCHAR2(100),
regfmfs VARCHAR2(6),
regccfirstd NUMBER(10) default 0,
regccfirstm NUMBER(10) default 0,
regccsecondd NUMBER(10) default 0,
regccsecondm NUMBER(10) default 0,
regccthreed NUMBER(10) default 0,
regccthreem NUMBER(10) default 0,
regchanchen VARCHAR2(6) default 0,
regchanchenm NUMBER(10),
reghyqk VARCHAR2(6),
reghyqkdes VARCHAR2(100),
reggjqk VARCHAR2(100),
regtaipan VARCHAR2(50),
regtaipanwzqk VARCHAR2(50),
regtaipanxz VARCHAR2(50),
regtaipanqt VARCHAR2(100),
regcscxl NUMBER(10,2),
regchcxl NUMBER(10,2),
reglcgwpd VARCHAR2(50),
regchansfmys VARCHAR2(50),
regyuncqbfz VARCHAR2(100),
birthdate DATE,
regyeqksex VARCHAR2(4),
regyeqkbody NUMBER(10,2),
regyeqkheight NUMBER(10,2),
regyeqkpf VARCHAR2(8),
regyeqkpfs VARCHAR2(8),
regyeqkpft VARCHAR2(8),
regyeqkfmjg VARCHAR2(50),
regjkzk VARCHAR2(6),
regyeqkcsqx VARCHAR2(50),
regyeqkfmsw VARCHAR2(8),
regyeqkswyy VARCHAR2(50),
regdeathtime DATE,
regyeqkfmqj VARCHAR2(8),
regyeqkbfz VARCHAR2(50),
regzjc VARCHAR2(8),
regzjcsj DATE,
regzysj VARCHAR2(8),
regzysjdate DATE,
regqydss VARCHAR2(50),
regqydsssj DATE,
regsfcjdx VARCHAR2(8),
regcompjys VARCHAR2(8),
regcfjys VARCHAR2(8),
regjsff VARCHAR2(6),
regjsys VARCHAR2(100),
regsyz VARCHAR2(100),
regremark VARCHAR2(100),
regtime DATE,
regauthor VARCHAR2(50),
altertime DATE,
alterauthor VARCHAR2(50),
options VARCHAR2(6)
)
tablespace WSJDB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table WSFMDJBAK
is '分娩登记信息备份表';
-- Add comments to the columns
comment on column WSFMDJBAK.newid
is '序列号';
comment on column WSFMDJBAK.reghospital
is '所属医院编码';
comment on column WSFMDJBAK.hospitalid
is '住院号';
comment on column WSFMDJBAK.regname
is '产妇姓名';
comment on column WSFMDJBAK.regaac002
is '产妇身份证号';
comment on column WSFMDJBAK.regmz
is '民族';
comment on column WSFMDJBAK.regblood
is '血型';
comment on column WSFMDJBAK.regage
is '年龄';
comment on column WSFMDJBAK.regspousename
is '配偶姓名';
comment on column WSFMDJBAK.regphone
is '联系电话';
comment on column WSFMDJBAK.regjsjh
is '计生证号';
comment on column WSFMDJBAK.regcsjh
is '出生证号';
comment on column WSFMDJBAK.regaddres
is '现住地址';
comment on column WSFMDJBAK.regyunci
is '孕次';
comment on column WSFMDJBAK.regchanci
is '产次';
comment on column WSFMDJBAK.regyunzhou
is '孕周';
comment on column WSFMDJBAK.redeverydate
is '天数';
comment on column WSFMDJBAK.regcheckcount
is '在本院检查次数';
comment on column WSFMDJBAK.regtaiershu
is '胎儿数';
comment on column WSFMDJBAK.regtaifangw
is '胎方位一级';
comment on column WSFMDJBAK.regtaifangws
is '胎方位二级';
comment on column WSFMDJBAK.yangshuiqkxz
is '羊水性质';
comment on column WSFMDJBAK.yangshuiqkl
is '羊水量概况';
comment on column WSFMDJBAK.yangshuiqk
is '羊水情况量描述(ml)毫升';
comment on column WSFMDJBAK.regjdqkms
is '脐带情况描述(附注)';
comment on column WSFMDJBAK.regjdrjzs
is '脐带绕颈周数';
comment on column WSFMDJBAK.regjdlong
is '脐带长度（cm)';
comment on column WSFMDJBAK.regpomo
is '破膜方式';
comment on column WSFMDJBAK.regpomodes
is '破膜描述';
comment on column WSFMDJBAK.regfmfs
is '分娩方式';
comment on column WSFMDJBAK.regccfirstd
is '产程一用时';
comment on column WSFMDJBAK.regccfirstm
is '产程一用分';
comment on column WSFMDJBAK.regccsecondd
is '产程二用时';
comment on column WSFMDJBAK.regccsecondm
is '产程二用分';
comment on column WSFMDJBAK.regccthreed
is '产程三用时';
comment on column WSFMDJBAK.regccthreem
is '产程三用分';
comment on column WSFMDJBAK.regchanchen
is '累计产程用时';
comment on column WSFMDJBAK.regchanchenm
is '累计产程用分';
comment on column WSFMDJBAK.reghyqk
is '开口方式';
comment on column WSFMDJBAK.reghyqkdes
is '会阴情况描述(cm)';
comment on column WSFMDJBAK.reggjqk
is '宫颈情况';
comment on column WSFMDJBAK.regtaipan
is '胎盘脱落方式';
comment on column WSFMDJBAK.regtaipanwzqk
is '胎盘完整情况';
comment on column WSFMDJBAK.regtaipanxz
is '胎盘性质';
comment on column WSFMDJBAK.regtaipanqt
is '胎盘重量G';
comment on column WSFMDJBAK.regcscxl
is '产时出血量/术中(ml)';
comment on column WSFMDJBAK.regchcxl
is '产后2小时出血量（ml)';
comment on column WSFMDJBAK.reglcgwpd
is '临产高危评定';
comment on column WSFMDJBAK.regchansfmys
is '产时分娩因素/主要手术指征';
comment on column WSFMDJBAK.regyuncqbfz
is '孕产期并发症';
comment on column WSFMDJBAK.birthdate
is '出生时间';
comment on column WSFMDJBAK.regyeqksex
is '婴儿性别';
comment on column WSFMDJBAK.regyeqkbody
is '婴儿体重';
comment on column WSFMDJBAK.regyeqkheight
is '婴儿身高';
comment on column WSFMDJBAK.regyeqkpf
is '婴儿评分1MIN';
comment on column WSFMDJBAK.regyeqkpfs
is '婴儿评分5MIN';
comment on column WSFMDJBAK.regyeqkpft
is '婴儿评分10MIN';
comment on column WSFMDJBAK.regyeqkfmjg
is '分娩结果';
comment on column WSFMDJBAK.regjkzk
is '健康状况';
comment on column WSFMDJBAK.regyeqkcsqx
is '婴儿出生缺陷';
comment on column WSFMDJBAK.regyeqkfmsw
is '新生儿死亡';
comment on column WSFMDJBAK.regyeqkswyy
is '婴儿死亡原因';
comment on column WSFMDJBAK.regdeathtime
is '死亡时间';
comment on column WSFMDJBAK.regyeqkfmqj
is '是否婴儿插管抢救';
comment on column WSFMDJBAK.regyeqkbfz
is '新生儿并发症';
comment on column WSFMDJBAK.regzjc
is '是否早接触';
comment on column WSFMDJBAK.regzjcsj
is '早接触时间';
comment on column WSFMDJBAK.regzysj
is '是否早吸吮';
comment on column WSFMDJBAK.regzysjdate
is '早吸吮时间';
comment on column WSFMDJBAK.regqydss
is '是否取阴道塞纱';
comment on column WSFMDJBAK.regqydsssj
is '取阴道塞纱时间';
comment on column WSFMDJBAK.regsfcjdx
is '是否采脐带血';
comment on column WSFMDJBAK.regcompjys
is '是否落实绝育术';
comment on column WSFMDJBAK.regcfjys
is '绝育术类型';
comment on column WSFMDJBAK.regjsff
is '接生方法';
comment on column WSFMDJBAK.regjsys
is '接生医生';
comment on column WSFMDJBAK.regsyz
is '护婴者';
comment on column WSFMDJBAK.regremark
is '备注';
comment on column WSFMDJBAK.regtime
is '登记时间/数据写入时间';
comment on column WSFMDJBAK.regauthor
is '登记人员';
comment on column WSFMDJBAK.altertime
is '修改时间';
comment on column WSFMDJBAK.alterauthor
is '修改人员';
comment on column WSFMDJBAK.options
is '操作类型(0/正常)(1/已经修改)(2/无效)(3/新增数据)';
-- Create/Recreate primary, unique and foreign key constraints
alter table WSFMDJBAK
add constraint PK_WSFMDJBAK primary key (NEWID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
3、创建多线程处理日志记录表：
-- Create table
create table PROCESS_JOB
(
process_id VARCHAR2(32) not null,
proce_name VARCHAR2(100),
process_des VARCHAR2(100),
process_code NUMBER(10),
process_mesg VARCHAR2(100),
process_date DATE
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table PROCESS_JOB
is '过程日志记录表';
-- Add comments to the columns
comment on column PROCESS_JOB.process_id
is '记录线程日志的主键ID';
comment on column PROCESS_JOB.proce_name
is '存取过程名称';
comment on column PROCESS_JOB.process_des
is '日志描述';
comment on column PROCESS_JOB.process_code
is '执行信息代码(100001/存在执行的线程|100002/存取过程出错|100003/记录动作说明)';
comment on column PROCESS_JOB.process_mesg
is '错误信息';
comment on column PROCESS_JOB.process_date
is '记录日志时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table PROCESS_JOB
add constraint PK_PROCESS_JOB primary key (PROCESS_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
4、创建多线程处理分组表：
-- Create table
create table PROCESS_GROUPS
(
xh VARCHAR2(32),
table_name VARCHAR2(50),
sign_code VARCHAR2(3) default '',
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、创建多线程分组处理备份表：
-- Create table
create table PROCESS_GROUPS_BAK
(
xh VARCHAR2(32),
table_name VARCHAR2(50),
sign_code VARCHAR2(3),
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
);
上述全部初始化工作已经完成，下面编写PLSQL 上干货，完成多线程处理
新建立PLSQL程序包和体
CREATE OR REPLACE PACKAGE PAK_多线程同步数据 IS DFF_OK CONSTANT NUMBER := 0; -- 成功
DFF_ERR CONSTANT NUMBER := -1; -- 系统错误(不能定义大于0的值)
TYPE MY_CURSOR IS REF CURSOR; PROCEDURE PRC_多线程_日志记录(PRM_PROCE_NAME IN VARCHAR2,
PRM_PROCE_DES IN VARCHAR2,
PRM_PROCE_CODE IN VARCHAR2,
PRO_PROCE_MESG IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMSG OUT VARCHAR2); PROCEDURE PRC_多线程_执行的线程(PRM_BZ OUT NUMBER,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2); PROCEDURE PRC_多线程_入口(PARAMETERS_ID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2); PROCEDURE PRC_WSFMDJBAK_数据同步(PARAMETERS_ID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2); PROCEDURE PRC_多线程调用(PARAMETERS_ID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2); PROCEDURE PRC_单线调用接口(PRM_线程号 IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2); PROCEDURE PRC_数据同步(PRM_NEWID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2); PROCEDURE PRC_读线程分组表数据(PRM_线程号 IN VARCHAR2,
RID OUT ROWID,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2); PROCEDURE GET_CURSOR_WSFMDJ(PARAMETERS_ID IN VARCHAR2,
CURSOR_WSFMDJ OUT MY_CURSOR);
END PAK_多线程同步数据;
编写具体的PLSQL函数功能实现：
CREATE OR REPLACE PACKAGE BODY PAK_多线程同步数据 IS
/*-------------------------------------------------------------------------------------
** 过程名称 ：PRC_多线程_日志记录
** 功能描述 ：多线程调用处理，日志记录功能
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PRM_PROCE_NAME 调用的存取过程名称 输入 VARCHAR2
** PRM_PROCE_DES 日志描述 输入 VARCHAR2
** PRM_PROCE_CODE 日志记录代码 输入 VARCHAR2
** PRO_PROCE_MESG 日志记录错误信息 输入 VARCHAR2
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_多线程_日志记录(PRM_PROCE_NAME IN VARCHAR2,
PRM_PROCE_DES IN VARCHAR2,
PRM_PROCE_CODE IN VARCHAR2,
PRO_PROCE_MESG IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMSG OUT VARCHAR2) IS
BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK;
INSERT INTO PROCESS_JOB
(PROCESS_ID,
PROCE_NAME,
PROCESS_DES,
PROCESS_CODE,
PROCESS_MESG,
PROCESS_DATE)
VALUES
(PROCESS_SEQ.NEXTVAL,
PRM_PROCE_NAME,
PRM_PROCE_DES,
PRM_PROCE_CODE,
PRO_PROCE_MESG,
SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMSG := '插入线程日子记录表出错' || SQLERRM;
END PRC_多线程_日志记录; /*-------------------------------------------------------------------------------------
** 过程名称 ：PRC_多线程_执行的线程
** 功能描述 ：判断是否存在未完成的线程还在执行
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PRM_BZ 是否存在标志 输出 NUMBER
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_多线程_执行的线程(PRM_BZ OUT NUMBER,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2) IS
N_COUN NUMBER(10) := 0;
BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK;
SELECT COUNT(1)
INTO N_COUN
FROM USER_SCHEDULER_JOBS A
WHERE A.JOB_NAME LIKE 'THREAD_FMDJ_%'; IF N_COUN > 0 THEN
PRM_BZ := 1;
ELSE
PRM_BZ := 0;
END IF; EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMESG := '判断是否存在执行的线程出错'||SQLERRM;
RETURN;
END PRC_多线程_执行的线程; /*-------------------------------------------------------------------------------------
** 过程名称 ：PRC_多线程_入口
** 功能描述 ：多线程调用入口
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PARAMETERS_ID 需要同步的主键字符串(以'，'间隔) 输入 VARCHAR2
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_多线程_入口(PARAMETERS_ID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2) IS
N_BZ NUMBER(2);
V_CODE NUMBER(3);
V_ERRMESG VARCHAR2(500);
BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK;
--判断是否存在执行的线程
PRC_多线程_执行的线程(N_BZ,V_CODE,V_ERRMESG);
IF N_BZ > 0 THEN
PRC_多线程_日志记录('PRC_多线程_入口',
'存在执行的线程，不能继续执行',
'',
'存在执行的线程',
V_CODE,
V_ERRMESG);
PRM_ERRMESG := '存在执行的线程，不能继续执行,请联系管理员！';
RETURN;
ELSE
PRC_WSFMDJBAK_数据同步(PARAMETERS_ID,PRM_APPCODE,PRM_ERRMESG);
IF PRM_APPCODE = PAK_多线程同步数据.DFF_ERR THEN
PRC_多线程_日志记录('PRC_多线程_入口',
PRM_ERRMESG,
'',
'数据同步出错',
V_CODE,
V_ERRMESG);
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMESG := '多线程入口出错：'||PRM_ERRMESG|| SQLERRM;
RETURN;
END PRC_多线程_入口; /*-------------------------------------------------------------------------------------
** 过程名称 ：PRC_WSFMDJBAK_数据同步
** 功能描述 ：数据同步操作入口
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PARAMETERS_ID 需要同步的主键字符串(以'，'间隔) 输入 VARCHAR2
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_WSFMDJBAK_数据同步(PARAMETERS_ID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2) IS
--N_TEMP NUMBER(8) := 0;
BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK;
--日志记录开始同步数据
PRC_多线程_日志记录('PRC_WSFMDJBAK_数据同步',
'同步分娩登记信息备份表数据开始',
'',
'',
PRM_APPCODE,
PRM_ERRMESG);
PRC_多线程调用(PARAMETERS_ID,PRM_APPCODE,PRM_ERRMESG);
IF PRM_APPCODE = PAK_多线程同步数据.DFF_ERR THEN
RETURN;
END IF; /*WHILE (TRUE) LOOP
--延时10秒后再次查询结果情况
DBMS_LOCK.SLEEP(2);
BEGIN
SELECT COUNT(1)
INTO N_TEMP
FROM PROCESS_GROUPS A
WHERE A.SIGN_CODE IN ('0', '1')
AND A.TABLE_NAME = 'WSFMDJ';
EXCEPTION
WHEN NO_DATA_FOUND THEN
N_TEMP := 0;
END;
EXIT WHEN N_TEMP = 0;
END LOOP;*/ --日志记录开始同步数据
PRC_多线程_日志记录('PRC_WSFMDJBAK_数据同步',
'同步分娩登记信息备份表数据结束',
'',
'',
PRM_APPCODE,
PRM_ERRMESG);
EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMESG := 'PRC_WSFMDJBAK_数据同步存取过程执行出错'||SQLERRM;
RETURN;
END PRC_WSFMDJBAK_数据同步; /*-------------------------------------------------------------------------------------
** 过程名称 ：PKG_多线程调用
** 功能描述 ：调用多线程进行数据处理
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PARAMETERS_ID 需要同步的主键字符串(以'，'间隔) 输入 VARCHAR2
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_多线程调用(PARAMETERS_ID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2) IS
N_THREADS PLS_INTEGER := 5; --启用的线程数量
CURSOR_WSFMDJ MY_CURSOR; --分娩登记信息表的游标变量
REC_FMDJ WSFMDJ%ROWTYPE;
STR_JOB VARCHAR2(500); BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK; BEGIN
--备份线程分组表的历史数据
INSERT INTO PROCESS_GROUPS_BAK SELECT * FROM PROCESS_GROUPS;
--删除线程分组表的历史数据
DELETE FROM PROCESS_GROUPS A WHERE A.TABLE_NAME='WSFMDJ';
--拿到需要同步操作的数据游标
GET_CURSOR_WSFMDJ(PARAMETERS_ID,CURSOR_WSFMDJ); --生成线程分组表主键数据
PRC_多线程_日志记录('PRC_多线程调用',
'生成线程分组表主键数据',
'',
'',
PRM_APPCODE,
PRM_ERRMESG); LOOP
FETCH CURSOR_WSFMDJ INTO REC_FMDJ;
EXIT WHEN CURSOR_WSFMDJ%NOTFOUND OR REC_FMDJ.NEWID IS NULL; -- 生成线程分组表的数据信息
INSERT INTO PROCESS_GROUPS
(XH,
TABLE_NAME,
SIGN_CODE,
START_TIME,
END_TIME,
THREAD_NUM,
ERR_MESG,
PRO_CODE)
VALUES
(REC_FMDJ.NEWID,
'WSFMDJ',
'',
NULL,
NULL,
NULL,
NULL,
NULL);
-- 提交
COMMIT;
END LOOP;
CLOSE CURSOR_WSFMDJ;
EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMESG := '生成线程分组表数据出错！错误原因：' || SQLERRM;
RETURN;
END; --产生多个线程进行数据处理
PRC_多线程_日志记录('PRC_多线程调用',
'产生多个线程进行数据处理',
'',
'',
PRM_APPCODE,
PRM_ERRMESG);
--产生5个线程调用处理数据
FOR X IN 0 .. N_THREADS - 1 LOOP
STR_JOB := 'DECLARE
PRM_APPCODE NUMBER(3);
PRM_ERRMESG VARCHAR2(200);
BEGIN
PAK_多线程同步数据.PRC_单线调用接口(''' || X || ''', PRM_APPCODE, PRM_ERRMESG);
END;'; DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'THREAD_FMDJ_' || X,
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => STR_JOB,
ENABLED => FALSE,
AUTO_DROP => TRUE,
COMMENTS => 'PRC_WSFMDJBAK_数据同步' || X);
DBMS_SCHEDULER.ENABLE(NAME => 'THREAD_FMDJ_' || X);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMESG := 'PRC_多线程调用存取过程执行出错,'||SQLERRM;
RETURN;
END PRC_多线程调用; /*-------------------------------------------------------------------------------------
** 过程名称 ：PRC_单线调用接口
** 功能描述 ：单线程处理接口入口，进行数据处理
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PRM_线程号 线程号 输入 VARCHAR2
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_单线调用接口(PRM_线程号 IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2) IS
RID ROWID;
REC_PROCESS_GROUPS PROCESS_GROUPS%ROWTYPE;
N_COUNT NUMBER(10);
BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK;
LOOP
PRC_读线程分组表数据(PRM_线程号, RID, PRM_APPCODE, PRM_ERRMESG);
IF PRM_APPCODE = PAK_多线程同步数据.DFF_ERR THEN
--日志记录开始同步数据
PRC_多线程_日志记录('PRC_单线调用接口',
'读取线程分组表ROWID出错',
'',
PRM_ERRMESG,
PRM_APPCODE,
PRM_ERRMESG);
RETURN;
END IF; IF RID IS NULL THEN
SELECT COUNT(1)
INTO N_COUNT
FROM PROCESS_GROUPS A
WHERE A.SIGN_CODE = ''
AND A.TABLE_NAME = 'WSFMDJ';
IF N_COUNT > 0 THEN
GOTO NEXTOPTION;
END IF;
END IF;
-- 未取得有效的数据,退出循环
EXIT WHEN RID IS NULL;
SELECT * INTO REC_PROCESS_GROUPS FROM PROCESS_GROUPS WHERE ROWID = RID; --行ID
--取到线程分组表中的XH 即是被处理表的主键，传入函数进行数据同步处理
PRC_数据同步(REC_PROCESS_GROUPS.XH, PRM_APPCODE, PRM_ERRMESG); BEGIN
UPDATE PROCESS_GROUPS A
SET A.SIGN_CODE = DECODE(PRM_APPCODE, '', '', ''), --执行标志
A.END_TIME = SYSDATE, --终止时间
A.ERR_MESG = SUBSTR(PRM_ERRMESG, 1, 200) --错误信息
WHERE ROWID = RID; --行ID
EXCEPTION
WHEN NO_DATA_FOUND THEN
GOTO NEXTOPTION;
END; <<NEXTOPTION>>
-- 提交
COMMIT;
END LOOP; UPDATE PROCESS_GROUPS A
SET A.SIGN_CODE = '', --执行标志
A.END_TIME = SYSDATE, --终止时间
A.ERR_MESG = '系统错误，暂时无法执行' --错误信息
WHERE A.TABLE_NAME = 'WSFMDJ' --表序号
AND A.SIGN_CODE = ''
AND A.THREAD_NUM = PRM_线程号; --执行标志 EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
UPDATE PROCESS_GROUPS A
SET A.SIGN_CODE = '', --执行标志
A.END_TIME = SYSDATE, --终止时间
A.ERR_MESG = SUBSTR(PRM_ERRMESG, 1, 200) --错误信息
WHERE ROWID = RID;
END PRC_单线调用接口; /*-------------------------------------------------------------------------------------
** 过程名称 ：PRC_数据同步
** 功能描述 ：进行具体的数据同步处理
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** NEWID 被处理表的主键 输入 VARCHAR2
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_数据同步(PRM_NEWID IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2) IS
REC_FMDJ WSFMDJ%ROWTYPE;
BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK;
BEGIN
SELECT WS.* INTO REC_FMDJ FROM WSFMDJ WS WHERE WS.NEWID = PRM_NEWID;
UPDATE WSFMDJBAK A
SET A.REGHOSPITAL = REC_FMDJ.REGHOSPITAL,
A.HOSPITALID = REC_FMDJ.HOSPITALID,
A.REGNAME = REC_FMDJ.REGNAME,
A.REGAAC002 = REC_FMDJ.REGAAC002,
A.REGMZ = REC_FMDJ.REGMZ,
A.REGBLOOD = REC_FMDJ.REGBLOOD,
A.REGAGE = REC_FMDJ.REGAGE,
A.REGSPOUSENAME = REC_FMDJ.REGSPOUSENAME,
A.REGPHONE = REC_FMDJ.REGPHONE,
A.REGJSJH = REC_FMDJ.REGJSJH,
A.REGCSJH = REC_FMDJ.REGCSJH,
A.REGADDRES = REC_FMDJ.REGADDRES,
A.REGYUNCI = REC_FMDJ.REGYUNCI,
A.REGYUNZHOU = REC_FMDJ.REGYUNZHOU,
A.REDEVERYDATE = REC_FMDJ.REDEVERYDATE,
A.REGCHECKCOUNT = REC_FMDJ.REGCHECKCOUNT,
A.REGTAIERSHU = REC_FMDJ.REGTAIERSHU,
A.REGTAIFANGW = REC_FMDJ.REGTAIFANGW,
A.REGTAIFANGWS = REC_FMDJ.REGTAIFANGWS,
A.YANGSHUIQKXZ = REC_FMDJ.YANGSHUIQKXZ,
A.YANGSHUIQKL = REC_FMDJ.YANGSHUIQKL,
A.YANGSHUIQK = REC_FMDJ.YANGSHUIQK,
A.REGJDQKMS = REC_FMDJ.REGJDQKMS,
A.REGJDRJZS = REC_FMDJ.REGJDRJZS,
A.REGJDLONG = REC_FMDJ.REGJDLONG,
A.REGPOMO = REC_FMDJ.REGPOMO,
A.REGPOMODES = REC_FMDJ.REGPOMODES,
A.REGFMFS = REC_FMDJ.REGFMFS,
A.REGCCFIRSTD = REC_FMDJ.REGCCFIRSTD,
A.REGCCFIRSTM = REC_FMDJ.REGCCFIRSTM,
A.REGCCSECONDD = REC_FMDJ.REGCCSECONDD,
A.REGCCSECONDM = REC_FMDJ.REGCCSECONDM,
A.REGCCTHREED = REC_FMDJ.REGCCTHREED,
A.REGCCTHREEM = REC_FMDJ.REGCCTHREEM,
A.REGCHANCHEN = REC_FMDJ.REGCHANCHEN,
A.REGCHANCHENM = REC_FMDJ.REGCHANCHENM,
A.REGHYQK = REC_FMDJ.REGHYQK,
A.REGHYQKDES = REC_FMDJ.REGHYQKDES,
A.REGGJQK = REC_FMDJ.REGGJQK,
A.REGTAIPAN = REC_FMDJ.REGTAIPAN,
A.REGTAIPANWZQK = REC_FMDJ.REGTAIPANWZQK,
A.REGTAIPANXZ = REC_FMDJ.REGTAIPANXZ,
A.REGTAIPANQT = REC_FMDJ.REGTAIPANQT,
A.REGCSCXL = REC_FMDJ.REGCSCXL,
A.REGCHCXL = REC_FMDJ.REGCHCXL,
A.REGLCGWPD = REC_FMDJ.REGLCGWPD,
A.REGCHANSFMYS = REC_FMDJ.REGCHANSFMYS,
A.REGYUNCQBFZ = REC_FMDJ.REGYUNCQBFZ,
A.BIRTHDATE = REC_FMDJ.BIRTHDATE,
A.REGYEQKSEX = REC_FMDJ.REGYEQKSEX,
A.REGYEQKBODY = REC_FMDJ.REGYEQKBODY,
A.REGYEQKHEIGHT = REC_FMDJ.REGYEQKHEIGHT,
A.REGYEQKPF = REC_FMDJ.REGYEQKPF,
A.REGYEQKPFS = REC_FMDJ.REGYEQKPFS,
A.REGYEQKPFT = REC_FMDJ.REGYEQKPFT,
A.REGYEQKFMJG = REC_FMDJ.REGYEQKFMJG,
A.REGJKZK = REC_FMDJ.REGJKZK,
A.REGYEQKCSQX = REC_FMDJ.REGYEQKCSQX,
A.REGYEQKFMSW = REC_FMDJ.REGYEQKFMSW,
A.REGYEQKSWYY = REC_FMDJ.REGYEQKSWYY,
A.REGDEATHTIME = REC_FMDJ.REGDEATHTIME,
A.REGYEQKFMQJ = REC_FMDJ.REGYEQKFMQJ,
A.REGYEQKBFZ = REC_FMDJ.REGYEQKBFZ,
A.REGZJC = REC_FMDJ.REGZJC,
A.REGZJCSJ = REC_FMDJ.REGZJCSJ,
A.REGZYSJ = REC_FMDJ.REGZYSJ,
A.REGZYSJDATE = REC_FMDJ.REGZYSJDATE,
A.REGQYDSS = REC_FMDJ.REGQYDSS,
A.REGQYDSSSJ = REC_FMDJ.REGQYDSSSJ,
A.REGSFCJDX = REC_FMDJ.REGSFCJDX,
A.REGCOMPJYS = REC_FMDJ.REGCOMPJYS,
A.REGCFJYS = REC_FMDJ.REGCFJYS,
A.REGJSFF = REC_FMDJ.REGJSFF,
A.REGJSYS = REC_FMDJ.REGJSYS,
A.REGSYZ = REC_FMDJ.REGSYZ,
A.REGREMARK = REC_FMDJ.REGREMARK,
A.REGTIME = REC_FMDJ.REGTIME,
A.REGAUTHOR = REC_FMDJ.REGAUTHOR,
A.ALTERTIME = REC_FMDJ.ALTERTIME,
A.ALTERAUTHOR = REC_FMDJ.ALTERAUTHOR,
A.OPTIONS = ''
WHERE A.NEWID = REC_FMDJ.NEWID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMESG := 'PAK_多线程同步数据.PRC_数据同步,更新分娩登记信息备份表出错' || SQLERRM;
RETURN;
END PRC_数据同步; /*-------------------------------------------------------------------------------------
** 过程名称 ：PRC_读线程分组表数据
** 功能描述 ：读取线程分组表中的ROWID值，并返回
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PRM_线程号 线程号 输入 VARCHAR2
** RID ROWID值 输出 ROWID
** PRM_APPCODE 执行返回代码 输出 NUMBER
** PRM_ERRMSG 出错信息 输出 VARCHAR2
||-----------------------------------------------------------------------------------*/
PROCEDURE PRC_读线程分组表数据(PRM_线程号 IN VARCHAR2,
RID OUT ROWID,
PRM_APPCODE OUT NUMBER,
PRM_ERRMESG OUT VARCHAR2) IS
BEGIN
PRM_APPCODE := PAK_多线程同步数据.DFF_OK;
-- 行ID
RID := NULL; BEGIN
SELECT ROWID --行ID
INTO RID
FROM PROCESS_GROUPS A
WHERE A.SIGN_CODE = '' --执行标志(0-未执行)
AND ROWNUM < 2
AND A.TABLE_NAME = 'WSFMDJ' --执行表名
FOR UPDATE SKIP LOCKED; --锁定记录
EXCEPTION
WHEN NO_DATA_FOUND THEN
GOTO ENDS;
END; BEGIN
UPDATE PROCESS_GROUPS A
SET A.SIGN_CODE = '', --执行标志(1-正在执行)
A.START_TIME = SYSDATE, --开始时间
A.THREAD_NUM = PRM_线程号 --线程号
WHERE ROWID = RID; --行ID
-- 提交
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
GOTO ENDS;
END; <<ENDS>>
NULL;
EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PAK_多线程同步数据.DFF_ERR;
PRM_ERRMESG := '取线程分组表中的rowid错误' || SQLERRM;
RETURN;
END PRC_读线程分组表数据; /*-------------------------------------------------------------------------------------
** 过程名称 ：GET_CURSOR_WSFMDJ
** 功能描述 ：拿到分娩登记信息表的游标
** 参数描述 ：参数标识 名称 输入输出 类型
** --------------------------------------------------------------------------------
** PARAMETERS_ID 需要同步的主键NEWID字符串 输入 VARCHAR2
** CURSOR_WSFMDJ 返回的游标 输出 MY_CURSOR
||-----------------------------------------------------------------------------------*/
PROCEDURE GET_CURSOR_WSFMDJ(PARAMETERS_ID IN VARCHAR2,
CURSOR_WSFMDJ OUT MY_CURSOR) IS
SQLSTR VARCHAR2(8000);
BEGIN
SQLSTR := ' SELECT A.* FROM WSFMDJ A WHERE A.NEWID IN( ';
FOR PARAMETERS_REC IN(SELECT TO_CHAR(strvalue) CODE
FROM TABLE(FN_SPLIT(PARAMETERS_ID, ','))) LOOP
SQLSTR := SQLSTR||''''||PARAMETERS_REC.CODE||''''||',';
END LOOP;
SQLSTR := SUBSTR(SQLSTR,0,(LENGTH(SQLSTR) - 1))||')';
OPEN CURSOR_WSFMDJ FOR SQLSTR;
END GET_CURSOR_WSFMDJ; END PAK_多线程同步数据;
经过上述内容，基本使用了多线程的PLSQL来处理大数据表，以提升处理效率！
简单的记录！