FUNCTION NUM_PETS (NAME IN HOPEMAN.OWNERS.OWNER_NAME%TYPE)
RETURN INTEGER
AS
O_NAME HOPEMAN.OWNERS.OWNER_NAME%TYPE;
NUMB_OWNED INTEGER := 0;
CURSOR owns (o_name HOPEMAN.OWNERS.OWNER_NAME%TYPE)
IS
SELECT OWNER_NAME
FROM PETS_OWNERS;
BEGIN
OPEN owns(NAME);
LOOP
FETCH owns INTO O_NAME;
EXIT WHEN owns%NOTFOUND;
IF O_NAME = NAME THEN
NUMB_OWNED := NUMB_OWNED + 1;
END IF;
END LOOP;
CLOSE owns;
RETURN NUMB_OWNED;
END;
Like any other function it can take paramters and it must have a RETURN type.
The syntax of these functions are as follows:
FUNCTION JUNK (parameters) RETURN datatype AS
[local declarations]
BEGIN
END;
CURSORS are datatypes that are used to retrive information. They must
be declared as a variable, opened, told to FETCH data, and closed. They
contain a SQL SELECT statement, and the data retrieved must be placed into
a variable of the identical type as the data retrieved. They can also take parameters.
In this example I have declared many of my parameters as:
"HOPEMAN.OWNERS.OWNER_NAME%TYPE"
This allows for me to use the exact type as the parameter that should be
passed to this function. I could have also used the CHAR or VARCHAR types here.
Return to Functions