2021-06-23

hana 存储过程 校验表的数据量 满足最小 数据量
create procedure schema名.sp_tb_data_check(
in tb_name varchar(200),
in min_ct integer,
out o1 integer
)
language sqlscript as
/*
@description 根据传入的表名和最小数据量，校验该表数据量是否满足 >最小数据量
@tb_name 入参1为表名
@min_ct 最小数据量
@return 符合条件返回1 不满足返回0
*/
begin
declare v_task_name varchar(100)=‘sp_tb_data_check’;
declare v_check_rule varchar(100)=‘count(1) >’ || min_cnt;
declare v_timestamp varchar(20)=to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS);
declare v_count integer;
– 异常处理，遇到表不存在或者其他sql异常，默认校验通过
declare EXIT HANDLER FOR SQLEXCEPTION
begin
execute immediate ‘rollback’;
insert into schema.data_check(…)
values (:v_task_name, …,substr(::SQL_ERROR_MESSAGE,1,90));
execute immediate ‘commit’;
o1=1;
end

exec ‘select count(1) from’ || :tn_name into v_count;

if v_count > min_cnt then
insert into data_check(字段) values(值);
commit;
o1 =1
else
insert into…;
commit;
o1 = 0;
end if;

end