sql :数据验证是如何通过存储过程完成的？

Question

存储过程通常用于数据验证或封装合并多个SQL查询的大型复杂处理指令。

这个甲骨文参考文献说。那么，有人能通过在现实世界中的例子来帮助我理解存储过程是如何用于数据验证的吗？

Answer 1

数据验证之所以发生，是因为为了将数据传递到存储过程中，它是通过显式设置为Oracle数据类型(或者用户定义的类型，也是基于Oracle数据类型的)参数来完成的。只有数据类型的验证才会发生--如果需要，必须构造更深入的验证(IE:检查数字数据类型中的小数)。参数化查询通常更不受SQL注入的影响，但它实际上取决于参数是什么和查询在做什么。

 CREATE OR REPLACE PROCEDURE example (IN_VALUE NUMBER) IS

 BEGIN

   SELECT t.*
     FROM TABLE t
    WHERE t.column = IN_VALUE;

 END;

在本例中，提交一个VARCHAR/string将导致一个错误--除了所支持的数字之外，任何其他内容都会导致错误。如果IN_VALUE数据类型不能隐式转换为TABLE.column的数据类型，您将得到一个错误。

存储过程封装事务，这允许复杂的处理指令(意思是多个SQL查询)。事务处理(IE:必须显式地声明“提交”或“回滚”)取决于设置。

Answer 2

验证可能意味着许多事情，并且可以通过各种方式在数据库中完成：

• 列数据类型本身就是一种验证形式:数字列只接受有效数字，等等。
• 主键、唯一键和外键约束执行验证。
• 检查约束执行其他简单的一行验证，如：。
  • END_DATE > START_DATE
  • 薪金>0
  • JOB =销售人员或佣金为空

​

但是，有更复杂的验证规则不能由上述任何一个强制执行，例如：- <= (从config_table中选择max_sal )-在分配给他们的部门的start_date和end_date之间的emp.start_date

执行这些规则有多种方法，包括数据库触发器，但通常首选的方法是创建一个存储过程，通常称为"API“来执行验证和操作。

PROCEDURE insert_emp (...) IS
    ...
BEGIN
    -- Validate
    -- 1) Salary less than max
    SELECT max_sal
    INTO   l_max_sal
    FROM   config;
    IF p_sal > l_max_sal THEN
        error_pkg.raise_error ('Salary is too high');
    END IF;
    ...
    -- Insert
    INSERT INTO emp (...) VALUES (...);
END; 

然后，应用程序只需调用此过程，而不是直接执行更新，所有必要的验证都将执行。实际上，应用程序可能会让有来调用这个过程--直接插入到表中可能会被禁用。

Answer 3

在理想的世界中，您选择的DBMS在关系上是完整的，允许您编写任意复杂的约束，并支持多个赋值，从而允许使用简单语句随时更新数据库(即不延迟或禁用约束)。在现实世界中，我们有SQL。

理想的SQL产品应该是完全符合SQL-92标准的:支持CREATE ASSERTION (模式级约束)，允许CHECK约束中的子查询，并支持事务中的约束延迟，以便在不禁用约束的情况下更新数据库。可悲的是，Oracle还没有达到这种功能水平。因此，在现实世界中，我们有时不得不使用过程代码来“管理”更新，同时维护数据完整性。

例如，考虑一种真正的一对一关系，这种关系非常普遍，业务规则如下：

一个数据库包含三个关系式的雇员和项目的详细信息: EMP、PROJ和EMP_PROJ。每个项目必须至少有一名员工，每个项目附件必须引用现有的项目。创建项目时，至少必须将一名员工同时附加到该项目。

在甲骨文中，您不能编写ASSERTION或CHECK来执行表间约束，因此延迟约束的能力在这种情况下几乎没有什么意义。

一种可以使用适当参数编写PROCEDURE以创建项目并为项目分配一名员工的方法。按照这一顺序(伪代码)，这样的程序：

1)开始交易；

2)插入PROJ；

3)插入EMP_PROJ；

4)对不满足概念约束的数据进行测试。

EXISTS (
        SELECT * 
          FROM PROJ
         WHERE NOT EXISTS (
                           SELECT * 
                             FROM EMP_PROJ
                            WHERE EMP_PROJ.project_code = PROJ.project_code
                          )
       );

5)如果测试发现非法数据，则回滚提交事务。

如果约束起作用，事务就会回滚，数据完整性已经得到维护(尽管您可能希望更优雅地处理这样的验证失败:)

要将员工从项目中删除，需要使用类似的过程，以防止在项目中删除最后剩余的指定员工时发生的情况(是阻止员工被撤职还是应该删除该项目？问你的设计师:)

因为只有通过执行这种过程代码才能确保数据完整性，所以每个人都可以方便地将其封装在数据库中的PROCEDURE对象中，然后将PROCEDURE上的“执行”权限授予用户(而不是在底层表上授予增强的权限)。若要强制一组用户(例如，最终用户应用程序)仅使用PROCEDURE更新数据，则应取消他们对底层表的更新权限。这可能需要提供进一步的“助手”功能，例如为项目分配雇员和删除项目。如果你相信“通过存储过程访问所有数据库”的思想，你无论如何都会这么做的。