数据库存储过程

1. 什么是存储过程？

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中。

存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是由 流控制 和 SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中。

存储过程 可由应用程序通过一个调用来执行，而且允许用户声明变量。

同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

2. 存储过程的功能

这类语言主要提供以下功能，让用户可以设计出符合引用需求的程序：

① 变量说明

② ANSI兼容的SQL命令(如Select,Update….)

③ 一般流程控制命令(if…else…、while….)

④ 内部函数

3. 存储过程的使用场景

在商业数据库应用中，例如金融、企业、政府等等，存储过程的使用非常广泛，有多方面的原因，例如：存储过程一旦调试完成通过后就能稳定运行，这与各个业务在一段时间内是相对稳定和确定是匹配的；存储过程大大地减少了业务系统与数据库的交互，一定程度降低了业务系统与数据库的耦合。

当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程;

当在一个事务的完成需要很复杂的商业逻辑时(比如，对多个数据的操作，对多个状态的判断更改等)要考虑;还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。 为了系统的控制方便，例如当系统进行调整时，这时只需要将后台存储过程进行更改，而不需要更改客户端程序。也无需重新安装客户端应用程序。

4. 存储过程和触发器的区别

存储过程就相当于定义函数，触发器就是设定条件自动触发。

触发器里可以调用存储过程，存储过程不能调用触发器。

触发器：当有操作影响到触发器保护的数据时，触发器就自动发生，因此，触发器是在特定表上进行定义的，该表也称为触发器表，也是一种特殊类型的存储过程，与存储过程的区别：存储过程可以由用户直接调用执行，但是触发器不能被直接调用执行

触发器的类型：insert类型，update类型，delete类型

参考： 视图、存储过程、触发器讲解

5. 存储过程的优缺点

　　◆优点：
　　执行速度更快。存储过程只在创造时进行编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程执行速度更快。
　　存储过程用于处理复杂的操作时，程序的可读性更强、网络的负担更小。
　　使用存储过程封装事务性能更佳。
　　能有效的防SQL注入，安全性更好。
　　可维护性高，在一些业务规则发生变化时，有时只需调整存储过程即可，而不用改动和重编辑程序。
　　更好的代码重用。
　　◆ 缺点：
　　存储过程将给服务器带来额外的压力。
　　 存储过程多多时维护比较困难。
　　移植性差，在升级到不同的 数据库时比较困难。
　　调试麻烦，SQL语言的处理功能简单。

　　总之复杂的操作或需要事务操作的SQL建议使用存储过程，而参数多且操作简单SQL语句不建议使用存储过程。

5. 存储过程的种类

① 系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。

② 本地存储过程：用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

③ 临时存储过程：分为两种存储过程：

一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;

二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

④ 远程存储过程：在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

⑤ 扩展存储过程：扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

参考文章：数据库存储过程

6. 存储过程的创建

【Oracle 数据库的一个分页存储过程示例】

create or replace procedure fenye(

tableName in varchar2,--表名
pageSize in number,--1页显示的记录数
pageNow in number,--当前为第几页
myrows out number, --总记录数(一定要小心代码中不能有Oracle的保留字，保留字是以青色显示的)
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录集
) is
--定义部分
--定义一个sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--执行部分(排序的话在最内层加条件)
v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName
   ||' order by sal) t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--要计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from '|| tableName;
--执行sql，并把返回的值，赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,pageSize)=0 then
  myPageCount:=myrows/pageSize;
else
  myPageCount:=myrows/pageSize+1;
end if;
--关闭游标(关了的话运行Java程序竟然报游标已经关闭的错误！)
--close p_cursor;
end;

【java调用存储过程的示例】

[java] view plain copy

