In Oracle, if a string containing a separator (such as "55 * 62 * 77", this string is separated by *, can be split into three numbers "55", "62" and " 77 "), to split them, it is more troublesome. The following stored procedure is to facilitate the splitting of the string. Create or Replace Function GetParamstr (- String Dismoring Function I_Srcstr Varchar2, - String I_SIGN VARCHAR2, - Parameter Symbol, such as '~', '' I_PARAMINDEX NUMBER, " O_Paramstr Out Varchar2 - Separated Parameters - Return Number - Return: - 0. Success - 1. I_Paramindex Crossing - 2. I_SRCSTR is empty - 3. Unable to locate parameters - Else. Oracle Error Codeas V_len PLS_INTEGER; V_STARTINDEX PLS_INTEGER; V_ENDINDEX PLS_INTEGER;
Err_srcstr_isnull Exception; err_Paramindex_Overflow Exception; err_startindex_notfound exception; begin if i_srcstr is null dam er er _srcstr_isnull; endiff;
If i_paramindex <= 0 THEN RAISE ERR_PARAMINDEX_OVERFLOW; END IF;
- Initialization output parameter o_Paramstr: = '';
- parameters obtained starting content index IF i_ParamIndex = 1 THEN v_StartIndex: = 0; ELSE v_StartIndex: = INSTRB (i_SrcStr, i_Sign, 1, i_ParamIndex - 1); IF v_StartIndex = 0 THEN RAISE Err_StartIndex_NotFound; END IF; END IF ;
- The end of the end of the parameter content is subscript v_endindex: = INSTRB (i_srcstr, i_sign, 1, i_paramindex); if v_ndindex = 0 THEN V_ENDINDEX: = Lengthb (i_SRCSTR) 1; end if;
O_Paramstr: = Substrb (i_srcstr, v_startindex 1, v_endindex - v_startindex - 1);
- Success to exit returnography;
EXCEPTION WHEN Err_ParamIndex_OverFlow THEN DBMS_OUTPUT.PUT_LINE ( 'failed: i_ParamIndex bounds'); RETURN 1; WHEN Err_SrcStr_IsNull THEN DBMS_OUTPUT.PUT_LINE (' failed: i_SrcStr blank '); RETURN 2; WHEN Err_StartIndex_NotFound THEN DBMS_OUTPUT.PUT_LINE (' fails : Unable to locate parameter serial number '); return 3; when ost_line (to_char (sqlcode) ||' '|| Substrb (SQlerRM, 1,200)); return sqlcode; endparamstr; / This is more convenient, as follows : Create or Replace Procedure P_agt_addplan (o_ret out number) is v_count number (3); - This is V_Item varchar2 (200) used to count; - this kind of string becom v_count: = 1 after saving split ; - Cyclic Sub-split string 55 # 66 # 77 put the desired value into v_item, if the loop is over, return to average 0, so I jump out the whene getparamstr ('55 # 66 # 77 ', '#', v_count, v_item = 0 loop dbms_output.put_line (v_item); v_count: = v_count 1; end loop; commit; o_ret: = 0; Exception when others kilback; o_ret: = 1; END P_AGT_ADDPLAN; /
Ayuan
9CBS certified blog expert
Blog expert
Huawei old employees
Big Data
More than ten years, in Huawei, Internet Corporation's experience, profound understanding and practical experience in CRM, big data, mainly sharing various project experience, including architecture, Java, big data and other articles
