OraFAQ Forum: SQL & PL/SQL » Written Assignment (+ example code)

Home » SQL & PL/SQL » SQL & PL/SQL » Written Assignment (+ example code)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Written Assignment (+ example code) [message #672553]

Wed, 17 October 2018 15:56

cwilliam912
Messages: 30
Registered: February 2009

Member

Written Assignment (Write a Program Using a Return Statement)

I am having an hard time with this assignment. I keep getting the same error. I went over it more than 50 times so can someone please out if they can please.

This is the example the professor give up to use:

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;
/

This is what he want use to go by:
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

Here is mu coding with the assignment with the error in it:

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
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;
ERROR at line 3:
ORA-06550: line 3, column 39:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= ( ; not null range default character