Oracle stored procedures with input and output parameters Return Affected rows SQL % ROWCOUNT

Oracle stored procedures with input and output parameters Return Affected rows SQL % ROWCOUNT [SQL] CREATE OR REPLACE PROCEDURE PROC_UPDATE_TEMP (P_DAYS IN NUMBER, -- execution days P_ROWS OUT VARCHAR2, -- returns the NUMBER of affected rows P_IS_SUCCESS out boolean) -- returns success or not AS V_NOW DATE: = TRUNC (SYSDATE); V_BEGIN_DATE DATE; V_SECOND NUMBER; I INT: = 0; BEGIN/* while I <P_DAYS loop update T_TEMP SET TEMP_STATUS = '01', TEMP_FLAG = '0' WHERE CREATE_TIME> = V_NOW-I AND CREATE_TIME <V_N OW + 1-I; COMMIT; I = I + 1; end loop; */for I IN 0 .. p_DAYS loop begin V_BEGIN_DATE: = SYSDATE; UPDATE T_TEMP SET TEMP_STATUS = '01', TEMP_FLAG = '0' WHERE CREATE_TIME> = V_NOW-I and CREATE_TIME <V_NOW + 1-I; P_IS_SUCCESS: = TRUE; V_SECOND: = (SYSDATE-V_BEGIN_DATE) * 24*60*60; -- returns the number of affected rows: SQL % ROWCOUNT P_ROWS: = SQL % ROWCOUNT | 'the row is updated, time consumed: '| TRUNC (V_SECOND, 2) | 'second'; DBMS_OUTPUT.PUT _ LINE (P_ROWS); COMMIT; exception when NO_DATA_FOUND then null; P_IS_SUCCESS: = FALSE; end loop; END PROC_UPDATE_TEMP; [SQL] -- call the Stored Procedure DECLARE -- Boolean parameters are translated from/to integers: -- 0/1/null <--> false/true/null P_IS_SUCCESS BOOLEAN; P_ROWS VARCHAR2 (200 ); BEGIN -- Call the procedure PKG_STL_UPDATE_BILL_DATA.PROC_STL_UPDATE_TRUNC (P_DAYS => 0, P_ROWS => P_ROWS, P_IS_SUCCESS => P _ IS_SUCCESS); -- Convert false/true/null to 0/1/null --: P_IS_SUCCESS: = SYS. DIUTIL. BOOL_TO_INT (P_IS_SUCCESS); dbms_output.put_line (P_ROWS); dbms_output.put_line (SYS. DIUTIL. BOOL_TO_INT (P_IS_SUCCESS); END; [SQL] -- SQL % ROWCOUNT usage BEGIN UPDATE T_JOB T SET T. JOB_ID = null where t. ID = '000000'; IF (SQL % ROWCOUNT! = 1) THEN RAISE_APPLICATION_ERROR (-20001, 'not successfully modified to record'); end if; END;