Create a function

A function is similar to a procedure, except that a function must return a value. You create a function using the CREATE FUNCTION statement:


CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN type
{IS | AS}
BEGIN
    function_body
END function_name;

IN |OUT | IN OUT is the mode of the parameter.


SQL> CREATE FUNCTION circle_area (p_radius IN NUMBER)
  2  RETURN NUMBER AS
  3     v_pi NUMBER := 3.1415926;
  4     v_area NUMBER;
  5  BEGIN
  6     v_area := v_pi * POWER(p_radius, 2);
  7     RETURN v_area;
  8  END circle_area;
  9  /

Function created.

SQL>

Calling a Function


SQL> SELECT circle_area(2)
  2  FROM dual;

CIRCLE_AREA(2)
--------------
    12.5663704

SQL>

Home »
Oracle »
PL/SQL »

Functions:

Create a function
Call a function with named and mixed notation
Return the average salary
Information on Functions
Dropping a Function