PL/SQL Function

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

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);
 select lastname into v_name from employee where departmentid = p_dep_id;
 return v_name;
 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;

Execute function example:

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

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: