Procedure not functioning properly [message #662977] |
Thu, 18 May 2017 02:37 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
I have the following procedure which inserts data into three tables:
CUSTOMER(cust_id(PK), cust_name, cust_phone(UNIQUE))
ADDRESS(address_id(PK), address, area)
CUSTOMER_ADDRESS(cust_id (PK), address_id(PK))--------------------> This is a junction table between CUSTOMER and ADDRESS
CUSTOMER_ORDER(order_id (PK), cust_id(FK to CUSTOMER))
create or replace procedure INSERT_ADDRESS(
p_cust_phone customer.cust_phone%type,
p_address address.address%type,
p_area address.area%type,
p_ord_id customer_order.order_id%type
)
is
l_cust_id customer.cust_id%type;
l_address_id address.address_id%type;
begin
begin
select cust_id
into l_cust_id
from customer c
where c.cust_phone=p_cust_phone;
exception
when no_data_found then
insert into customer
values (cust_id_seq.nextval, 'No Name', p_cust_phone)
returning cust_id into l_cust_id;
end;
insert into address
values (address_id_seq.nextval, UPPER(p_address), UPPER(p_area))
returning address_id into l_address_id;
insert into customer_address
values (l_cust_id, l_address_id);
insert into customer_order (order_id, cust_id)
values (p_ord_id,l_cust_id);
end;
The procedure checks weather the cust_phone exists or not and if not then enters a new cust_id otherwise selects the cust_id associated with the given cust_phone. The problem is that this condition works properly for CUSTOMER but not for ADDRESS table. Multiple address_ids are generated with the same address.
Sample Data which is passed into the procedure along with other values(not mentioned here):
cust_phone------address
9873128600 K-1C
9811120000 J-20 3RD FLOOR
9873128600 K-1
9810320000 N-2B
9810390000 J-25 GF
9873500000 M-3 1ST FLOOR
8447910000 J-6 GF
9873128600 K-1C
The result I am getting in the ADDRESS table
ADDRESS_ID-----ADDRESS
1 K-1C
2 J-20 3RD FLOOR
3 K-1C
4 N-2B
5 J-25 GF
6 M-3 1ST FLOOR
7 J-6 GF
8 K-1C
Desired result in the ADDRESS table:
ADDRESS_ID------ADDRESS
1 K-1C[/b]
2 J-20 3RD FLOOR
3 N-2B
4 J-25 GF
5 M-3 1ST FLOOR
6 J-6 GF
The problem is that in the CUSTOMER table everything gets inserted as planned but in the ADDRESS table 'K-1C' is inserted 3 times with different address_id for each. How to check for the address?
[Updated on: Thu, 18 May 2017 03:05] Report message to a moderator
|
|
|
Re: Procedure not functioning properly [message #662978 is a reply to message #662977] |
Thu, 18 May 2017 03:29 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That procedure can only ever insert one row into each table every time it's called.
To get the same address multiple times you have to call it with the same address multiple times.
Presumably at some point, either in this procedure or before it's called, you need to check if that address already exists for that customer, in much the same way you're checking if the customer exists.
You should also get into the habit of explicitly listing the columns to be inserted into in the insert statement. You've done it for customer_order but not the others.
|
|
|
|
|
|
|
Re: Procedure not functioning properly [message #662986 is a reply to message #662984] |
Thu, 18 May 2017 06:13 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
I didn't get you. Mismatch as in? Could you give an example?
To insert a row in CUSTOMER:
======> Phone_no(from the parameter list) is checked, if it exists then the cust_id is taken corresponding to that phone otherwise new cust_id is inserted in CUSTOMER.
The cust_id is stored in a variable.
To insert a row in ADDRESS
======> Address(address from the parameter list) is checked, if exists then address_id is taken corresponding to that address otherwise new address_id is inserted in ADDRESS.
The address_id is stored in a variable.
Finally both the variable are inserted into the CUSTOMER_ADDRESS table. So, how will the mismatch occur?
I guess you are talking about the case, like upper case or lower case or mixed case? Well, that's a minor thing and can be dealt with.
[Updated on: Thu, 18 May 2017 06:16] Report message to a moderator
|
|
|
|
|
|
|
Re: Procedure not functioning properly [message #663000 is a reply to message #662993] |
Thu, 18 May 2017 07:09 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
actually i would setup the CUSTOMER_ADDRESS table as the following
CREATE TABLE CUSTOMER_ADDRESS
(
I_CUST_ID NUMBER NOT NULL,
I_ADDRESS_ID NUMBER NOT NULL,
CONSTRAINT CUSTOMER_ADDRESS_PK
PRIMARY KEY
(I_CUST_ID, I_ADDRESS_ID)
ENABLE VALIDATE
)
ORGANIZATION INDEX;
This would allow it to be self indexing and will not allow duplicate addresses. You also don't have to maintain both a table and an index
[Updated on: Thu, 18 May 2017 07:10] Report message to a moderator
|
|
|
|
|
|