mysql 存储过程 @@rowcount_MySQL 存储过程

CURD: 插入,删除,更新,查找

MySQL执行流程

存储过程定义

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

存储过程的优点:

预编译

只在第一次调用时进行语法分析和编译，以后直接从内存中得到结果.可以避免重复的语法分析和编译，提高执行效率.

灵活性

可以通过控制语句对流程进行控制和判断,可以有多个返回值等.

减少网络流量

客户端不再需要提交一大段SQL语句到服务器执行,只需像函数调用一样,提交函数名和参数给服务器.

创建存储过程

语法结构

CREATE [DEFINER = {user|CURRENT_USER}]

-- 定义时的用户，若是不写就默认为当前用户

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

-- 可以带0到多个参数 sp_name存储过程的名字

[characteristic ...]

-- 特性characteristic解释见下文

routine_body

-- 其中参数

proc_parameter:

[IN|OUT|INOUT] param_name type

IN 表示该参数的值必须在调用存储过程时指定

OUT 表示该参数的值可以被存储过程改变，并且可以返回

INOUT 表示该参数的值调用时指定，并且可以被改变和返回

特性

characteristic

COMMENT 'string' |

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } |

SQL SECURITY{DEFINER|INVOKER}

COMMENT 注释

CONTAINS SQL 包含SQL语句, 但不包含读或写数据的语句

NO SQL 不包含SQL语句

READS SQL DATA 包含读数据的语句

MODIFIES SQL DATA 包含写数据的语句

SQL SECURITY {DEFINER|INVOKER} 指明谁有权限来执行

过程体

存储过程的过程体与自定义函数的函数体类似.

过程体由合法的SQL语句构成；

过程体可以是任意SQL语句(对表进行增删改查,连接,但是不能创建数据表)

过程体如果为复合结构则使用BEGIN...END 语句

复合结构可以使用条件、循环等控制语句

创建没有参数的存储过程

--sp1()获取mysql版本

CREATE PROCEDURE sp1()

SELECT VERSION();

调用存储过程

-- 不带参数的存储过程调用

CALL sp_name[()]

--示例,无参数可以不加小括号

CALL sp1();

CALL sp1;

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

DESC users;

DELIMITER //

CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id = id;

END

//

DELIMITER ; --恢复分隔符

--调用

CALL removeUserById(3);

参数名称id最好不要和表中的字段相同,修改过程体只能删掉存储过程重新定义如下:

--重新定义id为p_id

DROP PROCEDURE removeUserById;

--SELECT * FROM users;

DELIMITER //

CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id = p_id;

END

//

DELIMITER ; --恢复分隔符

--调用

--SELECT * FROM users;

CALL removeUserById(22);

SELECT * FROM users WHERE id = 22;

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

DELIMITER //

CREATE PROCEDURE removerUserAndReturnUserName

(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)

BEGIN

DELETE FROM user WHERE id = showID;

--该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 showName 变量中

SELECT count(ID) FROM user INTO showName;

END

--@nums 定义用户变量，可用 SELECT @nums 输出

CALL removerUserAndReturnUserName(10,@nums);

用 DECLARE 声明的变量是 局部变量，局部变量只能存在于 BEGIN...END 之间，且声明时必须置于 BEGIN...END 的第一行.

而通过 SELECT...INTO... 或者 SET @id = 7 这种方法定义的变量称为 用户变量，只能存在于当前用户所使用的mysql客户端有效。

--通过@或SET设置的变量称为用户变量

CALL rmUserAndRtUserNums(27, @nums);

SELECT @nums; --@nums是用户变量

SET @i = 7; --@i是用户变量

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

根据年龄删除用户,返回删除的用户数和剩下的用户数.

--ROW_COUNT()函数相当于PHP的MySQL函数库中的mysql_affected_rows()函数，两者的作用相同，都是统计当插入和修改、添加和删除记录时影响的行数。

DELIMITER //

CREATE PROCEDURE rmUserByAgeAndRtInfos

(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)

BEGIN

DELETE FROM users WHERE WHERE age = p_age; --注意变量不同

SELECT ROW_COUNT() INTO delNums;

SELECT COUNT(id) FROM users INTO leftNums;

END

//

--调用存储过程

DELIMITER ;

CALL rmUserByAgeAndRtInfos(20, @a, @b);

SELECT @a, @b;

修改存储过程

不能修改过程体,修改过程体只能删掉重新定义.

ALTER PROCEDURE sp_name [characteristic]

COMMENT 'string' |

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } |

SQL SECURITY{DEFINER|INVOKER}

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

--示例

DROP PROCEDURE removeUserById;

与自定义函数的区别

存储过程实现的功能相对复杂，函数的针对性更强

函数一般不对表进行操作,存储过程经常操作表

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

存储过程一般独立执行，函数往往作为 sql 语句的组成部分出现

存储过程用 CALL name 来调用,多个复杂功能的函数常常封装成一个存储过程,通过预编译提高执行效率.