Re: Code Re-Use Via Views

windcliff (windcliff_at_gmail.com) wrote:
: One of the ideas that's been talked about in my group is using text
: pre-processing to simulate this, e.g., if two queries share the same
: select list and group by clauses but differe in having clauses, we
: should be able to use comething like the C preprocessor to minimize the
: textual repetition at bare minimum.

: I've thought about using pipelined functions or returning cursors from
: functions, but my gut tells me joining to their output will be slow. I
: guess I'll try benchmarking.

If you use sql*plus as the tool that runs your DDL, then you can use define and @ to include any amount of useful shared snippets of sql.

e.g. various files

• financial_tables_for_join.sql table_1 t1, table2 t2
• person_tables_for_join.sql table_a t1, table_b tb
• financial_join_condition.sql t1.id = t2.id
• person_join_condition.sql ta.id = tb.id
• financial_rpt.sql create view financial_rpt as select * from @financial_tables_for_join.sql where @financial_join_condition.sql ;
• person_finance_rpt.sql create view person_finance_rpt as select * from @financial_tables_for_join.sql , -- comma @person_tables_for_join.sql where @financial_join_condition.sql and @person_join_condition.sql ;

etc etc Received on Mon Jan 09 2006 - 23:03:09 CST