oracle pl / sql函数名称解析错误 [英] oracle pl/sql function name resolution error

问题描述

我试图执行一个相当简单的功能。我想传入一个主机名到这个函数，我希望它获取该主机名并使用它来查找与该主机名关联的systemID并将其返回。

在你下面会看到我有一个pl / sql块，当我传递一个varchar到这个函数时，我调用一个名为 GET_SYSTEMID 的函数，我得到一个错误。当我硬编码功能相同的字符串我得到正确的结果回来。包含主机名的列有一个唯一的约束，所以如果我使用的是我的一个服务器的确切主机名（我确定）应该只有一个匹配的行。

这是我的调用块：

 声明
 sysid number; 
开始
 sysid：= Server.GET_SYSTEMID（'MyHost'）; 
 DBMS_OUTPUT.PUT_LINE（'SYSID是'|| sysid）; 
 END; 
  

如果我使用该块来调用此函数，则不起作用：

 功能GET_SYSTEMID（主机名varchar2）
返回号码
 IS 
 SysID号码; 
 BEGIN 
 SELECT mySystems.SYSTEMID 
 INTO SysID 
 FROM mySystems 
 where mySystems.HOSTNAME = Hostname; 
 
返回SysID; 
 END GET_SYSTEMID; 
  

当我运行上面的代码时，我收到以下错误消息：

 声明
 sysid number; 
开始
 sysid：= Server.GET_SYSTEMID（'MyHost'）; 
 DBMS_OUTPUT.PUT_LINE（'SYSID是'|| sysid）; 
 END; 
第3行错误
 ORA-01422：精确读取返回的行数多于
 ORA-06512：位于MySchema.Server行33 
 ORA-06512：在第12行
  

接下来的两个功能可以工作，但是因为它们对主机名进行了硬编码，所以他们不做我所需要的这个函数可以做到：

第一：

 功能GET_SYSTEMID（主机名varchar2）
 RETURN NUMBER 
 IS 
 SysID号; 
 tmp varchar2（8）：='MyHost';  - 应该与传入的值相同
 BEGIN 
 SELECT mySystems.SYSTEMID 
 INTO SysID 
 FROM mySystems 
 where mySystems.HOSTNAME = tmp; 
 
返回SysID; 
 END GET_SYSTEMID; 
  

第二：

 功能GET_SYSTEMID（主机名varchar2）
 RETURN NUMBER 
 IS 
 SysID号; 
 BEGIN 
 
 SELECT mySystems.SYSTEMID 
 INTO SysID 
 FROM mySystems 
 where mySystems.HOSTNAME ='MyHost';  - 应该是相同的值
 
返回SysID; 
 END GET_SYSTEMID; 
  

解决方案

问题是名称解析之一。

在参数主机名和主机名列中表的范围解决规则会引起大多数人的困惑。这就是为什么很多人推荐使用参数和局部变量的命名约定来区分它们与表名。例如，在我的代码中，我使用 p _ 作为参数名称的前缀， l _ 作为局部变量的前缀。

在你的代码中，当你有

  SELECT mySystems.SYSTEMID 
 INTO SysID 
 FROM mySystems 
 where mySystems.HOSTNAME = Hostname; 
  

主机名已解析为表，而不是参数。这会导致查询返回表中的每一行，其中 hostname 不为null，从而导致该错误。您可以显式地将参数名前缀为函数名称，以强制 hostname 解析为参数

  SELECT mySystems.SYSTEMID 
 INTO SysID 
 FROM mySystems 
 where mySystems.HOSTNAME = GET_SYSTEMID.Hostname; 
  

有效。但添加函数名称前缀通常会变得烦人。如果您采用前缀参数名称和局部变量名称的约定，您会得到类似于

  FUNCTION GET_SYSTEMID（p_hostname varchar2） 
 RETURN NUMBER 
 IS 
 l_sysID number; 
 BEGIN 
 SELECT mySystems.SYSTEMID 
 INTO l_sysID 
 FROM mySystems 
 where mySystems.HOSTNAME = p_hostname; 
 
