CREATE PROCEDURE SCHEMA.PD_PROCEDURE
(
IN IN_PARA VARCHAR(2048),
OUT OUT_PARA VARCHAR(1024)
)
BEGIN
DECLARE V_SQL VARCHAR(4000) DEFAULT ''; --存放动态SQL
DECLARE V_RTN_FLAG VARCHAR(1) DEFAULT '0'; --返回标识
DECLARE V_RTN_MSG VARCHAR(1024) DEFAULT ''; --返回错误信息
DECLARE SQLCODE INTEGER; --SQL代码标识
DECLARE V_ST STATEMENT; --执行动态SQL用到的参数
DECLARE SQLEXIT CONDITION FOR SQLSTATE '80001';
--自定义异常
DECLARE EXIT HANDLER FOR SQLEXIT
BEGIN
SET OUT_PARA='RTN_FLAG=1'||CHR(13)||CHR(10)||'RTN_MSG='||V_RTN_MSG;
END;
--定义数据库系统异常
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_RTN_MSG=MESSAGE_TEXT;
SET OUT_PARA='RTN_FLAG=1'||CHR(13)||CHR(10)||'RTN_MSG='||V_RTN_MSG;
END;
--根据判断条件,返回不同异常
IF NOT EXISTS(SELECT 1 FROM TEST WHERE TEST='Y') THEN
SET V_RTN_MSG='请*********执行本操作!';
SIGNAL SQLEXIT;
END IF;
--返回结果集
BEGIN
DECLARE V_RTN_CUR CURSOR WITH RETURN FOR
SELECT * FROM DB2INST1.TB_TEST;
ORDER BY SETDATE FETCH FIRST 3 ROWS ONLY;
OPEN V_RTN_CUR;
END;
--生成uuid
select concat(hex(RAND()), hex(RAND())) into v_uuid from SYSIBM.SYSDUMMY1;
--生成时间戳充当uuid
select replace(replace(char(current timestamp),'-',''),'.','') from SYSIBM.dual
--执行动态SQL
BEGIN
SET V_SQL='INSERT INTO DB2INST1.TB_TEST_'||V_DATE||' SELECT * FROM DB2INST1.TB_TEST;';
PREPARE V_ST FROM V_SQL;
EXECUTE V_ST;
END;
--校验两张表的数据是否完全相同
IF EXISTS
(SELECT 1
FROM
(
SELECT STD_ID,BS_FLAG,STD_NUM,SJ_NO,SJ_DATE,DEAR_DATE,DEAR_NO
FROM DB2INST1.TB_HOLD_CHECK
WHERE OPEN_STATUS='B'
) T1
FULL JOIN
(
SELECT STDID,BUYORSAL,STDNUM,STDNO,STDDATE,FADATE,CNTNO
FROM DB2INST1.TB_CNTRACT
WHERE OPEN_STATE='B'
) T2
ON T1.SJ_NO=T2.STDNO AND T1.SJ_DATE=T2.STDDATE AND T1.DEAR_NO=T2.CNTNO AND T1.DEAR_DATE=T2.FADATE
WHERE COALESCE(STD_NUM,0)<>COALESCE(STDNUM,0)
) THEN
STD V_ERRMSG='校验失败,****************';
STD V_FLAG = '1';
--插入错误记录表
INSERT INTO DB2INST1.TB_CHECK_RESULT(CLOSE_DATE,CHECK_NAME,CHECK_STEP,CHECK_RESULT,CHECK_MSG,OPR_USER,OPR_DATE)
VALUES(CURRENT_DATE,'PD_PROCEDURE','STEP*',V_FLAG,V_ERRMSG,'ADMIN',CURRENT_DATE);
END IF;
--程序无异常,返回的结果
SET OUTPARA='RTN_FLAG=0'||CHR(13)||CHR(10)||'RTN_MSG=操作成功!';
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29812844/viewspace-1988821/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29812844/viewspace-1988821/