How can I perform a SQL-INSERT/UPDATE with a virtual column-CodePudding

In my oracle application I'm using three columns and connect them in my SQL query to a product number:

SELECT id_part1 || '.' || id_part2 || '.' || id_part3 as product_number
FROM table

But I'm having trouble performing any update or insert query because of the virtual column. How can i perform these operations so that the virtual column is dissected into the three original columns and each value is saved correctly.

Thanks a lot

Jack

CodePudding user response：

The column product_number doesn't exists in the table so you can't UPDATE nor INSERT it

But I think you can use a simple trick to bypass the problem, you can try to add a phisical column product_number to your table and then add a BEFORE UPDATE trigger

CREATE OR REPLACE TRIGGER CalculatedColumnSplit
    BEFORE 
    INSERT OR UPDATE 
    ON myTable
    FOR EACH ROW
DECLARE
   
BEGIN
    :new.id_part1 := regexp_substr(:new.product_number,'[^.] ', 1, 1);
    :new.id_part2 := regexp_substr(:new.product_number,'[^.] ', 1, 2);
    :new.id_part3 := regexp_substr(:new.product_number,'[^.] ', 1, 3);
    :new.product_number := NULL;
END;

CodePudding user response：

As far as I understand from the comments and the question that you have a concerned an ordinary string type column product_number which needs to be splitted into other columns by dots. Then you can add virtual columns as desired like below

ALTER TABLE t
ADD (
    id_part1 AS (REGEXP_SUBSTR(product_number,'[^.] ',1,1)),
    id_part2 AS (REGEXP_SUBSTR(product_number,'[^.] ',1,2)),
    id_part3 AS (REGEXP_SUBSTR(product_number,'[^.] ',1,3))
);

those newly added added columns will totally depend on the column product_number and won't take any extra space within the related segment, but only represent your logic to display.

Demo