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.