mysql存储过程

自定义函数 必要条件(参数,返回值（1）可以用零个或者多个 参数（2）只能有一个 返回值简称UDF;是对MySQL扩展的一种途径
begin end 适用于 复合结构函数
create function function_name(c1 int unsigned,c2 int unsigned)
returns int unsigned
begin
...
end

存储过程优点：
增强SQL语句的功能和灵活性；实现较快的执行速度 ：第一次客户端调用的时候进行语法分析和编译等操作，以后调用的时候直接调用编译结果；减少了网络流量【只需传递存储过程的名字以及要删除的ID数值】
总结：
**存储过程是是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。**存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户声明变量以及进行流程控制，存储过程可以接收参数，可以接收输入类型的参数，也可以接收输出类型的参数，并且可以存在多个返回值。通过这个简单的介绍我们可以发现，存储过程的效率要比我们单一的执行SQL语句的效率要高，原因在于我们以前假设这两个SQL语句，Mysql的引擎会对这两个语句逐一的进行语法分析，逐一的进行编译再逐一的去执行，而我们采用了存储过程之后，只有在第一次才进行语法分析和编译，以后我们客户端再去调用直接调用编译的结果就可以了，这样就直接省略了两个环节，效率比以前要好。
可以没有或有多个返回值（指的是select返回结果集，内部不能使用RETURN）
1、MySQL通过存储过程（预编译），可以避免重复语法分析和编译，提高执行效率
2、存储过程：
（1）是SQL语句与控制语句的【预编译集合】，以【一个名称存储】作为【一个单元处理】
（2）优点：
· 增强了语句的功能和灵活性：可以通过控制语句对流程进行控制和判断
· 实现较快的执行速度，只在【第一次调用时进行语法分析和编译】 ，以后直接从内存中得到结果
· 减少网络流量
1、存储过程语法结构分析
 CREATE
 [DEFINER = {user|CURRENT_USER}]//定义时的用户，若是不写就默认为当前用户
PROCEDURE sp_name ([proc_parameter[,...]]) //可以带0到多个参数 sp_name存储过程的名字
 [characteristic ...] routine_body
 其中参数
 proc_parameter:
[IN|OUT|INOUT] param_name type
 IN, 表示该参数的值必须在调用存储过程时指定
OUT, 表示该参数的值可以被存储过程改变，并且可以返回
 INOUT, 表示该参数的值调用时指定，并且可以被改变和返回
 2.特性
 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}指明谁有权限来执行
 3.过程体
 (1)过程体由合法的SQL语句构成；
 (2)过程体可以是任意SQL语句;对表格进行增删改查，多表连接，但是不能创建数据表<br>
 (3)过程体如果为复合结构则使用BEGIN...END语句
 (4)复合结构可以使用条件、循环等控制语句
不带参数的存储过程：
创建存储过程：
CREATE PROCEDURE sp1（） SELECT VERSION()
调用存储过程：
用CALL sp1（）；调用
call sp_name([参数]);
call sp_name;
第一个是带参数，第二个不带参数，区别是封装的过程没有参数的话小括号带有或不带有都可以，但是存储过程带有参数的话，那么小括号就不能省略
创建带有IN类型参数的存储
参数中的名字不能和表中的记录名相同
为了不让begin中的分号影响mysql的语句结束判断，需要用的delimiter命令将定界符更改成其他（如//）
delimiter//
create procedure removeUserById(in p_id int unsigned)
begin
delete from users id=p_id;([id数据表中的字段]、[id以后需要调用的参数])
end
//
delimiter;
调用存储过程
call removeUsersById(3)
select * from users where id=3(结果Empty，说明记录已被删除)
修改存储过程（不能修改过程体）
delete procedure removeUserById;这是错误写法
drop procedure removeUsersById(删除过程体)
通过id删除记录的存储过程：
DELIMITER //;
CREATE PROCEDURE removeuserbyid(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE  id=p_id;参数的名字不能和表中的名字一样,既id=id否则系统会认为是一样，造成记录全部被删除；
END
//
CALL removeuserbyid(3);

删除
DROP FUNCTION IF EXISTS 存储过程名称;
DROP PROCEDURE sp;

从数据表users中删除记录不固定的记录，并且返回剩余的记录数
begin……end

delimiter//
create procedure removeUserAndReturnUserNUms(IN p_id int unsigned,out userNums int unsigned)
begin
delete from users where id=p_id
select count(id) from users INTO userNums; 将count（id）的结果放入userNums
end
//
delimiter;
调用存储过程
call removeUserAndReturnUserNums(27,@nums);[@nums存储过程返回的值]，接收返回的值
CALL R1(3,@NUMS);@NUMS代表的就是变量
select @NUMS;
1.用户变量：以"@"开始，形式为"@变量名"
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
可用 set @变量名=值 来创建变量并赋值（多次使用只是会覆盖，而不是报错），如果是调用存储过程时，给out的变量传入一个@变量，也相当于创建用户变量并传入，在存储过程内被赋值

2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名  或者  set @@global.变量名 
对所有客户端生效。只有具有super权限才可以设置全局变量
3.会话变量：只对连接的客户端有效。
4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量（必须位于句首，即begin之下的第一行）。
如DECLARE 变量名 数据类型
set语句可以为各种变量赋值，即set 变量=变量值

创建带有多个OUT类型参数的存储过程：<br>
row_count()得到插入、删除、以及更新的被影响的记录总数
1.明确什么是OUT类型的参数，有什么作用？<br>
答：OUT类型参数，表示在调用存储过程时，该参数的值可以被存储过程改变，并且返回一个值。通常是这种类型时，调用时写入的参数都是变量用“@”符号开头的变量，在BEGIN和END之中的变量是局部变量，在调用存储过程时写入的变量是“用户变量”，比如有一个存储过程的名称叫做“addUser()”，调用时addUser(@sum),sum就是用户变量。
2.ROW_COUNT()函数就相当于PHP的MySQL函数库中的mysql_affected_rows()这个函数，两者的作用都是相同的，都是计算当插入记录和修改记录、以及添加和删除记录时的个数总和的。
3.例子：
SELECT ROW_COUNT() INTO delete_User ；是指调用ROW_COUNT()这个函数，并且把返回值存储在delete_User这个局部变量中。
SELECT COUNT(id) FROM users INTO remain_User ;是指在删除用户后，调用COUNT()函数对剩下的用户数做一个统计，在哪张表中要说明清楚，然后把返回值存储在remain_User这个局部变量中。
【因为delete_User和remain_User是OUT类型的参数，所以会返回一个值给调用时的用户变量，用"SELECT @a,@b"可以知道返回的值是多少了】

delimiter //
create procedure removeUserByAgeAndReturnInfos(IN p_age smallint 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
//
delimiter ;
call removeUserByAgeAndReturnInfos(20,@a,@b);调用自定义函数
@a 删除的记录数
@b 剩余的记录数
[drop procedure removeUserByageandreturnInfos;]删除过程体

//存储过程与自定义函数的区别
A、存储过程实现的功能相对复杂，函数针对性较强
B、存储过程可以返回多个值，函数只能有一个返回值
C、存储过程一般独立执行，函数可以作为 sql 语句的组成部分来出现

//修改存储过程
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;