Oracle 替换功能

本文介绍了Oracle 替换功能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

选择查询时，我需要用 Table2 的值替换 Table1 的字段值.

I need to replace the Table1's filed values from Table2's values while select query.

例如:

表1:

Org Permission -------------------------------------- Company1 1,3,7 Company2 1,3,8

表2:

Permission Permission -------------------------------------- 1 Read 3 Write 7 Execute 8 Delete

我需要这样的:

Org Permission -------------------------------------- Company1 Read,Write,Execute Company2 Read,Write,Delete

推荐答案

如果您不想更新现有表而只想选择数据，那么您可以使用这个有点费力的查询.

If you don't want to update the existing table and only want to select the data then you can use this somewhat laborious query.

sqlfiddle/#!4/22909/4

WITH changed_table AS (SELECT val1, EXTRACTVALUE (x.COLUMN_VALUE, 'e') val2new FROM (SELECT val1, val2 xml_str FROM table1), TABLE (XMLSEQUENCE (XMLTYPE ( '<e><e>' || REPLACE (xml_str, ',', '</e><e>') || '</e></e>' ).EXTRACT ('e/e') ) ) x) SELECT ct.val1, listagg(table2.val2,',') within group (order by table2.val2) val2 FROM changed_table ct, table2 table2 WHERE ct.val2new = table2.val1 group by ct.val1;

我使用 XMLTYPE 将逗号分隔的数字分隔为行.然后将行与第二个表连接以获得描述，最后使用 LISTAGG 函数形成逗号分隔的字符串.不知道这个查询效率如何.我同意马克班尼斯特的评论.

I have used the XMLTYPE to separate the comma separated numbers to rows. Then joined the rows with second table to get the description and finally used the LISTAGG function to form comma separated string. Don't know how efficient this query is. I agree with Mark Bannister's comment.