在 oracle 过程的参数中测试有效日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/967616/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Testing for a valid date in a oracle procedure's parameter
提问by Sam
Oracle 8 here.
甲骨文 8 在这里。
When passing a date to a procedure, I can pass a '' and nothing is thrown.
将日期传递给过程时，我可以传递一个 '' 并且不会抛出任何内容。
Testing for MYDATE := ''doesn't eval to true. Nor does LENGTH < 1. DBMS_OUTPUT shows nothing coming through the parameter.
测试MYDATE := ''不为真。也没有LENGTH < 1。DBMS_OUTPUT 显示没有任何内容通过参数。
Trying to pass '01-30-2009' (instead of 30-JAN-2009)throws an invalid date error.
尝试传递'01-30-2009' (instead of 30-JAN-2009)会引发无效的日期错误。
How is passing a zero length string valid?
传递零长度字符串如何有效？
How do I test for a valid date?
如何测试有效日期？
回答by Tom Hubbard
In later versions of Oracle the empty string is considered the same as NULL. That is probably what you are running into.
在 Oracle 的更高版本中，空字符串被视为与 NULL 相同。这可能就是您遇到的情况。
You may be able to set the parameter to not null and then it ought to error out. (As Jeffery Kemp noted in the comments, you can NOT use not null for a parameter)
您可以将参数设置为非空，然后它应该会出错。（正如 Jeffery Kemp 在评论中指出的，您不能使用 not null 作为参数）
As far as the invalid date error Oracle will implicitly cast a string to a date if it is in the format dd-mmm-yyyy. Otherwise you will have to run it through to_date with the proper mask.
就无效日期错误而言，如果字符串的格式为 dd-mmm-yyyy，则 Oracle 会将字符串隐式转换为日期。否则，您将不得不使用适当的掩码通过 to_date 运行它。
I am not familiar with Oracle 8 so I'm not sure what is new or not. Hopefully this helps.
我不熟悉 Oracle 8，所以我不确定什么是新的。希望这会有所帮助。
回答by Pop
Personally, I think a date is a date and a string is a string. I wish there was a way to disable implicit conversion. But if you control the program that's calling the procedure, you can try:
就个人而言，我认为日期是日期，字符串是字符串。我希望有一种方法可以禁用隐式转换。但是，如果您控制调用该过程的程序，则可以尝试：
call my_proc(to_date('01-30-2009','MM-DD-YYYY'));
instead of:
代替：
call my_proc('01-30-2009');
Otherwise, make your procedure receive a string and check the format inside the procedure:
否则，让你的过程接收一个字符串并检查过程中的格式：
create procedure my_proc(p_date_str in varchar2) is
v_dt date;
begin
if length(v_dt) != 10 then
raise_application_error(-20000,'Wrong date format',true);
end if;
v_dt := to_date(p_date_str,'MM-DD-YYYY');
... now use v_dt as a date ...
end;
/
