Scribd
Upload
13 views

PLSQL Functions

A function in PL/SQL is a named block that must return a value, differing from a procedure which may not. The document provides syntax for creating functions, examples of executing them, and demonstrates a recursive function for calculating factorials and a function for finding the maximum of two numbers. It includes code snippets and expected output for clarity.

Uploaded by

classgattapugari
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views

PLSQL Functions

A function in PL/SQL is a named block that must return a value, differing from a procedure which may not. The document provides syntax for creating functions, examples of executing them, and demonstrates a recursive function for calculating factorials and a function for finding the maximum of two numbers. It includes code snippets and expected output for clarity.

Uploaded by

classgattapugari
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

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.

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;

For example, let’s create a function 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;
/

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.

employee_name := employer_details_func;
If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning
the return type of the function to it.

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.

The following program calculates the factorial of a given number by calling itself recursively −

DECLARE
num number;
factorial number;

FUNCTION fact(x number)


RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;

BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −

Factorial 6 is 720
PL/SQL procedure successfully completed.

Example
The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function
that computes and returns the maximum of two values.

DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 21;
b:= 40;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (21,40): ' || c);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −

Maximum of (21,40): 40

PL/SQL procedure successfully completed.

You might also like