用于分配的 Oracle 游标 [英] Oracle cursor for assignation

查看：39 发布时间：2021/6/12 20:59:08 oracle plsql

本文介绍了用于分配的 Oracle 游标的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下 2 个表:

<头>

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

<头>

A	C
A1	4
A2	10
A3	2
A4	10

我想创建一个 plsql 游标来添加一个具有以下逻辑的列 AF:

目标是将 t2 中的不同 As 分配给 t1 中的行.R 列决定了它们的分配顺序(如果可能).
如果填充了 AY 列，那么如果 t2 中的 C 仍然 > 0(在之前的分配之后)，则应将A"分配给 AF.
如果填充了 AN 列，则此处列出的任何A"都不能分配给该行.
'A' 的分配必须与其在 t2 中的 C 成比例.

以下是所需输出的一个示例(尽管可以按任何其他顺序分配 AF).

<头>

VK	AY	AN	R	AF	解释
VK1		A1、A2	1	A4	A3 和 A4 可用:选择 A4.
VK2			2	A2	所有可用:选择 A2.
VK3		A1、A2、A3、A4	3	空	无可用:不能选择任何.
VK4		A2	4	A4	除 A2 外的所有可用:选择 A4
VK5			5	A2	所有可用:选择 A2.
VK6			6	A1	所有可用:选择 A1.
VK7	A3		7	A3	必须分配A3:选择A3
VK8			8	A2	所有可用:选择 A2.
VK9			9	A4	所有可用:选择 A4.
VK10			10	A4	所有可用:选择 A4.
VK11			11	A2	所有可用:选择 A2.
VK12			12	A1	所有可用:选择 A1.
VK13	A3		13	A3	必须分配A3:选择A3
VK14			14	A2	所有可用:选择 A2.
VK15	A3		15	空	必须分配A3但A3已用完:无法分配
VK16			16	A4	所有可用:选择 A4.
VK17			17	A4	所有可用:选择 A4.
VK18			18	A2	所有可用:选择 A2.
VK19			19	A2	所有可用:选择 A2.
VK20			20	A1	所有可用:选择 A1.

请在下面找到表的示例查询:

