Mysql 存储过程研发规范

Mysql存储过程研发规范 前言 mysql数据库的错误机制比较弱，复杂的sql语句容易报错，且执行效率低下，业务逻辑不清晰，在用存储过程编写业务逻辑的时候，往往很难定位问题，以及错误sql，给研发人员带来开发上障碍。往往排除问题，需要花费比较长的时间，后期存储过程的维护交接上会存在较大的障碍。因此在做存储过程编写的时候，增加一种异常捕捉的机制，断点续跑能力，快速的错误定位，下面章节简要介绍。

存储过程编写规范 1. 过程的逻辑代码全部使用动态SQL。 2. 使用创建临时表的方式，方便后续数据问题定位。 3. 尽量不用复杂逻辑SQL关联关系，复杂的sql可以拆分成多步骤的逻辑sql。 4. 过程最后一步删除临时表。

存储过程监控函数 MAKE_ETL_LOG( IN IN_DAY integer, #当日的日志 IN IN_TCH_MOD integer, #存储过程模板实例ID IN IN_PROC_NAME VARCHAR(64), #存储过程名称 IN IN_REMARK VARCHAR(128), #存储过程功能描述 IN IN_STAFFS VARCHAR(20), #开发员工号ID IN IN_EXEC_TYPE CHAR, #执行类型 IN IN_STATE_DATE datetime) #存储过程的执行开始时间 函数功能说明：应用与开始执行存储过程的执行日志记录。

UPDATE_ETL_LOG( IN IN_DAY integer, #当日的日志 IN IN_TCH_MOD integer, #存储过程模板实例ID IN IN_RESULT integer, #存储过程执行结果 1 :成功; -N 失败(N 存储过程的步骤) IN IN_ERR_SQL VARCHAR(4000), #错误的sql语句 IN IN_STATE_DATE datetime) #记录时间 函数功能说明：记录存储过程执行完成是的状态时间。 动态sql执行函数 P_util_execute( in v_sqlvarchar(4000), # 动态sql语句。 out out_sqlvarchar(4000), # 返回错误sql语句 out _err1 int ) # 异常状态，报错后异常赋值到父过程。 函数说明：执行动态SQL的函数，配合存储过程的模板使用，。

存储过程业务逻辑编写模板

P_MODEL_TEST ( IN V_TCH_MOD integer # 存储过程的模板实例ID ) 函数说明：存储过程的模板，已经集成了日志信息。建立新的存储过程，将里面的内容拷贝到新的存储过程里。

模板结构说明： BEGIN #Routine body goes here... DECLARE _var,_err int default 0; DECLARE V_STEP integer ; DECLARE V_contint default 0; DECLARE V_RESULT integer; DECLARE V_beforestep_tablenamevarchar(50) ; DECLARE V_tablenamevarchar(50) ; DECLARE v_sql VARCHAR (4000) ; -- DECLARE v_out_sql VARCHAR (4000) ; DECLARE V_report_typevarchar(10) ; -- V_TCH_MOD integer := 2132; -- 环节模板号 DECLARE V_PROC_NAME varchar(50) default '222222222'; DECLARE V_DAY varchar(8) default date_format(CURDATE(), '%Y%m%d'); DECLARE v_b_dayvarchar(8) default date_format(CURDATE()-1, '%Y%m%d'); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND set _err=1;

-- 写日志 SELECT count(RESULT),result INTO V_cont, V_STEP FROM td_s_exe_log WHERE TCH_MOD = V_TCH_MOD AND DAY = V_DAY; IF V_cont=0 then set V_STEP=0; END if; IF V_STEP = 1 THEN set V_STEP=100000; # 不能return 只能赋值方式，让程序不执行。 ELSE set V_STEP = -1 * V_STEP; -- 从出错的第N步再执行 END IF; IF _err=1 then set V_STEP = 0; -- 从来没有执行过 END if; CALL MAKE_ETL_LOG((cast(V_DAY as BINARY)), -- 日期 V_TCH_MOD, -- 环节模板号 V_PROC_NAME, -- 存储过程名, 'good', -- 备注 NULL, -- 环节管理员 '1', -- 执行类型 (1：数据加载环节 2：审核校验环节) now() -- 状态时间 ); IF _err<>1 THEN -- 判断是否出现异常，无异常则执行 SET V_RESULT = 1; IF V_RESULT >= V_STEP THEN -- 代码业务逻辑编写地方。 setv_sql='SELECT 88888; '; callP_util_execute(v_sql,@v_out_vql,_err); END IF; END IF;

IF _err<>1 THEN -- 判断是否出现异常，无异常则执行 SET V_RESULT = 2; IF V_RESULT >= V_STEP THEN -- 代码业务逻辑编写地方。 select sleep(3); END IF; END IF;

IF _err<>1 THEN -- 修改程执行成功状态 CALL UPDATE_ETL_LOG((cast(V_DAY as BINARY)), V_TCH_MOD, 1, NULL, now()); COMMIT; END IF; IF _err=1 THEN ROLLBACK; callUPDATE_ETL_LOG((cast(V_DAY as BINARY)), V_TCH_MOD, -1 * V_RESULT, @v_out_vql, # 报错的sql脚本。 now()); END IF;

END 使用说明：红色部分是业务SQL逻辑代码的编写地方。所有的逻辑需要包裹在里面。可增加逻辑步奏。 增加方式拷贝以下的脚本，SET V_RESULT = 1;代办逻辑脚本在存储过程中的第一步。 IF _err<>1 THEN -- 判断是否出现异常，无异常则执行 SET V_RESULT = 1; IF V_RESULT >= V_STEP THEN -- 代码业务逻辑编写地方。 setv_sql='SELECT 88888; '; callP_util_execute(v_sql,@v_out_vql,_err); END IF; END IF;

日志表 TD_S_EXEC_LOG 字段 类型 是否为null 说明 DAY decimal(8,0) NO 记录执行日期

TCH_MOD decimal(12,0) NO 存储过程模板ID

TCH_NAME varchar(64) YES PROC_NAME varchar(64) YES 存储过程名称

STAFF varchar(20) YES 研发人员

EXEC_TYPE char(1) YES 可忽略

TCH_ID decimal(8,0) YES 可忽略

BEGIN_TIME datetime YES 过程执行时间

END_TIME datetime YES 过程结束时间

RESULT decimal(4,0) YES 执行结构1:成功–N 第N

步失败。 ERRMSG varchar(4000) YES 错误语句

PROC_RESULT varchar(64) YES 可忽略

STAFF_ID decimal(8,0) YES 研发人员工号

PROC_DATE date YES 可忽略 说明：当result=1 表示：上图标识当日的所有的存储过程都正常执行完毕。 当Result=-4 表示：在存储过程的第四步发生执行SQL异常。 当ERRMSG 表示：单result<>1 时候，记录错误的sql语句。研发人员拿出sql定位错误。 注意，过程重新跑的时候，只要当天的模板号不变，存储过程将会中报错的步奏开始执行，直到执行完成。 如果当日的过程已经执行完了。重新执行的时候存储过程的业务逻辑代码不会被执行。

代码附件：