Oracle PL/SQL 字串分割處理範例：以冒號分隔符拆解包裹清單

實務常需在 PL/SQL 中分割分隔符字串 (如 'GUID1:GUID2:GUID3') 逐元素處理。本文提供一個完整的存儲過程範例，展示 INSTR/SUBSTR 迴圈分割並執行 DML。

存儲過程完整代碼

CREATE OR REPLACE PROCEDURE ModifyLadingItemPack (
  ASoditemguid      VARCHAR2,  -- 合同電子倉單明細 GUID
  ALadingitemGUID   VARCHAR2,  -- 提單子項 GUID
  ACDResID          VARCHAR2,  -- 電子倉單號
  APackageIDList    VARCHAR2,  -- 倉單捆包 GUID 列表 (冒號分隔)
  Aladingnum        FLOAT,
  Aladingpieces     FLOAT,
  AReturnValue OUT  INTEGER
) IS
  ALadingItem_PackagesGUID VARCHAR2(32);
  ATempIDList              VARCHAR2(4000);
  AID                      VARCHAR2(32);
  APackageWeight           FLOAT;
  APackagePiece            FLOAT;
  ATotalPackWeight         FLOAT;
BEGIN
  AReturnValue := 1;
  ATempIDList := APackageIDList;
  APackageWeight := 0;
  APackagePiece  := 0;

  WHILE (INSTR(ATempIDList, ':') > 0) OR (LENGTH(ATempIDList) > 0) LOOP
    IF INSTR(ATempIDList, ':') > 0 THEN
      SELECT SYS_GUID() INTO ALadingItem_PackagesGUID FROM dual;
      AID := SUBSTR(ATempIDList, 1, INSTR(ATempIDList, ':') - 1);
      ATempIDList := SUBSTR(ATempIDList, INSTR(ATempIDList, ':') + 1);
    ELSE
      AID := ATempIDList;
      ATempIDList := '';
    END IF;

    -- 1. 寫入提單捆包明細
    INSERT INTO HT_LadingItem_packages (sGUID, sLadingItemGUID, sCDResID, sPackageID)
    VALUES (ALadingItem_PackagesGUID, ALadingitemGUID, ACDResID, AID);

    -- 2. 更新倉單捆包狀態
    UPDATE ZY_Packages SET state = 5 WHERE SGUID = AID;

    -- 3. 累加重量與件數
    SELECT fWeight INTO ATotalPackWeight FROM ZY_Packages WHERE sGUID = AID;
    APackageWeight := APackageWeight + ATotalPackWeight;
    APackagePiece  := APackagePiece + 1;
  END LOOP;

  -- 4. 更新合同電子倉單明細統計
  UPDATE HT_SODItem
  SET fLadingNum = fLadingNum + APackageWeight,
      fLadingPieces = fLadingPieces + APackagePiece
  WHERE sGUID = ASoditemguid;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    AReturnValue := 99;
    ROLLBACK;
END;
/

關鍵技巧解析

• INSTR(str, ':') 尋找分隔符位置，SUBSTR 截取前段。
• 迴圈條件 (INSTR(...) > 0) OR (LENGTH(...) > 0) 兼容「含分隔符」與「最後一段無分隔符」兩種情況。
• 使用 SYS_GUID() 產生唯一主鍵。
• 統一 COMMIT/ROLLBACK 保證交易原子性。

現代替代方案 (12c+)

-- 使用 JSON_TABLE 或 XMLTABLE 將分隔符字串轉為行集
SELECT TRIM(column_value) AS guid
FROM XMLTABLE(('"' || REPLACE(APackageIDList, ':', '","') || '"'));

掌握 PL/SQL 字串分割模式，能處理批次傳入的清單參數，避免多次呼叫帶來的網路往返開銷。