从对象表中删除PL-SQL项 [英] PL-SQL Delete item from a table of Objects

问题描述

我正在尝试从对象类型表的变量中删除一项:

I'm trying to delete an item from a variable of type table of objects:

CREATE OR REPLACE TYPE "T_ATTRIBUTEPAGE_ATTRIBUTELIST"  IS TABLE OF o_ATTRIBUTEPAGE_ATTRIBUTELIST;

CREATE OR REPLACE TYPE "O_ATTRIBUTEPAGE_ATTRIBUTELIST" IS OBJECT (
    WizAttrEditID           NUMBER,
    InternalIndex           NUMBER,
    DimensionObjectID       NUMBER,
    AttributeName           VARCHAR2(50),
    AttributeLabel          VARCHAR2(50),
    AttributeType           NUMBER,
    AttributeLength         VARCHAR2(50),
    MandatoryAttribute      NUMBER,
    ReadOnly                NUMBER,
    Name                    VARCHAR2(2000),
    Num                     NUMBER,
    IsModified              NUMBER,
    Colour                  NUMBER);

我正在遍历对象列表，检查并且ls_attr_list.COUNT是16，当满足条件时我尝试删除一项，但出现此错误:

I'm itterating through the list of objects, I checked and the ls_attr_list.COUNT is 16 and I try to delete one item when a criteria is met but I get this error :

ORA-01403:找不到数据

ORA-01403: no data found

在此行出现的

:ls_attr_list.Delete(i);

FOR i IN 1..ls_attr_list.COUNT LOOP
           BEGIN
               IF ls_attr_list(i).attributename = 'PROTECTION_ROLE' THEN
                   ls_attr_list.Delete(i);
                END IF;
               EXCEPTION WHEN OTHERS THEN
                 writelog(SQLERRM,'ERROR');
                 END;
            END LOOP;

ls_attr_list使用输入参数初始化:

the ls_attr_list is initialize with the input parameter:

PROCEDURE AttribInit(geninfo        IN OUT    o_geninfo,
                     pageinfo       IN OUT    o_attributepage_pageinfo,
                     attributelist  IN OUT    t_attributepage_attributelist,
                     enumlist       IN OUT    t_attributepage_enumlist)

AS
ls_attr_list := attributelist;

删除一项后在列表上重复出现的问题

Problem when itterating over the list after deleting one item

i := attributelist.FIRST; 
LOOP 
IF attributelist(i).attributename = 'PROTECTION_ROLE' THEN attributelist.DELETE(i); 
END IF; 
EXIT WHEN i = attributelist.LAST; 
i := attributelist.NEXT(i);
 END LOOP; 
--second FOR
FOR i in 1..attributelist.COUNT LOOP 

-到达上一个已删除项目的索引时找不到数据

--no data found when it reaches the index of the previous deleted item

writelog(attributelist(i).attributename,'attributename'); 
END LOOP;

我做错了什么，有什么想法吗?

I'm doing something wrong, any ideas what??

推荐答案

FOR i IN 1 .. ls_attr_list.COUNT LOOP

当您从集合中删除一个元素时，将导致异常/错误.每次删除元素时，它都会在集合中创建一个间隙，而下次尝试运行该过程时，它将达到该间隙并抛出一个ORA-01403: no data found.

Will cause exceptions/errors when you have deleted an element from the collection. Each time you delete an element it creates a gap in the collection and the next time you try and run the procedure it will get to that gap and throw a ORA-01403: no data found.

相反，您需要在i := ls_attr_list.FIRST和ls_attr_list.LAST之间循环，并使用i := ls_attr_list.NEXT(i)获取下一个索引.

Instead you need to loop between i := ls_attr_list.FIRST and ls_attr_list.LAST and use i := ls_attr_list.NEXT(i) to get the next index.

一个简化的工作示例是:

A simplified working example is:

CREATE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(20);
/

DECLARE
  vals VARCHAR2_Table := VARCHAR2_Table( 'a', 'b', 'c', 'd', 'e', 'f' );
  PROCEDURE del_Val (
    v IN OUT VARCHAR2_Table,
    x IN     VARCHAR2
  )
  AS
    i INT;
  BEGIN
    IF v IS NULL OR v IS EMPTY THEN
      RETURN;
    END IF;
    i := v.FIRST;
    LOOP
      IF v(i) = x THEN
        DBMS_OUTPUT.PUT_LINE(i);
        v.DELETE(i);
      END IF;
      EXIT WHEN i = v.LAST;
      i := v.NEXT(i);
    END LOOP;
  END;
BEGIN
  del_Val( vals, 'b' );
  del_Val( vals, 'e' );
  del_Val( vals, 'a' );
END;
/

但是使用此过程:

  PROCEDURE del_Val (
    v IN OUT VARCHAR2_Table,
    x IN     VARCHAR2
  )
  AS
  BEGIN
    FOR i IN 1 .. v.COUNT LOOP
      IF v(i) = x THEN
        DBMS_OUTPUT.PUT_LINE(i);
        v.DELETE(i);
      END IF;
    END LOOP;
  END;

第二次调用该程序时会导致ORA-01403: no data found.

Would have caused ORA-01403: no data found when the procedure is called the second time.

这篇关于从对象表中删除PL-SQL项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！