Functions

We can also write functions instead of procedures. In a function declaration, we follow the parameter list by RETURN and the type of the return value: 
   CREATE FUNCTION  <func_name>(<param_list>)
      RETURN  <return_type> AS ...
In the body of the function definition, “RETURN <expression>;” exits from the function and returns the value of <expression>.

Question: Write a function in PL/SQL to add up the integers from 1 to n, for any integer n. 

 SQL> CREATE FUNCTION  sum_n( n INTEGER )  RETURN INTEGER AS
   2    i      INTEGER;
   3    total  INTEGER := 0;
   4  BEGIN
   5    FOR  i  IN  1..n  LOOP
   6      total := total + i;
   7    END LOOP;
   8    RETURN  total;
   9  END;
  10  /
 Function created.

 SQL> SELECT  sum_n( 10 )  FROM  dual;

  SUM_N( 10 )
 ----------
         55

 SQL> VARIABLE  X  NUMBER;

 SQL> BEGIN  :x := sum_n( 10 );  END;
   2  /

 PL/SQL procedure successfully completed.

 SQL> PRINT  :x;

          X
 ----------
         55






      Recently my wife and I were sitting in our den together,    
      she was reading and I was watching pro wrestling.    
      I told her, “In case it ever comes up,    
      I would hate to ever live in a vegetative state, dependent on a machine.    
      If that time ever arrives, please pull the plug.”    
      She immediately got up and unplugged the TV.