PL/SQL Function Example in Oracle

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]