JavaEar 专注于收集分享传播有价值的技术资料

pl/sql remove element from array

I need to remove element from array. I have tried to use array.delete(n) function, but it deletes all elements from identifier n. How to just delete exact element n ?

For example if array is 1 2 3 4 5, and n = 3, after delete it should look like following 1 2 4 5.

My code so far :

  /* declare type array */ 
  TYPE number_index_by_number IS TABLE OF number INDEX BY binary_integer;

  v_n NUMBER := &sv_n;
  v_m NUMBER := &sv_m;
  v_min Number; 
  v_tmp Number;
  v_array number_index_by_number; 
  v_sorted_array number_index_by_number;
  for i in 1..v_n 
    v_array(i) := dbms_random.value(1,1000);
  end loop;
  for j in v_array.first..v_array.last
    DBMS_OUTPUT.put_line('v_array('||j||') :'||v_array(j));
  end loop;
  <<i_loop>> for i in 1..v_m
    /*set first array value to variable min*/
    v_min := v_array(1);
    v_tmp := 1;
    <<j_loop>> for j in v_array.first..v_array.last
      DBMS_OUTPUT.put_line('v_array('||j||') :'||v_array(j));
      if (v_min > v_array(j)) THEN
          v_min := v_array(j);
          v_tmp := j;
      end if;
    end loop;
    /*problem is in at this line*/
    v_sorted_array(i) := v_min; 
  end loop;
  for i in v_sorted_array.first..v_sorted_array.last
    DBMS_OUTPUT.put_line('v_sorted_array('||i||') :'||v_sorted_array(i));
  end loop;


  1. I cannot reproduce any of the behaviour you describe. I could not get the delete collection method to do anything other than what it is documented to do.

    However, there are a few errors in your code that could do with being tidied up.

    Firstly, I should point out if you delete an element with key 3 from a PL/SQL associative array, there is then nothing with key 3 in the array. The remaining values don't 'shuffle' down to fill the gap. If there was an element with key 4 before the delete, the same element will still have key 4 afterwards. As a result, if you delete element j from a PL/SQL associative array v_array and then attempt to get v_array(j), you will get a 'no data found' error. You should check to see whether the element exists, using v_array.exists(j), before attempting to get a nonexistent element.

    Secondly, the element with index 1 may get deleted before the last iteration of the outer loop. If this happens, v_array(1) will fail with a 'no data found' error. It would be better to assign NULL to v_min and v_tmp at the start of the loop, and assign to them during the loop if v_min is NULL or greater than v_array(j).

    Finally, it seems your code returns the v_m smallest numbers from v_n. It would be worth verifying that v_m is less than or equal to v_n, as otherwise this doesn't make sense.

  2. 参考答案2
  3. I'm affraid you cannot use a built-in method like this. Instead of you shoud create a temp array to collect the elements prior to and afterwards the selected one from the original array, and return the temp array.