DB存储过程关于数据处理的写法

开发背景

1.数仓推数据文件（可以带有文件日期，也可以不带文件日期，分两种判断），插入到数据库中，我们需要做的是判断它导入文件日期的数据中的日综余额和明细数据，是否能和导入文件日期的前一天的账号的日综进行相加减与导入文件日期数据一致，需求参考图：

例如：20220529的日综数据+20220530根据C（增加），D（减去）最后得到的数据与20220530蓝 色框内的日综余额进行对比

db存储过程写法如下：

CREATE PROCEDURE “ZNDT”.“CHECKDATA”
(IN V_TRADE_DATE_ZUORI VARCHAR(8),IN V_TRADE_DATE VARCHAR(8))
LANGUAGE SQL
SPECIFIC SQL220727170351722
BEGIN
DECLARE V_SQL_STR VARCHAR(4000);
DECLARE V_COUNT integer;
DECLARE V_SQL_STR1 VARCHAR(4000);
DECLARE I_CODE VARCHAR(8);
DECLARE inserterror INTEGER default 0;
DECLARE status INTEGER default 0;
DECLARE COUNTDATA integer;
DECLARE countdate integer;
DECLARE countdate1 integer;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING SET inserterror=1;

–适用于上线最初导数前一天无数据
SELECT COUNT(PAYERACCTNO) INTO COUNTDATA from TL9_MARGINACCOUNT where TRADE_DATE=V_TRADE_DATE_ZUORI;
–昨日无数据，正常进行导数没检查今日数据的准确性
if COUNTDATA=0 then
–清空临时表：TL9_MARGINACCOUNT_TEMP
DELETE from TL9_MARGINACCOUNT_TEMP
COMMIT;
select count(*) into countdate1 from (select count(PAYERACCTNO) as countdate from TL9_MARGINACCOUNT where TRADE_DATE=V_TRADE_DATE and LOANBALANCE !=‘’ group by PAYERACCTNO) b where b.countdate>1;
if countdate1>0 then
delete from TL9_MARGINACCOUNT where TRADE_DATE=V_TRADE_DATE ;
END IF;
–昨日无数据，判断今日的导数是否出现违反主键唯一性的错误
else

–清空临时表：TL9_MARGINACCOUNT_TEMP
DELETE from TL9_MARGINACCOUNT_TEMP
COMMIT;
–导入昨日日综余额
INSERT INTO TL9_MARGINACCOUNT_TEMP(acctno,min_balance) SELECT PAYERACCTNO,TO_NUMBER(LOANBALANCE)AS LOANBALANCE FROM TL9_MARGINACCOUNT WHERE LOANBALANCE !=‘’ AND TRADE_DATE=V_TRADE_DATE_ZUORI;
COMMIT;

–将昨日的贷方金额、昨日借方金额都刷成0
update TL9_MARGINACCOUNT_TEMP set credit_balance=0,debit_balance=0,max_balance=0 ;
COMMIT;

–更新今日日综余额
MERGE INTO TL9_MARGINACCOUNT_TEMP a
USING (SELECT PAYERACCTNO,TO_NUMBER(LOANBALANCE)AS LOANBALANCE FROM TL9_MARGINACCOUNT WHERE LOANBALANCE !=‘’ AND TRADE_DATE=V_TRADE_DATE )b
on (a.acctno=b.PAYERACCTNO)
when MATCHED then
update set a.max_balance=b.LOANBALANCE;
COMMIT;

–更新今日贷方总金额
MERGE INTO TL9_MARGINACCOUNT_TEMP a
USING (SELECT PAYERACCTNO,SUM(TO_NUMBER(AMOUNT)) AS credit_balance FROM TL9_MARGINACCOUNT WHERE LOANBALANCE =‘’ AND PAY_FLAG=‘C’ AND TRADE_DATE=V_TRADE_DATE GROUP BY PAYERACCTNO )b
on (a.acctno=b.PAYERACCTNO)
when MATCHED then
update set a.credit_balance=b.credit_balance;
COMMIT;
–更新今日借方总金额
MERGE INTO TL9_MARGINACCOUNT_TEMP a
USING (SELECT PAYERACCTNO, SUM(TO_NUMBER(AMOUNT)) AS debit_balance FROM TL9_MARGINACCOUNT WHERE LOANBALANCE =‘’ AND PAY_FLAG=‘D’ AND TRADE_DATE=V_TRADE_DATE GROUP BY PAYERACCTNO )b
on (a.acctno=b.PAYERACCTNO)
when MATCHED then
update set a.debit_balance=b.debit_balance;
COMMIT;

–更新最终合计总金额
MERGE INTO TL9_MARGINACCOUNT_TEMP a
USING (select acctno,(MIN_BALANCE+CREDIT_BALANCE-DEBIT_BALANCE) AS TOTAL_BALANCE from TL9_MARGINACCOUNT_TEMP)b
on (a.acctno=b.acctno)
when MATCHED then
update set a.TOTAL_BALANCE=b.TOTAL_BALANCE;
COMMIT;

IF inserterror=1 THEN
insert into TL9_MARGINACCOUNT_TEMP values(‘sql异常’,0,0,0,0,1);
–查询在临时表里面是否有当日日综余额和总金额不相等的情况
select count() into V_COUNT from TL9_MARGINACCOUNT_TEMP where max_balance<>total_balance;
if V_COUNT>= 1 THEN
–删除当日日综余额和总金额不相等的当日全部数据
delete from TL9_MARGINACCOUNT where TRADE_DATE=V_TRADE_DATE ;
END IF;
ELSE
–查询在临时表里面是否有当日日综余额和总金额不相等的情况
select count() into V_COUNT from TL9_MARGINACCOUNT_TEMP where max_balance<>total_balance;
if V_COUNT>= 1 THEN
–删除当日日综余额和总金额不相等的当日全部数据
delete from TL9_MARGINACCOUNT where TRADE_DATE=V_TRADE_DATE ;
END IF;
END IF;

END IF;
END

为什么用临时表替换了循环判断

1.临时表创建是为了避免进行循环的判断，增加了速度

分析下具体语法

这次的存储过程涉及几点操作：
1.输入参数
2.查询数量count() 赋值给事先声明的参数V_COUNT 固定写法：count() into V_COUNT
3.if 判断，语法使用：
if 条件 then
满足条件后需要执行的任务
else
不满足条件后需要执行的任务
end if;
4.MERGE INTO从别的表插入数据
5.update
6.sql异常的抛出，语法使用：
先声明异常值：DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING SET inserterror=1; 这个语句的意思应该是如果出现了sql相关的异常，给这个值（inserterror）赋值1
然后后面我用到这个inserterror的值进行了一次判断，判断是否出现这个异常，出现了异常该怎么办

我这边只给大家分享实际案例所需要的，其他的比如db存储过程的异常机制，回滚，还有一些语法的具体介绍的，我这边就不给大家进行介绍了哈~

关于从shell脚本中调用该存储过程的方法，到下一个文章进行分享