Oracle查询更新列值的子字符串

Question

我们有如下两张表:

表答:

tbody> <<tr>2

ROWNUM	description
1	{"to"+ 1111","from":"9999";}
	{"to"+ 5555","from":"8888";}

您可以在PL/SQL代码值中使用专用于JSON的技术，例如

DECLARE
v_jsoncol       tableA.description%TYPE;
v_json_obj      json_object_t;
v_new_jsoncol   tableA.description%TYPE;
v_col1          tableB.col1%TYPE;
v_col2          VARCHAR2(25);
l_key_list      json_key_list;     
BEGIN
FOR c IN
(
SELECT *
FROM tableA
)
LOOP
v_json_obj := TREAT(json_element_t.parse(c.description) AS json_object_t);
l_key_list := v_json_obj.get_keys; 

FOR i IN 1 .. l_key_list.COUNT 
LOOP 
IF l_key_list (i) = 'to' THEN
v_col1 := v_json_obj.get_string (l_key_list (i));
SELECT TO_CHAR(col2)
INTO v_col2
FROM tableB
WHERE col1 = v_col1;
v_json_obj.put(l_key_list (i),v_col2); 
v_new_jsoncol := v_json_obj.to_string; 
UPDATE tableA SET description = v_new_jsoncol WHERE row_num = c.row_num;
END IF;
END LOOP;      
END LOOP;
END;
/

我使用instr来获得第3和第4 ">

注意:ROWNUM, description是保留关键字，所以我建议不要使用它们作为列名

下面是最终代码:

SELECT  ROWNUM , 
REPLACE (description , 
SUBSTR(  description , INSTR(description, '"', 1, 3)+1,
INSTR(description, '"', 1, 4) -  INSTR(description, '"', 1, 3)-1) ,
(select COL2 from tblB where COL1 =  
SUBSTR(  description , INSTR(description, '"', 1, 3)+1,
INSTR(description, '"', 1, 4) -  INSTR(description, '"', 1, 3)-1)
) 
)        
from tblA 

不要使用字符串函数。您应该使用JSON函数，可以使用JSON_MERGEPATCH:

MERGE INTO table_a dst
USING (
SELECT a.ROWID AS rid,
b.col2
FROM   table_a a
INNER JOIN table_b b
ON JSON_VALUE(a.description, '$.to' RETURNING VARCHAR2(10)) =  b.col1
) src
ON (dst.ROWID = src.RID)
WHEN MATCHED THEN
UPDATE
SET description = JSON_MERGEPATCH(
dst.description,
JSON_OBJECT(KEY 'to' VALUE src.col2)
);

对于您的样本数据:

CREATE TABLE Table_A (description CLOB CHECK (description IS JSON));
INSERT INTO table_a (description)
SELECT '{"to": "+1111", "from": "9999"}' FROM DUAL UNION ALL
SELECT '{"to": "+5555", "from": "8888"}' FROM DUAL;
CREATE TABLE Table_B (COL1, COL2) AS
SELECT '+1111', 222 FROM DUAL UNION ALL
SELECT '+5555', 666 FROM DUAL;

:

SELECT * FROM table_a;

输出:

tbody> <<tr>

DESCRIPTION
{"to" 222年,"from":"9999";}
{"to" 666年,"from":"8888";}