MySQL Stored procedure Basics

DELIMITER//

CREATE PROCEDURE SS (in X1 int)
Begin

INSERT into Pro (ID) values (x1);
end//
DELIMITER;

Call SS (1004);

################################

View:

Method One: (direct query, more practical, view the currently customized stored procedures)

Select ' Specific_name ' from Mysql.proc where ' db ' = ' your_db_name ' and ' type ' = ' procedure '

Method Two: (View all stored procedures in the database + content)

Show procedure status;

Method Three: (View the list of stored procedures in the current database)

Select Specific_name from Mysql.proc;

Method Four: (View the specific contents of a stored procedure)

Select Body from mysql.proc where specific_name = ' your_proc_name ';

To view the creation code for a stored procedure or function:

Show CREATE PROCEDURE Your_proc_name;
Show Create function Your_func_name;

Call:

mysql> Set @a = 10;
Query OK, 0 rows Affected (0.00 sec)

mysql> set @b = 20;
Query OK, 0 rows Affected (0.00 sec)

mysql> set @c = 0;
Query OK, 0 rows Affected (0.00 sec)

Mysql>select @c;
+------+
| @c |
+------+
| 0 |
+------+

Mysql> Call My_add (@a, @b, @c);
Query OK, 0 rows Affected (0.00 sec)

Mysql> Select @a, @b, @c;
+------+------+------+
| @a | @b | @c |
+------+------+------+
|   10 |   20 | 30 |
+------+------+------+
1 row in Set (0.00 sec)

Delete

drop procedure Your_proc_name;

MySQL Stored procedure Basics