首页 > 代码库 > Oracle存储过程，以逗号分隔字符串传参的处理
Oracle存储过程，以逗号分隔字符串传参的处理
Oracle存储过程，经常会遇见传入的参数是逗号分隔。
处理需要3步：
第一步，创建Type类型
第二部，创建函数
第三部，创建存储过程
代码如下：
第一步：
create or replace type varTableType as table of nvarchar2(40)
第二步：
create or replace function str2numList123( p_string in varchar2 ) return varTableType as v_str long default p_string || ‘,‘; v_n number; v_data varTableType := varTableType(); begin loop 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 ); end loop; return v_data; end;
第三步：
CREATE OR REPLACE PROCEDURE PROC_UPDATE_LIUYANG(VIDNUMERIC VARCHAR2, VMARK VARCHAR2, VKEEPTYPE VARCHAR2, VMODIFYBY VARCHAR2, verrorint out int) AS CURSOR CUR_KEEPTYPE IS SELECT * FROM THE (SELECT CAST(STR2NUMLIST123(VKEEPTYPE) AS VARTABLETYPE) FROM DUAL); OLD_KEEP_TYPE VARCHAR2(60); VTEMPLATE_ID VARCHAR2(60); VSMPSORT VARCHAR2(40); VSAMPLENAME VARCHAR2(60); VSPECIFACTION VARCHAR2(60); VPRODUCTUNIT VARCHAR2(30); VBATCHNAME VARCHAR2(60); VCOUNT INT;BEGIN verrorint := 0; SELECT T.CT_KEEP_TYPE INTO OLD_KEEP_TYPE FROM SAMPLE T WHERE T.ID_NUMERIC = LPAD(VIDNUMERIC, 10); SELECT S.TEMPLATE_ID INTO VTEMPLATE_ID FROM SAMPLE S WHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10); SELECT S.CT_SMP_SORT INTO VSMPSORT FROM SAMPLE S WHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10); SELECT S.SAMPLE_NAME INTO VSAMPLENAME FROM SAMPLE S WHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10); SELECT S.CT_SMP_SPECIFICATION INTO VSPECIFACTION FROM SAMPLE S WHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10); SELECT S.PRODUCT_UNIT INTO VPRODUCTUNIT FROM SAMPLE S WHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10); SELECT S.BATCH_NAME INTO VBATCHNAME FROM SAMPLE S WHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10); SELECT COUNT(0) INTO VCOUNT FROM INVENTORY_HEADER H WHERE H.IDENTITY = VTEMPLATE_ID; INSERT INTO SAMPLE_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 INTO INVENTORY_HEADER (IDENTITY, BASE_UNIT, DESCRIPTION) VALUES (VTEMPLATE_ID, VPRODUCTUNIT, VSAMPLENAME || VSPECIFACTION || VSMPSORT); END IF; DELETE FROM CT_KEEP_SAMPLE CKS WHERE CKS.ID_NUMERIC = LPAD(VIDNUMERIC, 10); DELETE FROM INVENTORY_ITEM II WHERE II.SAMPLE_ID = TRIM(VIDNUMERIC); UPDATE SAMPLE S SET S.CT_KEEP_TYPE = VKEEPTYPE WHERE S.ID_NUMERIC = LPAD(VIDNUMERIC, 10); FOR C_K IN CUR_KEEPTYPE LOOP IF C_K.COLUMN_VALUE = ‘常规留样‘ THEN INSERT INTO CT_KEEP_SAMPLE (ID_NUMERIC, CT_K_TYPE) VALUES (LPAD(VIDNUMERIC, 10), C_K.COLUMN_VALUE); ELSE INSERT INTO INVENTORY_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; END LOOP; commit;EXCEPTION WHEN OTHERS THEN verrorint := 1; ROLLBACK;END PROC_UPDATE_LIUYANG;
Oracle存储过程，以逗号分隔字符串传参的处理
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。