Shulou(Shulou.com)06/02 Report--

This article mainly shows you "mysql how to create stored procedures and functions", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "mysql how to create stored procedures and functions" this article.

1. Stored procedure 1.1. Basic syntax create procedure name ([params]) UNSIGNED [characteristics] routine_body

Params: in | out | inout specified parameter list represents input and output

Routine_body: SQL code content, with begin. End

Characteristics: specifies the characteristics of stored procedures, including 5 types

1 DETERMINISTIC uncertain

2 NO SQL does not have a SQl statement and certainly does not modify the data

3 READS SQL DATA only reads the data and certainly does not modify the data

4 MODIFIES SQL DATA wants to modify the data

5 CONTAINS SQL contains SQL statements

1.2 create a stored procedure create DEFINER= `root` @ `% `procedure name ([params]) UNSIGNED [characteristics] routine_body that specifies the execution permission

DEFINER: indicate who has the authority to enforce it.

1.3 use of DELIMITER

"DELIMITER /" indicates that the symbol "/ /" is the closing word, because the default statement ends with a semicolon';'in mysql. In order to avoid conflicts between stored procedures and mysql statement symbols, DELIMITER is sometimes used to change the closing symbol, which should be used in conjunction with end / /.

Example: create a stored procedure executed by a root account that outputs the length of a given string

DELIMITER / / CREATE definer= `root` @ `% `PROCEDURE `avgFruitPrice` (in f_string VARCHAR) BEGIN select length (f_string); END//2. Create function

The function is created the same as the stored procedure

Example

DELIMITER / / CREATE definer= `root` @ `% `FUNCTION `root` (f_string VARCHAR) RETURNS INT (11) UNSIGNED NO SQLBEGIN return length (f_string); END//

Note: there are three points to pay attention to when creating a function

1. RETURNS: you need to specify the return type

2. UNSIGNED NO SQL needs to specify the characteristics of stored procedures

3.return: returns the required data

Mistakes encountered:

If the error is reported as above, the stored procedure property is not specified.

The stored procedure function can copy its parameters through the result of the mysql query: its statement is select. Into

Begindeclare onename char (50) default'0';declare twoname char (50); select f_name, b_name into onename, twoname from t_user where id = 1

Description:

Declare: variables defined inside stored procedures and functions

Default: default

These are all the contents of the article "how to create stored procedures and functions in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.