如何验证作为参数PL/SQL给出的YYYYMMDD日期 [英] How to validate YYYYMMDD date given as parameter PL/SQL

问题描述

我有一个名为ADD_COMPLEX_SALE的SP.它有4个参数:CUSTID，产品ID，数量和日期(varchar2).

I have a SP called ADD_COMPLEX_SALE. It gets 4 parameters : custid, prod id, qty and date(varchar2).

我需要验证日期并检查日期格式是否正确.它以YYYYMMDD的形式发送到过程.

I need to validate the date and check that it is in the right format. It is being sent to the procedure as YYYYMMDD.

这是我到目前为止所拥有的:

This is what I have so far:

IF (LENGTH(pdate) != 8) THEN
    raise_application_error (-20093,' Date not valid');
ELSIF (LENGTH(pdate) = 8)THEN
    vDATE := CONVERT(VARCHAR(10), pdate(), 111) AS [YYYY/MM/DD];
  END IF;

IF (pdate != 'YYYY/MM/DD') THEN
    raise_application_error (-20093,' Date not valid');
  END IF; 

基本上，这个想法也将其转换为实际"日期格式，因此我可以确定它的格式正确.除了我得到这个错误.还是非常新的pl/sql，因此将不胜感激.

Basically the idea is too convert it to a 'real' date format so that I can know for sure it is in the proper format. Except I get this error. Still very new pl/sql so any help will be appreciated.

错误(42,49):PLS-00103:预期以下情况之一时遇到符号"AS":.(*％& =-+; in处的是mod余数，而不是rem<>或！=或〜=> =< =<>或类似| 2多集成员子多集

Error(42,49): PLS-00103: Encountered the symbol "AS" when expecting one of the following: . ( * % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

更新:

将代码更改为以下内容:

Changed code to the following :

 vDATE := TO_DATE(pdate, 'yyyymmdd');
 IF (pdate != vDATE) THEN
   raise_application_error (-20093,' Date not valid');
 END IF;
 IF (LENGTH(vDATE) != 8) THEN
    raise_application_error (-20093,' Date not valid');
 END IF;

现在出现此错误:

ORA-20000:发生了另一个错误ORA-01861:文字与格式字符串不匹配

ORA-20000: Another error occured ORA-01861: literal does not match format string

上次更新错误是由于参数为varchar2，转换完成后我忘记了插入vDATE而不是pdate.

Last update error was due to Parameter being varchar2 and me forgetting to INSERT vDATE instead of pdate after the conversion was done.

推荐答案

convert(varchar(10)，pdate()，111)似乎是尝试使用SQL Server 转换功能.在Oracle中这是行不通的.

convert(varchar(10), pdate(), 111) appears to be an attempt to use the SQL Server convert function. That's not going to work in Oracle.

我会做类似的事情

DECLARE
  l_dt date;
BEGIN
  l_dt := to_date( pdate, 'yyyymmdd' );
EXCEPTION
  WHEN others
  THEN
    raise_application_error( -20001, pdate || ' is not a date in the format YYYYMMDD' );
END;

当然，如果您想进行多次检查，以便在长度不正确的情况下抛出不同的异常，或者添加一些检查以确保日期合理(即必须在最近的100年内或不超过将来的100年，依此类推)，您可以在 to_date 转换之后执行此操作.

Of course, if you want to do multiple checks so that you can throw a different exception if the length is incorrect or add some checks to ensure that the date is reasonable (i.e. must be within the last 100 years or no more than 100 years in the future, etc.) you could do that after the to_date conversion.

这篇关于如何验证作为参数PL/SQL给出的YYYYMMDD日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！