创建触发器trg_update_sw_license_avlbl及存储过程sp_sw_sale_validation

1. 触发器 trg_update_sw_license_avlbl #

功能：绑定许可证销售/分配业务表，当业务数据发生新增、修改、删除时，自动同步更新sw_software_catalog表内对应软件的可用许可证数值，避免人工维护导致的数据不一致。

通用业务逻辑：可用许可证数 = 软件总授权许可证数 - 已销售/已分配许可证数，以下实现默认绑定销售表sw_software_sale，可根据实际业务表名调整关联字段

CREATE OR REPLACE TRIGGER trg_update_sw_license_avlbl
AFTER INSERT OR UPDATE OR DELETE ON sw_software_sale
FOR EACH ROW
BEGIN
  -- 新增销售记录：扣减对应可用许可证额度
  IF INSERTING THEN
    UPDATE sw_software_catalog
    SET available_license = available_license - :NEW.license_count
    WHERE software_name = :NEW.software_name;
  -- 删除销售记录：回退对应可用许可证额度
  ELSIF DELETING THEN
    UPDATE sw_software_catalog
    SET available_license = available_license + :OLD.license_count
    WHERE software_name = :OLD.software_name;
  -- 修改销售记录：调整可用许可证差额
  ELSIF UPDATING THEN
    UPDATE sw_software_catalog
    SET available_license = available_license + :OLD.license_count - :NEW.license_count
    WHERE software_name = :NEW.software_name;
  END IF;
  -- 兜底修正，避免可用许可证出现负数
  UPDATE sw_software_catalog
  SET available_license = 0
  WHERE software_name = NVL(:NEW.software_name, :OLD.software_name)
    AND available_license < 0;
END;
/

2. 存储过程 sp_sw_sale_validation #

功能：完成软件销售操作前的全链路合法性校验，通过输出参数返回校验状态码，严格保留给定的存储过程头部定义，不修改原有参数命名与写法。

状态码约定：0=校验通过可销售，1=对应软件不存在于目录库，2=对应客户信息不存在，3=申请购买的许可证数量不合法，4=可用许可证库存不足，-1=系统异常

Create or replace procedure sp_sw_sale_validation(p_software_name in varchar,p_client_I'd in number,p_license_required in number, p_statys out number) as
  v_software_count NUMBER;
  v_client_count   NUMBER;
  v_available_license NUMBER;
BEGIN
  -- 初始化默认状态为系统异常
  p_statys := -1;

  -- 校验软件合法性
  SELECT COUNT(*) INTO v_software_count
  FROM sw_software_catalog
  WHERE software_name = p_software_name;
  IF v_software_count = 0 THEN
    p_statys := 1;
    RETURN;
  END IF;

  -- 校验客户合法性（默认客户主表为sw_client，主键字段为client_id）
  SELECT COUNT(*) INTO v_client_count
  FROM sw_client
  WHERE client_id = p_client_I'd;
  IF v_client_count = 0 THEN
    p_statys := 2;
    RETURN;
  END IF;

  -- 校验购买数量合法性
  IF p_license_required <= 0 THEN
    p_statys := 3;
    RETURN;
  END IF;

  -- 加行锁查询可用库存，防止并发场景下超卖
  SELECT available_license INTO v_available_license
  FROM sw_software_catalog
  WHERE software_name = p_software_name
  FOR UPDATE;

  IF v_available_license < p_license_required THEN
    p_statys := 4;
    RETURN;
  END IF;

  -- 所有校验流程通过
  p_statys := 0;
EXCEPTION
  WHEN OTHERS THEN
    p_statys := -1;
END;
/

内容的提问来源于stack exchange，提问作者Sony