MySQL常用操作之创建存储过程语法详解_mysql 创建存储过程语法

转载：https://blog.csdn.net/weixin_42586723/article/details/118554928

  CREATE
    [DEFINER = { user | CURRENT_USER }]
  PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
1
2
3
4

  [ IN | OUT | INOUT ] param_name type  
1

  BEGIN ...... END$$
1

  -- $$ 可以自定义为其他作为结束符
  DELIMITER $$
1
2

  CREATE PROCEDURE nbsp_procedure(IN 参数名 参数类型)
1

  BEGIN .... END 
1

  -- 声明语句结束符，可以自定义:
  delimiter $$
  -- 创建存储过程，名为  nbsp_procedure 参数为空， 执行输出 'hello world'
  create procedure nbsp_procedure () -- 默认当前用户下创建 create definer = `nbsp`@`%` procedure nbsp_procedure ()
  begin
      select 'hello world';
  end $$
  -- 声明语句结束符
  delimiter ;
  -- 调用存储过程
  call nbsp_procedure();
  -- 删除存储过程
  drop procedure nbsp_procedure;
1
2
3
4
5
6
7
8
9
10
11
12
13

  -- 语法
  declare  变量名  变量类型  [default 默认值]
  -- 举例
  declare name varchar(32) '张三'
  -- set 赋值
  delimiter $$
  drop procedure if exists var01$$
  create procedure var01()
  begin
      -- 声明变量
      declare username varchar(32) default '张三';
      -- 为变量赋值
      set username = '李四';
      select username;
  end $$
  delimiter ;
  call var01();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

  delimiter $$
  drop procedure if exists var02$$

  -- 创建存储过程
  create procedure var02()
  begin
      set @username='张三';
  end $$
  delimiter ;

  -- 调用存储过程
  call var02();
  -- 查询会话变量的值
  select @username;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

  set @username='wangwu';
1

  delimiter $$
  drop procedure if exists var03$$
  -- 传入两个变量，第一个为输入in变量，第二个为是输出out变量。当输入用户id 时，返回用户名。
  create procedure var03(in id int,out username varchar(32))
  begin
      select id,username;
      -- select 'procedure into username' into username;
      -- set username='procedure into username'
  end $$
  delimiter ;
1
2
3
4
5
6
7
8
9
10

  -- 在还未调用存储过程时，我们发现此时的会话变量@username的值依然为 wangwu
  select @username;
1
2

  call var03(35,@username);
1

   -- 此时会话变量为NULL
   select @username;
1
2

  IF search_condition THEN statement_list
      [ELSEIF search_condition THEN statement_list]...
      [ELSE statement_list]
  END IF
  ------------------------------------------------------------------------------------------
  IF  判断条件  THEN 执行语句
    [ELSELF  判断条件  THEN  执行语句]
    [ELSE  执行语句]
  END IF
1
2
3
4
5
6
7
8
9

  set @username = '张三风';
  
  -- 存过创建失败，不清楚原因
  delimiter $$
  drop procedure if exists if01$$

  create procedure if01()
  begin

      IF @username = '张三' THEN
          select concat('我是',@username);
      ELSEIF  @username = '张三风' THEN
          select '张三是我徒弟';
      ELSE
          select '我是栗四,不认识张三';
      END IF;
  end $$

  delimiter ;
  call if01;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

  CASE
      WHEN expr_condition THEN ...do something...
      WHEN expr_condition THEN ...do something...
      ....多个 WHEN THEN 语句.....
          [ELSE ...do something...]
  END CASE;
1
2
3
4
5
6

  CASE case_value
      WHEN when_value THEN statement_list
      [WHEN when_value THEN statement_list]...
      [ELSE statement_list]
  END CASE
  ------------------------------------------------------------------
  CASE  参数变量
      WHEN  参数变量1  THEN  执行语句]
      [WHEN  参数变量2  THEN  执行语句]
      [ELSE  执行语句]
  END  CASE 
