存储过程基础以及几种简单的实现

一、什么是存储过程？

存储过程是一些预编译的SQL语句。存储过程说白了就是一堆 SQL 的合并。中间加了点逻辑控制。

更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

或者这样理解：存储过程就类似于函数，就是把一段具有一定功能的T-SQL语句代码封装起来，当要执行这些代码的时候，可以通过调用该存储过程来实现。在封装的语句体里面，可以使用if/else,case,while等控制结构。可以进行SQL编程。

（1）查看现有的存储过程：>show procedures status;

（2）删除存储过程：>drop procedure 存储过程的名字；

eg1: 有存储过程p1,p2，可以 >drop procedure p1;

eg2: 第一个存储过程，体会封装SQL

  >    delimiter $

      create procedure p1()   //加小括号是因为相当于函数，可以有参数

       begin 

       select * from goods;

       end$

eg3:  第二个存储过程，体会参数

  >    delimiter $

      create procedure p2(n int)   //加小括号是因为相当于函数，可以有参数

       begin 

       select * from goods where num>n;

       end$

      调用存储过程: >call  p2(10)$   //查询数量大于10的商品

eg4:  第三个存储过程，体会控制结构

   >   delimiter $

      create procedure p3(j varchar(1), n tinyint)
      begin
      if j='h' then
      select * from g where num>n;
      else 
      select * from g where num<n;
      end if;
      end$

   调用存储过程: >call p3('h',10)$   或者  >call p3('1',10)$ 

eg5:  第四个存储过程，体会循环

      计算1-n的和

>create procedure p4(n tinyint)

begin
declare i int;  //声明变量i
declare s int;
set i=1;    //设置变量的值i=1
set s=0;
while i<=n do   //循环
set s=s+i;
set i=i+1;
end while;
select s;
end$

 调用存储过程: >call p4(100)$  

 二、 存储过程的优点  

1. 存储过程的能力大大增强了SQL语言的功能和灵活性。

     原因：（1）存储过程可以用流控制语句编写（可以使用if/else,case,while等控制结构），有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2. 可以减少网络的通信量

 原因：调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能 绝对比一条一条的调用SQL语句要高得多。 存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。 

3. 执行速度更快。

原因：（1）在存储过程创建的时候，数据库已经对其进行了一次解析和优化（数据库已对其进行了语法和句法分析，并给出了优化执行方案），由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。

           （2）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

4. 可保证数据的安全性和完整性

原因：（1）通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

　　    （2）通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

  5. 更强的适应性（同4）

原因：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。 
6. 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

7. 可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力，代码精简一致，一个存储过程可以用于应用程序代码的不同位置，升级、维护方便。

8. 存储过程可以重复使用，可减少数据库开发人员的工作量。写程序简单，采用存储过程调用类，调用任何存储过程都只要1-2行代码。

三、缺点

1. 可移植性差 

       由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。 

       数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

2. 如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。 

     如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

3. 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

4. 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

 

四、存储过程与函数区别：

（1）存储过程保存在数据库里面，存储过程可以被连接此数据库的所有程序设计语言和程序使用，自定义函数不能。 
（2）存储过程可以有数据库管理软件修改，使得多层结构程序调整系统逻辑时，并不需要编译和分发程序。 
（3）存储过程执行中，不会引起网络流量，不占用程序服务器的内存和CPU资源。　　

五、存储过程应用场景

当一个业务同时对多个表进行处理的时候采用存储过程比较合适。

1.使用存储过程在一般情况下会提高性能，因为数据库优化了存储过程的数据访问计划并应用缓存方便以后的查询；

2. 存储过程单独保护存在于数据库中。客户端可以获取权限执行存储过程,而不需要对底层的具体表设置其他的访问权限；

3.存储过程会使得维护起来更加方便，因为通常修改一个存储过程要比在一个已经发布的组件中修改SQL语句更加方便；

4.存储过程给底层数据格式增添了额外的抽象层。使得使用存储过程的客户端对存储过程的实现细节以及对底层数据格式是隔离独立的；

5.存储过程能够缓解网络带宽，因为可以批量执行SQL语句而不是从客户端发送超负载的请求。

复杂的数据处理用存储过程，如有些报表处理。

多条件多表联合查询，并做分页处理，用存储过程也比较适合。

适当的使用存储过程，能够提高我们SQL查询的性能，以便于提高我们的工作效率。