Question
I am getting an error when I work this program and I don't know why. Here are the DDI.LEDGER_VIEW codes: CREATE TABLE DDI.ROOMS ( RoomNum
I am getting an error when I work this program and I don't know why.
Here are the DDI.LEDGER_VIEW codes:
CREATE TABLE DDI.ROOMS ( RoomNum NUMBER NOT NULL, RoomSize VARCHAR2(6) NOT NULL, RoomBedCnt NUMBER NOT NULL, RoomRate NUMBER(18,2) NOT NULL, PRIMARY KEY (RoomNum))
CREATE TABLE DDI.PATRONS ( PatronID NUMBER NOT NULL, FirstName VARCHAR2(16) NOT NULL, LastName VARCHAR2(16) NOT NULL, PhoneNum VARCHAR2(12) Null, eMail VARCHAR2(100) Null, PRIMARY KEY (PatronID)) TABLESPACE USERS;
CREATE TABLE DDI.REGISTRATIONS ( RegID NUMBER NOT NULL, RegDate DATE NOT NULL, PatronID NUMBER NOT NULL, AdultCnt NUMBER Null, ChildCnt NUMBER Null, RoomNum NUMBER NOT NULL, RegNote VARCHAR2(100) Null, PRIMARY KEY (RegID), FOREIGN KEY (PatronID) REFERENCES DDI.PATRONS, FOREIGN KEY (RoomNum) REFERENCES DDI.ROOMS) TABLESPACE USERS;
CREATE VIEW DDI.LEDGER_VIEW AS SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName, REG.AdultCnt, REG.ChildCnt, REG.RoomNum, RM.RoomSize, RM.RoomBedCnt, RM.RoomRate FROM DDI.ROOMS RM, DDI.PATRONS P, DDI.REGISTRATIONS REG WHERE REG.PatronID = P.PatronID AND REG.RoomNum = RM.RoomNum ORDER BY REG.RegDate, REG.RoomNum;
The example that I use to do the program
SET ECHO ON SET SERVEROUT ON DECLARE emp_rec hr.employees%rowtype; crnt_dept hr.employees.department_id%type := 0; found_rows BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP -- DECLARE AND DEFINE FUNCTION TO CALC AVG SALARY BY DEPT FUNCTION avg_salary( dept_id IN NUMBER) RETURN NUMBER AS average NUMBER; BEGIN SELECT AVG(SALARY) INTO average FROM HR.EMPLOYEES WHERE department_id = dept_id; RETURN average; EXCEPTION WHEN others THEN RETURN NULL; END; BEGIN DBMS_OUTPUT.PUT_LINE('---------------------------------------'); DBMS_OUTPUT.PUT_LINE(' Average Salary by Department '); DBMS_OUTPUT.PUT_LINE('---------------------------------------'); -- LOOP CALLS FUNCTION AVG_SALARY ON EACH CHANGE IN DEPT ID/DISPLAYS AVG SALARY FOR emp_rec IN (SELECT * FROM hr.employees ORDER BY department_id) LOOP found_rows := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run IF crnt_dept != emp_rec.department_id THEN crnt_dept := emp_rec.department_id; DBMS_OUTPUT.PUT_LINE('Department '||crnt_dept||': '||TO_CHAR(avg_salary(crnt_dept),'$99999.99')); END IF; END LOOP; /* Used to throw exception for FOR LOOP when no rows are returned from SELECT query */ IF NOT found_rows THEN RAISE NO_DATA_FOUND; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found.'); END; /
The question to the assignment to do the program
For the assignment, create an average function similar to the example, and use that function to print to screen the average # of guests per room, ordered by room number. Your output will have three columns 1) room number, 2) avg guests per night, 3) # of nights booked. Make sure you round the average column to two places right of the decimal place, and base it on total guests, not just adults. Your output should be similar to the SQL statement below. Run this SQL statement from the PL/SQL prompt:
SELECT ROOMNUM, AVG(ADULTCNT + CHILDCNT) AS "AVG GUEST PER NIGHT", COUNT(*) AS "BOOKED NIGHTS" FROM DDI.LEDGER_VIEW GROUP BY ROOMNUM ORDER BY ROOMNUM;
You output should
1) Use a function 2) Be carefully formatted, something similar to the example, but also with column headers. 3) Code should use clear variable names. If you reuse code from other projects you shouldn't keep a variable named Salary and use it for room number without changing the name. 4) Include the exception handler call for the loop. Remember previously, if the for loop doesn't find any records it will not automatically call the no_data_found exception handler. You can test it by asking for the department_id at run time and choosing one that doesn't exist. 5) Include comments, header and code
My code after the program:
SET SERVEROUT ON
DECLARE
ROOM_REC DDI.LEDGER_VIEW%ROWTYPE;
CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE : 0 ;
NIGHT_BOOK NUMBER;
FOUND_ROWS BOOLEAN: = FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP
--DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOM
FUNCTION AVG_GUESTS(
ROOMNUM IN NUMBER)
AS AVERAGE NUMBER; BEGIN
SELECT AVG(ADULTCNT+CHILDCNT)
INTO AVERAGE
FROM DDI.LEDGER_VIEW
WHERE ROOMNUM = ROOMNUM;
RETURN AVERAGE;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ( '--------------------------------------- ');
DBMS_OUTPUT.PUT_LINE (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGHTS ');
DBMS_OUTPUT.PUT_LINE ( '---------------------------------------' );
--LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROOM NUMBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS
FOR ROOM_REC IN
(SELECT *
FROM DDI.LEDGER_VIEW
ORDER BY ROOMNUM)
LOOP
FOUND_ROWS : = TRUE; --IF DATA EXITS, SETS VARIABLE TO TRUE, SO IF STATEMENT DOESN'T RUN FOUND_ROWS BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP
IF CRNT_ROOM != ROOM_REC.ROOMNUM THEN
CRNT_ROOM : = ROOM_REC.ROOMNUM;
SELECT COUNT(*) INTO NIGHTS_BOOK FROM DDI.LEDGER_VIEW WHERE ROOMNUM = CRNT_ROOM;
DBMS_OUTPUT.PUT_LINE (DDI.LEDGER_VIEW || CRNT_ROOM|| ': '||TO_CHAR(AVG_GUESTS(CRNT_ROOM), 9999999')); END IF;
END LOOP;
/* USED TO THROW EXCEPTION for FOR LOOP WHEN NO ROWS ARE RETURNED FROM SELECT QUERY */
IF NOT FOUND_ROWS THEN
RAISE NO_DATA_FOUND;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');
END;
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started