存储过程
存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元出来.
优点
- 增强SQL语句的功能和灵活性
- 实现较快的执行速度(省略了语法分析和识别)
- 减少了网络流量
创建存储过程
CREATE
[DEFINER = { user | CURRENT-USER )]
PROCEDURE sp_name ([ proc_parameter...J ])[ characteristic.. ] routine-body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
参数
- IN,表示该参数的值必须在调用存储过程时指定 ,
- OUT,表示该参数的值可以被存储过程改变,并且可以返回
- INOUT,表示该参数的调用时指定,并且可以被改变和返回
特性
- COMMENT:注释
- CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句NO SQL:不包含SQL语句
- READS SQL DATA:包含读数据的语句
- MODIFIES SQL DATA:包含写数据的语句
- SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行
过程体
- 由合法的SQL语句构成
- 可以是任意的SQL语句
- 复合结构则使用BEGIN...END语句
- 复合结构可以包含声明,循环控制结构
//获取当前版本(不带参数)
CREATE PROCEDURE sp1() SELECT VERSION();
//删除记录(带IN类型参数)
DELIMITER#解释命令是否结束
DELIMITER //
CREATE PROCEDURE removeUserById(IN D_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = D_id;
END
//
DELIMITER ;
//删除记录并返回剩下记录(带OUT类型参数)
DELIMITER //
CREATE PROCEDURE removeUserAndReturnUserNums(IN D_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = D_id;
SELECT count(id) FROM users INTO userNums;
END
//
DELIMITER ;
//根据年龄删除参数,返回剩下的参数(带多个OUT类型参数)
ROW_COUNT();//返回修改的行数
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(
IN D_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,
OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = D_age;
SELECT ROW_COUNT INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
//
DELIMITER ;
调用存储过程
- CALL sp_name([ parameter[,...]]) //带参数时,括号不可以省略
- CALL sp_name[()]
//调用存储过程sp1()
CALL sp1();
//调用删除记录removeUserById()
removeUserById(3);
//调用删除记录并返回剩下记录removeUserAndReturnUserNums()
CALL removeUserAndReturnUserNums(27,@nums);
SELECT @nums;
//根据年龄删除参数,返回剩下的参数removeUserByAgeAndReturnInfos
CALL removeUserByAgeAndReturnInfos(20,@a,@b);
修改存储过程
(不能修改过程体)
ALTER PROCEDURE sp name [characteristic ...
COMMENT 'string‘
I { CONTAINS SQL I NO SQL | READS SQL DATA | MODIFIES SQL DATA)
I SQL SECURITY { DEFINER | INVOKER}
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name;
//删除removeUserById
DELETE PROCEDURE removeUserById;
存储过程和自定义函数的区别