with t1 (vk, ay, an, r) as(select 'VK1', null, 'A1, A2', 1 from dual union allselect 'VK2', null, null, 2 from dual union allselect 'VK3', null, 'A1, A2, A3, A4', 3 from dual union allselect 'VK4', null, 'A2', 4 from dual union allselect 'VK5', null, null, 5 from dual union allselect 'VK6', null, null, 6 from dual union allselect 'VK7', 'A3', null, 7 from dual union allselect 'VK8', null, null, 8 from dual union allselect 'VK9', null, null, 9 from dual union allselect 'VK10', null, null, 10 from dual union allselect 'VK11', null, null, 11 from dual union allselect 'VK12', null, null, 12 from dual union allselect 'VK13', 'A3', null, 13 from dual union allselect 'VK14', null, null, 14 from dual union allselect 'VK15', 'A3', null, 15 from dual union allselect 'VK16', null, null, 16 from dual union allselect 'VK17', null, null, 17 from dual union allselect 'VK18', null, null, 18 from dual union allselect 'VK19', null, null, 19 from dual union allselect 'VK20', null, null, 20 from dual),t2 (a, c) 为 (选择 'A1', 4 from dual union all选择 'A2', 10 from dual union all选择 'A3', 2 from dual union all选择A4"，双选 10)从 t1 中选择 *；

解决方案

设置数据类型和辅助函数:

CREATE TYPE string_list IS TABLE OF VARCHAR2(2);/创建类型 int_list 是 INT 表；/创建类型 t1_data 作为对象(vk VARCHAR2(4),是 VARCHAR2(2)，一个 VARCHAR2(14)，INT，c VARCHAR2(2));/创建类型 t1_table 是 t1_data 表；/创建或替换函数 split_String(i_str IN VARCHAR2，i_delim IN VARCHAR2 DEFAULT ',') 返回 string_list DETERMINISTIC作为p_result string_list := string_list();p_start NUMBER(5) := 1;p_end NUMBER(5);c_len 常数 (5) := LENGTH( i_str );c_ld 常数 (5) := LENGTH( i_delim );开始-- https://stackoverflow.com/a/35577315/1509264-- 许可证:CC BY-SA 4.0如果 c_len >0 那么p_end := INSTR( i_str, i_delim, p_start );而 p_end >0 循环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 );结束循环；如果 p_start <= c_len + 1 THENp_result.EXTEND;p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );万一;万一;返回 p_result;结尾;/

然后就可以使用函数了:

CREATE FUNCTION pick_values RETURN t1_table PIPELINED是TYPE t2_type 是 t2%ROWTYPE 表；t2_data t2_type;开始-- https://stackoverflow.com/a/67398434/1509264-- 许可证:CC BY-SA 4.0选择 *批量收集到 t2_data从 t2;FOR cur IN ( SELECT * FROM t1 ORDER BY r )环形宣布a_freqs INT_LIST := INT_LIST();cum_freq INT := 0;采用 STRING_LIST := SPLIT_STRING( cur.an, ', ' );idx INT;c T2.A%TYPE;开始a_freqs.EXTEND(t2_data.COUNT);FOR i IN 1 .. t2_data.COUNT 循环IF ( t2_data(i).a = cur.ay AND t2_data(i).c > 0 )或( cur.ay 为 NULL AND t2_data(i).a NOT MEMBER OF Taked AND t2_data(i).c > 0 )然后a_freqs(i) := cum_freq + t2_data(i).c;cum_freq := cum_freq + t2_data(i).c;别的a_freqs(i) := cum_freq;万一;结束循环；如果 cum_freq >0 那么idx := FLOOR(DBMS_RANDOM.VALUE(0, cum_freq));FOR i IN 1 .. t2_data.COUNT 循环如果idx＜a_freqs(i) THENc := t2_data(i).a;t2_data(i).c := t2_data(i).c - 1;出口;万一;结束循环；万一;管排(t1_data(cur.vk、cur.ay、cur.an、cur.r、c));结尾;结束循环；结尾;/

对于您的示例数据:

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

那么:

SELECT * FROM PICK_VALUES();

可能输出:

<块引用>

<头>

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

db<>fiddle 这里

I have the below 2 tables:

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

A	C
A1	4
A2	10
A3	2
A4	10

I would like to create a plsql cursor to add a column AF with the logic below:

The objective is to assign the different As in t2 to the rows in t1. It's column R which determines the order in which, if possible, they should be assigned.
If column AY is populated, then that 'A' should be assigned to AF if C in t2 is still >0 (after the previous assignations).
If column AN is populated, then none of the 'A's listed there can be assigned to that row.
The assignation of 'A's must be done proportionally to their C in t2.

The below would be one example of desired output (AF could be assigned in any other order though).

VK	AY	AN	R	AF	EXPLANATION
VK1		A1, A2	1	A4	A3 and A4 available: A4 chosen.
VK2			2	A2	All available: A2 chosen.
VK3		A1, A2, A3, A4	3	null	None are available: cannot pick any.
VK4		A2	4	A4	All but A2 available: A4 chosen
VK5			5	A2	All available: A2 chosen.
VK6			6	A1	All available: A1 chosen.
VK7	A3		7	A3	A3 must be assigned: A3 chosen
VK8			8	A2	All available: A2 chosen.
VK9			9	A4	All available: A4 chosen.
VK10			10	A4	All available: A4 chosen.
VK11			11	A2	All available: A2 chosen.
VK12			12	A1	All available: A1 chosen.
VK13	A3		13	A3	A3 must be assigned: A3 chosen
VK14			14	A2	All available: A2 chosen.
VK15	A3		15	null	A3 must be assigned but A3 already exhausted: cannot be assigned
VK16			16	A4	All available: A4 chosen.
VK17			17	A4	All available: A4 chosen.
VK18			18	A2	All available: A2 chosen.
VK19			19	A2	All available: A2 chosen.
VK20			20	A1	All available: A1 chosen.

Please find below the sample query for the tables:

with 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),
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)
select * from t1;

解决方案

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

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

db<>fiddle here

这篇关于用于分配的 Oracle 游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

用于分配的 Oracle 游标 [英] Oracle cursor for assignation

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

用于分配的 Oracle 游标 [英] Oracle cursor for assignation

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