SQL存储过程入门与提高

1.什么是存储过程呢？
         将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。
2.存储过程比SQL的优点？(速度快，进行复杂操作，重用率高，安全性高)
     a.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
     b.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
     c.存储过程可以重复使用,可减少数据库开发人员的工作量
     d.安全性高,可设定只有某此用户才具有对指定存储过程的使用权
3.存储过程的种类？
     a系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,
     如 sp_help就是取得指定对象的相关信息
     b.扩展存储过程 以XP_开头,用来调用操作系统提供的功能
             exec master..xp_cmdshell 'ping 10.8.16.1'
     c.用户自定义的存储过程,这是我们所指的存储过程
4.常用格式：
     Create procedure procedue_name
     [@parameter data_type][output]
     [with]{recompile|encryption}
     as
     sql_statement
     解释:
     output：表示此参数是可传回的
     with {recompile|encryption}
     recompile:表示每次执行此存储过程时都重新编译一次
     encryption:所创建的存储过程的内容会被加密
5.实例讲解
     如:
              表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

 

-----------------------------------------------------------------------------------------------------------

【关键词】sql 存储过程 C#

①为什么要使用存储过程?
因为它比SQL语句执行快.

②存储过程是什么?
把一堆SQL语句罗在一起,还可以根据条件执行不通SQL语句.(AX写作本文时观点)

③来一个最简单的存储过程
CREATE PROCEDURE dbo.testProcedure_AX
AS
select userID from USERS order by userid desc

注:dbo.testProcedure_AX是你创建的存储过程名,可以改为:AXzhz等,别跟关键字冲突就行了.AS下面就是一条SQL语句,不会写SQL语句的请回避.

④我怎么在ASP.NET中调用这个存储过程?
下面黄底的这两行就够使了.
         public static string GetCustomerCName(ref ArrayList arrayCName,ref ArrayList arrayID)
         {
             SqlConnection con=ADConnection.createConnection();
             SqlCommand cmd=new SqlCommand("testProcedure_AX",con);
             cmd.CommandType=CommandType.StoredProcedure;
             con.Open();
             try
             {
                 SqlDataReader dr=cmd.ExecuteReader();
                 while(dr.Read())
                 {
                     if(dr[0].ToString()=="")
                     {
                         arrayCName.Add(dr[1].ToString());
                     }
                 }
                 con.Close();
                 return "OK!";
             }
             catch(Exception ex)
             {
                 con.Close();
                 return ex.ToString();
             }
         }
注:其实就是把以前
SqlCommand cmd=new SqlCommand("select userID from USERS order by userid desc",con);
中的SQL语句替换为存储过程名,再把cmd的类型标注为CommandType.StoredProcedure(存储过程)

⑤写个带参数的存储过程吧,上面这个简单得有点惨不忍睹,不过还是蛮实用的.
参数带就带两,一个的没面子,太小家子气了.

CREATE PROCEDURE dbo.AXzhz
/*
这里写注释
*/
@startDate varchar(16),
@endDate varchar(16)
AS
select id   from table_AX where commentDateTime>@startDate and commentDateTime<@endDate order by contentownerid DESC

注:@startDate varchar(16)是声明@startDate 这个变量,多个变量名间用【,】隔开.后面的SQL就可以使用这个变量了.

⑥我怎么在ASP.NET中调用这个带参数的存储过程?

public static string GetCustomerCNameCount(string startDate,string endDate,ref DataSet ds)
{
             SqlConnection con=ADConnection.createConnection();
//-----------------------注意这一段--------------------------------------------------------------------------------------------------------
             SqlDataAdapter da=new SqlDataAdapter("AXzhz",con);

             para0=new SqlParameter("@startDate",startDate);
             para1=new SqlParameter("@endDate",endDate);
             da.SelectCommand.Parameters.Add(para0);
             da.SelectCommand.Parameters.Add(para1);
             da.SelectCommand.CommandType=CommandType.StoredProcedure;
//-------------------------------------------------------------------------------------------------------------------------------

             try
             {
                 con.Open();
                 da.Fill(ds);
                 con.Close();
                 return "OK";
             }
             catch(Exception ex)
             {
                 return ex.ToString();
             }           
         }

注:把命令的参数添加进去,就OK了
鸟的,改字体颜色的东西太垃圾了,改不好,大家凑活着看.

⑦我还想看看SQL命令执行成功了没有.
注意看下面三行红色的语句

CREATE PROCEDURE dbo.AXzhz
/*
   @parameter1 用户名
   @parameter2 新密码
*/
@password nvarchar(20),
@userName nvarchar(20)
AS
declare @err0 int
update WL_user set password=@password where UserName=@userName
set @err0=@@error
select   @err0 as err0

注:先声明一个整型变量@err0,再给其赋值为@@error(这个是系统自动给出的语句是否执行成功,0为成功,其它为失败),最后通过select把它选择出来,某位高人说可以通过Return返回,超出本人的认知范围,俺暂时不会,以后再补充吧

⑧那怎么从后台获得这个执行成功与否的值呢?
下面这段代码可以告诉你答案:
     public static string GetCustomerCName()
         {
             SqlConnection con=ADConnection.createConnection();
           
             SqlCommand cmd=new SqlCommand("AXzhz",con);
             cmd.CommandType=CommandType.StoredProcedure;
             para0=new SqlParameter("@startDate","2006-9-10");
             para1=new SqlParameter("@endDate","2006-9-20");
             da.SelectCommand.Parameters.Add(para0);
             da.SelectCommand.Parameters.Add(para1);
             con.Open();
             try
             {
                Int32 re=(int32)cmd.ExecuteScalar();
                 con.Close();
                 if (re==0)
                  return "OK!";
                 else
                  return "false";
             }
             catch(Exception ex)
             {
                 con.Close();
                 return ex.ToString();
             }
         }
注:就是通过SqlCommand的ExecuteScalar()方法取回这个值,这句话是从MSDN上找的,俺认为改成:
      int re=(int)cmd.ExecuteScalar();   99%正确,现在没时间验证,期待您的测试!!!

⑨我要根据传入的参数判断执行哪条SQL语句!!~
下面这个存储过程可以满足我们的要求,竟然是Pascal/VB的写法,Begin----End ,不是{},,,对使用C#的我来说,这个语法有点恶心.........

ALTER PROCEDURE dbo.selectCustomerCNameCount
@customerID int
AS
if @customerID=-1
begin
select contentownerid ,userCName,count(*) as countAll from view_usercomment group by contentownerid,userCName order by contentownerid DESC
end
else
begin
select contentownerid ,userCName,count(*) as countAll from view_usercomment where contentownerid=@customerID group by contentownerid,userCName order by contentownerid DESC
end

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/gzqok123/archive/2008/07/15/2652189.aspx