如何使用SQL查询实现这些验证 [英] How to implement these validations using SQL query

问题描述

我有一个场景今天我被困了一点。我在源代码Or​​acle源表（示例数据）中有以下数据

 ID，NAME，SALARY，BIRTHDAY 
 1，ABHIJIT，2000,17 / 12/1990 
 2，ROHIT，-2000,13 / 11/1988 
 3，MOHIT，500,2075-575-43 

现在薪水第二行中的是negetive，第三行中的 BIRTHDAY 是无效格式（有效是MM / dd / yyyy）。第2行和第3行都应该转到INVALID_EMPLOYEE，第1行应该转到VALID_EMPLOYEE。



源文件中的日期格式为dd / mm / yyyy。我必须转换为MM / dd / yyyy格式，还必须检查源文件中的日期格式是否为dd / mm / yyyy。



工资不应小于0.源表所有列都在String和目标表 ID 是整数， NAME 为VARCHAR2（255）， SALARY 是NUMBER和 BIRTHDAY 是DATE。



我已经在我的Project ETL工具中处理了所有这些。因此，我试图在Query中推送所有这些以提高性能。非常感谢任何帮助



我尝试过：



我已经完成了那些使用我的ETL。

解决方案

不要试图将其推送到数据库 - 它几乎肯定不会提高性能，但实际上会使情况变得更糟，因为你需要设置一个往返数据库来验证它，并且这比在插入值时在本地进行验证要慢。



特别是，你的日期是一个问题，因为就数据库而言，不应该得到一个无效的日期 - 你的所有查询都应该使用参数化的查询来转移一个验证的DateTime对象 - 获取DB的错误日期的唯一方法是将其作为字符串传递，这意味着字符串连接会使整个系统容易受到SQL注入问题的影响，这可能会损坏整个数据库。



和n存在关注点分离POV：负薪水是业务层关注点，而不是DB关注点，并且应该在业务甚至表示层进行验证和处理，而不是提供给数据层，到时候为此做太多为时已晚。



我认为您需要重新考虑这一点 - 如果可以进行SQL注入，可能还需要相当多的其他应用程序。

I have a scenario Today where I am stucked Up little bit. I have data like this below in my Source Oracle Source Table(Sample Data)

ID,NAME,SALARY,BIRTHDAY
1,ABHIJIT,2000,17/12/1990
2,ROHIT,-2000,13/11/1988
3,MOHIT,500,2075-575-43

Now Salary in the 2nd Row is negetive and BIRTHDAY in the 3rd Row is an Invalid Format(Valid is MM/dd/yyyy). Both 2nd and 3rd Row should go to INVALID_EMPLOYEE and 1st record should go to VALID_EMPLOYEE.

In the Source File Date Format is coming as dd/mm/yyyy. Which I have to convert to MM/dd/yyyy format and also have to check whether Date Format incoming in Source File is dd/mm/yyyy or not.

Salary should not be less than 0. Source Table All columns are in String and in Target Table ID is Integer,NAME as VARCHAR2(255), SALARY is NUMBER and BIRTHDAY is DATE.

I have handled all this in my Project ETL Tool. So I am trying to push all this in Query to improve performance.Any help is much appreciated

What I have tried:

I have done already those using my ETL.

解决方案

Don't try to push this to the DB - it almost certainly won't improve performance, but actually make it worse, because you need to set up a round trip to the DB to validate it, and that's going to be slower than doing validations locally when you insert values.

In particular, your date is a problem, because it shouldn't be possible to get an invalid date as far as the DB - all your queries should be using parameterised queries which transfer a validated DateTime object - the only way to get a "bad date" to the DB is to pass it as a string, and that implies string concatenation which leaves your whole system vulnerable to SQL Injection problems which can damage your whole DB.

And then there is the separation of concerns POV: a negative salary is a business layer concern, not a DB concern, and again, should be validated and dealt with at the business or even presentation layer, not fed to the data layer, by which time it's too late to do much about it.

I think you need to rethink this - and probably a fair amount of the rest of your application if SQL Injection is possible.

这篇关于如何使用SQL查询实现这些验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！