你的分享就是我们的动力 ---﹥

Oracle存储过程拼凑in语句 & 自定义split函数

Oracle存储过程拼接in语句 & 自定义split函数

目录(?)[+]

前言

简单描述一下场景,在Oracle的一个存储过程中遍历一个Cursor,然后在循环中需要用到in查询语句,而in里面的条件正是游标当前行的一个的字段值类型为字符串,形如:
这里写图片描述

而存储过程中又自然而然的写了这样的语句(重点第四行):

select count(*) into current_hjnum from t_studentinfo 
where kslbdm_ = 1 
and hjstreet_ = everyrow.streetcode_ 
and hjdoornum_ in (everyrow.num_) 
and areacode_ = everyrow.householdareaid_ 
and bmflag_>=5;

然而并查询不出结果,将上面的游标属性替换成具体值再查询的话就可以查出来,究竟是什么原因呢?下面具体研究一下。

字符串与结果集

首先打个断点调试一下(注意PLSQL调试断点是点step out,而且断点不能打在注释行):
这里写图片描述

鼠标放在everyrow.num_上查看发现并没有问题,那是什么原因导致查询不到数据呢?仔细想一下突然恍然大悟,in关键字后面如果是动态的条件通常需要一个类型匹配的结果集,而我这里从游标当前行取出的值是字符串,也就是说sql其实是这样的:

... and hjdoornum_ in ('2,3,4,6,8') and ...

而并非是预想的:

... and hjdoornum_ in (2,3,4,6,8) and ...

所以这个代码写的有点想当然了,需要把in中的条件返回一个字符串拆分后的结果集才行,然而Oracle中并没有直接的split函数(这一点postgresql做的很强大),所以我们必须想办法自定义一个过程或者函数来实现split(字符串分割),其实也很简单,下面看一下具体实现。

创建SPLIT函数

首先需要创建一个Oracle类型(Type):

CREATE OR REPLACE TYPE strsplit_type IS TABLE OF VARCHAR2 (4000);

接下来就是实现SPLIT函数:

CREATE OR REPLACE FUNCTION str_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)

RETURN strsplit_type

IS

j INT := 0;

i INT := 1;

len INT := 0;

len1 INT := 0;

str VARCHAR2 (4000);

str_split1 strsplit_type := strsplit_type ();

BEGIN

len := LENGTH (p_str);

len1 := LENGTH (p_delimiter);

WHILE j < len

LOOP

j := INSTR (p_str, p_delimiter, i);

IF j = 0

THEN

j := len;

str := SUBSTR (p_str, i);

str_split1.EXTEND;

str_split1 (str_split1.COUNT) := str;

IF i >= len

THEN

EXIT;

END IF;

ELSE

str := SUBSTR (p_str, i, j - i);

i := j + len1;

str_split1.EXTEND;

str_split1 (str_split1.COUNT) := str;

END IF;

END LOOP;

RETURN str_split1;

END str_split;

最后验证一下:

这里写图片描述

可以看到已经正确的将字符串分割并返回了结果集,最后把存储过程中的语句修改一下即可

selectcount(*) into current_hjnum from t_studentinfo where kslbdm_ = 1and hjstreet_ = everyrow.streetcode_ and hjdoornum_ in (select * fromtable(strsplit(everyrow.num_))) and areacode_ = everyrow.householdareaid_ and bmflag_>=5;

总结

简单记录一下这个小坑,希望对遇到类似问题的朋友有所帮助,The End。