MODEL函数的简单用法。

有人问：

原表达式：
SELECT REPLACE(REPLACE(REPLACE('来访用户的名是:<U1>,<U2>,<U3>...','<U1>','张三'),'<U2>','李四'),'<U3>','王五') FROM DUAL;
由于U1,U2,U3...通常是变化,嵌套太多REPLACE,想用regexp_replace代替,但折腾半天搞不定,请高手帮忙。

 

有人答：

下面的例子示范了逐行循环替换：
CREATE TABLE my_users (userid VARCHAR2(10),uname VARCHAR2(20));
INSERT INTO my_users VALUES ('U1','John');
INSERT INTO my_users VALUES ('U2','Tom');
INSERT INTO my_users VALUES ('U3','Peter');
INSERT INTO my_users VALUES ('U4','Lisa');
INSERT INTO my_users VALUES ('U5','Charlie');
INSERT INTO my_users VALUES ('U6','Mary');
var v_str varchar2(80);
exec :v_str:='Here is the list of users: <U1>,<U2>,<U3>,<U4>' ;
SELECT str
FROM (SELECT *
        FROM my_users u
       WHERE INSTR(:v_str,'<'||userid||'>')>0
      MODEL RETURN UPDATED ROWS
        DIMENSION BY (rownum rn)
         MEASURES (:v_str str,userid,uname)
         RULES (
         str[any] order by rn=REPLACE(str[GREATEST(1,CV()-1)],'<'||userid[CV()]||'>','<'||uname[CV()]||'>')
         )
      ORDER BY rn DESC
      )
WHERE ROWNUM=1;
STR
----------------------------------------------------------
Here is the list of users: <John>,<Tom>,<Peter>,<Lisa>

 

Or any other options like:
SQL> WITH t0 AS (SELECT '<U1>,<U2>,<U3>,<U4>' str FROM dual)
  2  , t1 as ( select * from (
  3  SELECT ltrim(SYS_CONNECT_BY_PATH(uname,'>,<'),'>,') ||'>' strs, level l
  4    FROM my_users , t0 where instr(str, userid) > 0 CONNECT BY userid >PRIOR userid) )
  5  select 'Here is the list of users:'||strs strs from t1 where l = ( select max(l) from t1);
STRS
--------------------------------------------------------------------------------
Here is the list of users:<John>,<Tom>,<Peter>,<Lisa>