我正在尝试在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})','\3-\1-\2'),'YYYY-MM-DD'));
END;
错误：
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.
欢迎任何关于此的想法,以及一般的UDF写作技巧(以及好的参考资料)！谢谢.
解决方法
在存储过程中指定参数时,我们不能限制数据类型.也就是说,只使用VARCHAR2而不是VARCHAR2(50).
只是为了证明我正在复制你的问题……
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,'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>
现在来解决它：
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,'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,'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.”
声明sql TYPE以强制执行大小调整有点过分.我们可以在PL / sql中声明SUBTYPE,但它们的大小实际上并未在存储过程签名中强制执行.但是有一些解决方法是I discuss in this other thread.
顺便说一下,为什么使用正则表达式解决这个问题？或者更确切地说,您尝试解决的问题是TO_CHAR和TO_DATE无法解决的问题？ Oracle对格式掩码非常宽容.
