plsql - Oracle cursor for assignation

Question

I have the below 2 tables:

t1

VK	AY	AN	R
VK1		A1, A2	1
VK2			2
VK3		A1, A2, A3, A4	3
VK4		A2	4
VK5			5
VK6			6
VK7	A3		7
VK8			8
VK9			9
VK10			10
VK11			11
VK12			12
VK13	A3		13
VK14			14
VK15	A3		15
VK16			16
VK17			17
VK18			18
VK19			19
VK20			20

Answer 1

Set up the data types and helper functions:

CREATE TYPE string_list IS TABLE OF VARCHAR2(2);
/

CREATE TYPE int_list IS TABLE OF INT;
/

CREATE TYPE t1_data AS OBJECT(
  vk VARCHAR2(4),
  ay VARCHAR2(2),
  an VARCHAR2(14),
  r  INT,
  c  VARCHAR2(2)
);
/

CREATE TYPE t1_table IS TABLE OF t1_data;
/

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN string_list DETERMINISTIC
AS
  p_result       string_list := string_list();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  -- https://stackoverflow.com/a/35577315/1509264
  -- License: CC BY-SA 4.0
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Then you can use the function:

CREATE FUNCTION pick_values RETURN t1_table PIPELINED
IS
  TYPE t2_type IS TABLE OF t2%ROWTYPE;
  
  t2_data t2_type;
BEGIN
  -- https://stackoverflow.com/a/67398434/1509264
  -- License: CC BY-SA 4.0
  SELECT *
  BULK COLLECT INTO t2_data
  FROM  t2;
  
  FOR cur IN ( SELECT * FROM t1 ORDER BY r )
  LOOP
    DECLARE
      a_freqs  INT_LIST := INT_LIST();
      cum_freq INT := 0;
      taken    STRING_LIST := SPLIT_STRING( cur.an, ', ' );
      idx      INT;
      c        T2.A%TYPE;
    BEGIN
      a_freqs.EXTEND(t2_data.COUNT);
      FOR i IN 1 .. t2_data.COUNT LOOP
        IF ( t2_data(i).a = cur.ay AND t2_data(i).c > 0 )
           OR ( cur.ay IS NULL AND t2_data(i).a NOT MEMBER OF taken AND t2_data(i).c > 0 )
        THEN
          a_freqs(i) := cum_freq + t2_data(i).c;
          cum_freq := cum_freq + t2_data(i).c;
        ELSE
          a_freqs(i) := cum_freq;
        END IF;
      END LOOP;
      IF cum_freq > 0 THEN
        idx := FLOOR(DBMS_RANDOM.VALUE(0, cum_freq));
        FOR i IN 1 .. t2_data.COUNT LOOP
          IF idx < a_freqs(i) THEN
            c := t2_data(i).a;
            t2_data(i).c := t2_data(i).c - 1;
            EXIT;
          END IF;
        END LOOP;
      END IF;
      PIPE ROW(
        t1_data(
          cur.vk, cur.ay, cur.an, cur.r, c
        )
      );
    END;
  END LOOP;
END;
/

Which, for your sample data:

CREATE TABLE t1 (vk, ay, an, r) as
    select 'VK1', null, 'A1, A2', 1 from dual union all
    select 'VK2', null, null, 2 from dual union all
    select 'VK3', null, 'A1, A2, A3, A4', 3 from dual union all
    select 'VK4', null, 'A2', 4 from dual union all
    select 'VK5', null, null, 5 from dual union all
    select 'VK6', null, null, 6 from dual union all
    select 'VK7', 'A3', null, 7 from dual union all
    select 'VK8', null, null, 8 from dual union all
    select 'VK9', null, null, 9 from dual union all
    select 'VK10', null, null, 10 from dual union all
    select 'VK11', null, null, 11 from dual union all
    select 'VK12', null, null, 12 from dual union all
    select 'VK13', 'A3', null, 13 from dual union all
    select 'VK14', null, null, 14 from dual union all
    select 'VK15', 'A3', null, 15 from dual union all
    select 'VK16', null, null, 16 from dual union all
    select 'VK17', null, null, 17 from dual union all
    select 'VK18', null, null, 18 from dual union all
    select 'VK19', null, null, 19 from dual union all
    select 'VK20', null, null, 20 from dual;

CREATE TABLE t2 (a, c) as
    select 'A1', 4 from dual union all
    select 'A2', 10 from dual union all
    select 'A3', 2 from dual union all
    select 'A4', 10 from dual;

Then:

SELECT * FROM PICK_VALUES();

May output:

VK	AY	AN	R	C
VK1		A1, A2	1	A4
VK2			2	A2
VK3		A1, A2, A3, A4	3
VK4		A2	4	A1
VK5			5	A2
VK6			6	A2
VK7	A3		7	A3
VK8			8	A1
VK9			9	A2
VK10			10	A4
VK11			11	A2
VK12			12	A1
VK13	A3		13	A3
VK14			14	A4
VK15	A3		15
VK16			16	A4
VK17			17	A2
VK18			18	A4
VK19			19	A4
VK20			20	A4