CODEWITHSUNDEEP
oracle-databaseplsqluniquesql-insert
Iren
Iren

Reputation: 43

How to check if I don't have a row with current id before insert, stored procedure pl/sql

My procedure gets data from several tables. I loop through these resulting rows and for each temp row I insert a new row into ZZ_TEST_RESULT table. Before inserting I want to verify that this (ZZ_TEST_RESULT) table doesn't have ID (PK) which I want to insert. ZZ_TEST.ID is PK, ZZ_TEST_RESULT.ID is also PK.

Help me to add the condition ZZ_TEST.ID = ZZ_TEST_RESULT.ID.

create or replace PROCEDURE "PRO_ZZZTEST"
AS
 CURSOR myCUR IS
         SELECT  ID, NAME, DESCR from ZZ_TEST;
         counter NUMBER(10) := 1;
BEGIN
  FOR ROWTEMP IN myCUR LOOP
    EXIT WHEN myCUR%NOTFOUND;
    INSERT INTO ZZ_TEST_RESULT (ID, NAME) VALUES (ROWTEMP.ID,ROWTEMP.NAME);
    COMMIT;

        DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter || '; Id = '|| TO_CHAR(ROWTEMP.ID));  
     counter := counter + 1;
  END LOOP;

END PRO_ZZZTEST;

Upvotes: 1

Views: 158

Answers (4)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

Log errors method.
1) Set up. 

begin
DBMS_ERRLOG.CREATE_ERROR_LOG ('ZZ_TEST_RESULT');
end;

The script will create dedicated table for dml errors. The name is ERR$_+table_name, (ERR$_ZZ_TEST_RESULT)
2) Change your insert, 

 INSERT INTO ZZ_TEST_RESULT (ID, NAME) VALUES (ROWTEMP.ID,ROWTEMP.NAME) log errors ('optional message')  REJECT LIMIT UNLIMITED

3) Verify error table.

select * from ERR$_ZZ_TEST_RESULT

Note, This method will capture all type of DML exceptions not only dup val error

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

The simplest approach is to ignore duplicates using the ignore_row_on_dupkey_index hint:

create or replace procedure pro_zzztest as
begin
    for cur_r in (
        select id, name, descr from zz_test
    )
    loop
        insert /*+ ignore_row_on_dupkey_index(zz_test_result(id)) */
        into zz_test_result (id, name) values (cur_r.id, cur_r.name);
    end loop;
end;

Upvotes: 4

APC
APC

Reputation: 146349

The pure SQL solution is MERGE. 

merge into zz_test_result ztr  
  using (select  id, name, descr 
         from zz_test) zt
  on (zt.id = ztr.id)
when not matched then
  insert (ztr.id, ztr.name)
  values (zt.id, zt.name)
/

The USING clause is the query from your cursor. The ON clause is the condition you want to test. The WHEN NOT MATCHED branch means rows are only inserted when that condition is false.

Obviously if this is homework and PL/SQL is mandated then that's what you need to hand in. But, in real life, in professional enterprise software, it is always better to write the least amount of code necessary to deliver a performative solution, and that almost always means SQL set operations rather than row-by-row procedural operations.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143083

As usual, there are different ways to do it. I'd suggest inner begin-exception-end block (within the loop). Doing so, if a primary key constraint is violated, exception will be raised and handled (don't do anything), and the loop will continue its execution.

Here's how:

create or replace procedure pro_zzztest as
begin
  for cur_r in (select id, name, descr from zz_test) loop
    -- inner block starts here
    begin
      insert into zz_test_result (id, name) values (cur_r.id, cur_r.name);
    exception
      when dup_val_on_index then
        null;
    end;
    -- inner block ended here
  end loop;
end;
/

Upvotes: 0

Related Questions