Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.