Detailed explanation of MYSQL stored procedure comments

// Compare editUser to a stored procedure public void editUser(User user,String username){
    String a = "nihao";
    user.setUsername(username);
}
main(){
    User user = new User();
 editUser(user,"张三");
    user.getUseranme(); //java basics}

delimiter $$ --declaration end character

#### 3.0 Syntax Structure ```sql
-- Stored procedure structure CREATE
    [DEFINER = user]
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
-- 1. The proc_parameter parameter part can be written as follows:
 [ IN | OUT | INOUT ] param_name type
 -- The type type can be any type supported by MySQL -- 2. In the routine_body part, you can write legal SQL statements BEGIN ... END

-- Declaration terminator. Because MySQL uses ';' as the terminator by default, and in the stored procedure, ';' is used as the end of a statement, resulting in a conflict between ';' and delimiter $$

CREATE PROCEDURE hello_procedure ()
BEGIN
 SELECT 'hello procedure';
END $$

call hello_procedure();

-- set the value of delimiter $$
create procedure sp_var01()
begin
 declare nickname varchar(32) default 'unkown';
 set nickname = 'ZS';
 -- set nickname := 'SF';
 select nickname;
end$$
-- into assigns delimiter $$
create procedure sp_var_into()
begin
 declare emp_name varchar(32) default 'unkown';
 declare emp_no int default 0;
 select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839;
 select emp_no,emp_name;
end$$

-- Assign delimiter $$
create procedure sp_var02()
begin
 set @nickname = 'ZS';
 -- set nickname := 'SF';
end$$
call sp_var02() $$
select @nickname$$ -- you can see the result

show session variables; -- View session variables select @@session.unique_checks; -- View a session variable set @@session.unique_checks = 0; -- Modify session variables

show global variables like '%char%'; 

-- View the value of the global variable character_set_client select @@global.character_set_client; 

-- IN type demo delimiter $$
create procedure sp_param01(in age int)
begin
 set @user_age = age;
end$$
call sp_param01(10) $$
select @user_age$$
-- OUT type, only responsible for output!
-- Requirement: Output the department number corresponding to the passed in address string.
delimiter $$

create procedure sp_param02(in loc varchar(64),out dept_no int(11))
begin
 select d.deptno into dept_no from dept d where d.loc = loc;
 --It is emphasized here that either the table is aliased or the parameter name is not consistent with the field name end$$
delimiter ;

--test set @dept_no = 100;
call sp_param02('DALLAS',@dept_no);
select @dept_no;
-- INOUT type delimiter $$
create procedure sp_param03(inout name varchar(49))
begin
 set name = concat('hello' ,name);
end$$
delimiter ;

set @user_name = 'Xiaoming';
call sp_param03(@user_name);
select @user_name;

-- Syntax IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

-- Prerequisite knowledge point: timestampdiff(unit,exp1,exp2) takes the difference exp2-exp1, the unit is unit
select timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499 ';
delimiter $$
-- DROP PROCEDURE IF EXISTS sp_param04;
create procedure sp_param05(in ages timestamp)
begin
 declare result varchar(32);
 if timestampdiff(year,ages,now())>40 
  then set result = 'Veteran';
 elseif timestampdiff(year,ages,now())>38 
  then set result = 'Old employee';
 ELSE 
  SET result = 'Newbie';
 end if;
 select result;
end $$
delimiter;
 
call sp_param05('1970-02-26 10:00:25');
-- Note: MYSQL timestamp must start from 1970.

-- Syntax 1 (similar to Java switch):
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
-- Syntax 2:
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

-- Requirement: Years of employment <= 38 for new employees > 38 <= 40 for veteran employees > 40 for veterans delimiter $$
create procedure sp_hire_case()
begin
 declare result varchar(32);
 declare message varchar(64);
 case
    when timestampdiff(year,'2001-01-01',now()) > 40 
  then 
   set result = 'Veteran';
   set message = 'Grandpa';
 when timestampdiff(year,'2001-01-01',now()) > 38
  then 
   set result = 'Old employee';
   set message = 'Greasy middle-aged man';
 else 
  set result = 'Newbie';
  set message = 'Newbie';
 end case;
 select result;
end$$
delimiter ;

-- Syntax [begin_label:] LOOP
    statement_list
END LOOP [end_label]

--Requirement: Loop print 1 to 10
--leave control loop exit delimiter $$
create procedure sp_flow_loop()
begin
 declare c_index int default 1;
 declare result_str varchar(256) default '1';
 cnt:loop
 
  if c_index >= 10
  then leave cnt;
  end if;

  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
  
 end loop cnt;
 
 select result_str;
end$$

-- iterate + leave control loop delimiter $$
create procedure sp_flow_loop02()
begin
 declare c_index int default 1;
 declare result_str varchar(256) default '1';
 cnt:loop

  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
  if c_index < 10 then 
   iterate cnt; 
  end if;
  -- Can the following sentence be executed? When will it be implemented? When c_index < 10 is false, execute leave cnt;
  
 end loop cnt;
 select result_str;
 
end$$

[begin_label:] REPEAT
    statement_list
UNTIL search_condition -- until ..., then exit the loop END REPEAT [end_label]
-- Requirement: Loop and print 1 to 10
delimiter $$
create procedure sp_flow_repeat()
begin
 declare c_index int default 1;
 -- Collect result string declare result_str varchar(256) default '1';
 count_lab:repeat
  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
  until c_index >= 10
 end repeat count_lab;
 select result_str;
end$$

Analogy to Java's while(){}
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]
-- Requirement: Loop and print 1 to 10
delimiter $$
create procedure sp_flow_while()
begin
 declare c_index int default 1;
 -- Collect result string declare result_str varchar(256) default '1';
 while c_index < 10 do
  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
 end while;
 select result_str;
