存储过程转载

学习存储过程，在网上找到一篇感觉说的还挺细的文章，这里转载一下：

今天是2006年9月20号。昨天开始学习存储过程，并且要在asp.net中调用。经过一天的努力，还算有点收获。我总结了一下，把资料贴上来，供大家来学习，同时也给自己的学习做个记录。
下面我们从最基础的开始学起。
注：黑色字体为原创。
资料：
存储过程的基础教程
什么是存储过程呢？

定义：

将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,
那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。

讲到这里,可能有人要问：这么说存储过程就是一堆SQL语句而已啊？

Microsoft公司为什么还要添加这个技术呢?

那么存储过程与一般的SQL语句有什么区别呢?

存储过程的优点：

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

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

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

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

存储过程的种类：

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
这上面主要是一些基础的教程，说的也很详细，我就不多说了，有朋友不清楚的话，可以留言。
因为我要写的存储过程跟游标有关，所以就又参考了一些关于游标的资料，如下：
declare mycursor cursor for------------定义游标
select cloum1,cloum2 from table
 open mycursor-----------------打开游标

declare @message varchar(100), @user_id varchar(100), @cityid varchar(100)

FETCH NEXT FROM mycursor  INTO @user_id ,@cityid 

WHILE @@FETCH_STATUS = 0

BEGIN    
   SELECT @message = 'user: ' + @user_id + 'bbbbb ' + @cityid
   PRINT @message

   FETCH NEXT FROM mycursor
   INTO  @user_id1 ,@cityid   
end 
close mycursor------------------关闭游标
deallocate mycursor------------释放游标
go 
如果看不明白，这里还有个更详细的：


  
  

   
   SQL SERVER
游标的使用 
  
  
 在这一步中，需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。
形式1（ANSI
92）
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR
select_statement
[FOR {READ ONLY | UPDATE ][OF
column_list]}]
形式2
DECLARE cursor_name CURSOR
[LOCAL |
GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY
| SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE
][OF
column_list]}]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。
SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。如果忽略该选项，则游标只能向前滚动（next）。
Select_statement指明SQL语句建立的结果集。Transact
SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。
READ
ONLY指明在游标结果集中不允许进行数据修改。
UPDATE关键字指明游标的结果集可以修改。
OF
column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。
LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。
GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。
STATIC的游标与INSENSITIVE的游标是相同的。
KEYSET指明选取的行的顺序。SQL
Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。
DYNAMIC指明游标将反映所有对结果集的修改。
SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
注意：
·
如果在SELECT语句中使用了DISTINCT、UNION、GROUP
BY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。
·
如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。
· 如果SELECT语句包含了ORDER BY，而被ORDER
BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQL
ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。
ii.
打开游标
打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：
OPEN { { [GLOBAL]
cursor_name } |
cursor_variable_name}
GLOBAL指明一个全局游标。
Cursor_name是被打开的游标的名称。
Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。
在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。@@cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
iii.
从游标中取值
在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：
FETCH
[NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n |
@nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO
@variable_name
][,……n]]
NEXT指明从当前行的下一行取值。
PRIOR指明从当前行的前一行取值。
FIRST是结果集的第一行。
LAST是结果集的最后一行。
ABSOLUTE
n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。
RELATIVE
n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。
INTO
@cursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。
每一次FETCH的执行都存储在系统变量@@fetch_status中。如果FETCH成功，则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。@@fetch_status可以用来构造游标处理的循环。
例如：
DECLARE
@iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM
author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname
= ‘Albert’
PRINT “Found Albert Ringer”
ELSE
Print “Other
Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
iv.
关闭游标
CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：
CLOSE
[GLOBAL] cursor_name | cursor_variable_name
v.
释放游标
游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：
DEALLOCATE [GLOBAL]
cursor_name | cursor_variable_name
下面给出游标的一个完整的例子：
USE master
GO
CREATE PROCEDURE
sp_BuildIndexes
AS
DECLARE @TableName sysname, @msg varchar(100), @cmd
varchar(100)
DECLARE table_cur CURSOR FOR
SELECT name FROM
sysobjects WHERE type=’u’
OPEN table_cur
FETCH NEXT FROM table_cur INTO
@TableName
WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status =
-2
CONTINUE
SELECT @msg = “Building indexes for table”+@TableName+”…”
PRINT
@msg
SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”')”
EXEC
(@cmd)
PRINT “ “
FETCH NEXT FROM table_cur INTO
@TableName
END
DEALLOCATE
table_cur
GO
下面的脚本将为PUBS数据库执行sp_BuildIndexes
USE pubs
GO
EXEC
ap_BuildIndexes
注意：上面也是创建用户定义的系统存储过程的示例。
使用临时表
临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL
Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。
临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。
虽然我理解了许多基础的东西，但是在真正的写起来，也是很吃力的。不过经过努力还是把一个关于检测死循环的存储过程写了出来，不过还是有错误的（5555......),主要还是游标的问题。因为我在过程中用到了递归操作，在执行过程中，就产生了重复定义游标的问题。至今还未解决。郁闷哦。不过我想一定会解决的。等解决之后再发上来跟大家分享。
接下来我再把关于调用的学习资料贴上：
解析在ASP.NET中调用存储过程的方法
                                       


