Return value from a function : Function Return « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Function Return

SQL> CREATE or replace FUNCTION compute_discounts (order_amt NUMBER)
  2  RETURN NUMBER IS
  3          small_order_amt NUMBER := 400;
  4          large_order_amt NUMBER := 1000;
  5          small_disct NUMBER := 1;
  6          large_disct NUMBER := 5;
  7  BEGIN
  8          IF (order_amt < large_order_amt
  9              AND
 10              order_amt >= small_order_amt)
 11          THEN
 12               RETURN (order_amt * small_disct / 100);
 13          ELSIF (order_amt >= large_order_amt)
 14          THEN
 15                RETURN (order_amt * large_disct / 100);
 16          ELSE
 17                RETURN(0);
 18          END IF;
 19  END compute_discounts;
 20  /


SQL>
SQL> set serveroutput on
SQL> DECLARE
  2          tiny NUMBER := 20;
  3          med NUMBER := 600;
  4          big NUMBER := 4550;
  5          wrong NUMBER := -35;
  6  BEGIN
  7          dbms_output.put_line (' Order     AND      Discount ');
  8          dbms_output.put_line (tiny || ' ' || compute_discounts(tiny));
  9          dbms_output.put_line (med || ' ' || compute_discounts (med));
 10          dbms_output.put_line (big || ' ' || compute_discounts (big));
 11          dbms_output.put_line (wrong || ' ' || compute_discounts (wrong));
 12  END;
 13  /
Order     AND      Discount
20 0
600 6
4550 227.5
-35 0

PL/SQL procedure successfully completed.

SQL>
SQL>