MYSQL function ,Stored procedure and Triggers

Introduction to MYSQL functions ,stored procedures and triggers

What is MYSQL function

In simple language ,MYSQL function is a stored program that you can pass parameters into and then return a value.

BASIC SYNTAX :-

		
   create function FUNCTION_NAME(input_parameter datatype ) returns datatype
	begin 
	DECLARE VARIABLES DATATYPE ; 
	logic you want to apply ;

	return 	variable_name / resultset ;


	end ;
	

LETS TRY WITH EXAMPLE :-

Function to convert input string to upper case. There is already a inbuilt function for this i.e. upper(input parameter), however just for understanding purpose :-

	
	DELIMITER //

	create function fn_upper_convert(input_string varchar(255)) returns varchar(255)
	begin 

	declare var_newstring varchar(255) ;

	set var_newstring =upper(input_string) ;

	return var_newstring ;

	end ; // 

	DELIMITER ;

	

How to invoke function ?

To execute function below command can be used :-

select FUNCTION_NAME('input parameter') ;

E.G. select fn_upper_convert('mysqlgyan') ;

What is MYSQL stored procedure

A stored procedure is a basically segment of SQL statements which can be written as per required business logic . A stored procedure can be invoked by triggers, other stored procedures, and applications such as PHP etc.

BASIC SYNTAX :-

	
	create procedure PROCEDURE_NAME() 
	begin 
	
	DECLARE VARIABLES DATATYPE ; 
	
	logic you want to apply ;

	end ;
	

LETS TRY WITH EXAMPLE :-

Procedure which greets to input name given : -

	DELIMITER //

	create procedure sp_greet(input_name varchar(255)) 
	begin 

	select concat("hello ",input_name," ! Have a nice day !") ;
	
	
	end ; // 

	DELIMITER ;

	

How to invoke stored procedure ?

Stored procedure can be invoked by keyword "call" i.e.

call PROCEDURE_NAME(input parameter if any) ;

E.G. call sp_greet('MYSQLGYAN') ;

Difference between MYSQL function and MYSQL stored procedure ?

-Function should always return a value , Stored procedure may or may not return a value. -Function can return only single value whereas procedure can return more than one values. -Function can be used in select/where/having SQL queries wheras procedures cannot. -Dynamic SQL is not allowed in functions whereas procedure can.

What is MYSQL triggers

Trigger is set of SQL statements executed automatically when an event such as insert/update/delete associated with specific table occurs. Triggers are very useful for maintaining logs ,Data integrity etc.

Types of triggers

There are two types of triggers :-

Before Trigger

-Before trigger executes before insert / update / delete.

After Trigger

-After trigger executes after insert / update / delete.

NOTE :- Triggers may put additional burden on your database and if table is too large it might slow down the queries as well ,So use it wisely.

BASIC SYNTAX :-

	
	create trigger TRIGGERNAME TIME(after/before) EVENT(insert/update/delete) on TABLE_NAME
	for each row
	begin
	
	logic you want to apply ;

	end ;
	

LETS TRY WITH EXAMPLE :-

Lets create  tables first 
create table tbl_employee_detail(
empid 		varchar(100) not null ,
department 	varchar(100) not null ,
primary key idx_empid(empid),
key idx_department(department)	
) ;	



create table tbl_employee_detail_log(
empid 		varchar(100) not null ,
department_old varchar(100) not null ,
department 	varchar(100) not null ,
action_done varchar(100) not null ,
key idx_empid(empid)

) ;	

Trigger to log changes after update : -

	DELIMITER //

	create trigger tg_log after update on tbl_employee_detail
	for each row
	begin 

	insert into tbl_employee_detail_log(empid,department_old,department,action_done)
	values(new.empid,old.department,new.department,'update') ;
	
	
	
	
	end ; // 

	DELIMITER ;