Getting the index of an item in a PL / SQL collection

Question

Is there a built-in function to determine the (first) index of an element in a PL / SQL collection?

Something like

DECLARE TYPE t_test IS TABLE OF VARCHAR2(1); v_test t_test; BEGIN v_test := NEW t_test('A', 'B', 'A'); dbms_output.put_line( 'A: ' || get_index( v_test, 'A' ) ); dbms_output.put_line( 'B: ' || get_index( v_test, 'B' ) ); dbms_output.put_line( 'C: ' || get_index( v_test, 'C' ) ); END; 

 A: 1 B: 2 C: 

I can use associative arrays, nested tables or Varrays, whatever is needed. If the same element exists more than once, then the index of the first occurrence is sufficient.

---

Otherwise, I will need to do something like

 CREATE FUNCTION get_index ( in_test IN t_test, in_value IN VARCHAR2 ) RETURN PLS_INTEGER AS i PLS_INTEGER; BEGIN i := in_test.FIRST; WHILE( i IS NOT NULL ) LOOP IF( in_test(i) = in_value ) THEN RETURN i; END IF; i := in_test.NEXT(i); END LOOP; RETURN NULL; END get_index; 

Answer 1

Not sure if this really helps, or if you think it is more elegant:

 create type t_test as table of varchar2(1); / DECLARE --TYPE t_test IS TABLE OF VARCHAR2(1); v_test t_test; function get_index(q in t_test, c in varchar2) return number is ind number; begin select min(rn) into ind from ( select column_value cv, rownum rn from table(q) ) where cv = c; return ind; end get_index; BEGIN v_test := NEW t_test('A', 'B', 'A'); dbms_output.put_line( 'A: ' || get_index( v_test, 'A' ) ); dbms_output.put_line( 'B: ' || get_index( v_test, 'B' ) ); dbms_output.put_line( 'C: ' || get_index( v_test, 'C' ) ); END; / show errors drop type t_test; 

Answer 2

I don’t think there is a built-in function that is looking for a collection. However, if you know that you will need to search the collection a lot, you can create an index. Adding an item to the collection will be a little more expensive, but the item will be searched for O (1) (instead of O (n) to search for brute force). For example, you can use something like this:

 SQL> DECLARE 2 TYPE t_test IS TABLE OF VARCHAR2(1); 3 TYPE t_test_r IS TABLE OF NUMBER INDEX BY VARCHAR2(1); 4 5 v_test t_test; 6 v_test_r t_test_r; 7 8 FUNCTION get_index(p_test_r t_test_r, 9 p_element VARCHAR2) RETURN NUMBER IS 10 BEGIN 11 RETURN p_test_r(p_element); 12 EXCEPTION 13 WHEN no_data_found THEN 14 RETURN NULL; 15 END get_index; 16 17 PROCEDURE add_element(p_test IN OUT t_test, 18 p_test_r IN OUT t_test_r, 19 p_element VARCHAR2) IS 20 BEGIN 21 p_test.extend; 22 p_test(p_test.count) := p_element; 23 p_test_r(p_element) := least(p_test.count, 24 nvl(get_index(p_test_r, p_element), 25 p_test.count)); 26 END add_element; 27 BEGIN 28 v_test := NEW t_test(); 29 add_element(v_test, v_test_r, 'A'); 30 add_element(v_test, v_test_r, 'B'); 31 add_element(v_test, v_test_r, 'A'); 32 dbms_output.put_line('A: ' || get_index(v_test_r, 'A')); 33 dbms_output.put_line('B: ' || get_index(v_test_r, 'B')); 34 dbms_output.put_line('C: ' || get_index(v_test_r, 'C')); 35 END; 36 / A: 1 B: 2 C: PL/SQL procedure successfully completed 

You can also define a record containing both arrays, and all functions / procedures for interacting with arrays will use this type of record.

Answer 3

If in doubt, refer to the documentation;) ( here )

 DECLARE TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER; aa_int aa_type_int; PROCEDURE print_first_and_last IS BEGIN DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST); DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST); END print_first_and_last; BEGIN aa_int(1) := 3; aa_int(2) := 6; aa_int(3) := 9; aa_int(4) := 12; DBMS_OUTPUT.PUT_LINE('Before deletions:'); print_first_and_last; aa_int.DELETE(1); aa_int.DELETE(4); DBMS_OUTPUT.PUT_LINE('After deletions:'); print_first_and_last; END; / 

Result:

 Before deletions: FIRST = 1 LAST = 4 After deletions: FIRST = 2 LAST = 3