Oracle存储过程拼接in语句 & 自定义split函数
来源:互联网 发布:世界网络强国有哪些 编辑:程序博客网 时间:2024/04/28 16:10
前言
简单描述一下场景,在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 strsplit(p_value varchar2, p_split varchar2 := ',')return strsplit_typepipelined 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;
最后验证一下:
可以看到已经正确的将字符串分割并返回了结果集,最后把存储过程中的语句修改一下即可:
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;
总结
简单记录一下这个小坑,希望对遇到类似问题的朋友有所帮助,The End。
2 0
- Oracle存储过程拼接in语句 & 自定义split函数
- oracle存储过程自定义split函数
- oracle自定义函数、存储过程
- Oracle 自定义split 函数
- oracle中实现split函数的存储过程
- oracle存储过程实现的split函数,分隔字符串
- oracle存储过程实现的split函数,分隔字符串
- oracle自定义函数和存储过程
- oracle自定义函数、存储过程2
- Oracle存储过程和自定义函数
- oracle存储过程和自定义函数
- Oracle基础 自定义函数和存储过程
- 存储过程中拼接SQL语句
- MySQL存储过程执行拼接语句
- SQL存储过程中SQL语句拼接
- sql存储过程,语句拼接,使用游标
- oracle存储过程及自定义函数(存储函数)初学
- oracle split函数(自定义)
- 使用WinINet和WinHTTP实现Http访问
- 登封造极之树——树链剖分
- mysql 默认八小时空闲自动断开连接
- 关于C++中结构体初始化小结
- Mybatis中配置Mapper的方法
- Oracle存储过程拼接in语句 & 自定义split函数
- 简单错误记录
- 1003
- 近期需要学习的知识点
- Database之增删改查工具类(简单版)
- 通过sql替换mysql 表某字段中的部分内容,
- Activity与Fragment易混点归纳
- Android Socket专题: UDP通信服务器端app的demo的实现
- Scrum简明入门