Avoiding repeating code in PL/SQL DML

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 31 Jul 2006 05:13:38 -0700
Message-ID: <1154348018.219673.6020@75g2000cwc.googlegroups.com>

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 Received on Mon Jul 31 2006 - 07:13:38 CDT