SQL Database Programming Write a program using a return statement and invoke a p

Question

SQL Database Programming

Write a program using a return statement and invoke a procedure to print

SQL> DECLARE
2 Room_rec DDI.LEDGER_VIEW%ROWTYPE;
3 Last_room DDI.LEDGER_VIEW.ROOMNUM%TYPE := 0;
4 Sub_Total number := 0;
5 Grand_Total number := 0;
6  
7 PROCEDURE HEAD_OF_FORM
8 IS
9 BEGIN
10 DBMS_OUTPUT.NEW_LINE;
11 DBMS_OUTPUT.PUT_LINE(' Room Rates from DDI.LEDGER_VIEW by Room Number ');
12 DBMS_OUTPUT.PUT_LINE('RmNum ID RegDate Last First Adults Children Size Beds Rate');
13 DBMS_OUTPUT.PUT_LINE('---- --- --------- ------------ ------------ -- -- -------- -- ----');
14 END;
15  
16 PROCEDURE FORM_BREAK(v_subtot IN OUT NUMBER)
17 IS
18 BEGIN
19 DBMS_OUTPUT.PUT_LINE(' --------');
20 DBMS_OUTPUT.PUT_LINE('Subtotal ' ||
21 LPAD(TO_CHAR(Sub_Total, '$99,999'),8));
22 DBMS_OUTPUT.NEW_LINE;
23 v_subtot := 0;
24 END;
25 PROCEDURE END_OF_FORM (v_gtot IN NUMBER)
26 IS
27 BEGIN
28 DBMS_OUTPUT.PUT_LINE(' --------');
29 DBMS_OUTPUT.PUT_LINE('Grand Total ' ||
30 LPAD(TO_CHAR(v_gtot, '$99,999'),8));
31 END;
32 BEGIN
33 FOR Room_rec IN
34 (SELECT *
35 FROM DDI.LEDGER_VIEW
36 ORDER BY ROOMNUM, REGDATE)
37 LOOP
38 IF (Last_room = 0) THEN
39 HEAD_OF_FORM;
40 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 3));
41 Last_room := Room_rec.ROOMNUM;
42 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
43 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
44 ELSIF (Room_rec.Roomnum <> Last_room) THEN
45 FORM_BREAK(Sub_Total);
46 HEAD_OF_FORM;
47 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 3));
48 Last_room := Room_rec.ROOMNUM;
49 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
50 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
51 ELSE
52 DBMS_OUTPUT.PUT(' ');
53 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
54 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
55 END IF;
56 DBMS_OUTPUT.PUT(' ');
57 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM, '999'), 3));
58 DBMS_OUTPUT.PUT(' ');
59 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.REGID, '999'), 3));
60 DBMS_OUTPUT.PUT(' ');
61 DBMS_OUTPUT.PUT(TO_CHAR(Room_rec.REGDATE, 'MM/DD/YY'));
62 DBMS_OUTPUT.PUT(' ');
63 DBMS_OUTPUT.PUT(RPAD(Room_rec.LASTNAME,12));
64 DBMS_OUTPUT.PUT(' ');
65 DBMS_OUTPUT.PUT(RPAD(Room_rec.FIRSTNAME,12));
66 DBMS_OUTPUT.PUT(' ');
67 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ADULTCNT, '99'), 3));
68 DBMS_OUTPUT.PUT(' ');
69 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.CHILDCNT, '99'), 3));
70 DBMS_OUTPUT.PUT(' ');
71 DBMS_OUTPUT.PUT(RPAD(Room_rec.ROOMSIZE,8));
72 DBMS_OUTPUT.PUT(' ');
73 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMBEDCNT, '99'), 3));
74 DBMS_OUTPUT.PUT(' ');
75 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMRATE, '$9,999'),6));
76 DBMS_OUTPUT.NEW_LINE;
77 END LOOP;
78 FORM_BREAK(Sub_Total);
79 END_OF_FORM(Grand_Total);
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 DBMS_OUTPUT.PUT_LINE('No data found.');
83  
84 END;
85 /

Explanation / Answer

The below program when complied will create a standalone store procedure and will call all the procedures mentioned above and accordingly print the values

Prior to compiling the below procedure, all the other three procedures mentioned above should be complied.

------------------------------------------------Code Starts Below-------------------------------------

Procedure Master_Program IS --procedure name

Room_rec DDI.LEDGER_VIEW%ROWTYPE; --decleration section
Last_room DDI.LEDGER_VIEW.ROOMNUM%TYPE := 0;
Sub_Total number := 0;
Grand_Total number := 0;

BEGIN ---main body section
FOR Room_rec IN
(SELECT *
FROM DDI.LEDGER_VIEW
ORDER BY ROOMNUM, REGDATE)
LOOP
IF (Last_room = 0) THEN
HEAD_OF_FORM;
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 3));
Last_room := Room_rec.ROOMNUM;
Sub_Total := Sub_Total + Room_rec.ROOMRATE;
Grand_Total := Grand_Total + Room_rec.ROOMRATE;
ELSIF (Room_rec.Roomnum <> Last_room) THEN
FORM_BREAK(Sub_Total);
HEAD_OF_FORM;
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 3));
Last_room := Room_rec.ROOMNUM;
Sub_Total := Sub_Total + Room_rec.ROOMRATE;
Grand_Total := Grand_Total + Room_rec.ROOMRATE;
ELSE
DBMS_OUTPUT.PUT(' ');
Sub_Total := Sub_Total + Room_rec.ROOMRATE;
Grand_Total := Grand_Total + Room_rec.ROOMRATE;
END IF;
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM, '999'), 3));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.REGID, '999'), 3));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(TO_CHAR(Room_rec.REGDATE, 'MM/DD/YY'));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(RPAD(Room_rec.LASTNAME,12));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(RPAD(Room_rec.FIRSTNAME,12));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ADULTCNT, '99'), 3));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.CHILDCNT, '99'), 3));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(RPAD(Room_rec.ROOMSIZE,8));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMBEDCNT, '99'), 3));
DBMS_OUTPUT.PUT(' ');
DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMRATE, '$9,999'),6));
DBMS_OUTPUT.NEW_LINE;
END LOOP;
FORM_BREAK(Sub_Total);
END_OF_FORM(Grand_Total);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
RETURN;
END; ---end of the procedure

---------------------------------------------Code Ends--------------------------------------------

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.