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结束'
f
