Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting index of element in PL/SQL collection

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 necessary. If the same element exists more than once, then the index of the first occurrence is sufficient.


Otherwise I'd have 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;
like image 751
Peter Lang Avatar asked Jan 27 '11 13:01

Peter Lang


People also ask

How do I find the index of a table in PL SQL?

To list only the indexes on a table, query the all_indexes view: SELECT index_name, index_type, uniqueness FROM all_indexes WHERE owner = UPPER('&owner') AND table_name = UPPER('&table_name'); Listing the indexes alone is seldom enough. You need to know at least the columns involved in each index.

How do you get the index of a table in Oracle?

To show indexes for a particular table in Oracle use the following command: select index_name from dba_indexes where table_name='tablename';

What is Varray in PL SQL?

A VARRAY is a type of collection in which each element is referenced by a positive integer called the array index. The maximum cardinality of the VARRAY is specified in the type definition. The TYPE IS VARRAY statement is used to define a VARRAY collection type.


2 Answers

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;
like image 107
René Nyffenegger Avatar answered Nov 02 '22 14:11

René Nyffenegger


I don't think there is a built-in function that searches a collection. However, if you know you will need to search a collection a lot, you could build an index. Adding element to the collection will be a bit more expensive, but looking for an element will be an O(1) operation (instead of O(n) for a brute force search). For example, you could 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 could also define a record that contains both arrays and all functions/procedures to interact with arrays would use this record type.

like image 43
Vincent Malgrat Avatar answered Nov 02 '22 12:11

Vincent Malgrat