功能光标分配 [英] Function cursor assignation
本文介绍了功能光标分配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个函数 - 请在问题的末尾找到一个 MRE - ，它按 pc 的分区和按 r 的顺序分配，如果ay"不为空，则ay"，如果 an 有任何值，则无法选择这些值.但是，我很难理解以下部分是如何工作的(即如何，如果 cur.ay 不为空，则分配的 a 是 ay).我还需要增强此功能以包括列ao"的逻辑，如果可能，然后将其分配给该a"，但是，如果 cur.c<=0，则尝试将其分配给任何可用的 '作为.有人可以澄清循环中的逻辑是如何工作的，以便我可以尝试自己增强逻辑吗?
I have a function -please find a MRE at the end of the question-, which assigns, by partition of pc and order by r, if 'ay' is not null, that 'ay', if an has any values, then those values cannot be picked. However, I'm struggling to understand how the below part works (namely how, if cur.ay is not null, then the assigned a is ay). I would also need to enhance this function to include also logic for a column 'ao' which, if possible then assigned to that 'a', but, if cur.c<=0, try to assign it to any of the available 'a's. Could someone clarify how the logic in the loop works so I can try to enhance the logic myself?
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
请在下面找到输出示例:
Please find below an example of output:
| PC | VK | AY | AO | AN | R | 说明 |
|---|---|---|---|---|---|---|
| 1 | VK1 | 空 | A1 | A2、A3 | A1 | AO 建议使用 A1，因此，由于 A1 有容量，因此将其分配给 A1 |
| 2 | VK1 | A1 | 空 | A2、A3 | A1 | AY 是说它应该只在有容量的情况下转到 A1，因此，由于 A1 有容量，所以它被分配给 A1 |
| ... | ... | ... | ... | ... | ... | ... |
| N | 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 的表和类型如下:
The tables and types for the MRE are below:
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;
推荐答案
您想要:
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<>fiddle 这里
这篇关于功能光标分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
