版权声明:本文为博主原创文章,转载请标明出处(http://blog.csdn.net/wlwlwlwl015)Thanks.
前言
简单描述一下场景,在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。
相关推荐
oracle自定义日期函数、你值得拥有!
本文档详细介绍了如何在Oracle数据库中实现像C#等其它编程语言中Split函数来拆分字符的功能。例子简单易懂,并附有详细的实现过程。
* 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100,可拼出超4000的超长字符串 * 可自定义指定分隔符separator * 可自定义指定排序字段sequence,对于数字或日期类型的...
Oracle效验统一代码正确性的自定义函数,效验规则以国标为准.
oracle存储过程中入参是逗号分隔,并且参数要使用在in过滤语句中查询数据。处理的方法与实现
Oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abc defg hijkl nmopqr stuvw xyz”,分隔符是空格,但空格个数不定。 源代码: CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR...
Oracle自定义聚合函数,分析函数.
Oracle 自定义函数需求有个字段,工号JOB_NUMBER ='10202804/10133066/10131810',数据库里是编号,所有想写个函数,将JOB_NUMBER 传递进去,然后返回 '张三/李四/王五',如果王五不存在则返回昵称拼接code '张三/...
Oracle存储过程、自定义函数、动态建表存储过程等例子的 sql 文件
自定义函数不能被直接在Java中调用,可以通过存储过程调用函数的方法。本文采用实例的方法,介绍如何在Oracle数据库客户端PL/SQL中创建自定义函数,然后创建相应的存储过程调用该函数,最终实现一个实例计算。
存储过程、自定义函数、包及触发器的详细,对存储过程不熟悉的人有帮助
Oracle存储过程、游标、函数的详解
C# 传入自定义列表List 到Oracle存储过程 亲测有效,执行速度极快
oracle 存储过程的基本语法 1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字 2.SELECT INTO ...
java调用oracle存储过程或者函数
oracle自定义函数的总结,主要对如何定义函数以及使用。
最近在oracle 中用到拆分字符串返回数组,一直头痛,因为在 oracle 中没有类似java中有split 函数 ,所以要自己写。好不容搜到一个。那网上是到处都是这个代码。怎么找都是这个方法,我就用了。这个方法如下: ...
ORACLE 用户自定义异常小例子 1.进入pl/sql测试窗口 2.执行语句 declare empname varchar2(255); customize_exp EXCEPTION; –自定义异常 begin FOR c IN (select d.* from scott.dept d) ...
大数据量,返回值虽然是CLOB,依然会报错; CLOB变量需要dbms_lob.createtemporary,临时表空间中,建立临时LOB。 大数据量,返回值虽然是CLOB,依然会报错; CLOB变量需要dbms_lob.createtemporary,临时表空间中,...