Detailed explanation of MySQL creation stored procedure and function

Source of the article ： Learn through http://www.bdgxy.com/

1. stored procedure

1.1. Basic grammar

create procedure name ([params])

UNSIGNED [characteristics] routine_body

?params ： in|out|inout Specify the parameter list Represents input and output

routine_body： SQL Code content , With begin ........? ?end

characteristics： Specify stored procedure properties , contain 5 Kind of

? ? ? ? ? ? ? ? ? ? ? ? ? ?1 DETERMINISTIC Not sure of
2 NO SQL No, SQl sentence , Of course, it doesn't change the data
3 READS SQL DATA Just read the data , Of course, it doesn't change the data
4 MODIFIES SQL DATA To modify data
5 CONTAINS SQL Contains SQL sentence

1.2 Create a stored procedure with specified execution permission

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

UNSIGNED [characteristics] routine_body

DEFINER： Indicate who has the authority to perform .

1.3?DELIMITER Use

? ? ? ”DELIMITER //“ Presentation settings “//” The symbol is the conclusion , because mysql The default statement ends with a semicolon ‘;', To avoid stored procedures and mysql Statement symbol conflict , So sometimes DELIMITER To change the closing symbol , To cooperate end // To use ;

? Example ： Create a root Stored procedures executed by the account , Output the length of the given string

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

2. Create a function ?

The creation of a function is the same as that of a stored procedure

Example

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

Be careful : When creating a function, you need to pay attention to 3 A place to ,

1.??RETURNS： You need to specify the return type

2.?UNSIGNED ?NO SQL You need to specify the stored procedure properties

3.return： Return the required data

Mistakes encountered ：

If an error is reported, the above information means that the stored procedure characteristics are not specified .

In the stored procedure function, you can use mysql The query result is copied for its parameters ： The sentence 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//

explain ：

declare: Variables defined inside stored procedures and functions

default： The default value is

This is about mysql This is the end of the article on creating stored procedures and functions , More about mysql Please search the rookie tutorial for the contents of stored procedures and functions www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！