java监控存储过程执行情况

引言

使用jdbc调用存储过程时，只要可以调通返回到的都是成功，无法知晓存储过程内部的执行情况，因此我们考虑在从存储过程本身出发，增加返回值和日志记录以查询存储过程执行的情况。

增加输出项

参数名	类型	是否必填	说明
code	int	N	返回编码（用于程序判断存储过程是否执行成功）200 成功 500 失败
message	text	N	返回信息（当存储过程执行出错时，或者用户自定义异常将信息存入message中返回给程序）

增加监控表

在存储过程内部将存储过程的执行情况存入监控表中，方便程序查询执行的情况

CREATE TABLE task_procedure_monitor (
  monitor_id varchar(50)  not  NULL,
  procedure_name varchar(100)   NOT NULL,
  monitor_msg varchar(2000)   NULL,
  remark varchar(255)   NULL
);

存储过程实例

sqlserver

CREATE PROCEDURE [dbo].demo_procedure
  @code int OUTPUT,
  @message varchar(200) OUTPUT
AS
BEGIN
  SET @code = 200
  SET @message = '成功'
  BEGIN TRY

  ------- 业务逻辑 -------
  DELETE  from dept_income

  ------- 自定义异常（选填） -------
  -- RAISERROR('执行过程中出现错误：%s',16,1,'数据为空')
  END TRY
  BEGIN CATCH
  ------- 存储过程执行失败 -------
    SET @code = 500
    SET @message = ERROR_MESSAGE()
  END CATCH
  ------- 记录结果（选填） -------
  -- INSERT INTO ex_task_procedure_monitor([monitor_id], [procedure_name],  [monitor_msg], [remark]) VALUES (NEWID(),'test_delete',@message,null);

END

oracle

CREATE OR REPLACE PROCEDURE demo_procedure(
    CODE out number,
    MESSAGE out VARCHAR2 
  )
  IS
  BEGIN
     CODE := 200;
     MESSAGE := '成功';
  
    ------- 业务逻辑 -------
    DELETE from WYT_INCOME;
    ------- 自定义异常（选填） -------
      -- RAISE_APPLICATION_ERROR(-20123, '出异常了');
    ------ 记录结果（选填） -------
     INSERT INTO ex_task_procedure_monitor(monitor_id, , procedure_name, monitor_msg, remark) VALUES (sys_guid(),'test_delete',MESSAGE,null);
    ------- 存储过程执行失败 -------
      EXCEPTION
      -- WHEN my_error THEN
      -- CODE := 500;
    -- MESSAGE:= sqlerrm;
      WHEN OTHERS THEN
      CODE := 500;
    MESSAGE:= sqlerrm;
      ROLLBACK;
    ------ 记录结果（选填） -------
     INSERT INTO ex_task_procedure_monitor(monitor_id, , procedure_name, monitor_msg, remark) VALUES (sys_guid(),'test_delete',MESSAGE,null);
  
  END;

java调用

CallableStatement clbStmt = conn.prepareCall("{CALL " + "demo_procedure" + "(?,?)}");
// 设置输出参数
clbStmt.registerOutParameter(1, Types.VARCHAR);
clbStmt.registerOutParameter(2, Types.VARCHAR);
clbStmt.execute();
String code = clbStmt.getString(1);
String message = clbStmt.getString(2);
if(!"200".equals(code)){
   throw new BusinessException("存储过程执行失败,返回信息:" + message);
}