SQL-Basics 5 (UDF,IF ELSE,WHILE LOOP)
select database();
use today;
select * from sales;
desc sales;
----------------------How to create the UDF(user-defined functions)-------------------
-------About Delimiter ----------
-------->Please refer to the below link about delimiter
https://www.mysqltutorial.org/mysql-stored-procedure/mysql-delimiter/
Ex:-1
-------
DELIMITER $$
create function add_to_col(a INT)
Returns INT
DETERMINISTIC
Begin
Declare b int;
set b=a+10;
return b;
end $$
select add_to_col(12.2);
-------This color code is just trial and error For understanding Deterministic--------------
Ex:-2(This is not recommended without deterministic)
Delimiter $$
create function add_to_col_1(a int)
returns int
Begin
Declare c int;
set c=a+20;
return c;
end $$
-------Above function am not mentioned Deterministic ---------
show variables like '%log_bin_trust_function_creators%';
set session log_bin_trust_function_creators=ON
SET GLOBAL log_bin_trust_function_creators=ON
select add_to_col_1(10.2)
Delimiter $$
create function add_to_col_2(a int)
returns decimal
NOT DETERMINISTIC
Begin
Declare c int;
set c=a+20;
return c;
end $$
select add_to_col_2(10.1)
SET GLOBAL log_bin_trust_function_creators=ON
Ex:-2
DELIMITER $$
create function add_to_col3(a decimal)
returns int
DETERMINISTIC
Begin
declare b int;
set b=a+10;
return b;
END $$
select add_to_col3(10.3)
DELIMITER $$
create function add_to_col4(a decimal)
returns decimal
DETERMINISTIC
Begin
declare b int;
set b=a+10;
return b;
END $$
select add_to_col4(12.5)
select add_to_col4(12.2)
DELIMITER $$
create function add_to_col5(a decimal)
returns decimal
NOT DETERMINISTIC
Begin
declare b int;
set b=a+10;
return b;
END $$
select add_to_col5(14.3)
select add_to_col5(14.5)
DELIMITER $$
create function add_to_col6(a decimal)
returns decimal
NOT DETERMINISTIC
Begin
declare b decimal;
set b=a+10;
return b;
END $$
select add_to_col6(12.6)
select add_to_col6(12)
DELIMITER $$
create function add_to_col7(a decimal)
returns decimal
DETERMINISTIC
Begin
declare b decimal;
set b=a+10;
return b;
END $$
select add_to_col7(29)
----------Up to to now as per my understanding Deterministic is used to return the constant value--------------------
select * from sales limit 5;
select quantity from sales;
select count(quantity) from sales;
select quantity+10 from sales;
-----How to use UDF(user defined function)-----------
select quantity,add_to_col(quantity) from sales;
-------How to write one more UDF --------
select * from sales limit 5;
DELIMITER $$
create function final_profits(profit int,discount int)
returns int
DETERMINISTIC
Begin
declare Final_profit int;
set Final_profit=profit-discount;
return Final_profit;
END $$
-----------How to use UDF(final_profits) -----------------------------
select final_profits(profit,discount) from sales;
select profit,discount,final_profits(profit,discount) from sales;
select profit,discount,final_profits(profit,discount) from sales limit 5;
EX:-2
DELIMITER $$
create function profit(profit decimal,discount decimal)
returns decimal
DETERMINISTIC
Begin
declare Final_profit decimal;
set Final_profit=profit-discount;
return Final_profit;
END $$
select * from sales limit 5;
select profit,discount,profit(profit,discount) from sales;
select profit,discount,profit(profit,discount) from sales limit 5;
Ex:-3
DELIMITER $$
create function final_profit_real(profit decimal(20,6),discount decimal(20,6),sales decimal(20,6))
returns int
Deterministic
Begin
declare profits int;
set profits=profit-discount*sales;
return profits;
END $$
----------------How to Use UDF (final_profit_real)---------------------
select profit,discount,sales from sales limit 5;
select profit,discount,sales,final_profit_real(profit,discount,sales) from sales;
select profit,discount,sales,final_profit_real(profit,discount,sales) from sales limit 5;
----------How to create int_to_str(UDF) -------------
Ex:-4
DELIMITER $$
create function int_to_str(a int)
returns varchar(30)
DETERMINISTIC
Begin
declare b varchar(30);
set b=a;
return b;
END $$
--------------------How to use UDF(int_to_str)-------------------
select int_to_str(23);
select * from sales limit 5;
select quantity from sales;
select quantity,int_to_str(quantity) from sales limit 5;
select max(sales) from sales;
select min(sales) from sales;
select min(sales),max(sales) from sales;
select * from sales limit 5;
--------How to write If,else if condition inside the function-------
prices
1-100 -super affordable product
100-300 - affordable
300-600 - moderate affordable
600+ - expansive
Ex:-5
DELIMITER $$
create function mark_sales(sales int)
returns int
DETERMINISTIC
Begin
declare flag_sales varchar(30);
if sales<100 then
set flag_sales="super affordable product";
elseif sales>100 and sales<300 then
set flag_sales=" affordable";
elseif sales>300 and sales<600 then
set flag_sales="moderate affordable";
else
set flag_sales="expansive";
end if;
return flag_sales;
END $$
-------------------How to use UDF(mark_sales)--------------------
select mark_sales(303);
select sales,mark_sales(sales) from sales;
------>While am using the above function it returns the flag_sale is 0,Bcz I create the mark_sales as int.
Ex:-6
DELIMITER $$
create function mark_sales1(sales int)
returns varchar(30)
DETERMINISTIC
Begin
declare flag_sales varchar(30);
if sales<100 then
flag_sales="super affordable product";
elseif sales>100 and sales<300 then
flag_sales="affordable";
elseif sales>300 and sales<600 then
flag_sales="moderate affordable";
else
flag_sales="expansive";
END if;
return flag_sales;
END $$
----------------------------How to use UDF(mark_sales1)-----------------------
select mark_sales1(20)
select sales,mark_sales1(sales) from sales limit 5;
EX:-7
DELIMITER $$
create function mark_sales2(sales int)
returns varchar(30)
DETERMINISTIC
Begin
declare flag_sale varchar(30);
if sales<=100 then
set flag_sale="super affordable product";
elseif sales>=100 and sales<=300 then
set flag_sale="affordable";
elseif sales>=300 and sales<=600 then
set flag_sale="moderate affordable";
else
set flag_sale="expansve";
END IF;
return flag_sale;
END $$
----------------------------How to use UDF(mark_sales2)-----------------------
select mark_sales2(100);
select sales,mark_sales2(sales) from sales limit 5;
select database()
Ex:-8
DELIMITER $$
create function sub_to_col(a int)
returns int
DETERMINISTIC
Begin
declare b int;
set b=a-1;
return b;
END $$
------------------How to use UDF(sub_to_col)---------------------
select sub_to_col(22);
select * from sales limit 5;
select sales,sub_to_col(sales) from sales;
alter table sales add column able_to varchar(30) after sales;
desc sales;
update sales set able_to=mark_sales2(sales);
select * from sales limit 5;
------------How to create Loop with if condition ---------
Ex:-1
create table loop_table(val int);
DELIMITER $$
create procedure insert_data()
Begin
set @var=10;
generate_data:loop
insert into loop_table values(@var);
set @var=@var+1;
if @var=100 then
leave generate_data;
end if;
end loop generate_data;
END $$
select * from loop_table;
call insert_data();
select * from loop_table limit 5;
select count(*) from loop_table;
--------For even number insert using loop-----
Ex:-2
create table loop_table1(val int);
DELIMITER $$
create procedure insert_data1()
Begin
set @var=10;
generate_data:loop
insert into loop_table1 values(@var);
set @var=@var+2;
if @var=100 then
leave generate_data;
end if;
end loop generate_data;
END $$
select * from loop_table1;
call insert_data1() ;
select * from loop_table1 limit 5;
-------For example I want insert the data from 10 to 100 which is divisible by 3-------
Ex:-3
create table loop_table2(val int)
DELIMITER $$
create procedure insert_data3()
Begin
set @var=10;
generate_data:loop
if @var%3=0 then
set @var=@var+20;
insert into loop_table2 values(@var);
if @var=100 then
leave generate_data;
END if;
END if;
end loop generate_data;
END $$
select * from loop_table2;
call insert_data3;
select database()
Comments
Post a Comment