Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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.orgReceived on Mon Jul 31 2006 - 10:13:48 CDT