Stored Procedure - loop?

bebopayan · #1 (**permalink**) 11-15-11, 14:50

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

anacedent · #2 (**permalink**) 11-15-11, 15:27

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

bebopayan · #3 (**permalink**) 11-15-11, 16:04

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.

anacedent · #4 (**permalink**) 11-15-11, 16:07

notice my INSERT does NOT contain "VALUES" clause.

VALUES & SELECT are mutually exclusive.

Internet Services

Web Development

Digital Marketing

Consumer Tech