19/04/2013 - MYSQL
Prosedürler fonksiyonlar gibi sorgular içinde çağrılamazlar. Prosedürler CALL ile çağrılırlar. Sorgu sonucunu geri verme zorunluluğu yoktur ama istenirse OUT ile sonuç geri verilebilir. Prosedürler fonksiyonların aksine birden fazla sonucu geri verebilirler. Parametre olarak IN, OUT ve INOUT kullanılır ama opsiyoneldir.
# 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)
Parametrenin önündeki IN opsiyoneldir.
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)
Parametrelerin önündeki IN ve OUT opsiyoneldir.
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)