代码之家 › 专栏 › 技术社区 › Gamma

如何防止无效数据插入SQL

sql-insert oracle asp.net-mvc sql

Gamma · 技术社区 · 6 年前

在我的项目中,我需要动态检查条件。以实现如下创建表。

CREATE TABLE myconditions 
( 
     conditionid INT IDENTITY PRIMARY KEY CLUSTERED, 
     minvalue    INT, 
     maxvalue    INT, 
     result      INT 
)

数据包含如下内容,

insert into MyConditions (MinValue, MaxValue, Result)
values (10, 20, 1), (20, 30, 2), (null, 10, 3), (30, null, 3)

我用这个表格数据来检查年龄范围,

declare @age int = 25 --this represents user age

select *
from MyConditions
where @age > isnull(MinValue, @age - 1)
  and @age <= isnull(MaxValue, @age)

但现在的问题是,假设有人插入了一个无效的范围,比如 values (5, 25, 4) 我是说这是无效的,因为在数据库中 (10, 20, 1) 这个值。当 @age = 15 两种情况都会过去的。所以我要防止 (5, 25, 4) 这个值是加法。如果有人需要添加这个 (5、25、4) 范围,这个范围值 (10,20,1) 应该删除。

我使用ASP.NET MVC应用程序将这些数据插入数据库。我该怎么做?在我的项目中使用的是Oracle。(在这个问题上,我使用了MS SQL示例代码,但我需要oracle)

2 回复 | 直到 6 年前

APC 6 年前

这种数据完整性验证很难以健壮和高效的方式实现。

首先,很大程度上取决于重叠范围的定义。例如,可以争辩说,您的所有示例数据范围都是无效的: maxvalue = 10 重叠 minvalue = 10 ,假设边界是用 >= 和 <= 这是默认的。同样地, null 界限创造复杂性:如果你有一个现有的范围 (30, null) 是 (40,50) 有效吗?

因此,一旦你理清了你的业务逻辑,就有了实现它们的问题。在甲骨文中,我们可以用复合触发器来做一些事情。对于每一行,我们将插入/更新行的ID存储在一个数组中。然后在语句的末尾,我们循环遍历数组并以交叉连接的形式查询表,以比较日期范围。

create or replace trigger myconditions_trg 
  for insert or update of minvalue, maxvalue 
    on myconditions 
  compound trigger 

  type condition_array is table of int 
    index by binary_integer; 
  conditions condition_array; 

  procedure validate_range (p_id in int) is 
    overlapping_range exception; 
    dummy char(1); 
  begin 
    begin 
      select null into dummy 
      from myconditions t1 
          , myconditions t2 
      where t1.conditionid = p_id 
      and t2.conditionid != p_id 
      and t1.minvalue != t2.minvalue 
      and ( 
           t1.minvalue between t2.minvalue and t2.maxvalue 
           or 
           t1.maxvalue between t2.minvalue and t2.maxvalue 
          ) 
      and rownum = 1; 
      raise overlapping_range; 
    exception 
      when no_data_found then 
        -- what we're hoping for, no overlaps found
        null; 
    end; 
  exception 
    when overlapping_range then 
      raise_application_error(-20000, 
        'overlapping range for id #' || p_id); 
  end validate_range; 

  procedure validate_ranges is 
    l_id int; 
  begin 
    l_id := conditions.first; 
    loop 
      exit when l_id is null; 
      validate_range (l_id); 
      l_id := conditions.next(l_id); 
    end loop; 
    conditions.delete; 
  exception 
    when others then 
      conditions.delete; 
      raise; 
  end validate_ranges; 

  BEFORE EACH ROW is 
  begin 
    -- store id to validate 
    conditions(:new.conditionid) := 1; 
  end before each row; 

  AFTER STATEMENT is 
  begin 
    validate_ranges; 
  end after statement; 

end myconditions_trg;

此触发器不尝试处理多用户方案。老实说,我们没什么办法阻止两个不同的会话产生重叠范围。唯一能保证的就是锁上整张桌子,但这可能并不可取。

如果你感兴趣,我已经发布了一个关于Oracle LiveSQL的演示(需要免费登录,抱歉!). Find it here.

Gordon Linoff 6 年前

您需要为此使用触发器或用户定义的函数。

简单的 check 约束只能检查单个中的值。坦率地说,我认为触发是更常见的方法。

两者的确切语法取决于数据库——您已经指定了其中的两个——所以更详细的答案是不可行的。