Records in INSERT and UPDATE

We can semplify our PL/SQL code using records to perform INSERT and UPDATE statements.

Here is an example where we try to INSERT a new row in table country, as available in the test Oracle HR schema. If the country id is already in the table, we get an exception, so we fall back to call an UPDATE:

declare
  l_country countries%rowtype; -- 1.
begin
  l_country.country_id := 'VA'; -- 2.
  l_country.country_name := 'Vatican';
  l_country.region_id := 1;
  
  insert into countries values l_country; -- 3.
exception
  when dup_val_on_index then -- 4.
    dbms_output.put_line('Country already inserted');
    update countries
    set row = l_country -- 5.
    where country_id = l_country.country_id;
end;

1. Declaration of a record matching the countries table definition.
2. Record setup
3. INSERT for record: after VALUES no round brackets.
4. Exception when we try to insert a country id already existing.
5. In UPDATE we use the SET ROW clause.

More information on DML in PL/SQL in chapter 14 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.