存储过程

存储过程的定义：

有关存储过程的定义，百度百科是这么说的：存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数的话）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

存储过程是由一些SQL语句和控制语句组成的被封装起来的过程，它驻留在数据库中，可以被客户应用程序调用，也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似，存储过程可以通过名字来调用，而且它们同样有输入参数和输出参数。根据返回值类型的不同，我们可以将存储过程分为三类：返回记录集的存储过程，返回数值的存储过程（也可以称为标量存储过程），以及行为存储过程。顾名思义，返回记录集的存储过程的执行结果是一个记录集，典型的例子是从数据库中检索出符合某一个或几个条件的记录；返回数值的存储过程执行完以后返回一个值，例如在数据库中执行一个有返回值的函数或命令；最后，行为存储过程仅仅是用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。

存储过程的写法：

----------------创建存储过程示例-----------------------
create or replace procedure proc1(
       p_para1 vachar2,
       p_para2 out varchar2,--out表示输出参数
       p_para3 in out varchar2--in表示输入参数
)as--紧跟着关键字as(is)关键字的是变量声明块，可以理解为pl/sql的declare关键字，用于声明变量。变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度
       v_name varchar2(20);
       begin--从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
         v_name := 'Tom';
         p_para3 := v_name;
         dbms_output.put_line('p_para3:'||p_para3);
         --异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选
       end;--结束块：由end关键字结果

--------------执行存储过程--------------------
exec procedure_name;
--or
call procedure_name;

--------------删除存储过程-----------------

drop procedure procedure_name --注意在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

创建存储过程的参数：

1.procedure_name ：存储过程的名称，在前面加#为局部临时存储过程，加##为全局临时存储过程。

2.; number：是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。

3.@parameter： 存储过程的参数。可以有一个或多个。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。

4.data_type：参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。
说明 对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。

5.VARYING： 指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

6.default： 参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

7.OUTPUT ：表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

8.RECOMPILE: 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。 说明 在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。

10.FOR REPLICATION :指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

11.AS :指定过程要执行的操作。

12.sql_statement :过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。

存储过程的适用场合：

当一个业务同时对多个表进行处理的时候采用存储过程比较合适。 或者是当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。

为了系统的控制方便，例如当系统进行调整时，这是只需要将后台存储过程进行更改，而不需要更改客户端程序。也无需重新安装客户端应用程序。

存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。

存储过程的特点：

1.运行速度:大多数高级的数据库系统都有statement cache的，所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多（检查权限等），所以对于很简单的sql，存储过程没有什么优势。
2.网络负荷:如果在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的。
3.团队开发:很遗憾，比起成熟的IDE，没有什么很好存储过程的IDE工具来支持，也就是说，这些必须手工完成。
4.安全机制:对于传统的C/S结构，连接数据库的用户可以不同，所以安全机制有用；但是在web的三层架构中，数据库用户不是给用户用的，所以基本上，只有一个用户，拥有所有权限（最多还有一个开发用户）。这个时候，安全机制有点多余。
5.用户满意:实际上这个只是要将访问数据库的接口统一，是用存储过程，还是EJB，没太大关系，也就是说，在三层结构中，单独设计出一个数据访问层，同样能实现这个目标。
6.开发调试:一样由于IDE的问题，存储过程的开发调试要比一般程序困难（老版本DB2还只能用C写存储过程，更是一个灾难）。
7.移植性:算了，这个不用提，反正一般的应用总是绑定某个数据库的，不然就无法靠优化数据库访问来提高性能了。
8.维护性:的确，存储过程有些时候比程序容易维护，这是因为可以实时更新DB端的存储过程，但是在3层结构下，更新server端的数据访问层一样能实现这个目标，可惜现在很多平台不支持实时更新而已。

存储过程的优点：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的SQL语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

3.存储过程可以重复使用，可减少数据库开发人员的工作量。

4.安全性高，可设定只有某此用户才具有对指定存储过程的使用权。

5.速度快。尤其对于较为复杂的逻辑，减少了网络流量之间的消耗。如果有的过程和函数达到了几百行，一个微型编译器，相信用程序就更麻烦了。

6.写程序简单，采用存储过程调用类，调用任何存储过程都只要1-2行代码。

7.升级、维护方便。

8.调试其实也并不麻烦，可以用查询分析器。

9.如果把所有的数据逻辑都放在存储过程中，那么asp.net只需要负责界面的显示功能，出错的可能性最大就是在存储过程。一般情况下就是这样

存储过程的缺点：

1、调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2、移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3、重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4、如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

简单示例介绍创建各式各样的存储过程：

1、只返回单一记录集的存储过程

--创建名为GetUserAccount的存储过程
create Procedure GetUserAccount
as
select * from UserAccount
go

--执行上面的存储过程
exec GetUserAccount

结果：相当于运行的一条select语句，得到的结果是整张表的数据

