Needs through stored procedures for the table B supply with material with customers in the assigned to list A number of data, the need to supply data to assign A date less than demand, eventually hopes to achieve the following results:
CodePudding user response:
Create table structure, and insert the data:
CREATE TABLE "TABLE A"
(" customer "CHAR (1),
"Material code" VARCHAR2 (10),
"Need DATE" the DATE,
"Demand" number,
"Allocated supply number");
CREATE TABLE "TABLE B
"(" customer "CHAR (1),
"Material code" VARCHAR2 (10),
"Supply DATE" the DATE,
"Supply" number,
"Allocated supply number");
INSERT INTO table "A" VALUES (' A ', 'ITEM01', the DATE '2018-01-01', 100, 0).
INSERT INTO table "A" VALUES (' A ', 'ITEM01', the DATE '2018-02-01', 100, 0).
INSERT INTO table "A" VALUES (' A ', 'ITEM01', the DATE '2018-03-01', 100, 0).
INSERT INTO table "A" VALUES (' A ', 'ITEM01', the DATE '2018-04-01', 100, 0).
INSERT INTO table "A" VALUES (' A ', 'ITEM01', the DATE '2018-05-01', 100, 0).
INSERT INTO table "A" VALUES (' B ', 'ITEM01', the DATE '2018-01-01', 80, 0).
INSERT INTO table "A" VALUES (' B ', 'ITEM01', the DATE '2018-02-01', 80, 0).
INSERT INTO table "A" VALUES (' B ', 'ITEM01', the DATE '2018-03-01', 80, 0).
INSERT INTO table "A" VALUES (' B ', 'ITEM01', the DATE '2018-04-01', 80, 0).
INSERT INTO table "A" VALUES (' B ', 'ITEM01', the DATE '2018-05-01', 80, 0).
INSERT INTO "table B" VALUES (' A ', 'ITEM01', the DATE '2017-12-10', 300, 0).
INSERT INTO "table B" VALUES (' A ', 'ITEM01', the DATE '2018-04-10', 150, 0).
INSERT INTO "table B" VALUES (' B ', 'ITEM01', the DATE '2018-03-10', 100, 0).
COMMIT;
CREATE TABLE "results"
(" customer "CHAR (1),
"Material code" VARCHAR2 (10),
"Need DATE" the DATE,
"Demand" number,
"Allocated supply number");
Stored procedure code
CREATE OR REPLACE PROCEDURE p1 AS
V_supply NUMBER;
V_keep NUMBER;
The BEGIN
V_supply:=0;
FOR r1 (IN the SELECT a. *, row_number () over (PARTITION BY "customers", "material code" ORDER BY "supply date") seqno
FROM "table B" a
ORDER BY "the client", "material code", "supply date") LOOP
IF r1. Seqno=1 THEN
V_supply:=r1. Supply;
The ELSE
V_supply:=v_supply + r1. Supply;
END IF;
FOR r2 IN (SELECT *
FROM "table A"
WHERE "clients"=r1.
"clients"AND "material code"=r1. "material code"
The ORDER BY "need date") LOOP
IF r2. "need date" & gt;=r1. "supply date" THEN
V_keep:=further (v_supply, r2. "demand");
V_supply:=v_supply - v_keep;
The ELSE
V_keep:=0;
END IF;
Dbms_output. Put_line (r1. "customers" | | ', '| | r1. "supply date" | |', '| | r2. The demand date | |', '| | v_keep | |', '| |
V_supply);
The MERGE INTO "result" a
USING (SELECT r2. "customers", "clients" r2. "material code" material code ", "r2. The" need date "need date", "r2." demand "demand", "
V_supply "allocated supply
"The FROM dual) b
ON (a.=b. "clients" AND "clients" a. "material number=" b. "material code" AND a. "need date"=b. "need date")
The WHEN MATCHED THEN
UPDATE the SET "allocated supply"="allocated supply" + v_keep
WHEN NOT MATCHED THEN
INSERT
(" customer ", "material code", "need date", "demand", "allocated supply")
VALUES
(b. b. "customers", "material code", b. "need date", b. "demand", v_keep);
END LOOP;
END LOOP;
COMMIT;
END;
Execute the stored procedure:
The BEGIN
P1.
END;
- results of a query
SELECT * FROM "result";
CodePudding user response: