一.demo

DELIMITER $$
CREATE PROCEDURE hello_procedure()
BEGIN
    SELECT 'hello_world';
END$$
DELIMITER ;

二.变量

1.普通变量定义

DELIMITER $$
CREATE PROCEDURE sp_var_01()
BEGIN
    DECLARE NAME VARCHAR(8) DEFAULT '小明';
    SET NAME='老五';
    SELECT NAME;
END$$
DELIMITER ;

2.普通变量定义 into

DELIMITER $$
CREATE PROCEDURE sp_var_02()
BEGIN
    DECLARE var_name VARCHAR(8) DEFAULT '小明';
    SELECT NAME INTO var_name FROM student WHERE id =1;
    SELECT var_name;
END$$
DELIMITER ;

3.用户变量。当前会话(连接)有效

# 不需要提前声明，使用即声明
DELIMITER $$
CREATE PROCEDURE sp_var_03()
BEGIN
    SET @name='用户变量不用提前声明';
END$$
DELIMITER ;
CALL sp_var_03();
SELECT @name;

4.会话变量

由系统提供，当前会话(连接)有效

语法：
@@session.var_name

SHOW SESSION VARIABLES;-- 查看会话变量
SELECT @@session.unique_checks;-- 查看某会话变量
SET @@session.unique_checks=0；-- 修改会话变量

5.全局变量

由系统提供，整个mysql服务器有效

语法：
@@global.var_name

SHOW GLOBAL VARIABLES LIKE '%char%'; -- 查看全局变量中变量名有char的记录
SELECT @@global.character_set_client;

三.入参出参

1.入参

DELIMITER $$
CREATE PROCEDURE sp_in(IN id INT)
BEGIN
    SELECT NAME FROM student s WHERE s.id= id;
END$$
DELIMITER ;
​
CALL sp_in(2);

2.出参

DELIMITER $$
CREATE PROCEDURE sp_in_out(IN id INT,OUT s_name VARCHAR(20))
BEGIN
    SELECT NAME INTO s_name FROM student s WHERE s.id= id;
END$$
DELIMITER ;

3.出入参

DELIMITER $$
CREATE PROCEDURE sp_in_inout(IN id INT,INOUT s_name VARCHAR(20))
BEGIN
    DECLARE var_stu_name VARCHAR(20);
    SET var_stu_name=s_name;
    SELECT NAME INTO s_name FROM student s WHERE s.id= id;
    SELECT CONCAT(s_name,'----->',var_stu_name);
END$$
DELIMITER ;
​
SET @stu_wel='欢迎您的到来';
CALL sp_in_inout(1,@stu_wel);
SELECT @stu_wel;

四.分支判断

使用表数据

select * from student;

1.if

DELIMITER $$
CREATE PROCEDURE sp_if(IN compareYear VARCHAR(18),IN id INT)
BEGIN
    DECLARE var_year INT;
    DECLARE result VARCHAR(6);
    SELECT TIMESTAMPDIFF(YEAR,compareYear,s.birthday) INTO var_year FROM student s WHERE s.id  = id;
    IF var_year>= 3 
        THEN SET result='老员工';
    ELSEIF  var_year>2 
        THEN SET  result='新员工';
    ELSE 
        SET  result='新手';
    END IF;
    SELECT result;
    
END$$
DELIMITER ;
​
CALL sp_if('2018-01-01',2);

2.case

语法一
case case_value
•   when when_value then statement_list
•   [when when_value then statement_list]
•   [else statement_list]
end case;
​
语法二
•   case when search_condition then statement_list
•   [when search_condition then statement_list]
•   [else statement_list]
end case;

五.循环

1.loop

死循环处理

show processlist;
kill id;

语法
[begin_label:] LOOP
•   statemet_list;
•   leave end_label;--  结束循环
•   iterate end_label; --   跳过本次循环
end loop [end_label];

DELIMITER $$
CREATE PROCEDURE sp_loop()
BEGIN
    DECLARE c_index INT DEFAULT 1;
    test:LOOP
        SET c_index=c_index+1;
        IF c_index>5
        THEN
          LEAVE test;
        END IF;
        SELECT c_index;
    END LOOP test;
END$$
DELIMITER ;
​
CALL sp_loop();

2.repeat

语法
[begin_lable:]  repeat
•   statement_list
until search_condition  -- 直到...为止，才退出循环
end repeat[end_label];

DELIMITER $$
CREATE PROCEDURE sp_repeat()
BEGIN
    DECLARE c_index INT DEFAULT 1;
    DECLARE c_result VARCHAR(256) DEFAULT '1';  
    count_rep:REPEAT
        SET c_index=c_index+1;
        SET c_result=CONCAT(c_result,',',c_index);
        UNTIL c_index>=5
    END REPEAT count_rep;
    SELECT c_result;
END$$
DELIMITER ;

3.while

语法
[begin_lable:] while search_condition do
•   statement_list
end while [end_lable];

DELIMITER $$
CREATE PROCEDURE sp_while()
BEGIN
    DECLARE c_index INT DEFAULT 1;
    DECLARE c_result VARCHAR(256) DEFAULT '1';  
    count_while:WHILE c_index<5 DO
        SET c_index=c_index+1;
        SET c_result=CONCAT(c_result,',',c_index);
    END WHILE count_while;
    SELECT c_result;
END$$
DELIMITER ;

六.游标

语法
declare cursor_name cursor for select _statement
--  打开语法
•   open cursor_name
--  取值语法
fetch cursor_name into var_name[,...]
--  关闭语法
close cursor_name

DELIMITER $$
CREATE PROCEDURE sp_cursor(IN birthday DATE)
BEGIN
    DECLARE var_name VARCHAR(20);
    DECLARE var_age INT;
    DECLARE lp_flag BOOLEAN DEFAULT TRUE;
    DECLARE cur_stu CURSOR FOR SELECT s.name,s.age FROM student s WHERE s.birthday=birthday;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET lp_flag=FALSE;
    OPEN cur_stu;
        
        lp_stu:LOOP
            FETCH cur_stu INTO var_name,var_age;
            IF lp_flag
                THEN SELECT var_name,var_age;
            ELSE 
                LEAVE lp_stu;
            END IF;
        END LOOP lp_stu;
    CLOSE cur_stu;
​
END$$
DELIMITER ;
​
CALL sp_cursor('2022-07-05');