CODEWITHSUNDEEP
plsqloracle10g
Mistre83
Mistre83

Reputation: 2827

PLSQL - Function as parameter workaround

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

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 ?

Upvotes: 0

Views: 58

Answers (1)

Martina
Martina

Reputation: 929

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;

Upvotes: 1

Related Questions