MySQL Stored Procedure foundation for beginner
From w3cyberlearnings
Contents |
How to create Stored Procedures in MySQL for user input?
Require user input and generate the result.
mysql> DELIMITER $$
mysql> CREATE PROCEDURE multiplyby100(IN INPUT_NUM INT)
-> BEGIN
-> SELECT INPUT_NUM * 100 AS RESULT;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
Test stored procedure
mysql> CALL multiplyby100(2); +--------+ | RESULT | +--------+ | 200 | +--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
stored procedure for two input parameters
mysql> DELIMITER $$
mysql> CREATE PROCEDURE add_two(IN number1 INT, IN number2 INT)
-> BEGIN
-> SELECT number1 + number2 AS RESULT;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL add_two(45,55);
+--------+
| RESULT |
+--------+
| 100 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
stored procedure for input parameters & output parameters
mysql>DELIMITER $$
mysql> CREATE PROCEDURE add_two2(IN num1 INT, IN num2 INT, OUT out_result INT)
-> BEGIN
-> SET out_result = num1 + num2;
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql>DELIMITER ;
mysql> CALL add_two2(30,40,@addresult);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @addresult;
+------------+
| @addresult |
+------------+
| 70 |
+------------+
1 row in set (0.00 sec)
stored procedure for parameter that can act as input and out
mysql> DELIMITER $$
mysql> CREATE PROCEDURE multiplye_3(INOUT num INT)
-> BEGIN
-> SET num = num * 3;
-> END;
-> $$
Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER ;
mysql> SET @mynum=40;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL multiplye_3(@mynum);
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @mynum;
+--------+
| @mynum |
+--------+
| 120 |
+--------+
1 row in set (0.00 sec)
