将两个数组的可能组合插入PLSQL中的另一个数组中

Question

我有两个数组Course和time。我正在尝试用这两个数组的所有可能组合创建另一个数组。例如：

DECLARE
type course_array
IS
  TABLE OF NUMBER;
type time_array
IS
  TABLE OF NUMBER;
type combination_array
IS
  TABLE OF NUMBER;
  Course_pref course_array;
  Time_pref time_array;
  combination combination_array;
BEGIN
  Course_pref := course_array(1, 2, 4);
  Time_pref   := time_array(1, 2, 3, 5);

我想要一个看起来像的阵列

combination:= combination_array((1,1),(1,2),(1,4),(2,1),(2,2),(2,4)......)

感谢

Answer 1

也许这个函数是更好的方法。（未经测试的代码！！）

DECLARE
type course_array IS TABLE OF NUMBER;
type time_array   IS TABLE OF NUMBER;
function combination_function(i number, j number) return number is 
  t_i number := 1 + trunc( (i-1) / time_array.last);
  c_i number := 1 + ((i-1) mod course_array.last);
begin
  if i <= (course_array.last * time_array.last) then
    return(decode(j, 1, time_array(t_i), 
                     2, course_array(c_i)));
  else
    return(null);
  end if;
end;
BEGIN
  Course_pref := course_array(1, 2, 4);
  Time_pref   := time_array(1, 2, 3, 5);
  for i in 1 .. (course_array.last * time_array.last) loop
    dbms_output.put_line('('||combination_function(i, 1)||','||combination_function(i, 2)||')');
  end loop;
END;

Answer 2

推荐的方法是使用PL/SQL的SQL部分。

我们使用SQL的功能，因此类型需要是SQL类型。SQL类型可以在PL/SQL中使用，但不能反过来。

create type num_list_t is table of number;
/
create type num_pair_t is object(
    a number,
    b number
);
/
create type num_pair_list_t is table of num_pair_t;
/

一个如何使用SQL交叉连接进行组合的示例。这是核心解决方案。另请参阅例如如何生成m列中n行的排列或组合？稍后您将看到如何将其应用于PL/SQL。

with
t1 as (
  select column_value from table(num_list_t(0,2,4,6,8))
),
t2 as (
  select column_value from table(num_list_t(1,3,5,7,9))
)
select t1.column_value as a, t2.column_value as b
from t1
cross join t2;
         A          B
---------- ----------
         0          1
         0          3
         0          5
         0          7
         0          9
         2          1
         2          3
         2          5
         2          7
         2          9
         4          1
         4          3
         4          5
         4          7
         4          9
         6          1
         6          3
         6          5
         6          7
         6          9
         8          1
         8          3
         8          5
         8          7
         8          9
25 rows selected.

如何将上面的SQL示例应用于PL/SQL的示例。请注意对象构造函数和大容量收集是如何用于将结果模糊到PL/SQL的。

declare
  v_a constant num_list_t := num_list_t(10, 20, 30, 40);
  v_b constant num_list_t := num_list_t(1, 3, 5, 7, 9);
  v_c num_pair_list_t;
  function combine(p_a in num_list_t, p_b in num_list_t) return num_pair_list_t is
    v_res num_pair_list_t := num_pair_list_t();
  begin
    with
    t1 as (
      select column_value from table(p_a)
    ),
    t2 as (
      select column_value from table(p_b)
    )
    select num_pair_t(t1.column_value, t2.column_value)
      bulk collect into v_res
    from t1
    cross join t2;
    return v_res;
  end;
  procedure print(p_list in num_pair_list_t) is
  begin
    for i in 1 .. p_list.last
    loop
      dbms_output.put_line('(' || p_list(i).a || ',' || p_list(i).b || ')');
    end loop;
  end;
begin
  v_c := combine(v_a, v_b);
  print(v_c);
  print(combine(num_list_t(34,68), num_list_t(12,24)));
end;
/
(0,1)
(0,3)
(0,5)
(0,7)
(0,9)
(2,1)
(2,3)
(2,5)
(2,7)
(2,9)
(4,1)
(4,3)
(4,5)
(4,7)
(4,9)
(6,1)
(6,3)
(6,5)
(6,7)
(6,9)
(8,1)
(8,3)
(8,5)
(8,7)
(8,9)
(34,12)
(34,24)
(68,12)
(68,24)
PL/SQL procedure successfully completed.