函数内部的错误参数(oracle)

Question

我的基本职能是：

create or replace function t_owner(tname in varchar2, oname out varchar2)
return varchar2
is
begin
  select owner into oname from table where table_name = 'tname';
  return oname;
end;

select t_owner('table_test') from dual;

当我这么说的时候，我就明白了：

ORA-06553:请-306:调用“T_OWNER”的参数数目或类型错误

Answer 1

函数不应该有OUT参数；它们无论如何都会返回该值。所以：

create or replace function t_owner(tname in varchar2)
  return varchar2  
is
  oname table.owner%type;       --> declare a local variable which will be returned
begin
  select owner into oname from table where table_name = tname;
  return oname;
end;

如果要使用OUT参数，请切换到过程：

create or replace procedure t_owner(tname in varchar2, oname out varchar2)
is
begin
  select owner into oname from table where table_name = tname;
end;

你会把它叫做

declare
  l_out table.owner%type;
begin
  t_owner('table_test', l_out);
  dbms_output.put_line(l_out);
end;

Answer 2

oname应该是局部变量，而不是输出参数，tname不应该作为字符串引用，而应该作为输入参数引用。

create or replace function t_owner(tname in varchar2)
return varchar2
is
    oname table.owner%type;
begin
    select owner into oname 
    from table 
    where table_name = tname;

    return oname;
end;

select t_owner('table_test') from dual;