Oracle stored procedure in keyword usage

Source: Internet
Author: User

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       &NBSP;INTEGER;&NBSP;&NBSP;V_STR&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;VARCHAR2 (+);   v_strs_last &NBSP;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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.