create or replace procedure
report_create_tree(useriD varchar2) as
t_YH0000 VARCHAR2(20);
t_DM0000 VARCHAR2(8);
t_MC0000 VARCHAR2(50);
t_FJDM00 VARCHAR2(8);
CNT INT;
t_Error INT;
CURSOR C_1 IS
SELECT DISTINCT tt_TableGroup.TableNum FROM tt_Operator, tt_OperStatistics3,tt_STableDescript,tt_TableGroup
WHERE ( tt_Operator.OperGroup1 = tt_OperStatistics3.OperGroup1 ) anD
( tt_OperStatistics3.TableGroup = tt_TableGroup.TableGroup ) anD
( tt_TableGroup.TableNum = tt_STableDescript.STableNum ) anD
( ( tt_Operator.Operator = userid ) AND
( tt_TableGroup.TableNum like '%') )
ORDER BY tt_TableGroup.TableNum ASC;
t_DM0001 C_1%ROWTYPE;
BEGIN //ADDED BY HZY
Delete from TT_REPORT_TREE where YH0000=useriD;
OPEN C_1(useriD);
FETCH C_1 INTO t_DM0001;
WHILE C_1%FOUND LOOP
--BEGIN
SELECT DM0000,MC0000,FJDM00 INTO t_DM0000,t_MC0000,t_FJDM00 FROM TT_REPORT_DZTREE WHERE DM0001=t_DM0001;
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(t_DM0001.TableNum,t_DM0000,t_MC0000,t_FJDM00);
--FETCH C_1 INTO t_DM0001;
--END;
END LOOP;
CLOSE C_1;
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) values(useriD,'0','故障统计报表','');
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '01%';
IF CNT>0 THEN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'01','统计报表(一)','0');
END IF;
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '02%';
IF CNT>0 THEN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'02','统计报表(二)','0');
END IF;
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '03%';
IF CNT>0 THEN
BEGIN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'03','统计报表(三)','0');
END IF;
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '04%';
IF CNT>0 THEN
BEGIN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'04','统计报表(四)','0');
/* --Defined for Zhongshan
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0402','市话机房障碍统计日报表','04')
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0403','市话区局障碍统计月报表','04')
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0404','市话机房障碍统计月报表','04')
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0405','市话线路障碍统计月报表','04')
*/
END IF;
EXCEPTION
/*
WHEN OTHERS THEN
//t_Error:=SQLCODE;
GOTO ERROR0;
END
*/
IF SQLCODE<>0 THEN
GOTO ERROR0
END IF;
END; COMMIT;
GOTO END0;
<<ERROR0>>
ROLLBACK;
GOTO END0;
<<END0>>
RETURN;
END report_create_tree;
调试过程中有问题的话,把错误提示详细贴出来,
大家才好帮你。
report_create_tree(useriD varchar2) as
t_YH0000 VARCHAR2(20);
t_DM0000 VARCHAR2(8);
t_MC0000 VARCHAR2(50);
t_FJDM00 VARCHAR2(8);
CNT INT;
t_Error INT;
CURSOR C_1 IS
SELECT DISTINCT tt_TableGroup.TableNum FROM tt_Operator, tt_OperStatistics3,tt_STableDescript,tt_TableGroup
WHERE ( tt_Operator.OperGroup1 = tt_OperStatistics3.OperGroup1 ) anD
( tt_OperStatistics3.TableGroup = tt_TableGroup.TableGroup ) anD
( tt_TableGroup.TableNum = tt_STableDescript.STableNum ) anD
( ( tt_Operator.Operator = userid ) AND
( tt_TableGroup.TableNum like '%') )
ORDER BY tt_TableGroup.TableNum ASC;
t_DM0001 C_1%ROWTYPE;
BEGIN //ADDED BY HZY
Delete from TT_REPORT_TREE where YH0000=useriD;
OPEN C_1(useriD);
FETCH C_1 INTO t_DM0001;
WHILE C_1%FOUND LOOP
--BEGIN
SELECT DM0000,MC0000,FJDM00 INTO t_DM0000,t_MC0000,t_FJDM00 FROM TT_REPORT_DZTREE WHERE DM0001=t_DM0001;
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(t_DM0001.TableNum,t_DM0000,t_MC0000,t_FJDM00);
--FETCH C_1 INTO t_DM0001;
--END;
END LOOP;
CLOSE C_1;
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) values(useriD,'0','故障统计报表','');
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '01%';
IF CNT>0 THEN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'01','统计报表(一)','0');
END IF;
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '02%';
IF CNT>0 THEN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'02','统计报表(二)','0');
END IF;
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '03%';
IF CNT>0 THEN
BEGIN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'03','统计报表(三)','0');
END IF;
SELECT NVL(COUNT(*),0) INTO CNT FROM TT_REPORT_TREE WHERE YH0000=useriD AND DM0000 LIKE '04%';
IF CNT>0 THEN
BEGIN
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(useriD,'04','统计报表(四)','0');
/* --Defined for Zhongshan
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0402','市话机房障碍统计日报表','04')
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0403','市话区局障碍统计月报表','04')
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0404','市话机房障碍统计月报表','04')
insert into TT_REPORT_TREE(YH0000,DM0000,MC0000,FJDM00) VALUES(@useriD,'0405','市话线路障碍统计月报表','04')
*/
END IF;
EXCEPTION
/*
WHEN OTHERS THEN
//t_Error:=SQLCODE;
GOTO ERROR0;
END
*/
IF SQLCODE<>0 THEN
GOTO ERROR0
END IF;
END; COMMIT;
GOTO END0;
<<ERROR0>>
ROLLBACK;
GOTO END0;
<<END0>>
RETURN;
END report_create_tree;
调试过程中有问题的话,把错误提示详细贴出来,
大家才好帮你。
用你写的存储过程写上去,运行,还是报那个错,
我尝试过建另外一个很简单的存储过程,同样会报这个错误.
"警告: 创建程序时发生编译错误。"
sql>show error;
"缓冲区溢出,用SET命令减少ARRAYSIZE或增加MAXDATA.
没有任何错误."
或者再增大一点试试。