Mysql storage-variables, functions, cursors, judgments, loops

Stored procedure (procedure)

1 Introduction:
A stored procedure is a collection of SQL statements that have been compiled in advance and stored in the database. Calling a stored procedure can reduce the transmission of data between the database and the application server, which is beneficial to improving the efficiency of data processing.

The concept of stored procedures is very simple, it is code encapsulation and reuse at the SQL language level of the database
2. Features
Encapsulation and reuse
Can receive parameters and return data
Reduce network interaction and improve efficiency
3. Basic grammar
Create storage

CREATE PROCEDURE stored procedure name ([parameter list])
BEGIN
   --SQL statement
END;

call storage

call stored procedure name

View storage

-- View storage
-- View the specific information about switching to the specified storage creation in the specified library
show create procedure p1;
-- View all storage in the library
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='test';

delete storage

DROP PROCEDURE [IF EXISTS] stored procedure name;

Case

-- Create storage
create procedure p4()
begin
    declare name_sto int;-- define variables
    select count(*) into name_sto from emp; -- Query the data and assign it to the defined variable into variable name: data source complex assignment variable
end;
-- call storage
call p4();
-- View storage
show create procedure p4;
-- Delete storage
drop procedure p4;

Variables

1. System variables

introduce:
System variables are provided by the MySQL server and are not user-defined. They belong to the server level.
Can be divided into: global variables (GLOBAL), session variables (SESSION).
Global variables are valid in all sessions, session variables are valid only in the current session

View system variables:

-- View all system variables
SHOW [SESSION | GLOBAL] VARIABLES;

--You can find variables through like fuzzy matching
SHOW [SESSION | GLOBAL] VARIABLES LIKE '...';

-- View the value of the specified variable
SELECT @@[SESSION. | GLOBAL.] System variable name;

Set system variables:

SET [SESSION | GLOBAL] system variable name = value;
SET @@[SESSION. | GLOBAL.] system variable name = value;

Case:

--Variables: system variables
-- View system variables
show session variables;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;

--Set system variables
set session autocommit = 1;
set global autocommit = 1;
set @@global.autocommit = 1;

Summary: The variables that come with the mysql system can be viewed and set through the system variable requirements.

2. User-defined variables

User-defined variables are variables defined by users according to their own needs. User variables do not need to be declared in advance. They can be used directly with “@variable name” when using them. The scope is the current connection (session).
grammar:
Assignment

SET @var_name = expr[, @var_name = expr] ...;
SET @var_name := expr[, @var_name := expr] ...;

SELECT @var_name := expr[, @var_name := expr] ...;
SELECT field name INTO @var_name FROM table name;

transfer:
select @var_name

Case:

set @myname = 'itcast'; -- Custom variable
set @int=14;
select @myname;-- View custom variables
select @`int`;

Local variables

Local variables are variables that are defined as needed and take effect locally. Before accessing, a DECLARE statement is required. Can be used as local variables and input parameters within stored procedures. The scope of local variables is the BEGIN ... END block declared within it.

grammar:

Declare local variables

DECLARE variable name variable type [DEFAULT ...];

Local variable assignment:

SET variable name = value;
SET variable name := value;
SELECT field name INTO variable name FROM table name ...;

The variable type is the database field type: INT, BIGINT, CHAR, VARCAHR, DATE, TIME, etc.

Case

create procedure p5()-- Create storage
begin
    declare id int default 0;-- declare local variables
    set id=100;-- variable assignment
    select count(*) into id from emp;-- variable assignment
    select id;-- View variables or call local variables to return to storage
end;

call p5;-- Call stored procedure

if

grammar:

IF condition 1 THEN
  ...
ELSEIF condition 2 THEN -- optional
  ...
ELSE -- optional
  ...
END IF;

Case

create procedure p6()
begin
    -- Declare local variables
    declare score int default 60;-- define local variables and assign default values
    declare result varchar(100);-- define local variables
    if score>80 then-- if writing
        set result='Excellent';
        elseif score>70 then
        set result='Good';
        else
        set result='unqualified';
    end if;
    select result;-- View variable value and return to storage
end;
call p6;-- call storage

Parameters (IN/OUT/INOUT)

parameter:

IN This type of parameter is used as input, that is, the value needs to be passed in when calling. Default
OUT This type of parameter is used as output, that is, this parameter can be used as a return value
INOUT can be used as both an input parameter and an output parameter

CREATE PROCEDURE stored procedure name ([IN/OUT/INOUT parameter name parameter type])
BEGIN
  --SQL statement
END;

case:

Grammar 1:

CASE case_value
     WHEN when_value1 THEN statement_list1
     [WHEN when_value2 THEN statement_list2]...
     [ELSE statement_list]
END CASE;

Grammar 2:

CASE
     WHEN search_condition1 WHEN statement_list1
     [WHEN search_condition2 WHEN statement_list2]...
     [ELSE statement_list]
END CASE;

Case:

--Create stored procedure
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 transmission';
  end case;
  
  select concat('The month you entered is:',month,", and the quarter it belongs to is:",result);
end;

-- transfer
call p6(8);

cycle
1. while
The while loop is a conditional loop control statement. After the conditions are met, the SQL statement in the loop body is executed. The specific syntax is:

# Determine the condition first. If the condition is true, the logic will be executed. Otherwise, the logic will not be executed.
WHILE condition DO
   SQL logic...
END WHILE;

2.repeat
Repeat is a conditional loop control statement that exits the loop when the condition is met. The specific syntax is:

# First execute the logic once, and then determine whether the logic is satisfied. If so, exit. If not satisfied, continue to the next cycle
REPEAT
   SQL logic...
UNTIL condition
END REPEAT;

stored function

A stored function is a stored procedure with a return value. The parameters of a stored function can only be of type IN.

grammar:

CREATE FUNCTION stores function name ([parameter list])
RETURNS type [characteristics ...]
BEGIN
--SQL statement
RETURN ...;
END;

Description of characteristics:

DETERMINISTIC: The same input parameters always produce the same results
NO SQL: does not contain SQL statements
READS SQL DATA: Contains statements for reading data, but does not contain statements for writing data.

Case:

-- stored function
-- Accumulation from 1 to n
create function fun1(n int)
returns int deterministic --return type
begin
declare total int default 0;
\t
while n > 0 do
set total = total + n;
set n = n - 1;
end while;
\t
return total;
end;

-- Call the stored function and display the result
select fun1(100);