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