PL/SQL Functions

In this post we will see how create, use and drop PLSQL functions using the syntax.

CREATE FUNCTION

Like in most of programming languages we have provision to create user defined functions. Oracle also provide way to create our own custom functions to do various operations on database.

Syntax:

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; 

• 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, varchar2 etc.
• The execution section and exception section both should return a value which is of the datatype defined in the header section.
• Function can also have parameters there are three types of parameters that can be declared

1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

Example

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

 CREATE OR REPLACE FUNCTION student_age_finder ( stdId IN varchar2 )

    RETURN number;

 IS 

    stdage  number; 

 BEGIN 

SELECT age INTO stdage

FROM student WHERE studentId = stdId;

RETURN stdage;

END;

/ 

In the example we are retrieving the ‘age’ of Student with stdId given in function call to variable 'stdage'.

The return type of the function is number.

The function returns the 'stdage' which is of type NUMBER.

How to execute a PL/SQL Function?

A function can be executed in the following ways.

1) As a part of a SELECT statement

SELECT student_age_finder(100) FROM dual;

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

student_age :=  student_age_finder(100);

3) In a PL/SQL Statements like,

dbms_output.put_line(student_age_finder(100));

DROP FUNCTION

Syntax

The syntax to a drop a function in Oracle is:

DROP FUNCTION function_name;

Where function_name is name of which is to be dropped.

Example

DROP FUNCTION student_age_finder