PL/SQL 中的函数错误

Question

我尝试创建以下函数：

CREATE OR REPLACE
function CATEGORYTEST(n in number) return VARCHAR(200)
IS
  catname VARCHAR(200);
  BEGIN
  SELECT NAME AS PARENTCAT INTO CATNAME FROM CATEGORY_LANGUAGE WHERE CATEGORY_ID = (
      SELECT PARENT_ID
      FROM CATEGORY
      WHERE ID        = N
      AND CAT_TYPE_ID = 7
      AND IS_ACTIVE   = 1
      AND IS_DELETED  = 0
      )
      AND IS_ACTIVE = 1
      AND IS_DELETED = 0;
   RETURN CATNAME;   
end;

我收到以下错误：

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

我也尝试过 varchar2 但收到相同的错误。

Answer 1

试试这个：

CREATE OR REPLACE 
FUNCTION CATEGORYTEST(
   n IN NUMBER
) 
   RETURN VARCHAR2
IS
   catname VARCHAR2(200);
BEGIN
   SELECT NAME AS PARENTCAT
     INTO CATNAME
     FROM CATEGORY_LANGUAGE
    WHERE CATEGORY_ID = (SELECT PARENT_ID
                           FROM CATEGORY
                          WHERE ID = 'N'
                            AND CAT_TYPE_ID = 7
                            AND IS_ACTIVE   = 1
                            AND IS_DELETED  = 0)
      AND IS_ACTIVE = 1
      AND IS_DELETED = 0;

    RETURN CATNAME;
end CATEGORYTEST; 

您需要将字符串文字用单引号括起来，将 VARCHAR 更改为 VARCHAR2 以及其他一些小的更改。

顺便说一句，您的 catname 变量最好声明为：

catname CATEGORY_LANGUAGE.NAME%TYPE;

您可能还想查看也被灵活声明的函数的返回类型，例如：

RETURN CATEGORY_LANGUAGE.NAME%TYPE

希望它有帮助......

Answer 2

使用此标头：（

CREATE OR REPLACE function CATEGORYTEST(n in number) return VARCHAR

返回类型没有长度规范）