Using MySQL Project 4 is about printing a formatted report from DDI.LEDGER_VIEW

Question

Using MySQL

Project 4 is about printing a formatted report from DDI.LEDGER_VIEW using Head of Forms, Form Breaks, and an End of Form.

1.      SPOOL your output to c:CS4210project4spool.txt

2.      DECLARE a record variable, a variable to keep track of a change in RoomNum, and two accumulators

3.      Add procedures to the DECLARE block to print a Head of Form, subtotals, and a grand total

4.      In the BEGIN block add a select statement to read all the ledger records in DDI.LEDGER_VIEW ordered by RoomNum and RegDate

5.      Add a LOOP

6.      Use DBMS__OUTPUT.PUT statements to print all the values for each record on a single line. End with a DBMS_OUTPUT.NEW_LINE

7.      After the LOOP be sure to print the final subtotal and the grand total

8.      Compile and run the procedure

Explanation / Answer

- Please consider formatting your output using the below commands before executing the PL/SQL code:

set lines 200 pages 300

for formatting

- For spooling, you need to do the fllowing:

SQL > spool project5spool.txt

SQL > DECLARE ...................

SQL > spool off;

By doing this, you will get a text file with name project4spool.txt in your O.S pwd(present working directory).

Code:

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
26 PROCEDURE END_OF_FORM (v_gtot IN NUMBER)
27 IS
28 BEGIN
29 DBMS_OUTPUT.PUT_LINE(' --------');
30 DBMS_OUTPUT.PUT_LINE('Grand Total ' ||
31 LPAD(TO_CHAR(v_gtot, '$99,999'),8));
32 END;
33
34 BEGIN
35 FOR Room_rec IN
36 (SELECT *
37 FROM DDI.LEDGER_VIEW
38 ORDER BY ROOMNUM, REGDATE)
39 LOOP
40 IF (Last_room = 0) THEN
41 HEAD_OF_FORM;
42 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 3));
43 Last_room := Room_rec.ROOMNUM;
44 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
45 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
46 ELSIF (Room_rec.Roomnum <> Last_room) THEN
47 FORM_BREAK(Sub_Total);
48 HEAD_OF_FORM;
49 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 3));
50 Last_room := Room_rec.ROOMNUM;
51 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
52 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
53 ELSE
54 DBMS_OUTPUT.PUT(' ');
55 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
56 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
57 END IF;
58 DBMS_OUTPUT.PUT(' ');
59 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM, '999'), 3));
60 DBMS_OUTPUT.PUT(' ');
61 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.REGID, '999'), 3));
62 DBMS_OUTPUT.PUT(' ');
63 DBMS_OUTPUT.PUT(TO_CHAR(Room_rec.REGDATE, 'MM/DD/YY'));
64 DBMS_OUTPUT.PUT(' ');
65 DBMS_OUTPUT.PUT(RPAD(Room_rec.LASTNAME,12));
66 DBMS_OUTPUT.PUT(' ');
67 DBMS_OUTPUT.PUT(RPAD(Room_rec.FIRSTNAME,12));
68 DBMS_OUTPUT.PUT(' ');
69 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ADULTCNT, '99'), 3));
70 DBMS_OUTPUT.PUT(' ');
71 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.CHILDCNT, '99'), 3));
72 DBMS_OUTPUT.PUT(' ');
73 DBMS_OUTPUT.PUT(RPAD(Room_rec.ROOMSIZE,8));
74 DBMS_OUTPUT.PUT(' ');
75 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMBEDCNT, '99'), 3));
76 DBMS_OUTPUT.PUT(' ');
77 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMRATE, '$9,999'),6));
78 DBMS_OUTPUT.NEW_LINE;
79 END LOOP;
80 FORM_BREAK(Sub_Total);
81 END_OF_FORM(Grand_Total);
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 DBMS_OUTPUT.PUT_LINE('No data found.');
85
86 END;

Sample Output:

Room Rates from DDI.LEDGER_VIEW by Room Number

RmNum ID RegDate Last First Adults Children Size Beds Ra
---- --- --------- ------------ ------------ -- -- -------- -- --
10 10 06/01/15 Marino Chip 2 0 King 1 $6
10 1 06/02/15 Parks Eric 2 1 King 1 $6   
10 3 06/05/15 Downs Marsha 2 0 King 1 $6   
10 3 06/06/15 Sailors Mike 1 0 King 1 $6   
10 4 06/07/15 Romez Richardo 1 0 King 1 $6   
------
Subtotal $30

Feel free to reach out if you have any doubts.
Rate if the answer was helpful.
Thanks

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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