MySQL 存储过程

表和视图,存储过程和函数的区别

表 : 是真实存在的,它占存储空间

视图 : 相当于一个查询语句,不占有存储空间

------------------------------------------------------------------------------------

函数 : 只能返回一个变量, 可以嵌入SQL中使用

存储过程 : 可以返回多个变量, 用CALL命令调用,不能嵌入SQL中

函数和存储过程本质上没有区别

一个存储过程的示例

先看一个存储过程的示例,后边详细介绍

DELIMITER //    --把结束符改成//
CREATE PROCEDURE proc1()
BEGIN
	SELECT * FROM users;
	SELECT * from goods;
END//
DELIMITER ;     --把结束符改回;

　　使用CALL proc1();调用该存储过程后,会把users表和goods表的所有数据展示出来

存储过程的声明

CREATE PROCEDURE 过程名() BEGIN ...... END;

1.过程名必须要加上括号(),可以用来传参

2.BEGIN......END;这一段叫做过程体,过程体中可以嵌套BEGIN...END;

嵌套BEGIN...END的示例:

DELIMITER $$
CREATE PROCEDURE proc2()
BEGIN
	SELECT * FROM users;
	BEGIN
		SELECT * FROM goods;
	END ;
END $$
DELIMITER ;

声明变量

局部变量 : DECLARE 变量名数据类型 [ DEFAULT 默认值 ] ;

示例 : DECLARE numb INT DEFAULT 1;

用户变量 : 在变量名前加@,无需声明,在使用时给变量名前加上@即可.

示例 : SELECT @a:=1; (可以复制这句SQL运行试试)

　　 SET @a=@a+1;

局部变量和用户变量比较:

　　1.作用域 : 局部变量作用域在过程体内,用于变量作用域是全局

　　2.生命周期 : 局部变量在执行完对应的过程体后消亡,用户变量在会话中断时消亡(用户变量的生命周期是一次会话/连接)

　　3.用户变量在普通的SQL语句上也可以使用,比如 SELECT @a:=1; 会查询到a=1的结果,自己试试吧~ 在SELECT 后使用用户变量,需要用:=来赋值,SET后则=和:=都可用

示例

DELIMITER //
CREATE PROCEDURE proc3()
BEGIN
	DECLARE a INT DEFAULT 1;
	SET @z=1;
	SET @z=@z+1;
	SELECT a;
	SELECT @z;
END//
DELIMITER ;
--call proc3();后,运行结果
--a=1 , z=2
--自己试试吧

存储过程的参数

在存储过程的声明中我们讲过,存储过程名后必须加上括号(),这个括号就是用来传参的.

存储过程可以传3种不同的参数

　　1.in : 输入参数,可以传入字面量或变量

　　2.out : 输出参数,只能传入变量

　　3.inout : 输入输出参数,只能传入变量

参数的定义方法是 : 存储过程名(in 参数名数据类型)

一.in输入参数

DELIMITER //
CREATE PROCEDURE proc4(IN numb INT)
BEGIN
	SELECT numb;
	SET numb=numb+5;
	SELECT numb;
END//
DELIMITER ;
SET @a=2;
CALL proc4(@a);--也可以传入字面量,比如3
--结果1:numb=2
--结果2:numb=7

二.out输出参数

DELIMITER //
CREATE PROCEDURE proc5(OUT numb INT)
BEGIN
	SELECT numb;
	SET numb=5;
	SELECT numb;
END//
DELIMITER ;

SET @a=1;
CALL proc5(@a);   --此处@a用于接收输出参数的值
--执行结果如下
--结果1:numb=null   因为@a作为输出参数传入,所以这里接收不到@a的值,输出null
--结果2:numb=5
SELECT @a;   --结果:@a=5

三.inout输入输出参数

DELIMITER //
CREATE PROCEDURE proc6(INOUT numb INT)
BEGIN
	SELECT numb;
	SET numb=numb+5;
	SELECT numb;
END//
DELIMITER ;

SET @a=1;
CALL proc6(@a);   --用@a接收输出结果
--结果1:numb=1
--结果2:numb=6
SELECT @a;
--结果:@a=6

存储过程的控制语句

一.条件语句

　　1.if - then - else语句

　　格式 : if 判断 then 执行语句

　　　　 elseif 判断 then 执行语句

　　　　else 执行语句

　　　　 end if;

DELIMITER //
CREATE PROCEDURE proc7(IN numb INT)
BEGIN
	IF numb>5 THEN SET numb=numb-5;
	ELSEIF -5<numb<0 THEN SET numb=numb+5;
	ELSE SET numb=numb+10;
	END IF;
	SELECT numb;
END//
DELIMITER ;

　　2.case语句

　　格式 : case 变量

　　　　 when 判断 THEN 执行语句

　　　　 else 执行语句

DELIMITER //
CREATE PROCEDURE proc8(IN numb INT)
BEGIN
	CASE numb
	WHEN 1 THEN SET numb=numb+3;
	WHEN 2 THEN SET numb=numb-6;
	ELSE SET numb=0;
	END CASE;
	SELECT numb;
END//
DELIMITER ;

二.循环语句

　　1.while循环

　　格式 : while 判断 do

　　　　循环语句;

　　　　 end while;

DELIMITER //
CREATE PROCEDURE proc9(IN numb INT)
BEGIN
	WHILE numb<=0 DO
	SET numb=numb+3;
	END WHILE;
	SELECT numb;
END//
DELIMITER ;

　　2.repeat循环

　　格式 : repeat

　　　　循环语句;

　　　　 until 判断

　　　　 end repeat;

DELIMITER //
CREATE PROCEDURE proc10(IN numb INT)
BEGIN
	REPEAT
	SET numb=numb+3;
	UNTIL numb>5
	END REPEAT;
	SELECT numb;
END//
DELIMITER ;

　　3.loop循环

　　格式 : 标签:loop

　　　　循环语句;

　　　　 if 判断 then leave 标签;

　　　　 end if;

　　　　 end loop;

DELIMITER //
CREATE PROCEDURE proc11(IN numb INT)
BEGIN
	a:LOOP
	SET numb=numb+3;
	IF numb>50 THEN LEAVE a;
	END IF;
	END LOOP;
	SELECT numb;
END//
DELIMITER ;

　　4.iterate : 相当于continue,结合标签使用

DELIMITER //
CREATE PROCEDURE proc12(IN numb INT)
BEGIN
	DECLARE str VARCHAR(100) DEFAULT '开始';
	loop_lable:LOOP
	SET numb=numb-1;
	IF numb>1 THEN 
		SET str=CONCAT(str, numb);
		ITERATE loop_lable;  --相当于continue,意思是从loop_lable标签处继续循环
	ELSE
	SET str=CONCAT(str, '结束');
	LEAVE loop_lable;
	END IF;
	END LOOP;
	SELECT str;
	SELECT numb;
END//
DELIMITER ;

CALL prop12(5);    --结果是str='开始432结束'