Re: Avoiding repeating code in PL/SQL DML

Martin T. wrote:
> Hey all. (Oracle 9.2.0.1.0 on Windows XP)
>
> I have the following DML in my PL/SQL code:
> ----
> delete from machine_down_times
> where start_measure_id in (
> SELECT m.id
> from measures m
> where m.order_id = p_order_id
> and time_stamp >= v_delete_from_date
> and time_stamp < v_delete_to_date
> )
> and stop_measure_id in (
> SELECT m.id
> from measures m
> where m.order_id = p_order_id
> and time_stamp >= v_delete_from_date
> and time_stamp < v_delete_to_date
> );
> ----
>
> As you see, I use the same nested subquery twice in that delete
> statement.
> Since repeating code by c&p is rather evil in my opinion I would like
> to know how to avoid such constructs. (_Especially_ if I have that
> range SELECT from measures... appear a few more times in my PL/SQL
> package)
>
> * VIEW -- ... clutters schema namespace with something only used in my
> package
> * PL/SQL Collection -- ... performance? It would have to be public to
> be used as TABLE(v_collection) (?)
> * Inline VIEW -- (what if I have the same SELECT in multiple DML
> stmts?)
>
> thanks a bunch!
>
> best,
> Martin

In 10g I'd recommend using the WITH clause. In 9i an inline view.

But is this a real problem or just a matter of elegance?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org