特点:封装、复用,可以接受参数,可以返回参数
-- 创建存储过程
CREATE PROCEDURE p1()
BEGIN
SELECT * from ev_users;
END;
-- 使用存储过程
CALL p1()
-- 查看存储过程定义
SHOW CREATE PROCEDURE p1;
-- 删除存储过程
DROP PROCEDURE if EXISTS p1;
2.用户变量
-- 设置用户变量
set @myage := 18;
-- 查询用户变量
SELECT @myage;
-- 实例
SELECT COUNT(*) INTO @myage from ev_users
流程
-- 局部变量
-- 声明 - DECLARE
-- 赋值 -
CREATE PROCEDURE p2()
BEGIN
DECLARE stu_count int DEFAULT 0;
SELECT count(*) into stu_count from ev_users;
SELECT stu_count;
END;
call p2();
-- if 判断
CREATE PROCEDURE p3 ()
BEGIN
DECLARE score int DEFAULT 58;
DECLARE result VARCHAR ( 10 );
IF
score >= 85 THEN
SET result := '优秀';
ELSEIF score >= 60 THEN
SET result := '及格';
ELSE
SET result := '不及格';
END IF;
END;
-- IN,OUT
CREATE PROCEDURE p4 (in score int,out result VARCHAR(10))
BEGIN
-- DECLARE result VARCHAR ( 10 );
IF
score >= 85 THEN
SET result := '优秀';
ELSEIF score >= 60 THEN
SET result := '及格';
ELSE
SET result := '不及格';
END IF;
END;
SELECT @result2
call p4(70,@result2);
-- inout
CREATE PROCEDURE p5(INOUT score DOUBLE)
BEGIN
SET score := score* 0.5;
END
set @score :=78;
CALL p5(@score)
SELECT @score
-- CASE
CREATE PROCEDURE p6 ( IN MONTH INT ) BEGIN
DECLARE
result VARCHAR ( 10 );
CASE
WHEN MONTH >= 1
AND MONTH <= 3 THEN
SET result := '第一季度';
WHEN MONTH >= 4
AND MONTH <= 6 THEN
SET result := '第二季度';
WHEN MONTH >= 7
AND MONTH <= 9 THEN
SET result := '第三季度';
WHEN MONTH >= 10
AND MONTH <= 12 THEN
SET result := '第四季度';
ELSE
SET result :='非法参数';
END CASE;
SELECT CONCAT('您输入的月份为:',month,',所属的季度为:',result) '结果';
END;
CALL p6(2)
-- 循环 WHILE 满足则继续执行
CREATE PROCEDURE p7(in n int)
BEGIN
DECLARE total int DEFAULT 0;
WHILE n>0 DO
set total :=total+n;
set n:=n-1;
END WHILE;
SELECT total;
END;
call p7(8)
-- REPEAT 满足时,退出循环
CREATE PROCEDURE p8(in n int)
BEGIN
DECLARE total int DEFAULT 0;
REPEAT
set total:=total+n;
set n:=n-1;
UNTIL n<=0 END REPEAT;
SELECT total;
END;
CALL p8(10)
-- loop 如不增加退出,则会一直循环
-- LEAVE 退出循环
-- ITERATE 跳出当前循环
CREATE PROCEDURE p9 ( IN n INT ) BEGIN
DECLARE
total INT DEFAULT 0;
sum :
LOOP
IF
n <= 0 THEN
LEAVE sum;
END IF;
IF
n % 2 = 1 THEN
set n := n - 1;
ITERATE sum;
END IF;
SET total := total + n;
SET n := n - 1;
END LOOP sum;
SELECT
total;
END;
CALL p9(100)
-- 游标