存储过程:一组预先编译好的sql语句的集合。
好处:1.提高代码重用性
2.简化操作
3.减少编译次数并且减少和数据服务器连接次数。
#一.创建语法:
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意:
1》》参数列表包含三部分
参数模式 参数名 参数模型
实例:IN(参数模式) stuname(参数名) VARCHAR(20)(参数模型)
参数模式:
IN: 需要调用方调用时传入值
OUT:作为输出,该参数可以作为返回值
INOUT:既可以输入值 又可以返回值。
2》》如果存储过程只有一句话,BEGIN 和 END 可以省略。
存储过程中每条SQl语句结尾要求必须加分号。
存储过程结尾可以使用DELIMTER重新设置
语法:
DELIMITER 结束标志
案列:
DELIMITER $
BEGIN
#二.调用语法:
CALL 存储过程名(实参列表);
#1.空参列表
#案例:插入到user表中3条记录
案例:
创建:
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO `user` (name) VALUES('eee'),('fff'),('gggg');
END $
调用:
CALL myp1();
#2.创建In模式存储过程
案例:
创建:
DELIMITER $
CREATE PROCEDURE myp2(IN sname VARCHAR(20))
BEGIN
SELECT * FROM `user` WHERE `name` = sname;
END $
调用:
CALL myp2 ('eee');
#存储过程模拟登陆实例
创建
DELIMITER $
#实例 存储过程实现登录
CREATE PROCEDURE myp3(IN sname VARCHAR(10),IN pass VARCHAR(20))
BEGIN
DECLARE result int(20) DEFAULT 0;#声明并初始化接受返回值的变量
SELECT COUNT(*) INTO result #赋值
FROM `user`
WHERE name = sname AND password = pass;
SELECT IF(result>0,'成功','失败');#使用
END $
调用:
CALL myp3('qq','111');
#3.创建带有out存储过程
创建
实例:根据Id查询名称
DELIMITER $
CREATE PROCEDURE myp4(IN sid BIGINT(20),OUT sname VARCHAR(10))
BEGIN
SELECT name INTO sname
FROM `user`
WHERE id = sid;
END $
调用:
DELIMITER $
CREATE PROCEDURE myp4(IN sid BIGINT(20),OUT sname VARCHAR(10))
BEGIN
SELECT name INTO sname
FROM `user`
WHERE id = sid;
END $
实例:根据ID 返回多值
创建:
DELIMITER $
#根据Id返回名称和信息
CREATE PROCEDURE myp5(IN sid INT(10),OUT sname VARCHAR(20),OUT sinfo VARCHAR(20))
BEGIN
SELECT name,info INTO sname,sinfo
FROM `user`
WHERE id = sid;
END $
调用:
#展示查询结果
CALL myp5(7,@sname,@sinfo);
SELECT @sname ,@sinfo;
#4.创建带有inout模式存储过程
创建:
CREATE PROCEDURE myp6(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b = b*2;
END
调用:
#赋值并调用
SET @m = 10 ;
SET @n = 20 ;
CALL myp6(@m,@n);
SELECT @m ,@n;
#创建存储过程传入用户名和密码 并插入到user表中
CREATE PROCEDURE myp7(IN sname VARCHAR(20),IN spass VARCHAR(10))
BEGIN
INSERT INTO `user`(name,password) VALUES(sname,spass);
END
CALL myp7('vvvvvvv','8888888')
#根据Id 返回用户名称和信息
CREATE PROCEDURE myp8(IN sid INT,OUT sname VARCHAR(10),OUT sinfo VARCHAR(20))
BEGIN
SELECT name , info INTO sname,sinfo
FROM `user`
WHERE id = sid;
END
#调用
CALL myp8(2,@sname,@sinfo);
SELECT @sname,@sinfo;
#存储过程比较日期大小
CREATE PROCEDURE myp9(IN data1 DATETIME ,IN data2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(data1,data2) INTO result;
END
调用:
CALL myp9('2018-09-01',now(),@result);
SELECT @result;
#创建存储过程实现日期转换成固定格式
CREATE PROCEDURE myp10(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END
CALL myp10(NOW(),@str);
SELECT @str;
#三 删除存储过程
drop PROCEDURE 存储过程名称;
#四 查看存储过程
SHOW CREATE PROCEDURE 存储过程名称;