存储过程(调用、优缺点)

原创 2007年09月15日 15:00:00

 

存储过程是由一些SQL语句和控制语句组成的被封装起来的过程,它驻留在数据库中,可以被客户应用程序调用,也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似,存储过程可以通过名字来调用,而且它们同样有输入参数和输出参数。

存储过程的优点:

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

2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

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

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

5.减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。 

6.执行速度更快。有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用。 

7.更强的适应性:由于存储过程对数据库的访问是通过存储过程来进行的,因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动,而这些改动不会对应用程序造成影响。 

8.布式工作:应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。 

存储过程的种类:

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

                               如 sp_help就是取得指定对象的相关信息

    2.扩展存储过程   以XP_开头,用来调用操作系统提供的功能

                              exec master..xp_cmdshell 'ping 10.8.16.1'

    3.用户自定义的存储过程,这是我们所指的存储过程



根据返回值类型的不同,我们可以将存储过程分为三类:

返回记录集的存储过程, 返回数值的存储过程(也可以称为标量存储过程),

行为存储过程。

顾名思义,返回记录集的存储过程的执行结果是一个记录集,典型的例子是从数据库中检索出符合某一个或几个条件的记录;返回数值的存储过程执行完以后返回一个值,例如在数据库中执行一个有返回值的函数或命令;最后,行为存储过程仅仅是用来实现数据库的某个功能,而没有返回值,例如在数据库中的更新和删除操作。

常用格式

   Create procedure procedue_name

   [@parameter data_type][output]

   [with]{recompile|encryption}

   as

        sql_statement

解释:  

output:表示此参数是可传回的

with {recompile|encryption}

recompile:表示每次执行此存储过程时都重新编译一次

encryption:所创建的存储过程的内容会被加密


创建和使用实例:

如:

   表book的内容如下

   编号    书名                           价格

   001      C语言入门                   $30

   002      PowerBuilder报表开发  $52

实例1:查询表Book的内容的存储过程

   create proc query_book

      as 

      select * from book

   go

   exec query_book

实例2:加入一笔记录到表book,并查询此表中所有书籍的总金额

   Create proc insert_book

   @param1 char(10),@param2 varchar(20),@param3 money,@param4 money output

   with encryption  ---------加密

   as

   insert book(编号,书名,价格) Values(@param1,@param2,@param3)

   select @param4=sum(价格) from book

  go

  执行例子: 

  declare @total_price money 

  exec insert_book '003','Delphi 控件开发指南',$100,@total_price

  print '总金额为'+convert(varchar,@total_price)

  go

存储过程的3种传回值:

   1.以Return传回整数

   2.以output格式传回参数

   3.Recordset

传回值的区别:

       output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中  

实例3:设有两个表为Product,Order,其表内容如下:

      Product

           产品编号       产品名称    客户订数     

            001             钢笔         30         

            002             毛笔         50         

            003             铅笔         100        

      order  

           产品编号         客户名     客户订金

            001              南山区      $30

            002              罗湖区      $50

            003              宝安区      $4

请实现按编号为连接条件,将两个表连接成一个临时表,该表只含编号.产品名.客户名.订金.总金额,

总金额=订金*订数,临时表放在存储过程中

代码如下:

     Create proc temp_sale

     as

       select a.产品编号,a.产品名称,b.客户名,b.客户订金,a.客户订数* b.客户订金 as总金额

       into #temptable from Product a inner join order b on a.产品编号=b.产品编号

    if  @@error=0 

       print 'Good'

    else

       print 'Fail'

     go

在.NET中调用存储过程的“官方”方法


要在应用程序中访问数据库,一般性的步骤是:首先声明一个数据库连接SqlConnection,然后声明一个数据库命令SqlCommand,用来执行SQL语句和存储过程。有了这两个对象后,就可以根据自己的需要采用不同的执行方式达到目的。需要补充的是,不要忘记在页面上添加如下的引用语句:using System.Data.SqlClient。


就执行存储过程来说,如果执行的是第一类存储过程,那么就要用一个DataAdapter将结果填充到一个DataSet中,然后就可以使用数据网格控件将结果呈现在页面上了;如果执行的是第二和第三种存储过程,则不需要此过程,只需要根据特定的返回判定操作是否成功完成即可。

  (1)执行一个没有参数的存储过程的代码如下: 



SqlConnection conn=new SqlConnection(“connectionString”); 

SqlDataAdapter da = new SqlDataAdapter(); 

da.SelectCommand = new SqlCommand(); 

da.SelectCommand.Connection = conn; 

da.SelectCommand.CommandText = "NameOfProcedure"; 

da.SelectCommand.CommandType = CommandType.StoredProcedure;  

  然后只要选择适当的方式执行此处过程,用于不同的目的即可。 

  (2)执行一个有参数的存储过程的代码如下(我们可以将调用存储过程的函数声明为ExeProcedure(string inputdate)): 


SqlConnection conn=new SqlConnection(“connectionString”); 

SqlDataAdapter da = new SqlDataAdapter(); 

da.SelectCommand = new SqlCommand(); 

da.SelectCommand.Connection = conn; 

da.SelectCommand.CommandText = "NameOfProcedure"; 

da.SelectCommand.CommandType = CommandType.StoredProcedure; 

(以上代码相同,以下为要添加的代码) 

param = new SqlParameter("@ParameterName", SqlDbType.DateTime); 

