Mysql——存储过程

特点：封装、复用，可以接受参数，可以返回参数

-- 创建存储过程
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)
 
-- 游标