SQL Database Programming Project 5-Printing Multiple Records II SPOOL your outpu
ID: 3591902 • Letter: S
Question
SQL Database Programming
Project 5-Printing Multiple Records II
SPOOL your output to c:csproject7spool.txt
1. DECLARE a record variable, a variable to keep track of a change in RoomNum, and two accumulators
2. Add procedures to the Print multiple records
3. In the BEGIN block add a select statement to read all the ledger records in DDI.LEDGER_VIEW ordered by RoomNum and RegDate
4. Add a LOOP
5. Use DBMS__OUTPUT.PUT statements to print all the values for each record on a single line. End with a DBMS_OUTPUT.NEW_LINE
6. After the LOOP be sure to print the final subtotal and the grand total
7. Compile and run the procedure
Explanation / Answer
SELECT *
INTO #temp
FROM DDI.LEDGER_VIEW
// 1.
DECLARE @record varchar(100)
DECLARE @subTotal int
DECLARE @grandTotal int
DECLARE @recordID int
// 2.
CREATE OR REPLACE PROCEDURE printRecords
AS
BEGIN
// 3.
SELECT *
FROM DDI.LEDGER_VIEW
ORDER BY RoomNum , RegDate
// 4.
FOR x IN (SELECT * FROM #temp)
BEGIN
// 5.
// to fetch single record
SELECT top 1 @recordID = recordID
FROM #temp
ORDER BY recordID asc
/* print records here */
DELETE #temp
where recordID = @ recordID
DBMS_OUTPUT.NEW_LINE(‘ ’)
END
// 6.
DBMS_OUTPUT.PUT (‘Sub total :’ || @subTotal || ‘Grand total :’ || @grandTotal)
END printRecords;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.