delimiter $         -- 声明sql语句的分隔符，默认情况下是;
CREATE  PROCEDURE p()
BEGIN
SELECT 'hello procedure';
END$
call p()
-- -----------------                  注释符后空一格

delimiter $
CREATE PROCEDURE process_01()
BEGIN
DECLARE nickname VARCHAR(18) DEFAULT('大哥哥');    -- 声明变量，默认值为‘大哥哥’
SET nickname = '小弟弟';        -- 直接赋值
SELECT nickname;
END$

DROP PROCEDURE process_01;  -- 删除该存储过程

CALL process_01()       -- 调用该存储过程

-- INTO------------------
delimiter $
CREATE PROCEDURE process_02()
BEGIN
DECLARE num int;
SELECT COUNT(*) INTO num FROM  sinfo;        -- INTO 将SELECT COUNT(*) FROM  sinfo;的查询结果传递给num  
SELECT CONCAT('sinfo表中的元组数为 ', num);
END$

DROP PROCEDURE process_02;

call process_02


-- ------------------
delimiter $
CREATE PROCEDURE process_03()
BEGIN
SET @nickname = '小弟弟';
END$ 

DROP PROCEDURE process_03;

call process_03

SELECT @nickname;
-- ---------------------
  -- IN 该参数可作为输入
  -- OUT  该参数可作为输出
  -- INOUT  即可输入也可输出
delimiter $
CREATE PROCEDURE process_04(in height int)
BEGIN
  DECLARE description VARCHAR(30) DEFAULT('没身高');
  if height >= 180 then
  set description = '身材高挑';
  elseif height >= 170 and height < 180 then     -- 注意elseif不能分开
  set description = '标准身材';
  else
  set description = '一般身材';
  end if;                                       -- 结束if
  
  SELECT height;
  SELECT description;
END$

DROP PROCEDURE process_04;

call process_04(190);                           -- 调用时传入
call process_04(170);
call process_04(160);
-- if语句-------------------
  -- IN 该参数可作为输入
  -- OUT  该参数可作为输出
  -- INOUT  即可输入也可输出
delimiter $
CREATE PROCEDURE process_05(inout height int, out description VARCHAR(12) )
BEGIN

  if height >= 180 then
  set description = '身材高挑';
  elseif height >= 170 and height < 180 then     
  set description = '标准身材';
  else
  set description = '一般身材';
  set height = 172;          -- 长高了,使用inout将传递这个值出来
  set description = '标准身材';
  end if;                                       -- 
END$

DROP PROCEDURE process_05;

set @height = 190;
call process_05(@height,@description);    -- @description 用户会话变量，代表整个会话过程都有用，类似全局变量
SELECT @height;                           -- @@description 系统变量
SELECT @description;  

set @height = 170;
call process_05(@height,@description);
SELECT @height;   
SELECT @description;

set @height = 160;
call process_05(@height,@description);
SELECT @height;   
SELECT @description;
-- CASE语句-----------------
delimiter $
CREATE PROCEDURE process_06(mon int)
BEGIN
DECLARE result VARCHAR(10);
CASE 
  WHEN mon >=1 and mon<=3  THEN
    set result = '第一季度';
  WHEN mon >=4 and mon<=6  THEN
    set result = '第二季度';
  WHEN mon >=7 and mon<=9  THEN
    set result = '第三季度';
  ELSE
    set result = '第四季度';
END CASE;
  SELECT CONCAT('传递的月份为 ',mon,'所属季度是 ',result);
end$
call  process_06(7);
-- while循环-------------           不满足条件则退出循环
delimiter $
CREATE PROCEDURE process_07(n int)
BEGIN
DECLARE total int DEFAULT 0;
DECLARE num int DEFAULT 1;
WHILE num <= n DO                         -- 累加1~n的值
  set total = total + num;
  set num = num + 1;
END WHILE;
SELECT CONCAT('累计后的值为 ',total);
end$
DROP PROCEDURE process_07;
call  process_07(10);
-- repeat循环----------------      满足条件则退出循环
delimiter $
CREATE PROCEDURE process_08(n int)
BEGIN
DECLARE total int DEFAULT 0;
REPEAT
  set total = total + n;
  set n = n -1;
UNTIL n = 0 END REPEAT;           --  满足条件则退出循环
SELECT CONCAT('累计后的值为 ',total);
end$
DROP PROCEDURE process_08;
call  process_08(10);
-- loop语句--------------         使用leave语句推出循环
delimiter $
CREATE PROCEDURE process_09(n int)
BEGIN
DECLARE total int DEFAULT 0;
c:loop
  set total = total + n;
  set n = n -1;
  if n<=0 then
  LEAVE c;                        --  使用leave语句推出循环
  end if;
end loop c; 
SELECT CONCAT('累计后的值为 ',total);
end$
DROP PROCEDURE process_09;
call  process_09(10);
-- 游标------------------            
-- 用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。
-- 光标的使用包括光标的声明、OPEN、FETCH和CLOSE，其语法分别如下。
delimiter $
CREATE PROCEDURE process_10()
BEGIN
DECLARE sinfo_name VARCHAR(12);        -- 定义接收变量
DECLARE sinfo_number int;
DECLARE num int DEFAULT(1);
DECLARE n int DEFAULT(0);
DECLARE sinfo_result CURSOR for SELECT * from sinfo;    -- 定义游标
SELECT COUNT(*) INTO num FROM sinfo;
open sinfo_result;            -- 打开游标
c: LOOP
  FETCH sinfo_result into sinfo_name,sinfo_number;
  SELECT CONCAT('name：',sinfo_name,' number：',sinfo_number);
  set n = n + 1;
  IF n >= num THEN
    LEAVE c; 
  END IF; 
END LOOP c;
CLOSE sinfo_result;           -- 关闭游标
end$
DROP PROCEDURE process_10;
call  process_10();
--              <--- fetch   每fetch一次执行下一行
-- 吕布 20             |
-- ddd  21             |
-- aaa  21            \|/ 
-- n  7
-- hh 12
-- 韩信 1
-- 王老五  123

-- 存储函数-----------------
SET GLOBAL log_bin_trust_function_creators = 1;   --下方单独一个存储函数无法运行，需设置，下方链接为原因
delimiter $
create FUNCTION fun1(num int)
RETURNS int
BEGIN
DECLARE sum int DEFAULT(0);
SELECT COUNT(*) INTO sum FROM sinfo where number = num;
return sum;
end$

drop FUNCTION fun1;
SELECT fun1(21);



更多内容详见微信公众号：Python研究所

MySQL~存储过程基本操作_结果集