19/04/2013 - MYSQL
Procedures cannot be called from the SQL statement like functions. A procedure is invoked using a CALL statement. Query returns are not mandatory but OUT can be used to return results. They can return multiple results unlike functions. Can use the IN, OUT and INOUT parameters but not mandatory.
# Table structure
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`surname` varchar(45) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# Current data
mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 21:41:17 |
| 2 | bye | world | 2015-05-01 21:42:35 |
| 3 | blog | entry | 2015-05-02 09:27:58 |
| 4 | another | post | 2015-05-02 09:27:58 |
| 5 | hello | replica | 2015-05-02 09:27:58 |
| 6 | inanzzz | blog | 2015-05-02 09:30:01 |
+----+---------+---------+---------------------+
6 rows in set (0.00 sec)
Keyword IN in parameter below is not mandatory.
DELIMITER $$
DROP PROCEDURE IF EXISTS `student_list_procedure`$$
CREATE PROCEDURE `student_list_procedure` (IN IN_name VARCHAR(45))
BEGIN
SELECT * FROM `student`
WHERE `name` = IN_name;
END$$
DELIMITER ;
mysql> CALL student_list_procedure('inanzzz');
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 21:41:17 |
| 6 | inanzzz | blog | 2015-05-02 09:30:01 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Keywords IN and OUT in parameters below are not mandatory.
DELIMITER $$
DROP PROCEDURE IF EXISTS `student_total_procedure`$$
CREATE PROCEDURE `student_total_procedure` (IN IN_name VARCHAR(45), OUT OUT_total INT)
BEGIN
SELECT COUNT(*) INTO OUT_total FROM `student`
WHERE `name` = IN_name;
END$$
DELIMITER ;
mysql> CALL student_total_procedure('inanzzz', @total);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)