1. What is the Save-and-Save process

Characteristic:

1. Quick execution - The decimation process is performed on the database server side.
2. Poor portability - stored procedures with different data are not portable

Grammar:

delimiter $-- Declare stored procedures
create procedure name--Stored procedure name (parameter list)
begin --Start
Write sql statements; --Multiple or one sql statement
End $--end Terminator

Execute Stored Procedure

Call name (); --call stored procedure name ([parameter])

Parameters:

in: Represents an input parameter that can carry data with a stored procedure
out: Represents an output parameter and can return results from stored procedures
inout: Represents an input-output parameter that can either input or output functions.

Two. Six Stored Procedures

1. Stored procedures with input parameters

--Create Stored Procedures
delimiter $ 
create procedure pro_findbyid(in eid int)
begin
    select * from employee where id = eid;
end $
--implement
call eurasia_echarts.pro_findbyid(1);

2. Stored procedures with output parameters

--Create Stored Procedures
delimiter $ 
create procedure pro_out(out str varchar(20))
begin
    set str ='Output Me';
end $
--implement
set @str = '0';--(Session Variable)
call eurasia_echarts.pro_out(@str);
select @str;

3. Stored procedures with input and output parameters

--Create Stored Procedures
delimiter $ 
create procedure pro_in_out(inout  m int)
begin
    select m;
    set m=500;
end $
--to m Assignment to 10
set @m=10;

--Execute statement: result is 10
call eurasia_echarts.pro_in_out(@m);

--Execute statement m The value is 500
select @m;

4. Stored Procedures with Conditional Judgement

delimiter $ 
delimiter $ 
create procedure pro_testif(in  num int,out str varchar(20))
begin
    if num=1 then
            set str='Monday';
    elseif num=2 then
            set str='Tuesday';
     elseif num=3 then
            set str='Wednesday';   
     elseif num=4 then
            set str='Thursday';
     else 
            set str='Input error';
     end if;
end $

call eurasia_echarts.pro_testif(2,@str);

select @str;

5. Stored Procedures with Looping Procedures

delimiter $ 
create procedure pro_testwhile(in  num int,out sum int)
begin
    declare i int default 1;-- Define Variables
    declare vsum int default 0;
    while i<=num do
        set vsum = vsum+i;
        set i = i+1;
    end while;
    set sum = vsum;
end $

call eurasia_echarts.pro_testwhile(50,@sum);

select @sum;

6. Use the results of the query as return values

select * from employee;

delimiter $ 
create procedure pro_findemployee(in  eid int,out vname varchar(20))
begin
    select employee.name into vname from employee where eid=id;
end $

call eurasia_echarts.pro_findemployee(1,@name);
select @name;