PL/SQL Functions

What are PL/SQL Functions?

In PL/SQL, a Function is like a predefined set of instructions that you can create and store directly within the database itself. Think of it as a recipe that guides the database on how to perform a specific task, and you can refer to that recipe multiple times.

But why do we need functions? Imagine you're faced with a complex task that needs to be tackled repeatedly. For instance, updating multiple rows of data, calculating intricate values, or orchestrating several database operations at once. Instead of rewriting the same code every time you face that task, you can create a function for it and use it as many times as you want.👨‍🏭

Creating a PL/SQL Function

In PL/SQL, CREATE OR REPLACE FUNCTION creates a new standalone function definition or replaces an already existing function with a new definition.

Syntax for Creating or Replacing PL/SQL Functions

Syntax Explanation

• CREATE [ OR REPLACE ] FUNCTION:

  This is the SQL command used to create a new PL/SQL function. The OR REPLACE option allows you to replace an existing function with the same name.

• function_name:

  The name of the function. It should be unique within the schema.

• (parameter_name [ IN | OUT | IN OUT ] parameter_type [, ...]):

  This section is optional. Here, you can define input parameters (IN), output parameters (OUT), or parameters that serve as both input and output (IN OUT). These parameters allow you to pass values into the function or get values out of it. It is IN by default.

• RETURN return_type:

  Specifies the data type of the value that the function will return.

• { AS | IS }:

  Marks the beginning of the function's declaration section. You can choose either AS or IS, their functionality is the same.

• BEGIN:

  Marks the beginning of the function's body, where you write the logic for the function.

• RETURN expression:

  The expression you provide will be evaluated and returned as the result of the function.

• EXCEPTION:

  This section is optional and used for handling exceptions.

• END:

  Marks the end of the function's body.

Example

Let's assume we have a table called Employees with the following structure:

Table name: Employees

Now we will create this table and populate it with some data to play with.

:joystick:

The resultant table of this code will be:

Now we will create a PL/SQL function to return the total expenditure of the organization on employee salaries.

The above PL/SQL function total_salary() calculates the total expenditure of the company on its employees.

Calling a PL/SQL Function

Now that we know how to create functions in PL/SQL, we can utilize those functions by calling them.

When a program calls a function, the program control is transferred to the called function. The program control is transferred back to the main program when the called function is executed.

Syntax for Calling a PL/SQL Function

Syntax Explanation

• function_name:

  Replace this with the name of the function you want to call.

• arguments:

  If the function takes any arguments, provide them within the parentheses. If the function doesn't require any arguments, you can omit the parentheses.

Example

We have already created the total_salary() function and now we will call that function.

The output of the above code will be:

Dropping a PL/SQL Function

When a function is dropped from the PL/SQL database, it means that the function is permanently removed from the database and can no longer be used or accessed.

Syntax for Dropping a PL/SQL Function

Syntax Explanation

• DROP FUNCTION:

  This is the main command used to drop a function. It tells the database that you want to remove a specific function from the system.

• function_name:

  The name of the function to be dropped.

Example

We will now demonstrate dropping a function by dropping the total_salary() function that we created in the previous sections.

The total_salary function will be dropped after executing this statement.

If other database objects, such as procedures, triggers, or views, are dependent on a function, dropping that function might be a bad idea as these objects may become invalid or unusable.

PL/SQL Recursive Functions

In PL/SQL, we have the flexibility to create recursive functions. Recursive functions are functions that can call themselves. Yes, that's it!

We can use recursive functions to solve a problem by breaking it down into smaller problems (sub-problems).

Let's take a look at an example to understand PL/SQL recursive functions better, shall we?

Recursive function to calculate the factorial of a given number n

The factorial() function is a recursive function that calculates the factorial of a positive integer $n$.

Base case: When $n$ is 0, the factorial is 1. This serves as the termination condition for the recursion.

Recursive case: For $n$ greater than 0, the factorial is calculated as $n$ multiplied by the factorial of $(n - 1)$. This divides the problem into smaller sub-problems.

Calling the factorial() Function

Output

The factorial of 5 is indeed 120, that's to say our recursive function is working just fine!

It's important to realize that recursive functions must have a well-defined base case to avoid infinite recursion. Excessive use of recursion can lead to performance issues and stack overflow errors. I know, I am not very fun at the parties! :nerd_face:

PL/SQL Function Examples

1. A PL/SQL Function to Output the Maximum of Two Values

This function takes two numbers as input and outputs the greater number of the two.

PL/SQL Code:

Output:

In the above example, the greater_of_two() function is outputting the greater number among the two numbers passed to it.

2. Using PL/SQL Function on a Table

Let's create a table Movies with the following structure:

Table name: Movies

Now we will populate this table with some data to play with. :joystick:

The resultant table of this code will be:

Now we will create a PL/SQL function that returns the total runtime of the inputted genre.

PL/SQL Code:

The above PL/SQL function total_salary() calculates the total expenditure of the company on its employees.

Output:

The total runtime of comedy movies is 332 minutes as there are three comedy movies - "The Truman Show" (103 minutes), "Groundhog Day" (101 minutes), and "La La Land" (128 minutes)

3. Adding New Records in a Table Using the PL/SQL Function

In this example, we will write a PL/SQL function to add a new record to the Movies table.

PL/SQL Code:

After running this program, our Movies table will be filled with 3 more rows.

Output

Resultant Movies Table

As you can see three new records have been added to the Movies table.

Advantages of PL/SQL Functions

1. Modularity & Reusability

Functions promote modularity by allowing you to break down your code into smaller pieces of code. This makes your program organized, easier to manage, and reusable as you can call the same function as many times as you want.

2. Performance Optimization

Functions can improve the performance of database operations by executing logic on the database server and minimizing data transfer between client applications and the database.

3. Manageable & Readable

We can divide the work into small modules using PL/SQL functions which becomes quite manageable and promotes readability.

4. Code Maintainability

By using PL/SQL functions, you can centralize changes and updates. If a particular code or logic needs to be tweaked, you only need to update the function once rather than making changes in multiple locations.

5. Security

PL/SQL functions can also be used to enforce stringent security measures, such as granting access rights or validating user input. This ensures that logic has been executed properly without exposing it to any vulnerabilities.

6. Consistency

Using PL/SQL functions ensures that the same logic is applied uniformly whenever a particular functionality is needed across the database, reducing the risk of inconsistencies and errors.

Scope and Visibility of PL/SQL Functions

The scope and visibility of PL/SQL functions refer to their accessibility within a database.

Scope refers to the region of the code where a particular function can be recognized and directly used. A function's scope is determined by its validity and accessibility, typically defined by the block of code or program where the function is created.

Visibility refers to whether a function can be seen or recognized from a particular part of the database. This may sound similar to scope but visibility emphasizes the availability of the function for use.