select t.requestid, t.nodeid, t.operatedate from WORKFLOW_REQUESTLOG t where t.requestid in(select aa.requestid from WORKFLOW_REQUESTBASE aa where workflowid = 59 ) and t.nodeid in (279, 278);用这语句查下结果,是不是因为数据本身就是存在这样的记录。
cursor ccv is select t.nodeid, t.operatedate from WORKFLOW_REQUESTLOG t where t.requestid = requestid and t.nodeid in (279, 278) and rownum <= 1; 这个游标要传参数 cursor ccv(requestid in integer) is select t.nodeid, t.operatedate from WORKFLOW_REQUESTLOG t where t.requestid = requestid and t.nodeid in (279, 278) and rownum <= 1;下面循环用得时候 for v_ii in ccv(requestid) loop...end loop;
这个第二个游标才能根据第一游标查到的requestid 值,去检索日志信息。
问题出现在内层光标的定义上,cursor ccv is select t.nodeid, t.operatedate from WORKFLOW_REQUESTLOG t where t.requestid = requestid and t.nodeid in (279, 278) and rownum <= 1; 你这里的requestid是死的。需要使用带参光标定义。
内层光标的定义如下:CURSOR ccv(pRequestid WORKFLOW_REQUESTLOG.Requestid%TYPE) IS select t.nodeid, t.operatedate from WORKFLOW_REQUESTLOG t where t.requestid = pRequestid and t.nodeid in (279, 278) and rownum <= 1; 在使用时要为带参光标指定实参,参考代码如下:for v_ii in ccv(v_i.requestid) loop ....其它代码不变。
建议可以把上述PL/SQL块做成存储过程,然后在PL/SQL加断点调试一下,就知道程序是如何执行的了。这种逻辑错误很难找。必须调试。
(
requestid INTEGER,
workflowid INTEGER,
nodeid INTEGER,
logtype CHAR(1),
operatedate CHAR(10),
operatetime CHAR(8),
operator INTEGER,
re1 VARCHAR2(4000),
clientip CHAR(15),
operatortype INTEGER default 0,
destnodeid INTEGER default 0,
receivedpersons VARCHAR2(4000),
showorder INTEGER,
agentorbyagentid INTEGER,
agenttype CHAR(1),
logid INTEGER,
re LONG,
annexdocids VARCHAR2(2000),
requestlogid INTEGER default 0 not null,
operatordept INTEGER
)
create table WORKFLOW_REQUESTBASE
(
requestid INTEGER not null,
workflowid INTEGER,
lastnodeid INTEGER,
lastnodetype CHAR(1),
currentnodeid INTEGER,
currentnodetype CHAR(1),
status VARCHAR2(60),
passedgroups INTEGER,
totalgroups INTEGER,
requestname VARCHAR2(400),
creater INTEGER,
createdate CHAR(10),
createtime CHAR(8),
lastoperator INTEGER,
lastoperatedate CHAR(10),
lastoperatetime CHAR(8),
deleted INTEGER default 0,
creatertype INTEGER default 0,
lastoperatortype INTEGER default 0,
nodepasstime FLOAT default -1,
nodelefttime FLOAT default -1,
docids VARCHAR2(4000),
crmids VARCHAR2(4000),
hrmids VARCHAR2(4000),
prjids VARCHAR2(4000),
cptids VARCHAR2(4000),
requestlevel INTEGER default 0,
request VARCHAR2(100),
messagetype INTEGER,
mainrequestid INTEGER
)
from WORKFLOW_REQUESTLOG t
where t.requestid in(select aa.requestid from WORKFLOW_REQUESTBASE aa where workflowid = 59 ) and t.nodeid in (279, 278);用这语句查下结果,是不是因为数据本身就是存在这样的记录。
select t.nodeid, t.operatedate
from WORKFLOW_REQUESTLOG t
where t.requestid = requestid
and t.nodeid in (279, 278)
and rownum <= 1;
这个游标要传参数
cursor ccv(requestid in integer) is
select t.nodeid, t.operatedate
from WORKFLOW_REQUESTLOG t
where t.requestid = requestid
and t.nodeid in (279, 278)
and rownum <= 1;下面循环用得时候
for v_ii in ccv(requestid) loop...end loop;
select t.nodeid, t.operatedate
from WORKFLOW_REQUESTLOG t
where t.requestid = requestid
and t.nodeid in (279, 278)
and rownum <= 1;
你这里的requestid是死的。需要使用带参光标定义。
IS
select t.nodeid, t.operatedate
from WORKFLOW_REQUESTLOG t
where t.requestid = pRequestid
and t.nodeid in (279, 278)
and rownum <= 1;
在使用时要为带参光标指定实参,参考代码如下:for v_ii in ccv(v_i.requestid) loop
....其它代码不变。