DELETE收集方法在Oracle工作错误

问题描述：

我想用DELETE Collection方法来删除收集一些元素，如：DELETE收集方法在Oracle工作错误

create or replace procedure testloop3 (clearaaa out nestedtable) as 
type nestedtable is table of varchar2(255); 
reply_ref_messageIds nestedtable; 
getDelete_messageIds nestedtable; 
begin 
select distinct r.messagebox_id bulk collect into reply_ref_messageIds from reply r; 
select m.id bulk collect into getDelete_messageIds from messagebox m; 
     getDelete_messageIds.delete(2); 
    getDelete_messageIds.delete(4); 
    getDelete_messageIds.delete(7); 
    getDelete_messageIds.delete(11); 
    getDelete_messageIds.delete(13); 
    for i in getDelete_messageIds.FIRST .. getDelete_messageIds.LAST loop 
    dbms_output.put_line(i); 
end loop; 
for i in 5 .. 12 loop 
    dbms_output.put_line(i); 
end loop; 
end;

，然后我调试这个程序与PLSQL开发

-- Created on 2013/4/4 by THINKPAD 
declare 
    -- Local variables here 
aa nestedtable; 
begin 
    -- Test statements here 
    testloop3(aa); 
end;

和我得到getDelete_messageIds索引前删除是1至15.

但是：当我调试到getDelete_messageIds.delete(2);它删除索引1和2 ..我无法解释为什么。
，然后当我调试下一条语句getDelete_messageIds.delete(4);它删除索引3和4，然后getDelete_messageIds.delete(7);只删除索引7 ...
我无法理解......

欢迎的StackOverflow！请在您的帖子中格式化代码。 – peterm 2013-04-04 15:56:15

thanks.but如何格式化代码？只有四个空间？我不明白，规范... – 2013-04-04 16:12:04

答

你的程序，张贴，是深藏不露这种类型。你只是循环周围像

for idx in 1..15 loop

（。首先将解析为1和.LAST将解决15个）。这并不意味着新台币还有15个元素。

您不检查索引是否被删除。我认为你对通过嵌套表格循环的正确方法感到困惑。

即可以看到元素被删除：

SQL> create table messagebox(id) as select to_char(rownum) from dual connect by level <= 15; 

Table created. 

SQL> create or replace procedure testloop3 
    2 as 
    3 type nestedtable is table of varchar2(255); 
    4 getDelete_messageIds nestedtable; 
    5 v_idx number; 
    6 begin 
    7  select m.id bulk collect into getDelete_messageIds from messagebox m; 
    8  getDelete_messageIds.delete(2); 
    9  getDelete_messageIds.delete(4); 
10  getDelete_messageIds.delete(7); 
11  getDelete_messageIds.delete(11); 
12  getDelete_messageIds.delete(13); 
13  v_idx := getDelete_messageIds.first; 
14  while v_idx is not null 
15  loop 
16  dbms_output.put_line(v_idx); 
17  v_idx := getDelete_messageIds.next(v_idx); 
18  end loop; 
19 end; 
20/

Procedure created. 

SQL> exec testloop3 
1 
3 
5 
6 
8 
9 
10 
12 
14 
15

SO 2，4，7，11，13被删除。完全如预期。

谢谢。我明白你的意思。但我发现getDelete_messageIds是不同的，当我调试它，并在plsql中使用视图集合变量集合变量显示2,5,8,9,14,15时，developer.getDelete_messageIds显示1,3,5,6 ，8,10,12,14,15。是否有plsql开发人员的错误？ – 2013-04-05 02:00:32

答

你可以看到

DECLARE 
    TYPE NumList IS TABLE OF NUMBER; 
    n NumList := NumList(1,3,5,7); 
    counter INTEGER; 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST); 
    DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST); 
-- When the subscripts are consecutive starting at 1, 
-- it's simple to loop through them. 
    FOR i IN n.FIRST .. n.LAST 
    LOOP 
     DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i)); 
    END LOOP; 
    n.DELETE(2); -- Delete second element. 
-- When the subscripts have gaps 
-- or the collection might be uninitialized, 
-- the loop logic is more extensive. 
-- Start at the first element 
-- and look for the next element until there are no more. 
    IF n IS NOT NULL THEN 
     counter := n.FIRST; 
     WHILE counter IS NOT NULL 
     LOOP 
     DBMS_OUTPUT.PUT_LINE 
      ('Element #' || counter || ' = ' || n(counter)); 
     counter := n.NEXT(counter); 
     END LOOP; 
    ELSE 
     DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.'); 
    END IF; 
END;

参考Finding the First or Last Collection Element

DELETE收集方法在Oracle工作错误

相关推荐