Oracle預存程序，以逗號分隔字串傳參的處理

標籤：des   style   blog   color   io   ar   for   div   sp   

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預存程序，以逗號分隔字串傳參的處理