end$$

Analogy to Java's breake
-- LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).
LEAVE label

Analogy to Java continue
-- ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label

Analogy to jdbc ResultSet
--Declaration syntax DECLARE cursor_name CURSOR FOR select_statement
-- Open syntax OPEN cursor_name
-- Value retrieval syntax FETCH cursor_name INTO var_name [, var_name] ...
-- Close syntax CLOSE cursor_name
-- Requirement: Query employees by department name, and view employee ID, name, and salary by selecting. (Note that this is just a demonstration of cursor usage)
-- Change the end character to $$
delimiter $$
-- Create a stored procedure (with one input parameter)
create procedure sp_create_table02(in dept_name varchar(32))
begin
-- The variable must be declared first declare e_no int;
 declare e_name varchar(32);
 declare e_sal decimal(7,2);
 
 declare lp_flag boolean default true;
-- Next, declare the cursor: the cursor value is table(e.empno,e.ename,e.sal) obtained by query(dept_name)
 declare emp_cursor cursor for 
  select e.empno,e.ename,e.sal
  from emp e, dept d
  where e.deptno = d.deptno and d.dname = dept_name;
  
-- Then declare the handler:
-- About handle: https://blog.csdn.net/qq_43427482/article/details/109898934
-- If you still don't understand after reading this, read again: https://www.cnblogs.com/phpper/p/7587556.html
-- SQL STATE is involved here: https://blog.csdn.net/u014653854/article/details/78986780
-- Declare the handler: When each SQL statement passes an error with ERROR STATE of no value, set the variable lp_flag to false and continue to execute the SQL statement (if not declared, when a loop reports an error, the entire SQL statement will stop the loop directly)
 declare continue handler for NOT FOUND set lp_flag = false;
-- Open the cursor open emp_cursor;
-- Open LOOP: emp_loop
 emp_loop:loop
