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

sql : How is data validation done through Stored procedures?

提问by SuperMan

" Stored procedures are typically used for data validation or to encapsulate large, complex processing instructions that combine several SQL queries."

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

Says this Oracle reference. So can someone help me understand by putting in real world examples how stored procedures are used fro data validation ?

说这个 Oracle参考。那么有人可以通过放入真实世界的示例来帮助我理解如何使用存储过程进行数据验证吗？

采纳答案by OMG Ponies

Data validation occurs because to pass data into a stored procedure, it's done via parameters which are explicitly set to Oracle data types (or user defined types, which are also based on Oracle data types). Only validation of the data type occurs - more in-depth validation has to be constructed if necessary (IE: checking for decimals in a NUMBER data type). Parameterized queries are generally more safe from SQL injection, but it really depends on what the parameters are and what the query is doing.

发生数据验证是因为要将数据传递到存储过程，这是通过显式设置为 Oracle 数据类型（或用户定义的类型，也基于 Oracle 数据类型）的参数完成的。仅发生数据类型的验证 - 如有必要，必须构建更深入的验证（即：检查 NUMBER 数据类型中的小数）。参数化查询通常更安全，不受 SQL 注入的影响，但这实际上取决于参数是什么以及查询在做什么。

 CREATE OR REPLACE PROCEDURE example (IN_VALUE NUMBER) IS

 BEGIN

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

 END;

In this example, submitting a VARCHAR/string will result in an error - anything other than what NUMBER supports will result in an error. And you'll get an error if the IN_VALUE data type can't be implicitly converted to the data type of TABLE.column.

在此示例中，提交 VARCHAR/字符串将导致错误 - 除了 NUMBER 支持的任何内容都将导致错误。如果无法将 IN_VALUE 数据类型隐式转换为 TABLE.column 的数据类型，则会出现错误。

A stored procedure encapsulates a transaction, which is what allows complex processing instructions (meaning, more than one SQL query). Transaction handling (IE: having to explicitly state "COMMIT" or "ROLLBACK") depends on settings.

一个存储过程封装了一个事务，它允许复杂的处理指令（意思是多个 SQL 查询）。事务处理（即：必须明确声明“COMMIT”或“ROLLBACK”）取决于设置。

回答by Tony Andrews

Validation can mean a number of things, and can be done in the database in various ways:

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

• Column datatypes are themselves a form of validation: NUMBER columns accept only valid numbers, etc.
• Primary key, unique and foreign key constraints perform validation
• Check constraints perform other simple one-row validations such as:
  • END_DATE > START_DATE
  • SALARY > 0
  • JOB = SALESMAN OR COMMISSION IS NULL

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

However, there are more complex validation rules that cannot be enforced by any of the above, such as: - SALARY <= (SELECT max_sal FROM config_table) - emp.start_date BETWEEN start_date AND end_date of the department they are assigned to

但是，还有更复杂的验证规则不能由上述任何规则强制执行，例如： - SALARY <= (SELECT max_sal FROM config_table) - emp.start_date BETWEEN start_date AND end_date 他们分配到的部门

There are various ways to enforce these rules, including database triggers, but often the preferred method is to create a stored procedure, often known as an "API" to perform the validation and action e.g.

有多种方法可以强制执行这些规则，包括数据库触发器，但通常首选的方法是创建一个存储过程，通常称为“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; 

The application can then just call this procedure instead of performing the update directly and all the necessary validation will be performed. In fact, the application would probably haveto call this procedure - direct insert into the table would probably be disabled.

然后应用程序可以只调用此过程而不是直接执行更新，并且将执行所有必要的验证。事实上，应用程序可能不得不调用这个过程——直接插入表可能会被禁用。

回答by onedaywhen

In an ideal world, your DBMS of choice would be relationally complete, allow you to write constraints of arbitrary complexity and support multiple assignment to allow your database to be updated at all times (i.e. without deferring or disabling constraints) using simple statements. In the real world we have SQL.

