Functions in Oracle

Function:

A function is a subprogram that returns a single value. You must declare and define a function before invoking it. You can
either declare and define it at the same time, or you can declare it first and then define it later in the same block.

The syntax for a function is:

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END ;
/

Function Example

CREATE OR REPLACE FUNCTION
myFunction(booknam in varchar2)
RETURN INTEGER AS

quan INTEGER;
BEGIN
SELECT quantity into quan FROM books WHERE bookname=booknam;
RETURN quan;

EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
END;

select myFunction('Teach Yourself C++') from dual;

A few more differences between a procedure and a function:
1.A function MUST return a value
2.A procedure cannot return a value
3.Procedures and functions can both return data in OUT and IN OUT parameters
4.The return statement in a function returns control to the calling program and returns the results of the function
5.The return statement of a procedure returns control to the calling program and cannot return a value
6.Functions can be called from SQL, procedure cannot
7.Functions are considered expressions, procedure are not