Home » SQL & PL/SQL » SQL & PL/SQL » Inserting data in multiple tables (Oracle 11g R2, pl/sql)
Inserting data in multiple tables [message #662436] Tue, 02 May 2017 06:57 Go to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I have 4 Tables

Customer(cust_id (PK), cust_phone, name)

Order((order_id,cust_id) (PK), order_date, order_amt)

Order_line((order_id,Product_id)(PK), qty)

Product(product_id, Product_name, product_price)

The order table has a composite key saying an order only exists with a customer.

The Order_line table has a composite key as it is a bridge table between product and order table. the order_amt is a calculated column, its the total order value.

The question is how do i insert data into the order table and the Order_line table? using procedure, triggers or a temp table or any other way? I am really stuck at this. Help!!

Do I need to make any changes in the model?
Re: Inserting data in multiple tables [message #662437 is a reply to message #662436] Tue, 02 May 2017 07:03 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

More often than not it is a Bad Thing to store computed value in table column; since it can be calculated any time it is required.

Re: Inserting data in multiple tables [message #662438 is a reply to message #662436] Tue, 02 May 2017 07:54 Go to previous messageGo to next message
joy_division
Messages: 4902
Registered: February 2005
Location: East Coast USA
Senior Member
I don't understand the issue. What is the problem with the INSERT? Insert into order. Insert into order_line.

[Updated on: Tue, 02 May 2017 07:54]

Report message to a moderator

Re: Inserting data in multiple tables [message #662439 is a reply to message #662436] Tue, 02 May 2017 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Quote:
The question is how do i insert data into the order table and the Order_line table?
This is not clear and needs a more detailed explanation along with an example.

Quote:
using procedure
This is possible.

Quote:
[or] triggers
This should be avoided.

Quote:
or a temp table
The question is then how do the data come into the temp table?

Most often, the applications do this in this way:
* start a transaction
* insert a new order with 0 as amount
* insert all order lines
* update the order with the total amount
* commit

Note that if the PK of ORDER_LINE is (ORDER_ID, PRODUCT_ID) then the PK of ORDER is ORDER_ID alone.
If it is mandatory to be (ORDER_ID, CUST_ID) (due to some specific reasons at your place) then CUST_ID should also be in ORDER_LINE and its PK.

Re: Inserting data in multiple tables [message #662441 is a reply to message #662439] Tue, 02 May 2017 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Having a pk of order_id and cust_id on order means you can have 2 orders for 2 different customers with the same order_id. That isn't generally a good idea.
All you need to make sure that an order belongs to a customer is to have the cust_id column on order, make it not null and add a foreign key that points to customer. No need to have it part of the key on order.
Re: Inserting data in multiple tables [message #662443 is a reply to message #662441] Tue, 02 May 2017 09:28 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I'm a little new at this. And yes I'll change the pk. Cust_id should not be a part of pk for the order table.

But What I really want to know is how do I insert data into the order and order_line tables. Mainly the value of "order_amt" which is the total value to be paid.
How to calculate the value since price and qty come from different tables.
Re: Inserting data in multiple tables [message #662444 is a reply to message #662443] Tue, 02 May 2017 09:34 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd follow the process Michel outlined above. That's a pretty basic process, what exactly are you struggling with?
Re: Inserting data in multiple tables [message #662445 is a reply to message #662443] Tue, 02 May 2017 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
kaos.tissue wrote on Tue, 02 May 2017 07:28
I'm a little new at this. And yes I'll change the pk. Cust_id should not be a part of pk for the order table.

But What I really want to know is how do I insert data into the order and order_line tables. Mainly the value of "order_amt" which is the total value to be paid.
How to calculate the value since price and qty come from different tables.
Why do you feel compelled to store ORDER_AMT in any table?
You could use a virtual column of LINE_AMOUNT to show the total for each line item.
So when you want ORDER_AMT you just SUM(LINE_AMOUNT)
Re: Inserting data in multiple tables [message #662446 is a reply to message #662444] Tue, 02 May 2017 09:56 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
What I want to achieve is to add some attributes in an excel file like qty, cust_phone etc.
Then use sql loader to push it into a temporary table
Then call procedures to add data into tables.
Now the problem is how to enter values into calculated column like "order_amt"?
The price comes from the product table, the qty comes from order_line table and the order_amt is in another table.
Re: Inserting data in multiple tables [message #662447 is a reply to message #662446] Tue, 02 May 2017 10:04 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Exactly @blackswan but the definition of "line_amount" should be in which table? And what the definition would be?
In order to calculate line_amount I need qty from one table and price from
Another.
Re: Inserting data in multiple tables [message #662448 is a reply to message #662446] Tue, 02 May 2017 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The price comes from the product table, the qty comes from order_line table and the order_amt is in another table.
You join the tables and use SUM to sum (product_price*qty).

Re: Inserting data in multiple tables [message #662449 is a reply to message #662447] Tue, 02 May 2017 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
kaos.tissue wrote on Tue, 02 May 2017 08:04
Exactly @blackswan but the definition of "line_amount" should be in which table? And what the definition would be?
In order to calculate line_amount I need qty from one table and price from
Another.
IMO, PRICE should be present ORDER_LINE table, because in the real world PRICE changes exist & line item represent cost for that point in time.
Price changes is another reason why it is Bad Thing to store computed value in table column
Re: Inserting data in multiple tables [message #662450 is a reply to message #662449] Tue, 02 May 2017 10:20 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Ok I got your point @blackswann. If I have a virtual column "line_amt"
Then what is "order_amt"? That's also a virtual column in the order table right so how can I compute sum(line_amt)
Can I make computations on virtual columns?
And if not a computed value then how to go about the problem?

[Updated on: Tue, 02 May 2017 10:23]

Report message to a moderator

Re: Inserting data in multiple tables [message #662451 is a reply to message #662450] Tue, 02 May 2017 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
http://lmgtfy.com/?q=oracle+virtual+column
Re: Inserting data in multiple tables [message #662452 is a reply to message #662451] Tue, 02 May 2017 10:29 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
The expression used in the virtual column definition has the following restrictions:
It cannot refer to another virtual column by name.
So the "order_amt" column is not a virtual one?

How to do it without the virtual columns? Since you said it's a bad idea
Re: Inserting data in multiple tables [message #662453 is a reply to message #662450] Tue, 02 May 2017 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I agree with the fact that the product price (PRODUCT_PRiCE) should be in ORDER_LINE as the price can changed between the order and the next time you will check the order.
Amount could be in ORDER_LINE or not, I don't see the value to have it, but if it is there then it must be a virtual column (as product of PRODUCT_PRICE and QTY, from ORDER_LINE table of course).
I disagree that the computed column ORDER_AMT should not be in the ORDER table. This allows to see the order summary without having to query ORDER_LINE.
And this is especially true if an order can't be modified afterwards. If it can then you must have process to prevent from inconsistencies.
Note: ORDER_AMT in ORDER table can't be a virtual column.

[Updated on: Tue, 02 May 2017 10:35]

Report message to a moderator

Re: Inserting data in multiple tables [message #662454 is a reply to message #662452] Tue, 02 May 2017 10:35 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
You have price as an actual column on order_line - that stores the price of the product at the time the order_line was created.
You then have a virtual column on order_line for line_amount - which is set to price * quantity
You don't store order amount, nor do you have it as a virtual column (because you can't), but whenever the application needs to display order amount you just select sum(line_amount).
Re: Inserting data in multiple tables [message #662455 is a reply to message #662452] Tue, 02 May 2017 10:39 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
kaos.tissue wrote on Tue, 02 May 2017 08:29
The expression used in the virtual column definition has the following restrictions:
It cannot refer to another virtual column by name.
So the "order_amt" column is not a virtual one?

How to do it without the virtual columns? Since you said it's a bad idea
Please review my previous responses regarding ORDER_AMT.
At no time did I say that ORDER_AMT should exist in any table.
Re: Inserting data in multiple tables [message #662457 is a reply to message #662455] Tue, 02 May 2017 10:42 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Ok I got it. Thanks a lot!!!
One last thing
The other ways of doing it without virtual columns
Re: Inserting data in multiple tables [message #662458 is a reply to message #662457] Tue, 02 May 2017 10:47 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
kaos.tissue wrote on Tue, 02 May 2017 08:42
Ok I got it. Thanks a lot!!!
One last thing
The other ways of doing it without virtual columns
It could be done using a VIEW.
It could be done using actual (non-virtual) column.
Re: Inserting data in multiple tables [message #662459 is a reply to message #662458] Tue, 02 May 2017 10:59 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Ok yes I can now imagine how. Thanks a lot for your answer. I'm learning so I'll be posting a lot of questions. Thanks again.cheers!
Re: Inserting data in multiple tables [message #662488 is a reply to message #662459] Wed, 03 May 2017 11:44 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
Its fairly easy you define your your foreign key like the following

ALTER TABLE EMP
ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
REFERENCES DEPT(DEPTNO) INITIALLY DEFERRED ;

The clause INITIALLY DEFERRED tells oracle to not check the constraint until a commit is issued. This will allow you do put your data in any order and then database integrity is checked at the commit;
Re: Inserting data in multiple tables [message #662497 is a reply to message #662488] Thu, 04 May 2017 02:43 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Yeah you are right but the in my problem the phone no. will come first for every record.
Re: Inserting data in multiple tables [message #662499 is a reply to message #662497] Thu, 04 May 2017 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And then? How is this a problem in what Bill posted?
He just said you can insert it after, he did not say you must insert it after, you can still insert it before if you want.

If your requirement is that you must insert it before then do not declare the constraint as DEFERRED.

[Updated on: Thu, 04 May 2017 03:05]

Report message to a moderator

Re: Inserting data in multiple tables [message #662500 is a reply to message #662499] Thu, 04 May 2017 03:04 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
yes @michel I know what he said. In my problem the phone no. will anyways come first so why not make it that way.
Re: Inserting data in multiple tables [message #662501 is a reply to message #662500] Thu, 04 May 2017 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I added the following last sentence in my post when you were posting:

Quote:
If your requirement is that you must insert it before then do not declare the constraint as DEFERRED.
It is just a possibility Oracle allows you, you have not to do so if you don't want.

Re: Inserting data in multiple tables [message #662502 is a reply to message #662501] Thu, 04 May 2017 03:07 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Yeah exactly. Thanks Very Happy
Re: Inserting data in multiple tables [message #662509 is a reply to message #662502] Thu, 04 May 2017 08:34 Go to previous message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
I didn't realize that this was a homework assignment. If it was a production problem you would not care what order the information was inserted.
Previous Topic: Need to get the top 3 employees whose salary increased in three consecutive year
Next Topic: ORA-14097
Goto Forum:
  


Current Time: Sat Oct 20 22:33:43 CDT 2018