Sunday, 14 February 2016

Write a cursor to display the Electricity consumption report in following format

Write a cursor to display the Electricity consumption report in following format

DECLARE
CONS NUMBER;
TOT_CONS NUMBER;
CURSOR C1 IS SELECT B.CUST_NO, A.CUST_NAME, B.CURR_READ, B.PREV_READ, A.CITY FROM CUSTOMER_MST A, CUSTOMER_DTL B WHERE A.CUST_NO=B.CUST_NO;
CUST_N CUSTOMER_MST.CUST_NAME%TYPE;
CUST_C CUSTOMER_MST.CITY%TYPE;
CURR_R CUSTOMER_DTL.CURR_READ%TYPE;
PREV_R CUSTOMER_DTL.PREV_READ%TYPE;
C_NO CUSTOMER_DTL.CUST_NO%TYPE;
BEGIN
OPEN C1;
TOT_CONS:=0;
DBMS_OUTPUT.PUT_LINE('Electricity  Consumption');
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('customer no       Name        Comnsumption    City');
DBMS_OUTPUT.PUT_LINE('==================================================');
LOOP
FETCH C1 INTO C_NO, CUST_N, CURR_R, PREV_R, CUST_C;
CONS:=CURR_R-PREV_R;
TOT_CONS:=TOT_CONS+CONS;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_NO||' '||CUST_N||' '||CONS||' '||CUST_C);
END LOOP;
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('TOTAL CONSUMPTION: '||TOT_CONS);
CLOSE C1;
END;
/

No comments:

Post a Comment