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);
链接：
[https://blog.csdn.net/maweiba163/article/details/51895323]
