表变量仅填充一个值 [英] Table variable is filled only with one value

问题描述

我有一个存储过程，该过程应该返回多个结果-但它仅返回一行.我认为这是结果集中的最后一行.

I have a stored procedure which should return several results - but it returns only one row. I think it's the last row in result set.

我不确定，但是我认为问题出在这行代码中:

I am not sure, but I think the problem is in this line of code:

       select chi.id bulk collect into v_numbers from dual;

并且该行以某种方式覆盖了所有先前的结果(每个循环有多个结果).如何在不覆盖先前结果的情况下插入v_numbers?我知道只插入一行也是错误的，但是我还没有找到从chi插入几行的解决方案.

and that this line somehow overrides all previous results (there is several of them for each loop). How to insert into v_numbers without overriding previous results? I know that it's also wrong to insert only one row, but I haven't found solution to insert several rows from chi.

PROCEDURE GET_ATTRIBUTES(
      P_AUTH_USE_ID IN NUMBER,
      P_CATEGORY_ID IN NUMBER,
      P_VERSION_ID IN NUMBER,
      P_RESULT OUT TYPES.CURSOR_TYPE
  ) IS
    v_numbers sys.odcinumberlist := null;
  BEGIN

  FOR item IN
    (SELECT ID FROM INV_SRV WHERE SRV_CATEGORY_ID IN 
            (
                SELECT id
                FROM   inv_srv_category
                START WITH parent_category_id = P_CATEGORY_ID
                CONNECT BY PRIOR id = parent_category_id
            ) OR SRV_CATEGORY_ID = P_CATEGORY_ID)
    LOOP

        for chi in (select s.id
                  from inv_srv s
                    start with s.parent_srv_id = item.id
                    connect by prior s.id = s.parent_srv_id
                   )
        loop        
           select chi.id bulk collect into v_numbers from dual; --> here I should insert all rows from  that loop, but I don't know how
        end loop;    

    END LOOP;

    OPEN P_RESULT FOR SELECT t.column_value from table(v_numbers) t; --> only one row is returned


  END;

推荐答案

每次执行循环时，都会一次又一次地填充v_numbers，因此，或者1)使用v_numbers.extend; v_numbers(v_numbers.last)=您的值"或将所有内容写在一个批量收集中.

Every time the loop executes v_numbers will be re populated again and again so, either 1) use v_numbers.extend; v_numbers(v_numbers.last) = "Your Value" or write everything in a single bulk collect.

select s.id 
bulk collect into v_numbers 
from inv_srv s
start with s.parent_srv_id in (SELECT ID FROM INV_SRV 
                                WHERE SRV_CATEGORY_ID IN 
                                                    (
                                                        SELECT id
                                                        FROM   inv_srv_category
                                                        START WITH parent_category_id = P_CATEGORY_ID
                                                        CONNECT BY PRIOR id = parent_category_id
                                                    ) 
                                  OR SRV_CATEGORY_ID = P_CATEGORY_ID)
connect by prior s.id = s.parent_srv_id

这篇关于表变量仅填充一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！