mysql复合语句、存储过程、游标

一、概念：

1、mysql复合语句：必须放在存储过程或者函数或者其他数据库对象中

2、存储过程：一组SQL语句组成，可以带参数，用于实现特定功能

BEGIN#Routine body goes here...END;

放在BEGIN……END中的语句就是复合语句

二、复合语句的使用

1、声明变量

1）使用关键字declare，写法：declare 变量名称 变量类型

  declare i int;#定义一个整型的变量i  declare name varchar(20);

注意：变量的声明必须写在其他复合语句之前

2）声明变量的同时制定默认值，使用关键字declare...default：

 declare address varchar(50) default '成都';

3）定义全局变量使用@，全局变量是同一个连接不同查询都可以使用的变量。

<span style="font-size:12px;">set@x=1;//定义全局变量x，并赋初值</span>

2、给变量赋值，使用关键字set

set i=100;set name='赵文卓';

3、分支语句

1）if-else-then语句。

if i>90 && i<100 then  set result='优';else if i>80  then  set result='良';else   set result='及格';end if;end if;#注意使用end if结束

注意：每个if需要使用end if结束条件。

2）case when

select address as '地点', case address   when '重庆' then '火锅'   when '成都' then '肥肠粉'   when '上海' then '甜品' end   as '特产' from employee;

4、 循环语句

1）while循环

declare tem_salary int;declare i int;declare sum int;#查询salary赋值到tem_salaryselect salary into tem_salary from employee where id=101;while tem_salary<1000000 do    set tem_salary=tem_salary+5000;end while;

2）loop循环

loop1:loop#loop1是循环的标签  set tem_salary=tem_salary-2000;#循环体  if tem_salary>500000 then    iterate loop1;#iterate让循环继续  end if;    leave loop1;#leave让循环结束end loop;update employee set salary=tem_salary where id=101;

3）repeat循环

set i=1;set sum=0;repeat #重复执行以下语句，直到i>10为止 set i=i+1; set sum=sum+i;until i>10end repeat;

5、异常处理，SQL使用sqlstate：标准SQL的错误代码，由5位数字组成。mysql特有的错误代码，mysql_error_code mysql由4位组成，一般使用sqlstate。

BEGIN  #如果出现23000异常，则把全局变量x设置为1，异常处理程序，出了异常就会执行的代码  #出了异常就继续  declare continue handler for sqlstate '23000' set@x=1;  #出现异常就退出  declare exit handler for sqlstate 'HY000' set@z=1;  set @y=1;  insert into employee values(125,'松子',27,15000,'上海',1001,107);  set @y=2;  insert into employee values(125,'松子',27,15000,'上海',1001,107);  set @y=3;#如果异常处理成功，则会处理该行代码END

更多sqlstate，标准SQL的错误代码参考mysql官网：http://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html

三、游标的使用

1、概念：游标，存放结果集。

2、游标的使用步骤：

1）声明游标：declare 游标名称 cursor for 查询结果集;

2）打开游标：open 游标名称;

3）使用游标（游标本身不带循环），使用fetch一次取游标中一行数据存入临时变量中：fetch 游标名称 into 临时变量集合;

4）关闭游标： close 游标名称;

参考代码：

BEGIN  declare c_id int;#临时变量  declare c_salary int;#临时变量  declare done int default false;#done表示是否有数据     #1 声明游标 查询employee中所有记录修改前的id,salary  declare c1 cursor for select id,salary from employee;  #当游标数据获取完毕，抛出异常，把结束标志设置为true  declare continue handler for not found  set done=true;  #2 打开游标  open c1;  #3 使用游标，游标本身不带循环  loop1:loop    if done THEN#如果结束标志为true则结束循环       leave loop1;    end if;    fetch c1 into c_id,c_salary;#fetch一次取游标中一行数据存入临时变量中    #根据当前的c_id 为当前数据的salary加500    update employee set salary=c_salary+500 where id=c_id;  end loop loop1;  # 关闭游标  close c1;END

四、存储过程，实现特定功能一组SQL语句组成。

1、不带参数的存储过程

1）使用SQL创建不带参数的存储过程

create procedure getEmp()BEGIN  #不带参数的存储过程  select * from employee;END;

2）调用不带参数的存储过程

call getEmp();

2、带参数的存储过程

1）存储过程的参数分为三种：

      a）in：传入参数，不会返回数据。

      b）out：传出参数，返回数据给调用的方法。

      c）inout：传入，传出参数。

2）使用SQL创建带参数的存储过程，用法参考：

CREATE PROCEDURE calcs(IN c_id int,OUT c_name varchar(20),INOUT c_salary int)BEGIN  #将查询结果放入传出参数，条件使用的传入参数  select name into c_name from employee where id=c_id;  #将传入的c_salary加上1000，然后存入传出参数  select salary+c_salary into c_salary from employee where id=c_id;END;

3）调用带参数的存储过程：

set @c_id=101;#传入参数，调用完后不再使用set @c_salary=5000;#传入传出参数，调用完后可以接受值call calcs(@c_id,@c_name,@c_salary);

3）验证结果：

select @c_name,@c_salary;