时间戳

首页 > 解决方案 > 使用 SQL / PL SQL 验证日期

sql - 使用 SQL / PL SQL 验证日期

问题描述

我在验证代码中的日期时遇到问题。它并没有按应有的方式工作。

错误的部分与此有关。

ADD_COMPLEX_SALE_TO_DB - GENERATED UNEXPECTED EXCEPTION
ADD_COMPLEX_SALE_TO_DB - DATE NOT VALID EXCEPTION INCORRECT (WRONG MONTH)
ADD_COMPLEX_SALE_TO_DB - DATE NOT VALID EXCEPTION INCORRECT (WRONG DAY)

我的测试代码是这样的。我有一个不同的输出块，没有问题。

CREATE OR REPLACE PROCEDURE ADD_COMPLEX_SALE_TO_DB (pcustid Number, pprodid Number, pqty Number, pdate Varchar2) AS
    QTY_OUTSIDE_RANGE EXCEPTION;
    STATUS_NOT_OK EXCEPTION;
    INVALID_SALE_DATE EXCEPTION;
    vCUST CUSTOMER%ROWTYPE;
    vPROD PRODUCT%ROWTYPE;
    SALEID SALE%ROWTYPE;
    vPROD_ERROR INT := 0;
BEGIN
    IF 1 > pqty OR pqty > 999 THEN
    RAISE QTY_OUTSIDE_RANGE;
    END IF;
    
    SELECT * INTO vCUST
    FROM CUSTOMER
    WHERE CUSTID = pcustid;
    
    vPROD_ERROR := 1; 
    
    IF vCUST.STATUS != 'OK' THEN
    RAISE STATUS_NOT_OK;
    END IF; 

    SELECT * INTO vPROD
    FROM PRODUCT
    WHERE PRODID = pprodid;
    
    IF (LENGTH(pdate) <> 8) THEN 
        RAISE INVALID_SALE_DATE;
    END IF;
    
    INSERT INTO SALE (SALEID, CUSTID, PRODID, QTY, PRICE, SALEDATE) 
    VALUES (SALE_SEQ.NEXTVAL, pcustid, pprodid, pqty, vPROD.SELLING_PRICE, pdate); 
        
    UPD_CUST_SALESYTD_IN_DB(pcustid, (pqty*vPROD.SELLING_PRICE));   
    UPD_PROD_SALESYTD_IN_DB(pprodid, (pqty*vPROD.SELLING_PRICE));

EXCEPTION
    WHEN QTY_OUTSIDE_RANGE THEN
        RAISE_APPLICATION_ERROR (-20234, 'Sale Quantity outside valid range');
    WHEN STATUS_NOT_OK THEN 
        RAISE_APPLICATION_ERROR(-20245, 'Customer status is not OK');
    WHEN INVALID_SALE_DATE THEN
        RAISE_APPLICATION_ERROR(-20252, 'Date not valid');
    WHEN NO_DATA_FOUND THEN
        IF vPROD_ERROR = 0 THEN
            RAISE_APPLICATION_ERROR (-20263, 'Customer ID not found');
        ELSE 
            RAISE_APPLICATION_ERROR (-20271, 'Product ID not found');
        END IF;
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, SQLERRM);
END;

以下是表创建语句。

CREATE TABLE CUSTOMER (
CUSTID  NUMBER
, CUSTNAME  VARCHAR2(100)
, SALES_YTD NUMBER
, STATUS    VARCHAR2(7)
, PRIMARY KEY   (CUSTID) 
);
CREATE TABLE PRODUCT (
PRODID  NUMBER
, PRODNAME  VARCHAR2(100)
, SELLING_PRICE NUMBER
, SALES_YTD NUMBER
, PRIMARY KEY   (PRODID)
);
CREATE TABLE SALE (
SALEID  NUMBER
, CUSTID    NUMBER
, PRODID    NUMBER
, QTY   NUMBER
, PRICE NUMBER
, SALEDATE  DATE
, PRIMARY KEY   (SALEID)
, FOREIGN KEY   (CUSTID) REFERENCES CUSTOMER
, FOREIGN KEY   (PRODID) REFERENCES PRODUCT
);

我该如何去测试日期是否有效？看来我需要以某种方式分解并分别测试月/日/年，但我不确定如何最好地做到这一点。

标签： sqloracleplsql

解决方案

不要通过检查它的长度来检查日期。01/01/21 是有效日期，但 99/01/21 不是。oracle 数据库将尝试进行隐式转换，但将字符串更改为日期并不是一个好习惯。

既然你得到一个字符串，那么传递数据的人必须知道传递日期的格式。如果是 2021 年 2 月 9 日，他会传递 09/02/21、02/09/21、09-FEB-21还是 2021 年 2 月 9 日？所有这些都是有效日期，因此您应该假设 date 传入日期参数​​的格式是已知的。使用已知的格式，您仍然可以获得无效的日期。假设您的日期格式是 DD/MM/YY，那么 09/02/21 是有效日期，但 50/02/21 不是，09/50/21 也不是。

用于TO_DATE将字符串转换为数据类型DATE。Oracle 有几个与日期相关的例外情况。

例子：

DECLARE
  l_date_input VARCHAR2(20) := '50/02/21';
  l_date DATE;
BEGIN
  l_date := TO_DATE(l_date_input,'DD/MM/YY');
END;
/
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 5
01847. 00000 -  "day of month must be between 1 and last day of month"

自己试试这个。将 '50/02/21' 更改为 '09/50/21' 并查看引发了什么异常。

现在，让我们将其放在一个仅处理分配的日期部分的示例中。请注意，此示例是一个匿名 pl/sql 块，但可以轻松地将其更改为过程。pragmaEXCEPTION_INIT允许将用户定义的异常名称与错误代码相关联，因此可以将以下示例中引发的错误映射到您自己的异常。

DECLARE
  l_date_input VARCHAR2(2000);
  l_date DATE;
  
  --exception thrown: ORA-01843: not a valid month
  e_not_a_valid_month EXCEPTION; 
  PRAGMA EXCEPTION_INIT (e_not_a_valid_month, -01843); 
  --exception thrown:  ORA-01847: day of month must be between 1 and last day of month
  e_not_a_valid_day EXCEPTION; 
  PRAGMA EXCEPTION_INIT (e_not_a_valid_day, -01847);   

BEGIN
  l_date_input := '01/99/21';
  l_date :=  TO_DATE(l_date_input,'DD/MM/YY');
EXCEPTION 
  WHEN e_not_a_valid_month THEN
    RAISE_APPLICATION_ERROR (-20234, 'DATE NOT VALID EXCEPTION INCORRECT (WRONG MONTH)');
  WHEN e_not_a_valid_day THEN
    RAISE_APPLICATION_ERROR (-20235, 'DATE NOT VALID EXCEPTION INCORRECT (WRONG DAY)');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20236, 'GENERATED UNEXPECTED EXCEPTION');
END;
/
ORA-20234: DATE NOT VALID EXCEPTION INCORRECT (WRONG MONTH)
ORA-06512: at line 17

推荐阅读