Project 3: Printing multiple records 1. Project 3 is about printing multiple rec

Question

Project 3: Printing multiple records

1. Project 3 is about printing multiple records from DDI.LEDGER_VIEW using a PL/SQL LOOP

2. SPOOL your output to c:CSproject3spool.txt

3. Set up a record variable in the DECLARE section using %ROWTYPE

4. SELECT all the fields in DDI.LEDGER_VIEW where ROOMNUM is 107

5. Use DBMS__OUTPUT.PUT_LINE to print the values for each field for each record.

6. Compile and run the procedure.

Here is an example of printing variables for a single record from DDI.LEDGER_VIEW using PL/SQL:

DECLARE

REGID DDI.LEDGER_VIEW.regid%type;

REGDATE DDI.LEDGER_VIEW.regdate%type;

PATRONID DDI.LEDGER_VIEW.patronid%type;

ADULTCNT DDI.LEDGER_VIEW.adultcnt%type;

CHILDCNT DDI.LEDGER_VIEW.childcnt%type;

ROOMNUM DDI.LEDGER_VIEW.roomnum%type;

BEGIN

SELECT REGID, REGDATE, PATRONID, ADULTCNT, CHILDCNT, ROOMNUM FROM DDI.LEDGER_VIEW WHERE REGID = REGID;

dbms_output.put_line('REGID' || REGID);

dbms_output.put_line('REGDATE' || REGDATE);

dbms_output.put_line('PATRONID' || PATRONID);

dbms_output.put_line('ADULTCNT' || ADULTCNT);

dbms_output.put_line('CHILDCNT' || CHILDCNT);

dbms_output.put_line('ROOMNUM' || ROOMNUM);

EXCEPTION

WHEN no_data_found THEN dbms_output.put_line('Data not found');

WHEN others THEN dbms_output.put_line('Error');

END;

.

SELECT REGID, REGDATE, PATRONID, ADULTCNT, CHILDCNT, ROOMNUM FROM REGISTRATIONS WHERE REGID = 17;

here is the script for database tables script:

Explanation / Answer

DECLARE
CURSOR c1 IS
SELECT RegID, RegDate,PatronID, LastName, FirstName,
AdultCnt, ChildCnt, RoomNum,
RoomSize, RoomBedCnt, RoomRate FROM LEDGER_VIEW
WHERE RoomNum = 107;
employee_rec c1%ROWTYPE;
BEGIN

OPEN c1;
FETCH c1 INTO employee_rec;
dbms_output.put_line('REGID' || employee_rec.REGID);
dbms_output.put_line('REGDATE' || employee_rec.REGDATE);
dbms_output.put_line('PATRONID' || employee_rec.PATRONID);
DBMS_OUTPUT.PUT_LINE('LASTNAME: ' || employee_rec.LASTNAME);
DBMS_OUTPUT.PUT_LINE('FIRSTNAME: ' || employee_rec.FIRSTNAME);
dbms_output.put_line('ADULTCNT' || employee_rec.ADULTCNT);
dbms_output.put_line('CHILDCNT' || employee_rec.CHILDCNT);
dbms_output.put_line('ROOMNUM' || employee_rec.ROOMNUM);
dbms_output.put_line('ROOMSIZE' || employee_rec.ROOMSIZE);
dbms_output.put_line('ROOMBEDCNT' || employee_rec.ROOMBEDCNT);
dbms_output.put_line('ROOMRATE' || employee_rec.ROOMRATE);
EXCEPTION
WHEN no_data_found THEN dbms_output.put_line('Data not found');
WHEN others THEN dbms_output.put_line('Error');
END;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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