ORACLE存儲(chǔ)過(guò)程in關(guān)鍵字使用

一、問(wèn)題描述：

寫(xiě)了一個(gè)存儲(chǔ)過(guò)程（只取了半截，能說(shuō)明命題即可）

CREATE OR REPLACE 
PROCEDURE "MONTH_RPT_CENTER_STU_DATA_PROC" (centerIds in VARCHAR2)
AS mlockdate date;
BEGIN
/*
	創(chuàng)建人：fengcl
	創(chuàng)建時(shí)間：2018.1.23
	更新人：
	更新時(shí)間：
	參數(shù)：中心編號(hào)（字符串類型）
	功能：調(diào)用時(shí)，先從中間表刪除指定中心的數(shù)據(jù)，再重新查詢插入指定中心的數(shù)據(jù)到中間表
*/
--獲取月報(bào)鎖定日期
select trunc(sysdate,'mm')+to_number( name) into mlockdate 
from mdm_dic_option where code='monthReportDate' and dic_code='system_set_001';

if mlockdate<trunc(sysdate)  then 
delete from rpt_month_stu_data 
where RPT_DATE>=add_months(trunc(sysdate,'mm'),-1) 
	    and RPT_DATE<trunc(sysdate,'mm') and CENTER_ID in (centerIds);
commit;

在調(diào)用這個(gè)存儲(chǔ)過(guò)程的時(shí)候，如果參數(shù)centerIds只有一個(gè)數(shù)字，例如：1 可以正常執(zhí)行，但是如果有多個(gè)數(shù)字的話，例如：1,2,3,4,5 就回報(bào)如下錯(cuò)誤，

Procedure execution failed

ORA-01722: 無(wú)效數(shù)字

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

ORA-06512: 在 line 1

查詢時(shí)間: 0.029s

二、解決過(guò)程

通過(guò)百度發(fā)現(xiàn)，存儲(chǔ)過(guò)程把這個(gè)參數(shù)作為了一個(gè)整體來(lái)處理了，并不象普通的sql一樣，把他作為一個(gè)數(shù)組來(lái)對(duì)待

三、解決方案

百度了一下，發(fā)現(xiàn)網(wǎng)上方案挺多，但是我選擇了以下的方案，就是自定義一個(gè)函數(shù)，對(duì)這個(gè)參數(shù)進(jìn)行處理。

步驟一、新建一個(gè)oracle變量：（普通的sql查詢窗口既可以創(chuàng)建）

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

步驟二、新建一個(gè)函數(shù)（這個(gè)函數(shù)是網(wǎng)上現(xiàn)成的，親測(cè)可用）

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;

步驟三、改造存儲(chǔ)過(guò)程（將原先的 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
/*
	創(chuàng)建人：fengcl
	創(chuàng)建時(shí)間：2018.1.23
	更新人：
	更新時(shí)間：
	參數(shù)：中心編號(hào)（字符串類型）
	功能：調(diào)用時(shí)，先從中間表刪除指定中心的數(shù)據(jù)，再重新查詢插入指定中心的數(shù)據(jù)到中間表
*/
--獲取月報(bào)鎖定日期
select trunc(sysdate,'mm')+to_number( name) into mlockdate 
from mdm_dic_option where code='monthReportDate' and dic_code='system_set_001';

if mlockdate<trunc(sysdate)  then 
delete from rpt_month_stu_data 
where RPT_DATE>=add_months(trunc(sysdate,'mm'),-1) 
	    and RPT_DATE<trunc(sysdate,'mm') and CENTER_ID in (select * from table(strsplit(centerIds)));
commit;

到此，問(wèn)題解決。