oracle存储过程中可用in,ORACLE存储过程in关键字使用

一、问题描述：

写了一个存储过程(只取了半截，能说明命题即可)

CREATE OR REPLACE 

PROCEDURE "MONTH_RPT_CENTER_STU_DATA_PROC" (centerIds in VARCHAR2)

AS mlockdate date;

BEGIN

/*

创建人：fengcl

创建时间：2018.1.23

更新人：

更新时间：

参数：中心编号(字符串类型)

功能：调用时，先从中间表删除指定中心的数据，再重新查询插入指定中心的数据到中间表

*/

--获取月报锁定日期

select trunc(sysdate,'mm')+to_number( name) into mlockdate

from mdm_dic_option where code='monthReportDate' and dic_code='system_set_001';

if mlockdate

delete from rpt_month_stu_data

where RPT_DATE>=add_months(trunc(sysdate,'mm'),-1)

and RPT_DATE

commit;

在调用这个存储过程的时候，如果参数centerIds只有一个数字，例如：1 可以正常执行，但是如果有多个数字的话，例如：1,2,3,4,5 就回报如下错误，

Procedure execution failed

ORA-01722: 无效数字

ORA-06512: 在 "CRM3TEST.MONTH_RPT_CENTER_STU_DATA_PROC", line 17

ORA-06512: 在 line 1

查询时间: 0.029s

二、解决过程

通过百度发现，存储过程把这个参数作为了一个整体来处理了，并不象普通的sql一样，把他作为一个数组来对待

三、解决方案

百度了一下，发现网上方案挺多，但是我选择了以下的方案，就是自定义一个函数，对这个参数进行处理。

步骤一、新建一个oracle变量：(普通的sql查询窗口既可以创建)CREATE OR REPLACE TYPE strsplit_type IS TABLE OF VARCHAR2 (4000)

步骤二、新建一个函数(这个函数是网上现成的，亲测可用)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;

步骤三、改造存储过程(将原先的 in (centerIds) 更改为： in (select * from table (strsplit(centerIds))) )CREATE OR REPLACE

PROCEDURE "MONTH_RPT_CENTER_STU_DATA_PROC" (centerIds in VARCHAR2)

AS mlockdate date;

BEGIN

/*

创建人：fengcl

创建时间：2018.1.23

更新人：

更新时间：

参数：中心编号(字符串类型)

功能：调用时，先从中间表删除指定中心的数据，再重新查询插入指定中心的数据到中间表

*/

--获取月报锁定日期

select trunc(sysdate,'mm')+to_number( name) into mlockdate

from mdm_dic_option where code='monthReportDate' and dic_code='system_set_001';

if mlockdate

delete from rpt_month_stu_data

where RPT_DATE>=add_months(trunc(sysdate,'mm'),-1)

and RPT_DATE

commit;

到此，问题解决。