MySQL stored procedures

stored procedures, which can be said to encapsulate some functionality together, are equivalent to stored procedures, in MySQL functions and stored procedures are two different things, do not confuse.

To establish a stored procedure:

1. Declaring Stored procedures

Mysql> delimiter//"delimiter//" means to change the MySQL terminator to "//" first

Mysql> CREATE PROCEDURE Delstu ()

Begin

Delete from member;

-End//

Mysql> delimiter; "Delimiter;" Means to change the Terminator back to ";" State

2. Call the stored procedure

Mysql> call Delstu;

3. Delete stored Procedures

Mysql> drop procedure Delstu;

Establish a stored procedure that can pass parameters

Mysql> delimiter//

Mysql> CREATE PROCEDURE Delstuline (in SID int)//in for incoming values

Begin

--delete from student where ID = SID;

-End//

Mysql> delimiter;

Mysql> call Delstuline (14); When the stored procedure is called, the data with ID 14 is cleared

Create a stored procedure that inserts data

Mysql> delimiter//

Mysql> CREATE PROCEDURE Insertline (in name varchar), gender enum ("Male", "female"), age Tinyint,score tinyint,address varchar (200))

Begin

INSERT into student (name,gender,age,score,address) values (name,gender,age,score,address);

-End//

Mysql> delimiter;

Mysql> Call Insertline ("small white", "female", 20, 65, "Beijing Daxing District"); Calling a stored procedure inserts data

Create a stored procedure for statistical data

Mysql> delimiter//

Mysql> CREATE PROCEDURE Countline (out TJ Int)//out as return value

Begin

Select COUNT (*) into the TJ from student;

-End//

Query OK, 0 rows Affected (0.00 sec)

Mysql> delimiter;

Mysql> call Countline (@tj); Call a stored procedure

Mysql> Select @tj; Output return value

Create a stored procedure that looks at all the data

Mysql> delimiter//

Mysql> CREATE PROCEDURE showtable ()

Begin

SELECT * from student;

-End//

Mysql> delimiter;

Establish an if loop stored procedure

Mysql> delimiter//

Mysql> CREATE PROCEDURE InsertLines ()

Begin

Set @i = 0;

Aa:loop

INSERT into student (name,gender,age,score,address) VALUES ("Zhang Sanfeng", "male", 80, 97, "Wudangshan");

Set @i = @i + 1;

If @i <

-Iterate AA;

-End If;

-Leave AA;

-End loop AA;

-End//

Mysql> delimiter;

MySQL stored procedures