时间戳

首页 > 解决方案 > 如何防止无效数据插入SQL

sql - 如何防止无效数据插入SQL

问题描述

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

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）

标签： sqlasp.net-mvcoraclesql-insert

解决方案

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

对于初学者来说，很大程度上取决于重叠范围的定义。例如，可以说您的所有样本数据范围都是无效的： maxvalue = 10与 重叠minvalue = 10，假设边界正在测试>=并且<=这是默认值。同样，null界限会产生复杂性：如果你有一个现有的范围(30, null)是(40,50)有效的？

因此，一旦您理清了业务逻辑，接下来就是实施它们的问题。在 Oracle 中，我们可以使用复合触发器来做一些事情。对于每一行，我们将插入/更新行的 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 上发布了一个演示（需要免费登录，抱歉！）。在这里找到它。

推荐阅读