First of all we have to crate our example table called “Customer”.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE `mysql`.`Customer` ( `CustomerID` int(11) NOT NULL auto_increment, `CompanyID` int(11) NOT NULL, `FirstName` varchar(45) default NULL, `LastName` varchar(45) default NULL, `Address` varchar(500) default NULL, `City` varchar(45) default NULL, `State` varchar(45) default NULL, `ZipCode` int(5) default NULL, `ContactNumber` int(10) default NULL, PRIMARY KEY (`CustomerID`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 |
We have to insert one record into Customer table. Let’s add Steve Jobs! 🙂
|
1 2 |
INSERT INTO mysql.Customer(CompanyID, FirstName, LastName, Address, City, State, ZipCode, ContactNumber) VALUES(1, 'Steve', 'Jobs', '1 Infinity Loop', 'Cupertino', 'CA', 95014, 1112223333); |
Here is syntax to create our function which will bring Concat First and Last Name and return single string.
|
1 2 3 4 5 6 7 8 |
CREATE DEFINER=`scott`@`%` FUNCTION `mysql`.`getCustomerFullName`(intCustomerID INT) RETURNS varchar(100) CHARSET latin1 BEGIN DECLARE strCustomerFullName VARCHAR(100); SET strCustomerFullName = CONCAT((SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),' ', (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID)); return strCustomerFullName; END |
How to call getCustomerFullName() function.
|
1 |
SELECT *, mysql.getCustomerFullName(c.CustomerID) AS 'Full Name' FROM mysql.Customer AS c |
This is how our output will look.
Please give some difference between functions and procedures.
please tell If i can return multiple values using a function, say all fields of a comment table join to all fields of user table.
other way in the line 5 of the function.
CREATE DEFINER=
scott@%FUNCTION
mysql.getCustomerFullName(intCustomerID INT)RETURNS varchar(100) CHARSET latin1
BEGIN
DECLARE strCustomerFullName VARCHAR(100);
SET strCustomerFullName = (SELECT CONCAT(FirstName, ‘ ‘, LastName) FROM Customer WHERE CustomerID = intCustomerID);
return strCustomerFullName;
END
Thanks Gustavo, your query is more efficient than mine.