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 intDEFAULT(1);
DECLARE n intDEFAULT(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 intDEFAULT(0);
SELECT COUNT(*) INTO sum FROM sinfo where number = num;return sum;
end$
drop FUNCTION fun1;
SELECT fun1(21);
