CREATE PROCEDURE SCHEMA.PD_PROCEDURE
(
IN INPARA VARCHAR(2048),
OUT OUTPARA VARCHAR(1024)
)
BEGIN
DECLARE V_SQL VARCHAR(4000) DEFAULT ''; --存放动态SQL
DECLARE V_FLAG VARCHAR(1) DEFAULT '0'; --返回标识
DECLARE V_ERRMSG 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 OUTPARA='RTN_FLAG=1'||CHR(13)||CHR(10)||'RTN_MSG='||V_ERRMSG;
END;
--定义数据库系统异常
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 V_ERRMSG=MESSAGE_TEXT;
SET OUTPARA='RTN_FLAG=1'||CHR(13)||CHR(10)||'RTN_MSG='||V_ERRMSG;
END;
--根据判断条件,返回不同异常
IF NOT EXISTS(SELECT 1 FROM TEST WHERE TEST='Y') THEN
SET V_ERRMSG='请*********执行本操作!';
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;
--执行动态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;
[DB2]存储过程经常使用方法
最新推荐文章于 2023-04-07 16:59:32 发布