我的存储过程有什么问题？ 怎么也不成功，又看不到原因？盼
SET SCHEMA AAA ;
SET CURRENT PATH = "SYSIBM","SYSFUN","AAA";
CREATE PROCEDURE AAA.tempUpdateBarcode ( )
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
P1: BEGIN
declare tmpORGANIZATION_NAME varchar(50) ;
declare tmpOUT_DATE date ;
declare tmpPRODUCT_TYPE_NAME varchar(50);
declare tmpPRODUCT_NAME varchar(50);
declare tmpCONTRACT_NUMBER varchar(50);
declare tmpBARCODE varchar(50);
declare at_end int default 0;
declare not_found CONDITION FOR SQLSTATE '02000';
--定义一个产品游标
DECLARE c1 CURSOR FOR
select e_boardid
from AAA_epm.AAA_eq_rprboarditem ;
DECLARE CONTINUE HANDLER FOR not_found
select count(*) into at_end
from AAA_epm.AAA_eq_rprboarditem ;
open c1 ;
while at_end=0 do
fetch c1 into tmpBARCODE;
select out_date into tmpOUT_DATE from AAA.TMPBARFROM209 where barcodeid =tmpBARCODE ;
select ORGANIZENAME,
PRODUCT_TYPE_NAME , PRODUCT_NAME,
CONTRACT_NUM
into tmpORGANIZATION_NAME, tmpPRODUCT_TYPE_NAME, tmpPRODUCT_NAME, tmpCONTRACT_NUMBER
from AAA.TMPBARFROMERP
where BARCODEID = tmpBARCODE;
update AAA_epm.AAA_eq_rprboarditem
set e_OrganizationName = tmpORGANIZATION_NAME,e_OriginSendTime = tmpOUT_DATE,
E_Productmodel = tmpPRODUCT_TYPE_NAME,E_Productitem = tmpPRODUCT_NAME ,E_Contactid = tmpCONTRACT_NUMBER
where e_boardid = tmpBARCODE;
set at_end = at_end-1;
end while;
close c1;
END P1;
#SYNC 10;
问题点数：0、回复次数：1Top
1 楼jkfree（乌鸦大间谍）回复于 2003-12-09 21:54:06 得分 0
CREATE PROCEDURE AAA.tempUpdateBarcode ( out p_errmsg varchar(30))
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
P1: BEGIN
declare tmpORGANIZATION_NAME varchar(50) ;
declare tmpOUT_DATE date ;
declare tmpPRODUCT_TYPE_NAME varchar(50);
declare tmpPRODUCT_NAME varchar(50);
declare tmpCONTRACT_NUMBER varchar(50);
declare tmpBARCODE varchar(50);
declare at_end int default 0;
declare select_over char(1) default 'n';
--定义一个产品游标
DECLARE c1 CURSOR FOR
select e_boardid
from AAA_epm.AAA_eq_rprboarditem ;
DECLARE CONTINUE HANDLER FOR not_found
begin
set select_over='y';
end;
open c1 ;
while select_over='n' do
fetch c1 into tmpBARCODE;
select out_date into tmpOUT_DATE from AAA.TMPBARFROM209 where barcodeid =tmpBARCODE ;
select ORGANIZENAME,
PRODUCT_TYPE_NAME , PRODUCT_NAME,
CONTRACT_NUM
into tmpORGANIZATION_NAME, tmpPRODUCT_TYPE_NAME, tmpPRODUCT_NAME, tmpCONTRACT_NUMBER
from AAA.TMPBARFROMERP
where BARCODEID = tmpBARCODE;
update AAA_epm.AAA_eq_rprboarditem
set e_OrganizationName = tmpORGANIZATION_NAME,e_OriginSendTime = tmpOUT_DATE,
E_Productmodel = tmpPRODUCT_TYPE_NAME,E_Productitem = tmpPRODUCT_NAME ,E_Contactid = tmpCONTRACT_NUMBER
where e_boardid = tmpBARCODE;
if select_over='y' then
set p_errmsg='未找到数据';
rollback;
end if;
end while;
close c1;
END P1;
Top
