Do you think IIT Guwahati certified course can help you in your career?
Introduction
Do you know how we can use functions and procedures in SQL? If not, then don’t worry. We will help you to understand them.
In this article, we will discuss about functions and procedures in SQL. We will see how we can implement them. In the end, we will also see the difference between function and procedure in SQL. Moving forward, let’s first understand a bit about SQL.
What is SQL?
SQL stands for Structured Query Language. SQL is used for communicating with databases. SQL is a programming language that we use for organizing, managing, and retrieving data from databases. A database is a collection of data records, mainly in the form of tables, that contain information. There are certain SQL commands that can be used to manipulate databases. A few commonly used SQL commands are:
CREATE TABLE used to create a new table.
SELECT used to extract data from a database.
UPDATE used to update data in a database.
DELETE used to delete data from a database.
INSERTINTO used to insert new data into a database.
CREATE DATABASE used to create a new database.
ALTER DATABASE used to modify a database.
Moving forward, let’s understand about function in SQL.
What is a Function in SQL?
A function is a particular block of code that accepts parameters as input and returns the required result. In SQL, a function consists of SQL statements that perform specific tasks. A function in SQL either returns a single value or a table. Functions can be either predefined or user-defined. A few predefined functions in SQL are MIN(), MAX() and SUM(), used to find the minimum, maximum, and sum of selected columns from the database, respectively.
Let’s make a program to understand functions in SQL more clearly.
Implementation
DECLARE
num1 int;
num2 int;
m int;
FUNCTION multiply(a IN int, b IN int)
RETURN int
IS
z int;
BEGIN
z:=a*b;
RETURN z;
END;
BEGIN
num1:= 10;
num2:= 20;
m:= multiply(num1, num2);
dbms_output.put_line('Multiplication of 10 and 20 is: ' || m);
END;
Output
Explanation
In the above code, we have made a program to print the multiplication of two numbers in SQL. Firstly, we have declared three int type variables named num1, num2, and m. We have made a function named multiply() which accepts two variables. We have written code inside BEGIN and END keywords to return the multiplication of two numbers. The BEGIN and END are just like open and closed curly brackets {} that we use in other programming languages and are used to write compound statements. The compound statements allow more than one SQL statement to be grouped together. The IN parameter allows you to pass a value. Then we are calling the multiply() function and storing its result in the m variable, and then printing the result using dbms_output.put_line.
What is a Procedure in SQL?
A procedure can be defined as a set of instructions that accepts input to perform tasks. In SQL, no value is returned from a procedure. We can create a procedure using the below code.
Code
CREATE PROCEDURE ninjas
AS
BEGIN
dbms_output.put_line('Hello Ninjas!');
END;
Output
To execute the ninja procedure, we can use the below code.
Code
BEGIN
ninjas;
END;
Output
Let’s implement a procedure to print the multiplication of two numbers.
Implementation
DECLARE
num1 int;
num2 int;
m int;
PROCEDURE multiply(x IN int, y IN int, z OUT int) IS
BEGIN
z:=x*y;
END;
BEGIN
num1:= 10;
num2:= 20;
multiply(num1, num2, m);
dbms_output.put_line('Multiplication of 10 and 20 is: ' || m);
END;
Output
Explanation
In the above code, we have made a program to print multiplication of two numbers in SQL. Firstly, we have declared three int type variables named num1, num2, and m. We have made a procedure named multiply() which accepts three variables. In variable z we have assigned the result of multiplication of two numbers. The IN parameter allows you to pass a value and the OUT parameter returns a value to the calling program. Then we are calling the multiply() function and then printing the result using dbms_output.put_line.
Key Difference Between Function and Procedure in SQL
Key Difference Between Function and Procedure in SQL:
1. Return Value:
Functions return a single value.
Procedures do not necessarily return a value.
2. Usage:
Functions are used for computation and return a single value based on input parameters.
Procedures are used for executing a sequence of statements or operations.
3. Call Method:
Functions are called within SQL statements, such as SELECT or WHERE clauses.
Procedures are called using the CALL statement.
4. Transaction Handling:
Functions cannot modify database state or perform transaction control operations.
Procedures can modify database state and handle transactions using COMMIT, ROLLBACK, etc.
5. Portability:
Functions are more portable across different SQL databases.
Procedures may have differences in syntax and features depending on the SQL database system.
Difference Between Function and Procedure in SQL
Parameter
Function
Procedure
Return Value
The function calculates and returns the results after receiving certain inputs.
The procedure performs certain tasks after receiving certain inputs.
Try-Catch Blocks
Functions do not support try-catch blocks.
Procedures support try-catch blocks.
SQL Query Integration
Functions are called in SQL Query
Procedures cannot be called in a SQL query.
Select Statements
SELECT statements can have function calls
SELECT statements cannot have procedure calls.
DML Usage
We cannot use DML(Data Manipulation Language) in function.
We can use DML in procedure to manipulate data using UPDATE, DELETE and INSERT commands.
Inter-Procedure Calling
We can call a function using procedure.
Procedure cannot be called in a SQL query.
Example
For example, it calculates the total price of products in a shopping cart or converts units of measurement.
For example, a procedure is one that updates the inventory of products when a purchase is made or inserts new records into a transaction log.
*DML is used to manipulate data using UPDATE, DELETE and INSERT commands.
Frequently Asked Questions
Why we use a function instead of procedure in SQL?
Due to their ability to return values, seamless integration in queries, code reuse, portability between database systems, and improved security management, functions in SQL are preferred over procedures.On the other hand, procedures excel at handling written operations and offering transactional control.
What is a Database?
A database is a collection of data records, mainly in the form of tables, that contain information.
What is the difference between stored procedure and function and views in SQL?
Stored Procedure is the named set of SQL statements stored in the database, used to perform specific tasks with input/output parameters. A function is a database object that returns a single value based on input parameters. A view is a virtual table that functions as a predefined SQL statement that streamlines data retrieval and modification.
Which is better, stored procedure or function?
The decision between stored procedures and functions is determined by the demands of the application. Stored procedures provide procedural logic and flexibility, whereas functions focus on data manipulation and reusability. To make the best decision, consider the requirements as well as the database capabilities.
Conclusion
In this article, we have discussed about SQL(Structured Query language). We have discussed about functions and procedures in SQL. We have also implemented programs to understand both of them clearly. In the end, we have also discussed the difference between function and procedure in SQL. To learn more about SQL, you can read the below-mentioned articles:
We hope this article helped you in understanding the difference between function and procedure in SQL. You can read more such articles on our platform, Coding Ninjas Studio. You will find articles on almost every topic on our platform. Also, you can practice coding questions at Coding Ninjas to crack good product-based companies. For interview preparations, you can read the Interview Experiences of popular companies. Visit here for the top 100 SQL problems asked in various big companies.
570+ registered 
