The CREATE OR REPLACE PROCEDURE APP_MANAGER. PB_EQUIP_SUBMIT (Y IN VARCHAR2, M IN VARCHAR2) AS

CURSOR CS IS
The SELECT FACILITY_NO
, DEPRECIATION_THIS_MONTH
, SERVICE_LIFE
, UNUSED_LIFE
, ACCUMULATIVE_DEPRECIATION
, CURRENT_VALUE
The FROM APP_MANAGER. SB_DEPRECIATION_2019
WHERE SEQ_ID LIKE '%' | | Y | | M;


The BEGIN
The FOR LOOP IN CS C
The BEGIN

The UPDATE APP_MANAGER. ASSET_EQUIP_FACILITY
The SET DEPRECIATION_THIS_MONTH=C.D EPRECIATION_THIS_MONTH
, c.s. ERVICE_LIFE SERVICE_LIFE=
, ACCUMULATIVE_DEPRECIATION=c.a. CCUMULATIVE_DEPRECIATION
, CURRENT_VALUE=https://bbs.csdn.net/topics/C.CURRENT_VALUE
WHERE FACILITY_NO=C.F ACILITY_NO;

END;
END LOOP;
END PB_EQUIP_SUBMIT;

The procedure can be run now,

Parameter Y is, M is a month, I wrote above a 2019 is dead, I'd like to replace 2019 Y, how to do?? The complete code,

CodePudding user response:

Using dynamic cursor


The CREATE OR REPLACE PROCEDURE APP_MANAGER. PB_EQUIP_SUBMIT (Y IN VARCHAR2,
M IN VARCHAR2,
P_Y VARCHAR2) AS IN
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
The SELECT FACILITY_NO,
DEPRECIATION_THIS_MONTH,
SERVICE_LIFE,
UNUSED_LIFE,
ACCUMULATIVE_DEPRECIATION,
CURRENT_VALUE
The FROM APP_MANAGER. SB_DEPRECIATION_2019
WHERE SEQ_ID LIKE '%' | | Y | | M;

The BEGIN
The OPEN V_CUR_C FOR 'SELECT FACILITY_NO,
DEPRECIATION_THIS_MONTH,
SERVICE_LIFE,
UNUSED_LIFE,
ACCUMULATIVE_DEPRECIATION,
CURRENT_VALUE
The FROM APP_MANAGER. SB_DEPRECIATION_ '| | P_Y | |' WHERE SEQ_ID LIKE '%' | | '| | Y | |' | | '| | M;
The 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

The 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=https://bbs.csdn.net/topics/V_CURRENT_VALUE
WHERE FACILITY_NO=V_FACILITY_NO;

The 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;
The CLOSE V_CUR_C;
The EXCEPTION
The WHEN OTHERS THEN
The CLOSE V_CUR_C;
RAISE;
END PB_EQUIP_SUBMIT;

CodePudding user response:

Also live above the 2019,

CodePudding user response:

You this because the name of the table to use parameters, so have to dynamic SQL

CodePudding user response:

Top up, big hands to solve the problem,

CodePudding user response:

2 l haven't solved?

CodePudding user response:

reference 5 floor nayi_224 reply:

2 l haven't solved?

without a lot of 2019 didn't write

CodePudding user response:

refer to 6th floor Sauron_ Long Xiaoben response:

Quote: refer to the fifth floor nayi_224 reply:

2 l haven't solved?

no so many 2019 didn't write live

The 2019 is only used to define data types, the name of the table has been changed to dynamically access,

CodePudding user response:

Reference string command execution mode, form the joining together of SQL statements:
https://www.cnblogs.com/senyier/p/7801350.html
======================================
Under this article refer to
=========================