CREATE PROCEDURE name( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
----------------------------定义变量------------------------------
DECLARE SQLCODE INTEGER DEFAULT 0; --系统SQL出错编码
DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; --系统SQL出错状态码
DECLARE strState varchar(40) DEFAULT '';
DECLARE v_Msg VARCHAR(1000); --消息
----------------------------变量定义结束--------------------------
----------------------------异常处理------------------------------
/*无数据异常*/
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_Msg='';
/*SQL异常*/
DECLARE EXIT HANDLER FOR SQLSTATE '99999'
BEGIN
SET v_Msg=strState||' 错误';
RESIGNAL SQLSTATE '80000'
SET MESSAGE_TEXT=v_Msg;
END;
----------------------------异常处理结束--------------------------
----------------------------处理过程开始--------------------------
----------------------------处理过程结束--------------------------
SET strState='完成';
set strState ='完成';
/*注释*/
IF (strState <>'完成')
THEN
SIGNAL SQLSTATE '99999';
ELSE
SET v_Msg='完成';
END IF;
--返回结果
DECLARE GLOBAL TEMPORARY TABLE SESSION.RET
(
INFO VARCHAR(300)
)
NOT LOGGED WITH REPLACE;
INSERT INTO SESSION.RET VALUES(v_Msg);
P1:BEGIN
DECLARE Rs1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM SESSION.RET;
OPEN Rs1;
END P1;
END;