I want to create a function that returns the number of rows in a table called Rating with a where clause.Where am i going wrong before the declare statement and the end statement?
create or replace
FUNCTION get_movies(user IN NUMBER) RETURN NUMBER
IS
DECLARE cnt NUMBER;
BEGIN
SELECT count(*)
INTO cnt
FROM rating
where userid= user;
RETURN cnt;
END;
I will appreciate help.Thanks.
You should not have the DECLARE keyword. You only need that for an anonymous block (or a sub-block).
create or replace
FUNCTION get_movies(p_userid IN NUMBER) RETURN NUMBER
IS
cnt NUMBER;
BEGIN
...
user is a reserved word so I'd suggest not using that as your parameter name. In the where clause I'm not sure if it will use your parameter value, or the name of the user executing the function; which would error as that string value couldn't be implicitly converted to a number.
