基于Oracle plsql的多线程编程架构

--程序包

CREATE OR REPLACE PACKAGE PKG_多线程服务 IS

TYPE TYPE_TABLE_STRING IS TABLE OF VARCHAR(100) INDEX BY BINARY_INTEGER;


FUNCTION FUN_字符解析(Prm_Sourcestr IN VARCHAR2,
Prm_Separator IN VARCHAR2,
Prm_Arrary OUT TYPE_TABLE_STRING) RETURN NUMBER;

PROCEDURE PRC_服务进程(PRM_THREADNAME IN VARCHAR2,
prm_program IN VARCHAR2,
prm_parmcount IN VARCHAR2,
prm_operid IN VARCHAR2);

PROCEDURE xxxxxxxxxxxxxx;

PROCEDURE PRC_调试使用(PRM_NAME IN VARCHAR2,
PRM_PARMS IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMSG OUT VARCHAR2);

PROCEDURE PRC_进程控制(prm_program IN VARCHAR2,
prm_parmcount IN VARCHAR2,
prm_operid IN varchar2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMSG OUT VARCHAR2);

PROCEDURE PRC_进程停止;

END PKG_多线程服务;
/
CREATE OR REPLACE PACKAGE BODY PKG_多线程服务 IS

PROCEDURE PRC_调试使用(PRM_NAME IN VARCHAR2,
PRM_PARMS IN VARCHAR2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMSG OUT VARCHAR2) IS

STRINGARRAY TYPE_TABLE_STRING;
n_count PLS_INTEGER;
v_cur_hdl INTEGER;
v_rows_processed BINARY_INTEGER;
dym_sql VARCHAR2(500);
v_space INTEGER;
BEGIN
PRM_APPCODE := PKG_A_MACRO.DEF_OK;
PRM_ERRMSG := NULL;
IF v_cur_hdl IS NULL THEN

n_count:=FUN_字符解析(PRM_PARMS,'|',STRINGARRAY);
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
dym_sql :='BEGIN '||PRM_NAME||'(';

FOR i IN 1..n_count LOOP
IF i = n_count THEN
dym_sql:=dym_sql||':'||i;
ELSE
dym_sql:=dym_sql||':'||i||',';
END IF;
END LOOP;
dym_sql:=dym_sql||'); END;';
DBMS_SQL.PARSE(v_cur_hdl, dym_sql, DBMS_SQL.NATIVE);

END IF;

FOR i IN 1..n_count-2 LOOP
DBMS_SQL.BIND_VARIABLE(v_cur_hdl, ':'||i,nvl(STRINGARRAY(i),'') );
END LOOP;

DBMS_SQL.BIND_VARIABLE(v_cur_hdl, ':'||(n_count-1),v_space);
DBMS_SQL.BIND_VARIABLE(v_cur_hdl, ':'||n_count,v_space);

v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);


DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);

EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PKG_A_MACRO.DEF_ERR;
PRM_ERRMSG := 'PRC_调试使用:' || SQLERRM;
END;

PROCEDURE xxxxxxxxxxxxxx IS
BEGIN
NULL;
END;
/*
分析输入参数
*/
FUNCTION FUN_字符解析(Prm_Sourcestr IN VARCHAR2,
Prm_Separator IN VARCHAR2,
Prm_Arrary OUT TYPE_TABLE_STRING) RETURN NUMBER IS

n_Count NUMBER(6); -- 分隔符的个数
n_Posb NUMBER(6); -- 初始位置
n_Pose NUMBER(6); -- 分隔符的位置

BEGIN
n_Count := 0;
n_Posb := 1;

-- 非空校验
IF Prm_Sourcestr IS NULL THEN
RETURN 0;
ELSIF Prm_Separator IS NULL THEN
RETURN 0;
END IF;

LOOP
n_Pose := Instr(Prm_Sourcestr, Prm_Separator, n_Posb, 1);

n_Count := n_Count + 1;
IF n_Pose = 0 THEN
Prm_Arrary(n_Count) := Substr(Prm_Sourcestr, n_Posb, Length(Prm_Sourcestr) - n_Posb + 1);
EXIT;
END IF;

Prm_Arrary(n_Count) := Rtrim(Ltrim(Substr(Prm_Sourcestr, n_Posb, n_Pose - n_Posb)));
n_Posb := n_Pose + Length(Prm_Separator);

END LOOP;

RETURN n_Count;

END;

/*
调用相应的业务过程
如果有错误会写任务列表的错误字段
*/
PROCEDURE PRC_业务过程(PRM_RID IN ROWID,
prm_cursor IN INTEGER) IS

