Can anybody explain why this does not work? For marketing research, the company
ID: 3714075 • Letter: C
Question
Can anybody explain why this does not work?
For marketing research, the company has decided they would like a function which will return the number of movies a member has rented in the past by inputting a member_id. Create this function and test it with both member ids 10 & 13.
Part 1: Creating a Function
SQL:
CREATE OR REPLACE FUNCTION rental_history_sf/* creating a function*/
(p_mem_id IN mm_rental.member_id%TYPE) /*creating variables */
RETURN NUMBER /* declares value type to be returned */
AS
lv_count NUMBER := 0;
lv_rental_count NUMBER := 0;
lv_mem_id NUMBER := 'NULL';
BEGIN /*beginning of PL/SQL block */
SELECT count(rental_id)
INTO lv_count
FROM mm_rental;
IF lv_count > 0 THEN /*loop to test conditions */
DBMS_OUTPUT.PUT_LINE('The number of rentals for member: ' || lv_count); /*output*/
ELSE
DBMS_OUTPUT.PUT_LINE('Please Try Again');
END IF;
RETURN 0; /* indicates value to be returned */
END; /* end of function */RESULTS:
Explanation / Answer
CREATE OR REPLACE FUNCTION rental_history_sf
(p_mem_id IN mm_rental.member_id%TYPE)
RETURN NUMBER
AS
lv_count NUMBER := 0;
lv_rental_count NUMBER := 0;
lv_mem_id NUMBER := 'NULL';
BEGIN
SELECT count(p_mem_id)
INTO lv_count
FROM mm_rental;
IF lv_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('The number of rentals for member: ' || lv_count);
ELSE
DBMS_OUTPUT.PUT_LINE('Please Try Again');
END IF;
RETURN 0;
END;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.