Create a function

A PL function is a subprogram that contains one or more SQL statements and can be repeatedly executed. It must contain a valid RETURN statement and only returns one variable.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
      argment [ { IN | OUT | IN OUT } ] Type ]
    [ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
{ IS | AS }
 delarification_block
BEGIN
    FUNCTION_body
EXCEPTION
    exception_handler
END;

Example:

obclient> CREATE TABLE employees(  
       empno          NUMBER(4,0),  
       empname     VARCHAR(10),  
       job               VARCHAR(10),      
      deptno         NUMBER(2,0),
      salary          NUMERIC  
   );
Query OK, 0 rows affected

obclient> INSERT INTO employees VALUES (200,'Jennifer','AD_ASST',1,15000),
       (202,'Pat','MK_REP',3,12000),(119,'Karen','PU_CLERK', 4,10000),(201,'Michael','MK_MAN',3,9000);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> CREATE OR REPLACE FUNCTION get_salary_by_dept(
       v_in_dept_id NUMBER,
       v_out_emp_count OUT NUMBER)
       RETURN NUMBER
   IS
       v_sum NUMBER;
   BEGIN
       SELECT SUM(salary), count(*) INTO v_sum, v_out_emp_count
           FROM employees WHERE deptno=v_in_dept_id;
       RETURN v_sum;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('Dept id '||v_in_dept_id||' not found');
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('ERR: '||SQLCODE||': '||SQLERRM);
   END get_salary_by_dept;
/
Query OK, 0 rows affected

Pass parameters

You can pass parameters to a function through the following three methods:

• Specify the sequence

  You can pass parameters in the predefined sequence by using the following syntax: argument_value1[,argument_value2 ...].

  Example:

  obclient> DECLARE
           v_num NUMBER;
           v_sum NUMBER;
       BEGIN
           v_sum :=get_salary_by_dept(3, v_num);
           DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
           DBMS_OUTPUT.PUT_LINE('Total amount of person: '||v_num);
       END;
       /
  Query OK, 0 rows affected
  
  Total salary: 21000
  Total amount of person: 2
  

• Specify the parameter names

  You can explicitly specify the parameter names and values to be passed to the function by using the following syntax: argument1 => parameter1 [, argument2 => parameter2[, ...]]. This method overwrites the predefined sequence.

  Example:

  obclient> DECLARE
           v_num NUMBER;
           v_sum NUMBER;
       BEGIN
           v_sum :=get_salary_by_dept(v_out_emp_count => v_num, v_in_dept_id => 3 );
           DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
           DBMS_OUTPUT.PUT_LINE('Total amount of person: '||v_num);
           END;
       /
  Query OK, 0 rows affected
  
  Total salary: 21000
  Total amount of person: 2
  

• Hybrid method

  You can combine the preceding two methods to pass parameters to a function. Note that parameters passed in the predefined sequence must be put before those passed by name. This is because once you pass a parameter by specifying its name, all subsequent parameters must be passed by name.

  Example:

  obclient> DECLARE
           v_num NUMBER;
           v_sum NUMBER;
       BEGIN
           v_sum :=get_salary_by_dept(3, v_out_emp_count => v_num );
           DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
           DBMS_OUTPUT.PUT_LINE('Total amount of person: '||v_num);
       END;
       /
  Query OK, 0 rows affected
  
  Total salary: 21000
  Total amount of person: 2
  

Default value of a parameter

In the CREATE OR REPLACE FUNCTION statement, you can use the DEFAULT keyword to specify the default value of an input parameter. For example:

CREATE OR REPLACE FUNCTION demo_def_args(
    name VARCHAR2,
    age INTEGER,
    -- If you do not pass a value of gender, the default value is male.
    gender VARCHAR2 DEFAULT 'Male')
    RETURN VARCHAR2
AS
    V_var VARCHAR2(32);
BEGIN
    V_var := name||', '||gender||', '||TO_CHAR(age)||' years old.';
    RETURN v_var;
END;

When a function with default values for parameters is called, the function uses the default value if the caller does not specify a value for a parameter. Otherwise, the function uses the specified value. When you create a function, you can specify a default value only for an input parameter, but not for an input/output parameter.

obclient> DECLARE
     Var VARCHAR(32);
     BEGIN
         Var := demo_def_args('Roger', 30);
         DBMS_OUTPUT.PUT_LINE(var);

         Var := demo_def_args('Allen', age => 40);
         DBMS_OUTPUT.PUT_LINE(var);

         Var := demo_def_args('Tracy', gender => 'Female', age => 20);
         DBMS_OUTPUT.PUT_LINE(var);
     END;
     /
Query OK, 0 rows affected

Roger, Male, 30 years old.
Allen, Male, 40 years old.
Tracy, Female, 20 years old.