MYSQL存储过程（六）

1、MySQL执行流程，通过存储过程，可以避免重复语法分和编译，提高执行效率
2、存储过程：
（1）是SQL语句与控制语句的【预编译集合】，以【一个名称存储】作为【一个单元处理】
（2）优点：
· 增强了语句的功能和灵活性：可以通过控制语句对流程进行控制和判断
· 实现较快的执行速度，只在【第一次调用时进行语法分析和编译】 ，以后直接从内存中得到结果
· 减少网络流量

存储过程语法结构解析
1.创建存储过程
CREATE
[DEFINER = {userCURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,…]]) //可以带0到多个参数
[characteristic …] routine_body
其中参数
proc_parameter:
[IN OUT INOUT] param_name type
ps:
IN, 表示该参数的值必须在调用存储过程时指定
OUT, 表示该参数的值可以被存储过程改变，并且可以返回
INOUT, 表示该参数的值调用时指定，并且可以被改变和返回
2.特性
COMMENT ‘string’
{CONTAINS SQL NO SQL READS SQL DATA MODIFIES SQL DATA SQL SECURITY{DEFINER INVOKER}
ps:
COMMENT:注释
CONTAINS SQL:包含SQL语句, 但不包含读或写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含读数据的语句
MODIFIES SQL DATA:包含写数据的语句
SQL SECURITY {DEFINERINVOKER}指明谁有权限来执行
3.过程体
(1)过程体由合法的SQL语句构成;
(2)过程体可以是任意SQL语句;(增删改查)
(3)过程体如果为复合结构则使用BEGIN…END语句
(4)复合结构可以使用条件、循环等控制语句

使用存储过程
创建没有参数的存储过程
CREATE PROCEDURE sp1() SELECT VERSION();

/创建实例代码（参数名不能和表中的字段名相同）/
创建带有INT类型参数的存储过程：
DELIMITER // //定界符
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED) //int 是数据类型
BEGIN DELETE FROM users WHERE id = p_id;
END
// //结束
DELIMITER ;

创建IN OUT参数的存储过程
DELIMITER //
CREATE PROCEDURE removerUserAndReturnUserName(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = showID;
SELECT count(ID) FROM user INTO showName;
END
//
SELECT count(ID) FROM user INTO showName; /* 该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 showName 变量中 /
CALL removerUserAndReturnUserName(10,@nums); /* @nums 所代表的就是用户变量，可用 SELECT @nums 输出 /
用 DECLARE 声明的变量是局部变量，局部变量只能存在于 BEGIN…END 之间，且声明时必须置于 BEGIN…END 的第一行
而通过 SELECT…INTO…/SET @id = 07 这种方法设置的变量我们称之为用户变量，只能存在于当前用户所使用的客户端有效。
CALL rmUserAndRtUserNums(27, @nums);
SELECT @nums; //@nums - 就是用户变量

创建带有多个OUT类型参数的存储过程
INSERT test(username) VALUES(‘A’),(‘B,’),(‘C’);
SELECT ROW_COUNT(); //返回被插入的记录总数
SELECT * FROM test;
UPDATE test SET username = CONTCAT(username, ‘–immoc’) WHERE id <= 2;
SELECT row_COUNT(); //返回更新的记录总数

DELIMITER //
CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age; //注意变量不同
SELECT ROW_COUNT() INTO delNums; //返回插入、删除和更新的受影响的记录总数
SELECT COUNT(id) FROM users INTO leftNums;
END

修改存储过程：(修改注释，名称，不能修改过程体)
ALTER PROCEDURE sp_name [characteristic]
COMMENT ‘string’
{CONTAINS SQLNO SQLREADS SQL DATAMODIFIES SQL DATA}
SQL SECURITY{DEFINERINVOKER}

删除存储过程：
DROP PROCEDURE [IF EXISTS] sp_name;

调用存储过程
CALL sp_name([parameter[,…]]) //带参数的存储过程的调用
CALL sp_name[()] //不带参数的存储过程调用
CALL sp1; or CALL sp1();