The functions in MySQL can be create by similar procedure like we are writting scripts in any language, but gui tool provide some more easy step to do it,they are as follow.the script can be write in two ways , they are
- Stored Procedure
Open the MySQL administrator window as we have use in the previous text description. click on the Script tab on the page and select "Create stored procedures/functions" as given below in the image:
A new form appear as Enter Store Procedures/Function Name, as image given below
Select as you want to create function or Stored Procedure, a new query browser window is appear in which we have to write the function or store procedure code whose image is given below :
Syntax of Function
DELIMITER $$ DROP FUNCTION IF EXISTS `dbname`.`functionname` $$ CREATE FUNCTION `dbname`.`functionname` () RETURNS returntype BEGIN mysql query or functionprocess END $$ DELIMITER ;
Note:before doing all the work you must select your database in which you want to add your script else dbselection error appear
DELIMITER $$ DROP FUNCTION IF EXISTS `school`.`getAddress` $$ CREATE FUNCTION `getFULLNAME`(fname CHAR(10), lname CHAR(10)) RETURNS char(250) CHARSET latin1 BEGIN DECLARE fullname CHAR(250); SET fullname=CONCAT(fname,' ',lname); RETURN fullname; END $$ DELIMITER ;
after writing the script click on the execute button, a new script is add into your database as shown in given below image
now move to the query browser run the MySQL command as given below
Now if you have to create a stored procedure the you can select the store procedure after giving the name to your procedure, its syntax is given below
DELIMITER $$ DROP FUNCTION IF EXISTS `dbname`.`functionname` $$ CREATE PROCEDURE `dbname`.`functionname` () RETURNS returntype BEGIN mysql query or functionprocess END $$ DELIMITER ;
DELIMITER $$ DROP FUNCTION IF EXISTS `school`.`getSTUDENT` $$ CREATE procedure `school`.`getSTUDENT` () BEGIN select * from studinfo; END $$ DELIMITER ;
Similar steps to follow and make the use of this procedure as given command below;
after execute this query the result is givem below as a image.
Note: While executing the procedure method we must use Call Syntax rather then select.
Here we have use another example of store procedure in which where condition is using by giving the external values and execute the command.
DELIMITER $$ DROP PROCEDURE IF EXISTS `school`.`showdetail` $$ CREATE PROCEDURE `school`.`showdetail` ( ide int(3)) BEGIN select fname,lname from studinfo where sid=ide; END $$ DELIMITER ;
In the above procedure we have use the input sid of student and get the required details as like passing the sid number by user. To run the above code use the below command