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

I believe many inexperienced people don't know what to do about how to use stored procedures in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Mysql stored procedure

A stored procedure (Stored Procedure) is a database object that stores complex programs in a database for external programs to call. The purpose is to complete a set of SQL statements for specific functions, which are compiled, created and saved in the database, and can be called and executed by the user by specifying the name of the stored procedure and giving parameters (when needed). To put it bluntly, it is the code encapsulation and reuse at the level of database SQL language. The stored procedures you create are usually saved in the data dictionary of the database.

1. Create a stored procedure

Create procedure stored procedure name (parameter list) begin stored procedure body (a set of legal sql statements) end

The parameter list consists of three parts:

Parameter mode parameter name parameter type.

Example: IN userid varchar (20)

There are 3 parameter modes:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

In: incoming receive

Out: output as return value

Inout: can be used as both input and output

Begin... End uses:

If there is only one sentence in the body of a stored procedure, begin end may not write

Each sql statement of a stored procedure must end with a semicolon

The end of the stored procedure is reset using delimiter

Delimiter $... $

two。 Call stored procedure syntax

CALL stored procedure name (parameter list)

3. Create and invoke case resolution

1. Empty parameter list

# empty parameter stored procedure case # create stored procedure DELIMITER $# modify the closing identifier of the stored procedure CREATE PROCEDURE myP1 () BEGIN insert into demo01 (id,name,age,sex) values (10011, "jalen", 27 DELIMITER 1), (10021, "xiar", 26 DELIMITER 1), (10031, "heli", 24 DELIMITER 0), (10041, "weiwei", 28 DELIMITER) # change the end flag back to # call the stored procedure CALL myP1 (); # View the table results select * from demo01

Stored procedures for ② .in mode parameters

Enter a stored procedure for a single parameter

# 2.1 stored procedure for in mode parameters, enter a single parameter # create the stored procedure DELIMITER $CREATE PROCEDURE myP2 (IN InName varchar (20)) BEGIN SELECT * from demo01 where name = InName; END $DELIMITER; # change the closing flag back # call the stored procedure CALL myP2 ("jalen")

Enter a stored procedure with multiple parameters

# 2.2 stored procedure for in mode parameters, enter multiple parameters # create stored procedure DELIMITER $CREATE PROCEDURE myP3 (IN InAge int, IN InSex int) BEGIN DECLARE res varchar (20) default ""; # declare a local variable res SELECT name into res # assign FROM demo01 WHERE age = InAge AND sex = InSex; SELECT res; # use local variable END $DELIMITER; # call stored procedure CALL myP3 (27dy1)

Stored procedures for ③ .out mode parameters

# stored procedures for 3.out mode parameters # create stored procedures delimiter $# modify the closing identity of stored procedures CREATE PROCEDURE myP4 (IN name varchar (20), out age int, out sex int) BEGIN select demo01.age,demo01.sex into age,sex from demo01 where demo01.name = name; END $DELIMITER; # call CALL myP4 ("jalen", @ age,@sex); select @ age,@sex

Stored procedures for ④ .inout mode parameters

# stored procedure for 4.inout mode parameters # create stored procedure delimiter $# modify the closing identity of the stored procedure CREATE PROCEDURE myP5 (inout m int, inout n int) BEGIN set m: = mend10; set n: = nsigned 10; END $DELIMITER; # change back to the original closing identification semicolon # call set @ a: = 5jinjobb: = 10; CALL myP5 (@ arecoverb); select @ ahomemb

4. Delete stored procedure

Syntax: drop procedure stored procedure name

Note: only one stored procedure can be deleted at a time

Case: drop procedure myP1

5. View information about stored procedures

Show create procedure storage name

After reading the above, have you mastered how to use 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.