Creating Stored Functions With the SQL CREATE FUNCTION Statement

The SQL CREATE FUNCTION statement lets you create stored functions that are stored in an Oracle database. These stored (schema level) subprograms can be accessed from SQL. You can use the optional OR REPLACE clause to modify an existing function.

Example: Creating a Stored Function That Returns a String is an example of a function that returns a character string that contains the upper case last and first names of an employee. The example also show how to run (call) the function.

Creating a Stored Function That Returns a String

CREATE OR REPLACE FUNCTION last_first_name (empid NUMBER)
  RETURN VARCHAR2 IS
  lastname   employees.last_name%TYPE; -- declare a variable same as last_name
  firstname  employees.first_name%TYPE; -- declare a variable same as first_name
BEGIN
  SELECT last_name, first_name INTO lastname, firstname FROM employees 
    WHERE employee_id = empid;
  RETURN ( 'Employee: ' || empid || ' - ' || UPPER(lastname) 
                                 || ', ' || UPPER(firstname) );
END last_first_name; 
/

-- you can use the following block to call the function
DECLARE
  empid NUMBER := 163; -- pick an employee ID to test the function
BEGIN
-- display the output of the function
  DBMS_OUTPUT.PUT_LINE( last_first_name(empid) );
END;
/

-- you can also call a function from a SQL SELECT statement 
-- using the dummy DUAL table
SELECT last_first_name(163) FROM DUAL;

Example: Creating a Stored Function That Returns a Number is an example of a stored function that returns the calculated salary ranking for a specific employee based on the current minimum and maximum salaries of employees in the same job category.

Creating a Stored Function That Returns a Number

-- function calculates the salary ranking of the employee based on the current
-- minimum and maximum salaries for employees in the same job category
CREATE OR REPLACE FUNCTION emp_sal_ranking (empid NUMBER) 
  RETURN NUMBER IS
  minsal        employees.salary%TYPE; -- declare a variable same as salary
  maxsal        employees.salary%TYPE; -- declare a variable same as salary
  jobid         employees.job_id%TYPE; -- declare a variable same as job_id
  sal           employees.salary%TYPE; -- declare a variable same as salary
BEGIN
-- retrieve the jobid and salary for the specific employee ID
  SELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = empid;
-- retrieve the minimum and maximum salaries for employees with the same job ID
  SELECT MIN(salary), MAX(salary) INTO minsal, maxsal FROM employees
      WHERE job_id = jobid;
-- return the ranking as a decimal, based on the following calculation
  RETURN ((sal - minsal)/(maxsal - minsal));
END emp_sal_ranking;
/

-- create a PL/SQL block to call the function, you can also use another subprogram
-- because a function returns a value, it is called as part of a line of code
DECLARE
  empid NUMBER := 163; -- pick an employee ID to test the function
BEGIN
-- display the output of the function, round to 2 decimal places
  DBMS_OUTPUT.PUT_LINE('The salary ranking for employee ' || empid || ' is: ' 
                       || ROUND(emp_sal_ranking(empid),2) );
END;
/

The output of the PL/SQL block is similar to:

The salary ranking for employee 163 is: .63