ORACLE：WHERE 中的参数引用不起作用

Question

我在oracle 10g中创建了一个简单的静态函数来根据对象的pk获取对象的引用。

STATIC FUNCTION getRef(nome IN VARCHAR2) 
RETURN REF folder_typ IS

  fl_r REF folder_typ := null;

BEGIN

   SELECT REF(fl) 
     INTO fl_r
     FROM folder_tab fl
    WHERE fl.nome = nome;

   RETURN fl_r;

END getRef;

这给了我一个错误，因为它无法获取行。 如果安装了 WHERE fl.nome = nome; 我写 WHERE fl.nome = 'folder1'; ——它可以工作。

我认为我没有以正确的方式使用该参数。 我该如何使用它？

Answer 1

这是一个范围界定问题。

这是我对您的设置的版本，是根据您发布的内容推断出来的。请注意，参数名称与属性名称不同。

create or replace type folder_t as object
(name varchar2(128))
/

create table folders of folder_t
/

create or replace function getRef
    (nome in varchar2)
    return ref folder_t
is
    fl_r REF folder_t;
begin
    select ref(fl)
    into fl_r
    from folders fl
    where fl.name = nome;

    return fl_r;
end  getRef;
/

正如你所看到的，给定这个测试数据……

SQL> insert into folders values (folder_t('temp'))
  2  /

1 row created.

SQL> insert into folders values (folder_t('work'))
  2  /

1 row created.

SQL>

我可以查询两个 REF：

SQL> select getRef('temp') from dual
  2  /

GETREF('TEMP')
--------------------------------------------------------------------------------
00002802091051432318864AF594741916D743E1291CF597373A4F4D7A93F159DA53A73FC0010372
2D0000


SQL> select getRef('work') from dual
  2  /

GETREF('WORK')
--------------------------------------------------------------------------------
0000280209F31778C18D5740FBA0CB90929E1B6FBD1CF597373A4F4D7A93F159DA53A73FC0010372
2D0001


SQL>

但是，如果我更改函数声明，使参数名称与属性名称相同，则会发生这种情况：

SQL> create or replace function getRef
  2      (name in varchar2)
  3      return ref folder_t
  4  is
  5      fl_r REF folder_t;
  6  begin
  7      select ref(fl)
  8      into fl_r
  9      from folders fl
 10      where fl.name = name;
 11
 12      return fl_r;
 13  end  getRef;
 14  /

Function created.

SQL> select getRef('temp') from dual
  2  /

GETREF('TEMP')
------------------------------------------------------------


SQL>

SQL 引擎应用作用域从桌子向外。因为不合格的 NAME 与表中的列匹配，所以它不会检查是否也存在该名称的参数。这就是为什么给参数起一个独特的名称是个好主意。就我个人而言，我更喜欢在参数前加上 P_ 前缀的做法，这样参数就不会与局部变量或对象名称发生冲突。

Answer 2

最佳实践是始终提供所有变量的范围，而不仅仅是表别名中的变量：

FUNCTION getRef(nome IN VARCHAR2) 
RETURN REF folder_typ IS
  fl_r REF folder_typ := null;
BEGIN

   SELECT REF(fl) 
     INTO fl_r
     FROM folder_tab fl
    WHERE fl.nome = getRef.nome;

   RETURN fl_r;
END getRef;