CREATE OR REPLACE PROCEDURE APP_MANAGER.PB_EQUIP_SUBMIT(Y IN VARCHAR2,M IN VARCHAR2) AS
CURSOR CS IS
SELECT FACILITY_NO
,DEPRECIATION_THIS_MONTH
,SERVICE_LIFE
,UNUSED_LIFE
,ACCUMULATIVE_DEPRECIATION
,CURRENT_VALUE
FROM APP_MANAGER.SB_DEPRECIATION_2019
WHERE SEQ_ID LIKE '%'||Y||M;
BEGIN
FOR C IN CS LOOP
BEGIN
UPDATE APP_MANAGER.ASSET_EQUIP_FACILITY
SET DEPRECIATION_THIS_MONTH = C.DEPRECIATION_THIS_MONTH
,SERVICE_LIFE = C.SERVICE_LIFE
,ACCUMULATIVE_DEPRECIATION = C.ACCUMULATIVE_DEPRECIATION
,CURRENT_VALUE = C.CURRENT_VALUE
WHERE FACILITY_NO = C.FACILITY_NO;
END;
END LOOP;
END PB_EQUIP_SUBMIT;
現在這個存盤程序是可以運行的。
引數Y是年，M是月，我上面有個2019是寫死的，我想把2019替換成Y，如何實作呢？？求完整代碼。
uj5u.com熱心網友回復：
使用動態游標CREATE OR REPLACE PROCEDURE APP_MANAGER.PB_EQUIP_SUBMIT(Y IN VARCHAR2,
M IN VARCHAR2,
P_Y IN VARCHAR2) AS
TYPE REF_CURSOR_TYPE IS REF CURSOR;
V_CUR_C REF_CURSOR_TYPE;
V_FACILITY_NO APP_MANAGER.SB_DEPRECIATION_2019.FACILITY_NO%TYPE;
V_DEPRECIATION_THIS_MONTH APP_MANAGER.SB_DEPRECIATION_2019.DEPRECIATION_THIS_MONTH%TYPE;
V_SERVICE_LIFE APP_MANAGER.SB_DEPRECIATION_2019.SERVICE_LIFE%TYPE;
V_UNUSED_LIFE APP_MANAGER.SB_DEPRECIATION_2019.UNUSED_LIFE%TYPE;
V_ACCUMULATIVE_DEPRECIATION APP_MANAGER.SB_DEPRECIATION_2019.ACCUMULATIVE_DEPRECIATION%TYPE;
V_CURRENT_VALUE APP_MANAGER.SB_DEPRECIATION_2019.CURRENT_VALUE%TYPE;
CURSOR CS IS
SELECT FACILITY_NO,
DEPRECIATION_THIS_MONTH,
SERVICE_LIFE,
UNUSED_LIFE,
ACCUMULATIVE_DEPRECIATION,
CURRENT_VALUE
FROM APP_MANAGER.SB_DEPRECIATION_2019
WHERE SEQ_ID LIKE '%' || Y || M;
BEGIN
OPEN V_CUR_C FOR 'SELECT FACILITY_NO,
DEPRECIATION_THIS_MONTH,
SERVICE_LIFE,
UNUSED_LIFE,
ACCUMULATIVE_DEPRECIATION,
CURRENT_VALUE
FROM APP_MANAGER.SB_DEPRECIATION_' || P_Y || 'WHERE SEQ_ID LIKE ''%''||' || Y || '||' || M;
FETCH V_CUR_C
INTO V_FACILITY_NO,
V_DEPRECIATION_THIS_MONTH,
V_SERVICE_LIFE,
V_UNUSED_LIFE,
V_ACCUMULATIVE_DEPRECIATION,
V_CURRENT_VALUE;
WHILE V_CUR_C%FOUND LOOP
UPDATE APP_MANAGER.ASSET_EQUIP_FACILITY
SET DEPRECIATION_THIS_MONTH = V_DEPRECIATION_THIS_MONTH,
SERVICE_LIFE = V_SERVICE_LIFE,
ACCUMULATIVE_DEPRECIATION = V_ACCUMULATIVE_DEPRECIATION,
CURRENT_VALUE = V_CURRENT_VALUE
WHERE FACILITY_NO = V_FACILITY_NO;
FETCH V_CUR_C
INTO V_FACILITY_NO,
V_DEPRECIATION_THIS_MONTH,
V_SERVICE_LIFE,
V_UNUSED_LIFE,
V_ACCUMULATIVE_DEPRECIATION,
V_CURRENT_VALUE;
END LOOP;
CLOSE V_CUR_C;
EXCEPTION
WHEN OTHERS THEN
CLOSE V_CUR_C;
RAISE;
END PB_EQUIP_SUBMIT;
uj5u.com熱心網友回復：
上面的那些2019也是活的。uj5u.com熱心網友回復：
你這個因為表名也要用引數，所以得用動態SQLuj5u.com熱心網友回復：
頂起來，來大手解決問題。uj5u.com熱心網友回復：
2L不是已經解決了？uj5u.com熱心網友回復：
沒有啊 好多2019沒寫活啊uj5u.com熱心網友回復：
那些2019只是用來定義資料型別的，表名已經改為動態獲取了。
uj5u.com熱心網友回復：
參考使用字串的命令執行方式，形成拼接的sql陳述句：https://www.cnblogs.com/senyier/p/7801350.html
======================================
上述這篇文章可參考下
=========================
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/52475.html
標籤：開發