针对视图进行查询时，将忽略视图定义中的筛选子句

我有一个带有LDATE和SCHTYPEID列的表SCHEDULES。如果SCHTYPEID = 1，则LDATE包含数字格式的日期（例如，20170918今天的日期）。如果SCHTYPEID = 2，则LDATE包含0。

经常编写查询时，我会将LDATE转换为实际日期，并将其滤除零以：

：

SELECT TO_DATE(LDATE, 'YYYYMMDD') LDATE
FROM SCHEDULES
WHERE SCHTYPEID = 1;

但是，当我将其放入视图

时

CREATE VIEW FOO (THE_DATE)
AS SELECT TO_DATE(LDATE, 'YYYYMMDD')
  FROM SCHEDULES
  WHERE SCHTYPEID = 1;

和这样的查询：

SELECT *
FROM FOO
WHERE THE_DATE = TO_DATE(20170918, 'YYYYMMDD');

我收到错误" ORA-01840：输入值不足以进行日期格式"。像这样查询它：

SELECT * FROM FOO;

工作正常。但是，每当我尝试过滤它时，Oracle似乎都会忽略视图中SCHTYPEID上的过滤器，并且它包括带有SCHTYPEID = 2的记录，从而导致错误。

我有什么可以做的，以确保我只能用填充的LDATE列对行进行查询？

您可以使您的视图处理两种情况（即使WHERE子句将数据限制在SCHTYPEID = 1中），因此：

CREATE VIEW FOO(THE_DATE) AS
  SELECT CASE WHEN SCHTYPEID = 1 THEN TO_DATE(LDATE, 'YYYYMMDD') ELSE NULL END
    FROM SCHEDULES
   WHERE SCHTYPEID = 1;

CREATE VIEW FOO (THE_DATE)
AS SELECT TO_DATE(LDATE, 'YYYYMMDD')
  FROM SCHEDULES
  WHERE SCHTYPEID = 1
UNION ALL
SELECT TO_DATE('19000101', 'YYYYMMDD')
  FROM SCHEDULES
  WHERE SCHTYPEID = 2

将日期放入日期列

不会有任何伤害

即使使用CASE语句进行视图定义来有条件地施放ldate列，SQL语句也会导致错误。

您可以将日期放入DATE列中。这是最好的方法。
假设您不能做上述内容，您可以对此列有限制（也许是条件约束）。这可能是下一个最好的解决方案。
假设您无法在表中添加约束，某些应用程序围绕分配给各个字段的值集具有功能（例如，Oracle Apps描述flexfields属于此类别）。
假设您可能不需要/可以添加一个值集，您可能会有一个自定义实用程序包在事实之后检查数据。这将不是理想的。

这是我对这个问题的检查：

