Table of contents
1.
Introduction 
1.1.
What is PL/SQL?
1.2.
PL/SQL functions
1.3.
Syntax to create a PL/SQL function:
1.4.
PL/SQL Drop function
2.
Frequently Asked Questions
3.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL Functions

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction 

What is PL/SQL?

PL/SQL is a procedural language that provides the functionality of decision making, iteration, and many more other features of a procedural programming language. SQL is mainly used to manipulate data, while PL/SQL creates an application. PL/SQL can execute many queries in one block using a single command.

PL/SQL functions

PL/SQL functions must return a value, and It is a named PL/SQL block that performs one or more specific tasks.

Syntax to create a PL/SQL function:

CREATE [OR REPLACE] FUNCTION function_name ( parameter_list )

// this statement is must for all the functions 

RETURN return_datatype  

IS | AS

    // declaration block

BEGIN 

    // execution block

    // program code

    Return return_variable;

[ EXCEPTION ]

    // execption block

    Return return_variable;

END [function_name];

Example to create a PL/SQL function:

Create or Replace function getProduct(num1 in number, num2 in number )

return number

Is

  num3 number(5);

begin

  num3= num1* num2;

  return num3;

end;

A function’s return value can be assign to a variable :

result= getProduct(5,6);

Example1:

Using PL/SQL functions, let us see an example to find the total strength of students present in different college sections using PL/SQL functions.

// create a table named "sections" with parameter student_id and student_name and strength

create table sections(student_id int, student_name varchar(20), strength int );

// Inserting data into the sections table

insert into sections values(1, 'computer science', 20);

insert into sections values(2, 'electronics', 45);

insert into sections values(3, 'electrical', 80);

// Defining the function named "totalStrength"

create or replace function totalStrength

// Defining the return type

return integer

as

total integer:=0;

begin                        

// calculating the sum and storing the result in the total variable and returning it

select sum(strength) into total from section;

return total;

// closing of the function

end totalStrength;

set server output on;

declare

answer integer;

begin

answer:=totalstrength();

   dbms_output.put_line('The total strength of students is  ' || answer);  

end;

The output of the above example will be:

The total strength of students is 145

 

 

Must Read  C Program to Reverse a Number

Example2:

Let's take an example to understand the declaring and defining of a simple PL/SQL function that will reverse the given number and return it.

// an example to understand the declraing and definngg of the PL/SQL function

set serveroutput on;

// declaration of variables that will be used in the computation

declare

    a int;

    c int;

    n int;

   rev int:=0;

    r int;

// Defining the function "reverse_n"

function reverse_n( x  IN int) 

return  int

as

z int;

// function code

begin

      //execution block begins

    n := x;

    while (n > 0)

    loop

        r := mod(n, 10);

        rev := (rev * 10) + r;

        n := trunc(n / 10);

    end loop;

    z := rev;

    return z;

      //execution block ends

end  ;

BEGIN  

   a := 9356217;    

   c := reverse_n(a);   //caling the function and storing the returned value in a variable

   dbms_output.put_line('The reverse of the given number is  ' || c);  

END;

Output:

The reverse of the given number is 7126539

Example3

Let's see a recursive PL/SQL function that calculates the factorial of a given number.

// An example of a recursive function in PL/SQL 

DECLARE  

   num int;  

   answer int;  

// Defining the function name "factorial" with integer return type

FUNCTION factorial(x number)  

RETURN int  

IS  

   f int;  

BEGIN  

      //function code begins

   IF x = 0 THEN  

      f := 1;  

   ELSE  

      f := x * factorial(x - 1);  

   END IF;  

RETURN f;  

      // function code ends

END;  

BEGIN  

   num := 4;  

   answer := factorial(num);  // calling the fuction and storing the returned value in a variable 

   dbms_output.put_line(' The factorial of  '|| num || ' is ' || answer);  

END; 

Output :

The factorial of 4 is 24

PL/SQL Drop function

Once the use of PL/SQL function is finished, we need to remove the space by removing that function, thus Drop function is used to remove the created PL/SQL function.

Syntax for removing the created PL/SQL function:

Drop function function_name;

Recommended topic, DCL Commands in SQL and Tcl Commands in SQL

Must Read SQL Clauses

Frequently Asked Questions

1. What are the benefits of PL/SQL?

The following are the benefits of PL/SQL:

  •  Better performance than SQL, as SQL is executed in bulk rather than a single statement
  • Advanced security
  • Scalability and Manageability
  • High Productivity 

    2. What are the advantages of using PL/SQL functions?

The following are the advantages of PL/SQL functions:

  • They promote reusability
  • We can make a single call to the database to run a block of statements. Thus it will improve the performance against running SQL multiple times. This will eventually reduce the number of calls between the database and the application, which will increase the performance.
  • We can divide overall work into small modules that become pretty manageable and enhance the code's readability.
     

3. What is the full form of PL/SQL?

It stands for Procedural Language extensions to SQL.

Key Takeaways

In this blog, we learned about PL/SQL and functions in PL/SQL. We learned the syntax to create a PL/SQL function. We also study the various examples to understand the declaration, defining, and calling of the PL/SQL functions. At last, we learned how to remove the created PL/SQL function along with its syntax.

Visit here for the top 100 SQL problems asked in various product and service-based companies like Google, Microsoft, Infosys, IBM, etc.

Also Read - TCL Commands In SQL

You can visit Coding Ninjas Studio to practice programming problems for your complete interview preparation. Check it out to get hands-on experience with frequently asked interview questions and land your dream job.

Live masterclass