使用触发器/过程根据实体的值评估参数

Question

我有以下问题。在我的应用程序中，用户使用特殊系统（力量、感知等，值从 1 到 10）创建一个游戏角色。在保存时或之后（通过调用过程），我需要根据特殊参数值对字符进行统计。我该怎么做？这是关系方案：

这是 SQL 代码：

create table Player (
  id_player numeric,
  player_name varchar2(50) not null,
  age decimal not null,
  strength decimal not null,
  perception decimal not null,
  endurance decimal not null,
  charisma decimal not null,
  inteligence decimal not null,
  agility decimal not null,
  luck decimal not null,
  caps decimal not null,
  statistics numeric,
  CONSTRAINT chk_s check (strength <= 10),
  CONSTRAINT chk_p check (perception <= 10),
  CONSTRAINT chk_e check (endurance <= 10),
  CONSTRAINT chk_c check (charisma <= 10),
  CONSTRAINT chk_i check (inteligence <= 10),
  CONSTRAINT chk_a check (agility <= 10),
  CONSTRAINT chk_l check (luck <= 10),
  CONSTRAINT unique_name UNIQUE (player_name),

  CONSTRAINT PLAYER_PK primary key (id_player)
);

create table Player_derived_statistics(
  id_statistics numeric,
  carry_weight decimal,
  hit_points decimal,
  radiation_resistance decimal,

  CONSTRAINT DERIVED_STATISTICS_PK primary key (id_statistics)
);

alter table Player add constraint PLAYER_DERIVED_STATISTICS_FK1 foreign key (statistics) references Player_derived_statistics (id_statistics);

以及返回所有参数的查询：

SELECT p.strength, p.perception, p.endurance, p.charisma, p.inteligence, p.agility, p.luck
from player p inner join player_derived_statistics s on s.id_statistics = p.statistics;

所以最后我希望能够计算每个玩家的携带重量、命中点数和辐射阻力。假设所有公式均为 (player_parameter * 10) + 150。使用什么更好：触发器还是过程？

---

编辑

我正在尝试使用答案中的代码，但收到错误 遇到符号“INNER”，当期望出现以下其中一项时：( ....

CREATE OR REPLACE PACKAGE pkg_player_stats AS
  FUNCTION get_derived_stats( p_id_player IN player.id_player%TYPE )
    RETURN derived_stats_rec
  IS
    l_stats_rec derived_stats_rec;
  BEGIN
    SELECT (p.strength*10)+150,
           (p.endurance*20)+150,
           ((p.endurance-1)*2)/100
      INTO l_stats_rec.carry_weight,
           l_stats_rec.hit_points,
           l_stats_rec.radiation_resistance
      FROM (
        SELECT p.strength,  
               p.endurance
          from player p inner join player_derived_statistics s on s.id_statistics = p.statistics);
    RETURN l_stats_rec;
  END get_derived_stats;
END;

Answer 1

我绝对不会使用触发器来做这种事情。听起来您想要一个接受 ID_PLAYER 参数并返回一个值或值记录的函数。像这样的东西（请注意，我不确定我是否理解你所描述的公式，所以我猜测有点

CREATE OR REPLACE PACKAGE pkg_player_stats
AS
  TYPE derived_stats_rec IS RECORD (
    carry_weight NUMBER,
    hit_points   NUMBER,
    radiation_resistance NUMBER );

  FUNCTION get_derived_stats( p_id_player IN player.id_player%TYPE )
    RETURN derived_stats_rec;
END;

CREATE OR REPLACE PACKAGE pkg_player_stats
AS
  FUNCTION get_derived_stats( p_id_player IN player.id_player%TYPE )
    RETURN derived_stats_rec
  IS
    l_stats_rec derived_stats_rec;
  BEGIN
    SELECT carry_weight         * multiplier + 150,
           hit_points           * multiplier + 150,
           radiation_resistance * multiplier + 150
      INTO l_stats_rec.carry_weight,
           l_stats_rec.hit_points,
           l_stats_rec.radiation_resistance
      FROM (
        SELECT p.strength + 
               p.perception + 
               p.endurance +
               p.charisma + 
               p.inteligence +
               p.agility +
               p.luck multiplier,
               s.carry_weight,
               s.hit_points,
               s.radiation_resistance
          from player p 
               inner join player_derived_statistics s on s.id_statistics = p.statistics);
    RETURN l_stats_rec;
  END get_derived_stats;
END;

Answer 2

为什么需要两张桌子？我会选择

• 仅包含所有特殊统计信息的单个表和计算派生统计信息的视图（在您的应用程序中您将查询视图）：

  创建视图player_v AS 
  SELECT p.strength, ..., /* 所有属性 */
         p.strength * 10 + 150 作为carry_weight，
         p.endurance * 20 + 150 作为 hit_points，
         (p.endurance - 1) * 2 / 100 作为辐射电阻
    来自玩家 p
  

• 包含使用触发器更新的派生列的单个表：

  创建或替换触发器player_ins_up_trg 
     更新或插入播放器之前
     对于每一行
  开始
     :new.carry_weight := :new.strength * 10 + 150;
     :new.hit_points := :new.endurance * 20 + 150;
     :new.radiation_resistance := (:new.endurance - 1) * 2 / 100;
  结尾;
  

• 如果您使用的是 Oracle 11，您还可以使用 虚拟列：

  <块引用>

  虚拟列不存储在磁盘上。相反，数据库通过计算一组表达式或函数按需导出虚拟列中的值。

  例如：

  ALTER TABLE 玩家 ADD (carry_weight AS (力量 * 10 + 150));