MySQL—stored procedure (local variables, user variables, system variables (global variables, session variables), parameter passing (in, out, inout))

1. Stored procedure features

Stored procedures are code encapsulation and reuse at the SQL language level of the database.

There are input and output parameters, variables can be declared, and control statements such as if/else, case, while, etc., can be realized by writing stored procedures

complex logic functions;

Universal features of functions: modularization, encapsulation, code reuse;

The speed is fast, only the first execution needs to go through compilation and optimization steps, and subsequent calls can be executed directly, eliminating the above steps;

-- Syntax:

delimiter custom end symbol
create procedure storage name([ in ,out ,inout ] parameter name data type...)
begin
  sql statement
end custom end match
delimiter;

delimiter $$
create procedure proc01()
begin
  select empno,ename from emp;
end $$
delimiter;

-- call the stored procedure
call proc01();

2. Define a local variable:

Local variables: user-defined, valid in begin/end blocks

--Syntax: declare variable declare var_name type [default var_value];
declare nickname varchar(32);

delimiter $$
create procedure proc02()
begin
    declare var_name01 varchar(20) default 'aaa'; -- define local variables
    set var_name01 = 'zhangsan';
    select var_name01;
end $$
delimiter;
-- call the stored procedure
call proc02();

MySQL can also use the SELECT..INTO statement to assign values to variables:

select col_name [...] into var_name[,...]
from table_name wehre condition

The col_name parameter indicates the field name of the query;

The var_name parameter is the name of the variable;

The table_name parameter refers to the name of the table;

The condition parameter refers to the query condition.

Note: When assigning the query result to a variable, the query result can only be a single row and a single column.

delimiter $$
create procedure proc03()
begin
 
  declare my_ename varchar(20) ;
  select ename into my_ename from emp where empno=1001;
  select my_ename;
end $$
delimiter;
-- call the stored procedure
call proc03();

3. Define a user variable:

User-defined, valid for the current session (connection). Analogous to java’s member variables

Syntax: @var_name does not need to be declared in advance, it is declared when used

delimiter $$
create procedure proc04()
begin
    set @var_name01 = 'ZS';
end $$
delimiter;
call proc04();
select @var_name01 ; --You can see the result

4. System variables

System variables are divided into global variables and session variables.

Global variables are automatically initialized to default values by the server when MYSQL starts, and these default values can be changed by

my.ini this file to change.

Session variables are initialized by MYSQL each time a new connection is established. MYSQL will save all current global variables

A copy of the value. as a session variable.

In other words, if the values of session variables and global variables have not been manually changed after the session is established, the values of all these variables

all the same.

The difference between global variables and session variables is that modifications to global variables will affect the entire server, but modifications to session variables

Changes will only affect the current session (that is, the current database connection).

The values of some system variables can be changed dynamically by using statements, but the values of some system variables are read-only.

Some system variables that can be changed, we can use the set statement to change.

4.1 Global variables

Provided by the system and valid throughout the database.

Syntax: @@global.var_name

-- view global variables
show global variables;
-- view a global variable
select @@global.auto_increment_increment;
-- Modify the value of the global variable
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;

4.2 Session variables

Provided by the system, the current session (connection) is valid

Syntax: @@session.var_name

-- view session variables
show session variables;
-- View a session variable
select @@session.auto_increment_increment;
-- Modify the value of the session variable
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000;

5. Stored procedure parameter passing

in
Input parameter, the parameter should be passed to the process of the stored process, the value of the parameter modified in the stored process cannot be returned.

out output parameter,
This value can be changed inside the stored procedure and exported.

inout
Input and output parameters can both input a value and pass out a value.

5.1 in

in means the incoming parameter, you can pass in a value or a variable, even if you pass in a variable, it will not change the value of the variable, you can change it internally

Change, only in the scope of the function.

-- Encapsulate the stored procedure with parameters, pass in the employee number, and find employee information
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
        select * from emp where empno = param_empno;
end $$
 
delimiter;
call dec_param01('1001');

-- Encapsulate the stored procedure with parameters, you can query the employee information of the specified department and the salary is greater than the specified value by passing in the department name and salary
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
begin
        select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
 
delimiter;
call dec_param0x('Ministry of Education',20000);

5.2 out

out means to pass the value from the stored procedure to the caller

-- ---------Outgoing parameters: out------------------------------- --
-- Encapsulate the stored procedure with parameters, pass in the employee number, and return the employee name
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
begin
  select ename into out_ename from emp where emp.empno = empno;
end $$
 
delimiter;
 
call proc08(1001, @o_ename);
select @o_ename;

-- Encapsulate the stored procedure with parameters, pass in the employee number, and return the employee's name and salary
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
begin
  select ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$
 
delimiter;
 
call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;

5.3 inout

inout means a variable that can be returned after the parameters passed in from the outside are modified. You can use the value of the passed variable or modify the variable.

value of the quantity (even if the function finishes executing).

-- Pass in the employee name, concatenate the department number, pass in the salary, and calculate the annual salary
delimiter $$
create procedure proc10(inout inout_ename varchar(50), inout inout_sal int)
begin
  select concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename;
  set inout_sal = inout_sal * 12;
end $$
delimiter;
set @inout_ename = 'Guan Yu';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal);
select @inout_ename;
select @inout_sal;

(Daily photo time)