PL/SQL Functions

Index - PL/SQL Tutorial

Introduction to PL/SQL
Chap 1 : Block structure and Advantages of PL/SQL
Chap 2 : PL/SQL Variables and Constants
Chap 3 : PL/SQL Records
Chap 4 : PL/SQL IF ELSE statement / Conditional statements
Chap 5 : PL/SQL FOR and WHILE LOOP / Iterative Statements
Chap 6 : Cursors in PL/SQL
Chap 7 : PL/SQL Procedures
Chap 8 : PL/SQL Functions
Chap 9 : Parameters-Procedure and Function in PL/SQL
Chap 10 : What are PL/SQL Triggers
Chap 11 : Exception Handling in PL/SQL

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)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

CREATE OR REPLACE FUNCTION employer_details_func
RETURN VARCHAR(20);
IS
emp_name VARCHAR(20);
BEGIN
SELECT first_name INTO emp_name
FROM emp_tbl WHERE empID = '100';
RETURN emp_name;
END;
/