ORACLE預存程序in關鍵字使用

標籤：描述   更改   題解   執行   cti   var   方案   參數   failed   

一、問題描述：

寫了一個預存程序（只取了半截，能說明命題即可）

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<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;

在調用這個預存程序的時候，如果參數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_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;

步驟三、改造預存程序（將原先的 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<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;

到此，問題解決。

ORACLE預存程序in關鍵字使用