I have table ‘Studies’ (with columns: student_id, name, surname, course_name, mark) and I have a task to write a PL/SQL program, where will be WITH word + FUNCTION word + PROCEDURE word.I decided to make such a program: the function will calculate the average mark for some course (input parameter) and the procedure will display information about students whose mark in this course is higher than the average. I managed to create a function that returns the average score,
create or replace FUNCTION average_mark(co_name IN VARCHAR2) RETURN REAL IS iter NUMBER := 0; aver NUMBER := 0; CURSOR c1 IS SELECT mark FROM studies WHERE course_name = co_name; BEGIN FOR student IN c1 LOOP iter := iter + 1; aver := aver + student.mark; END LOOP; RETURN ROUND((aver/iter),2); END above_average_mark;
and procedure which displays information about a student whose mark in the course is more than a certain one, how now to connect the procedure and the function and the WITH word?
CREATE OR REPLACE PROCEDURE above(co_name IN VARCHAR2) IS CURSOR c2 IS SELECT * FROM studies WHERE course_name = co_name; BEGIN FOR student IN c2 LOOP IF (student.mark > 4) THEN DBMS_OUTPUT.PUT_LINE('name: ' || student.student_name || ', mark: ' || student.mark); END IF; END LOOP; END;
i need something like this: