用存储过程构造数据窗口时出错
CREATE OR REPLACE PROCEDURE "P_SB_GOOD_RATE" (
start_month IN CHAR DEFAULT NULL,
end_month IN CHAR DEFAULT NULL,
RC1 OUT ty_tbc_globalpkg.globalPkg.RCT1)
AS
start_month_ CHAR(6) := start_month;
end_month_ CHAR(6) := end_month;
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_crowcnt INTEGER := 0;
StoO_fetchstatus INTEGER := 0;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
year_end NUMBER(10,0);
month_end NUMBER(10,0);
month_start NUMBER(10,0);
month VARCHAR2(20);
dept_nm VARCHAR2(50);
dept_nm1 VARCHAR2(50);
li_cnt_good NUMBER(10,0);
li_cnt_good1 NUMBER(10,0);
li_cnt_total NUMBER(10,0);
li_cnt_bad NUMBER(10,0);
ld_dept_id NUMBER(18,0);
ld_rate NUMBER(12,4);
ld_rate_chk NUMBER(12,4);
i NUMBER(10,0);
CURSOR c1 IS
SELECT DISTINCT sb_good_cond_rec_s.mon, sb_equi_acc.act_dept_id,
rl_dept_cd.sys_cd_nm
FROM rl_dept_cd, sb_equi_acc, sb_good_cond_rec_s
WHERE
(sb_equi_acc.id_key = sb_good_cond_rec_s.equi_id)
and
(rl_dept_cd.sys_id_key = sb_equi_acc.act_dept_id)
and ROUND( TO_NUMBER(SUBSTR(sb_good_cond_rec_s.mon, 1, 4)),0)
= year_end
ORDER BY rl_dept_cd.sys_ord_cd ;
BEGIN
BEGIN
year_end := ROUND( TO_NUMBER(SUBSTR(end_month_,1, 4)),0);
month_end := ROUND( TO_NUMBER(SUBSTR(end_month_,LENGTH(end_month_) - 1,2)),0);
month_start := ROUND( TO_NUMBER(SUBSTR(start_month_,LENGTH(start_month_) - 1, 2)),0);
i := 0;
DELETE FROM TT_TEMP_GEN_1;
NULL;/*DECLARE CURSOR c1 */
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
StoO_crowcnt := 0;
open c1;
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
StoO_crowcnt := 0;
fetch c1 into
month, ld_dept_id, dept_nm;
EXCEPTION
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
--raise_application_error(SQLCODE, SQLERRM,true);
END;
--IF c1%NOTFOUND THEN
-- StoO_sqlstatus := 2;
-- StoO_fetchstatus := -1;
--ELSE
-- StoO_sqlstatus := 0;
-- StoO_fetchstatus := 0;
--END IF;
dept_nm1 := dept_nm;
i := i + 1;
dept_nm := RPAD(i, 2, ' ')||' ' || dept_nm;
IF ( SQL%NOTFOUND) THEN
BEGIN
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
close c1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
--raise_application_error(SQLCODE, SQLERRM,true);
END;
OPEN RC1 FOR
SELECT TT_TEMP_GEN_1.chk_month
, TT_TEMP_GEN_1.dept_id
, TT_TEMP_GEN_1.dept_nm
, TT_TEMP_GEN_1.good_rate
FROM TT_TEMP_GEN_1;
RETURN ;
END;
END IF;
<<i_loop1>>
WHILE ( SQL%FOUND) LOOP
BEGIN
BEGIN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := 0;
SELECT count(*)
INTO li_cnt_total
FROM sb_equi_acc
WHERE sb_equi_acc.abc_tp IN ('A', 'B')
AND sb_equi_acc.act_dept_id = ld_dept_id;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
StoO_rowcnt := 2;
WHEN NO_DATA_FOUND THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
BEGIN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := 0;
SELECT count(*)
INTO li_cnt_bad
FROM sb_good_cond_rec_s, sb_equi_acc
WHERE
(sb_equi_acc.id_key = sb_good_cond_rec_s.equi_id)
AND sb_good_cond_rec_s.mark < 85
AND sb_good_cond_rec_s.chk_tp = '1'
AND sb_good_cond_rec_s.mon = month
AND sb_equi_acc.act_dept_id = ld_dept_id;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
StoO_rowcnt := 2;
WHEN NO_DATA_FOUND THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
li_cnt_good := li_cnt_total
- li_cnt_bad;
ld_rate := ROUND( TO_NUMBER(( ROUND( TO_NUMBER(li_cnt_good),4)
/ ROUND( TO_NUMBER(li_cnt_total),4) )),4);
BEGIN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := 0;
SELECT count(*)
INTO li_cnt_bad
FROM sb_good_cond_rec_s, sb_equi_acc
WHERE
(sb_equi_acc.id_key = sb_good_cond_rec_s.equi_id)
AND sb_good_cond_rec_s.mark < 85
AND sb_good_cond_rec_s.chk_tp = '2'
AND sb_good_cond_rec_s.mon = month
AND sb_equi_acc.act_dept_id = ld_dept_id;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
StoO_rowcnt := 2;
WHEN NO_DATA_FOUND THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
li_cnt_good1 := li_cnt_good
- li_cnt_bad;
ld_rate_chk := ROUND( TO_NUMBER(( ROUND( TO_NUMBER(li_cnt_good1),4)
/ ROUND( TO_NUMBER(li_cnt_good),4) )),4);
ld_rate := ld_rate * ld_rate_chk
* 100;
month_start := ROUND( TO_NUMBER(SUBSTR(month,
LENGTH(month) - 2 + 1, 2)),0);
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
INSERT INTO TT_TEMP_GEN_1
VALUES (month_start, ld_dept_id,
dept_nm, ld_rate);
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
StoO_crowcnt := 0;
fetch c1 into
month, ld_dept_id, dept_nm;
EXCEPTION
WHEN OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
IF c1%NOTFOUND THEN
StoO_sqlstatus := 2;
StoO_fetchstatus := -1;
ELSE
StoO_sqlstatus := 0;
StoO_fetchstatus := 0;
END IF;
IF dept_nm = dept_nm1 THEN
dept_nm := RPAD(i, 2, ' ')
+ ' ' || dept_nm;
ELSE
BEGIN
i := i + 1;
dept_nm1 := dept_nm;
dept_nm := RPAD(i, 2, ' ')
+ ' ' || dept_nm;
END;
END IF;
END;
END LOOP;
BEGIN
StoO_error := 0;
StoO_rowcnt := 0;
close c1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
raise_application_error(SQLCODE, SQLERRM,true);
END;
OPEN RC1 FOR
SELECT TT_TEMP_GEN_1.chk_month
, TT_TEMP_GEN_1.dept_id
, TT_TEMP_GEN_1.dept_nm
, TT_TEMP_GEN_1.good_rate
FROM TT_TEMP_GEN_1;
END;
END P_SB_GOOD_RATE;
我用的是oracle数据库，用上面这个存储过程构造数据窗口时出错，请各位指导一下，谢谢
问题点数：0、回复次数：10Top
1 楼aty283（白冰）回复于 2003-09-01 12:26:28 得分 0
upTop
2 楼jerrymao（加菲猫－－抵制倭寇和高丽棒子）回复于 2003-09-01 12:42:45 得分 0
看都看累了，你就不能用一个视图啊，或者用这个过程创建一个表。Top
3 楼bbybrandy（观云）回复于 2003-09-01 12:58:45 得分 0
用 pl/sql developer 打开，就一目了然了，本身没什么错误
就是用它来构建数据窗口的时候出错
谢谢upTop
4 楼bbybrandy（观云）回复于 2003-09-01 15:51:06 得分 0
up有分
Top
5 楼jerrymao（加菲猫－－抵制倭寇和高丽棒子）回复于 2003-09-01 15:58:49 得分 0
你的最后一句话，OPEN RC1 FOR
SELECT TT_TEMP_GEN_1.chk_month
, TT_TEMP_GEN_1.dept_id
, TT_TEMP_GEN_1.dept_nm
, TT_TEMP_GEN_1.good_rate
FROM TT_TEMP_GEN_1;
我怀疑有问题，你唯一调用TT_TEMP_GEN_1就是在两个end的时候Top
6 楼bbybrandy（观云）回复于 2003-09-01 16:10:11 得分 0
这有个很简单的存储过程，用它来做数据源也不行：
CREATE OR REPLACE PROCEDURE p_equ_workrate (
start_dt in date DEFAULT NULL,
end_dt in date DEFAULT NULL,
dept_id in number DEFAULT NULL,
days in number DEFAULT NULL,
hours in number DEFAULT NULL,
RC1 OUT ty_tbc_globalpkg.globalPkg.RCT1)
as
BEGIN
DELETE TT_TEMP_EQU_WORKRATE;
insert INTO tt_temp_equ_workrate(dept_id,dept_nm ,equi_id,pos,output,jldw_id,work_time)
select DISTINCT a.dept_id,
c.sys_cd_nm,
a.equi_id,
b.equi_pos_no,
sum(a.output),
max(a.jldw_id),
sum(a.run_tm1 + a.stop_tm + a.adju_tm - a.eff_rate_tm)
from sb_run_rec_s a ,sb_equi_acc b ,sha.rl_dept_cd c
where a.dept_id = c.sys_id_key and
a.equi_id = b.id_key and
start_dt<=a.dt and
end_dt>=a.dt and
a.dept_id = dept_id
group by a.dept_id,a.equi_id,c.sys_cd_nm,b.equi_pos_no ;
DELETE FROM TT_TEMP_EQU_11;
INSERT INTO TT_TEMP_EQU_11
select a.equi_id,a.team_id
from sb_run_rec_s a
where start_dt<=a.dt and
end_dt>=a.dt and
a.dept_id = dept_id
group by a.equi_id,a.team_id
having count(a.team_id) >= days and sum(a.run_tm1 + a.adju_tm ) >= hours ;
/*??temp1???,??temp2??????????temp1???*/
delete from tt_temp_equ_workrate a
where a.equi_id not in (select distinct equi_id from TT_TEMP_EQU_11) ;
update tt_temp_equ_workrate b
set b.jldw_nm = (select a.sys_cd_nm
from sha.sb_jldw_cd a,tt_temp_equ_workrate
where a.sys_id_key = tt_temp_equ_workrate.jldw_id );
update tt_temp_equ_workrate b
set b.output_xs = (select NVL(a.coeff,0)
from sha.sb_jldw_cd a , tt_temp_equ_workrate b
where a.sys_id_key = b.jldw_id) ;
update tt_temp_equ_workrate d
set (d.design_capa,d.capa_xs) = (select nvl(b.desi_prod_capa,0),
nvl(c.coeff,0)
from sb_equi_acc a,sha.sb_model_cd b,sha.sb_jldw_cd c,tt_temp_equ_workrate d
where a.mdl_id = b.sys_id_key and
b.capa_unit_id = c.sys_id_key and
a.id_key = d.equi_id );
update tt_temp_equ_workrate
set workrate = to_number((output*output_xs)/(work_time*design_capa*capa_xs),'9,9999')
where work_time<>0 and design_capa<>0 and capa_xs<>0 ;
OPEN RC1 FOR
select dept_nm ,
pos ,
workrate,
output,
jldw_nm,
work_time
from tt_temp_equ_workrate;
END P_EQU_WORKRATE;
Top
7 楼jerrymao（加菲猫－－抵制倭寇和高丽棒子）回复于 2003-09-01 16:17:18 得分 0
SQL语句怎么看都没有问题啊，是不是你调用的包有点问题？Top
8 楼bbybrandy（观云）回复于 2003-09-01 17:19:26 得分 0
错误是：sqlstate=37000
[oracle][odbc]syntax error or access violationTop
9 楼jerrymao（加菲猫－－抵制倭寇和高丽棒子）回复于 2003-09-01 17:41:34 得分 0
sqlstate=37000的错误代码表示：sql语句中间语法错误，而且以操作符丢失为主。可是我还是找不出来那有操作性的错误Top
10 楼bbybrandy（观云）回复于 2003-09-02 09:55:01 得分 0
我把我调用的包都检查了一遍，都没什么问题，55555555555
难道我没救了？？？？？？？？Top
