Functions

A function is similar to a procedure except that a function must return a value to the statement from which it is called. Together, stored procedures and functions are sometimes referred to as stored subprograms because they are, in one sense, small programs.

In this section, you ll learn how to:

• Create a function

• Call a function

• Get information on functions

• Drop a function

Creating a Function

You create a function using the CREATE FUNCTION statement. The simplified syntax for the CREATE FUNCTION statement is as follows :

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

where

• OR REPLACE specifies the function that is to replace an existing function if present. You can use this option when you want to modify the definition of a function.

• function_name specifies the name of the function.

• parameter_name specifies the parameter name. A function may be passed multiple parameters.

• IN OUT IN OUT specifies the mode of the parameter.

• type specifies the PL/SQL type of the parameter.

• function_body contains the SQL and PL/SQL statements to perform the function s task. Unlike a procedure, the body of a function must return a value of the PL/SQL type specified in the RETURN clause.

The following CREATE FUNCTION statement creates a function named circle_area(), which returns the area of a circle. The radius of the circle is passed as a parameter to circle_area() . This function is created when you run the store_schema.sql script:

 CREATE OR REPLACE FUNCTION circle_area (   p_radius IN NUMBER ) RETURN NUMBER AS   v_pi NUMBER := 3.1415926;   v_area NUMBER; BEGIN   v_area := v_pi * POWER(p_radius, 2);   RETURN v_area; END circle_area; / 

Notice circle_area() returns a NUMBER whose value is set to the computed area of a circle. The next example creates a function named average_product_price(), which returns the average price of products whose product_type_id equals the parameter value. This function is created by the store_schema.sql script:

 CREATE OR REPLACE FUNCTION average_product_price (   p_product_type_id IN INTEGER ) RETURN NUMBER AS   v_average_product_price NUMBER; BEGIN   SELECT AVG(price)   INTO v_average_product_price   FROM products   WHERE product_type_id = p_product_type_id;   RETURN v_average_product_price; END average_product_price; / 

Calling a Function

You call your own functions as you would call any of the built-in database functions; you saw how to call built-in functions in Chapter 3. Just to refresh your memory, you can call a function using a SELECT statement that uses the dual table in the FROM clause. The following example calls circle_area(), passing a radius of 2 meters to the function:

  SELECT circle_area(2)   FROM dual;  CIRCLE_AREA(2) --------------     12.5663704 

The next example calls average_product_price(), passing the parameter value 1 to the function to get the average price of products whose product_type_id is 1:

  SELECT average_product_price(1)   FROM dual;  AVERAGE_PRODUCT_PRICE(1) ------------------------                   29.965 

Getting Information on Functions

You can get information on your functions from the user_procedures view; this view was covered earlier in the section Getting Information on Procedures. The following example retrieves the object_name, aggregate, and parallel columns from user_procedures for circle_area() and average_product_price():

  SELECT object_name, aggregate, parallel   FROM user_procedures   WHERE object_name IN ('CIRCLE_AREA', 'AVERAGE_PRODUCT_PRICE');  OBJECT_NAME                    AGG PAR ------------------------------ --- --- AVERAGE_PRODUCT_PRICE          NO  NO CIRCLE_AREA                    NO  NO 

Dropping a Function

You drop a function using DROP FUNCTION . For example, the following statement drops circle_area():

 DROP FUNCTION circle_area;