原有数据格式:
拆分后数据格式:
拆分SQL:
select bc.资源名称,bc.资源ID,bc.签到时间,bc.签到人,bb.column_value 签到附件ID
from report_fir.V_kw_checksign_info bc,table(hbrmw6.split_string(bc.签到附件ID,',')) bb;
HBRMW6.split_string函数创建:
CREATE OR REPLACE FUNCTION HBRMW6.split_string(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN sys.odcivarchar2list
AS
l_tablen PLS_INTEGER;
l_idx PLS_INTEGER;
l_start PLS_INTEGER := 1;
l_delimlen PLS_INTEGER := LENGTH(p_delimiter);
l_result sys.odcivarchar2list := sys.odcivarchar2list();
BEGIN
IF p_string IS NULL OR p_delimiter IS NULL THEN
RETURN l_result;
END IF;
l_tablen := REGEXP_COUNT(p_string, p_delimiter) + 1;
FOR i IN 1..l_tablen LOOP
l_idx := INSTR(p_string, p_delimiter, l_start);
IF l_idx = 0 THEN
l_result.EXTEND;
l_result(l_result.COUNT) := SUBSTR(p_string, l_start);
EXIT;
END IF;
l_result.EXTEND;
l_result(l_result.COUNT) := SUBSTR(p_string, l_start, l_idx - l_start);
l_start := l_idx + l_delimlen;
END LOOP;文章来源:https://www.toymoban.com/news/detail-537964.html
RETURN l_result;
END;
文章来源地址https://www.toymoban.com/news/detail-537964.html
到了这里,关于表中签到ID格式是以逗号分隔,需将ID拆开并分成多条数据的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!