存储过程

存储过程：

一、   基本知识  
  　　1 存储过程格式  
  CREATE   PROC存储过程名  
  　　　　[   参数1数据类型[=默认值]，   参数2数据类型[=默认值],   …   参数n数据类型[=默认值]   ]  
  AS  
  　　　　[   DECLARE变量1数据类型，   DECLARE变量2数据类型，   …   DECLARE变量n数据类型   ]   
  BEGIN   
  T-SQL语句   
  END  
  GO  
  注：  
  　　〈1〉方括号内语句根据需要可以有，也可没有。  
  　　〈2〉T-SQL中自定义之全局变量，前缀为@@,具体有那些全局变量可参阅有关书藉，存储过程自定义之局部变量，前缀为@
  　　〈3〉基本数据类型种类：  
  　　numeric(m,n)   浮点数据类型，m：总位数，n：小数位数  
  　　int   　　整数数据类型  
  　　char(m)　　   字符串数据类型，当m为一时，表示单个字符  
  　　〈4〉默认值：当调用此存储过程时如未向此参数传递值，则此参数取此默认值，如果默认值为null时，可返回定制的消息  
  　　2 基本语法、命令  
  　　IF…ELSE　　　   　　条件执行语句  
  　　WHILE　　　　　   　   循环执行语句  
  　　GOTO标号　　　   　　跳转语句  
  　　RETURN[返回数]   　　退出命令  
  　　PRINT[参数]　　   　   输出命令  
  　　BREAK　　　　   　　   退出循环  
  　　CONTINUE　　　   　　跳至下一循环  
  　　RAISERROR　　   　　   显示自定义错误信息  
  　　WAITFOR　　　   　　   等待某一事件发生后执行  
  　　BEGIN…END　　   　　将一组SQL语句作为一个语句块  
  　　GO　　　　　　　　   发送批命令，必须输在行首  
  　　欲知详情，请参阅有关书藉。  
  　　3 游标  
  　　为处理大量数据，常需使用游标，使用方法如下：  
  　　〈1〉定义  
  　　declare   游标名 cursor   for  
  　　   t－sql语句  
  　　〈2〉打开  
  　　   open 游标名  
  　　〈3〉使用：    
  　　fetch 游标名 [into变量1 变量n]  
  　　〈4〉关闭：  
  　　close 游标名  
  　　deallocate   cursor 游标名  

二、   实例

    if   object_id('Change_node') is   not   NULL　　　/＊检查此存储过程是否存在＊/  
  　drop   proc   Change_node　　　　　/＊删除此存储过程＊/  
  　go

 

    create   proc   Change_node  
  　as  
  　begin  

    //局部变量

    declare   @m_rowid   numeric(10,2), @m_terminal   char(3),  @m_node_no_char(4)  
  　declare   another   cursor   for  
  　　   select   rowid,terminal  
  　　　 from授权流水表  
  　　　 where   node_no=‘行号’　　/＊此处为各行全国联行行号的后四位＊ /

         open   another  
  　　   while@@sqlstatus!=2   
  　　　　 begin  
  　　　　    fetch   another   into   @m_rowid,@m_terminal  
  　　　　    select@m_node_no=convert(char(1),0)＋@m_terminal    
  　　        print“终端号：％1!节点号：％2!”@m_terminal,@m_node_no  
  　　        update   授权流水表  
  　　　　　  set   node_no=@m_node_no  
  　　　　　  where   rowid=@m_rowid  
  　　    end

    end   
    close   another  
  　deallocate   cursor   another  
  　end  
  　go

2 常用全局变量

   @@CONNECTIONS    
  返回自上次启动以来连接或试图连接的次数。    
   
  @@CURSOR_ROWS    
  返回连接上最后打开的游标中当前存在的合格行的数量。    
   
  @@DATEFIRST    
  返回每周第一天的数字     
   
  @@ERROR    
  返回最后执行的SQL   语句的错误代码。     
   
  @@FETCH_STATUS    
  返回被   FETCH   语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。    
   
  @@IDENTITY    
  返回最后插入的标识值    
   
  @@LANGID    
  返回当前所使用语言的本地语言标识符(ID)。    
   
  @@LANGUAGE    
  返回当前使用的语言名。    
   
  @@LOCK_TIMEOUT    
  返回当前会话的当前锁超时设置，单位为毫秒。    
   
  @@PROCID    
  返回当前过程的存储过程标识符   (ID)   。    
   
  @@ROWCOUNT    
  返回受上一语句影响的行数。    
   
  @@SERVERNAME    
  返回运行   的本地服务器名称。    
   
  @@SPID    
  返回当前用户进程的服务器进程标识符   (ID)。    
   
  @@TRANCOUNT    
  返回当前连接的活动事务数。    
   
  @@VERSION    
  返回当前安装的日期、版本和处理器类型

 

3 存储过程好处

    1.存储过程可以使得程序执行效率更高、安全性更好，因为过程建立之后 已经编译并且储存到数据库，直接写sql就需要先分析再执行因此过程效率更高，直接写sql语句会带来安全性问题，如：sql注入 
    2.建立过程不会很耗系统资源，因为过程只是在调用才执行。 

 

    1、预编译，存储过程预先编译好放在数据库内，减少编译语句所花的时间。
    2、缓存，编译好的存储过程会进入缓存，所以对于经常执行的存储过程，除了第一次执行外，其他次执行的速度会有明显提高。
    3、减少网络传输，特别对于处理一些数据的存储过程，不必像直接用sql语句实现那样多次传送数据到客户端。
   4、更好的利用服务器内存，特别对于处理中间数据量不大的情况，存储过程中可以利用存放在内存的表变量。

 

  4 java调用存储过程

     CallableStatement proc = null;
     Connection conn = null;
     try {
       conn = DBUtil.getConn();
       proc = conn.prepareCall("{ ? = call cwnew_pack.addCwform(?) }");
       proc.registerOutParameter(1, Types.CHAR);
       proc.setString(2, 参数);

       proc.execute();
       String str = proc.getString(1);
       return str;

     }

     finally{

       try {
          proc.close();
         }
     }

 

触发器:

  触发器是一种特殊类型的存储过程,触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求

  优点如下：

        触发器是自动的：它们在对表的数据作了任何修改之后立即被激活。 
        触发器可以通过数据库中的相关表进行层叠更改
        触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