param.Direction = ParameterDirection.Input; 

param.Value = Convert.ToDateTime(inputdate); 

da.SelectCommand.Parameters.Add(param);  

  这样就添加了一个输入参数。若需要添加输出参数: 

param = new SqlParameter("@ParameterName", SqlDbType.DateTime); 

param.Direction = ParameterDirection.Output; 

param.Value = Convert.ToDateTime(inputdate); 

da.SelectCommand.Parameters.Add(param);  

  若要获得参储过程的返回值: 


param = new SqlParameter("@ParameterName", SqlDbType.DateTime); 

param.Direction = ParameterDirection.ReturnValue; 

param.Value = Convert.ToDateTime(inputdate); 

da.SelectCommand.Parameters.Add(param);  

  从上面的代码我们可以看出,当存储过程比较多或者存储过程的参数比较多时,这种方法会大大影响开发的速度;另外一方面,如果项目比较大,那么这些用于数据库逻辑的函数在以后的维护中也是一个很大的负担。那么,有没有一种改进的方法可以解决这个问题呢?想到在执行没有参数的存储过程时只需要传入一个存储过程的名字就可以调用相应的存储过程,而且在SqlServer数据库中我们可以直接在查询分析器中敲入“存储过程名(参数列表)”样的字符串就可以执行存储过程,那么,是否可以把这种思想应用到应用程序中呢?  

  于是在编译器中键入相应代码。这些代码是在调用不带参数的存储过程的代码的基础上改的。具体代码如下: 


SqlConnection conn=new SqlConnection(“connectionString”); 

SqlDataAdapter da = new SqlDataAdapter(); 

da.SelectCommand = new SqlCommand(); 

da.SelectCommand.Connection = conn; 

da.SelectCommand.CommandText = "NameOfProcedure(’para1’,’para2’,para3)"; 

da.SelectCommand.CommandType = CommandType.StoredProcedure;  



  为了使代码更具有代表性,要调用的存储过程的第一个和第二个参数都为字符串类型,第三个参数为整型。执行以后发现,完全可以达到预期的效果!
标签:
 
例如有下面存储过程  
  create   proc   contractview  
  (  
    @project_name_2   [nvarchar](50),  
    @contract_name_3   [nvarchar](50)  
  )  
  as  
   
  if   exists(select   *   from   contract   where   contract_name=@contract_name_3)  
  return   1  
   
  if   not   exists(select   *   from   project   where   project_name=@@project_name_2)  
        return   2  
 
p1=cm.Parameters.Add("@returnval",System.Data.OleDb.OleDbType.Integer);  
  p1.Direction=ParameterDirection.Output;  
   
  cm.ExecuteNonQuery();  
  int   x=cm.Parameters[1].Value.ToString();
 

业务逻辑层与存储过程优缺点对比

一,存储过程 优点: 1,减少网络带宽,按理论存储过程会提高性能. 2,无需重新编译,更改后即可运行,无需重新编译代码 3,由专门的dba写的sql语句更高效 4,安全性,(在传输用户名密码...

视图、索引、存储过程优缺点

SQL存储过程的概念 优点及语法整理在学习程序过程之前,先了解下什么是存储过程?为什么要用存储过程,他有那些优点定义:将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来, 那么以后...

数据库-存储过程(概念、优缺点、分类)

(1)概念:     ① 存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集。经编译后存储在数据库 中。     ② 存储过程是数据库中的一个重要对象,用户通过指定存...
  • zdplife
  • zdplife
  • 2015年08月28日 16:39
  • 1906

视图、索引、存储过程优缺点

 1.视图 (1).什么是视图? 视图(View)作为一种数据库对象,为用户提供了一个可以检索数据表中的数据方式。用户通过视图来浏览数据表中感兴趣的部分或全部数据,而数据的物理存储位置仍然在...

使用视图和存储过程的优缺点

使用视图和存储过程的优缺点     –以下内容整理自网络视图的优点:   简单性      视图不仅可以简化用户对数据的理解,也可以简化他们的操作,哪些经常使用的查询可以被定义为视图,从而使用户不必为...

存储过程的优缺点个人总结

转自: http://www.cnblogs.com/ego/archive/2012/12/06/2804592.html 公司的系统是自主开发的,历史比较悠久,有不少是传统C/S架构,采用存储...

存储过程的优缺点及适宜使用的场合详解

存储过程不仅仅适用于大型项目,对于中小型项目,使用存储过程也是非常有必要的。本文我们主要介绍了使用存储过程的优势以及存储过程适宜使用的场合,希望能够对您有所帮助。 我们知道,存储过程在数据库...

存储过程的优缺点是什么

优点: 1.由于应用程序随着时间推移会不断更改,增删功能,T-SQL过程代码会变得更复杂,StoredProcedure为封装此代码提供了一个替换位置。 2.执行计划(存储过程在首次运行时...

存储过程优缺点

存储过程的优缺点: 优点: 1.由于应用程序随着时间推移会不断更改,增删功能,T-SQL过程代码会变得更复杂,StoredProcedure为封装此代码提供了一个替换位置。 2.执行计划...

mysql存储过程优缺点

优点 1.在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器。但这一点便利被许多人滥用了。有人直接就在正式服务器上修改存储过程,而没有经过完整的测试,后果非常...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:存储过程(调用、优缺点)
举报原因:
原因补充:

(最多只允许输入30个字)