MySQL~存储过程基本操作

声明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]

MySQL~存储过程基本操作

标签：else   result   color   web   org   art   Fix   mys   declare