1. package org.bruce.test.oracle.procedure;  
2.   
3. import java.sql.CallableStatement;  
4. import java.sql.Connection;  
5. import java.sql.DriverManager;  
6. import java.sql.ResultSet;  
7. import java.sql.SQLException;  
8.   
9. public class TestOracleProc6 {  
10.     public static void main(String[] args) {  
11.         Connection ct = null;  
12.         CallableStatement cs = null;  
13.         int pageNow = 1;  
14.         try {  
15.             Class.forName("oracle.jdbc.driver.OracleDriver");   // 1.加载驱动  
16.             ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","SCOTT","m123");  
17.             cs = ct.prepareCall("{call my_fenye(?,?,?,?,?,?)}");  
18.               
19.             cs.setString(1, "emp"); // 取emp表  
20.             cs.setInt(2, 5);    // 每页取 5 条记录  
21.             cs.setInt(3, pageNow);  // 当前页为第 pageNow 页  
22.             cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); // 注册总记录数            
23.             cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); // 注册总页数  
24.             cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); // 注册返回的结果集  
25.   
26.             cs.execute();  
27.               
28.             int rowNum = cs.getInt(4); // 取出总记录数(getInt(4)中4，是由该参数的位置决定的  
29.             int pageCount = cs.getInt(5);  
30.             ResultSet rs = (ResultSet)cs.getObject(6);  
31.   
32.             System.out.println("总共有多少行记录：" + rowNum);  
33.             System.out.println("总共有多少页：" + pageCount);  
34.             System.out.println("当前为多少页：" + pageNow);  
35.             while(rs.next()) {  
36.                 System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getFloat(6));                  
37.             }  
38.         } catch(Exception e) {  
39.             e.printStackTrace();  
40.         } finally {  
41.             try { // 关闭各个打开的资源...  
42.                 if(cs != null) {  
43.                     cs.close();  
44.                 }  
45.                 if(ct != null) {  
46.                     ct.close();  
47.                 }  
48.             } catch (SQLException e) {  
49.                 e.printStackTrace();  
50.             }  
51.         }  
52.     }  
53. }  

参考： 数据库存储过程

MySQL例子：

创建一个存储过程

create procedure GetUsers()
begin 
    select * from user; 
end;

调用存储过程

call GetUsers();

删除存储过程

drop procedure if exists GetUsers;

4. 带参数的存储过程

MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;

下面的存储过程接受三个参数 , 分别用于获取用户表的最小 , 平均 , 最大分数 , 每个参数必须具有指定的类型 , 这里使用十进制值(decimal(8,2)) , 关键字 OUT 指出相应的参数用来从存储过程传出

create procedure GetScores(
    out minScore decimal(8,2),
    out avgScore decimal(8,2),
    out maxScore decimal(8,2)
)
begin
    select min(score) into minScore from user;
    select avg(score) into avgScore from user;
    select max(score) into maxScore from user;
end;

调用此存储过程 , 必须指定3个变量名(所有 MySql 变量都必须以 @ 开始) , 如下所示 :

call GetScores(@minScore, @avgScore, @maxScore);

该调用并没有任何输出 , 只是把调用的结果赋给了调用时传入的变量 @minScore, @avgScore, @maxScore , 然后即可调用显示该变量的值 :

select @minScore, @avgScore, @maxScore;

使用 IN 参数 , 输入一个用户 id , 返回该用户的名字 :

create procedure GetNameByID(
    in userID int,
    out userName varchar(200)
)
begin
    select name from user
    where id = userID
    into userName;
end;

调用存储过程 :

call GetNameByID(1, @userName);
select @userName;

• 
  

5. 复杂一点示例

根据 ID 获取货品的价格 , 并根据参数判断是否折扣 :

create procedure GetPriceByID(
    in prodID int,
    in isDisc boolean,
    out prodPrice decimal(8,2)
) 
begin
    declare tmpPrice decimal(8,2);
    declare prodDiscRate decimal(8,2);
    set prodDiscRate = 0.88;

    select price from products
    where id = prodID
    into tmpPrice;

    if isDisc then
        select tmpPrice*prodDiscRate into tmpPrice;
    end if;

    select tmpPrice into prodPrice;
end;

该存储过程传入三个参数 , 货品 ID , 是否折扣以及返回的价格 , 在存储过程内部 , 定义两个局部变量 tmpPrice 和 prodDiscRate , 把查询出来的结果赋给临时变量 , 再判断是否折扣 , 最后把局部变量的值赋给输出参数 ; 调用如下 :

call GetPriceByID(1, true, @prodPrice);
select @prodPrice;

参考文章：  SQL存储过程使用介绍