存储过程

存储过程简介

存储过程（Stored Procedure）是数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。

存储过程分类

1系统存储过程
　　以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
2本地存储过程
　　用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，这跟各种编程语言里用户自己写的函数非常类似。我们一般所说的存储过程就是指本地存储过程。今天我们重点介绍本地存储过程，其他存储过程了解即可。
3临时存储过程
分为两种存储过程：一是本地临时存储过程，以“#”开头，这样的存储过程就是存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;　　二是全局临时存储过程，以“##”开头，这样的存储过程就是存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。
4远程存储过程
　　在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。
5扩展存储过程
　　扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

创建存储过程的基本代码结构：

 create procedure Procedure_Name //Procedure_Name为存储过程（不能以阿拉伯数字开头），在一个数据库中触发器名是唯一的。
 @Param1 int ,@Param2 int//@Param1和  @Param2为存储过程的参数，DataType为参数类型，多个参数采用“,”隔开

AS//存储过程要执行的操作

BEGIN
     --begin跟end组成，可以不写，如果执行sql语句较为麻烦，则使用BEGIN END会使得代码更加整齐，容易理解
END
GO //操作完成
exec Procedure_Name[采纳数名] //调用存储过程

存储过程优缺点总结：

优点
　　1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
　　2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
　　3.存储过程可以重复使用,可减少数据库开发人员的工作量(复用性高，面向对象的编程思想)
　　4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权
缺点
　　1.调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
　　2.移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
　　3.重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
　　4.如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

简单秒杀存储过程

DELIMITER $$ --console; 转化为$$
--定义存储过程
--参数：in 输入参数;out 输出参数
--row_count();返回上一条修改类型sql(delete、insert、update)的影响行数
--row_count:0 未修改数据 >0 表示修改的行数;<0:sql错误/未执行系应该sql
CREATE PROCEDURE `seckill`.`excute_seckill`
(in v_seckill_id bigint,in v_phone VARCHAR,in v_kill_time TIMESTAMP,out r_result int)
  BEGIN
    DECLARE insert_count int default 0;
    START TRANSACTION;
    insert ignore into success_killed
       (seckill_id,user_phone,create_time)
       values (v_seckill_id,v_phone,v_kill_time)
    select row_count() into insert_count;
    IF (insert_count<0) THEN
       ROLLBACK ;
       set r_result = -1;
    ELSEIF(insert_count<0) THEN
       ROLLBACK ;
       SET r_result = -2;
    ELSE
       update seckil set number = number-1
       where seckill_id = v_seckill_id
         and end_time > v_kill_time
         and start_time < v_kill_time
         and number > 0;
       select row_count() into insert_count;
       IF(insert_count==0) THEN
          ROLLBACK ;
          SET r_result = 0;
       ELSEIF(insert_count < 0) THEN
          ROLLBACK ;
          SET r_result = -2;
       ELSE
          COMMIT;
          SET r_result = 0;
       END IF;
    END IF;
  END;
$$
--存储过程定义结束
DELIMITER ;
set @r_result = -3;
--调用执行存储过程
call execute_seckill(1003,15400899706,now(),@r_result);

--获取结果
select @r_result;

博客搬家：大坤的个人博客
欢迎评论哦~