【摘
要】存储过程的调用在B/S系统中用的很多。传统的调用方法不仅速度慢，而且代码会随着存储过程的增多不断膨胀，难以维护。新的方法在一定程度上解决了这些问题。




　　关键词 ASP.NET；存储过程
　

　　在使用.NET的过程中，数据库访问是一个很重要的部分，特别是在B/S系统的构建过程中，数据库操作几乎成为了一个必不可少的操作。调用存储过程实现数据库操作使很多程序员使用的方法，而且大多数的程序员都是能使用存储过程就使用存储过程，很少直接使用SQL语句，所以存储过程是很有用而且很重要的。
　　存储过程简介
　　简单的说，存储过程是由一些SQL语句和控制语句组成的被封装起来的过程，它驻留在数据库中，可以被客户应用程序调用，也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似，存储过程可以通过名字来调用，而且它们同样有输入参数和输出参数。
　　根据返回值类型的不同，我们可以将存储过程分为三类：返回记录集的存储过程，
返回数值的存储过程（也可以称为标量存储过程），以及行为存储过程。顾名思义，返回记录集的存储过程的执行结果是一个记录集，典型的例子是从数据库中检索出符合某一个或几个条件的记录；返回数值的存储过程执行完以后返回一个值，例如在数据库中执行一个有返回值的函数或命令；最后，行为存储过程仅仅是用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。
　　使用存储过程的好处
　　相对于直接使用SQL语句，在应用程序中直接调用存储过程有以下好处：
　　(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。
　　(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。
　　(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
　　(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
　　由以上的分析可以看到，在应用程序中使用存储过程是很有必要的。
　　两种不同的存储过程调用方法
　　为了突出新方法的优点，首先介绍一下在.NET中调用存储过程的“官方”方法。另外，本文的所有示例程序均工作于SqlServer数据库上，其它情况类似，以后不再一一说明。本文所有例子均采用C#语言。
　　要在应用程序中访问数据库，一般性的步骤是：首先声明一个数据库连接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;

　　为了使代码更具有代表性，要调用的存储过程的第一个和第二个参数都为字符串类型，第三个参数为整型。执行以后发现，完全可以达到预期的效果！

　　两种调用方法的比较
　　
　　通过比较我们可以看到，第二种方法具有一个很明显的优点，那就是可以提高开发速度，节省开发时间，而且代码容易维护，在一定程度上也减少了系统大小。但是，由于对存储过程参数的处理比较笼统，如果要获取输出参数或者得到存储过程的返回值，这种方法就不能满足需要了。虽然如此，但是，这种方法毕竟可以让开发人员少些很大一部分的代码。如果不需要获取输出参数和返回值，那么几乎可以做到“一劳永逸”。因此在实际的程序开发中，这种方法还是具有一定的实用价值的。
注意：上面的param变量是SqlParameter类型的，一定要声明再使用。
例：SqlParameter param1=new
SqlParameter("@MAINPARTID",SqlDbType.VarChar);
方法SqlParameter（），有好多重载函数，你可以根据需要选择使用。
好了今天就到这了，我该去解决那个关于在递归中使用游标的问题了。