SQL Database Programming Project 5-Printing Multiple Records II SPOOL your outpu
ID: 3593120 • 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
SQL> SELECT *
2
SQL> INTO #temp
SP2-0734: unknown command beginning "INTO ..." - rest of line ignored.
SQL>
SQL> FROM DDI.LEDGER_VIEW
SP2-0734: unknown command beginning "FROM ..." - rest of line ignored.
SQL> DECLARE @record varchar(100)
2
3 DECLARE @subTotal int
4
5 DECLARE @grandTotal int
6
7 DECLARE @recordID int
8 CREATE OR REPLACE PROCEDURE printRecords
9
10 AS
11
12 BEGIN
13 SELECT *
14
15 FROM DDI.LEDGER_VIEW
16
17 ORDER BY RoomNum , RegDate
18 FOR x IN (SELECT * FROM #temp)
19
20 BEGIN
21 SELECT top 1 @recordID = recordID
22
23 FROM #temp
24
25 ORDER BY recordID asc
26 DELETE #temp
27
28 where recordID = @ recordID
29
30 DBMS_OUTPUT.NEW_LINE(‘ ’)
31
32 END;
33 .
SQL> DBMS_OUTPUT.PUT (‘Sub total :’ || @subTotal || ‘Grand total :’ || @grandTotal)
SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL>
SQL> END printRecords;
SP2-0734: unknown command beginning "END pri..." - rest of line ignored.
SQL>
Explanation / Answer
SQL> SELECT *
2
SQL> INTO #temp
SP2-0734: unknown command beginning "INTO ..." - rest of line ignored.
SQL>
SQL> FROM DDI.LEDGER_VIEW
SP2-0734: unknown command beginning "FROM ..." - rest of line ignored.
SQL> DECLARE @record varchar(100)
2
3 DECLARE @subTotal int
4
5 DECLARE @grandTotal int
6
7 DECLARE @recordID int
8 CREATE OR REPLACE PROCEDURE printRecords
9
10 AS
11
12 BEGIN
13 SELECT *
14
15 FROM DDI.LEDGER_VIEW
16
17 ORDER BY RoomNum , RegDate
18 FOR x IN (SELECT * FROM #temp)
19
20 BEGIN
21 SELECT top 1 @recordID = recordID
22
23 FROM #temp
24
25 ORDER BY recordID asc
26 DELETE #temp
27
28 where recordID = @ recordID
29
30 DBMS_OUTPUT.NEW_LINE(‘ ’)
31
32 END;
33 .
SQL> DBMS_OUTPUT.PUT (‘Sub total :’ || @subTotal || ‘Grand total :’ || @grandTotal)
SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored.
SQL>
SQL> END printRecords;
SP2-0734: unknown command beginning "END pri..." - rest of line ignored.
SQL>
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.