Create a function

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

The basic syntax for creating a function is as follows:

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

Here is an 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

Parameter passing

You can pass parameters to a function in the following ways:

• Positional notation.

  You can pass parameters in the order they are defined. The syntax is argument_value1[,argument_value2 ...]

  Here is an 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
  

• Named notation.

  You can explicitly specify the parameter names and values. The parameters do not need to be passed in the order they are defined. The syntax is argument1 => parameter1 [, argument2 => parameter2[, ...]]

  Here is an 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
  

• Mixed notation.

  You can use both positional and named notation to pass parameters to a function. When you use mixed notation, the parameters passed by using positional notation must be placed before the parameters passed by using named notation. In other words, if any parameter is passed by using named notation, all subsequent parameters must also be passed by using named notation.

  Here is an 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
  

Function features

DETERMINISTIC

The DETERMINISTIC feature indicates that the function is deterministic, meaning it always returns the same result for the same input parameters. This is important for optimizing query performance.

PARALLEL_ENABLE

The PARALLEL_ENABLE feature indicates that the function can be safely executed in parallel queries. When you specify this feature, the function must meet the following conditions:

• The function must be deterministic (DETERMINISTIC)
• The function cannot modify the database state
• The function cannot depend on session state or global variables

Example of using PARALLEL_ENABLE:

CREATE OR REPLACE FUNCTION sub_query_result(input VARCHAR)
RETURN VARCHAR
DETERMINISTIC
PARALLEL_ENABLE
IS
    result tp_sys_para.para_value%type;
BEGIN
    SELECT tp.para_value INTO result
    FROM tp_sys_para tp
    WHERE tp.para_name_e = input;
    RETURN result;
END;
/

Default parameter values

You can use the DEFAULT keyword in the CREATE OR REPLACE FUNCTION statement to specify default values for input parameters. Here is an example:

CREATE OR REPLACE FUNCTION demo_def_args(
    name VARCHAR2,
    age INTEGER,
    -- If the gender parameter is not provided, 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 is created, if no actual parameter value is provided for a parameter with a default value during the function call, the function will use the default value of the parameter. However, if an actual parameter value is provided for the default parameter, the function will use the actual parameter value. When creating a function, you can only set default values for input parameters, not for input/output parameters.

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

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

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

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