MySQL And database series article directory

【MySQL And the database 】MySQL & database Chapter one ： Installation and uninstallation of database
【MySQL And the database 】MySQL & database Chapter two ： Database and MySQL A basic overview of
【MySQL And the database 】MySQL & database The third chapter ：DQL Study （ Mainly record query ）
【MySQL And the database 】MySQL & database Chapter four ：DML Study （ Mainly the addition, deletion and modification of records ）
【MySQL And the database 】MySQL & database The fifth chapter ：DDL Study （ Mainly for the library 、 surface 、 Addition, deletion and modification of fields ）
【MySQL And the database 】MySQL & database Chapter six ：TCL Study （ Mainly for transaction control ）
【MySQL And the database 】MySQL & database Chapter vii. ： View learning
【MySQL And the database 】MySQL & database Chapter viii. ：MySQL The variables in the
【MySQL And the database 】MySQL & database Chapter nine ： The learning of stored procedure
【MySQL And the database 】MySQL & database Chapter ten ： Function learning
【MySQL And the database 】MySQL & database Chapter 11 ： Process control structure

stored procedure

• meaning ： A set of precompiled SQL Collection of statements , Understood as a batch statement ;

• characteristic ：

  • Improve code reuse ;
  • Simplified operation ;
  • Reduce the number of compilations and connections to the database server , Improved efficiency ;

• Create Syntax

  create procedure  Stored procedure name （ parameter list ）
  begin
  	 Stored procedure body （ A combination of sql sentence ）
  end
  

  • The parameter list consists of three parts ： Parameter mode 、 Parameter name 、 Parameter type

     give an example ：
    in stuname varchar(20);
    
     Parameter mode ：
    in： This parameter is used as input , Need to call method input value ;
    out： This parameter is used as the output , As return value ;
    inout： It can be used as input or output , Both incoming and return values ;
    

  • If the stored procedure body has only one statement ,begin and end It can be omitted ;

  • Each of the stored procedure bodies sql A semicolon is required at the end of a statement ;

  • The end of the stored procedure can use delimiter To reset ;

     grammar ：
    delimiter  End mark 
     Case study ：
    delimiter $
    

• Call syntax

  call  Stored procedure name （ Argument list ）;
  

• Case study 1： Create a stored procedure with an empty argument list

  delimiter $
  create procedure myq() 
  begin
  	 A group of sql sentence ;
  end $
  
  call myq() $
  

• Case study 2： Create a stored procedure with a single input argument

  delimiter $
  create procedure myq2(in beautyName varchar(20))
  begin 
  	select bo.*
  	from boys bo 
  	right beauty b on bo.id = b.bofrinend_id
  	where b,name = beautyName;
  end $
  
  call myq2('Alice') $
  

• Case study 3： Create a stored procedure with multiple input arguments

  delimiter $
  create procedure myq3(in userName varchar(20), in password varchar(20))
  begin
  	declare result int default 0;
  	select count(*) into result
  	from admin
  	where admin.userName = userName
  	and admin.password = password;
  	select if(result > 0, 'succeed', 'fault');
  end $
  
  call myq3('Mike', '123456') $
  

• Case study 4： Create a stored procedure that returns a single parameter

  delimiter $
  create procedure myq4(in beautyName varchar(20), out boyName varchar(20))
  begin
  	select bo.boyName into boyName
  	from boys bo inner join beauty b 
  	on bo.id = b.boyfriend_id
  	where b.name = beautyName;
  end $
  
  set @bName $
  call myq4(' Small zhao ', @bName) $
  select @bName $
  

• Case study 5： Create a stored procedure that returns multiple parameters

  delimiter $
  create procedure myq5(in beautyName varchar(20), out boyName varchar(20), out userCP int)
  begin
  	select bo.boyName, bo.userCP into boyName, userCP
  	from boys bo inner join beauty b
  	on bo.id = b.boyfriend_id
  	where b.name = beautyName;
  end $
  
  call myq5(' Small zhao ', @bName, @usercp) $
  select @bName, @usercp $
  

• Case study 6： Create with inout Stored procedure of mode parameters

  delimiter $
  create procedure myq6(inout a int, inout b int) 
  begin
  	set a = a * 2;
  	set b = b * 2;
  end $
  
  set @m = 10 $
  set @n = 20 $
  call myq6(@m, @n) $
  select @m, @n $
  

• Delete Syntax

  drop procedure  Stored procedure name ;
  # Only one stored procedure can be deleted at a time ;
  

• Check the syntax

  show create procedure  Stored procedure name ;
  # You can only view , Cannot modify stored procedure ;