Detailed explanation of creating stored procedures and functions in mysql

1. Stored Procedure

1.1. Basic Syntax

create procedure name ([params])

UNSIGNED [characteristics] routine_body

params: in|out|inout specifies the parameter list representing input and output

Routine_body: SQL code content, beginning with "begin" and ending with "end".

characteristics: specifies the characteristics of the stored procedure, including 5 types

1 DETERMINISTIC
2 NO SQL No SQL statements, and of course no data modification
3 READS SQL DATA only reads data, and of course does not modify the data
4 MODIFIES SQL DATA To modify data
5 CONTAINS SQL contains SQL statements

1.2 Create a stored procedure with specified execution permissions

create DEFINER=`root`@`%` procedure name ([params])

UNSIGNED [characteristics] routine_body

DEFINER: Specifies who has the authority to execute.

1.3 Use of DELIMITER

"DELIMITER //" means setting the "//" symbol as the end word, because the default statement end in MySQL is a semicolon ';'. In order to avoid conflicts between stored procedures and MySQL statement symbols, DELIMITER is sometimes used to change the end word symbol, and it should be used in conjunction with end //;

Example: Create a stored procedure executed by the root account to output the length of a given string

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

2. Create a function

Functions are created in the same way as stored procedures

Example

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

Note: There are three things to note when creating a function.

1. RETURNS: The return type must be specified

2. UNSIGNED NO SQL requires specifying the stored procedure feature

3.return: Return the required data

Errors encountered:

If the error message above is displayed, it means that the stored procedure characteristics are not specified.

In a stored procedure function, you can use the MySQL query result as its parameter: The statement is select .... into

begin

declare onename char(50) default'0';

declare twoname char(50);

select f_name, b_name into onename, twoname from t_user where id =1;

.......

end//

illustrate:

declare: variables defined inside stored procedures and functions

default: default value

This is the end of this article about creating stored procedures and functions in MySQL. For more relevant MySQL stored procedures and functions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!