one , Basic syntax of stored procedures :
1, Create stored procedure create procedure Stored procedure name (in/out/inout Parameter name Parameter type ( length )) begin SQL sentence ; end;
explain : in: This type parameter is used as input , That is, the value is passed in when calling out: This type parameter is used as output , That is, this parameter can be used as the return value
inout: It can be used as an input parameter , It can also be used as an output parameter
Parameter type length : When length is not specified mysql Will default to a length , as int Will default int(11), Why 11, because int The maximum length of the signed type of is -2147483648, yes 11 Bitwise .
2, View stored procedures select * from information_schema.routines where routine_schema =
'xxx'; -- View the stored procedure and status information of the specified database show create procedure Stored procedure name ; -- View the definition of a stored procedure sql sentence
3, delete drop procedure [if exists] Stored procedure name ;
be careful :

When defining stored procedures on the command line , Keyword is required delimiter Define end symbol ( Default is ;) , Change here to $$        

two , Use of variables
1, System variables : yes mysql Server provided , Not user-defined , Belongs to the server level . Divided into full variables global, Session variables session. View system variables show
[session|global] variables; -- View all system variables show [session|global] variables like
‘...’; -- Can pass like Fuzzy matching method to find variables select @@[session/global.] System variable name ;
-- View the specified changed value , Pay attention to the optional parameters . Set system variables set [session|global] System variable name = value ; -- unwanted @, Query yes @
The above method of setting global variables will be invalid after restart , Modify the parameter file without invalidation , Then restart mysqld vi /etc/my.cnf [mysqld]
wait_timeout=10 example :show session variables like 'auto%' select
@@global.autocommit; select @@session.autocommit; set session auto_increment =
1 ; 2, User variables : It is a variable defined by users according to their needs , User variables need not be declared in advance , It's time to use it directly ‘@ Variable name ’ Just use it . Its scope is the current connection . assignment set
@var_name = value ; set @var_name := value ; -- It is recommended to use this colon equal method to define select @var_name := value ;
select Field name into @var_name from Table name ; View variables select @var_name ; -- If direct select
A non-existent user variable name will be returned null Without reporting an error example :-- assignment set @myname := 'xiaoming'; set @myage := 18;
-- use select @myname,@myage
3, local variable : It is a variable that takes effect locally as needed , Before visit , need declare statement . It can be used as local variables and input parameters in stored procedures , The scope of a local variable is declared within it begin...end modular .
statement : declare Variable name Variable type ( length ) [default Default value ]
The variable type is the database field type :int,bigint,char,varchar,date,time assignment : set Variable name = value ; set Variable name := value ;
select Field name into Variable name from Table name ;
ps: Distinguishing skills : System variables are 2 individual @@, User variables 1 individual @, Local variables are used inside stored procedures or functions declare statement .

three , Other grammars

four , practice  
1, Create a stored procedure , Enter a score and return to the evaluation level of the score create procedure p1(in score int, out result
varchar(10)) begin if score >= 85 then set result := ' excellent '; elseif score >= 60
then set result := ' pass '; eles set result := ' fail, '; end if; end; call
p1(85,@result); -- A user-defined variable to receive out Returned value select @result;
2, Incoming 200 Points in the scoring system , Perform conversion , Change the hundred point system , Then return the score ---> inout create procedure p2(inout score
duble) begin set score := score * 0.5; end; set @score = 78; call p2(@score);
select @score; 3,while realization 1 reach n Accumulation of create procedure p3(in n int) begin declare
total int default 0; while n>0 do set total := total + n; set n := n -1; end
while; select total; end; 4,repeat Implement calculation 1 reach n Accumulation of ,n Pass in parameter values for --a. Define local variables , Record the accumulated value ;
--b. Once per cycle , Right n➖1,n by 0 Then exit the cycle create procedure p4(in n int) begin declare total int
default 0; repeat set total := total + n; set n := n-1; until n<=0 end repeat;
select total; end; 5,loop realization 1 reach n Accumulation of odd numbers of create procedure p5(in n int) begin
declare total int default 0; sum:loop if n<=0 then leave sum; end if; if n%2 =
1 then set n := n -1; iterate sum; end if; set total := total + n; set n := n
-1; end loop sum; select total; end; 6,case when use create procedure p6(in month
int) begin declare result varchar(10); case when month >= 1 and month <= 3 then
set result := ' first quarter '; when month >=4 and month <= 6 then set result := ' Second quarter ';
when month >= 7 and month <= 9 then set result := ' Third quarter '; when month >=10 and
month <= 12 then set result := ' Fourth quarter '; else set result := ' illegal parameter '; end case;
select result; end; call p6(6) 7, Use of cursors ,
Enter an age , hold tb_user The information of users younger than this age in the table is inserted into another table tb_user_pro in create procedure p7(int uage
int) begin declare u_cursor cursor for select name,profession from tb_user
where age <=uage; declare uname varchar(10); declare upro varchar(100); declare
exit handler for NOT FOUND close u_cuesor; -- Condition handler , Below while When the data of the cursor is empty during the loop, no error will be reported .
drop table id exists tb_user_pro; create table if exists tb_user_pro( id int
primary key auto_increment, name varchar(10), profession varchar(100) ); open
u_cursor; while true do fetch u_cursor into uname,upro; insert into tb_user_pro
values(null,uname,upro); end while; close u_cursor; end;

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?