Skip to Main Content
Learning Oracle PL/SQL
book

Learning Oracle PL/SQL

by Bill Pribyl, Steven Feuerstein
November 2001
Beginner content levelBeginner
424 pages
11h 11m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning Oracle PL/SQL

Learning Oracle PL/SQL

Darryl Hurley
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl

Publisher Resources

ISBN: 0596001800Supplemental ContentCatalog PageErrata