2、没有输入和输出的存储过程

--创建名为GetUserAccount的存储过程
create Procedure GetUserAccount
as
insert into UserAccount(UserName,[Password]) values(Tom,***)
go

--执行上面的存储过程
exec GetUserAccount

结果：相当于运行了一条insert语句。

3、有返回值的存储过程

--创建名为GetUserAccount的存储过程
create Procedure GetUserAccount
as
insert into UserAccount(UserName,[Password]) values(Tom,***)
return @@rowcount
go

--执行上面的存储过程
exec GetUserAccount

结果：这里的@@rowcount为执行存储过程影响的行数，执行的结果不仅插入了一条数据，还返回了一个值，即return value=1，这个可以在程序中获取，从下面的java调用也可以看出来。

4、有输入参数和输出参数的存储过程

--创建名为GetUserAccount的存储过程
create Procedure GetUserAccount
@UserName nchar(20),
@UserID int output
as
if(@UserName > 5)
select @UserID=count(*) from UserAccount where UserID>25
else
set @UserID=1000
go

--执行上面的存储过程
exec GetUserAccount '7',null

结果：@UserName为输入参数，@UserID为输出参数。 运行结果为@userID为COOUT（*）即 =1

5、同时具有输入参数和输出参数的存储过程

--创建名为GetUserAccount的存储过程
create Procedure GetUserAccount
@UserName nchar(20),
@UserID int output
as
if(@UserName > 5)
select @UserID=count(*) from UserAccount where UserID>25
else
set @UserID=1000
return @@rowcount
go

--执行上面的存储过程
exec GetUserAccount '7',null

结果：@userID为COOUT（*）即 =1，Retun Value=1。

6、同时返回参数和记录结果集的存储过程

--创建名为GetUserAccount的存储过程
create Procedure GetUserAccount
@UserName nchar(20),
@UserID int output
as
if(@UserName > 5)
select @UserID=count(*) from UserAccount where UserID>25
else
set @UserID=1000
select * from UserAccount
return @@rowcount
go

--执行上面的存储过程
exec GetUserAccount '7',null

结果：返回执行 select * from UserAccount 这句代码的结果集，同时@userID为COOUT（*）即 =1，Retun Value等于影响的行数。

7、返回多个记录集的存储过程

--创建名为GetUserAccount的存储过程
create Procedure GetUserAccount
as
select * from UserAccount
select * from UserAccount where UserID>5
go

--执行上面的存储过程
exec GetUserAccount

结果：返回两个结果集，一个为 select * from UserAccount，另一个为 select * from UserAccount where UserID>5 。

JdbcTemplate调用存储过程：
一、调用无返回值的存储过程

public class callProcedure {    
    private JdbcTemplate jdbcTemplate;    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {    
    this.jdbcTemplate = jdbcTemplate;    
    }    
    public void test(){    
       this.jdbcTemplate.execute("{call procedureName (?)}");    
    }    
  } 

二、调用有返回值的存储过程（不是结果集）

public class test {

      /**
       * 调用无参的存储过程（有返回值）
       * @return
       */
      public static int callProcedure() {
            String str = (String)jdbcTemplate.execute(new CallableStatementCreator(){
                  @Override
                  public CallableStatement createCallableStatement(Connection connection) {
                        String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_INCRE (?)}";//存储过程
                        CallableStatement cs = null;//创建存储过程的对象
                        try {
                              cs = connection.prepareCall(procedure);
                              cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return cs;
                  }
            }, new CallableStatementCallback(){
                  @Override
                  public Object doInCallableStatement(CallableStatement cs) {
                        String csStr = null;
                        try {
                              cs.execute();
                              csStr = cs.getString(1);//获取输出参数的值
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return csStr;//获取输出参数的值
                  }});
            return Integer.parseInt(str);
      }

      /**
       * 调用有参的存储过程（有返回值）
       * @return
       */
      public static int callProcedure() {
            String str = (String)jdbcTemplate.execute(new CallableStatementCreator(){
                  @Override
                  public CallableStatement createCallableStatement(Connection connection) {
                        String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_INCRE (?,?)}";//存储过程
                        CallableStatement cs = null;//创建存储过程的对象
                        try {
                              cs = connection.prepareCall(procedure);
                              cs.setString(1,"value1");//设置入参的值
                              cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return cs;
                  }
            }, new CallableStatementCallback(){
                  @Override
                  public Object doInCallableStatement(CallableStatement cs) {
                        String csStr = null;
                        try {
                              cs.execute();
                              csStr = cs.getString(2);//获取输出参数的值
                        } catch (Exception e) {
                              logger.error("call procedure error : " + e);
                        }
                        return csStr;//获取输出参数的值
                  }});
            return Integer.parseInt(str);
      }
}

三、调用有返回值的存储过程（是结果集）

public class test {

