[SQL / PLSQL] PL/SQL Functions

   

PL/SQL Functions

What is a Function in PL/SQL?

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

The General Syntax to create a function is: 

CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype; 

IS 

Declaration_section 

BEGIN 

Execution_section

Return return_variable; 

EXCEPTION 

exception section 

Return return_variable; 

END;

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.

2) The execution and exception section both should return a value which is of the datatype defined in the header section.

For example, let’s create a frunction called ''employer_details_func' similar to the one created in stored proc

1> CREATE OR REPLACE FUNCTION employer_details_func

2>    RETURN VARCHAR(20);

3> IS

5>    emp_name VARCHAR(20);

6> BEGIN

7>      SELECT first_name INTO emp_name

8>      FROM emp_tbl WHERE empID = '100';

9>      RETURN emp_name;

10> END;

11> / 

In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.

The return type of the function is VARCHAR which is declared in line no 2.The function returns the 'emp_name' which is of type VARCHAR as the return value in line no 9.

How to execute a PL/SQL Function?

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);

This line displays the value returned by the function.