存储过程(预处理)


  1. 1.语法结构:
1. create [definer = {user | current_user}] 
procedure sp_name ([proc_parameter[,...]])  [characteristic...] routine_body
  proc_parameter: [in | out | inout] param_name type

in:该参数的值必须在调用存储过程时指定

       out:该参数的值可以被存储过程改变，并且可以返回

 inout:表示该参数在调用时指定，并且可以被改变和返回给调用者

过程体:由合法的sql语句构成，对于记录的增删改查及多表连接的sql语句，过程体如果超过两个语句，必须使用begin...end，复合语句内可以声明变量，循环，控制语句。
2. 创建不带参数的存储过程:create procedure sp1() select version();  //创建一个名为sp1的存储过程 功能是返回版本
3. 带有in类型参数的存储过程：

create procedure removeUserById(in myId int unsigned)
 -> begin
     -> delete from users where id = myId; //标准语句 参数赋值 参数名字不能和字段名相同
     -> end
     -> //


4. 调用存储过程:

call sp_name([parameter[,...]]) //若存储过程有参数，则()不能省略
   call sp_name[()]

示例：

call sp1; call sp1();
     call removeUserById(3);


5. 修改存储过程

:alter procedure sp_name [characteristic...] comment ‘string’
| {contains sql | no sql | reads sql data | modifies sql data}
| sql security {definer | invoker}


6. 删除存储过程:

drop procedure [if exists] sp_name

示例:

drop procedure removeUserById;

7.带有in和out类型参数的存储过程:

create procedure removeUserAndReturnUserNums(in p_id int unsigned,out userNums int unsigned)
    -> begin
    -> delete from test where id = p_id;
    -> select count(id) from test into userNums; //userNums代表接收表test的id总数
    -> end
    -> //

调用：

call removeUserAndReturnUserNums(2,@nums); //@nums代表变量

查看：

select @nums;

8.带有多个out类型参数的存储过程

create procedure removeUserByAgeAndReturnInfos(in p_age smallint unsigned,out deleteUsers smallint unsigned,out userCountssmallint unsigned)
 -> begin 
-> delete from users where age=p_age;  根据p_age参数删除表的记录
-> select row_count() into deleteUsers;  返回改变字段的数量并赋给deleteUsers
    -> select count(id) from users into userCounts; 返回id数量并赋给userCounts
    -> end
    -> //

in即为输入的变量    out是输出的变量

  row_count():返回改变的字段的数量

调用: 

call removeUserByAgeAndReturnInfos(20,@a,@b);

调用，删除age为20的所有记录，并返回改变字段的总数给a，返回剩余id数量给b。