I need to split the string in the procedure, then select from table a a value based on the values passed in from the string, and then I need to insert that value into table b. I want to do this loop for every value contained in the string ('01' first, then '03', '07', etc.).
- split the string to get individual values. (From now on, I'll refer to this task as "divide".)
- do whatever you need to do with each value. (I'll call this "conquer".)
Your ultimate goal is to conquer, and it sounds like you know how to handle that part; perhaps you already have a procedure that does it for a single value. Don't touch that procedure! For purposes of code maintenance it's best to have this logical unit contained in a separate module.
CREATE OR REPLACE PROCEDURE goal ( in_text IN table_a.id%TYPE ) -- *************** -- ** g o a l ** -- *************** -- goal inserts yadda yadda yadda ... -- based on table_a blah blah blah ... IS BEGIN INSERT INTO table_b (column_2) SELECT column_1 FROM table_a WHERE id = in_text; EXCEPTION WHEN NO_DATA_FOUND THEN ... END goal; /
Since dividing is such a generic, useful task, you'll almost certainly want to isolate it as a separate procedure. If you're using packages, you'll want the two procedures in separate packages, since you'll probably want to give lots of people the ability to divide, but only a few people will have the power to conquer.
This was first published in November 2003