-- Pass the cursor value to three variables fetch emp_cursor into e_no, e_name, e_sal;
-- If the variable lp_flag is true, get the values ​​of these three parameters; otherwise interrupt the emp_loop if lp_flag then
   select e_no,e_name,e_sal;
  else
   leave emp_loop;
  end if;
-- End loop end loop emp_loop;
-- Define user variables and assign values ​​(user variables do not need to be declared in advance and are only valid for the current session) > I don't understand the meaning of this step set @end_falg = 'exit_flag';
-- Close the cursor close emp_cursor;
-- End the stored procedure end$$
-- Restore; end delimiter;

-- Use this stored procedure and pass parameters call sp_create_table02('RESEARCH');

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE -- continue execution | EXIT -- exit execution | UNDO -- do nothing}

CONTINUE: Execution of the current program continues. -- Continues execution of the current program EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block. -- Stops execution of the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

SQLWARNING: Shorthand for the class of SQLSTATE values ​​that begin with '01'. -- That is, the class that begins with 01. NOT FOUND: Shorthand for the class of SQLSTATE values ​​that begin with '02'. -- That is, the class that begins with 02. SQLEXCEPTION: Shorthand for the class of SQLSTATE values ​​that do not begin with '00', '01', or '02'. -- That is, the class that does not begin with 00, 01, or 02. -- Various ways of writing:
 DECLARE exit HANDLER FOR SQLSTATE '42S01' set @res_table = 'EXISTS';
 DECLARE continue HANDLER FOR 1050 set @res_table = 'EXISTS';
 DECLARE continue HANDLER FOR not found set @res_table = 'EXISTS';

delimiter // -- Define the terminator create procedure high_sal(in dept_name varchar(32)) -- Create a stored procedure begin -- Start a stored procedure declare e_no int; -- Declare a variable declare e_name varchar(32);
 declare e_sal decimal(7,2);
 
 declare lp_flag boolean default true;
 
 declare emp_cursor cursor for -- declare cursor select e.empno,e.ename,e.sal
  from emp e, dept d
  where e.deptno = d.deptno and d.dname = dept_name;
  
 --Declare handler handle (conditional processing)
 declare continue handler for NOT FOUND set lp_flag = false;
  
 open emp_cursor; -- Open the cursor emp_loop:loop -- Start the loop fetch emp_cursor into e_no,e_name,e_sal; -- Variable assignment if lp_flag then -- Flow control if e_name = 'king' then 
    iterate emp_loop; -- continue loop else 
    update emp e set e.sal = e.sal + 100 where e.empno = e_no; -- Update data end if;
  else
   leave emp_loop; -- leave the loop end if; -- end the process end loop emp_loop; -- end the loop set @end_falg = 'exit_flag'; -- declare user variables close emp_cursor; -- variable ratio cursor end // -- end the stored procedure delimiter; -- restore the terminator call high_sal('ACCOUNTING');

-- Knowledge point: Using local variables after preprocessing prepare statement from will result in an error -- https://dev.mysql.com/doc/refman/5.6/en/sql-prepared-statements.html
-- If you can't understand English documents, read this: https://www.cnblogs.com/geaozhang/p/9891338.html; I should also do some reading of English technical documents to improve PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
{DEALLOCATE | DROP} PREPARE stmt_name

