已经测试通过,请自行优化
--构造数据
Create Table t (
a Varchar2(30),
b Varchar2(30),
c Date
);
Insert Into t Values('辣椒','食品',Null);
Commit;
--存储过程
Declare
str_tmp Varchar2(4000) := '辣椒_食品,西红柿_食品';--请自行封装成函数入参
CURSOR cur_emp Is
SELECT REGEXP_SUBSTR(str_tmp, '[^,]+', 1, ROWNUM,'i') str
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH(str_tmp) -
LENGTH(Replace(str_tmp, ',',''))+1;
row_emp cur_emp%ROWTYPE;
col1 Varchar2(4000);
col2 Varchar2(4000);
Begin
OPEN cur_emp;
FETCH cur_emp INTO row_emp;
WHILE cur_emp%FOUND
LOOP
col1 := REGEXP_SUBSTR(row_emp.str,'[^_]+',1,1,'i');
col2 := REGEXP_SUBSTR(row_emp.str,'[^_]+',1,2,'i');
Update t Set c=Sysdate Where a=col1 And b=col2;
FETCH cur_emp INTO row_emp;
END LOOP;
End;