我在程序里面调用oracle存储过程,报错:ORA-01023 未找到游标上下文,但存储过程里面游标确实已经打开。不知道怎么回事。
存储过程如下:
create or replace procedure pr_CA_GetCardNoList
(
p_key number,
p_SchoolID number,
p_Address number,
p_Cursor out pk_Public.Cur
)
as
begin
case
when p_key = 0 then
execute immediate 'truncate table T_CA_TempRedList';
insert into T_CA_TempRedList(trCardNo, trInternalID)
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardChild a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardChild group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and not (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null)))
union all
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardTeacher a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardTeacher group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and not (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null))); execute immediate 'truncate table T_CA_TempBlackList';
insert into T_CA_TempBlackList(tbCardNo, tbInternalID)
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardChild a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardChild group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null)))
union all
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardTeacher a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardTeacher group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null))); delete from T_CA_RedList where rlSchoolID = p_SchoolID and rlAddress = p_Address and rlCardNo not in
(select trCardNo from T_CA_TempRedList);
delete from T_CA_BlackList where blSchoolID = p_SchoolID and blAddress = p_Address and blCardNo not in
(select tbCardNo from T_CA_TempBlackList); open p_Cursor for
select tbInternalID as InternalID, tbCardNo as CardNo, 0 as BlackRed from T_CA_TempBlackList
where tbCardNo not in (select blCardNo from T_CA_BlackList where blSchoolID = p_SchoolID and blAddress = p_Address)
union all
select trInternalID as InternalID, trCardNo as CardNo, 1 as BlackRed from T_CA_TempRedList
where trCardNo not in (select rlCardNo from T_CA_RedList where rlSchoolID = p_SchoolID and rlAddress = p_Address)
order by BlackRed, InternalID;
return;
end case;
commit;
exception
when others then
rollback;
end pr_CA_GetCardNoList;
存储过程如下:
create or replace procedure pr_CA_GetCardNoList
(
p_key number,
p_SchoolID number,
p_Address number,
p_Cursor out pk_Public.Cur
)
as
begin
case
when p_key = 0 then
execute immediate 'truncate table T_CA_TempRedList';
insert into T_CA_TempRedList(trCardNo, trInternalID)
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardChild a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardChild group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and not (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null)))
union all
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardTeacher a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardTeacher group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and not (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null))); execute immediate 'truncate table T_CA_TempBlackList';
insert into T_CA_TempBlackList(tbCardNo, tbInternalID)
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardChild a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardChild group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null)))
union all
select distinct a.icCardNo, a.icInternalID from T_CA_IssueCardTeacher a,
(select icCardNo, max(icTime) as MaxTime from T_CA_IssueCardTeacher group by icCardNo) b
where a.icSchoolID = p_SchoolID and a.icIsNew = 0 and a.icCardNo = b.icCardNo and a.icTime = b.MaxTime
and (a.icTypeID in (3, 4) or (a.icTypeID = 2 and (a.icRepTypeID != 5 or a.icRepTypeID is null))); delete from T_CA_RedList where rlSchoolID = p_SchoolID and rlAddress = p_Address and rlCardNo not in
(select trCardNo from T_CA_TempRedList);
delete from T_CA_BlackList where blSchoolID = p_SchoolID and blAddress = p_Address and blCardNo not in
(select tbCardNo from T_CA_TempBlackList); open p_Cursor for
select tbInternalID as InternalID, tbCardNo as CardNo, 0 as BlackRed from T_CA_TempBlackList
where tbCardNo not in (select blCardNo from T_CA_BlackList where blSchoolID = p_SchoolID and blAddress = p_Address)
union all
select trInternalID as InternalID, trCardNo as CardNo, 1 as BlackRed from T_CA_TempRedList
where trCardNo not in (select rlCardNo from T_CA_RedList where rlSchoolID = p_SchoolID and rlAddress = p_Address)
order by BlackRed, InternalID;
return;
end case;
commit;
exception
when others then
rollback;
end pr_CA_GetCardNoList;
还有,为什么我在plsql里面调试成功呢?
错误提示:资源正忙,但指定以NOWAIT方式获取资源
定位在line24,
execute immediate 'truncate table T_CA_TempBlackList'; 这行。
晕掉了,该怎么改啊?