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.
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
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;