Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TYPE INT_LIST IS TABLE OF INT;
CREATE TYPE STRING_LIST IS TABLE OF VARCHAR2(2);
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;
/
CREATE TABLE t1 (pc, vk, ay, ao, an, r) as
    select 1, 'VK1', null, null, 'A1, A2', 1 from dual union all
    select 1, 'VK2', null, null, null, 2 from dual union all
    select 1, 'VK3', null, null, 'A1, A2, A3, A4', 3 from dual union all
    select 1, 'VK4', null, null, 'A2', 4 from dual union all
    select 1, 'VK5', null, null, null, 5 from dual union all
    select 1, 'VK6', null, null, null, 6 from dual union all
    select 1, 'VK7', 'A3', null, null, 7 from dual union all
    select 1, 'VK8', null, null, null, 8 from dual union all
    select 1, 'VK9', null, null, null, 9 from dual union all
    select 1, 'VK10', null, null, null, 10 from dual union all
    select 1, 'VK11', null, null, null, 11 from dual union all
    select 1, 'VK12', null, null, null, 12 from dual union all
    select 1, 'VK13', 'A3', null, null, 13 from dual union all
    select 1, 'VK14', null, null, null, 14 from dual union all
    select 1, 'VK15', 'A3', null, null, 15 from dual union all
    select 1, 'VK16', null, null, null, 16 from dual union all
    select 1, 'VK17', null, null, null, 17 from dual union all
    select 1, 'VK18', null, null, null, 18 from dual union all
    select 1, 'VK19', null, 'A1', null, 19 from dual union all
    select 1, 'VK20', null, null, null, 20 from dual union all
    select 2, 'VK1', null, null, 'A1, A2', 1 from dual union all
    select 2, 'VK2', null, null, null, 2 from dual union all
    select 2, 'VK3', null, null, 'A1, A2, A3, A4', 3 from dual union all
    select 2, 'VK4', null, null, 'A2', 4 from dual union all
    select 2, 'VK5', null, null, null, 5 from dual union all
    select 2, 'VK6', null, null, null, 6 from dual union all
    select 2, 'VK7', 'A3', null, null, 7 from dual union all
    select 2, 'VK8', null, null, null, 8 from dual union all
    select 2, 'VK9', null, null, null, 9 from dual union all
    select 2, 'VK10', null, null, null, 10 from dual union all
    select 2, 'VK11', null, null, null, 11 from dual union all
    select 2, 'VK12', null, null, null, 12 from dual union all
    select 2, 'VK13', 'A3', null, null, 13 from dual union all
    select 2, 'VK14', null, null, null, 14 from dual union all
    select 2, 'VK15', 'A3', null, null, 15 from dual union all
    select 2, 'VK16', null, null, null, 16 from dual union all
    select 2, 'VK17', null, null, null, 17 from dual union all
    select 2, 'VK18', null, null, null, 18 from dual union all
    select 2, 'VK19', null, null, null, 19 from dual union all
    select 2, 'VK20', null, null, null, 20 from dual;
40 rows affected

CREATE TABLE t2 (pc, a, c) as
    select 1, 'A1', 4 from dual union all
    select 1, 'A2', 10 from dual union all
    select 1, 'A3', 2 from dual union all
    select 1, 'A4', 10 from dual union all
    select 2, 'A1', 11 from dual union all
    select 2, 'A2', 1 from dual union all
    select 2, 'A3', 4 from dual union all
    select 2, 'A4', 6 from dual;
8 rows affected

CREATE TYPE t1_prueba_data AS OBJECT (
    pc   INT,
    vk   VARCHAR2(4),
    ay   VARCHAR2(2),
    ao   VARCHAR2(2),
    an   VARCHAR2(14),
    r    INT,
    c    VARCHAR2(2)
);
CREATE TYPE t1_prueba_table IS TABLE OF t1_prueba_data;
CREATE OR REPLACE FUNCTION pick_values RETURN t1_prueba_table
    PIPELINED
IS
    TYPE t2_type IS
        TABLE OF t2%rowtype;
    t2_data   t2_type;
    v_pc      t1.pc%TYPE;
BEGIN
  -- https://stackoverflow.com/a/67516191/1509264
  -- License: CC BY-SA 4.0
  FOR cur IN (
    SELECT *
    FROM   t1
    ORDER BY pc, r
  ) LOOP
    IF v_pc IS NULL OR v_pc <> cur.pc THEN
      v_pc := cur.pc;
      SELECT *
      BULK COLLECT INTO t2_data
      FROM   t2
      WHERE  pc = cur.pc;
    END IF;

    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.ao
           AND t2_data(i).c > 0
        THEN
          c := t2_data(i).a;
          t2_data(i).c := t2_data(i).c - 1;
          cum_freq := 0;
          EXIT;
        ELSIF ( 
                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_prueba_data(cur.pc, cur.vk, cur.ay, cur.ao, cur.an, cur.r, c)
      );
    END;
  END LOOP;
END;
/
SELECT * FROM USER_ERRORS;
SELECT * FROM TABLE(pick_values())


PC
VK
AY
AO
AN
R
C


1
VK1
null
null
A1, A2
1
A4

1
VK2
null
null
null
2
A2

1
VK3
null
null
A1, A2, A3, A4
3
null

1
VK4
null
null
A2
4
A4

1
VK5
null
null
null
5
A2

1
VK6
null
null
null
6
A3

1
VK7
A3
null
null
7
A3

1
VK8
null
null
null
8
A2

1
VK9
null
null
null
9
A2

1
VK10
null
null
null
10
A2

1
VK11
null
null
null
11
A2

1
VK12
null
null
null
12
A4

1
VK13
A3
null
null
13
null

1
VK14
null
null
null
14
A1

1
VK15
A3
null
null
15
null

1
VK16
null
null
null
16
A4

1
VK17
null
null
null
17
A1

1
VK18
null
null
null
18
A2

1
VK19
null
A1
null
19
A1

1
VK20
null
null
null
20
A1

2
VK1
null
null
A1, A2
1
A3

2
VK2
null
null
null
2
A4

2
VK3
null
null
A1, A2, A3, A4
3
null

2
VK4
null
null
A2
4
A1

2
VK5
null
null
null
5
A3

2
VK6
null
null
null
6
A1

2
VK7
A3
null
null
7
A3

2
VK8
null
null
null
8
A1

2
VK9
null
null
null
9
A4

2
VK10
null
null
null
10
A3

2
VK11
null
null
null
11
A1

2
VK12
null
null
null
12
A4

2
VK13
A3
null
null
13
null

2
VK14
null
null
null
14
A1

2
VK15
A3
null
null
15
null

2
VK16
null
null
null
16
A1

2
VK17
null
null
null
17
A1

2
VK18
null
null
null
18
A4

2
VK19
null
null
null
19
A2

2
VK20
null
null
null
20
A1

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