Mysql stored procedure learning notes -- creating a simple stored procedure _ MySQL

The commonly used Operating Database Language SQL statements must be compiled and then executed before execution. the stored procedure (StoredProcedure) is a set of SQL statements for specific functions, compiled and stored in the database. you can specify the name of the stored procedure and specify parameter 1 and stored procedure

Stored Procedures (Stored Procedure) are a set of SQL statements for specific functions in large database systems. they are compiled and Stored in the database.
You can run a stored procedure by specifying its name and giving a parameter (if the stored procedure has a parameter. While SQL statements are executed in common database operating languages
Compilation and execution are required first, so the execution efficiency is not as high as that of the stored procedure.

The advantages of stored procedures are as follows:

Reuse. Stored procedures can be reused to reduce the workload of database developers. Improve performance. The stored procedure is compiled during creation and will not be re-translated for future use. A general SQL statement needs to be compiled every time it is executed, so the efficiency is improved by using the stored procedure. Reduce network traffic. The stored procedure is stored on the server. you only need to pass the name and parameters of the stored procedure when calling the procedure. this reduces the amount of data transmitted over the network. Security. Parameterized stored procedures can prevent SQL injection attacks and apply Grant, Deny, and Revoke permissions to stored procedures.

Simple syntax of the stored procedure:

Create procedure stored PROCEDURE name (input/output type variable name type, input/output type variable name type) BEGIN -- Declaration, add, delete, modify, and query operations to be completed by the statement... END

II. instances

The stored procedure in this example uses mysql as an example.
The table structure is as follows:

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

1. stored procedures with only IN (input parameters)

Indicates that the value of this parameter must be specified when the stored procedure is called. modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

DROP PROCEDURE IF EXISTS proc_person_findById;

-- Create a stored procedure
Create procedure proc_person_findById (
In n int
)
BEGIN
SELECT * FROM person where id = n;
END

-- Define variables
SET @ n = 2;
-- Call a stored procedure
CALL proc_person_findById (@ n );

The call result is as follows:

2. stored procedures with only OUT parameters

This value can be changed within the stored procedure and can be returned.

DROP PROCEDURE IF EXISTS proc_person_getCount

-- Create a stored procedure
Create procedure proc_person_getCount (
Out n int (11)
)
BEGIN
Select count (*) INTO n FROM person;
END

-- Call a stored procedure
CALL proc_person_getCount (@ n );
SELECT @ n as 'total ';

The call result is as follows:

3. with IN (input parameter) and OUT (output parameter)

Can be changed and returned.

DROP PROCEDURE IF EXISTS proc_person_findInfoById; 

-- Create a stored procedure
Create procedure proc_person_findInfoById (
IN n INT (11 ),
OUT pusername VARCHAR (255 ),
OUT page INT (11)
)
BEGIN
SELECT username, age INTO pusername, page FROM person WHERE id = n;
END

-- Define variables
SET @ id = 2;
-- Call a stored procedure
CALL proc_person_findInfoById (@ id, @ username, @ age );
SELECT @ username as 'username', @ age ';

The call result is as follows:

　　

4. stored procedures with INOUT parameters

-- Input and output drop procedure if exists proc_person_get_age; -- create procedure proc_person_get_age (INOUT n INT (11) begin select age into n from person WHERE id = n; ENDSET @ id = 1; CALL proc_person_get_age (@ id); SELECT @ id;

The call result is as follows:

5. input and output parameters

The IN parameter is input. if the IN parameter is not added, the input parameter is used by default. OUT is the output. when defining parameters, it must be added. INOUT is input and output and must be added. This parameter can be input or output after processing.