the hw is i have account table in which one of the column is acct_bal_cny need to be updated
You are to provide a stored procedure that will increase the balance of an account in your ACCOUNT
table based on a rating of 1, 2, or 3:
A rating of 1 will warrant a 2% increase balance.
A rating of 2 will warrant a 1% increase balance.
If not a 1 or 2, increase the balance by 0.5%.
You will use the CASE SQL procedure language construct to accomplish this. The new procedure should
return the old balance and the updated balance...
is there any error in below store produre and cobal code
CREATE PROCEDURE ZUSER02.UPDBAL (IN ACCTID CHAR(10),IN RATE SMALLINT,
OUT OLD_BAL DEC(10,2),OUT UPD_BAL DEC(10,2))
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR SQLSTATE '70444'
SELECT ACCT_BAL_CNY
INTO OLD_BAL
FROM ACCOUNT
WHERE ACCT_ID=ACCTID;
CASE RATE
WHEN 1 THEN
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.02)
WHERE ACCT_ID=ACCTID;
WHEN 2 THEN
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.01)
WHERE ACCT_ID=ACCTID;
ELSE
UPDATE ACCOUNT
SET ACCT_BAL_CNY=OLD_BAL+(OLD_BAL*0.005)
WHERE ACCT_ID=ACCTID;
END CASE;
SELECT ACCT_BAL_CNY
INTO UPD_BAL
FROM ACCOUNT
WHERE ACCT_ID=ACCTID;
END P1
cobal code
*---------------------------------------------------------------*
* IBM SVL MINI BANKING SYSTEM *
*---------------------------------------------------------------*
*---------------------------------------------------------------*
* PROGRAM NAME : CALL A STORED PROCEDURE UPDBAL *
* PROGRAM ID : CALLSP *
* INTERFACE AREA : N/A *
* DATABASE USAGE : BINKDB *
* TABLE USAGE : ACCOUNT *
* FILE USAGE : N/A *
* FUNCTION : CALL A STORED PROCEDURE UDPBAL *
*---------------------------------------------------------------*
* PREPARED BY : JINSONG FENG *
* DATE WRITTEN : APR. 2009 *
* REVIEWED BY : JINSONG FENG *
* DATE MODIFIED : APR. 2009 *
*---------------------------------------------------------------*
*---------------------------------------------------------------*
* I D E N T I F I C A T I O N D I V I S I O N *
*---------------------------------------------------------------*
IDENTIFICATION DIVISION.
PROGRAM-ID. CALLSP.
AUTHOR. SVL.
*---------------------------------------------------------------*
* E N V I R O N M E N T D I V I S I O N *
*---------------------------------------------------------------*
ENVIRONMENT DIVISION.
*---------------------------------------------------------------*
* D A T A D I V I S I O N *
*---------------------------------------------------------------*
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-ACCT-ID PIC X(10) VALUE '0000000001'.
01 WS-RATE PIC S9 COMP VALUE +2.
01 WS-OLD-BAL PIC S9(10)V9(2) USAGE COMP-3.
01 WS-NEW-BAL PIC S9(10)V9(2) USAGE COMP-3.
*---------------------------------------------------------------*
* SQL INCLUDE FOR SQLDA / SQLCA *
*---------------------------------------------------------------*
EXEC SQL INCLUDE SQLDA END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE ACCOUNT END-EXEC.
LINKAGE SECTION.
*---------------------------------------------------------------*
* P R O C E D U R E D I V I S I O N *
*---------------------------------------------------------------*
PROCEDURE DIVISION.
MAIN SECTION.
DISPLAY "START TO CALL STORED PROCEDURE."
EXEC SQL
CALL ZUSER02.UPDBAL(:WS-ACCT-ID
,:WS-RATE
,:WS-OLD-BAL
,:WS-NEW-BAL
)
END-EXEC
DISPLAY "FOR ACCOUNT : ", WS-ACCT-ID
DISPLAY "THE OLD BALANCE IS : ", WS-OLD-BAL
DISPLAY "THE NEW BALANCE IS : ", WS-NEW-BAL
STOP RUN.