最近在项目中碰到一个问题,有个表的字段是按逗号分割存入的字符串,然后页面需要对这个字段进行筛选查询。Oracle有个交集的关键字INTERSECT,但是用在字段上好像行不通。变通了下,写一个函数。如下:
CREATE OR REPLACE FUNCTION GET_INTERSECT(V_STR1 VARCHAR2,
V_STR2 VARCHAR2,
V_SEP VARCHAR2) RETURN VARCHAR2 IS
V_RES VARCHAR2(100);
V_STR VARCHAR2(10);
CURSOR CUR_GET_DATA IS
SELECT REGEXP_SUBSTR(res, '[^' || V_SEP || ']+', 1, ROWNUM) res
FROM (SELECT V_STR1 AS RES FROM DUAL)
CONNECT BY ROWNUM <=
LENGTH(REGEXP_REPLACE(RES, '[^' || V_SEP || ']+')) + 1
INTERSECT
SELECT REGEXP_SUBSTR(RES, '[^' || V_SEP || ']+', 1, ROWNUM) RES
FROM (SELECT V_STR2 AS RES FROM DUAL)
CONNECT BY ROWNUM <=
LENGTH(REGEXP_REPLACE(RES, '[^' || V_SEP || ']+')) + 1;
BEGIN
OPEN CUR_GET_DATA;
LOOP
FETCH CUR_GET_DATA
INTO V_STR;
EXIT WHEN CUR_GET_DATA%NOTFOUND;
V_RES := V_RES || V_STR || V_SEP;
END LOOP;
--去掉最后一个符号
V_RES := substr(V_RES, 1, LENGTH(V_RES) - 1);
CLOSE CUR_GET_DATA;
RETURN(V_RES);
END GET_INTERSECT;
经过简单的测试,感觉没问题,就用在了项目中试了下,正式表中有10000多数据,发现一下子没反应了。卡住了。。。缩小了下数据范围到100多条也得执行10s多时间。显然此函数无法满足项目需要。
查询了下发现,由于使用的是动态游标,由于里面带参数,所以sql语句不会在一开始就编译,要等到调用的时候参数确定后才编译,相比效率是低了一点,数据量一大就更不行了。
最后再变一下,不要游标了,经测试满足项目需求。函数如下供参考:
CREATE OR REPLACE FUNCTION GET_INTERSECT(IN_STR1 VARCHAR2,
IN_STR2 VARCHAR2,
V_SEP VARCHAR2) RETURN VARCHAR2 IS
V_RES VARCHAR2(100);
V_STR1 VARCHAR2(100);
V_STR2 VARCHAR2(100);
V_FIRST1 VARCHAR2(10);
V_FIRST2 VARCHAR2(10);
BEGIN
V_STR1:=ltrim(rtrim(IN_STR1));
V_STR2:=ltrim(rtrim(IN_STR2));
WHILE V_STR1 IS NOT NULL LOOP
IF INSTR(V_STR1,V_SEP)<>0 THEN
V_FIRST1:=SUBSTR(V_STR1,1,INSTR(V_STR1,V_SEP)-length(V_SEP));
ELSE
V_FIRST1:=V_STR1;
END IF;
V_FIRST1:=ltrim(rtrim(V_FIRST1));
WHILE INSTR(V_STR2,V_SEP)<>0 LOOP
V_FIRST2:=SUBSTR(V_STR2,1,INSTR(V_STR2,V_SEP)-length(V_SEP));
V_FIRST2:=ltrim(rtrim(V_FIRST2));
IF V_FIRST1 = V_FIRST2 THEN
V_RES := V_RES || V_FIRST1 || V_SEP;
END IF;
V_STR2:=SUBSTR(V_STR2,INSTR(V_STR2,V_SEP)+length(V_SEP),length(V_STR2));
END LOOP;
V_STR2:=ltrim(V_STR2);
IF V_FIRST1 = V_STR2 THEN
V_RES := V_RES || V_FIRST1 || V_SEP;
END IF;
IF INSTR(V_STR1,V_SEP)=0 THEN
V_STR1:=NULL;
ELSE
V_STR1:=SUBSTR(V_STR1,INSTR(V_STR1,V_SEP)+length(V_SEP),length(V_STR1));
V_STR2:=ltrim(rtrim(IN_STR2));
END IF;
END LOOP;
--去掉最后一个符号
V_RES := SUBSTR(V_RES, 1, LENGTH(V_RES) - 1);
RETURN(V_RES);
END GET_INTERSECT;