Oracle stored procedure in keyword usage

First, the problem description:

Wrote a stored procedure (only half, can explain the proposition can be)

CREATE OR REPLACE PROCEDURE "Month_rpt_center_stu_data_proc" (Centerids in VARCHAR2) as Mlockdate date; begin/* Creator: Fengcl created: 2018.1.23 Updated: Update time: Parameter: Center number (String type) function: When called, the data of the specified center is deleted from the intermediate table, and then the data inserted into the specified center is re-queried to the intermediate table */-- Get monthly Lock Date 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;

When calling this stored procedure, if the parameter centerids has only one number, for example: 1 can execute normally, but if there are multiple numbers, for example: 1,2,3,4,5 returns the following error,

Procedure Execution failed

ORA-01722: Invalid number

ORA-06512: In the crm3test. Month_rpt_center_stu_data_proc ", line 17

ORA-06512: On line 1

Enquiry Time: 0.029s

Ii. process of Settlement

Baidu found that the stored procedure to take this parameter as a whole to deal with, not like ordinary SQL, he as an array to treat

Third, the solution

Baidu a bit, found online programs quite a lot, but I chose the following scheme, is to customize a function, to deal with this parameter.

Step one, create a new Oracle variable: (Normal SQL query window can be created)

CREATE OR REPLACE TYPE Strsplit_type is TABLE of VARCHAR2 (4000)

Step two, create a new function (this function is on-line, pro-Test available)

Create or replace function strsplit (p_value varchar2,                                      p_split varchar2 :=   ', ') return strsplit_typepipelined is  v_idx        INTEGER;  V_STR       VARCHAR2 (+);   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&nbSp;row (V_STR);   end loop;  pipe row (V_strs_last);  return;end  Strsplit;

Step three, modify the stored procedure (change the original in (Centerids) to: (SELECT * FROM table (Strsplit (Centerids))))

CREATE OR REPLACE PROCEDURE "Month_rpt_center_stu_data_proc" (Centerids in VARCHAR2) as Mlockdate date; begin/* Creator: Fengcl created: 2018.1.23 Updated: Update time: Parameter: Center number (String type) function: When called, the data of the specified center is deleted from the intermediate table, and then the data inserted into the specified center is re-queried to the intermediate table */-- Get monthly Lock Date 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 (cent Erids)); commit;

To this, the problem is resolved.

Oracle stored procedure in keyword usage