PLSQL - Function as parameter workaround

Question

I have a table that we use for import some data. This table have field like this:

field_1
field_2
field_3

After the import is done, I call a postprocess procedure where I have to do some logic in this field (and the logic could be different for each one of this) and store in online table.

This is a possible pseudo-code:

DECLARE
    rOnlineTable online_table%ROWTYPE;
FOR cur in (SELECT *
              FROM import_table
             WHERE state IS NULL)
LOOP

  rOnlineTable.online_field_1 := handleField(/* here i would like to call a function that have the logic to handle cur.field_1*/);
  rOnlineTable.online_field_2 := handleField(/* here i would like to call a function that have the logic to handle cur.field_2*/);
  rOnlineTable.online_field_3 := handleField(/* here i would like to call a function that have the logic to handle cur.field_3*/);

  INSERT INTO online_table VALUES rOnlineTable;

END LOOP;

In this way (I think) I could change only the implementation of the handler methods if in future something will change. Or I can swap some of that handler-function for other fields (for example: tomorrow field_1 need the same logic as field_3).

There are some workaround to do this, or maybe another better solution ?

I'm not sure that I see a question here. Or at least I don't understand the question you're asking. Why can't you create a function that takes a parameter of type cur.field_1 and returns a value of type rOnlineTable.online_field_1? — Justin Cave
– Justin Cave, Commented Mar 13, 2015 at 14:15
Are these handler functions going to be used elsewhere, or is the code involved long and complex? If so, then by all means use a function. If not, however, all you're doing is reducing clarity and adding complexity. ??? — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Mar 15, 2015 at 17:18

Martina · Accepted Answer · 2015-03-13 21:33:26Z

1

What about simple insert statement?

insert into online_table select func1(field1), func2(field2), func3(field3)   from import_table

where func1..3 would look like

  create or replace function func1(a in import_table.field1%type) return   online_table.field1%type as
begin
  null; //some logic
end;

answered Mar 13, 2015 at 21:33

Martina

9297 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PLSQL - Function as parameter workaround

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related