CREATE PROCEDURE DB2INST1.P_TEST1(out OV_RETVAL INTEGER,
out OV_RETMSG VARCHAR(400))
SPECIFIC SQL090819
RESULT SETS 2
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
BEGIN
DECLARE v_strstate char(5) default ' '; ----自定义SQL出错状态码
DECLARE SQLCODE integer; ----系统SQL出错编码
DECLARE SQLSTATE char(5); ----系统SQL出错状态码
DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR ----返回给客服
SELECT NAME FROM TEST1;
DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR
SELECT OID FROM TEST1;
---异常处理
DECLARE EXIT HANDLER FORM SQLEXCEPTION
BEGIN
VALUES(SQLCODE,SQLSTATE) INTO OV_RETVAL,v_strstate;
SET OV_RETMSG = ov_retmsg||'处理失败,其中SQLSTATE:'||v_strState;
END;
----固定变量赋初值
SET OV_RETVAL=0; ---返回代码
SET OV_RETMSG=''; ---返回信息 OPEN rs1;
OPEN rs2;
SET OV_RETMSG='执行成功';
END在DbVisualizer下执行,报如下错误
刚学习写DB2的存储过程。
15:18:50 [CREATE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;char(5) default ' ';<psm_semicolon>
15:18:50 [DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: integer;DECLARE SQLCODE;END-OF-STATEMENT
15:18:50 [DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: DECLARE SQLSTATE char;BEGIN-OF-STATEMENT;<values>
15:18:51 [DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: DECLARE RS1 CURSOR WITH RETURN TO CLIENT FO;BEGIN-OF-STATEMENT;<space>
15:18:51 [DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: DECLARE RS2 CURSOR WITH RETURN TO CALLER FO;BEGIN-OF-STATEMENT;<space>
15:18:51 [DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: <space>;EXIT;HANDLER
15:18:51 [SET - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: SET OV_RETMSG =;BEGIN-OF-STATEMENT;<values>
15:18:52 [END - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;END;JOIN <joined_table>
15:18:52 [SET - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: OV_RETVAL;SET ;JOIN <joined_table>
15:18:52 [SET - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: OV_RETMSG;SET ;JOIN <joined_table>
15:18:53 [OPEN - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;OPEN rs1;JOIN <joined_table>
15:18:53 [OPEN - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;OPEN rs2;JOIN <joined_table>
15:18:53 [SET - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: OV_RETMSG;SET ;JOIN <joined_table>
15:18:53 [END - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;END;JOIN <joined_table>
... 14 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 14 errors]
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货