PL- SQL functions in Oracle

Learn PL/SQL functions with simple explanation with examples.

PL-SQL-Functions

A FUNCTION is a named PL-SQL block that may or may not take a values as input but it must return the value.
FUNCTION Can have RETURN clause in the Executable section of a function.
Functions are mainly used to perform the calculation
the data type of RETURN value must be declared in the header section of the function.
A function has the output that needs to be assigned to a variable or it can be used in a select statement.
Function cannot call when it has return type as boolean
A function can have more than one RETURN statement. Each Exception should have a Return statement.
Procedures and Functions can be stored in a database server

PL/SQL FUNCTION Syntax

CREATE [OR REPLACE] FUNCTION function_name [(
parameter_1 [IN] [OUT] data_type,
parameter_2 [IN] [OUT] data_type,
parameter_N [IN] [OUT] data_type]
RETURN return_data_type IS
--the declaration statements
BEGIN
-- the executable statements
return return_data_type;
EXCEPTION
-- the exception-handling statements
END;

PL/SQL FUNCTION Examples

Example-1: pl sql function that prints hello world.

CREATE OR REPLACE FUNCTION MESSSAGE
RETURN VARCHAR2
IS
BEGIN
RETURN ('HELLO WORLD');
END;

for the output use the below query

SELECT MESSSAGE FROM DUAL;

OUTPUT
HELLO WORLD

Example -2

Below is a simple function “ADDITION1” that receives two numbers as parameters and returns sum of two numbers.

CREATE OR REPLACE FUNCTION ADDITION1(A IN NUMBER,B IN NUMBER)
RETURN NUMBER
IS
C NUMBER;
BEGIN
C:= A+B;
RETURN C;
END;

Let’s see how to call this pl sql function in Select statements , PL/SQL block and without blocks.

Calling function with SELECT statement

SELECT ADDITION1(2,3) AS SUM FROM DUAL;

OUTPUT
SUM
5

Calling function IN PL-SQL Block

DECLARE
D NUMBER;
BEGIN
D:=ADDITION1(2,3);
DBMS_OUTPUT.PUT_LINE('SUM OF TWO NUMBERS IS '|| D);
END;

OUTPUT
SUM OF TWO NUMBERS IS 5

Calling function from non sql block

var cint NUMBER;
EXEC :CINT:=ADDITION1(2,3);
PRINT :CINT;

OUTPUT
CINT
–
5

HOW TO PASS VALUES DYNAMICALLY TO A VARIABLE DYNAMICALLY

Consider this small program that receives value of a number in NUM1 variable.

DECLARE
A NUMBER:=&NUM1;
BEGIN
DBMS_OUTPUT.PUT_LINE(A);
END;

Now, hit the run statement on sql developer, it will promt to enter the value. Once you enter the value, the value will be passed dynamically to variable NUM1 and store into the variable A. It prints the value of A at the execution part.