DB2 V9.7存储过程调试
补充:请帮忙把下面这个存储过程在DB2 9.7下面调试通过.谢谢...
贴不下.放在空间里了.麻烦帮忙调试下
http://hi.zzzyk.com/wangyu_nick/blog/item/19b63810405727e4c3ce79f8.html
追问:不对啊.提示如下:
DECLARE re_1 VARCHAR(20) default 'un'
DB21034E 该命令被当作 SQL 语句来处理,因为它是无效的“命令行处理器”命令。在
SQL 处理期间,它返回:
SQL0104N 在 "HAR(20) default 'un'" 后面找到异常标记
"END-OF-STATEMENT"。预期标记可能包括:"<psm_semicolon>"。 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.';
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
其他:下面是报的错误:因为存储过程比较长 没办法贴上来只能发文件给各位看了 加了 找我,我帮你解决,519256438 加QQ了
上一个:db2中关于date() to_date() char() to_char()函数的语法。是关于db2的,不是SQL、ORAClE 希望有经验者告知
下一个:问一个DB2数据库的问题(高分求解)