在 Oracle 中编译用户定义函数时出错 PLS-00103
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3398610/
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
Error PLS-00103 compiling user-defined function in Oracle
提问by Stew
I'm trying to create a user defined function in Oracle that will return a DATE when given a text argument containing a date substring. I've tried a couple ways of writing this, and all seem to throw the same error:
我正在尝试在 Oracle 中创建一个用户定义的函数，当给定包含日期子字符串的文本参数时，该函数将返回 DATE。我已经尝试了几种写法，但似乎都抛出了同样的错误：
CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2(50))
RETURN DATE DETERMINISTIC IS
BEGIN
RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','--'), 'YYYY-MM-DD'));
END;
the error:
错误：
FUNCTION lm_date_convert Compiled. 1/46
PLS-00103: Encountered the symbol "(" when expecting one of the following::= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
函数 lm_date_convert 已编译。1/46
PLS-00103：在预期以下情况之一时遇到符号“（”：：=。) , @% 默认字符 符号“:=”被替换为“(”以继续。
Any thoughts on this, and general UDF writing tips (and good references) are welcome! Thanks.
欢迎对此有任何想法，以及一般的 UDF 写作技巧（和好的参考资料）！谢谢。
回答by APC
We cannot restrict the datatype when specifying parameters in stored procedures. That is, just use VARCHAR2 rather than VARCHAR2(50).
在存储过程中指定参数时，我们不能限制数据类型。也就是说，只需使用 VARCHAR2 而不是 VARCHAR2(50)。
Just to prove I'm reproducing your problem ...
只是为了证明我正在重现你的问题......
SQL> CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2(50))
2 RETURN DATE DETERMINISTIC IS
3 BEGIN
4 RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','--'), 'YYYY-MM-DD'));
5 END;
6 /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION LM_DATE_CONVERT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/49 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
SQL>
Now to fix it:
现在修复它：
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2)
2 RETURN DATE DETERMINISTIC IS
3 BEGIN
4 RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','--'), 'YYYY-MM-DD'));
5* END;
SQL> r
1 CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2)
2 RETURN DATE DETERMINISTIC IS
3 BEGIN
4 RETURN(TO_DATE(REGEXP_REPLACE(lm_date_in, '([[:digit:]]{2})[-/.]*([[:digit:]]{2})[-/.]*([[:digit:]]{4})','--'), 'YYYY-MM-DD'));
5* END;
Function created.
SQL>
"If you really do want a VARCHAR2(50) then declare a type of VARCHAR2(50) and use the type."
“如果你真的想要一个 VARCHAR2(50)，那么声明一个 VARCHAR2(50) 类型并使用该类型。”
Declaring a SQL TYPE to enforce sizing is a bit of overkill. We can declare SUBTYPEs in PL/SQL but their sizes are not actually enforced in stored procedure signatures. However there are workarounds as I discuss in this other thread.
声明一个 SQL TYPE 来强制调整大小有点矫枉过正。我们可以在 PL/SQL 中声明 SUBTYPE，但它们的大小实际上并未在存储过程签名中强制执行。但是，正如我在另一个线程中讨论的那样，有一些解决方法。
As an aside, why are you using Regex to solve this problem? Or rather, what problem are you trying to solve which cannot be solved with TO_CHAR and TO_DATE? Oracle's pretty forgiving with format masks.
顺便说一句，你为什么使用正则表达式来解决这个问题？或者更确切地说，您要解决哪些问题是 TO_CHAR 和 TO_DATE 无法解决的？Oracle 对格式掩码非常宽容。
