Procedure to find multiples values in a table and insert if doesnt exist using pl/sql

Question

I have this table with this relationship: UNIT --> CHARGE ---> ROLE

UNIT_CODE    |    CHARGE_CODE    |    ROLE_CODE
0101010001   |     9023409       |    AZAAAA
0102010002   |     8023409       |    AXAAAB
0103010003   |     7023409       |    ACAAAC
0104010004   |     6023409       |    AVAAAV
0101010001   |     5023409       |    NEWROL
0102010002   |     4023409       |    AZAAAA

This table has almost 2.000 rows/line.

So, here is the situations:

I have to insert a specific ROLE_COD (NEWROL) for a specific 90 unit_code. But this role apply for a specific charge of the units.

Example: i have to insert the role_code = NEWROLE for this units 0101010001 and this charge 5023409

• So, i have to check:

  1. If units 0101010001 already exist? - YES
  2. If the unit / charge 0101010001 / 5023409 relationship already exist? - YES
  3. If the unit / charge / rol 0101010001 / 5023409 / NEWROL already exist? - YES

If the answer is YES for the 3 questions i have to check other unit / charge combinations:

0102010002 / 4023409

1. If units 0102010002 already exist? - YES
2. If the unit / charge 0102010002 / 4023409 relationship already exist? - YES
3. If the unit / charge / rol 0102010002 / 4023409 / NEWROL already exist? - NO

So i have to insert into the table the role: NEWROL for this units/charge and check the other units and charge.

NOTE: The units are unique, but some units have the same/multiples roles or charge.

I have to check 90 units, i need a procedure to take the unit_code and charge to make the validations and insert the role_code if necesary.

Answer 1

I solved the situation using this query:

CREATE OR REPLACE PROCEDURE my_procedure
    (in_unit_code IN table_1.unit_code%TYPE,
     in_charge_code IN table_1.charge_code%TYPE,
     in_role_code IN table_1.role_code%TYPE)
    AS
        BEGIN
        trace('my_procedure_p', 'a', 'Role Insert starting');
            INSERT INTO table_1 (
                unit_code,
                charge_code,
                role_code
                ) values (
               in_unit_code,
               in_charge_code,
               in_role_code
           );
           trace('my_procedure_p', 'a',
           'It is inserted correctly. ROLE: ' || in_role_code ||
           ', UNIT: ' || in_unit_code ||
           ', CHARGE: ' || in_charge_code ||
           ' Date: ' || SYSDATE);    
           COMMIT;
    --Note: If the insert is successful i COMMIT, but if records are duplicates i captured with this exception.
    EXCEPTION
    WHEN dup_val_on_index THEN
    ptraza('my_procedure_p', 'a',
    'Attention, ROLE: ' || in_role_code ||
    ' It is already in the table UNIT: ' || in_unit_code ||
    ', CHARGE: ' || in_charge_code);
END my_procedure;

And i make the call like this:

BEGIN
    my_procedure('0000000004','CHARGE01','ROLECODE01');
    my_procedure('0000000003','CHARGE02','ROLECODE01');
    my_procedure('0000000002','CHARGE00','ROLECODE03');
    my_procedure('0000000001','CHARGE00','ROLECODE03');
END;