-- Here is a preprocessing example: Use string definition to preprocess SQL (calculate the hypotenuse of a right triangle)
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; -- POW(x,y) function, used to calculate x to the power of y (http://c.biancheng.net/mysql/pow_power.html); SQRT function is used to find the square root (https://blog.csdn.net/weixin_39554172/article/details/113124290)
SET @a = 3;
SET @b = 4; -- User variable usage is declaration EXECUTE stmt1 USING @a, @b; -- The result is 5
DEALLOCATE PREPARE stmt1; 

-- Knowledge point: Time processing -- EXTRACT(unit FROM date) extracts the specified position value of the time -- DATE_ADD(date,INTERVAL expr unit) date operation -- LAST_DAY(date) gets the date of the last day of the date -- YEAR(date) returns the year in the date -- MONTH(date) returns the month of the date -- DAYOFMONTH(date) returns the day -- Note: According to https://stackoverflow.com/questions/35838321/day-vs-dayofmonth-in-mysql, in fact, the effect of DAY(date) is the same -- Idea: Loop to build table names comp_2020_05_01 to comp_2020_05_31; and execute the create statement.
-- Analysis: 1. The table is constructed in a loop, and only the table name is different. Consider using a stored procedure to perform loop processing and use preprocessing to improve efficiency. 2. First, a variable is needed to store the executed SQL; then a variable is needed for the year, a variable is needed for the month, a variable is needed for the date, and a variable is needed for the concatenated table name; in addition, a number is needed for accumulation; so far, we have obtained the need for at least 6 variables. In order to complete the date 0, two variables of month and day are added to supplement 0 to form 01, 02, etc. 3. Considering the pre-processing format (no local variables after from), write 7 local variables and 1 user variable delimiter // --declaration terminator create procedure sp_create_table()
begin
-- Define a bunch of local variables declare next_year int;
 declare next_month int;
 declare next_month_maxday int;
  
 declare next_month_str char(2);
 declare next_month_maxday_str char(2);
 
 -- Process the table name for each day declare table_name_str char(10);
 -- Statistical sequence declare t_index int default 1;
 -- declare create_table_sql varchar(200);
 
 -- Get the year of the next month set next_year = year(date_add(now(),INTERVAL 1 month));
 -- Get the next month set next_month = month(date_add(now(),INTERVAL 1 month));
 -- What is the last day of next month? set next_month_maxday = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
 
 -- Add 0: 01,02····，09 for January-September
 if next_month < 10
  then set next_month_str = concat('0',next_month);
 else
  set next_month_str = concat('',next_month);
 end if;
 
 
 while t_index <= next_month_maxday do
  
  -- Same as above, add 0 to the days
  if (t_index < 10)
   then set next_month_maxday_str = concat('0',t_index);
  else
   set next_month_maxday_str = concat('',t_index);
  end if;
  
  -- 2020_05_01
  set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_maxday_str);
  -- Splice create sql statement (user variable)
  set @create_table_sql = concat(
     'create table comp_',
     table_name_str,
     '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
  -- Local variables cannot be used after FROM! That's why we use user variables prepare create_table_stmt FROM @create_table_sql;
  execute create_table_stmt;
  DEALLOCATE prepare create_table_stmt;
  
  set t_index = t_index + 1;
  
 end while; 
end//
delimiter;

call sp_create_table()

-- The following is a simplified version of the delimiter //
CREATE PROCEDURE sp_createtable1 () BEGIN-- DECLARE statistical sequence
  t_index INT DEFAULT 1;
 WHILE
   t_index <= DAY (
    LAST_DAY(
    date_add( now(), INTERVAL 1 MONTH ))) DO
   
   SET @create_table_sql = concat(
    'CREATE TABLE comp_',
    YEAR (
    date_add( now(), INTERVAL 1 MONTH )),
    '_',
    MONTH (
    date_add( now(), INTERVAL 1 MONTH )),
    '_',
    t_index,
    '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB' 
   );-- Local variables cannot be used after FROM! That's why we use user variables PREPARE create_table_stmt 
  FROM
   @create_table_sql;
  EXECUTE create_table_stmt;
  DEALLOCATE PREPARE create_table_stmt;
  
  SET t_index = t_index + 1;
  
 END WHILE;
 
END // 
delimiter;
CALL sp_createtable1 ()

Characteristics:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

-- If there is an infinite loop, you can view and end it by using the following command: show processlist;
kill id;

https://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html
select 
 case
  when timestampdiff(year,e.hiredate,now()) <= 38 then 'Newbie'
  when timestampdiff(year,e.hiredate,now()) <= 40 then 'Old employee'
  else 'Veteran'
 end hir_loc,
 e.*
from emp e;

create temporary table table name(
  Field name type [constraint],
  name varchar(20) 
)Engine=InnoDB default charset utf8;

-- Requirement: Query employees by department name, and view employee ID, name, and salary by selecting. (Note that this is just a demonstration of cursor usage)
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
 declare emp_no int;
 declare emp_name varchar(32);
 declare emp_sal decimal(7,2);
 declare exit_flag int default 0;
 
 declare emp_cursor cursor for
  select e.empno,e.ename,e.sal
  from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name;
 
 declare continue handler for not found set exit_flag = 1;
 
 -- Create a temporary table to collect data CREATE temporary TABLE `temp_table_emp` (
  `empno` INT(11) NOT NULL COMMENT 'Employee number',
  `ename` VARCHAR(32) NULL COMMENT 'Employee name' COLLATE 'utf8_general_ci',
  `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT 'Salary',
  PRIMARY KEY (`empno`) USING BTREE
 )
 COLLATE='utf8_general_ci'
 ENGINE=InnoDB; 
 
 open emp_cursor;
 
 c_loop:loop
  fetch emp_cursor into emp_no,emp_name,emp_sal;
  
  
  if exit_flag != 1 then
   insert into temp_table_emp values(emp_no,emp_name,emp_sal); 
  else
   leave c_loop;
  end if;
  
 end loop c_loop;
 
 select * from temp_table_emp;
 
 select @sex_res; -- Just to see if it will execute until close emp_cursor;
 
end$$

call sp_create_table02('RESEARCH');

CREATE TABLE `dept` (
 `deptno` INT(11) NOT NULL COMMENT 'Department number',
 `dname` VARCHAR(32) NULL COMMENT 'Department name' COLLATE 'utf8_general_ci',
 `loc` VARCHAR(64) NULL COMMENT 'Department address' COLLATE 'utf8_general_ci',
 PRIMARY KEY (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
INSERT INTO DEPT VALUES
 (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
 (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
 (40,'OPERATIONS','BOSTON');
 
CREATE TABLE `emp` (
 `empno` INT(11) NOT NULL COMMENT 'Employee number',
 `ename` VARCHAR(32) NULL COMMENT 'Employee name' COLLATE 'utf8_general_ci',
 `job` VARCHAR(10) NULL COMMENT 'Position' COLLATE 'utf8_general_ci',
 `mgr` INT(11) NULL COMMENT 'Superior number',
 `hiredate` DATE NOT NULL COMMENT 'Job start time',
 `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT 'Salary',
 `comm` DECIMAL(7,2) NULL COMMENT 'Year-end bonus',
 `deptno` INT(11) NOT NULL COMMENT 'Department number',
 PRIMARY KEY (`empno`) USING BTREE,
 INDEX `FK_emp_dept` (`deptno`) USING BTREE,
 CONSTRAINT `FK_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `procedure_demo`.`dept` (`deptno`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

insert into emp values
(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values
(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values
(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values
(7566,'jones','manager',7839,'1981-02-04',2975,null,20);
insert into emp values
(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values
(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values
(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
insert into emp values
(7788,'scott','analyst',7566,'1987-07-13')-85,3000,null,20);
insert into emp values
(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values
(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values
(7876,'adams','clerk',7788,'1987-07-13')-51,1100,null,20);
insert into emp values
(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values
(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values
(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);

CREATE TABLE `salgrade` (
 `grade` INT(11) NULL,
 `losal` INT(11) NULL,
 `hisal` INT(11) NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
insert into salgrade values ​​(1,700,1200);
insert into salgrade values ​​(2,1201,1400);
insert into salgrade values ​​(3,1401,2000);
insert into salgrade values ​​(4,2001,3000);
insert into salgrade values ​​(5,3001,9999);