Functions in MYSQL
In this blog, we will discuss Functions in MYSQL. Like we have functions or methods in programming languages in which we can write multiple statements together just like that we can write multiple or single queries in stored procedures and in functions in mysql. If we have to execute a single or some set of queries again and again on a database so instead of writing and executing those queries repeatedly what we can do is, we can write query(s) once in function and execute/call that function over multiple times. Basically, functions are used to achieve the reusability of queries in MySQL. Functions are used to perform complex tasks and data manipulation. In MySQL functions, we can perform mathematical calculations as well.
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
Mysql provides some inbuilt functions that are divided into 5 categories Math functions, string functions, Date and time functions, Advanced SQL functions, and aggregate functions. We will see these inbuilt functions in the next blog. In this blog, we will see some examples of user-defined functions. User-defined functions are the functions that are defined by the user and in that function user can perform customization.
Functions are the same as stored procedures, the only difference between stored procedures and function is functions should always return a value whereas stored procedures do not return any value.
Syntax For Creating Functions.
- CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
- RETURNS return_datatype
- BEGIN
- Declaration_section
- Executable_section
- END;
In the above syntax:
Function_name: is an identifier indicating the name of the function
Parameter: indicates a number of parameters. It can be zero, one, or more than one.
return_datatype: return value datatype of the function
Declaration_section: in this section, we can declare all variables that we wish to use in the function body
executable_section: code or body or definition for the function is written here.
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
Let’s assume the student1 table given below:
select * from student1;
+—-+———–+——–+——+——-+———–+
| id | stud_name | course | age | marks | division1 |
+—-+———–+——–+——+——-+———–+
| 11 | jay | java | 14 | 99.4 | NULL |
| 24 | ram | java | 13 | 78.9 | NULL |
| 61 | AJAY | cpp | 34 | 55.7 | A |
| 65 | harry2 | hadoop | 29 | 20 | A |
| 78 | era | .net | 10 | 50.8 | NULL |
| 79 | rameshwar | test | 31 | 5 | A |
+—-+———–+——–+——+——-+———–+
#1.creating function
delimiter %%
create function getData(i int) returns varchar(45)
begin
declare v varchar(67);
select course into v from student1 where id=i;
return v;
end %%
#calling function
set @u= getData(24); #here, @u is session variable which holds the value return by
#this getData() function.
select @u; #printing session variable. We can give any name to this session
#vaiable
OUTPUT:
+——+
| @u |
+——+
| java |
+——+
#—————————————————————-
#2.creating function
delimiter &&
create function findingEvenOdd9(i int) returns int
begin
declare n int;
if (i%2=0) then
set n=true;
else
set n=false;
end if;
return n;
end &&
#calling function
set @z= findingEvenOdd9(89) ; #here, @z is session variable
select @z;
OUTPUT:
+——+
| @u |
+——+
| 0 |
+——+
#——————————————————
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
#3.creating function
delimiter ^^
create function add1(a int,b double) returns double
begin
declare c double;
set c= a+b;
return c;
end ^^
set @j=add1(3,2.5);
select @j;
OUTPUT:
+——+
| @j |
+——+
| 5.5 |
+——+
#4.creating avg() function
delimiter &&
create function findingAvg(math int,eng int,sci int) returns int
begin
declare n int;
set n=(math+eng+sci)/3;
return n;
end &&
set @j= findingAvg(10,20,30) ; #here, @u is session variable
select @j as Avg_of_given_number;
OUTPUT:
+———————+
| Avg_of_given_number |
+———————+
| 20 |
+———————+
#dropping function
drop function findingEvenOdd10;
Do watch our Channel to learn more: Click Here
Author:-
Pooja Nandode-Bhavsar
Call the Trainer and Book your free demo class for SQL now!!!
© Copyright 2020 | SevenMentor Pvt Ltd.