PL/SQL Function

PL/SQL Function

There are two types of PL/SQL blocks: named block and an anonymous block.
There are two types of named blocks: Functions and Procedures

In PL/SQL a Function takes zero or more parameter values and returns one value.

Syntax of PL/SQL function:

FUNCTION name [(parameter[, parameter, …])] RETURN
datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];

Example 1 of PL/SQL Function:

Creating a Function: Examples The following statement creates the function Ask_Balance on the sample table cust_orders (the PL/SQL is in italics):

CREATE FUNCTION Ask_Balance(acc_num IN NUMBER) 
   RETURN NUMBER 
   IS acc_bal NUMBER(11,2);
   BEGIN 
      SELECT order_total 
      INTO acc_bal 
      FROM cust_orders 
      WHERE customer_id = acc_num; 
      RETURN(acc_bal); 
    END;
/

The Ask_Balance function returns the balance of a specified account.

When you call the function, you must specify the argument acc_num, the number of the account whose balance is sought. The datatype of acc_num is NUMBER.

The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.

The function uses a SELECT statement to select the balance column from the row identified by the argument acc_num in the cust_orders table. The function uses a RETURN statement to return this value to the environment in which the function is called.

The function created in the preceding example can be used in a SQL statement. For example:

SELECT Ask_Balance(165) FROM DUAL;

Ask_Balance(165)
------------
2519

[Source]

Example 2 of PL/SQL Function:

create or replace function find_area
(Len in number, Wid in number)
return number
as
varea number;
begin
varea := Len * Wid;
return varea;
end;

SQL> select find_area (10, 30) area from dual;

AREA
---------
300