Tuesday, May 01, 2007

Another use for INSERT ALL syntax

Over on the Pythian blog Babette Turner-Underwood discusses Oracle's multitable insert syntax. She's been using in on a data conversion project. The advantage of INSERT ALL is that it allows us to insert several different VALUES clauses in a single statement. The documentation shows two uses for multitable inserts:

  • transforming a single row of data from one table into several rows of data in a another table;
  • putting data from a single row into one or more tables, depending upon the values of certain columns (a poor man's partitioining).

And, as Babette notes, in data migration we sometimes need to split columns from one old table into more than one new table.

None of these uses are what we might call everyday. So Babette is correct to describe the multitable insert as "little known". However, there is one use for the syntax, which is slightly arcane but is a much more common situation: creating referenced records for foreign keys on the fly. Usually we would expect to have our primary key records before we create child records because they exist as entities in their own right: Customers, Products, etc. But there are others which are only brought into existence by the creation of child records. For instance, the canonical implementation of an order process requires two tables - Order_Headers and Order_Lines. Order_Headers really just acts as an intersection table between Customers and Order_Lines: we only need an Order Header record when a customer places an order for a specific item.

One way of handling this is to have foreign keys which are DEFERRABLE INITIALLY DEFERRED. This means that the foreign key is not enforced until the end of the transaction. So we can insert a Order_Lines record without having a Order_Headers record. This is only a temporary suspension: eventually we will need to create that parent record. Besides, this violation of relational theory will disappoint Hugh Darwen. It would be nice to be able to create the parent record at the moment when we create the child record, so we can enforce the foreign key immediately. Which is where the multitable insert comes in.

Here is a simple orders implementation, with an IMMEDIATE foreign key:

SQL> desc order_headers
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(3)

SQL> desc order_lines
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER
LINE_ID NOT NULL NUMBER
PROD_ID NOT NULL NUMBER
QTY NOT NULL NUMBER
CREATED NOT NULL DATE
STATUS NOT NULL VARCHAR2(3)
SHIPPED DATE

SQL> insert into order_lines (order_id, line_id, prod_id, qty)
2 values (63, ordl_id.nextval, 5000, 2)
3 /
insert into order_lines (order_id, line_id, prod_id, qty)
*
ERROR at line 1:
ORA-02291: integrity constraint (A.ORDL_ORD_FK) violated - parent key not found


SQL>
.
What we want to be able to do is use the creation of the first order line to create the order header record too. The multitable syntax has several limitations. The two which are relevant here are: it must be driven off a subquery and we cannot use the RETURNING clause to retrieve the values of any sequence used in the insertion.

SQL> create or replace procedure new_order_line (
2 p_cust_id in customers.cust_id%type
3 , p_prod_id in products.prod_id%type
4 , p_qty in order_lines.qty%type
5 , p_ord_id in out order_headers.order_id%type
6 )
7 is
8 l_ord_id order_headers.order_id%type;
9 begin
10 if p_ord_id is null then
11 select ord_id.nextval into l_ord_id from dual;
12 else
13 l_ord_id := p_ord_id;
14 end if;
15
16 insert all
17 when p_ord_id is null then
18 into order_headers (order_id, cust_id)
19 values (new_ord_id, new_cust_id)
20 when 1=1 then
21 into order_lines (order_id, line_id, prod_id, qty)
22 values (new_ord_id, ordl_id.nextval, new_prod_id, new_qty)
23 select p_cust_id as new_cust_id
24 , p_prod_id as new_prod_id
25 , p_qty as new_qty
26 , l_ord_id as new_ord_id
27 from dual;
28
29 if p_ord_id is null then
30 p_ord_id := l_ord_id;
31 end if;
32 end new_order_line ;
33 /

Procedure created.

SQL>

I admit this is a slighty clunky procedure. My orginal implementation required only one IF statement, but had a single table INSERT statement for Order_Lines as well as the multitable statement. I'm sure if I had more time I could polish this code more. Anyway, let's call this procedure twice:

SQL> declare
2 n number;
3 begin
4 new_order_line(1000, 5000, 2, n);
5 new_order_line(1000, 5002, 1, n);
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from order_headers
2 /
ORDER_ID CUST_ID STA
---------- ---------- ---
80002 1000 NEW

SQL> select * from order_lines
2 /
ORDER_ID LINE_ID PROD_ID QTY CREATED STA SHIPPED
---------- ---------- ---------- ---------- --------- --- ---------
80002 250510 5000 2 30-APR-07 NEW
80002 250511 5002 1 30-APR-07 NEW

SQL>

Of course I could have impelmented this as two separate single table inserts, which would have avoided the some of duplication I mentioned. However there is another advantage to the multitable version. It is a single statement, so if any part of it fails, the whole thing fails:

SQL> declare
2 n number;
3 begin
4 new_order_line(1000, 5001, null, n);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("A"."ORDER_LINES"."QTY")
ORA-06512: at "A.NEW_ORDER_LINE", line 13
ORA-06512: at line 4


SQL>
SQL> select * from order_headers
2 /

no rows selected

SQL>

This is neater because the logic of the business model is that we do not want an order header if there are no order lines for it.

We still don't have a constraint syntax which allows us to enforce parentage. And there is still no sign of a constraint which will allow us to enforce arcs. But there is always hope.

10 Comments:

Blogger Jornica said...

You are demonstrating so called Procedure-Level Atomicity as well:
It is a single statement, so if any part of it fails, the whole thing fails.
The procedure call is the single statement you're referring to. If any statement in the procedure fails, the transaction will be rolled back to the situation before the execution of the procedure. if you replace the "insert all" by two "insert" statements, the behaviour will be the same.

If you execute the "insert" statements in SQL you will find one record in the order header in contrast with the "insert all" statement. In that case there are no records inserted.

1 May 2007 13:26:00 GMT-7  
Blogger Laurent Schneider said...

well, there is an insert all, but no delete all, no merge all, no update all... so this is worth only for inserts, and you will probably have to end by writting something special for delete, will not you?

2 May 2007 00:13:00 GMT-7  
Blogger APC said...

>> you will probably have to end by
>> writting something special for
>> delete, will not you?

Fair point.

In the end this use of INSERT ALL is another SQL curiosity rather than a practical data modelling enhancement. That would really require a constraint which enforces the relationship at both ends.

Cheers, APC

2 May 2007 04:15:00 GMT-7  
Blogger Don said...

Just an FYI that you have a typo in "releavnt".

2 May 2007 09:01:00 GMT-7  
Blogger APC said...

Hi Don

You know, I've never seen anybody ever pick up a blog author over a typo before. I shall take it as a compliment to the overall quality of my output. Please don't disabuse me if that isn't what yoiu intended ;)

Cheers, APC

2 May 2007 09:38:00 GMT-7  
Anonymous zantac said...

companies marketing mineral makeups and also get the best bargains in mineral makeup you can imagine,
find aout how to consolidate your students loans or just how to lower your actual rates.,
looking for breast enlargements? in Rochester,
homeopathy for eczema learn about it.,
Allergies, information about lipitor,
save big with great bargains in mineral makeup,

change edition interviewing motivational people preparing second
,

interviewing motivational people preparing second time
,

interviewing people motivational preparing for a second time
,

black mold exposure
,

black mold exposure symptoms
,

black mold symptoms of exposure
,

free job interview questions
,

free job interview answers
,

interview answers to get a job
,

lookfor hair styles for fine thin hair
,

search hair styles for fine thin hair
,

hair styles for fine thin hair
,

beach resort in the philippines
,

great beach resort in the philippines
,

luxury beach resort in the philippines
,
iron garden gates, here,
iron garden gates,
wrought iron garden gates
, here
,
wrought iron garden gates
,
You: The Owner's Manual: An Insider's Guide to the Body That Will Make You Healthier and Younger
,
eat eating mindless more than think we we why
,


texturizer,
texturizers here,
black hair texturizer,
find aout how care curly hair,
find about how to care curly hair,
care curly hair,
lipitor rash,
lipitor reactions,
new house ventura california,
the house new houston tx,
new house washington dc,
new house pa philadelphia,
san antonio tx house new,
house new pa philadelphia,
new house washington dc,
new house ventura california,
the house new houston tx,
house new san antonio tx,
the house new houston tx, that you are looking for,
new house ventura california, you need to buy,
new house washington dc,
house new pa philadelphia,
new house san antonio tx,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

chocolate esophagus heartburn study
,

chocolate esophagus heartburn study
be informed,

digestion healing healthy heartburn natural preventing way
,

digestion healing healthy heartburn natural preventing way
,
sew skirts, 16simple styles you can make!,
sew what skirts 16 simple styles you,
rebates and discounts on sunsetter awnings,
sunsetter awnings discounts and rebates,
discount on sunsetter awnings


truck and bus tires 12r 22.5, get the best price,
tires truck and bus 12r 22.5 best price,
tires truck bus tires12r 22.5 best price,
plush car seat strap covers,
car seat strap covers,plush,
car seat strap, plush covers,
oscoda voip phone systems, the best!,
oscoda voip the phone system,
oscoda voip phone systems,
exterior iron gates,
oriental wrought iron gates,
powder coated iron garden fencing,

9 February 2008 10:29:00 GMT-8  
Anonymous iron gates said...

black mold exposure,
black mold symptoms of exposure,

wrought iron garden gates,
your next iron garden gates, here,

hair styles for fine thin hair,
search hair styles for fine thin hair,

night vision binoculars,
buy, night vision binoculars,

lipitor reactions,
lipitor reactions,

luxury beach resort in the philippines,
beach resort in the philippines,

homeopathy for baby eczema.,
homeopathy for baby eczema.,

save big with great mineral makeup bargains,
companies marketing mineral makeups,

prodam iphone praha,
Apple prodam iphone praha,

iphone clone cect manual,
manual for iphone clone cect,

fero 52 binoculars night vision,
fero 52 night vision,

best night vision binoculars,
buy, best night vision binoculars,

computer programs to make photo albums,
computer programs, make photo albums,

26 March 2008 14:58:00 GMT-7  
Anonymous Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情人歡愉用品,情趣用品,AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰,自慰套,性感吊帶襪,情趣用品加盟,情人節禮物,情人節,吊帶襪,成人網站,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,A片,A片下載,免費A片,免費A片下載,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,正妹,成人網站,A片,免費A片,A片下載,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,色情影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,嘟嘟成人網

愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片

11 October 2008 10:08:00 GMT-7  
Blogger sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,A片,視訊聊天室,聊天室,視訊,視訊聊天室,080苗栗人聊天室,上班族聊天室,成人聊天室,中部人聊天室,一夜情聊天室,情色聊天室,視訊交友網

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,avdvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX,成人論壇

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

24 December 2008 01:27:00 GMT-8  
Blogger Jerry said...

cheap wedding gowns
discount bridal gowns
China wedding dresses
discount designer wedding dresses
China wedding online store
plus size wedding dresses
cheap informal wedding dresses
junior bridesmaid dresses
cheap bridesmaid dresses
maternity bridesmaid dresses
discount flower girl gowns
cheap prom dresses
party dresses
evening dresses
mother of the bride dresses
special occasion dresses
cheap quinceanera dresses
hot red wedding dresses

18 June 2009 09:20:00 GMT-7  

Post a Comment

<< Home