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.


Örnek tablo


# 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)

Stored procedure


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 ;

Test


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)

Stored procedure


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 ;

Test


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)