PL/SQL Function

A PL/SQL FUNCTION is a named block of code that performs a specific task and returns a single value. It can accept parameters, execute SQL queries, and implement complex logic. Functions enhance code reusability and modularity within Oracle database applications by encapsulating logic that can be called from SQL queries or other PL/SQL blocks. They are defined with a return type, which specifies the data type of the value returned by the function upon completion.

 

In PL/SQL, functions are named blocks of code that can

accept parameters,

perform a specific task,

and return a value.

 

Here's the basic syntax for creating a PL/SQL function:

 

CREATE [OR REPLACE] FUNCTION function_name

   (parameter1 datatype, parameter2 datatype, ...)

RETURN return_datatype

IS

   -- Declarations (optional)

BEGIN

   -- Function body (implementation)

   -- Return statement (required)

   RETURN return_value;

END;

 

Here's a simple example of a PL/SQL function that adds two numbers:

CREATE OR REPLACE FUNCTION add_numbers

   (num1 NUMBER, num2 NUMBER)

RETURN NUMBER

IS

   result NUMBER;

BEGIN

   result := num1 + num2;

   RETURN result;

END;

 

You can then call this function like this:

DECLARE

   sum NUMBER;

BEGIN

   sum := add_numbers(5, 7);

   DBMS_OUTPUT.PUT_LINE('Sum: ' || sum);

END;

 

Or

Select add_numbers(5, 7) from dual;

 

Functions are reusable blocks of code that can be called from other PL/SQL code, SQL queries, or even other functions.

They allow you to encapsulate logic and improve code readability and maintainability.