时间戳

首页 > 解决方案 > 表变量仅填充一个值

oracle - 表变量仅填充一个值

问题描述

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

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

       select chi.id bulk collect into v_numbers from dual;

并且这条线以某种方式覆盖了所有以前的结果（每个循环都有几个）。如何在不覆盖先前结果的情况下插入 v_numbers？我知道只插入一行也是错误的，但是我还没有找到从 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;

标签： oracleplsql

解决方案

用于批量插入BULK COLLECT和FORALL更好的性能。FORALL语句将允许为集合中的每一行运行 DML，而无需每次都进行上下文切换，从而提高整体性能。

CREATE OR REPLACE 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 
    SELECT s.id
    BULK COLLECT --> Bulk collect all values
    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;

        FORALL i IN 1..v_numbers.COUNT   
        INSERT INTO your_table VALUES v_numbers ( i ); --> Bulk insert

END;

推荐阅读