MySQL simple demo of stored procedures

The tool used is navicat for MySQL.

First create a student table

1Mysql> Create TableStudent (S_namevarchar( -) not NULL default 'Unknown', sexvarchar(4) not NULL default 'Unknown', S_Noint(5) Auto_increment,ageint(3) not NULL, heightint(3) not NULL,Primary Key(S_No));2Query OK,0rows Affected3 4Mysql> Insert  intoStudent (S_name,sex,age,height)Values('Xiao Zhang','male', +,176);5Query OK,1Row Affected6 7Mysql> Insert  intoStudent (S_name,sex,age,height)Values('Xiao Li','male', A,175);8Query OK,1Row Affected9 TenMysql> Insert  intoStudent (S_name,sex,age,height)Values('Xiao Ming','male', -,178); OneQuery OK,1Row Affected A  -Mysql> Insert  intoStudent (S_name,sex,age,height)Values('Little Red','female', at,165); -Query OK,1Row Affected the  -Mysql> Insert  intoStudent (S_name,sex,age,height)Values('Little Li','female', +, the); -Query OK,1Row Affected -  +Mysql> Select *  fromstudent; - +--------+-----+------+-----+--------+ + |S_name|Sex|S_No|Age|Height| A +--------+-----+------+-----+--------+ at |Xiao Zhang|Man|    1 |   + |    176 | - |Xiao Li|Man|    2 |   A |    175 | - |Xiao ming|Man|    3 |   - |    178 | - |Little Red|Woman|    4 |   at |    165 | - |Xiaoli|Woman|    5 |   + |     the | - +--------+-----+------+-----+--------+ in 5Rowsinch Set

Then write a stored procedure, pass in name S_name, return to study number S_No

1 delimiter $$2 Drop procedure if existsPro_gets_no;3 Create procedurePro_gets_no (inchPNamevarchar( -), out pnoint(5))4 begin5     SelectS_No intoPno fromStudentwhereS_name=pname;6 End $$7Delimiter

Called after running

Set @pname = ' Little Red ' ; Set @pno = 0 ; call Pro_gets_no (@pname,@pno); Select *  from where s_no=@pno;

The results are as follows

Create a stored procedure, change the student height height of the incoming name to 175, and change the name to the height name as the name output

1 delimiter $$2 Drop procedure if existsPro_updateheight;3 Create procedurePro_updateheight (inout pnamevarchar( -))4 begin 5     UpdateStudentSetHeight=175 whereS_name=pname;6     SelectConcat (Height,s_name) intoPName fromStudentwhereS_name=pname;7 End $$8Delimiter

Called after running

1 Set @pname = ' Xiao Li ' ; 2 Call Pro_updateheight (@pname); 3 Select @pname;

The results are as follows

MySQL Simple demo of stored procedures