Oracle存储过程数组数不清

Question

我创建了3个存储过程。它们的每一个功能是:
1. Proc_insertleveluser ->插入到"leveluser"表中并返回最后插入的id
2. Proc_insertpermissiondtl ->插入到"permission_dtl"表中并返回最后插入的id
3.Proc_insert_relation_lpd ->将过程1和过程2提供的数据插入到"lvl_permission_dtl"表

proc_insertleveluser

create or replace procedure proc_insertleveluser(level_desc varchar, level_ int, department_id varchar, r_id_level out int)
as
begin
  insert into leveluser (level_desc, level_, department_id) values (level_desc, level_, department_id)
  returning id_level into r_id_level;
  commit;
end;

proc_insertpermissiondtl

create or replace procedure proc_insertpermissiondtl(status_ int, entry_ int, view_ int, modify_ int, delete_ int, approval_ int, r_id_p_dtl out int)
as
begin
  insert into permission_dtl (status_, entry_, view_, modify_, delete_, approval_)
  values(status_, entry_, view_, modify_, delete_, approval_)
  returning id_p_dtl into r_id_p_dtl;
  commit;
end;

和 proc_insert_relation_lpd

create or replace procedure proc_insert_relation_lpd(lu_level_desc varchar, lu_level_ int, lu_department_id varchar)
as
  r_id_level int;  /* r_ is for return */
  r_id_p_dtl int;  
  type arr_id_p_dtl is varray(13) of int;
  arraynya arr_id_p_dtl := arr_id_p_dtl();
begin
  proc_insertleveluser(lu_level_desc, lu_level_, lu_department_id, r_id_level); 
  for i in 1..arraynya.count loop  
    proc_insertpermissiondtl(0, 0, 0, 0, 0, 0, r_id_p_dtl);
    arraynya(i) := r_id_p_dtl;
  end loop;
  /* more code here to insert to "lvl_permission_dtl" table */
  commit;
end;

当我执行代码时，1条记录添加到"leveluser"表中，但没有添加到"permission_dtl"表中。看起来for循环内的代码没有被执行。

是什么原因造成的?"arraynya"变量是空的，所以for循环不会执行?还是有别的问题?:)

Answer 1

您已经创建了一个空数组，因此当您到达循环时count将为0。正如Bob Jarvis指出的，如果你想在循环中迭代13次，你应该使用数组的limit，而不是count。

SQL> ed
Wrote file afiedt.buf
  1  declare
  2    type arr_id_p_dtl is varray(13) of int;
  3    arraynya arr_id_p_dtl := arr_id_p_dtl();
  4  begin
  5    dbms_output.put_line( 'Count is ' || arraynya.count );
  6    dbms_output.put_line( 'Limit is ' || arraynya.limit );
  7    --
  8    -- This doesn't do anything since the count is 0
  9    --
 10    for i in 1..arraynya.count
 11    loop
 12      dbms_output.put_line( 'Count loop i=' || i );
 13    end loop;
 14    --
 15    -- This will iterate 13 times
 16    --
 17    for i in 1..arraynya.limit
 18    loop
 19      dbms_output.put_line( 'Limit loop i=' || i );
 20    end loop;
 21* end;
 22  /
Count is 0
Limit is 13
Limit loop i=1
Limit loop i=2
Limit loop i=3
Limit loop i=4
Limit loop i=5
Limit loop i=6
Limit loop i=7
Limit loop i=8
Limit loop i=9
Limit loop i=10
Limit loop i=11
Limit loop i=12
Limit loop i=13
PL/SQL procedure successfully completed.