       List resultList = (List) jdbcTemplate.execute(  
               new CallableStatementCreator() {  
                  public CallableStatement createCallableStatement(Connection con) throws SQLException {  
                     String storedProc = "{call PRC_BJ_SYNC_CUST_DATA(?,?)}";// 调用的sql  
                     CallableStatement cs = con.prepareCall(storedProc);  
                     cs.setString(1, "p1");// 设置输入参数的值  
                     cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型  
                     return cs;  
                  }  
               }, new CallableStatementCallback() {  
                  public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {  
                     List resultsMap = new ArrayList();  
                     cs.execute();  
                     ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值  
                     while (rs.next()) {// 转换每行的返回值到Map中  
                        Map rowMap = new HashMap();  
                        rowMap.put("id", rs.getString("id"));  
                        rowMap.put("name", rs.getString("name"));  
                        resultsMap.add(rowMap);  
                     }  
                     rs.close();  
                     return resultsMap;  
                  }  
            });  
        for (int i = 0; i < resultList.size(); i++) {  
           Map rowMap = (Map) resultList.get(i);  
           String id = rowMap.get("id").toString();  
           String name = rowMap.get("name").toString();  
           System.out.println("id=" + id + ";name=" + name);  
        }
}

查看oracle中的存储过程：

1、使用SQL语句

SELECT TEXT

FROM USER_SOURCE

WHERE NAME='存储过程名'

ORDER BY LINE

2、直接在PL/SQL中查看

Oracle存储过程的常用技巧

Oracle的DBMS.OUTPUT

Oracle的 DBMS_OUTPUT.PUTLINE()方法

几个简单小应用：

应用1：现场的同事需要对接我的接口，获取接口返回中的某个字段值，接口返回格式如下：value1,value2,value3,…,valuen，让我帮忙写了一个获取目标字符串的存储过程，如下：

/*
  存储功能：获取目标字符串()
  @STR_PARAM：入参[规范：1,2,3,4,5,6,7,8,9,0]
  @STR_NUMBER：需要获取的字符串所在的位置
  @STR_LENGTH：从头开始到需要获取的字符串在目标字符串中长度
 */
CREATE OR REPLACE PROCEDURE PROC_GET_PARAM(STR_PARAM  IN VARCHAR2,
                                           STR_NUMBER IN VARCHAR2, 
                                           STR_LENGTH in VARCHAR2,
                                           STR_STRING OUT VARCHAR2) AS   
  STR_ONE NUMBER;

BEGIN
  STR_ONE := STR_NUMBER - 1; 


  IF STR_NUMBER > STR_LENGTH THEN
    STR_STRING := '超出目标字符串长度';
  ELSE
    IF STR_NUMBER = STR_LENGTH THEN
      STR_STRING := SUBSTR(STR_PARAM, INSTR(STR_PARAM, ',', 1, STR_ONE) + 1);
    ELSE
      IF STR_NUMBER = 1 THEN
        STR_STRING := SUBSTR(STR_PARAM, 1, INSTR(STR_PARAM, ',', 1, 1) - 1);
      ELSE
        STR_STRING := SUBSTR(STR_PARAM,
                             INSTR(STR_PARAM, ',', 1, STR_ONE) + 1,
                             INSTR(STR_PARAM, ',', 1, STR_NUMBER) -
                             INSTR(STR_PARAM, ',', 1, STR_ONE) - 1);
      END IF;
    END IF;
  END IF;

END PROC_GET_PARAM;

应用2：

/*
 *存储功能：根据工单编号判断该工单是否存在，如果存在返回0；不存在返回1，并将该张工单记入数据库，并记录改动日志。
 *STR_NUM：入参，工单编号
 *STR_RETURN_CODE：返回编码
 */
CREATE OR REPLACE PROCEDURE PROC_ONE_HOUR(STR_NUM      IN VARCHAR2,
                                          STR_RETURN_CODE OUT VARCHAR2) AS
  STR_IS          NUMBER;
BEGIN

  --判断该工单是否存在
  SELECT COUNT(*) INTO STR_IS FROM TBL_ONEHOUR T WHERE T.ANI = STR_NUM;
  --如果存在该工单，返0；不存在返1

  IF STR_IS > '0' THEN
    STR_RETURN_CODE := '0';

  ELSE
    --如果不存在,返回1,并写入数据库
    STR_RETURN_CODE := '1';
    INSERT INTO TBL_ONEHOUR VALUES (STR_NUM, SYSDATE);
    COMMIT;
  END IF;

  INSERT INTO TBL_ONEHOUR_LOG
  VALUES
    (STR_NUM, STR_RETURN_CODE, SYSDATE);
  COMMIT;

END PROC_ONE_HOUR;

存储过程的使用较为复杂，但是用的好的话又能极大地提高效率，以上只是在学习过程中的一些简答示例，更加具体的使用就需要各位在实际应用中去发掘了。