Mysql Stored Procedure Learning notes--creating simple stored procedures _mysql

First, stored procedures

Stored Procedures (Stored Procedure) are in a large database system, a set of SQL statements to complete a specific function, compiled and stored in the database, the user
Executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). And our common Operations database language SQL statements are executed at the time
Waiting to be compiled first and then executed, so the efficiency of execution is no higher than the stored procedure.

The advantages of stored procedures are as follows:

Repeated use. Stored procedures can be reused to reduce the workload of database developers. Improve performance. Stored procedures are compiled at the time they are created and are not translated again when they are used in the future. Normal SQL statements need to be compiled once per execution, so using stored procedures improves efficiency. Reduce network traffic. The stored procedure is on the server, and when invoked, it is only necessary to pass the name of the stored procedure and the parameters, thus reducing the amount of data transmitted by the network. Security. Parameterized stored procedures can prevent SQL injection attacks and can apply grant, deny, and revoke permissions to stored procedures.

Stored procedure simple syntax:

CREATE PROCEDURE Stored Procedure name (
input Output type variable name type,

input Output type variable name type

)
BEGIN
-declaration, statement to complete the operation, additions and deletions to check ... End

Second, the example

Examples of stored procedures are used in MySQL as an example.
The table structure is as follows:

DROP TABLE IF EXISTS ' person ';
CREATE TABLE ' person ' (
' id ' int (one) not NULL auto_increment,
' username ' varchar (255) DEFAULT NULL,
' Age ' int (one) DEFAULT NULL,
' Password ' varchar (255) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=11 DEFAULT Charset=utf8;

1. Stored procedures with in (input parameters only)

The value that represents the parameter must be specified when the stored procedure is invoked, and the value that modifies the parameter in the stored procedure cannot be returned as 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

--Defining variables
SET @n=2;
--Calling a stored procedure
Call Proc_person_findbyid (@n);

The result of the call is as follows:

2. Stored procedures with out (output parameters) only

The value can be changed inside 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)
)

SELECT COUNT (*) into the n from person;

--Calling a stored procedure
Call Proc_person_getcount (@n);
SELECT @n as ' total ';

The result of the call is as follows:

3, with in (input parameters) and out (output parameters) of the

Specified at call time, and can be changed and returned

--Create a stored procedure
CREATE PROCEDURE Proc_person_findinfobyid (


Out page INT (11)
)

SELECT username, age in Pusername, page from person WHERE id=n;
End

--Defining variables
SET @id = 2;
--Calling a stored procedure
Call Proc_person_findinfobyid (@id, @username, @age);
SELECT @username as ' username ', @age ' age ';

The result of the call is as follows:

　　

4. Stored procedures with inout (input and output) parameters

--Input and output drop PROCEDURE IF EXISTS proc_person_get_age;--Create stored procedure created PROCEDURE proc_person_get_age (  INOUT n INT (11)) BEGIN   SELECT age into N from WHERE id=n; Endset @id = 1; Call Proc_person_get_age (@id); SELECT @id;

The result of the call is as follows:

5. About input and output parameters

In for the input, when defining the parameters, you can not add, and then default to the input parameters. Out for output, when defining parameters, it must be added. InOut for input and output, must be added. Indicates that the parameter can be entered or stored after processing to output the result.