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

 

存储过程是由一些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();