REC_SC01 SC01%ROWTYPE;
STRINGARRAY TYPE_TABLE_STRING;
n_count PLS_INTEGER;
v_rows_processed BINARY_INTEGER;
APPCODE VARCHAR2(3);
ERRMSG VARCHAR2(500);
v_space INTEGER;
BEGIN
APPCODE := PKG_A_MACRO.DEF_OK;
ERRMSG := NULL;
--获取任务参数
SELECT * INTO REC_SC01 FROM SC01 WHERE ROWID = PRM_RID;

n_count:=FUN_字符解析(REC_SC01.Parms,'|',STRINGARRAY);

FOR i IN 1..n_count-2 LOOP
DBMS_SQL.BIND_VARIABLE(prm_cursor, ':'||i,nvl(STRINGARRAY(i),'') );
END LOOP;
DBMS_SQL.BIND_VARIABLE(prm_cursor, ':'||(n_count-1),v_space);
DBMS_SQL.BIND_VARIABLE(prm_cursor, ':'||n_count,v_space);

v_rows_processed := DBMS_SQL.EXECUTE(prm_cursor);
dbms_sql.variable_value(c =>prm_cursor,name => ':'||to_char(n_count-1),value => APPCODE);
dbms_sql.variable_value(c =>prm_cursor,name => ':'||to_char(n_count),value => ERRMSG);

--捕获业务过程产生异常
IF APPCODE <> PKG_A_MACRO.DEF_OK THEN
UPDATE SC01
SET STATUS = PKG_A_MACRO.DEF_ERR, ERROR_MSG = ERRMSG
WHERE ROWID = PRM_RID;
END IF;
--ORA-21000
EXCEPTION
WHEN OTHERS THEN
ERRMSG:=SQLERRM;
UPDATE SC01
SET STATUS = PKG_A_MACRO.DEF_ERR, ERROR_MSG = ERRMSG
WHERE ROWID = PRM_RID;
END;

/*
任务读取主要工作是为每一个服务进程读取任务列表。
使用了ORACLE自治事务，主要是为了屏蔽ORACLE读一致性
*/
PROCEDURE PRC_任务读取(prm_threadname IN VARCHAR2,prm_operid IN VARCHAR2, RID OUT ROWID) IS
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
RID := NULL;
UPDATE SC01
SET STATUS = '1', THREAD_NAME = prm_threadname
WHERE STATUS = '0'
AND operid = prm_operid
AND ROWNUM < 2
RETURNING ROWID INTO RID;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END;

/*
服务进程主要由ORACLE负责管理
该过程做要功能如下：
1、读取任务列表ROWID
2、调用相应的业务存储过程
*/
PROCEDURE PRC_服务进程(PRM_THREADNAME IN VARCHAR2,
prm_program IN VARCHAR2,
prm_parmcount IN VARCHAR2,
prm_operid IN VARCHAR2) IS

RID ROWID;
v_cur_hdl INTEGER;
dym_sql varchar2(4000);
n_count NUMBER(3);
BEGIN

n_count := to_number(prm_parmcount);

IF v_cur_hdl IS NULL THEN
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
dym_sql :='BEGIN '||prm_program||'(';
FOR i IN 1..n_count LOOP
IF i = n_count THEN
dym_sql:=dym_sql||':'||i;
ELSE
dym_sql:=dym_sql||':'||i||',';
END IF;
END LOOP;
dym_sql:=dym_sql||'); END;';
DBMS_SQL.PARSE(v_cur_hdl, dym_sql, DBMS_SQL.NATIVE);
END IF;

LOOP
PRC_任务读取(PRM_THREADNAME,prm_operid,RID);
EXIT WHEN RID IS NULL;

--业务过程
PRC_业务过程(RID,v_cur_hdl);

--每次任务提交一次
COMMIT;

END LOOP;
IF v_cur_hdl IS NOT NULL THEN
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END IF;
--提交最后的任务
COMMIT;

EXCEPTION
WHEN OTHERS THEN
IF v_cur_hdl IS NOT NULL THEN
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END IF;
ROLLBACK;
raise_application_error(SQLCODE,'服务进程执行异常:'||Sqlerrm);
END;


--本过程用于成服务进程
--输入参数描述 程序全名，程序所对应的参数个数
--传入对应业务或模块名称

PROCEDURE PRC_进程控制(prm_program IN VARCHAR2,
prm_parmcount IN VARCHAR2,
prm_operid IN varchar2,
PRM_APPCODE OUT NUMBER,
PRM_ERRMSG OUT VARCHAR2) IS
N_THREADS PLS_INTEGER;
N_EXISTS PLS_INTEGER;
BEGIN
PRM_APPCODE := PKG_A_MACRO.DEF_OK;

--SELECT VALUE INTO N_THREADS FROM V$PARAMETER WHERE NAME = 'cpu_count';
N_THREADS:=4;
--控制系统级别重复调用
SELECT COUNT(*)
INTO N_EXISTS
FROM USER_SCHEDULER_JOBS S
WHERE S.STATE = 'RUNNING'
AND S.JOB_ACTION = 'PKG_多线程服务.PRC_服务进程'||prm_operid;

