MySQL存储过程进阶 Java实现智能财务报销审批流程

MySQL存储过程与Java审批流程
原创 于 2025-09-17 01:23:01 发布 · 289 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #java #数据库

MySQL存储过程进阶

在智能财务报销审批流程中，MySQL存储过程可以高效处理复杂的业务逻辑。以下是一些进阶用法：

创建带参数的存储过程，用于报销单提交：

DELIMITER //
CREATE PROCEDURE sp_submit_expense(
    IN p_employee_id INT,
    IN p_amount DECIMAL(10,2),
    IN p_category VARCHAR(50)
)
BEGIN
    DECLARE v_approver_id INT;
    
    -- 根据金额自动分配审批人
    IF p_amount <= 5000 THEN
        SET v_approver_id = (SELECT manager_id FROM employees WHERE id = p_employee_id);
    ELSE
        SET v_approver_id = (SELECT finance_manager_id FROM department WHERE id = 
                            (SELECT department_id FROM employees WHERE id = p_employee_id));
    END IF;
    
    INSERT INTO expense_records 
    (employee_id, amount, category, approver_id, status, submit_time)
    VALUES 
    (p_employee_id, p_amount, p_category, v_approver_id, 'PENDING', NOW());
END //
DELIMITER ;

多级审批流程存储过程示例：

DELIMITER //
CREATE PROCEDURE sp_process_approval(
    IN p_expense_id INT,
    IN p_approver_id INT,
    IN p_decision VARCHAR(10)
)
BEGIN
    DECLARE v_current_status VARCHAR(20);
    DECLARE v_amount DECIMAL(10,2);
    DECLARE v_final_approver_id INT;
    
    -- 获取当前状态和金额
    SELECT status, amount INTO v_current_status, v_amount
    FROM expense_records WHERE id = p_expense_id;
    
    -- 验证审批权限
    IF NOT EXISTS (SELECT 1 FROM expense_records 
                  WHERE id = p_expense_id AND approver_id = p_approver_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无审批权限';
    END IF;
    
    -- 处理审批结果
    IF p_decision = 'APPROVE' THEN
        IF v_amount > 20000 THEN
            -- 大额报销需要财务总监最终审批
            SET v_final_approver_id = (SELECT id FROM employees WHERE title = 'CFO');
            UPDATE expense_records 
            SET status = 'FINAL_APPROVAL_PENDING',
                final_approver_id = v_final_approver_id
            WHERE id = p_expense_id;
        ELSE
            -- 直接批准
            UPDATE expense_records 
            SET status = 'APPROVED',
                approve_time = NOW()
            WHERE id = p_expense_id;
            
            -- 触发付款流程
            CALL sp_trigger_payment(p_expense_id);
        END IF;
    ELSE
        -- 拒绝处理
        UPDATE expense_records 
        SET status = 'REJECTED',
            reject_reason = CONCAT('审批人ID:', p_approver_id, ' 拒绝')
        WHERE id = p_expense_id;
    END IF;
END //
DELIMITER ;

Java实现智能审批流程

在Java中实现与MySQL存储过程的交互，构建智能审批系统：

Spring Boot集成示例：

@Repository
public class ExpenseRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public Long submitExpense(ExpenseRequest request) {
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("sp_submit_expense");
        
        Map<String, Object> params = new HashMap<>();
        params.put("p_employee_id", request.getEmployeeId());
        params.put("p_amount", request.getAmount());
        params.put("p_category", request.getCategory());
        
        Map<String, Object> result = jdbcCall.execute(params);
        return ((Number)result.get("last_insert_id")).longValue();
    }
}

智能审批规则引擎集成：

@Service
public class ApprovalService {
    @Autowired
    private ExpenseRepository expenseRepo;
    @Autowired
    private RuleEngine ruleEngine;
    
    public void processApproval(ApprovalDecision decision) {
        // 调用存储过程处理基础审批
        expenseRepo.processApproval(
            decision.getExpenseId(), 
            decision.getApproverId(),
            decision.getDecision()
        );
        
        // 智能规则补充处理
        ExpenseRecord record = expenseRepo.findById(decision.getExpenseId());
        if("APPROVED".equals(record.getStatus())) {
            ruleEngine.checkFraudRisk(record);
            ruleEngine.checkBudgetLimit(record);
        }
    }
}

流程自动化设计

财务报销的状态机实现：

public enum ExpenseStatus {
    DRAFT,
    PENDING,
    APPROVED,
    REJECTED,
    FINAL_APPROVAL_PENDING,
    PAYMENT_PROCESSING,
    COMPLETED
}

