DB2 V9.7存储过程调试

DB2 V9.7存储过程调试

CREATE PROCEDURE usp_client_insert
(
IN i_tradeSN VARCHAr(20),
IN i_scanSN VARCHAr(20),
OUT o_tradeID INT,
OUT Erro INT,
OUT Erro_msg VARCHAr(20)
)
language sql
specific SPOVERTIMEPAY
dynamic result sets 1
modifies sql data

BEGIN
DECLARE re_1 VARCHAr(20) default 'un';

declare exit handler for not found
begin
set Erro =99;
set vErro_msg = 'insert new trade record failed.';
end;

SET o_tradeID = 0;
if (i_tradeSN is null and i_scanSN is null) then
SET Erro =99;
SET Erro_msg = 'no record for insert.';
THEN SIGNAL SQLSTATE '77880' SET MESSAGE_TEXT = 'no record for insert.';
end if;

if i_tradeSN is null then
SELECt TRADEID INTO o_tradeID FROM TBLTRADES WHERe SCANSN = i_scanSN;
end if;

if i_scanSN is null then
SELECt TRADEID INTO o_tradeID FROM TBLTRADES WHERe TRADESN = i_tradeSN;
end if;

if o_tradeID = 0 then
INSERT INTO TBLTRADES (TRADESN, SCANSN, REMARKS) VALUES ( i_tradeSN,i_scanSN,re_1);
SELECt TRADEID INTO o_tradeID FROM TBLTRADES WHERe TRADESN = i_tradeSN and SCANSN = i_scanSN;
end if;
END追问不对啊.提示如下:
DECLARE re_1 VARCHAr(20) default 'un'
DB21034E 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在
SQL 处理期间，它返回：
SQL0104N 在 "HAR(20) default 'un'" 后面找到异常标记
"END-OF-STATEMENT"。预期标记可能包括：""。 LINE NUMBER=15.
SQLSTATE=42601追答CREATE PROCEDURE usp_client_insert
(
IN i_tradeSN VARCHAr(20),
IN i_scanSN VARCHAr(20),
out o_tradeID INT,
out Erro INT,
out Erro_msg VARCHAr(20)
)
language sql
specific SPOVERTIMEPAY
dynamic result sets 1
modifies sql data

BEGIN
DECLARE re_1 VARCHAr(20) default 'un';

declare exit handler for not found
begin
set Erro =99;
set vErro_msg = 'insert new trade record failed.';
end;

SET o_tradeID = 0;
if (i_tradeSN is null and i_scanSN is null) then
SET Erro =99;
SET Erro_msg = 'no record for insert.';
SIGNAL SQLSTATE '77880'
SET MESSAGE_TEXT = 'no record for insert.';
end if;

if i_tradeSN is null then
SELECt TRADEID INTO o_tradeID FROM TBLTRADES WHERe SCANSN = i_scanSN;
end if;

if i_scanSN is null then
SELECt TRADEID INTO o_tradeID FROM TBLTRADES WHERe TRADESN = i_tradeSN;
end if;

if o_tradeID = 0 then
INSERT INTO TBLTRADES (TRADESN, SCANSN, REMARKS) VALUES ( i_tradeSN,i_scanSN,re_1);
SELECt TRADEID INTO o_tradeID FROM TBLTRADES WHERe TRADESN = i_tradeSN and SCANSN = i_scanSN;
end if;
END;
再试试

加QQ了

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息