MySQL

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

1. Function
2. Stored Procedure

Function

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:

example

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

example

SELECT getFULLNAME('arun','saxena');

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

Syntax

DELIMITER $$
DROP FUNCTION IF EXISTS `dbname`.`functionname` $$
CREATE PROCEDURE `dbname`.`functionname` () 
RETURNS returntype
BEGIN
mysql query or functionprocess 
END $$
DELIMITER ;

example

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;

Call getSTUDENT()

after execute this query the result is givem below as a image.

Note

Here we have use another example of store procedure in which where condition is using by giving the external values and execute the command.

example

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

Call showdetail(101)