Oracle-该范围中不存在名称为X的函数 [英] Oracle - no function with name X exists in this scope

问题描述

该函数显然存在，因为我可以使用SQL Developer导航到该函数，并且可以很好地进行编译，但是当我尝试使用带有或不带有"call"的函数时，它会抛出:

The function is clearly there, because I can navigate to it using SQL Developer and it compiles all fine, but when I try to use the function with or without "call", it throws:

错误(36,24):PLS-00222:在此不存在名称为"x"的函数 范围

Error(36,24): PLS-00222: no function with name 'x' exists in this scope

该函数的外观如下:

create or replace function testfunction
  (
    somevalue in varchar2 
  )
  return varchar2
  AS
  cursor testcursor IS 
  select column1, column2 from table1 t
  where t.column1 = somevalue; 
  testcursorrec testcursor %rowtype;
  messaget VARCHAR2(500);
  begin
       open testcursor ; 
       fetch testcursor into testcursorrec ; 
       close testcursor ; 
       messaget := testcursor.column1;
      return messaget ;
  end;

这就是我的称呼方式

messaget := testfunction(somevalue); 

其中messageT和somevalue都声明为varchar2类型.

where both messageT and somevalue are declared as varchar2 type.

函数内部不允许使用游标吗?

Are cursors not allowed inside function or something like that?

推荐答案

错误将是messaget := testcursor.column1;，因为此时关闭了光标(您应该只使用testcursorrec.column2.

the error would be messaget := testcursor.column1; as the cursor is closed by then (you should just use testcursorrec.column2.

您所编写的代码不会检查任何行，也不会检查重复的行.您可以将其简化为

you're code isn't checking for no rows, nor duplicate rows. you can simplify this to

create or replace function testfunction
  (
    somevalue in table1.column1%type
  )
  return table1.column2%type
  AS
  messaget table1.column2%type; -- use %type where possible.
  begin
    select t.column2
      into messaget
      from table1 t
     where t.column1 = somevalue
       and rownum = 1;--only if you dont care if theres 2+ rows. 
    return messaget;
  exception 
    when no_data_found
    then 
      return null; -- if you want to ignore no rows.
  end;

这篇关于Oracle-该范围中不存在名称为X的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！