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

218次阅读

共计 1549 个字符，预计需要花费 4 分钟才能阅读完成。

前言

简单描述一下场景，在 Oracle 的一个存储过程中遍历一个 Cursor，然后在循环中需要用到 in 查询语句，而 in 里面的条件正是游标当前行的一个的字段值类型为字符串，形如：
Oracle 存储过程拼接 in 语句 & 自定义 split 函数

而存储过程中又自然而然的写了这样的语句（重点第四行）：

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，而且断点不能打在注释行）：
Oracle 存储过程拼接 in 语句 & 自定义 split 函数

鼠标放在 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 strsplit(p_value varchar2,
                                    p_split varchar2 := ',')
return strsplit_type
pipelined is
  v_idx       integer;
  v_str       varchar2(500);
  v_strs_last varchar2(4000) := p_value;

begin
  loop
    v_idx := instr(v_strs_last, p_split);
    exit when v_idx = 0;
    v_str       := substr(v_strs_last, 1, v_idx - 1);
    v_strs_last := substr(v_strs_last, v_idx + 1);
    pipe row(v_str);
  end loop;
  pipe row(v_strs_last);
  return;

end strsplit;

最后验证一下：
Oracle 存储过程拼接 in 语句 & 自定义 split 函数

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

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