oracle创建函数编译错误,错误PLS-00103在Oracle中编译用户定义的函数

在存储过程中指定参数时,我们不能限制数据类型.也就是说,只使用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对格式掩码非常宽容.