oracle业务关系sql,oracle 普通业务数据统计sql

字符串分割示例

SELECT REGEXP_SUBSTR(‘17,20,23‘, ‘[^,]+‘, 1, LEVEL, ‘i‘) AS STR

FROM DUAL CONNECT BY LEVEL <= LENGTH(‘17,20,23‘) - LENGTH(REGEXP_REPLACE(‘17,20,23‘, ‘,‘, ‘‘))+1;CREATE TABLEtm_change(

IDNUMBER,

transit_znoVARCHAR2(10),

srcVARCHAR2(100),

desVARCHAR2(100)

);CREATE TABLEtemp_tm_change(

IDNUMBER,

transit_znoVARCHAR2(10),

srcVARCHAR2(100),

desVARCHAR2(100)

);--原数据

SELECT t.*,ROWID FROMtm_change t;--输出数据

SELECT t.* FROMtemp_tm_change t;DELETE FROMtemp_tm_change;COMMIT;DECLARErecordCountNUMBER;--一条记录衍生的记录条数

sumCount NUMBER; --总条数

BEGINsumCount := 0;--sum

FOR rs IN (SELECT id,transit_zno ,src,des FROMtm_change) LOOP

recordCount := 0;--src

FOR srcRS IN (SELECT REGEXP_SUBSTR(rs.src, ‘[^,]+‘, 1, LEVEL, ‘i‘) AS str

FROM DUAL CONNECT BY LEVEL <= LENGTH(rs.src) - LENGTH(REGEXP_REPLACE(rs.src, ‘,‘, ‘‘))+1) LOOP--des

FOR desRS IN (SELECT REGEXP_SUBSTR(rs.des, ‘[^,]+‘, 1, LEVEL, ‘i‘) AS str

FROM DUAL CONNECT BY LEVEL <= LENGTH(rs.des) - LENGTH(REGEXP_REPLACE(rs.des, ‘,‘, ‘‘))+1) LOOP

recordCount := recordCount + 1;

sumCount := sumCount + 1;INSERT INTO temp_tm_change(ID,transit_zno,src,des) VALUES (rs.id,rs.transit_zno,srcRs.str,desRS.str);COMMIT;ENDLOOP;ENDLOOP;

--dbms_output.put_line(‘insert a record : id ->‘ || rs.id || ‘recordCount -->‘ ||recordCount ); buffer overflowENDLOOP;

--dbms_output.put_line(‘create record sumCount -->‘ ||sumCount );END;SELECT transit_zno,src,des FROM temp_tm_change GROUP BY transit_zno,src,des HAVING COUNT(*) > 1;

oracle 普通业务数据统计sql

标签：span   delete   color   com   replace   har   varchar   tput   ora

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/fubinhnust/p/7260020.html