IF N_EXISTS > 0 THEN
PRM_APPCODE := PKG_A_MACRO.DEF_ERR;
PRM_ERRMSG := '多线程程序正在运行,请稍后再试';
RETURN;
END IF;

--产生多个进程调用
FOR X IN 0 .. N_THREADS - 1 LOOP
--清除日志信息
DBMS_SCHEDULER.PURGE_LOG(JOB_NAME => 'thread_' || X);
--产生新的日志信息
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'thread_' || X,
NUMBER_OF_ARGUMENTS => 3,
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'PKG_多线程服务.PRC_服务进程',
ENABLED => FALSE,
AUTO_DROP => TRUE,
COMMENTS => 'PKG_多线程服务'||prm_operid);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME => 'thread_' || X,
ARGUMENT_POSITION => 1,
ARGUMENT_VALUE => 'thread_' || X);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME => 'thread_' || X,
ARGUMENT_POSITION => 2,
ARGUMENT_VALUE => prm_program);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME => 'thread_' || X,
ARGUMENT_POSITION => 3,
ARGUMENT_VALUE => prm_parmcount);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME => 'thread_' || X,
ARGUMENT_POSITION => 4,
ARGUMENT_VALUE => prm_operid);

DBMS_SCHEDULER.ENABLE(NAME => 'thread_' || X);
END LOOP;

EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PKG_A_MACRO.DEF_ERR;
PRM_ERRMSG := 'PRC_进程控制调用失败:' || SQLERRM;
END;

--强行终止进程
PROCEDURE PRC_进程停止 IS
BEGIN
FOR j IN (SELECT s.job_name
FROM USER_SCHEDULER_JOBS S
WHERE S.STATE = 'RUNNING'
AND S.JOB_ACTION = 'PKG_多线程服务.PRC_服务进程') LOOP
dbms_scheduler.stop_job(job_name => j.job_name,force => FALSE);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(SQLCODE,'PRC_进程停止失败:'||Sqlerrm);
END;

END PKG_多线程服务;
/

 

---基础数据

 

--conn sys
grant manage scheduler to AHSIMIS;
grant create any job to AHSIMIS;

--conn ahsimis
-- Create table
create table SC01
(
BUS_NAME VARCHAR2(40),
OPERID VARCHAR2(18),
PARMS VARCHAR2(400) not null,
THREAD_NAME VARCHAR2(30),
STATUS VARCHAR2(3) not null,
ERROR_MSG VARCHAR2(1000)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column SC01.BUS_NAME
is '业务名称';
comment on column SC01.OPERID
is '操作流水号(用于区分同一个业务的多统筹区)';
comment on column SC01.PARMS
is '参数使用|分割';
comment on column SC01.THREAD_NAME
is '线程名称';
comment on column SC01.STATUS
is '状态(1成功|0未执行|-1错误)';
comment on column SC01.ERROR_MSG
is '错误信息';

 

CREATE OR REPLACE VIEW SC02 AS
SELECT S.JOB_CREATOR 现场创建用户,
S.JOB_NAME 线程名称,
S.JOB_ACTION 过程名称,
S.last_start_date 启动时间,
S.COMMENTS 备注,
(SELECT COUNT(*)
FROM SC01
WHERE THREAD_NAME = lower(S.JOB_NAME)
AND STATUS = '0') 未处理数,
(SELECT COUNT(*)
FROM SC01
WHERE THREAD_NAME = lower(S.JOB_NAME)
AND STATUS = '1') 运行成功数,
(SELECT COUNT(*)
FROM SC01
WHERE THREAD_NAME = lower(S.JOB_NAME)
AND STATUS = '-1') 运行失败数
FROM USER_SCHEDULER_JOBS S
ORDER BY S.JOB_NAME;

CREATE OR REPLACE VIEW SC03 AS
SELECT S.OWNER 用户名,
S.JOB_NAME 线程名称,
S.STATUS 线程状态,
S.LOG_DATE 日志时间,
S.ACTUAL_START_DATE 线程实际运行时间,
S.RUN_DURATION 线程持续运行时间,
S.SESSION_ID 会话ID,
S.SLAVE_PID 进程ID,
S.ADDITIONAL_INFO 线程备注信息,
(SELECT COUNT(*) FROM Sc01 WHERE thread_name = lower(S.JOB_NAME) AND status = '0') 未处理数,
(SELECT COUNT(*) FROM Sc01 WHERE thread_name = lower(S.JOB_NAME) AND status = '1') 运行成功数,
(SELECT COUNT(*) FROM Sc01 WHERE thread_name = lower(S.JOB_NAME) AND status = '-1')运行失败数
FROM USER_SCHEDULER_JOB_RUN_DETAILS S
ORDER BY S.JOB_NAME;