从函数内的 SELECT 返回一个变量

2021-12-30 00:00:00 oracle11g oracle plsql

我正在尝试创建一个返回 varchar 的函数，其中一个字段形成一个选择，即聚合字段.我收到下一个错误:

I'm trying to create a function that return a varchar, one of the fields form a select, the aggregation field. I'm getting the next error: 

ORA-01422: exact fetch returns more than requested number of rows

我的理解是，选择在聚合之前生成不止一行，这会在尝试将它们放入k、s、类别"时触发错误

What I understand is that the select produce more than one row before aggregating and this triggers the error when trying to put them 'into k, s, categories'

这是函数:

FUNCTION get_cat(kind_id IN varchar, system_id IN Number) RETURN VARCHAR2 
   AS categories VARCHAR2(2000);
   k STANDARDS.KIND_ID%TYPE;
   s SEEDTEST_RESULT.SLRN_ID%TYPE;   
BEGIN 

  SELECT STANDARDS.KIND_ID, SEEDTEST_RESULT.SLRN_ID, 
         listagg(CAT_LEVEL, ' ' ) within group (order by cat_level)
  INTO k, s, categories
  FROM STANDARDS, SEEDTEST_RESULT
  WHERE STANDARDS.CL_PRIORITY = SEEDTEST_RESULT.CL_PRIORITY
      AND SEEDTEST_RESULT.RESULT = 1
      AND SEEDTEST_RESULT.TEST_TYPE = 'C'
      AND STANDARDS.KIND_ID = trim(kind_id)
      AND SEEDTEST_RESULT.SLRN_ID = system_id
  GROUP BY STANDARDS.KIND_ID, SEEDTEST_RESULT.SLRN_ID;

  RETURN categories; 

END get_cat;

当我使用 kind_id 和 system_id 的特定值运行它时，select 语句在函数之外工作.

The select statement works outside the function when I run it with specific values for kind_id and system_id.

我一直在尝试创建一个临时表，以便我可以从选择中获取初始行，然后返回类别，但到目前为止，我还没有找到有关此特定情况的任何有用信息.请问有人知道我该怎么做吗?

I've been trying to create a temp table so I can get the initial rows from the select and then return categories, but so far I haven't been able to find any helpful information for this particular case. Does anyone knows how can I do this, please?

谢谢.

推荐答案

问题在于你的变量名:

FUNCTION get_cat(kind_id IN varchar, ...
...
      AND STANDARDS.KIND_ID = trim(kind_id)

您有一个名为 kind_id 的列，查询将优先使用它而不是同名的 PL/SQL 变量.

You have a column called kind_id and the query will use that in preference to the PL/SQL variable of the same name.

来自文档:

如果 SQL 语句引用的名称既属于列又属于局部变量或形参，则列名优先.

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

所以你没有匹配传入的值，你实际上是在为 kind_idsystem_id 匹配的所有行>.(除了 null，如果它们有前导/尾随空格...)

So you aren't matching the passed-in value, you're actually finding all rows which match system_id for any value of kind_id. (Except null, and if they have leading/trailing whitespace...)

改变你的变量名，这样它们就不会冲突，也不会混淆.将传入的参数作为前缀是很常见的，比如，一个 p 前缀，所以你会与 = p_kind_id 进行比较，并使用 l前缀.

Change your variable names so they do not clash and there is no confusion. It's common to prefix passed-in argument with, say, a p prefix so you'd be comparing with = p_kind_id, and local variables with an l prefix.

如果你真的想保留你拥有的名字，你也可以在引用前加上函数名:

If you really want to keep the names you have, you can also prefix the references to those with the function name:

      AND STANDARDS.KIND_ID = trim(get_cat.kind_id)

相关文章