First, I offer this challenge: say “intricacies” five times and then tell me that it doesn’t sound really weird, like not a word at all.
OK, moving on. The SQL insert statement is a DML statement – data modification language. Oddly enough, so is select, but we will leave that for another day.
We use insert to add a row to a table. And you can do some interesting things with inserts. Now, wait just a minute. If insert is a SQL statement, then why am I – that guy who knows almost nothing but PL/SQL – talking about it? Because the “interesting intricacies” I will focus on are related to executing SQL from within PL/SQL.
Here are the variations of insert I know about (which you can also find and run in this LiveSQL script):
The Usual
You know, insert a row, specifying the values, as in:
insert into favorite_activity (title, description)
values ('Be Outside', 'Outside is always better than inside.')
Insert Select
Instead of providing expressions pin a values clause, I write a query to return those values. Insert-Select can be used to insert just one row, but it is more likely used to insert multiple rows into the table.
In the statement below, I transfer all of the exciting stuff I recorded in my diary for last year into my favorite activities table.
insert into favorite_activity (title, description) select what_happened, details from my_diary where very_exciting = 'Y' and extract (year from happened_on) = 2021
One statement, multiple rows. But all into the same table. Can I execute a single insert statement that adds rows to more than one table? Sure!
Insert All and Insert First
This variation of insert lets me insert one or more rows into one or more tables, driven by a select (in other words, it’s a variation on insert-select). It’s a powerful variation, which even includes its own sort of case expression if needed. I’ll show one example below. Check out Tim Hall’s page on multitable inserts for lots more detail.
insert all
when very_exciting = 'Y' then
into favorite_activity (title, description) values (what_happened, details)
when never_again = 'Y' then
into avoid_at_all_costs (location, reason) values (location, details)
select what_happened, location, details
from my_diary
where extract (year from happened_on) = 2021
Pretty neat, eh? To be completely honest, I wasn’t even aware of this capability before doing the research for this Feuertip. I would just have written a case expression in PL/SQL with different insert statements in each when clause. And I’m supposed to be some expert at Oracle Database? Wow.
With insert-all, each when clause will be evaluated; any that evaluate to true will insert a row. If you use the insert first syntax (all the same as insert all, but change “all” to “first”), then after the first when returns true and the row is inserted, no other when clauses will be evaluated.
Insert With Record
With both inserts and updates, you can pass a record instead of individual expressions for each column. Here’s a script demonstrating it:
drop table emp_copy
/
create table emp_copy as select * from employees where 1=2
/
begin
for rec in (
select *
from employees
) loop
-- Notice that you do NOT put parens around the record.
insert into emp_copy values rec;
end loop;
end;
/
Table Values Constructor (23c)
Yes, I know. 23c is not yet in production. And if I were an Oracle employee, I couldn’t even talk about this feature until after CloudWorld (which is taking place in one of those U.S. cities that would dry up and blow away if water wasn’t shipped to it from many miles away). As I am not an Oracle employee any longer, here you go:
Wait, what? You can’t read it? Oh fine:
insert into bookings
values (12133, 'Vienna', '2022-09-21'),
(62932, 'San Francisco', '2022-10-12'),
(98172, 'Berlin', '2022-12-15'),
Now For Some Intricacies: mutating table errors
Suppose I have a table to keep track of variations on itineraries to a certain location. It has an ordering column:
create table itinerary ( location varchar2(100), variation_no integer, description varchar2(1000) ) /
Every time I add a new variation for a location, I want to increment the variation number. So I write a trigger to take care of that for me:
create or replace trigger itinerary_bi before
insert on itinerary
for each row
begin
select nvl(max(variation_no),0) + 1
into :new.variation_no
from itinerary
where location = :new.location;
end;
Now, there’s gotta be at least one really smart reader who looks at that trigger and shakes her head. “Steven, Steven, Steven,” I can just hear her say. “You are trying to read from the table that the trigger is defined on. Haven’t you ever heard of mutating table trigger errors?”
To which I reply….
insert into itinerary ( location, description ) select location, details from my_diary / ORA-04091: table SFEUERSTEIN.ITINERARY is mutating, trigger/function may not see it
Whoops, so sorry, silly me. And so, of course, the following insert statement will also fail, right?
insert into itinerary ( location, description ) values ( 'Yellowstone', 'Lovely place' )
Wrong!
1 row inserted.
Tee hee. That’s right, Oracle didn’t think it should get all hot and bothered about that insert. Why not? Because I am inserting just a single row. If I am inserting a single row in the table and a BEFORE INSERT trigger fires, that row is not yet in the table, the table is not mutating, and there can be no mutating table error.
I can even execute more than one single row insert in the same PL/SQL block.
begin
insert into itinerary (
location,
description
) values (
'Chicago',
'Home of my son'
);
insert into itinerary (
location,
description
) values (
'South Bend',
'Home of my grandkids'
);
end;
/
PL/SQL procedure successfully completed.
So clearly, the rule we should follow is: never execute multi-row inserts (which includes both insert-select and forall-insert). Right?
No! The rule we should follow is:
Never query from or try to change the table on which you have defined the trigger.
Or, more generally and even better:
Keep DML statements out of your table triggers.
Instead, put all your DML into a procedure that performs all the necessary work. Then make sure that developers call that procedure and do not write their own insert statements all over the application. And you can make sure, you know. Just grant execute on the package and grant nothing on the tables (which should be in their own schema).
Another Intricacy: returning
The returning clause is a wonderful feature, helping you avoid unnecessary SQL execution. You can get back information about the just-executed SQL statement. Suppose, for example, I just inserted a row into a table that uses an identity column to generate the primary key. But then, I need that key for the next step in my program. Without returning, I could have to execute a query to get the primary key, and sometimes it’s not all that clear what the where clause should be – or at least you have to write a bunch of repetitive code to do the job. With returning, I just ask for it back:
create or replace procedure add_itinerary (
p_location in varchar2,
p_description in varchar2,
p_itinerary_id out number
) is
begin
insert into itinerary (
location,
description
) values (
'Yellowstone',
'Lovely place'
) returning itinerary_id into p_itinerary_id;
end;
Very nice. OK, now let’s take advantage of returning when I am doing a bulk insert:
drop trigger itinerary_bi
/
create or replace type itinerary_ot is object (
location varchar2(100),
description varchar2(1000))
/
create or replace type itineraries_nt is table of itinerary_ot
/
create or replace type itinerary_ids_nt is table of number
/
create or replace procedure add_itineraries (
p_itineraries in itineraries_nt,
p_itinerary_ids out itinerary_ids_nt
) is
begin
forall indx in p_itineraries.first..p_itineraries.last
insert into itinerary (
location,
description
) values (
p_itineraries(indx).location,
p_itineraries(indx).description
) returning itinerary_id
bulk collect into p_itinerary_ids;
end;
/
declare
l_itineraries itineraries_nt := itineraries_nt(
itinerary_ot(
'Lake',
'Watery'
),
itinerary_ot(
'Ocean',
'Salty'
)
);
l_itinerary_ids itinerary_ids_nt := itinerary_ids_nt();
begin
add_itineraries(
p_itineraries => l_itineraries,
p_itinerary_ids => l_itinerary_ids
);
dbms_output.put_line(l_itinerary_ids.count);
end;
/
Inserted = 2
Very nice. And so, of course we will also do the same with insert-select:
declare
l_itinerary_ids itinerary_ids_nt := itinerary_ids_nt();
begin
insert into itinerary (
location,
description
)
select 'Lake',
'Watery'
from dual
union all
select 'Ocean',
'Salty'
from dual
returning bulk collect into l_itinerary_ids;
end;
/
Um, sorry, no:
ORA-00933: SQL command not properly ended
For reasons I cannot explain, returning bulk collect into is not supported with insert-select. So if you really do need that information, you will have to switch to forall-insert.
Another Trigger Intricacy
Ah, the old “What is a statement?” intricacy.
An insert-select is a single insert statement, correct? Correct. Because of that, if you have defined a statement-level trigger on insert into a table, it will only fire once, no matter how many rows are inserted.
We’ve already seen a difference between insert-select and forall-select. Now we will see a similarity.
Let’s revisit that forall-insert from above:
create or replace procedure add_itineraries (
p_itineraries in itineraries_nt,
p_itinerary_ids out itinerary_ids_nt
) is
begin
forall indx in p_itineraries.first..p_itineraries.last
insert into itinerary (
location,
description
) values (
p_itineraries(indx).location,
p_itineraries(indx).description
) returning itinerary_id
bulk collect into p_itinerary_ids;
end;
Suppose there are 100 values in p_itineraries. Won’t that mean that 100 insert statements are executed and so the statement-level trigger will fire 100 times? Nope. Here’s the way to understand forall:
It executes the same statement 100 times, using the contents of the collection as its bind variables.
Just one statement and, so as with insert-select, statement-level triggers fire just once. Want to see it in action? Check out this LiveSQL script.
I hope all this is intricate enough for you.
It certainly was alliterative with insert!
Resources
Multitable inserts by Tim Hall
Mutating Table Errors and Multi-Row Inserts by yours truly, on DZone
