PL/SQL Function – Oracle PL/SQL Tutorial

Oracle PL/SQL function is a subroutine available to applications that access an Oracle database. Functions provide modularity and code reusability.

A PL/SQL function is a block of PL/SQL code that can be called by another block of code or from a SQL statement. It returns a single value, which can be a scalar value or a record type. Functions can take zero or more parameters, and can also have a return type specified.

When creating a function, you specify a name for the function, its parameters (if any), and its return type. The return type can be a scalar data type (such as NUMBER, VARCHAR2, DATE, BOOLEAN) or a composite data type (such as an object type, record, or table).

Create function syntax:

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ]

   RETURN return_data_type

IS | AS -- Declarative part (optional)

-- Declarations of local types, variables, subprograms

BEGIN -- Executable part (required)

-- SQL statements
-- PL/SQL statements

EXCEPTION -- Exception-handling part (optional)

-- Exception handlers for exceptions (errors) raised in executable part

END [function_name]; -- End of executable part (required)

To create a function, you use the CREATE FUNCTION statement.

DECLARE any variables that the function will use.
These variables are local to the function and cease to exist when the function completes execution.

Begin the BEGIN…END block that contains the executable statements for the Oracle PL/SQL function.
Use the RETURN statement to return a value from the Oracle PL/SQL function.

Create function example:

create or replace function get_name (p_dep_id number)
return varchar2 as
 v_error varchar2(250);
 v_name varchar2(100);
begin
 select lastname into v_name from employee where departmentid = p_dep_id;
 return v_name;
exception
 when no_data_found then
  v_error := 'No data found!';
  return v_error;
 when too_many_rows then
  v_error := 'Too many rows!';
  return v_error;
end;

Execute function example:

declare
v_name varchar2(100);
begin
v_name := get_name(31);
DBMS_OUTPUT.PUT_LINE('The name is: '||v_name);
end;

Function with Parameters

Parameters are used to pass values into a Oracle PL/SQL function. The Oracle PL/SQL function can be:
In mode IN, the parameter acts as an input only. The value of the parameter can be set when the Oracle PL/SQL function is invoked.
In mode OUT, the parameter acts as an output only. The Oracle PL/SQL function can set the value of the parameter, which is then returned to the caller.
In mode IN OUT, the parameter acts as both an input and an output. The Oracle PL/SQL function can set the value of the parameter, which is then returned to the caller.

Drop function syntax:

DROP FUNCTION function_name;

Drop function example:

DROP FUNCTION get_name;

In summary, Oracle PL/SQL functions are a way to encapsulate a specific functionality to be reused across the database, they have a defined input and output, they can be called from other PL/SQL blocks and SQL statements, and they can be defined as deterministic or non-deterministic.