oracle存储过程分隔字符串,Oracle存储过程，以逗号分隔字符串传参的处理

CREATE OR REPLACE PROCEDURE PROC_UPDATE_LIUYANG(VIDNUMERIC VARCHAR2,

VMARKVARCHAR2,

VKEEPTYPEVARCHAR2,

VMODIFYBYVARCHAR2, verrorint out int) AS

CURSOR CUR_KEEPTYPE IS

SELECT *

FROM THE (SELECT CAST(STR2NUMLIST123(VKEEPTYPE) ASVARTABLETYPE)FROMDUAL);

OLD_KEEP_TYPEVARCHAR2(60);

VTEMPLATE_IDVARCHAR2(60);

VSMPSORTVARCHAR2(40);

VSAMPLENAMEVARCHAR2(60);

VSPECIFACTIONVARCHAR2(60);

VPRODUCTUNITVARCHAR2(30);

VBATCHNAMEVARCHAR2(60);

VCOUNTINT;BEGINverrorint := 0;SELECTT.CT_KEEP_TYPEINTOOLD_KEEP_TYPEFROMSAMPLE TWHERE T.ID_NUMERIC = LPAD(VIDNUMERIC, 10);SELECTS.TEMPLATE_IDINTOVTEMPLATE_IDFROMSAMPLE SWHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10);SELECTS.CT_SMP_SORTINTOVSMPSORTFROMSAMPLE SWHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10);SELECTS.SAMPLE_NAMEINTOVSAMPLENAMEFROMSAMPLE SWHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10);SELECTS.CT_SMP_SPECIFICATIONINTOVSPECIFACTIONFROMSAMPLE SWHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10);SELECTS.PRODUCT_UNITINTOVPRODUCTUNITFROMSAMPLE SWHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10);SELECTS.BATCH_NAMEINTOVBATCHNAMEFROMSAMPLE SWHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10);SELECT COUNT(0)INTOVCOUNTFROMINVENTORY_HEADER HWHERE H.IDENTITY =VTEMPLATE_ID;INSERT INTOSAMPLE_AUDIT_TRAILS

(SAMPLE, CREATE_BY, CREATE_ON, REASION, OPERATE_TYPE)VALUES(LPAD(VIDNUMERIC,10),

VMODIFYBY,

SYSDATE,

VMARK,‘取样台账中对样品留样类型有之前的‘ || OLD_KEEP_TYPE || ‘调整为现在‘ ||VKEEPTYPE);IF VCOUNT = 0 THEN

INSERT INTOINVENTORY_HEADER

(IDENTITY, BASE_UNIT, DESCRIPTION)VALUES(VTEMPLATE_ID,

VPRODUCTUNIT,

VSAMPLENAME|| VSPECIFACTION ||VSMPSORT);END IF;DELETE FROMCT_KEEP_SAMPLE CKSWHERE CKS.ID_NUMERIC = LPAD(VIDNUMERIC, 10);DELETE FROM INVENTORY_ITEM II WHERE II.SAMPLE_ID =TRIM(VIDNUMERIC);UPDATESAMPLE SSET S.CT_KEEP_TYPE =VKEEPTYPEWHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10);FOR C_K INCUR_KEEPTYPE LOOPIF C_K.COLUMN_VALUE = ‘常规留样‘ THEN

INSERT INTOCT_KEEP_SAMPLE

(ID_NUMERIC, CT_K_TYPE)VALUES(LPAD(VIDNUMERIC,10), 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_VALUE);END IF;ENDLOOP;commit;

EXCEPTIONWHEN OTHERS THENverrorint := 1;ROLLBACK;END PROC_UPDATE_LIUYANG;