oracle - 编译PL/SQL函数时出错

Question

CREATE OR REPLACE FUNCTION employer_details_func
   RETURN VARCHAR(20);
 IS 
   e_name VARCHAR(20); 
 BEGIN 
    SELECT emp_name INTO e_name
    FROM employees WHERE emp_no = '5';
    RETURN e_name;
 END employer_details_func;

编译上面的程序时出现此错误

Error(2,18): PLS-00103: Encountered the symbol "(" when expecting one of the following: . @ % ; is authid as cluster order using external character deterministic parallel_enable pipelined aggregate result_cache

UPDATE :

CREATE OR REPLACE FUNCTION employer_details_func
   RETURN VARCHAR2(20);
 IS 
   e_name VARCHAR2(20); 
 BEGIN 
    SELECT emp_name INTO e_name FROM employees WHERE emp_no ='5';
    RETURN e_name;
 END employer_details_func;

错误:

Error(2,19): PLS-00103: Encountered the symbol "(" when expecting one of the following:     . @ % ; is authid as cluster order using external character    deterministic parallel_enable pipelined aggregate    result_cache 

我的代码有什么问题？请告诉我。

Answer 1

使用RETURN VARCHAR而不是RETURN VARCHAR(20);。

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm上的Oracle文档说RETURN子句...数据类型不能指定长度，精度或小数位数...

编辑

我使用此代码在SQL Fiddle上再次检查了它，它似乎可以正常工作(返回bob):

CREATE TABLE employees(emp_name VARCHAR2(20), emp_no VARCHAR2(20))
/
CREATE OR REPLACE FUNCTION employer_details_func
   RETURN VARCHAR
 IS 
   e_name VARCHAR(20); 
 BEGIN 
   SELECT emp_name INTO e_name FROM employees WHERE emp_no ='5';
   RETURN e_name;
 END employer_details_func;
/

insert into employees values('bob','5');
select employer_details_func() from dual;