在理想情况下，您选择的 DBMS 将是关系完备的，允许您编写任意复杂性的约束并支持多重赋值，以允许使用简单的语句随时更新您的数据库（即，无需延迟或禁用约束）。在现实世界中，我们有 SQL。

An ideal SQL product would be Full SQL-92 Standard compliant: support CREATE ASSERTION(schema-level constraints), allow subqueries in CHECKconstraints and support the deferring of constraints within a transaction to enable your database to be updated without disabling constraints. Sadly, Oracle has not yet attained this level of functionality. Therefore, in the real world we have to sometimes resort to procedural code to "manage" updates while maintaining data integrity.

理想的 SQL 产品应该是完全符合 SQL-92 标准的：支持CREATE ASSERTION（模式级约束），允许CHECK约束中的子查询，并支持在事务中推迟约束，以便在不禁用约束的情况下更新数据库。遗憾的是，Oracle 尚未达到这种功能级别。因此，在现实世界中，我们有时不得不求助于过程代码来“管理”更新，同时保持数据完整性。

Consider for example a true one-to-one relationship, being common enough, with the business rules as follows:

例如，考虑一个真正的一对一关系，它足够常见，其业务规则如下：

A database contains details of employees and projects in three relvars: EMP, PROJ and EMP_PROJ. Every project must be have at least one employee and every project attachment must refer to an existing project. When a project is created at least one employee must be simultaneously attached to it.

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

In Oracle, you can't write an ASSERTIONor CHECKto enforce the inter-table constraints, so the ability to defer constraints is of little consequence in this case.

在 Oracle 中，您不能编写ASSERTIONorCHECK来强制执行表间约束，因此在这种情况下延迟约束的能力无关紧要。

One approach that can work to write a PROCEDUREwith appropriate parameters to create a project and assign one employee to the project. Such a procedure would, in this order (pseudo code):

一种可以编写PROCEDURE具有适当参数的方法来创建项目并将一名员工分配给该项目。这样的程序将按此顺序（伪代码）：

1) begin transaction;

1) 开始交易；

2) insert into PROJ;

2) 插入PROJ；

3) insert into EMP_PROJ;

3) 插入EMP_PROJ；

4) Test for data that would fail the notional constraints e.g.

4) 测试不符合概念约束的数据，例如

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

5) If the test finds illegal data then rollback otherwise commit the transaction.

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

If the constraints bite then the transaction is rolled back and data integrity has been maintained (though you perhaps would want to handle such a validation failure more gracefully :)

如果约束咬住，那么事务将回滚并保持数据完整性（尽管您可能希望更优雅地处理此类验证失败：）

A similar procedure would be required to remove an employee from a project to prevent the scenario when the last-remaining assigned employee is removed from the project (should the employee removal be prevented or should the project be deleted? ask your designer :)

从项目中删除一名员工需要类似的程序，以防止从项目中删除最后剩余的分配员工的情况（应该阻止员工删除还是应该删除项目？询问您的设计师:)

Because data integrity can only be ensured by executing such procedural code, it is convenient for everyone to encapulate it in a PROCEDUREobject in the database then grant 'execute' privileges on the PROCEDUREto users (rather than grant enhanced privileges on the underlying tables). To force a group of users (e.g. end user applications) to only use the PROCEDUREto update the data, their update privileges on the underlying tables should be revoked. This may require further 'helper' functions to be provided e.g. to assign employees to a project and to delete a project. If you buy into the "all database access through stored procs" school of thought you would be doing this anyway.

因为只有通过执行这样的过程代码才能确保数据完整性，所以每个人都可以方便地将其封装PROCEDURE在数据库中的一个对象中，然后授予用户“执行”权限PROCEDURE（而不是授予对底层表的增强权限）。要强制一组用户（例如最终用户应用程序）仅使用PROCEDURE更新数据，应撤销他们对基础表的更新权限。这可能需要提供进一步的“助手”功能，例如将员工分配到项目和删除项目。如果您接受“通过存储过程访问所有数据库”的想法，无论如何您都会这样做。