I'm stuck with the problem of returning more than one row in the query where there should be no more than one row and I do not understand where the problem is. Below is fully working code that reproduces the issue. The problem is in the very last inner loop that selects INTO m2id:
CREATE TABLE m1(
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
name NVARCHAR2(50)
);
CREATE TABLE m2(
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
m1id INTEGER,
name NVARCHAR2(50)
);
CREATE TABLE m3(
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
m1id INTEGER,
m2id INTEGER,
name NVARCHAR2(50)
);
ALTER TABLE m1 ADD CONSTRAINT pk_m1 PRIMARY KEY (id);
ALTER TABLE m2 ADD CONSTRAINT pk_m2 PRIMARY KEY (id);
ALTER TABLE m2 ADD CONSTRAINT fk_m2_m1id FOREIGN KEY (m1id) REFERENCES m1(id);
ALTER TABLE m3 ADD CONSTRAINT fk_m3_m1id FOREIGN KEY (m1id) REFERENCES m1(id);
ALTER TABLE m3 ADD CONSTRAINT fk_m3_m2id FOREIGN KEY (m2id) REFERENCES m2(id);
DECLARE
`cnt INT;`
`maincntmax INT := 5;`
`maincnt INT;`
`m1id INT;`
`m2id INT;`
BEGIN
`maincnt := 0;`
`WHILE maincnt < maincntmax LOOP`
`cnt := 0;`
`INSERT INTO m1 (name)`
`VALUES ('grpA-' || maincnt)`
`RETURNING id INTO m1id;`
`WHILE cnt < 14 LOOP`
`INSERT INTO m2 (m1id, name)`
`VALUES (m1id, 'pnt-' || cnt);`
`cnt := cnt + 1;`
`END LOOP;`
`cnt := 0;`
`WHILE cnt < 10 LOOP`
`SELECT id`
`INTO m2id`
`FROM m2`
`WHERE name = 'pnt-' || cnt AND m1id = m1id;`
`INSERT INTO m3 (m1id, m2id, name)`
`VALUES (m1id, m2id, 'ac-' || cnt);`
`cnt := cnt + 1;`
`END LOOP;`
`maincnt := maincnt + 1;`
`END LOOP;`
END;
/