I have been doing some data transformation/processing in PL/SQL and I want to eliminate duplicate code from my package. This are the relevant code parts.
Input type for the table function:
type t_legs_cur is ref cursor return legs%rowtype;
Procedure that process one record:
procedure discontinuity_rule(p_leg in out nocopy legs%rowtype) as
begin
null; --business logic here
end discontinuity_rule;
Table function that iterates over a cursor, process each row in cursors and pipes the output (if any):
function apply_discontinuity_rule(p_cur t_legs_cur)
return t_legs pipelined
order p_cur by (/* some fields */)
parallel_enable (partition p_cur by range (/* some fields */))
as
v_leg legs%rowtype;
begin
loop
fetch p_cur into v_leg;
exit when p_cur%notfound;
discontinuity_rule(v_leg); --call back
if v_leg.id is not null then
pipe row (v_leg);
end if;
end loop;
end apply_discontinuity_rule;
There are several steps of transformation/processing, e.g. I would run the following select to do the some processing and apply some rules in a given order:
select * from table(trip_rules.generate_trips_from_legs(cursor(
select * from table(trip_rules.apply_5_legs_rule(cursor(
select * from table (trip_rules.apply_previous_city_rule(cursor(
select * from table (trip_rules.apply_backhaul_rule(cursor(
select * from table(trip_rules.apply_connection_time_rule(cursor(
select * from table(trip_rules.apply_discontinuity_rule(cursor(
select * from table(trip_rules.generate_legs_from_input(cursor(
select * from INPUT_DATA
)))
)))
)))
)))
)))
)))
)));
This is all fine and dandy, only problem is, my trip_rule package contains many apply_*_rule functions. They all similar to the example apply_discontinuity_rule. The only difference is the actual procedure (discontinuity_rule) they call back.
SO, my question would be, how can I avoid to copy the code of the apply_* functions. Is there a more elegant way to do this then use a big if: if p_rule_name == 'discontinuity_rule' then
function apply_rule(p_cur t_legs_cur, p_rule_name in varchar2)
return t_legs pipelined
order p_cur by (/* some fields */)
parallel_enable (partition p_cur by range (/* some fields */))
as
v_leg legs%rowtype;
begin
loop
fetch p_cur into v_leg;
exit when p_cur%notfound;
if p_rule_name == 'discontinuity_rule' then
discontinuity_rule(v_leg);
elsif p_rule_name == 'other_rule' then
other_rule(v_leg);
elsif p_rule_name == 'totally_other_rule' then
totally_other_rule(v_leg);
-- and so on...
end if;
if v_leg.id is not null then
pipe row (v_leg);
end if;
end loop;
end apply_rule;
I also understand that it would be possible to create an anonymous PL/SQL block on the fly using the procedure name and execute it as dynamic SQL. I wonder if it can be done properly, without killing my performance. Any idea is appreciated.
