store procedure using MySql server

Below examples will give you some idea.

EXAMPLE 1:

DELIMITER $$

CREATE PROCEDURE my_procedure_Local_Variables()

BEGIN /* declare local variables */

DECLARE a INT DEFAULT 10;

DECLARE b, c INT; /* using the local variables */

SET a = a + 100;

SET b = 2;

SET c = a + b;

BEGIN /* local variable in nested block */

DECLARE c INT;

SET c = 5;

/* local variable c takes precedence over the one of the

same name declared in the enclosing block. */

SELECT a, b, c;

END;

SELECT a, b, c;

END$$

Now execute the procedure :

mysql> CALL my_procedure_Local_Variables();

+------+------+------+

| a | b | c |

+------+------+------+

| 110 | 2 | 5 |

+------+------+------+

1 row in set (0.00 sec)

+------+------+------+

| a | b | c |

+------+------+------+

| 110 | 2 | 112 |

+------+------+------+

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

------------------------------------------------------------------------------------

EXAMPLE 2:

DELIMITER $$

CREATE PROCEDURE my_procedure_User_Variables()

BEGIN

SET @x = 15;

SET @y = 10;

SELECT @x, @y, @x-@y;

END$$

Now execute the procedure :

mysql> CALL my_procedure_User_Variables() ;

+------+------+-------+

| @x | @y | @x-@y |

+------+------+-------+

| 15 | 10 | 5 |

+------+------+-------+

1 row in set (0.04 sec)

Query OK, 0 rows affected (0.05 sec)

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bharathi Raja8y ago
https://www.w3resource.com/mysql/mysql-procedure.php
Panna Chatterjee8y ago
Below examples will give you some idea.

EXAMPLE 1:

DELIMITER $$
CREATE PROCEDURE my_procedure_Local_Variables()
BEGIN /* declare local variables */
DECLARE a INT DEFAULT 10;
DECLARE b, c INT; /* using the local variables */
SET a = a + 100;
SET b = 2;
SET c = a + b;
BEGIN /* local variable in nested block */
DECLARE c INT;
SET c = 5;
/* local variable c takes precedence over the one of the
same name declared in the enclosing block. */
SELECT a, b, c;
END;
SELECT a, b, c;
END$$
Now execute the procedure :
mysql> CALL my_procedure_Local_Variables();
+------+------+------+
| a | b | c |
+------+------+------+
| 110 | 2 | 5 |
+------+------+------+
1 row in set (0.00 sec)
+------+------+------+
| a | b | c |
+------+------+------+
| 110 | 2 | 112 |
+------+------+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.03 sec)

------------------------------------------------------------------------------------
EXAMPLE 2:

DELIMITER $$
CREATE PROCEDURE my_procedure_User_Variables()
BEGIN
SET @x = 15;
SET @y = 10;
SELECT @x, @y, @x-@y;
END$$
Now execute the procedure :
mysql> CALL my_procedure_User_Variables() ;
+------+------+-------+
| @x | @y | @x-@y |
+------+------+-------+
| 15 | 10 | 5 |
+------+------+-------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.05 sec)
Manav Pandya8y ago
Hello

Here from the below link , you can find complete guide :

https://dev.mysql.com/doc/connectors/en/connector-cpp-tutorials-stored-routines-statements.html

Thanks

Mark as accepted if useful