Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Question Unanswered: Stored Procedure - loop?

    Is there a way to make a loop, something like a for or a while? Probably it's not even the way to solve the problem I have. So i'll write down the issue.

    The problem I have is:
    I have two tables, table1 have 2 primary keys, ID1 and ID2.
    table2 uses ID1 and ID2 (foreign key) and an ID3.
    (I'm ignoring the rest of the data, as I think it's unrelated to the issue.)

    What I want to do, is to develop a SP to insert on table2. But I'm only receiving ID1 and ID3. What I want to is to insert the register for each different ID2 it exists on table1 with the same ID1 i'm getting.

    That said, I'll write a little example to try to make it clearer.

    Table1 has:
    Code:
    ---TABLE1---
    ------------
    ID1     ID2
    1        10
    1        20
    And I want to call my_stored_procedure with ID1 and ID3 as parameters once.
    Code:
    call my_stored_procedure(ID1, ID3)
    Let's say ID1 = 1 and ID3=101

    As result of the call, table2 should be:
    Code:
    --------TABLE2--------
    ----------------------
    ID1     ID2      ID3
    1       10       101
    1       20       101
    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> @id3
    SQL> drop table table1;
    
    Table dropped.
    
    SQL> create table table1(id1 number, id2 number);
    
    Table created.
    
    SQL> insert into table1 values(1,10);
    
    1 row created.
    
    SQL> insert into table1 values(1,20);
    
    1 row created.
    
    SQL> drop table table2;
    
    Table dropped.
    
    SQL> create table table2 (id1 number, id2 number, id3 number);
    
    Table created.
    
    SQL> create or replace procedure my_stored_procedure(val1 number, val2 number)
      2  as
      3  begin
      4  insert into table2 t2 select t1.id1, t1.id2, val2 from table1 t1
      5  where t1.id1 = val1;
      6  end;
      7  /
    
    Procedure created.
    
    SQL> exec my_stored_procedure(1,101);
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from table2;
    
           ID1	  ID2	     ID3
    ---------- ---------- ----------
    	 1	   10	     101
    	 1	   20	     101
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    thank you anacedent, your help is well appreciated.

    Seems like it works fine.

    Although it works well, I just have a little doubt, can the INSERT in the SP be changed to the way where you specify the columns and values?

    I tried
    Code:
      INSERT INTO TABLE2
      (
      ID1,
      ID2,
      ID3
      )
      VALUES (
      SELECT TABLE1.ID1, TABLE1.ID2, VAL2 FROM TABLE1 WHERE TABLE1.ID1=VAL1
      );
    Also tried:
    Code:
      INSERT INTO TABLE2
      (
      ID1,
      ID2,
      ID3
      )
      VALUES (
      VAL1,
      SELECT TABLE1.ID2 FROM TABLE1 WHERE TABLE1.ID1=VAL1,
      VAL2
      );
    But it isn't allowed.

    The reason, well for scalability reasons mainly. I guess I could use an extra table and use it or simply add the rest of the params in the SELECT clause. But I'd rather to be able to specify which column is getting which value.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    notice my INSERT does NOT contain "VALUES" clause.

    VALUES & SELECT are mutually exclusive.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •