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

What are the tips for using mysql stored procedures? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

The skills of using mysql stored procedures are: 1, to create a stored procedure with in schema parameters; 2, to create a stored procedure with out schema parameters; 3, to create a stored procedure with inout schema parameters.

Tips for using mysql stored procedures are as follows:

Define

A collection of precompiled SQL statements that understand batch statements, similar to the methods in java

1. Improve the reusability of code

2. Simplify the operation

3. The number of compilations and connections to the database server are reduced, and the efficiency is improved.

Create syntax

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

For example:

In stuname varchar (20)

Parameter mode:

In: this parameter can be used as input, that is, it needs to be passed in by the caller.

Out: this parameter can be used as an output, that is, it can be used as a return value

Inout: this parameter can be used as both input and output, that is, it needs to pass in and return values.

If the stored procedure body has only one sentence, begin end can omit it.

Each sql statement in the body of a stored procedure must end with a semicolon.

The end of the stored procedure can be reset using delimiter

Syntax:

Delimiter end tag

Case study:

Delimiter $

Call syntax

CALL stored procedure name (argument list)

Empty parameter list

Insert five records into the admin table

SELECT * FROM admin;DELIMITER $CREATE PROCEDURE myp1 () BEGININSERT INTO admin (username, `password`) VALUES ('john1','0000'), (' lily','0000'), ('rose','0000'), (' jack','0000'), ('tom','0000'); END $# calls CALL myp1 () $

Create a stored procedure with in schema parameters

Create a stored procedure to query the corresponding male god information CREATE PROCEDURE myp2 (IN beautyName VARCHAR (20)) BEGINSELECT bo.*FROM boys boRIGHT JOIN beauty b ON bo.id = b.boyfriend_idWHERE b. Name _ end $# call CALL myp2 ('Liuyan') $according to the name of the goddess.

Create a stored procedure implementation, and whether the user has logged in successfully

CREATE PROCEDURE myp4 (IN username VARCHAR (20), IN PASSWORD VARCHAR (20)) BEGINDECLARE result INT DEFAULT 0 * declare and initialize the SELECT COUNT (*) INTO result# assignment FROM adminWHERE admin.username = usernameAND admin.password = PASSWORD;SELECT IF (result > 0 CALL myp3 'success', 'failure'); # call CALL myp3 ('Zhang Fei', '8888') with END $#

Create a stored procedure for out schema parameters

Return the corresponding male goddess name according to the entered goddess name

CREATE PROCEDURE myp6 (IN beautyName VARCHAR (20), OUT boyName VARCHAR (20)) BEGINSELECT bo.boyname INTO boynameFROM boys boRIGHT JOINbeauty b ON b.boyfriend_id = bo.idWHERE b.name=beautyName; END $

Return the corresponding male goddess name and charm value according to the entered goddess name

CREATE PROCEDURE myp7 (IN beautyName VARCHAR (20), OUT boyName VARCHAR (20), OUT usercp INT) BEGINSELECT boys.boyname, boys.usercp INTO boyname,usercpFROM boys RIGHT JOINbeauty b ON b.boyfriend_id = boys.idWHERE b.name=beautyName; END $# calls CALL myp7 ('Xiao Zhao', @ name,@cp) $SELECT @ name,@cp$

Create a stored procedure with inout schema parameters

Pass in two values, an and b, and eventually both an and b are doubled and returned

CREATE PROCEDURE myp8 (INOUT an INT, INOUT b INT) BEGINSET axiaobao2 * * set baccalaure2 * * end $# call SET @ mechel10 $SET @ nud20 $CALL myp8 (@ m _ pencil _ n) $SELECT @ m _ penn$

Delete stored procedure

Drop procedure stored procedure name DROP PROCEDURE p1terDROP PROCEDURE p2 p3Tinct # ×

View information about stored procedures

DESC myp2;SHOW CREATE PROCEDURE myp2;, thank you for your reading! After reading the above, do you have a general understanding of the techniques for using mysql stored procedures? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are 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.