Learning Oracle PL/SQL

3.3. Retrieving a Book Count with a Function

Before trying to write the function, let's examine the generic syntax for creating this second type of stored program.

3.3.1. Syntax for Creating a Function

Here is the template for creating a function. As you look it over, you'll probably realize that you have seen most of these elements before, other than those in boldface.

CREATE [ OR REPLACE ] FUNCTION procedure_name  
   (parameter1 MODE DATATYPE  DEFAULT expression ,
    parameter2 MODE DATATYPE  DEFAULT expression ,
    ...)
RETURN DATATYPE 
AS
[ variable1 DATATYPE ;
   variable2 DATATYPE ;
   ... ]
BEGIN
   executable_statement ;
   RETURN expression; 
[ EXCEPTION
   WHEN exception_name 
   THEN
      executable_statement ; ]
END;
/

The differences between this function template and the procedure template are minimal. In addition to the fact that the CREATE statement says FUNCTION instead of PROCEDURE, this code differs from a procedure in only two places: the header, which specifies the returned datatype, and the body, which must explicitly convey a value back to the caller.

RETURN datatype

In the header, the RETURN clause is part of the function declaration. It tells the compiler (and other programs) what datatype to expect back when you invoke the function.

RETURN expression

Inside the executable section, this use of RETURN is known as the return statement, and it says "Okay, I'm all done; it's time to send back (return) the following value." You can also put a RETURN statement in the EXCEPTION section. ...