"Refactoring is the process of changing a software system in such a way that it does not alter the external behavior of the code yet improves its internal structure." -- Martin Fowler in Refactoring: Improving The Design Of Existing Code
Here is an example how I build PL/SQL table function usually.
Before refactoring the code, we have to write the SQL in 2 places, it is a repetition.
create or replace PACKAGE demo_pkg
AS
CURSOR gc_data IS select 1 col1, 2 col2 from dual;
type data_tab is table of gc_data%RowType;
FUNCTION f1(p_a number)
RETURN data_tab PIPELINED;
end;
/
CREATE OR REPLACE PACKAGE BODY demo_pkg
AS
FUNCTION f1(p_a number)
RETURN data_tab PIPELINED
is
BEGIN
FOR lr in (select 1 col1, 2 col2 from dual where p_a > 5)
LOOP
pipe ROW(lr);
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
/* Don't forget to clean up here, too! */
/* Standard error logging mechanism */
--logger.log_error('Unhandled Exception', l_scope);
raise;
END;
end;
/
After refactoring the code, we only need to write the SQL(cursor) one time, removed unnecessary repetition.
create or replace PACKAGE demo_pkg
AS
CURSOR gc_data (p_a number) IS
select 1 col1, 2 col2, 3 col3 from dual where p_a > 5;
type data_tab is table of gc_data%RowType;
FUNCTION f1(p_a number)
RETURN data_tab PIPELINED;
end;
/
CREATE OR REPLACE PACKAGE BODY demo_pkg
AS
FUNCTION f1(p_a number)
RETURN data_tab PIPELINED
is
lr gc_data%rowtype;
BEGIN
OPEN gc_data(p_a);
LOOP
FETCH gc_data INTO lr;
pipe ROW(lr);
EXIT WHEN gc_data%NotFound;
END LOOP;
close gc_data;
RETURN;
EXCEPTION
WHEN OTHERS THEN
/* Don't forget to clean up here, too! */
/* Standard error logging mechanism */
--logger.log_error('Unhandled Exception', l_scope);
raise;
END;
end;
/
Observed:
This makes the output DRY, but for input, if we add/update/remove parameters, we have to update 2 places.
Reference:
1) http://www.refactoring.com/
2) call example: select col1, col2 from table(demo_pkg.f1(18));
