初学mysql(九)-数据库之存储过程、函数与游标（上）

上一篇博客讲了mysql数据库中的触发器，这一篇博客来说说mysql数据库中公认的稍微比较难理解的存储过程、函数与游标。

定义：存储过程是一组为了完成特定功能的SQL语句集合。使用存储过程的目的是将常用或复杂的工作，预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中（可以减少客户端与服务器端的数据传输），因此称为存储过程。在以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需用call语句来调用存储过程名字，即可自动完成命令。

优点：运行效率高。存储过程在创建时已经对其进行了语法分析及优化工作，并且存储过程一旦执行，在内存中会保留该存储过程，当数据库服务器再次调用该存储过程时，可以直接从内存中进行读取，所以执行速度更快。

降低了网络通信量。使用存储过程可以实现客服机只需通过网络向服务器发出存储过程的名字和参数，就可以执行SQL语句。在存储过程包含上百行的SQL语句时，执行性能尤为明显。

业务逻辑可以封装在存储过程中，方便实施企业规则。利用存储过程将企业规则的运算程序存储在数据库服务器中，由RDBMS统一管理，当用户的规则发生变化时，可以只修改存储过程，无需修改其他的应用程序，这样不仅容易维护，而且简化了复杂的操作。

创建存储过程框架：

create procedure 存储过程名字(参数1，参数2...)[characteristic.....]
routine_body.

创建存储函数框架：

create function 存储函数名字(参数1，参数2...)
 return type [characteristic...]
routine_body.

小例子：创建表t_user,t_user1：字段1 id，字段2 userName，字段3 job，字段4 jobtypeId。

创建一个存储过程通过jobTypeId字段查看这一工作在这一工作类型的工人数。

创建存储过程: 

delimiter && 
      create procedure pro_user(in uid int, out counts int) 
       READS SQL DATA
       begin  
           select count(*) from t_user where jobTypeId = uid; 
         end 
&&

调用存储过程：

call pro_user(1,@nums);

创建一个存储函数通过id查看这一职工的名字。

创建存储函数：      

delimiter && 
      create function func_user(uid int)
      returns varchar(20) 
         begin 
              return(select userName from t_user where id = uid); 
          end 
&&    

调用存储函数：

call func_user(2) 名字;

1）：a:定义变量：declare 名字1, 名字2, 名字3    类型

例子：给t_user表中插入数据：           

delimiter && 
        create procedure pro_user1() 
        begin
              declare a,b varchar(20);
               declare c int;
               insert into t_user values(null, a, b, c);
          end 
&&  

调用:call pro_user1();

b:给变量赋值：

形式1:set 名字1='xxxxx', 名字2='xxxxx',名字3=‘xxxxx’;    

形式2:select 字段1，字段2，字段3 into a,b,c from t_user1;

例子：给t_user表中插入数据：         

delimiter &&
     create procedure pro_user2()
     begin 
          declare a,b varchar(20);
          declare c int; 
           set a='马化腾',b='CEO',c=2;
           insert into t_user values(null, a,b,c); 
      end 
&&

或者:               

delimiter && 
      create procedure pro_user3()
      begin
          declare a,b varchar(20);
           declare c int; 
           select userName, job,jobTypeId into a,b,c from t_user;
           insert into t_user values(null, a,b, c); 
       end 
&&

调用：call pro_user2(); call pro_user3();

 2）：游标：查询语句可能查询出多条记录，在存储过程和存储函数中使用游标来逐条的读取查询结果集中的记录。游标必须声明在处理程序之前，并且声明在变量和条件之后。游标的使用包括以下4个步骤。

游标的使用步骤：

1：声明游标 ：declare 游标名字 cursor for  查询语句；

2：打开游标：open 游标名字；

3：使用游标：fetch 游标名字 into 字段1，字段2，.....；

4：关闭游标：close 游标名字；

例子：在t_user表中赋值指定id的员工再次插入t_user表中，要求使用游标。

delimiter &&
    create procedure pro_user4(in uid int)
    begin
          declare a,b varchar(20);
          declare c int;
          declare cur_t_user cursor for select userName , job , jobTypeId from t_user where id = uid;
          open cur_t_user;
          fetch cur_t_user into a,b,c;
          insert into t_user values(null, a,b,c);
          close cur_t_user;
    end
&&

调用带有游标的存储函数：

call pro_user4(2);