时间戳

首页 > 解决方案 > 功能光标分配

sql - 功能光标分配

问题描述

我有一个函数-请在问题的末尾找到一个 MRE-，它通过 pc 的分区和 r 的顺序分配，如果“ay”不为空，则“ay”，如果 an 有任何值，那么那些无法选取值。但是，我很难理解以下部分的工作原理（即，如果 cur.ay 不为空，则分配的 a 是 ay）。我还需要增强此功能以包括列“ao”的逻辑，如果可能，然后分配给该“a”，但是，如果 cur.c<=0，尝试将其分配给任何可用的“作为。有人可以澄清循环中的逻辑是如何工作的，以便我可以尝试自己增强逻辑吗？

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

编辑：

请在下面找到输出示例：

个人电脑 VK 是的 AO 一个 R 解释
1 VK1 空值 A1 A2、A3 A1 AO 建议 A1，所以，因为 A1 有容量，所以分配给 A1
2 VK1 A1 空值 A2、A3 A1 AY 说它应该只去，如果容量，到 A1，所以，因为 A1 有容量，它被分配给 A1
... ... ... ... ... ... ...
ñ VK1 空值 A1 A2、A3 A4 AO建议A1，但是假设A1的容量已经用完，而A4还有容量，就分配给A4（禁止A2和A3）

请在下面找到函数：

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/67398434/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.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;

MRE 的表格和类型如下：

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;

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;


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;

标签： sqloracleplsql

解决方案

你要：

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
          -- If there is an "ao" value and it has capacity then assign it to "c"
          c := t2_data(i).a;
          -- Decrement the appropriate "t2_data" row to show it has been used.
          t2_data(i).c := t2_data(i).c - 1;
          -- Set the "cum_freq" to 0 so the loop where values are randomly assigned is skipped.
          cum_freq := 0;
          -- Exit the loop
          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;
/

db<>在这里摆弄

推荐阅读