获取 PL/SQL 集合中元素的索引 [英] Getting index of element in PL/SQL collection

问题描述

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

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

类似的东西

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;

推荐答案

不确定，如果这真的有帮助，或者您认为它更优雅:

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;

这篇关于获取 PL/SQL 集合中元素的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！