sql：数据验证如何通过存储过程完成？(sql : How is data validation done through Stored procedures?)

sql：数据验证如何通过存储过程完成？(sql : How is data validation done through Stored procedures?)

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

说这个Oracle 参考 。 那么有人可以帮助我理解存储过程如何用于数据验证？

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

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数据类型的参数（或基于Oracle数据类型的用户定义类型）完成的。 只发生数据类型验证 - 必要时可以构建更深入的验证（IE：检查NUMBER数据类型中的小数）。 参数化查询通常比SQL注入更安全，但它确实取决于参数是什么以及查询的内容。

CREATE OR REPLACE PROCEDURE example (IN_VALUE NUMBER) IS BEGIN SELECT t.* FROM TABLE t WHERE t.column = IN_VALUE; END;

在这个例子中，提交一个VARCHAR /字符串会导致一个错误 - 除NUMBER支持的内容之外的任何内容都会导致错误。 如果IN_VALUE数据类型不能隐式转换为TABLE.column的数据类型，你会得到一个错误。

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

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.

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.

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.