How would you force DRY (Do not Repeat Yourself) in an SQL script?

Question

I am changing the database (oracle) with a script containing several updates similar to:

UPDATE customer
SET status = REPLACE(status,   'X_Y',   'xy')
WHERE status LIKE '%X_Y%'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

UPDATE customer
SET status = REPLACE(status,   'X_Z',   'xz')
WHERE status LIKE '%X_Z%'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

-- More updates looking the same...

In this case, how would you force DRY (not to repeat yourself)?

I especially wanted to solve the following two recurring problems:

• Define a function available only from this script to extract the subquery SELECT id FROM category WHERE code = 'ABC'

• Create a set of replacement rules (which might look like {"X_Y": "yx", "X_Z": "xz", ...}in a popular programming language), and then repeat one update request on it.

Thanks!

Answer 1

I would reduce it to one query:

UPDATE customer
SET status = REPLACE(REPLACE(status, 'X_Y', 'xy'), 'X_Z', 'xz')
WHERE status REGEXP_LIKE 'X_[YZ]'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

Answer 2

, , - , , DRY. , , , , .

PL/SQL :

declare
   type str_tab is table of varchar2(30) index by binary_integer;
   from_tab str_tab;
   to_tab str_tab;
begin
   from_tab(1) := 'X_Y';
   from_tab(2) := 'X_Z';
   to_tab(1) := 'xy';
   to_tab(2) := 'xz';

   for i in 1..from_tab.count loop

      UPDATE customer
      SET status = REPLACE(status,   from_tab(i),   to_tab(i))
      WHERE status LIKE '%' || from_tab(i) || '%'
       AND category_id IN
        (SELECT id
         FROM category
         WHERE code = 'ABC');

   end loop;
end;

Answer 3

, -.

UPDATE customer
SET status = REPLACE(REPLACE(status,'X_Y','xy'),'X_Z','xz')
WHERE ( status LIKE '%X_Y%' Or status LIKE '%X_Z%')
  AND category_id IN
     (SELECT id
      FROM category
      WHERE code = 'ABC');

Answer 4

script, . ( , SQLPlus script.)

replace_in_status.sql:

UPDATE customer
SET status = REPLACE(status,   UPPER('&1'),   '&2')
WHERE status LIKE '%' ||UPPER('&1')|| '%'
 AND category_id IN
  (SELECT id
   FROM category
   WHERE code = 'ABC');

script:

@replace_in_status X_Y xy
@replace_in_status X_Z xz

Answer 5

, , : -)

:

DECLARE
  v_sql1   VARCHAR2(1000);
  v_sql2   VARCHAR2(2000);
  TYPE T_Rules IS RECORD (srch  VARCHAR2(100),  repl(VARCHAR2(100));
  TYPE T_RuleTab IS TABLE OF T_Rules INDEX BY BINARY_INTEGER;
  v_rules T_RuleTab;

  FUNCTION get_subquery RETURN VARCHAR2 IS
  BEGIN
    RETURN '(SELECT id FROM category WHERE code = ''ABC'')';
  END;

BEGIN
  v_sql1 := 'UPDATE customer SET status = REPLACE('':1'','':2'') WHERE status LIKE ''%:1%'' AND category_id IN ';
  v_rules(1).srch := ('X_Y'); v_rules(1).repl := 'yx';
  v_rules(2).srch := ('X_Z'); v_rules(2).repl := 'xz';

  FOR i IN 1..v_rules.COUNT LOOP
    v_sql2 := v_sql1||get_subquery();
    EXECUTE IMMEDIATE v_sql2 USING v_rules(i).srch, v_rules(i).repl;
  END LOOP;
END;

PL/SQL , .

, get_subquery, ; -)

Dang! , WHERE. - , Oracle...

Answer 6

, script, :

• ,
• script , .

, .

, .

Answer 7

:

• sql PL/SQL .
• , ​​ IBATIS, SQL, XML.
• ORM , , SQL.

, , , .

Answer 8

, soulmerge, , , - "". ,

status like '%tagada%'

. replace() , , . , , , , . - category_id... , , soulmerge : - . , category_id , , Oracle . , ?