MySQL Learning 21: initial stored Procedure

Introduction to a stored procedure

Before learning the stored procedure, let's take a look at the command execution flow in the MySQL database:

(1) Stored procedures

Stored procedures are precompiled collections of SQL statements and control statements that are stored as a single name and processed as a unit.

(2) Advantages of the stored procedure

1) Enhance the functionality and flexibility of SQL statements.

2) achieve a faster execution speed.

3) Reduce network traffic.

(3) Parsing of stored procedure syntax structure The syntax structure of the MySQL database creation stored procedure is:

CREATE [definer = {User | Current_User}] PROCEDURE sp_name ([proc_parameter[,...])

[Characteristic ...] Routine_body;

Meaning of the representative of Proc_parameter:

[In | Out | INOUT] Param_name Type

Parameter meaning:

1) In, indicating that the value of the parameter must be specified when the stored procedure is called.

2) out, indicating that the value of the parameter can be changed by the stored procedure and can be returned.

3) INOUT, which indicates that the value of this parameter is specified at the time of invocation and can be changed and returned.

(4) Stored procedure characteristics

COMMENT ' String '

       | {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}
| SQL SECURITY {definer | INVOKER}

Feature Explanation:

1) COMMENT: note.

2) CONTAINS sql: A statement that contains SQL statements but does not contain read or write data.

3) No SQL: does not contain SQL statements.

4) READS SQL data: The statement that contains the read data.

5) modifies SQL data: The statement that contains the write data.

6) SQL SECURITY {definer | INVOKER}: Indicates who has permission to execute.
(5) Process body

1) The process body is composed of legitimate SQL statements.

2) The process body can be any SQL statement.

3) If the process body is a composite structure, use begin ... and statement.

4) Composite structures can also contain declarations, loops, and control structures.

(6) call a stored procedure

The syntax of the MySQL database call stored procedure is:

1) Call the stored procedure with parameters

Call Sp_name ([parameter[,...]]);

2) calling a stored procedure with no parameters

Call sp_name[()];

Two creating a stored procedure (1) Creating a stored procedure with no parameters

Create a stored procedure that queries the MySQL database version number

CREATE PROCEDURE SP1 () SELECT VERSION ();

Call a stored procedure

Call SP1;

Call SP1 ();

(2) Creating a stored procedure with an in type parameter

DESC Users3;

SELECT * from Users3;

Create a stored procedure that deletes all records in a data table

To modify the terminator of a MySQL sentence:

DELIMITER//

CREATE PROCEDURE Removeuserbyid (in ID int. UNSIGNED) BEGIN DELETE from Users3 WHERE

Id=id; end//

DELIMITER;

Call a stored procedure

Call Removeuserbyid (3);

SELECT * from Users3;

to modify the syntax structure of a stored procedure:

ALTER PROCEDURE sp_name [chatacteristic ...] COMMENT ' String ' | {CONTAINS SQL | NO SQL | READS

SQL DATA | Modifies SQL DATA} | SQL SECURITY {definer | INVOKER};

to delete the syntax structure of a stored procedure: DROP PROCEDURE [IF EXISTS] sp_name;

Example of deleting a stored procedure:

DROP PROCEDURE Removeuserbyid;

To re-insert a record into the USERS3 data table

INSERT Users3 VALUES (NULL, ' Tom ', ' 123 ', 25, 1);

INSERT Users3 VALUES (NULL, ' John ', ' 223 ', default,0);

INSERT Users3 VALUES (DEFAULT, ' Rose ', ' 323 ', 25, 1);

INSERT Users3 VALUES (DEFAULT, ' Paul ', ' 123 ', 23, 1);

INSERT users3 VALUES (DEFAULT, ' Jord ', ' 123 ', 23, 1);

INSERT Users3 VALUES (DEFAULT, ' Lee ', ' 123 ', 23, 1);

INSERT Users3 VALUES (DEFAULT, ' Jams ', ' 123 ', 23, 1);

INSERT Users3 VALUES (NULL, ' Dave ', ' 456 ', 23,0);

INSERT Users3 VALUES (NULL, ' Jack ', ' 456 ', 24, 1);

Create a stored procedure that deletes a record from a data table DELIMITER//

CREATE PROCEDURE Removeuserbyid (in p_id int. UNSIGNED) BEGIN DELETE from Users3 WHERE

id=p_id; end//

DELIMITER;

SELECT * from Users3;

Call a stored procedure

Call Removeuserbyid (n);

SELECT * from Users3;

(3) Creating a stored procedure with the in and out type parameters create a stored procedure that deletes a record and returns the total number of remaining records

DELIMITER//

CREATE PROCEDURE removeuserandreturnusernums (in p_id int unsigned,out usernums int

UNSIGNED) BEGIN DELETE from Users3 WHERE id=p_id; SELECT count (id) from USERS3 to Usernums;

end//

DELIMITER;

SELECT count (id) from USERS3;

Call a stored procedure

Call removeuserandreturnusernums (@nums);

SELECT @nums;

The sequence of characters beginning with the @ symbol refers to the user variable, which is the variable defined by the user in the MySQL client, and is generally used in the begin and block.

Declare, define, etc. the parameters passed in the stored procedure.

(3) Creating a stored procedure with multiple out type parameters

The command to get the number of rows affected is: SELECT Row_count ():

Create a stored procedure that deletes records through the age field and returns the number of records deleted and the number of remaining records

DELIMITER//

CREATE PROCEDURE Removeuserbyageandreturninfos (in P_age INT unsigned,out deleteusers

SMALLINT unsigned,out usercounts SMALLINT UNSIGNED) BEGIN DELETE from Users3 WHERE

Age=p_age; SELECT Row_count () into deleteusers; SELECT COUNT (ID) from USERS3 to usercounts;

end//

DELIMITER;

SELECT * from Users3;

SELECT COUNT (ID) from Users3 WHERE age=23;

Call a stored procedure ( delete age=23 records )

Call Removeuserbyageandreturninfos (23,@a,@b);

SELECT @a;

SELECT @b;

SELECT * from Users3;

SELECT @a,@b;

The difference between a three-stored procedure and a custom function The difference between a stored procedure and a custom function:

1) The functions of the stored procedure implementation are more complex, and the functions are more targeted.

2) A stored procedure can return multiple values; a function can have only one return value.

3) Stored procedures are generally performed independently, whereas functions can appear as part of other SQL statements.
Using stored procedure considerations

1) When creating a stored procedure or custom function, you need to modify the delimiter through the delimiter statement.

2) If the function body or process body has multiple statements, it needs to be included in the Beigin ... and statement blocks.

3) The stored procedure is called by the call keyword.

MySQL Learning 21: initial stored Procedure