Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

An overview of the

A stored procedure is a collection of pre-compiled SQL statements, similar to batch statements, that expose a name and are called by that name when needed.

advantages

  • Improved code reuse, created by compilation and stored in a database, is invoked by the user by specifying the name of the stored procedure and given parameters (when needed).

  • Simplified operation

  • Improved performance, reduced compilation times and reduced database connections, improved efficiency

Use of stored procedures

Declaration statement terminator

  • For example, the end character of a user-defined declaration statement is$$👉 DELIMITER $$

Creating a stored procedure

CREATE PROCEDURE Name of the stored PROCEDURE (parameter list) BEGIN Body of the stored PROCEDURE (a set of valid SQL statements) END $$Copy the code

  • The parameter list consists of three parts: Parameter Mode Parameter Name Parameter Type Example: IN username varchar (20)

  • Parameter mode:

    1. IN: Parameters decorated with this pattern can be input, that is, parameters that require a value passed in by the caller
    2. OUT: This parameter can be used as output, and this parameter can be used as return value
    3. INTOUT: This parameter can be used as either an input value or an output value. That is, this parameter can be either passed in or returned

    🚨 If the stored procedure body contains only one statement, the BEGIN END can be omitted and each SQL statement in the stored procedure body must END with a semicolon (;).

Calling a stored procedure

Syntax: CAll stored procedure name (argument list)

  • For example,
CALL stored procedure name (@outParam1,@outParam2...) Select @outparam1, @outparam2...Copy the code

Deleting a stored procedure

  • 🚦 You can delete only one stored procedure at a time
DROP PROCEDURE Saves the PROCEDURE nameCopy the code

View information about stored procedures

SHOW CREATE PROCEDURE Name of the stored PROCEDURECopy the code

example

A stored procedure with no arguments

#$DELIMITER $CREATE PROCEDURE mypro() BEGIN INSERT INTO USER (user_id,real_name) VALUES(4,'bb'); CALL mypro()$# DROP PROCEDURE IF EXISTS myproCopy the code

Create a stored procedure with an IN model

  • Defining variables: When defining variables in a stored procedure, the declaration of local variables must be placed at the beginning of the stored procedure body.

    Syntax: DECLARE variable name data type;

  • Variable assignment

Syntax: SET variable name = value

CREATE PROCEDURE MyPRO2 (IN user_id INT,IN PASSWORD VARCHAR(20)) BEGIN DECLARE result INT DEFAULT 0; SELECT COUNT(*) INTO result FROM USER WHERE user.`user_id`=user_id AND user.`password`=PASSWORD; SELECT IF(result,' registered ', 'unregistered '); END $# CALL myPro2 (1,'12334')$;Copy the code

Create a stored procedure to the INOUT model

  • create
DELIMITER $$

DROP PROCEDURE IF EXISTS `test2`$$

CREATE PROCEDURE `test2`(INOUT username VARCHAR(20))
BEGIN
	INSERT INTO USER(username) VALUE(username);
END$$

DELIMITER ;
Copy the code
  • perform
SET @username=' Programmer' CALL test2(@username) SELECT @usernameCopy the code
  • The results of

Create stored procedures with both IN and OUT models

  • create
The function of the stored procedure is to insert a user's information, DELIMITER $$CREATE PROCEDURE 'test' (IN username VARCHAR(20),IN sex VARCHAR(20),OUT ID INT) BEGIN INSERT INTO USER (username,sex,birthday) VALUES(username,sex,NOW()); SELECT LAST_INSERT_ID() INTO id; END$$ DELIMITER ;Copy the code
  • Execute and get the returned ID
CALL test(' Programmer without milk tea ',' male ',@id) SELECT @idCopy the code
  • performSELECT @idThe results of the

  • Check the database table, it did successfully insert a user information, and return the self-increased ID

🏁 the above is a brief introduction to the MySQL stored procedure. If there are any errors, please leave a comment. If you think this article is helpful to you, please like 👍