由于系统数据量越来越大,所以将涉及查询大数据的业务逻辑 写在存储过程里,可以提高查询效率
create or replace package tomorrow as
type p_cursor is ref Cursor;end;
create or replace package pro_query_mywork is
---办事处我的工作查询工作流
procedure query_mywork_info(curpage in varchar2,
unitpage in varchar2,
operatorid in varchar2,
processid in varchar2,
tasktype in varchar2,
appliant in varchar2,
taskstatus in varchar2,
mercnum in varchar2,
mercnam in varchar2,
agentid in varchar2,
agtnam in varchar2,
area in varchar2,
appday1 in varchar2,
appday2 in varchar2,
in_roles in varchar2,
cdt_in_opers in varchar2,
cdt_not_in_opers in varchar2,
v_cursor out tomorrow.p_cursor);
procedure query_mywork_count(operatorid in varchar2,
processid in varchar2,
tasktype in varchar2,
appliant in varchar2,
taskstatus in varchar2,
mercnum in varchar2,
mercnam in varchar2,
agentid in varchar2,
agtnam in varchar2,
area in varchar2,
appday1 in varchar2,
appday2 in varchar2,
cdt_in_opers in varchar2,
cdt_not_in_opers in varchar2,
v_cursor out tomorrow.p_cursor);
end;
create or replace package body pro_query_mywork is
----办事处我的工作查询工作流
procedure query_mywork_info(curpage in varchar2,
unitpage in varchar2,
operatorid in varchar2,
processid in varchar2,
tasktype in varchar2,
appliant in varchar2,
taskstatus in varchar2,
mercnum in varchar2,
mercnam in varchar2,
agentid in varchar2,
agtnam in varchar2,
area in varchar2,
appday1 in varchar2,
appday2 in varchar2,
in_roles in varchar2,
cdt_in_opers in varchar2,
cdt_not_in_opers in varchar2,
v_cursor out tomorrow.p_cursor) as
startpos number;
endpos number;
all_sql varchar2(4000);
tj varchar2(300);
tja varchar2(300);
tjb varchar2(300);
begin
tj := '';
tja := '';
tjb := '';
--申请日期
if (appday1 is not null) then
tj := tj || 'AND SUBSTR(A.CREATETIME,1,8)>=' || appday1 || '';
end if;
if (appday2 is not null) then
tj := tj || 'AND SUBSTR(A.CREATETIME,1,8)<=' || appday2 || '';
end if;
--任务流水号
if (processid is not null) then
tj := tj || ' AND (A.PROCESSID =''' || processid ||
''' OR A.PROCESSID LIKE ' || '''' || '%' || processid || '%' || '''' || ')';
end if;
--任务类型
if ((tasktype is not null) and (tasktype <> '-1')) then
if (tasktype = 'WF014') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF018''' || ')';
elsif (tasktype = 'WF008') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF015''' || ')';
elsif (tasktype = 'WF012') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF016''' || ')';
elsif (tasktype = 'WF011') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF017''' || ')';
elsif (tasktype = 'WF020') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF020''' || ')';
elsif (tasktype = 'WF021') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF021''' || ')';
else
tj := tj || ' AND A.TASKTYPE =''' || tasktype || '''';
end if;
end if;
if ((tasktype is not null) and (tasktype = '-1')) then
tj := tj || ' AND A.TASKTYPE in' || '(' || '''WF020''' || ',' ||
'''WF008''' || ',' || '''WF021''' || ',' || '''WF009''' || ',' ||
'''WF010''' || ',' || '''WF011''' || ',' || '''WF014''' || ',' ||
'''WF012''' || ',' || '''WF005''' || ',' || '''WF018''' || ',' ||
'''WF017''' || ',' || '''WF016''' || ',' || '''WF015''' || ')';
end if;
--商户序号
if (mercnum is not null) then
tj := tj || ' AND (C.MERCNUM =''' || mercnum ||
''' OR C.MERCNUM LIKE ' || '''' || '%' || mercnum || '%' || '''' || ')';
end if;
--申请人
if (appliant is not null) then
tj := tj || ' AND (A.APPNAM =''' || appliant ||
''' OR A.APPNAM LIKE ' || '''' || '%' || appliant || '%' || '''' || ')';
end if;
--商户名称
if (mercnam is not null) then
tj := tj || ' AND (C.MERCNAM =''' || mercnam ||
''' OR C.MERCNAM LIKE ' || '''' || '%' || mercnam || '%' || '''' || ')';
end if;
--办事处编号
if (agentid is not null) then
tj := tj || ' AND (A.AGENTID =''' || agentid ||
''' OR A.AGENTID LIKE ' || '''' || '%' || agentid || '%' || '''' || ')';
end if;
--办事处名称
if (agtnam is not null) then
tj := tj || ' AND (B.AGTNAM =''' || agtnam || ''' OR B.AGTNAM LIKE ' || '''' || '%' ||
agtnam || '%' || '''' || ')';
end if;
--所属区域
if (area is not null) then
tj := tj || ' AND A.AREA =''' || area || '''';
end if;
--任务状态
--未处理
if ((taskstatus is not null) and (taskstatus = '0')) then
tja := tja || 'AND A.TASKSTATUS=0' || cdt_in_opers;
tjb := tjb || 'AND 1=2';
end if;
--已处理
if ((taskstatus is not null) and (taskstatus = '1')) then
tjb := tjb || 'AND A.TASKSTATUS=0' || cdt_not_in_opers ||
'AND A.PROCESSID IN (SELECT DISTINCT PROCESSID FROM PMWKFSTEPDEF WHERE TLRID =''' ||
operatorid || ''')';
tja := tja || 'AND 1=2';
end if;
--已完成
if ((taskstatus is not null) and (taskstatus = '2')) then
tjb := tjb || 'AND A.TASKSTATUS=1' ||
'AND A.PROCESSID IN (SELECT DISTINCT PROCESSID FROM PMWKFSTEPDEF WHERE TLRID =''' ||
operatorid || ''')';
tja := tja || 'AND 1=2';
end if;
--不限 查询所有状态的任务
if ((taskstatus is not null) and (taskstatus = '-1')) then
tja := tja || 'AND A.TASKSTATUS=0' || cdt_in_opers;
tjb := tjb || 'AND (A.TASKSTATUS=1' || 'OR (A.TASKSTATUS=0' ||
cdt_not_in_opers || '))' ||
'AND A.PROCESSID IN (SELECT DISTINCT PROCESSID FROM PMWKFSTEPDEF WHERE TLRID =''' ||
operatorid || ''')';
end if;
all_sql := 'SELECT
A.TASKTYPE,G.TASKNAME,A.MERATTR, C.MERCNAM,A.MERCHANTID,A.APPNAM,
A.PROCESSID,B.AGTNAM,A.AGENTID,SUBSTR(A.CREATETIME,1,8) CREATETIME,A.CURRENTSTEP,A.STEPID,
A.OPERATORID,C.MERCID,D.USER_NAM,D.USER_ID,E.ROLE_NAM,H.CITYNAM AREANAM,F.ROLE_NAM AS TLRROLENAME,A.TASKSTATUS,
0 AS TASKSTATE
FROM PMWKDLIST A
LEFT JOIN PMMERINF C ON A.MERCHANTID = C.MERCNUM
LEFT JOIN PMUSRINF D ON A.OPERATORID = D.USER_ID
LEFT JOIN PMAGTINF B ON A.AGENTID = B.AGENTID
LEFT JOIN PMCTYCOD H ON A.AREA=H.CITYID
LEFT JOIN PMROLEINF E ON A.OPERATORROLE = E.ROLE_ID
LEFT JOIN PMROLEINF F ON A.TLRROLE = F.ROLE_ID
LEFT JOIN PMWKFDEF G ON A.TASKTYPE = G.TASKTYPE
WHERE TRIM(A.DELETEFLAG) IS NULL
' || tj || '
' || tja || '
UNION
SELECT
A.TASKTYPE,G.TASKNAME,A.MERATTR, C.MERCNAM,A.MERCHANTID,A.APPNAM,
A.PROCESSID,B.AGTNAM,A.AGENTID,SUBSTR(A.CREATETIME,1,8) CREATETIME,A.CURRENTSTEP,A.STEPID,
A.OPERATORID,C.MERCID,D.USER_NAM,D.USER_ID,E.ROLE_NAM,H.CITYNAM AREANAM,F.ROLE_NAM AS TLRROLENAM,A.TASKSTATUS,
(CASE
WHEN A.TASKSTATUS = 1 THEN 2
ELSE 1 END
) AS TASKSTATE
FROM PMWKDLIST A
LEFT JOIN PMMERINF C ON A.MERCHANTID = C.MERCNUM
LEFT JOIN PMUSRINF D ON A.OPERATORID = D.USER_ID
LEFT JOIN PMAGTINF B ON A.AGENTID = B.AGENTID
LEFT JOIN PMCTYCOD H ON A.AREA=H.CITYID
LEFT JOIN PMROLEINF E ON A.OPERATORROLE = E.ROLE_ID
LEFT JOIN PMROLEINF F ON A.TLRROLE = F.ROLE_ID
LEFT JOIN PMWKFDEF G ON A.TASKTYPE = G.TASKTYPE
WHERE TRIM(A.DELETEFLAG) IS NULL
' || tj || '
' || tjb || '
ORDER BY TASKSTATE , PROCESSID DESC';
if ((curpage is not null) and (unitpage is not null)) then
startpos := (curpage - 1) * unitpage + 1;
endpos := curpage * unitpage;
end if;
all_sql := 'select * from (select B.*,rownum rid from (' || all_sql ||
')B where rownum<=' || endpos || ')A where A.rid>=' ||
startpos;
open v_cursor for all_sql;
dbms_output.put_line(all_sql);
Exception
when others then
dbms_output.put_line('数据库出现异常!');
raise;
end query_mywork_info;
--查询办事处我的工作个数
procedure query_mywork_count(operatorid in varchar2,
processid in varchar2,
tasktype in varchar2,
appliant in varchar2,
taskstatus in varchar2,
mercnum in varchar2,
mercnam in varchar2,
agentid in varchar2,
agtnam in varchar2,
area in varchar2,
appday1 in varchar2,
appday2 in varchar2,
cdt_in_opers in varchar2,
cdt_not_in_opers in varchar2,
v_cursor out tomorrow.p_cursor) as
count_sql varchar2(4000);
tj varchar2(300);
tja varchar2(300);
tjb varchar2(300);
begin
tj := '';
tja := '';
tjb := '';
--申请日期
if (appday1 is not null) then
tj := tj || 'AND SUBSTR(A.CREATETIME,1,8)>=' || appday1 || '';
end if;
if (appday2 is not null) then
tj := tj || 'AND SUBSTR(A.CREATETIME,1,8)<=' || appday2 || '';
end if;
--任务流水号
if (processid is not null) then
tj := tj || ' AND (A.PROCESSID =''' || processid ||
''' OR A.PROCESSID LIKE ' || '''' || '%' || processid || '%' || '''' || ')';
end if;
--任务类型
if ((tasktype is not null) and (tasktype <> '-1')) then
if (tasktype = 'WF014') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF018''' || ')';
elsif (tasktype = 'WF008') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF015''' || ')';
elsif (tasktype = 'WF012') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF016''' || ')';
elsif (tasktype = 'WF011') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF017''' || ')';
elsif (tasktype = 'WF020') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF020''' || ')';
elsif (tasktype = 'WF021') then
tj := tj || ' AND (A.TASKTYPE =''' || tasktype || '''' ||
' OR A.TASKTYPE = ' || '''WF021''' || ')';
else
tj := tj || ' AND A.TASKTYPE =''' || tasktype || '''';
end if;
end if;
if ((tasktype is not null) and (tasktype = '-1')) then
tj := tj || ' AND A.TASKTYPE in' || '(' || '''WF020''' || ',' ||
'''WF008''' || ',' || '''WF021''' || ',' || '''WF009''' || ',' ||
'''WF010''' || ',' || '''WF011''' || ',' || '''WF014''' || ',' ||
'''WF012''' || ',' || '''WF005''' || ',' || '''WF018''' || ',' ||
'''WF017''' || ',' || '''WF016''' || ',' || '''WF015''' || ')';
end if;
--商户序号
if (mercnum is not null) then
tj := tj || ' AND (C.MERCNUM =''' || mercnum ||
''' OR C.MERCNUM LIKE ' || '''' || '%' || mercnum || '%' || '''' || ')';
end if;
--申请人
if (appliant is not null) then
tj := tj || ' AND (A.APPNAM =''' || appliant ||
''' OR A.APPNAM LIKE ' || '''' || '%' || appliant || '%' || '''' || ')';
end if;
--商户名称
if (mercnam is not null) then
tj := tj || ' AND (C.MERCNAM =''' || mercnam ||
''' OR C.MERCNAM LIKE ' || '''' || '%' || mercnam || '%' || '''' || ')';
end if;
--办事处编号
if (agentid is not null) then
tj := tj || ' AND (A.AGENTID =''' || agentid ||
''' OR A.AGENTID LIKE ' || '''' || '%' || agentid || '%' || '''' || ')';
end if;
--办事处名称
if (agtnam is not null) then
tj := tj || ' AND (B.AGTNAM =''' || agtnam || ''' OR B.AGTNAM LIKE ' || '''' || '%' ||
agtnam || '%' || '''' || ')';
end if;
--所属区域
if (area is not null) then
tj := tj || ' AND A.AREA =''' || area || '''';
end if;
--任务状态
--未处理
if ((taskstatus is not null) and (taskstatus = '0')) then
tja := tja || 'AND A.TASKSTATUS=0' || cdt_in_opers;
tjb := tjb || 'AND 1=2';
end if;
--已处理
if ((taskstatus is not null) and (taskstatus = '1')) then
tjb := tjb || 'AND A.TASKSTATUS=0' || cdt_not_in_opers ||
'AND A.PROCESSID IN (SELECT DISTINCT PROCESSID FROM PMWKFSTEPDEF WHERE TLRID =''' ||
operatorid || ''')';
tja := tja || 'AND 1=2';
end if;
--已完成
if ((taskstatus is not null) and (taskstatus = '2')) then
tjb := tjb || 'AND A.TASKSTATUS=1' ||
'AND A.PROCESSID IN (SELECT DISTINCT PROCESSID FROM PMWKFSTEPDEF WHERE TLRID =''' ||
operatorid || ''')';
tja := tja || 'AND 1=2';
end if;
--不限 查询所有状态的任务
if ((taskstatus is not null) and (taskstatus = '-1')) then
tja := tja || 'AND A.TASKSTATUS=0' || cdt_in_opers;
tjb := tjb || 'AND (A.TASKSTATUS=1' || 'OR (A.TASKSTATUS=0' ||
cdt_not_in_opers || '))' ||
'AND A.PROCESSID IN (SELECT DISTINCT PROCESSID FROM PMWKFSTEPDEF WHERE TLRID =''' ||
operatorid || ''')';
end if;
count_sql := 'SELECT A.PROCESSID
FROM PMWKDLIST A
LEFT JOIN PMMERINF C ON A.MERCHANTID = C.MERCNUM
LEFT JOIN PMUSRINF D ON A.OPERATORID = D.USER_ID
LEFT JOIN PMAGTINF B ON A.AGENTID = B.AGENTID
LEFT JOIN PMCTYCOD H ON A.AREA=H.CITYID
LEFT JOIN PMROLEINF E ON A.OPERATORROLE = E.ROLE_ID
LEFT JOIN PMROLEINF F ON A.TLRROLE = F.ROLE_ID
LEFT JOIN PMWKFDEF G ON A.TASKTYPE = G.TASKTYPE
WHERE TRIM(A.DELETEFLAG) IS NULL
' || tj || '
' || tja || '
UNION
SELECT A.PROCESSID
FROM PMWKDLIST A
LEFT JOIN PMMERINF C ON A.MERCHANTID = C.MERCNUM
LEFT JOIN PMUSRINF D ON A.OPERATORID = D.USER_ID
LEFT JOIN PMAGTINF B ON A.AGENTID = B.AGENTID
LEFT JOIN PMCTYCOD H ON A.AREA=H.CITYID
LEFT JOIN PMROLEINF E ON A.OPERATORROLE = E.ROLE_ID
LEFT JOIN PMROLEINF F ON A.TLRROLE = F.ROLE_ID
LEFT JOIN PMWKFDEF G ON A.TASKTYPE = G.TASKTYPE
WHERE TRIM(A.DELETEFLAG) IS NULL
' || tj || '
' || tjb || '';
count_sql := 'SELECT COUNT(1) AS TOLCNT FROM (' || count_sql || ')';
open v_cursor for count_sql;
dbms_output.put_line(count_sql);
Exception
when others then
dbms_output.put_line('数据库出现异常!');
raise;
end query_mywork_count;
end;