postgresql - PostgreSQL - 使用 CURSORS 将 2 个 TABLES 的数量“分配”/“隔离”到第三个 TABLE

Question

我有以下 2 个表：

SALES_ORDERS此表包含特定项目的客户销售订单

    DROP TABLE IF EXISTS "SALES_ORDERS";

    CREATE TABLE "SALES_ORDERS" ( 
    "SO" int4,
    "ITEM" varchar(255), 
    "QTY" int4 );

    INSERT INTO "SALES_ORDERS" VALUES (101, 'iPhone RED', 39);
    INSERT INTO "SALES_ORDERS" VALUES (102, 'iPhone BLACK', 100);
    INSERT INTO "SALES_ORDERS" VALUES (103, 'iPhone RED', 34);
    INSERT INTO "SALES_ORDERS" VALUES (104, 'iPhone RED', 204);
    INSERT INTO "SALES_ORDERS" VALUES (105, 'iPhone BLACK', 50);

WORK_ORDERS此表包含客户订单履行的计划订单

    DROP TABLE IF EXISTS "WORK_ORDERS";

    CREATE TABLE "WORK_ORDERS" (
    "WO" int4,
    "ITEM" varchar(255),
    "QTY" int4 );

    INSERT INTO "WORK_ORDERS" VALUES (5001, 'iPhone RED', 273);
    INSERT INTO "WORK_ORDERS" VALUES (5002, 'iPhone BLACK', 50);
    INSERT INTO "WORK_ORDERS" VALUES (5003, 'iPhone RED', 28);
    INSERT INTO "WORK_ORDERS" VALUES (5004, 'iPhone BLACK', 100);

我正在寻找第三张表中列出的输出：

SALES_ORDER_FULFILLMENT此表根据第一个收到的客户订单和第一个计划工作订单分配工作订单数量与销售订单数量

    DROP TABLE IF EXISTS "SALES_ORDER_FULFILLMENT";

    CREATE TABLE "SALES_ORDER_FULFILLMENT" (
      "SO" int4,
      "SO_ITEM" varchar(255),
      "SO_QTY" int4,
      "WO" int2,
      "WO_QTY" int4,
      "WO_FULFILLED_QTY" int4,
      "WO_REMAINING_QTY" int4,
      "SO_DELTA" int4
    );

    INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES (101, 'iPhone RED', 39, 5001, 273, 39, 234, 0);
    INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES (103, 'iPhone RED', 34, 5001, 273, 34, 200, 0);
    INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES (104, 'iPhone RED', 204, 5001, 273, 200, 0, 4);
    INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES (104, 'iPhone RED', 204, 5003, 28, 4, 24, 0);
    INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES (102, 'iPhone BLACK', 100, 5002, 50, 50, 0, 50);
    INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES (102, 'iPhone BLACK', 100, 5004, 100, 50, 50, 0);
    INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES (105, 'iPhone BLACK', 50, 5004, 100, 50, 0, 0);

我尝试使用 2 个 CURSORS 和 2 个 LOOPS 来获取一个 work_order，然后是同一项目的所有 sales_orders 并进行分配。

但是，我面临的问题是，当“work_order_qty”不足以满足特定的“sales_order”时，CURSOR 获取下一个“work_order”，前一个未完成的“sales_order”的引用丢失，因此分配开始再次。

这是我目前拥有的代码，请注意它不是当前代码或完整代码，我在此处列出仅供参考。

我确信这可能是一种更简单或更有效的方法。我也在寻找一些带有递归查询的解决方案，任何方向都会非常感激，谢谢。

CREATE OR REPLACE FUNCTION "SE"."WO_ALLOCATION"()
RETURNS "pg_catalog"."void" AS $BODY$

DECLARE
so_cur cursor for SELECT * FROM "SALES_ORDERS";
so_row "SALES_ORDERS"%rowtype;
so_qty integer;
so_delta integer;

wo_cur cursor for SELECT * FROM "WORK_ORDERS";
wo_row "WORK_ORDERS"%rowtype;
wo_qty integer;
wo_fulfilled integer;
wo_remaining integer;

FOR wo_row IN wo_cur LOOP

    wo_fulfilled = 0;
    wo_remaining = wo_row."QTY";

    WHILE (wo_remaining > 0 ) LOOP

          FOR so_row IN so_cur LOOP

          so_qty = so_row."QTY";

          IF (wo_remaining > so_qty) THEN 
             wo_fulfilled = so_qty;
             wo_remaining = wo_remaining - wo_fulfilled;
             so_delta = so_qty - wo_fulfilled;
          END IF;

         IF (wo_remaining < so_qty) THEN 
             wo_fulfilled = wo_remaining;
             wo_remaining = wo_remaining - wo_fulfilled;
             so_delta = so_qty - wo_fulfilled;
         END IF;


        INSERT INTO "SALES_ORDER_FULFILLMENT" VALUES(
        so_row."SO",
        so_row."ITEM",
        so_row."QTY",
        wo_row."WO",
        wo_row."QTY",
        wo_fulfilled,
        wo_remaining,
        so_delta);

        END LOOP;

    END LOOP;

END LOOP;

  END
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

标签： postgresqlrecursive-querydatabase-cursor