使用動態 SQL 拼接字符串，聲明游標 cs_py 遍歷表中 fieldname2 為空的行，調用另一過程 sp_Public_PyWbm 計算拼音縮寫並更新回表。
| 特性 | SQL Server | Oracle |
|---|---|---|
| 變量聲明 | DECLARE @var TYPE | var TYPE; (在 DECLARE 塊中) |
| 動態 SQL | EXEC('sql') | EXECUTE IMMEDIATE 'sql' USING ... |
| 游標 | DECLARE cur CURSOR FOR ... | CURSOR cur IS SELECT ... (隱式/顯式) |
| 循環 | WHILE @@FETCH_STATUS=0 | LOOP FETCH ... EXIT WHEN cur%NOTFOUND; END LOOP; |
CREATE OR REPLACE PROCEDURE sp_Public_PyWbmGet(
p_tablename VARCHAR2,
p_fieldname1 VARCHAR2,
p_fieldname2 VARCHAR2,
p_pybz CHAR DEFAULT '0'
) AS
v_sql VARCHAR2(1000);
v_name VARCHAR2(255);
v_shortcut VARCHAR2(255);
CURSOR c_data IS
SELECT p_fieldname1, p_fieldname2
FROM p_tablename
WHERE p_fieldname2 IS NULL
FOR UPDATE OF p_fieldname2;
BEGIN
FOR rec IN c_data LOOP
-- 調用計算拼音過程 (需單獨遷移)
sp_Public_PyWbm(rec.p_fieldname1, p_pybz, v_shortcut);
UPDATE p_tablename
SET p_fieldname2 = v_shortcut
WHERE CURRENT OF c_data;
END LOOP;
END;
/
OPEN/FETCH，建議使用靜態游標 FOR rec IN ... LOOP 或 DBMS_SQL 包處理極度動態場景。EXECUTE IMMEDIATE) 拼接，注意防注入。
