A script-enabled browser is required for this page to function properly.

Function Syntax

Every function must execute a RETURN statement. The RETURN statement sets the return value of the function and immediately returns control to the trigger, user-named subprogram, or menu item command that called the function.

Syntax

FUNCTION function_name [argument List] RETURN [type] IS
[local variable declaration]
BEGIN
statements
RETURN (result);
[EXCEPTION exception_handlers]
RETURN (result);
END;

where:

function_name Is the unique, user-defined name of the function. This name must adhere to ORACLE naming conventions.

argument_List Is ({var_name [mode] type [:= value]}[,...])

where:

var_name Is the unique name of a local variable.

mode Is {IN|OUT|IN OUT}.

type Is {BOOLEAN|VARCHAR2|NUMBER| DATE|Oracle Forms Object Type}.

value Is a PL/SQL expression.

local_variable_ declaration Is {var_name type [:= value]}(,...]

statements Are the executable PL/SQL statements.

exception_handler 

result Is the expression indicating the value of the function. The value of the expression must be compatible with the type specified in the function specification.

Procedure Example:

The following procedure processes an order by decrementing the inventory available for a particular product. To call this procedure, you pass in the product ID, the number of units being ordered, and the warehouse that should fill the order:

PROCEDURE do_order (units_ordered IN NUMBER,
prod_id IN NUMBER,
warehouse IN NUMBER) IS
units_in_stock  NUMBER;
BEGIN
SELECT amount_in_stock INTO units_in_stock FROM inventory
WHERE product_id = prod_id
AND warehouse_id = warehouse;
IF units_in_stock >= units_ordered THEN
UPDATE inventory
SET amount_in_stock = units_in_stock - units_ordered
WHERE product_id = prod_id; 
ELSE
Message('Insufficient stock on hand.');
Raise Form_Trigger_Failure;
END IF;
END;

You could use this procedure in a trigger as follows:

do_order (:order.units, :order.prod_id, :warehouse.id);

In this example, the values of the three formal parameters are the current values of items in the form, specified with standard bind variable syntax:

:block_name.item_name

Function Example:

The following function returns the number of product units in stock at a particular warehouse. When you call this function, you pass in a product ID and warehouse ID:

FUNCTION get_inventory (product NUMBER, warehouse NUMBER)
RETURN NUMBER IS
amount NUMBER;
BEGIN
SELECT amount_in_stock INTO amount FROM inventory
WHERE product_id = product and warehouse_id = warehouse;
RETURN amount;
EXCEPTION
WHEN OTHERS THEN
Message('Invalid product name or warehouse ID.');
RETURN (-1);
END;

You could use this function in a trigger as follows:

DECLARE
invNUMBER;
amount_required  NUMBER := :order_block.order_item;
BEGIN
inv := Get_inventory (:product.id, :warehouse.id);
IF inv < 0 THEN
. . . -- handle the error here
ELSE IF inv >= amount_required THEN
... -- process the order here
END IF;
END;