MySQL数据库基础学习（六）-存储过程

MySQL数据库基础学习（六）-存储过程

1 概述

SQL命令—MySQL引擎—语法正确—可识别命令—执行结果—客户端

存储过程是SQL语句和控制语句的预编译过程的集合，一个名称存储并作为一个单元处理。

优点：

1. 增强SQL语句的功能和灵活性

2. 实现较快的执行速度

3. 减少网络流量

2 存储过程语法结构分析

CREATE

[DEFINER = {user | CURRENT_USER}]

PROCEDURE sp_name([pro_parameter[,...]])

[characteristic ... ] routine_body

pro_parameter

[IN | OUT | IN OUT] 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语句

复合结构可以包含声明、循环、控制语句

3 创建不带参数的存储过程

调用存储过程

CALL sp_name ([parameter [,...]])

CALL sp_name [()] 如果无参数 ，则()可以省略

案例分析

创建获取当前版本的不带参数的存储过程

mysql>CREATE PROCEDURE sp1() SELECT VERSION();

CALL sp1();

CALL sp1;

4 创建带有IN类型参数的存储过程

案例分析

传入一个id参数，根据传入的参数删除记录

mysql>DELIMITER //

CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id = p_id; %注意这两者必须不同

END

//

mysql>DELIMITER ;

CALL removeUserById(22);

5 创建带有IN和OUT类型参数的存储过程

案例分析

删除某一ID记录，并返回剩余记录总数

mysql>DELIMITER //

mysql>CREATE PROCEDURE removeUserAndReturnNums(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)

BUGIN

DELETE FROM users WHERE id = p_id;

SELECT COUNT(id) FROM users INTO userNums;

END

//

mysql>DELIMITER ;

mysql>SELECT COUNT(id) FROM users;

mysql>CALL removeUserAndReturnUserNums(7,@nums); %@nums表示局部变量

mysql>SELECT @nums;

mysql>SET @i = 7; %表示用户变量

6 创建带有多个IN和OUT类型参数的存储过程

案例分析

根据年龄删除记录，返回删除的用户数和剩余记录总数

系统函数:得到记录变化数（增、删、改数）

ROW_COUNT()

mysql>UPDATE test1 SET first_name = CONCAT(first_name,'-imooc') WHERE id <= 2;

mysql>ROW_COUNT();

mysql>CREATE PROCEDURE removeUserByAgeAndReturnIndos(IN p_age SMATLLINT UNSIGNED, OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)

BEGIN

DELETE FROM users WHERE age = p_age;

SELECT ROW_COUNT() INTO deleteUsers;

SELECT COUNT(id) FROM users INTO userCounts;

END

//

mysql>SELECT COUNT(id) FROM users;

mysql>SELECT COUNT(id) FROM users WHERE age =22;

mysql>CALL removeUserByAgeAndReturnInfos(22,@a,@b);

mysql>SELECT @a,@b;

7 修改存储过程

ALTER PROCEDURE sp_name[characteristic ...]

COMMENT 'string'

|{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER}

8 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

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

1. 存储过程实现的功能要复杂些；而函数的针对性更强

2. 存储过程可以返回多个值；函数只能有一个返回值

3. 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现

小结

存储过程：增 删 改 调用