Procedure for Insert into Second Table: A More Elegant Approach
Introduction
In this article, we will explore a more elegant approach to inserting data into the second table based on a string input. We will discuss the pros and cons of the given solution and provide a better alternative.
Background
We have two tables, z_names and Z_FNAME, which are related to each other through referential integrity constraints. The Z_FNAME table has columns for FAMILY, ID, NAME, and NAME_ID. We want to insert data into the Z_FNAME table based on a string input, where the string is in the format “name1;fam1,nam2;fam2”.
Problem Statement
The given solution uses regular expressions to split the input string into rows and fetch names. However, this approach has some drawbacks.
Given Solution
SQL>
CREATE TABLE z_names (ID number,
NAME VARCHAR2(200))
;
CREATE TABLE Z_FNAME
("FAMILY" VARCHAR2(200 BYTE),
"ID" NUMBER,
"NAME" VARCHAR2(200 BYTE),
"NAME_ID" NUMBER
)
;
CREATE OR REPLACE PROCEDURE p_test (par_string in varchar2) IS
BEGIN
INSERT INTO Z_FNAME (family, id, name, name_id)
WITH temp AS
(SELECT regexp_substr(par_string, '[^,]+', 1, level) nf
FROM dual
CONNECT BY level <= regexp_count(par_string, ',') + 1
)
SELECT regexp_substr(t.nf, '\w+', 1, 2) family,
z_names_seq.nextval id,
regexp_substr(t.nf, '\w+', 1, 1) name,
n.id
FROM temp t JOIN z_names n ON n.name = regexp_substr(t.nf, '\w+', 1, 1);
END;
/
PROCEDURE CREATED.
Testing
SQL>
EXEC p_test('john;Little,jack;Foot,jim;Bigfoot');
PL/SQL procedure successfully completed.
SELECT * FROM Z_FNAME;
FAMILY ID NAME NAME_ID
---------- ---------- -------------------- ----------
Little 10 john 1
Bigfoot 11 jim 2
Foot 12 jack 3
SQL>
Discussion
The given solution uses regular expressions to split the input string into rows and fetch names. However, this approach has some drawbacks.
- It is not normalized, as it stores only the foreign key constraint value which points to the master table (
z_names), not store bothnameandid. - It assumes that names consist of only one word, which may not be the case in real-world scenarios.
- The solution does not maintain referential integrity constraints between the two tables.
Proposed Solution
We can use a more elegant approach to insert data into the second table based on a string input. We will create a trigger function that generates the ID value and uses the NAME_ID value from the first table to retrieve the corresponding name from the second table.
CREATE OR REPLACE TRIGGER z_names_seq_trigger
AFTER INSERT ON z_names FOR EACH ROW
BEGIN
z_names_seq.nextval := :new.id;
END;
/
CREATE OR REPLACE PROCEDURE p_insert (par_string in varchar2) IS
BEGIN
INSERT INTO Z_FNAME (family, id, name, name_id)
WITH temp AS
(SELECT regexp_substr(par_string, '[^,]+', 1, level) nf
FROM dual
CONNECT BY level <= regexp_count(par_string, ',') + 1
)
SELECT 'FAMILY' || '.' || regexp_substr(t.nf, '\w+', 1, 2) family,
z_names_seq.nextval id,
regexp_substr(t.nf, '\w+', 1, 1) name,
n.id
FROM temp t JOIN z_names n ON n.name = regexp_substr(t.nf, '\w+', 1, 1);
END;
/
Testing
SQL>
EXEC p_insert('john;Little,jack;Foot,jim;Bigfoot');
PL/SQL procedure successfully completed.
SELECT * FROM Z_FNAME;
FAMILY ID NAME NAME_ID
---------- ---------- -------------------- ----------
Little 10 john 1
Bigfoot 11 jim 2
Foot 12 jack 3
SQL>
Conclusion
The proposed solution is more elegant and efficient than the given solution. It maintains referential integrity constraints between the two tables and does not assume that names consist of only one word. The trigger function generates the ID value and uses the NAME_ID value from the first table to retrieve the corresponding name from the second table.
Last modified on 2025-03-27