How to create stored procedures in mysql
I believe many inexperienced people don't know what to do about how to create stored procedures in mysql. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
Mysql creates stored procedures and functions
A stored program is a set of SQL statements that can be stored in the server. You can create a stored procedure or function with CREATE PROCEDURE and CREATE FUNCTION from the client. Call the created stock procedure or function on the client side through the CALL statement.
Syntax:
Create a storage subroutine:
01
CREATE PROCEDURE sp_name ([proc_parameter [,...]])
02
[characteristic...] Routine_body
03
CREATE FUNCTION sp_name ([func_parameter [,...]])
04
RETURNS type
05
[characteristic...] Routine_body
06
Proc_parameter:
07
[IN | OUT | INOUT] param_name type
08
Func_parameter:
09
Param_name type
10 www.2cto.com
Type:
eleven
Any valid MySQL data type
twelve
Characteristic:
thirteen
LANGUAGE SQL
fourteen
| | [NOT] DETERMINISTIC |
fifteen
| | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} |
sixteen
| | SQL SECURITY {DEFINER | INVOKER} |
seventeen
| | COMMENT 'string' |
eighteen
Routine_body:
nineteen
Valid SQL procedure statement or statements
By default, the subroutine is associated with the current. To explicitly associate a subroutine with a given database, you can specify the name db_name.sp_name when you create the subroutine. When the program is defined, use the client delimiter command to change the statement delimiter from; to / /. This allows the delimiter that is used in the program body to be passed to the server rather than interpreted by mysql itself. For example:
01
Mysql > delimiter / /
02
Mysql > CREATE PROCEDURE simpleproc (OUT param1 INT)
03
-> BEGIN
04
-> SELECT COUNT (*) INTO param1 FROM t
05
-> END www.2cto.com
06
-> / /
07
Query OK, 0 rows affected (0.00 sec)
08
Mysql > delimiter
09
Mysql > CALL simpleproc (@ a)
ten
Query OK, 0 rows affected (0.00 sec)
eleven
Mysql > SELECT @ a
twelve
+-+
thirteen
| | @ a |
fourteen
+-+
fifteen
| | 3 |
sixteen
+-+
seventeen
1 row in set (0.00 sec)
01
Mysql > delimiter / /
02
Mysql > CREATE FUNCTION hello (s CHAR (20)) RETURNS CHAR (50)
03
-> RETURN CONCAT ('Hello,', 'spjimg')
04
-> / /
05
Query OK, 0 rows affected (0.00 sec)
06
Mysql > delimiter
07 www.2cto.com
Mysql > SELECT hello ('world')
08
+-+
09
| | hello ('world') | |
ten
+-+
eleven
| | Hello, world! | |
twelve
+-+
thirteen
1 row in set (0.00 sec)
After reading the above, have you mastered the method of how to create stored procedures in mysql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
