Home » SQL & PL/SQL » SQL & PL/SQL » Working of stored procedure (Oracle 11g R2 version(11.2.0.1), pl1sql)
Working of stored procedure [message #663053] |
Sat, 20 May 2017 01:48 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
I have two tables:
CREATE TABLE "T1"
(
"CID" NUMBER,
"CNAM" VARCHAR2(10 BYTE),
PRIMARY KEY ("CID")
)
CREATE TABLE "T2"
( "ORDID" NUMBER,
"CUID" NUMBER,
PRIMARY KEY ("ORDID")
CONSTRAINT "TCID_FK" FOREIGN KEY ("CUID")
REFERENCES "T1" ("CID")
)
The procedure to insert values into these tables (the wrong version):
create or replace procedure ins_t2(
p_cid t1.cid%type,
p_name t1.cnam%type,
p_ordid t2.ordid%type
)
is
l_cust_id t1.cid%type;
begin
select cid
into l_cust_id
from t1
where t1.cid=p_cid;
exception
when no_data_found then
insert into t1
values (p_cid, p_name)
returning cid into l_cust_id;
insert into t2 values (p_ordid,l_cust_id);
end;
The problem in the above procedure is that it does not insert repeated orders made by same customer.
Example:
INSERT INTO T1 (cid,cnam) VALUES(1,c1);
INSERT INTO T1 (cid,cnam) VALUES(2,c2);
INSERT INTO T1 (cid,cnam) VALUES(3,c3);
INSERT INTO T2 (ordid,cuid) VALUES(1,1);
INSERT INTO T2 (ordid,cuid) VALUES(2,2);
INSERT INTO T2 (ordid,cuid) VALUES(3,3);
INSERT INTO T2 (ordid,cuid) VALUES(4,1);
INSERT INTO T2 (ordid,cuid) VALUES(5,3);
The last two values do not get inserted. The (4,1) is repeated by c1 and (5,3) is repeated by c3. The result:
select * from t2;
(1,1)
(2,2)
(3,3)
However, it should display the (4,1) and (5,3) as well.
I solved the problem by making a small modification in the procedure towards the end:
create or replace procedure ins_t2(
p_cid t1.cid%type,
p_name t1.cnam%type,
p_ordid t2.ordid%type
)
is
l_cust_id t1.cid%type;
l_ordid t2.ordid%type;
begin
begin
select cid
into l_cust_id
from t1
where t1.cid=p_cid;
exception
when no_data_found then
insert into t1
values (p_cid, p_name)
returning cid into l_cust_id;
end;
begin
select
ordid
into
l_ordid
from t2 where ordid = p_ordid;
exception
when no_data_found then
insert into t2 values (p_ordid,l_cust_id);
end;
end;
I am not able to understand the thing that why I need the following change since the ord_id will always be new and never repeat anyway. I want to understand why the solution worked? It should have worked with my original procedure too.
select
ordid
into
l_ordid
from t2 where ordid = p_ordid;
|
|
|
Re: Working of stored procedure [message #663063 is a reply to message #663053] |
Sat, 20 May 2017 11:35 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
kaos.tissue wrote on Fri, 19 May 2017 23:48
The procedure to insert values into these tables (the wrong version):
create or replace procedure ins_t2(
p_cid t1.cid%type,
p_name t1.cnam%type,
p_ordid t2.ordid%type
)
is
l_cust_id t1.cid%type;
begin
select cid
into l_cust_id
from t1
where t1.cid=p_cid;
exception
when no_data_found then
insert into t1
values (p_cid, p_name)
returning cid into l_cust_id;
insert into t2 values (p_ordid,l_cust_id);
end;
The problem in the above procedure is that it does not insert repeated orders made by same customer.
When cid already exists, no EXCEPTION fires therefore no INSERT is ever executed
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:28:07 CDT 2024
|