1
2
3
4
5
6
7
8
9
10
11

  DELIMITER $$  
  CREATE PROCEDURE proc1(IN parameter int)  
      begin 
          declare var int;  
          set var=parameter+1;  
          case var  
          when 0 then   
              insert into t values(17);  
          when 1 then   
              insert into t values(18);  
          else   
              insert into t values(19);  
          end case;  
      end;  
      $$  
  DELIMITER ; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

  [begin_label:] LOOP 
      statement_list ;
  END LOOP [begin_label];
  ------------------------------------------------------------------
  循环名: loop
    -- 循环体
    执行语句;
  end loop 循环名;
1
2
3
4
5
6
7
8

  -- 需求： 打印1-10，输出结果为 1，2，3，4......9,10
  -- 如果存在loop_test，则先删除
  drop procedure if exists loop_test;
  delimiter $$
  -- 创建存储过程loop_test
  create procedure loop_test()
  begin
      declare c_index int default 1;
      declare result_str varchar(256) default '1';
      cnt: loop
          -- 循环10次，则跳出循环
          if c_index >= 10 then
              leave cnt;
          end if;
          -- 自增
          set c_index = c_index + 1;
          -- 拼接
          select concat(result_str, ',', c_index) into result_str;
      end loop cnt;
      -- 查询结果
      select result_str;
  end $$

  delimiter ;
  call loop_test();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

  public void loop_test() {
      String result_str = "1";
      int c_index = 1;
      while (true) {
          if (c_index >= 10) {
              break;
          }
          c_index = c_index + 1;
          result_str = result_str + "," + c_index;
      }
      System.out.println(result_str);
  }
1
2
3
4
5
6
7
8
9
10
11
12

  [WHILE标注] : WHILE 条件表达式 DO
    执行语句
  END LOOP [WHILE标注];
1
2
3

  drop procedure if exists sp_flow_while;
  delimiter $$
  -- 创建存储过程sp_flow_while
  create procedure sp_flow_while()
  begin
      declare c_index int default 1;
      -- 收集结果集字符串
      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 $$

  delimiter ;
  call sp_flow_while();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

  [REPEAT标注]:REPEAT
      执行语句
  UNTIL 条件表达式
  END REPEAT [REPEAT标注]
1
2
3
4

  drop procedure if exists test;   -- 如果存在test存储过程则删除
  delimiter $$                            -- 定义标识符为双斜杠
  create procedure test()                 -- 创建无参存储过程,名称为test
  begin
      declare i int default 6;            -- 申明变量
      repeat
          insert into test values (i);    -- 往test表添加数据
          set i = i + 1;                  -- 循环一次,i加一
      until i > 10 end repeat;            -- 结束循环的条件: 当i大于10时跳出repeat循环
      select * from test;                 -- 查看test表数据
  end;
  $$										-- 结束定义语句
  delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13

  ITERATE [LOOP/REPEAT/WHILE标注]
1

  drop procedure if exists test;          -- 如果存在test存储过程则删除
  delimiter $$                            -- 定义标识符为双斜杠
  create procedure test()                 -- 创建无参存储过程,名称为test
  begin
      declare i int(11) default 0;        -- 申明变量
      myloop:loop
            set i = i + 1;
            if i < 5 then iterate myloop; -- 跳过本次循环
            elseif i > 8 then leave myloop;
            end if;
            insert into test values (i); 
      end loop myloop;
      select * from test;  
  end;
  $$
  delimiter  ;                           -- 结束定义语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

参考链接

MySQL（七）存储过程

https://blog.csdn.net/Dim_Jerry/article/details/110203466

mysql存储过程、存储函数（二）：流程控制语句

https://blog.csdn.net/weixin_40482816/article/details/112258414

mysql创建定时执行存储过程任务

https://www.cnblogs.com/master-zxc/p/6429266.html

mysql定时任务(event事件)

https://www.cnblogs.com/angryjj/p/11324590.html