CREATE OR REPLACE PROCEDURE BuildingCheckCertDOC AS
Cursor BuildingCheckCert_Cursor is
SELECT TM, gcmc FROM YDGH.BJ_GHYS_CPB where id >= 1 and id <= 10;
--检索bj_ghys_cpb的数据 完结的件是ZT= 18
RefBuildingCheckCert BuildingCheckCert_Cursor%rowtype;
------------------------------------------------------------------------------------------
Cursor sfj_cursor is --旧系统的统一收发件
SELECT jjsj
FROM YDGH.SFJ_ZXJJ
where TM >= 'BJ409-0000001-01' and tm <= 'BJ409-0000010-01';
Refsfj sfj_cursor%rowtype;
-----------------------------------------------------------------------------------------------------------------
newID number; --当从旧表导入到新表时,新表的docid要自动产生
tempdate date;
date1 date; --收件时间
BEGIN
BuildingCheckCertDOC;
OPEN BuildingCheckCert_Cursor; --------------------------------------------------------------------------------------------------
if RefSFJ.jjsj is null then
--收件时间
date1 := To_Date('0001-01-01', 'yyyy-mm-dd');
else
date1 := RefSFJ.jjsj;
end if;
select document.docid.nextval into newID from dual; --把查询出来的SQL值赋给newID变量
loop
<<continue_label>>
fetch BuildingCheckCert_Cursor
into RefBuildingCheckCert;
exit when BuildingCheckCert_Cursor%notfound;
insert into document.doc
(tm,
id, --条码
name, --件名称
bid, --批ID
town, --镇
district, --管理区
receptdate, --收件日期
senderid, --件的发出人ID
userid, --件的当前持有办理人
groupid, --件的当前持有办理组
section, --主办科室
catalog, --业务类别
busyid, --业务
flowid) --流程岗位
values
(RefBuildingCheckCert.TM,
newID,
RefBuildingCheckCert.gcmc,
0,
1,
0,
Refsfj.jjsj,
0,
-1,
-1,
-1,
-1,
530,
-1); -- 插入document.doc NOT NULL数据
-------------------------------------------------------------------------------------------------------------------------
CLOSE BuildingCheckCert_Cursor;
END LOOP;
END BuildingCheckCertDOC;
/
在exec 的时候 总是报出oracle 内部错误!
请帮我看看那里有问题!谢谢!
Cursor BuildingCheckCert_Cursor is
SELECT TM, gcmc FROM YDGH.BJ_GHYS_CPB where id >= 1 and id <= 10;
--检索bj_ghys_cpb的数据 完结的件是ZT= 18
RefBuildingCheckCert BuildingCheckCert_Cursor%rowtype;
------------------------------------------------------------------------------------------
Cursor sfj_cursor is --旧系统的统一收发件
SELECT jjsj
FROM YDGH.SFJ_ZXJJ
where TM >= 'BJ409-0000001-01' and tm <= 'BJ409-0000010-01';
Refsfj sfj_cursor%rowtype;
-----------------------------------------------------------------------------------------------------------------
newID number; --当从旧表导入到新表时,新表的docid要自动产生
tempdate date;
date1 date; --收件时间
BEGIN
BuildingCheckCertDOC;
OPEN BuildingCheckCert_Cursor; --------------------------------------------------------------------------------------------------
if RefSFJ.jjsj is null then
--收件时间
date1 := To_Date('0001-01-01', 'yyyy-mm-dd');
else
date1 := RefSFJ.jjsj;
end if;
select document.docid.nextval into newID from dual; --把查询出来的SQL值赋给newID变量
loop
<<continue_label>>
fetch BuildingCheckCert_Cursor
into RefBuildingCheckCert;
exit when BuildingCheckCert_Cursor%notfound;
insert into document.doc
(tm,
id, --条码
name, --件名称
bid, --批ID
town, --镇
district, --管理区
receptdate, --收件日期
senderid, --件的发出人ID
userid, --件的当前持有办理人
groupid, --件的当前持有办理组
section, --主办科室
catalog, --业务类别
busyid, --业务
flowid) --流程岗位
values
(RefBuildingCheckCert.TM,
newID,
RefBuildingCheckCert.gcmc,
0,
1,
0,
Refsfj.jjsj,
0,
-1,
-1,
-1,
-1,
530,
-1); -- 插入document.doc NOT NULL数据
-------------------------------------------------------------------------------------------------------------------------
CLOSE BuildingCheckCert_Cursor;
END LOOP;
END BuildingCheckCertDOC;
/
在exec 的时候 总是报出oracle 内部错误!
请帮我看看那里有问题!谢谢!
是内部错误
如:
exception When others then
dbms_output.put_line(SubStr(SQLERRM,1,100))可以看看具体的错误信息。记得写存储过程一定要有异常处理。
1、Cursor sfj_cursor 没有打开分配就使用,使用完了也没有关闭
2、RefSFJ.jjsj 返回多行还是单行
3、当你在循环中CLOSE BuildingCheckCert_Cursor;后还怎么分配游标