描述:
MySQL存储过程是一组预编译的SQL语句,可以在MySQL数据库中创建和存储,然后可以在需要时调用执行。存储过程可以接受参数,可以包含流程控制语句,可以返回结果集或者输出参数。存储过程可以提高数据库的性能和安全性,减少网络流量,简化复杂的操作。
好处:
- 简化操作,提高了sql语句的重用性,减少了开发程序员的压力
- 减少操作过程中的失误,提高效率
- 减少网络传输量(客户端不需要把所有的 SQL 语句通过网络发给服务器)
- 减少了 SQL 语句暴露在网上的风险,也提高了数据查询的安全性
和视图、函数的对比:
它和视图有着同样的优点,清晰、安全,还可以减少网络传输量。不过它和视图不同,视图是虚拟表 ,通常不对底层数据表直接操作,而存储过程是程序化的 SQL,可以直接操作底层数据表 ,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。
一旦存储过程被创建出来,使用它就像使用函数一样简单,我们直接通过调用存储过程名即可。
分类
存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下:
1、没有参数(无参数无返回)
CREATE PROCEDURE User_one()
BEGIN
#需要进行处理的语句块
SELECT * from `user`;
END;
2、仅仅带 IN 类型(有参数无返回)
CREATE PROCEDURE User_one(IN id INT)
BEGIN
#需要进行处理的语句块
SELECT * from `user` a where a.user_id=id ;
END;
3、仅仅带 OUT 类型(无参数有返回)
CREATE PROCEDURE `User_one`(out id INT)
BEGIN
#需要进行处理的语句块
SELECT a.user_id into id from `user` a LIMIT 1;
END
4、既带 IN 又带 OUT(有参数有返回)
CREATE PROCEDURE `User_one`(in id int ,out fhid INT)
BEGIN
#需要进行处理的语句块
SELECT a.user_id into fhid from `user` a where a.user_id=id;
END
注意:IN、OUT、INOUT 都可以在一个存储过程中带多个。
以下是一个简单的MySQL存储过程的例子,该存储过程接受一个参数,查询指定表中的数据并返回结果集:
CREATE PROCEDURE User_test(IN id INT)
BEGIN
#需要进行处理的语句块
END;
要调用存储过程,可以使用CALL语句:
CALL User_test(1);
要查看已创建的存储过程的代码,可以使用SHOW CREATE PROCEDURE语句:
SHOW CREATE PROCEDURE User_test;
要修改存储过程,可以使用ALTER PROCEDURE语句:
ALTER PROCEDURE User_test(IN id INT)
BEGIN
#修改内容
END;
练习内容:
Demo1 建一个存储过程 :实现把a表和b表连接查询返回的结果,写入到c表中。
CREATE PROCEDURE `User_test`(IN id INT)
BEGIN
/*
实现把user表和work表连接查询返回的结果,写入到user_ms表中。
*/
DECLARE result VARCHAR(250); #用来返回结果
DECLARE minid,maxid INT; #用来控制入参
DECLARE count INT; #用来获取user_ms中是否已经存在该记录
#获取work表中的最小id和最大id 作为一个值范围
select min(b.user_id),max(b.user_id)
INTO minid,maxid
from work b;
#先判断入参是否存在范围之内
IF id<minid or id>maxid THEN
SET result = '该用户ID不存在!!!!!!';
ELSE
select 1 into count from user_ms c where c.user_id=id ;
#先判断这个id是否已经存在user_ms中 有就不写 没有就写 避免数据写入报错
if count >0 then
SET result = '结果已存在user_ms表中,不需要同步数据!';
else
INSERT INTO user_ms
select a.user_id,a.user_ms,a.user_name,b.work_ms,b.work_dd
from `user` a,`work` b
where a.user_id=b.user_id and a.user_id=id;
COMMIT;
SET result = '结果已经存入user_ms表中';
end if;
END IF;
SELECT result;
END
例如入参为1 调用存储过程:call User_test(1)
查询一下user_ms表,数据语句成功写入到表中