PL/SQL 脚本中的本地函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21856832/
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
local function inside PL/SQL script
提问by user1630809
I'm trying to execute this code in Oracle 10 SQL Developer:
我正在尝试在 Oracle 10 SQL Developer 中执行此代码：
FUNCTION is_valid_date (p_val in VARCHAR2, p_format IN VARCHAR2 )
RETURN numeric IS
l_date VARCHAR2(100);
BEGIN
l_date := TO_date( p_val, p_format );
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END is_valid_date;
BEGIN
DBMS_OUTPUT.PUT_LINE(is_valid_date('20120101', 'YYYYMMDD' ));
END;
but I get a generic error without any specific Oracle code, as if it is a syntax problem.
但是我得到一个没有任何特定 Oracle 代码的通用错误，好像这是一个语法问题。
I need to check if a date is valid and, as there is no Oracle built in function for that, I have defined it inside my script (I don't want it to be global or stored somewhere).
我需要检查日期是否有效，并且由于没有为此内置的 Oracle 函数，我在脚本中定义了它（我不希望它是全局的或存储在某处）。
Edit:
编辑：
I have found a solution on an oracle forum using oracle regexp, instead of a function. My script is:
我在 oracle 论坛上找到了一个使用 oracle regexp 而不是函数的解决方案。我的脚本是：
BEGIN
select * from mytable where not REGEXP_LIKE(mydatefield, '(((0[1-9]|[12]\d|3[01])\.(0[13578]|1[02])\.((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\.(0[13456789]|1[012])\.((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\.02\.((19|[2-9]\d)\d{2}))|(29\.02\.((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))')
END;
where mydatefield is in the format DD.MM.YYYY
其中 mydatefield 的格式为 DD.MM.YYYY
回答by Alex Poole
If that's your entire script, you're missing the DECLAREkeyword at the start of your anonymous block:
如果那是您的整个脚本，那么您将缺少匿名块开头的DECLARE关键字：
DECLARE
FUNCTION is_valid_date (p_val in VARCHAR2, p_format IN VARCHAR2 )
RETURN numeric IS
l_date VARCHAR2(100);
BEGIN
l_date := TO_date( p_val, p_format );
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END is_valid_date;
BEGIN
DBMS_OUTPUT.PUT_LINE(is_valid_date('20120101', 'YYYYMMDD' ));
END;
/
anonymous block completed
1
Without that you'll get a series of errors starting with
没有它，你会得到一系列的错误开始
Error starting at line : 1 in command -
FUNCTION is_valid_date (p_val in VARCHAR2, p_format IN VARCHAR2 )
Error report -
Unknown Command
... which I imagine is the 'generic error' you referred to.
...我想这是您提到的“一般错误”。
