从函数内部的SELECT返回一个变量 [英] Return one variable from a SELECT inside a function

问题描述

我正在尝试创建一个函数，该函数返回一个varchar，其中一个字段构成一个select字段，即aggregation字段.我遇到下一个错误:

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.

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

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?

谢谢.

推荐答案

问题出在您的变量名上:

The problem is with your variable names:

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.

因此，您与传入的值不匹配，实际上是在为 any 值kind_id的所有与system_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)

这篇关于从函数内部的SELECT返回一个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！