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

This article mainly introduces the example analysis of stored procedures and functions in MYSQL, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

I. Mode

Stored procedures and stored functions

The syntax is as follows:

CREATE PROCEDURE sp_name ([proc_parameter [,...]])

[characteristic...] Routine_body

CREATE FUNCTION sp_name ([func_parameter [,...]])

RETURNS type

[characteristic...] Routine_body

Proc_parameter:

[IN | OUT | INOUT] param_name type

Func_parameter:

Param_name type

Type:

Any valid MySQL data type

Characteristic:

LANGUAGE SQL

| | [NOT] DETERMINISTIC |

| | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} |

| | SQL SECURITY {DEFINER | INVOKER} |

| | COMMENT string |

Routine_body:

Valid SQL procedure statement or statements

For example: stored procedure

> use test

Mysql > delimiter / / # define "/ /" as the start of the statement execution symbol

Mysql > create procedure simpleproc (out param1 int)

Begin

Select count (*) into param1 from t

End

/ /

Mysql > delimiter; # redefine; symbol for the start of statement execution

Mysql > call simpleproc (@ a)

Mysql > select @ a; # an is a variable

+-+

| | @ a |

+-+

| | 1 |

+-+

Difference: the procedure only handles one thing and does not return a value, but the processed value can be returned through the exit parameter param1, while the stored function can return a value

For example: storage function

1. String concatenation function hello ()

Mysql > use test

Mysql > delimiter / /

Mysql > create function hello (t char (20)) returns char (50)

Return concat ('hello,', t,'!')

/ /

Mysql > delimiter

Mysql > select hello ('world')

Thank you for reading this article carefully. I hope the article "sample Analysis of stored procedures and functions in MYSQL" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.