我有两个表.一个是answer_step_dtl另一个是tag_mst,如下所示
Ans_code Ans_Desc ------------------------------------------- 50000000000000005770 Enter <B><APN></B> and press Ok. 40000000000000000164 Enter <B><ACCOUNTNAME></B> in connection name. 40000000000000000165 Enter <B><ACCOUNTNAME></B> in <APN>.
等等.
我的tag_mst有这样的值
TAG_CODE TAG_NAME TAG_VALUE ------------------------------------------------------- 100 <APN> EXAMPLE.COM 101 <ACCOUNTNAME> EXAMPLE
现在我的要求是这个我想选择答案表答案表,替换标签桅杆的值.一个ans_desc中可以有多个标记.
我在想这个方法
>循环answer_dtl的结果从单个记录中获取标记
>循环记录中的多个标记,然后替换
标签
>存储在临时表中,然后打开新的tem表的光标.
解决方法
编辑：奖金查询使用(递归)CTE,inspired by a note从
@Rob van Wijk开始,需要11g(R2？)：
sql> WITH data AS ( 2 SELECT ans_code,Ans_Desc,tag_name,tag_value,3 row_number() OVER (partition BY ans_code ORDER BY t.rowid) no,4 row_number() OVER 5 (partition BY ans_code ORDER BY t.rowid DESC) is_last 6 FROM answer_step_dtl a 7 JOIN tag_mst t ON a.ans_desc LIKE '%' || t.tag_name || '%' 8 ),n(ans_code,no,is_last,replaced) AS ( 9 SELECT ans_code,no n,10 replace (ans_desc,tag_value) replaced 11 FROM data 12 WHERE no = 1 13 UNION ALL 14 SELECT d.ans_code,d.no,d.is_last,15 replace (n.replaced,d.tag_name,d.tag_value) replaced 16 FROM data d 17 JOIN n ON d.ans_code = n.ans_code 18 AND d.no = n.no + 1 19 ) 20 SELECT * 21 FROM n 22 WHERE is_last=1; ANS_CODE NO IS_LAST REPLACED -------------------- -- ------- --------------------------------------- 40000000000000000164 1 1 Enter <B>EXAMPLE</B> in connection 50000000000000005770 1 1 Enter <B>EXAMPLE.COM</B> and press Ok. 40000000000000000165 2 1 Enter <B>EXAMPLE</B> in EXAMPLE.COM.
初步答案：
您可以使用PL / sql函数.即使有多个要替换的标签,以下内容也能正常工作：
CREATE OR REPLACE FUNCTION replacetags(p_desc VARCHAR2)
RETURN VARCHAR2 IS
l_result LONG := p_desc;
l_tag_pos INTEGER := 1;
l_tag tag_mst.tag_name%TYPE;
BEGIN
LOOP
l_tag := regexp_substr(l_result,'<[^<]+>',l_tag_pos);
l_tag_pos := regexp_instr(l_result,l_tag_pos) + 1;
EXIT WHEN l_tag IS NULL;
BEGIN
SELECT replace(l_result,l_tag,tag_value)
INTO l_result
FROM tag_mst
WHERE tag_name = l_tag;
EXCEPTION
WHEN no_data_found THEN
NULL; -- tag doesn't exist in tag_mst
END;
END LOOP;
RETURN l_result;
END;
sql> SELECT ans_code,replacetags(ans_desc)
2 FROM answer_step_dtl;
ANS_CODE REPLACETAGS(ANS_DESC)
--------------------- ----------------------------------------
50000000000000005770 Enter <B>EXAMPLE.COM</B> and press Ok.
40000000000000000164 Enter <B>EXAMPLE</B> in connection
40000000000000000165 Enter <B>EXAMPLE</B> in EXAMPLE.COM.