返回l_sysID; 
 END GET_SYSTEMID; 
  

在我看来，这种方法也比较有效，并且倾向于比全部添加显式函数名称前缀更清晰这个地方。

I am trying to perform a fairly simple function. I want to pass in an hostname to this function, i want it to take that hostname and use it to find the systemID associated with that hostname and return it.

below you will see that i have a pl/sql block that is calling a function called GET_SYSTEMID when i pass in a varchar to this function i get an error back. when i hard code the same string inside the function i get the correct results back. The column holding the hostnames has a unique constraint so if i am using the exact hostname of one of my servers (i am for sure) there should only be one matching row.

This is my calling block:

Declare
    sysid number;
Begin
    sysid := Server.GET_SYSTEMID('MyHost');
    DBMS_OUTPUT.PUT_LINE('SYSID is '||sysid);    
END;

If i use that block to call this function it does not work:

FUNCTION GET_SYSTEMID(Hostname varchar2)
RETURN NUMBER
IS
    SysID number;
BEGIN 
    SELECT mySystems.SYSTEMID
    INTO SysID
    FROM mySystems
    where mySystems.HOSTNAME = Hostname;

    return SysID;
END GET_SYSTEMID;

When i run the above i get this error message:

Declare
    sysid number;
Begin
    sysid := Server.GET_SYSTEMID('MyHost');
    DBMS_OUTPUT.PUT_LINE('SYSID is '||sysid);    
END;
Error at line 3
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "MySchema.Server", line 33
ORA-06512: at line 12

These next two do work but as they hard code the hostname they dont do what i need this function to do:

Number one:

FUNCTION GET_SYSTEMID(Hostname varchar2)
RETURN NUMBER
IS 
    SysID number;
    tmp varchar2(8) := 'MyHost';--should be identical to passed in value
BEGIN 
    SELECT mySystems.SYSTEMID
    INTO SysID
    FROM mySystems
    where mySystems.HOSTNAME = tmp;

    return SysID;
END GET_SYSTEMID;

Number Two:

FUNCTION GET_SYSTEMID(Hostname varchar2)
RETURN NUMBER
IS
    SysID number;
BEGIN 

    SELECT mySystems.SYSTEMID
    INTO SysID
    FROM mySystems
    where mySystems.HOSTNAME = 'MyHost';--should be identical to passed in value

    return SysID;
END GET_SYSTEMID;

解决方案

The problem is one of name resolution.

When you have a parameter hostname and a hostname column in the table that you're referencing, the scope resolution rules cause most people confusion. That's why many people recommend using a naming convention for parameters and local variables that differentiates them from table names. In my code, for example, I use p_ to prefix parameter names and l_ to prefix local variables.

In your code, when you have

SELECT mySystems.SYSTEMID
INTO SysID
FROM mySystems
where mySystems.HOSTNAME = Hostname;

hostname is resolved as the column in the table, not the parameter. This causes the query to return every row in the table where hostname is not null which causes the error. You can explicitly prefix the parameter name with the function name to force hostname to resolve to the parameter

SELECT mySystems.SYSTEMID
INTO SysID
FROM mySystems
where mySystems.HOSTNAME = GET_SYSTEMID.Hostname;

That works. But adding the function name prefix generally gets annoying. If you adopt the convention of prefixing parameter names and local variable names, you'd get something like

FUNCTION GET_SYSTEMID(p_hostname varchar2)
RETURN NUMBER
IS
    l_sysID number;
BEGIN 
    SELECT mySystems.SYSTEMID
    INTO l_sysID
    FROM mySystems
    where mySystems.HOSTNAME = p_hostname;

    return l_sysID;
END GET_SYSTEMID;

That also works and tends (in my mind) to be clearer than adding explicit function name prefixes all over the place.

这篇关于oracle pl / sql函数名称解析错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！