Functions in PL/SQL

The PL/SQL Function is very similar to PL/SQL Procedure. The main difference between procedure and a function is, a function must always return a value, and on the other hand a procedure may or may not return a value. Except this, all the other things of PL/SQL procedure are true for PL/SQL function too.

Syntax to create a function:

          
CREATE [OR REPLACE] FUNCTION function_name [parameters]  
[(parameter_name [IN | OUT | IN OUT] type [, ...])]  
RETURN return_datatype  
{IS | AS}  
BEGIN  
   < function_body >  
END [function_name];  

Here:

• Function_name: specifies the name of the function.

• [OR REPLACE] option allows modifying an existing function.

• The optional parameter list contains name, mode and types of the parameters.

• IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.

• The function must contain a return statement.

• RETURN clause specifies that data type you are going to return from the function.

• Function_body contains the executable part.

• The AS keyword is used instead of the IS keyword for creating a standalone function.

PL/SQL Function Example

Output :

Addition is: 33
Statement processed.
0.05 seconds

Let's learn Cursors in PL/SQL

---

Share this page on :