`SCOTT@dev>CREATE TABLE schedules
  2      AS
  3          ( SELECT
  4              hiredate ldate,
  5              to_number(TO_CHAR(hiredate,'YYYYMMDD') ) ldate_number,
  6              1 schtypeid
  7          FROM
  8              emp
  9          UNION ALL
 10          SELECT
 11              TO_DATE(NULL),
 12              to_number(NULL),
 13              2
 14          FROM
 15              dual
 16          UNION ALL
 17          SELECT
 18              hiredate,
 19              to_number(substr(
 20                  TO_CHAR(hiredate,'YYYYMMDD'),
 21                  1,
 22                  4
 23              ) ),
 24              1
 25          FROM
 26              emp
 27          );
Table SCHEDULES created.

 SCOTT@dev>SELECT
      2      COUNT(1)
      3  FROM
      4      schedules;
    COUNT(1)  
    29

创建一个实用程序包来检查/查看这些铸造异常。我借了许多人使用过的逻辑（请参阅以下链接）：

贾斯汀·凯夫（Justin Cave）的解决方案：如何处理to_date except in Select Statment中的异常以忽略这些行？

尼古拉斯·克拉斯诺夫（Nicholas Krasnov）的解决方案：在pl/sql代码中调用to_date时，确切的例外是什么例外

    SCOTT@dev>CREATE OR REPLACE PACKAGE util AS
      2      FUNCTION to_date_exception (
      3          p_char_literal   IN VARCHAR2,
      4          p_date_format    IN VARCHAR2
      5      ) RETURN VARCHAR2;
      6  
      7      FUNCTION my_to_date (
      8          p_char_literal   IN VARCHAR2,
      9          p_date_format    IN VARCHAR2
     10      ) RETURN DATE;
     11  
     12  END;
     13  /
    Package UTIL compiled
    SCOTT@dev>CREATE OR REPLACE PACKAGE BODY util AS
      2  
      3      FUNCTION to_date_exception (
      4          p_char_literal   IN VARCHAR2,
      5          p_date_format    IN VARCHAR2
      6      ) RETURN VARCHAR2 IS
      7          l_check_date      DATE;
      8          l_error_code      VARCHAR(20);
      9          l_error_message   VARCHAR2(200);
     10      BEGIN
     11          l_check_date := TO_DATE(p_char_literal,p_date_format);
     12           -- NULL will be returned when cast works
     13          RETURN NULL;
     14      EXCEPTION
     15          WHEN OTHERS THEN
     16              l_error_code := 'ORA' || TO_CHAR(sqlcode);
     17              l_error_message := sqlerrm;
     18              RETURN l_error_code;
     19      END;
     20  
     21      FUNCTION my_to_date (
     22          p_char_literal  IN VARCHAR2,
     23          p_date_format IN VARCHAR2
     24      ) RETURN DATE IS
     25          l_date   DATE;
     26      BEGIN
     27          l_date := TO_DATE(p_char_literal,p_date_format);
     28          RETURN l_date;
     29      EXCEPTION
     30          WHEN OTHERS THEN
     31              RETURN TO_DATE(NULL);
     32      END;
     33  
     34  END;
     35  /
    Package Body UTIL compiled
SCOTT@dev>SELECT
  2      util.to_date_exception(ldate_number,'YYYYMMDD') excptn,
  3      util.my_to_date(ldate_number,'YYYYMMDD') the_date_all,
  4      ldate
  5  FROM
  6      schedules
  7  WHERE
  8      schtypeid = 1;
EXCPTN    THE_DATE_ALL             LDATE                    
          17-DEC-1980 12:00:00 AM  17-DEC-1980 12:00:00 AM  
          20-FEB-1981 12:00:00 AM  20-FEB-1981 12:00:00 AM  
          22-FEB-1981 12:00:00 AM  22-FEB-1981 12:00:00 AM  
          02-APR-1981 12:00:00 AM  02-APR-1981 12:00:00 AM  
          28-SEP-1981 12:00:00 AM  28-SEP-1981 12:00:00 AM  
          01-MAY-1981 12:00:00 AM  01-MAY-1981 12:00:00 AM  
          09-JUN-1981 12:00:00 AM  09-JUN-1981 12:00:00 AM  
          09-DEC-1982 12:00:00 AM  09-DEC-1982 12:00:00 AM  
          17-NOV-1981 12:00:00 AM  17-NOV-1981 12:00:00 AM  
          08-SEP-1981 12:00:00 AM  08-SEP-1981 12:00:00 AM  
          12-JAN-1983 12:00:00 AM  12-JAN-1983 12:00:00 AM  
          03-DEC-1981 12:00:00 AM  03-DEC-1981 12:00:00 AM  
          03-DEC-1981 12:00:00 AM  03-DEC-1981 12:00:00 AM  
          23-JAN-1982 12:00:00 AM  23-JAN-1982 12:00:00 AM  
ORA-1840                           17-DEC-1980 12:00:00 AM  
ORA-1840                           20-FEB-1981 12:00:00 AM  
ORA-1840                           22-FEB-1981 12:00:00 AM  
ORA-1840                           02-APR-1981 12:00:00 AM  
ORA-1840                           28-SEP-1981 12:00:00 AM  
ORA-1840                           01-MAY-1981 12:00:00 AM  
ORA-1840                           09-JUN-1981 12:00:00 AM  
ORA-1840                           09-DEC-1982 12:00:00 AM  
ORA-1840                           17-NOV-1981 12:00:00 AM  
ORA-1840                           08-SEP-1981 12:00:00 AM  
ORA-1840                           12-JAN-1983 12:00:00 AM  
ORA-1840                           03-DEC-1981 12:00:00 AM  
ORA-1840                           03-DEC-1981 12:00:00 AM  
ORA-1840                           23-JAN-1982 12:00:00 AM  

28 rows selected.

原始视图定义：

SCOTT@dev>CREATE OR REPLACE VIEW FOO
  2  AS SELECT TO_DATE(ldate_number, 'FXYYYYMMDD') the_date,
  3      ldate
  4    FROM SCHEDULES
  5    WHERE SCHTYPEID = 1;
View FOO created.
SCOTT@dev>SELECT
  2      COUNT(1)
  3  FROM
  4      foo;
COUNT(1)  
28        

SCOTT@dev>SELECT
  2      *
  3  FROM
  4      foo
  5  WHERE
  6      the_date = TO_DATE(20170918,'YYYYMMDD');
Error starting at line : 1 in command -
SELECT
    *
FROM
    foo
WHERE
    the_date = TO_DATE(20170918,'YYYYMMDD')
Error report -
ORA-01840: input value not long enough for date format
**************************************

使用CASE语句提出的视图定义：

SCOTT@dev>CREATE OR REPLACE VIEW FOO(THE_DATE) AS
  2    SELECT CASE WHEN SCHTYPEID = 1 THEN TO_DATE(ldate_number, 'YYYYMMDD') ELSE NULL END
  3      FROM SCHEDULES
  4     WHERE SCHTYPEID = 1;
View FOO created.
SCOTT@dev>SELECT
  2      COUNT(1)
  3  FROM
  4      foo;
COUNT(1)
28

    SCOTT@dev>list
  1  SELECT
  2     the_date 
  3  FROM
  4      foo
  5  WHERE
  6*     the_date = TO_DATE(20170918,'YYYYMMDD')
SCOTT@dev>/
Error starting at line : 1 in command -
SELECT
   the_date 
FROM
    foo
WHERE
    the_date = TO_DATE(20170918,'YYYYMMDD')
Error report -
ORA-01840: input value not long enough for date format

在这里可以看到（使用Oracle 12C），我认为CASE语句方法存在问题，并且应通过确保不介入不良数据进入本专栏来解决潜在的问题。

相关内容

最新更新

热门标签：