oracle 获取 PL/SQL 集合中元素的索引

Getting index of element in PL/SQL collection

提问by Peter Lang

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

是否有内置函数来确定 PL/SQL 集合中元素的（第一个）索引？

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;

回答by René Nyffenegger

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;

回答by Vincent Malgrat

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:

我认为没有搜索集合的内置函数。但是，如果您知道需要大量搜索集合，则可以构建索引。将元素添加到集合中会有点贵，但查找元素将是 O(1) 操作（而不是 O(n) 进行蛮力搜索）。例如，您可以使用以下内容：

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.

您还可以定义一个包含数组的记录，并且所有与数组交互的函数/过程都将使用此记录类型。

回答by tbone

When in doubt, consult 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