MySQL 存储过程

存储过程

存储过程是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;

存储过程和自定义函数的区别

 

原文链接:加载失败,请重新获取