项目6 创建与使用存储过程

MySQL的存储过程
存储过程是一组为了完成特定功能的SQL语句块，经编译后存储在数据库中，用户通过指定存储过程的名称并给定参数（如果该存储过程带有参数）来调用并执行它，存储过程能重复使用，这样可以大大减少数据库开发人员的工作量。

存储过程主要有以下优点：

①执行效率高：存储过程编译后存储在数据库服务器端，可以直接调用从而提高了SQL语句的执行效率。

②灵活：存储过程可以用结构化语句编写，可以完成较复杂的判断和运算。

③数据独立：用户在程序中调用存储过程，存储过程能把数据同用户程序隔离开，其优点是当数据表结构变化时，可以随时修改存储过程，不用修改程序源代码。

④安全：存储过程可被作为一种安全机制来充分利用，系统管理员通过设置存储过程的访问权限，从而实现相应数据的访问权限限制，避免了用户对数据表的直接访问，保证了数据的安全。

⑤降低网络流量：当在客户机上调用该存储过程时，网络中传送的只是该调用语句，而不是这一功能的全部代码，从而大大降低了网络负载。

1、变量：

用户变量：SET @变量名=值；

系统变量：@@version   @@date

局部变量：declare 变量名 数据类型（默认值）；

2、流程控制：

If 条件 then

 执行条件1

Else

 执行语句2

End if;

case 表达式

  when 值1 then sql语句1

when 值2 then sql语句2

…………………………

when 值n then sql语句n

else sql语句n+1

end case;

1

while 条件 do

 2 sql语句

end while;

3

1

标签

loop

2

If  条件 then

leave 标签;

end if

3

end loop;

4

repeat

  2

until 条件

end repeat

3

创建存储过程：

create procedure 存储过程名（[in | out | inout] 变量名 数据类型）    

begin                                   括号里的值：in 值 数据类型, out 值 数据类型

………………                              in(传入参数)，out（传出参数）

………………                               inout 传入传出都行

end

调用存储过程

call 存储过程名();

删除存储过程

drop procedure 存储过程名;

修改sql语句结束符

delimiter 结束符

创建游标

declare 游标名 cursor for 查询语句；

打开游标

open 游标名;

读取游标

fetch 游标名 into 变量;

关闭游标

close 游标名;

开启事务

Start transaction;

结束事务

提交(执行代码)    commit;

取消 (取消代码，不执行)   rollback;

设置事务隔离级别

Set session transaction isolation level 隔离级别;

事务隔离级别：

读未提交（脏读）   read uncomitted

读提交（不可重复读）    read committed

可重复读（幻读）     repeatable read

可串行化         serializable

创建存储过程示例：

create procedure adacount(out count int)   //创建存储过程，存储名为adacount

begin

declare itmp int;                       //创建局部变量 item

declare cur_id cursor for select id from stu;      //  创建游标cur_id 查询stu表中的id字段

declare exit handler for not found close cut_id;       //

select count(*) into count from stu;       //求数量

set @sum=0;                        //创建用户变量sum

open cur_id;                         //打开游标 cur_id

repeat                                //使用repeat循环方法

fetch cur_id into itmp;                   //读取游标

if itmp<10                           //使用if循环条件为itmp小于10

then set @sum=@sum+itmp;          //创建执行语言，把sum+itmp赋值给sum

end if;                            //结束if循环

until 0 end repeat;               //条件为0 ，end repeat;(结束repeat循环)

close cur_id;                     //关闭游标

end//                            //结束