oracle sql hierarchical queries

Question

I have two tables:

CREATE TABLE CATEGORY (
  cat_id      NUMBER PRIMARY KEY,
  cat_ust_id  NUMBER REFERENCES Category( cat_id )
);

CREATE TABLE PRODUCT (
  cat_1       NUMBER REFERENCES Category( cat_id ),
  cat_2       NUMBER REFERENCES Category( cat_id ),
  cat_3       NUMBER REFERENCES Category( cat_id ),
  cat_4       NUMBER REFERENCES Category( cat_id ),
  cat_id      NUMBER PRIMARY KEY
                     REFERENCES Category( cat_id )
);

INSERT INTO Category
SELECT    1, NULL FROM DUAL UNION ALL
SELECT    2, NULL FROM DUAL UNION ALL
SELECT   11,    1 FROM DUAL UNION ALL
SELECT  112,   11 FROM DUAL UNION ALL
SELECT  202,   24 FROM DUAL UNION ALL
SELECT   24,    2 FROM DUAL UNION ALL
SELECT 2035,  203 FROM DUAL UNION ALL
SELECT  203,   20 FROM DUAL UNION ALL
SELECT   20,    2 FROM DUAL;

INSERT INTO Product
SELECT 1, NULL, NULL, NULL, 11 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 202 FROM DUAL UNION ALL
SELECT 1, NULL, NULL, NULL, 112 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 2035 FROM DUAL;

In PRODUCT table, I have to update some column according to CATEGORY table hierarchy to get this result:

cat_1 | cat_2 | cat_3 | cat_4 | cat_id
---------------------------------------
    1 |    11 |  NULL |  NULL |     11
    2 |    24 |   202 |  NULL |    202
    1 |    11 |   112 |  NULL |    112
    2 |    24 |   203 |  2035 |   2035

Should I create a procedure or function for this?

Answer 1

Oracle Setup:

CREATE TABLE Category ( CAT_ID, CAT_UST_ID ) AS
SELECT    1, NULL FROM DUAL UNION ALL
SELECT    2, NULL FROM DUAL UNION ALL
SELECT   11,    1 FROM DUAL UNION ALL
SELECT  112,   11 FROM DUAL UNION ALL
SELECT  202,   24 FROM DUAL UNION ALL
SELECT   24,    2 FROM DUAL UNION ALL
SELECT 2035,  203 FROM DUAL UNION ALL
SELECT  203,   20 FROM DUAL UNION ALL
SELECT   20,    2 FROM DUAL;

CREATE TABLE Product ( Cat1, Cat2, Cat3, Cat4, Cat_ID ) AS
SELECT 1, CAST( NULL AS NUMBER ), CAST( NULL AS NUMBER ), CAST( NULL AS NUMBER ), 11 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 202 FROM DUAL UNION ALL
SELECT 1, NULL, NULL, NULL, 112 FROM DUAL UNION ALL
SELECT 2, NULL, NULL, NULL, 2035 FROM DUAL;

Update:

UPDATE Product p
SET ( cat1, cat2, cat3, cat4 ) = (
          SELECT new_cat1,
                 new_cat2,
                 new_cat3,
                 new_cat4
          FROM   (
            SELECT TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 1 ) ) AS new_cat1,
                   TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 2 ) ) AS new_cat2,
                   TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 3 ) ) AS new_cat3,
                   TO_NUMBER( REGEXP_SUBSTR( SYS_CONNECT_BY_PATH( CAT_ID, ',' ), '\d+', 1, 4 ) ) AS new_cat4,
                   cat_id
            FROM   Category
            START WITH CAT_UST_ID IS NULL
            CONNECT BY CAT_UST_ID = PRIOR CAT_ID
          ) c
          WHERE p.cat_id = c.cat_id
        );

Results:

SELECT * FROM Product;

gives:

      CAT1       CAT2       CAT3       CAT4     CAT_ID
---------- ---------- ---------- ---------- ----------
         1         11                               11 
         2         24        202                   202 
         1         11        112                   112 
         2         20        203       2035       2035