Oracle stored procedures, comma-delimited string parameter handling

Oracle stored procedures often meet incoming parameters that are separated by commas.

Processing takes 3 steps:

The first step is to create type

Part two, creating a function

Part III, creating a stored procedure

The code is as follows:

The first step:

Create or Replace  as Table    of Nvarchar2 (+)

Step Two:

Create or Replace functionStr2numlist123 (p_stringinch varchar2)returnVartabletype asV_strLong defaultP_string|| ','; V_n Number; V_data Vartabletype:=Vartabletype (); beginLoop V_n:=To_number (InStr (V_str,',' )); Exit  when(NVL (V_n,0)= 0);          V_data.extend; V_data (v_data.Count) := LTrim(RTrim(Substr (V_STR,1, V_n-1))); V_STR:=substr (V_str, V_n+1 ); EndLoop; returnV_data; End;

Step Three:

CREATE OR REPLACE PROCEDUREProc_update_liuyang (vidnumericVARCHAR2, VmarkVARCHAR2, VkeeptypeVARCHAR2, VmodifybyVARCHAR2, Verrorint outint) as  CURSORCur_keeptype is    SELECT *       fromThe (SELECT CAST(Str2numlist123 (Vkeeptype) asVartabletype) fromDUAL); Old_keep_typeVARCHAR2( -); vtemplate_idVARCHAR2( -); VsmpsortVARCHAR2( +); VsamplenameVARCHAR2( -); VspecifactionVARCHAR2( -); VproductunitVARCHAR2( -); VbatchnameVARCHAR2( -); VcountINT;BEGINVerrorint:= 0; SELECTT.ct_keep_type intoOld_keep_type fromSAMPLE TWHERET.id_numeric=Lpad (Vidnumeric,Ten); SELECTs.template_id intovtemplate_id fromSAMPLE SWHERES.id_numeric=Lpad (Vidnumeric,Ten); SELECTS.ct_smp_sort intoVsmpsort fromSAMPLE SWHERES.id_numeric=Lpad (Vidnumeric,Ten); SELECTS.sample_name intoVsamplename fromSAMPLE SWHERES.id_numeric=Lpad (Vidnumeric,Ten); SELECTs.ct_smp_specification intovspecifaction fromSAMPLE SWHERES.id_numeric=Lpad (Vidnumeric,Ten); SELECTS.product_unit intoVproductunit fromSAMPLE SWHERES.id_numeric=Lpad (Vidnumeric,Ten); SELECTS.batch_name intoVbatchname fromSAMPLE SWHERES.id_numeric=Lpad (Vidnumeric,Ten); SELECT COUNT(0)     intoVcount fromInventory_header HWHEREH.IDENTITY =vtemplate_id; INSERT  intosample_audit_trails (SAMPLE, create_by, create_on, Reasion, Operate_type)VALUES(Lpad (Vidnumeric,Ten), Vmodifyby, Sysdate, Vmark,'The sample retention type in the sample table has a prior' ||Old_keep_type|| 'adjust to present' ||Vkeeptype); IFVcount= 0  Then    INSERT  intoInventory_header (IDENTITY, Base_unit, DESCRIPTION)VALUES(vtemplate_id, Vproductunit, Vsamplename||Vspecifaction||vsmpsort); END IF; DELETE  fromct_keep_sample CKSWHERECks.id_numeric=Lpad (Vidnumeric,Ten); DELETE  fromInventory_item IIWHEREIi. sample_id=TRIM (vidnumeric); UPDATESAMPLE SSETS.ct_keep_type=VkeeptypeWHERES.id_numeric=Lpad (Vidnumeric,Ten);  forC_kinchCur_keeptype LOOPIFC_k.column_value= 'General Retention Sample'  Then      INSERT  intoct_keep_sample (Id_numeric, Ct_k_type)VALUES(Lpad (Vidnumeric,Ten), c_k.column_value); ELSE      INSERT  intoInventory_item (inventory_id, sample_id, Item_type, Item_code, description)VALUES(vtemplate_id, TRIM (vidnumeric), C_k.column_value, Vbatchname, C_k.column_val    UE); END IF; ENDLOOP; Commit; EXCEPTION whenOTHERS ThenVerrorint:= 1; ROLLBACK;ENDProc_update_liuyang;

Oracle stored procedures, comma-delimited string parameter handling