Home » SQL & PL/SQL » SQL & PL/SQL » Working of stored procedure (Oracle 11g R2 version(11.2.0.1), pl1sql)
icon5.gif  Working of stored procedure [message #663053] Sat, 20 May 2017 01:48 Go to next message
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 Go to previous message
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
Previous Topic: Percentage of occurrences of a row
Next Topic: need help in rectifying error
Goto Forum:
  


Current Time: Fri Mar 29 10:01:47 CDT 2024