您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

從 Java 預存程序遷移至 SQL PL 實戰筆記：部署失敗倒逼的重寫之路

編輯：DB2 教學

背景動機

專案早期在 DB2 開發中心以 Java (SQLJ) 開發預存程序。客戶資料庫升級遷移後，原 Java SP 部署持續失敗，報錯 SQL4304N reason code 5 (無法載入 Java 類別)。Google 中英資源均無解，決定將邏輯重寫為原生 SQL PL 程序，規避 Java 執行環境相依性。

原始 Java/SQLJ 邏輯概覽

功能：企業基準碼產生 (輸入企業名稱/地址/法人/電話，輸出基準碼)
核心流程：
1. 查詢 T_PUB_BASECODE 依企業名稱模糊比對最大基準碼
2. 若無則取全表最大基準碼
3. 解析本體碼 (第 3-8 字元)，按 0-9, A-Z 進位遞增邏輯產生新本體碼
4. 依權重表 (3→9, 4→10, 5→5, 6→8, 7→4, 8→2) 計算校驗碼 (Mod 11 算法，10=X, 11=0)
5. 組裝 GZ + 本體碼 + 校驗碼，寫入資料表並返回
技術棧：SQLJ、JDBC、HashMap、Iterator、SPContext

遷移後 SQL PL 程序碼

SET SCHEMA GDZJ;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","GDZJ";

DROP PROCEDURE GDZJ.BASECODEGEN_ZGW;

CREATE PROCEDURE GDZJ.BASECODEGEN_ZGW (
  IN ENPRNAME VARCHAR(256),
  IN ENPRADDR VARCHAR(256),
  IN ENPRLEADER VARCHAR(64),
  IN ENPRPHONE VARCHAR(64),
  OUT BASECODE VARCHAR(32)
)
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
l1: BEGIN
  DECLARE curBaseCode VARCHAR(16) DEFAULT '';
  DECLARE domain VARCHAR(16) DEFAULT 'GZ';      -- 主體碼
  DECLARE entityCode VARCHAR(16) DEFAULT '';    -- 本體碼
  DECLARE checkCode VARCHAR(16) DEFAULT '';     -- 校驗碼
  DECLARE rtnBaseCode VARCHAR(16) DEFAULT '';   -- 返回基準碼
  DECLARE newEntityCode VARCHAR(16) DEFAULT '';
  DECLARE V_COUNT INT DEFAULT 0;
  DECLARE V_INDEX INT DEFAULT 0;
  DECLARE cc INT DEFAULT 0;
  DECLARE c CHAR(1) DEFAULT '0';
  DECLARE charWeight INT DEFAULT 0;
  DECLARE entityCharNum INT DEFAULT 0;
  DECLARE sum INT DEFAULT 0;
  DECLARE sglChar CHAR(1) DEFAULT '0';

  -- 輸入驗證
  IF ENPRNAME IS NULL OR RTRIM(ENPRNAME) = '' THEN
    SET BASECODE = NULL;
    RETURN;
  END IF;

  -- 查詢企業名稱模糊比對最大基準碼
  SET curBaseCode = (SELECT MAX(enpr_basecode) FROM T_PUB_BASECODE 
                     WHERE enpr_name LIKE '%' || ENPRNAME || '%');

  IF curBaseCode IS NOT NULL AND RTRIM(curBaseCode) != '' THEN
    SET BASECODE = curBaseCode;
    RETURN;
  ELSE
    SET curBaseCode = (SELECT MAX(enpr_basecode) FROM T_PUB_BASECODE);
  END IF;

  -- 解析本體碼
  IF curBaseCode IS NULL THEN
    SET entityCode = '333333';
  ELSE
    SET entityCode = SUBSTR(curBaseCode, 3, 6);
  END IF;

  IF entityCode = 'ZZZZZZ' THEN
    SET BASECODE = NULL;
    RETURN;
  END IF;

  -- 本體碼遞增邏輯 (0-9, A-Z 循環)
  SET V_COUNT = LENGTH(entityCode);
  SET V_INDEX = V_COUNT;
  WHILE V_INDEX > 0 DO
    SET c = SUBSTR(entityCode, V_INDEX, 1);
    IF c = '9' THEN
      SET c = 'A';
    ELSEIF c = 'Z' THEN
      SET c = '0';
    ELSE
      SET c = CHR(DEC_TO_TEN(HEX(c), 16) + 1);  -- 呼叫自訂函數 16 進位轉 10 進位
    END IF;
    SET newEntityCode = c || newEntityCode;
    IF c < 'Z' AND c != '0' THEN
      SET newEntityCode = SUBSTR(entityCode, 1, V_INDEX - 1) || newEntityCode;
      SET V_INDEX = 0;
    END IF;
    SET V_INDEX = V_INDEX - 1;
  END WHILE;
  SET entityCode = newEntityCode;

  -- 校驗碼計算 (Mod 11)
  SET V_INDEX = 0;
  WHILE V_INDEX < 6 DO
    SET sglChar = SUBSTR(entityCode, V_INDEX + 1, 1);
    IF sglChar > '9' THEN
      SET entityCharNum = DEC_TO_TEN(HEX(sglChar), 16) - 55;
    ELSE
      SET entityCharNum = DEC_TO_TEN(HEX(sglChar), 16) - 48;
    END IF;
    -- 權重對應
    CASE V_INDEX
      WHEN 0 THEN SET charWeight = 9;
      WHEN 1 THEN SET charWeight = 10;
      WHEN 2 THEN SET charWeight = 5;
      WHEN 3 THEN SET charWeight = 8;
      WHEN 4 THEN SET charWeight = 4;
      WHEN 5 THEN SET charWeight = 2;
      ELSE SET charWeight = 0;
    END CASE;
    SET sum = sum + entityCharNum * charWeight;
    SET V_INDEX = V_INDEX + 1;
  END WHILE;

  SET cc = 11 - MOD(sum, 11);
  CASE cc
    WHEN 10 THEN SET checkCode = 'X';
    WHEN 11 THEN SET checkCode = '0';
    ELSE SET checkCode = CHAR(cc);
  END CASE;

  -- 組裝結果並寫入
  SET rtnBaseCode = domain || entityCode || checkCode;
  INSERT INTO T_PUB_BASECODE (enpr_name, charger, address, phone, enpr_basecode, gen_bc_time, give_bc_time)
  VALUES (ENPRNAME, ENPRLEADER, ENPRADDR, ENPRPHONE, rtnBaseCode, CURRENT DATE, CURRENT DATE);
  SET BASECODE = rtnBaseCode;
  RETURN;
END l1;

輔助函數：DEC_TO_TEN (16 進位轉 10 進位)

CREATE FUNCTION GDZJ.DEC_TO_TEN (
  pStr VARCHAR(25),
  p_from_base INTEGER
)
RETURNS INTEGER
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
  DECLARE l_num INT DEFAULT 0;
  DECLARE l_hex VARCHAR(16) DEFAULT '0123456789ABCDEF';
  DECLARE k INT DEFAULT 1;
  DECLARE kcount INT;
  DECLARE posNum INT DEFAULT 0;

  SET kcount = LENGTH(pStr);
  WHILE k <= kcount DO
    SET posNum = LOCATE(UPPER(SUBSTR(pStr, k, 1)), l_hex) - 1;
    SET l_num = l_num * p_from_base + posNum;
    SET k = k + 1;
  END WHILE;
  RETURN l_num;
END