public class ExpenseWorkflow {
    private static final Map<ExpenseStatus, List<ExpenseStatus>> transitions = Map.of(
        DRAFT, List.of(PENDING),
        PENDING, List.of(APPROVED, REJECTED, FINAL_APPROVAL_PENDING),
        FINAL_APPROVAL_PENDING, List.of(APPROVED, REJECTED),
        APPROVED, List.of(PAYMENT_PROCESSING),
        PAYMENT_PROCESSING, List.of(COMPLETED)
    );
    
    public static boolean isValidTransition(ExpenseStatus current, ExpenseStatus next) {
        return transitions.getOrDefault(current, Collections.emptyList())
                         .contains(next);
    }
}

性能优化策略

针对高频审批场景的优化方案：

使用存储过程批量处理：

DELIMITER //
CREATE PROCEDURE sp_batch_approve(
    IN p_approver_id INT,
    IN p_expense_ids TEXT
)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_processed INT DEFAULT 0;
    
    -- 创建临时表存储ID
    CREATE TEMPORARY TABLE temp_ids (id INT);
    
    -- 解析ID字符串
    SET @sql = CONCAT('INSERT INTO temp_ids VALUES ', 
                     REPLACE(REPLACE(p_expense_ids, ',', '),('), ' ', ''));
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 统计总数
    SELECT COUNT(*) INTO v_count FROM temp_ids;
    
    -- 批量更新审批状态
    UPDATE expense_records e
    JOIN temp_ids t ON e.id = t.id
    SET e.status = 'APPROVED',
        e.approver_id = p_approver_id,
        e.approve_time = NOW()
    WHERE e.approver_id = p_approver_id 
    AND e.status = 'PENDING';
    
    -- 获取处理数量
    SELECT ROW_COUNT() INTO v_processed;
    
    -- 返回处理结果
    SELECT v_count AS total_count, v_processed AS approved_count;
    
    -- 清理临时表
    DROP TEMPORARY TABLE temp_ids;
END //
DELIMITER ;

Java端连接池配置优化：

# application.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

安全与审计

确保审批流程的安全性和可追溯性：

审计日志存储过程：

DELIMITER //
CREATE PROCEDURE sp_log_approval_action(
    IN p_expense_id INT,
    IN p_actor_id INT,
    IN p_action_type VARCHAR(20),
    IN p_action_details TEXT
)
BEGIN
    INSERT INTO approval_audit_log
    (expense_id, actor_id, action_time, action_type, action_details)
    VALUES
    (p_expense_id, p_actor_id, NOW(), p_action_type, p_action_details);
    
    -- 同步更新报销单最后修改信息
    UPDATE expense_records
    SET last_updated = NOW(),
        last_updated_by = p_actor_id
    WHERE id = p_expense_id;
END //
DELIMITER ;

Java端审计切面实现：

@Aspect
@Component
public class ApprovalAuditAspect {
    @Autowired
    private AuditLogService logService;
    
    @AfterReturning(
        pointcut = "execution(* com..approval..*(..)) && args(decision)",
        argNames = "decision"
    )
    public void logApprovalAction(ApprovalDecision decision) {
        logService.logAction(
            decision.getExpenseId(),
            decision.getApproverId(),
            decision.getDecision(),
            "审批操作: " + decision.getDecision()
        );
    }
}

异常处理机制

健壮的异常处理设计方案：

MySQL错误处理示例：

DELIMITER //
CREATE PROCEDURE sp_safe_approval(
    IN p_expense_id INT,
    IN p_approver_id INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO,
        @text = MESSAGE_TEXT;
        
        INSERT INTO approval_errors
        (expense_id, approver_id, error_time, error_code, error_message)
        VALUES
        (p_expense_id, p_approver_id, NOW(), @errno, @text);
        
        RESIGNAL;
    END;
    
    -- 正常审批逻辑
    CALL sp_process_approval(p_expense_id, p_approver_id, 'APPROVE');
END //
DELIMITER ;

Java端异常处理策略：

@ControllerAdvice
public class ApprovalExceptionHandler {
    
    @ExceptionHandler(SQLException.class)
    public ResponseEntity<ErrorResponse> handleSQLException(SQLException ex) {
        ErrorResponse response = new ErrorResponse(
            "APPROVAL_PROCESS_ERROR",
            "审批流程数据库错误: " + ex.getMessage()
        );
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                            .body(response);
    }
    
    @ExceptionHandler(BusinessRuleException.class)
    public ResponseEntity<ErrorResponse> handleBusinessRuleException(BusinessRuleException ex) {
        ErrorResponse response = new ErrorResponse(
            "BUSINESS_RULE_VIOLATION",
            ex.getMessage()
        );
        return ResponseEntity.status(HttpStatus.BAD_REQUEST)
                            .body(response);
    }
}

EYOUTER234
关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值