CODEWITHSUNDEEP
oracle-databaseplsql
siretep
siretep

Reputation: 3

pl/SQL Procedure to generate values in one table from values in a different table

I am learning PL/SQL and don't really know where to start writing a procedure.

I have two tables, one with a department name , id ranges from and id ranges to. And another table that needs to have all values in those ranges. 

-- table_a
CREATE TABLE table_a (
    D_ID number not null primary key,
    DEPT VARCHAR(10),
    ID_FROM VARCHAR(7),
    ID_TO VARCHAR(7),
    IS_POP varchar(1) check(IS_POP IN ('Y', 'N'))
    );

--values in table a
INSERT INTO table_a values (1, 'abc', 'A10', 'A100', 'Y');
INSERT INTO table_a values (2, 'def', 'B10', 'B50', 'N');
INSERT INTO table_a values (3, 'ghi', 'C01', 'C25', 'N');

--table_b
CREATE TABLE table_b (
    D_id number,
    ID_NUM VARCHAR(7) primary key,
    STATUS VARCHAR(8) CHECK (status IN ('Free','Taken')),
    CONSTRAINT fk_interval FOREIGN KEY (D_id) REFERENCES table_a (D_ID)
    );

I am trying to write a procedure to populate table_b with values generated from table_a, where the IS_POP field has 'N', and change it to 'Y' when the values are generated. So table_b should have entries with 66 rows of something like 

D_id ID_NUM STATUS
2     B10   Free
2     B11   Free
2     B12   Free
2     B13   Free
...
3     C24   Free
3     C25   Free

Upvotes: 0

Views: 40

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

Here's an example procedure that produces the 66 records in this example data, though I was unsure is some uncertainty about whether the zero-padding is required in TABLE_B or not.

This also uses iterative DML, so could provide poor performance if used at scale, and it would not be suitable to hand a situation where TABLE_A was updated after having already being populated to TABLE_B. These things could be enhanced as required in your situation.

CREATE OR REPLACE PROCEDURE POPULATE_TAB_B
IS
BEGIN
    FOR POINTER IN
        (SELECT D_ID,
             REGEXP_REPLACE(ID_FROM , '[0-9].*' , NULL)             AS PREFIX,
             LEAST(LENGTH(REGEXP_REPLACE(ID_FROM , '[^0-9]' , NULL)) ,
                   LENGTH(REGEXP_REPLACE(ID_TO , '[^0-9]' , NULL))) AS PAD_LENGTH,
             TO_NUMBER(REGEXP_REPLACE(ID_FROM , '[^0-9]' , NULL))   AS FROM_NUM,
             TO_NUMBER(REGEXP_REPLACE(ID_TO , '[^0-9]' , NULL))     AS TO_NUM
         FROM TABLE_A
         WHERE TABLE_A.IS_POP = 'N' FOR UPDATE NOWAIT)
        LOOP
            INSERT INTO TABLE_B(D_ID , ID_NUM , STATUS)
            SELECT POINTER.D_ID,
                (POINTER.PREFIX || LPAD(TO_CHAR(((LEVEL - 1) + POINTER.FROM_NUM)) , POINTER.PAD_LENGTH , '0')), 'Free'
            FROM DUAL
            CONNECT BY LEVEL <= ((POINTER.TO_NUM + 1) - POINTER.FROM_NUM);

            UPDATE TABLE_A
            SET TABLE_A.IS_POP = 'Y'
            WHERE TABLE_A.D_ID = POINTER.D_ID;

        END LOOP;

END;
/

Procedure created.

BEGIN
    POPULATE_TAB_B();
END;
/

PL/SQL procedure successfully completed.

SELECT *
FROM TABLE_B
ORDER BY 1 ASC, 2 ASC;

 D_ID ID_NUM   STATUS
     2 B10      Free
     2 B11      Free
     2 B12      Free
     2 B13      Free
...
     3 C08      Free
     3 C09      Free
     3 C10      Free
     3 C11      Free
...
     3 C24      Free
     3 C25      Free

Upvotes: 0

Related Questions