存储过程游标详解与实例

.PAGE1.存储过程、游标详解与实例一、遇到的问题我们在之前学习的课程写了不少的批处理语句，这些批处理语句存在两个问题：1.没法像函数那样传参数运行（自定义化）2.没法像函数那样可以反复地调用（功能化模块化）说到这里，我们可以猜到，数据库中应该可以建立函数形式的数据库对象来解决这样的问题。但是介绍这种数据库对象之前，我们再来看几个需要解决的问题：我们已经学会把一条select语句封装在视图中，但是它只能用来查询，如果我们希望进行其它操作，比如增删改记录、建删库表等，是不能用视图来完成的。另一方面，如果把一个带有更改操作的批处理整个存储成数据库对象，还可以把处理数据的程序移动到离数据尽可能近的地方，而不是总把这些操作写在客户端程序（如Java、C#）中。通过将处理数据的程序从客户应用程序移动到服务器，可以降低网络流量，并提高性能和数据的完整性。二、存储过程的概念解决上面的问题，我们可以使用一种叫做“存储过程”的数据库对象。存储过程(StoredProcedure)把我们经常用到的一串复杂sql语句保存成一个数据库对象，并给它起一个名字。每次使用存储过程只需要使用如下的形式即可：execproc存储过程名存储过程还可以带参数运行：execproc存储过程名参数值[,参数值...]存储过程并不神秘，它就是批处理。之前提到视图是保存在服务器上的命名select语句，与之类似，存储过程是保存在服务器上的命名批处理，系统将预先对它进行编译。存储过程可以包含几乎所有的T-SQL语句，如数据存取语句、流程控制语句、错误处理语句等，使用起来弹性很大。数据库中也存在着系统函数和用户定义函数这两种对象，用户定义函数的功能和存储过程很像，但是有一定的区别，我们将在后面的课程中介绍。【存储过程的分类】系统存储过程systemstoredprocedure前缀sp_例如sp_helpsp、helpdb扩展存储过程extendedstoredprocedure前缀xp_例如xp_cmdshell用户自定义存储过程user-definedstoredprocedure也就是我们自己创建的三、用户自定义存储过程的创建、修改、删除【创建存储过程】createproc存储过程名--无参数的存储过程as批处理语句go【修改存储过程】alterproc存储过程名--无参数的存储过程as批处理语句go可以看到，修改存储过程的语法和创建的语法只差一个单词，把create换成alt即可。【删除存储过程】dropproc存储过程名我们还可使用managementstudio来管理存储过程，展开菜单树中的“可编程性”，在“存储过程”的子节点中可以进行各种操作。这里要说一下创建：当点击“新建存储过程”之后，会出现一个基于模板的创建语句。这时点击菜单中的“查询→指定模板参数的值”，即可弹出对话框来对模板进行设置，从而建立我们想要的存储过程。另外，点击菜单中的“视图→模板资源管理器”，可以看到SQLSERVER2005为我们提供的各种SQL语句模板。模板的功能比较高级，有兴趣的同学务必自学一下。【一个简单的例子】--插入一个以时间为用户名的用户createprocinsUserasbegintrandeclare@usernamevarchar(20)set@username=convert(varchar(8),getdate(),112)+replace(convert(varchar(10),getdate(),8),':','')ifnotexists(select*fromyonghuwhereyonghuming=@username)

insertintoyonghuvalues(@username,'111111','@163.com','新用户')committran--也可以写commit，但是建议不要去掉trango然后使用exec执行这个存储过程：execinsUser选中exec这一行，然后按F5快速地反复执行，你会发现在同一秒内只能插入一个用户。这个存储过程一旦建立就不能再次执行这段代码了，可以把create改成alter来修改。注意存储过程的代码中不能go语句，因为go是用来提交批的，一旦遇到go系统会认为这个存储过程的代码已经书写完毕，会提交create或者alter的批处理。如果希望在存储过程中执行另一个批处理，请把该批处理写成另一个存储过程并调用。四、用户自定义存储过程的参数传递和返回值【传递参数】create|alterproc存储过程名

@参数名参数类型[,@参数名参数类型...]as批处理语句go还记得我们前面做过的一个案例吗：declare@tablenamenvarchar(10),@idvarchar(10),@idvalueintdeclare@sqlvarchar(100)set@tablename='yiren'set@id='yirenid'set@idvalue=10set@sql='select*from'+@tablename+'where'+@id+'='+cast(@idvalueasvarchar)print@sqlexec(@sql)现在我们把它写成存储过程。这样我们每次都可以从一个指定表中提取我们想要的记录了createprocqueryItem

@tablenamenvarchar(10),

@idvarchar(20),

@idvalueint--参数外面还可以套上圆括号，看起来更加清晰as

declare@sqlvarchar(100)

set@sql='select*from'+@tablename+'where'+@id+'='+cast(@idvalueasvarchar)

exec(@sql)go调用的方法：execqueryItem'yiren','yirenid',@idvalue=10queryItem'jingjiren','jingjirenid',1存储过程不使用exec也可以调用，但是不推荐这么做。存储过程参数的名字可以在调用时写出来，但是这是完全没必要的，所以@idvalue=10直接写成10就可以了。exec加不加括号效果不一样。加括号是执行sql语句，不加括号是执行存储过程。【返回值】1.以retrun返回，始终是整数值return只能返回整数，即使不显式写出“return整数值”这样的语句，存储过程也会自动返回一个数值0表示成功。我们可以在发生错误时返回非0值，表示有错误发生。不要试图使用return返回一个在存储过程中处理的结果，比如姓名、生日之类的内容，因为它是整数，功能极为有限。我们只用它返回存储过程执行的状态就足够了。请看例子：createprocreturnProcasbegintrandeclare@errorintinsertintoyiren(xingming)values('王美丽')set@error=@@errorinsertintoyiren(yirenid)values(1)set@error=@error+@@errorif@error>0

rollbacktranelse

committranreturn@errorgo调用的方法：declare@errorint--set@error=execreturnProc--这样写是错误的……exec@error=returnProcselect'返回值'=@error如果returnProc有个参数@xingming希望传入'王美丽'，可以这样调用：exec@error=returnProc'王美丽'--或者：exec@error=returnProc@xingming='王美丽'2.以output参数返回数据output可以用来返回任何类型的数据，严格来说，它并不是一个“返回值”，而是一个能够被存储过程调用代码处看到的“外部变量”。这样说的原因看下面的例子就明白了：--通过id查询艺人的姓名和年龄createprocqueryProfile

@idint,

@xingmingvarchar(50)output,--必须有output

@nianlingintoutputas

select@xingming=xingming,@nianling=nianling

fromyirenwhereyirenid=@idgo调用的方法：declare@xingmingvarchar(50),@nianlingintexecqueryProfile1,@xingmingoutput,@nianlingoutput--必须有outputprint'1号艺人的姓名是'+@xingming

+'，年龄是'+cast(@nianlingasvarchar)+'岁'在调用处，我们先定义了两个变量，然后我们以output的方式把两个变量传递给了存储过程。于是存储过程就可以看到这两个来自外部的变量了。那么存储过程中对这两个变量的一切修改都可以立刻体现到调用处的代码中，因为它修改的实际上就是调用处的两个变量。3.select语句的结果集如果在存储过程中执行了select语句并显示结果集（并不是使用select语句给变量赋值），那么这个结果集也可以看做是一种返回值（不能被批处理语句用，但是作为结果集可以被C#等编程语言使用）。createprocselectProcasselect*fromyirengoexecselectProc这种存储过程可以用来实现“带有参数的视图”，在上面【传递参数】中举出的例子就是。【存储过程的常用功能】从存储过程参数和返回值的用法我们可以看出，存储过程通常用来处理一些对数据库的更新操作、或者是按照特定的需要从数据库中查询信息，并以变量的形式（而不是结果集的形式）返回给调用处、或者是以结果集的形式返回，但并不能被调用处的语句所使用。我们可以使用return的数值来监控存储过程执行得是否顺利。【在Java中使用“带有参数的视图”】我们没法像使用视图那样从一个返回select结果集的存储过程中进行二度查询，那么它究竟应该如何使用，我们现在来举一个例子：importjava.sql.*;classProgram{

publicstaticvoidmain(String[]args)throwsClassNotFoundException,SQLException{

//别忘了首先建立系统DSN，如：.\HYGJ

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connectioncon=DriverManager.getConnection("jdbc:odbc:Gohan","sa","111111");

Statementstmt=con.createStatement();

ResultSetrs=stmt.executeQuery("selectProc");while(rs.next())

System.out.println(rs.getString("xingming"));

con.close();

}}上面演示的是最基本的访问实例。可以看到，当这类存储过程没有参数的时候，调用的方法和一条普通select语句没有什么区别。但是当带有参数的时候就不一样了。不要着急，让我们回到存储过程的使用话题上面来。以后我们再学习Java如何调用带参数的存储过程。五、存储过程的嵌套调用在一个存储过程中还可以执行另一个存储过程，即嵌套调用。可以多次嵌套，但最多32层。可以用@@NESTLEVEL来查看嵌套当前层数：createprocproc_1--获取艺人id@yidintoutputas

selecttop1@yid=yirenidfromyiren

wherenicheng='芙蓉姐姐'

print'存储过程1在第'+cast(@@NESTLEVELasvarchar)+'层'go

createprocproc_1_1--获取粉丝id@fidintoutputas

declare@yidint

execproc_1@yidoutput

select@fid=yonghuidfromfensi

whereyirenid=@yid

orderbyyonghuiddesc

print'存储过程1_1在第'+cast(@@NESTLEVELasvarchar)+'层'gocreateprocproc_1_1_1--获得芙蓉姐姐的粉丝as

declare@fidint

execproc_1_1@fidoutput

select*fromyonghu

whereyonghuid=@fid

print'存储过程1_1_1在第'+cast(@@NESTLEVELasvarchar)+'层'goexecproc_1_1_1go六、系统存储过程系统存储过程是SQLSERVER2005系统创建的存储过程，其作用是方便查询系统信息或完成系统管理任务。常用系统sp如下（更多内容查阅联机丛书）：sp_databases

列出服务器上的所有数据库（无参数）sp_server_info

列出服务器信息（可以有参数）sp_stored_procedures

列出当前环境中的所有存储过程（可以有参数）sp_tables

返回当前环境下可查询的对象的列表（无参数）sp_configure

显示或更改当前服务器的全局配置设置sp_help

显示有关数据库对象的信息（可以有参数）如sp_helpyirensp_helpdb

显示有关数据库的信息（可以有参数）如sp_helpdbSuperStarsp_helptext

显示规则、默认值、未加密的存储过程、用户定义函数、触发器

或视图的文本（有参数）如sp_helptextproc_1sp_renamedb

重命名数据库（有参数）如sp_renamedb'pubs','出版社'七、存储过程的注意事项【存储过程的优点】只在创建时编译，执行速度快效率高减少网络传输流量提高安全性模块式编程，可以重复使用统一每次的操作流程【偷偷说一句】前面不管学什么都要提一下缺点，但是存储过程这里却没提。这并不意味着存储过程没有任何缺点，毕竟任何东西都不能滥用，但是可以看出来，存储过程的确是T-SQL编程的核心内容，是最重要的部分。而它本身又是如此地容易掌握，相信你现在的心情不错吧？【在存储过程中使用事务】存储过程中是可以使用事务的，这毫无疑问。在存储过程中使用事务不必用goto语句，在rollback或者commit语句之后直接return即可终止存储过程的执行。前面我们也提到过：在普通批处理中实际上也是可以使用return语句的。八、使用存储过程实现分页查询createprocqueryPage

@tablenamenvarchar(50),--用于传入表名

@idnamenvarchar(50),--用于传入字段名

@pagesizeint,--用于传入每页记录数

@currentpageint,--用于传入希望查看的页面编号

@totalpagesintoutput--用于传出页面总数as--声明保存查询语句的局部变量：declare@sqlasnvarchar(1000)--声明保存记录总数的局部变量：declare@rowcountasint--获得记录总数：set@sql='select@rc=count(*)from'+@tablename--不要直接执行select@rowcount=count(*)from@tablename--将参数传入语句：execsp_executesql@sql,N'@rcintoutput',@rc=@rowcountoutput--将根据每页的行数得到的总页数保存到输出参数中：set@totalpages=ceiling(cast(@rowcountasfloat)/cast(@pagesizeasfloat))if@currentpage>1begin

if@currentpage>@totalpages

begin

set@currentpage=@totalpages--则显示最后一页

end

set@sql='selecttop'+cast(@pagesizeasvarchar)

+'*from'+@tablename+'where'+@idname+'notin(selecttop'

+cast(@pagesize*(@current1)asvarchar)

+''+@idname+'from'+@tablename+'orderby'+@idname+')orderby'+@idnameendelse--只选第一页就不必使用子查询了，提高性能begin

set@sql='selecttop'+cast(@pagesizeasvarchar)

+'*from'+@tablename+'orderby'+@idnameendexec(@sql)--执行查询语句gosp_executesql这个系统存储过程是另一种执行sql语句的方法。它比exec(@sql)的功能高级一些。在这里我们为它传入3个参数，分别是等待处理的@sql（必须是nvarchar类型的）、使用字符串为@sql语句定义新的变量名、为字符串中定义的新变量赋值（@rc是字符串中定义的新变量，把它赋为@rowcount，又因为希望通过@rc为@rowcount返回值，所以指定为output）。ceiling函数得到大于某小数的最小整数，如ceiling(3.5)会得到4。这里把@rowcount和@pagesize相除，得到的数字是个小数。小数部分无法组成一个完整的分页但是不代表没有记录，所以使用ceiling函数，让@totalpages的值能够正确赋值。当传入的currentpage并不在正确的分页编号范围内时，我们的代码做出了处理，给@currentpage赋了一个正确范围内的数值。调用上面的存储过程：declare@totalpagesintexecqueryPage'yiren','yirenid',5,13,@totalpagesoutputprint'一共'+cast(@totalpagesasvarchar)+'行'游标一、遇到的问题实际上，也不算什么太大的问题O(∩_∩)O：我们有时候可能希望在批处理或者存储过程中直接对select结果集进行加工，这个时候，我们需要一种能够让我们逐条处理每一行记录的数据库对象。二、游标的概念解决上面的问题，我们可以使用一种叫做“游标”的数据库对象。游标(Cursor)可以看做一种数据类型，它可以用来遍历结果集，相当于指针，或者是数组中的下标。它处理结果集的方法有以下几种：定位到结果集的某一行从当前结果集的位置搜索一行或一部分行对结果集中的当前行进行数据修改三、游标的使用方法（创建、打开、读取、关闭、删除）【创建游标】和定义各种数据类型的方法有点像，但是注意，不要加“@”（实际上也有“游标类型的变量”，和“游标”的用法几乎完全相同，而且定义时使用@符号）。下面是定义游标的语句：declare游标名cursor[local|global][forward_only|scroll]forselect查询语句游标分为局部游标和全局游标两种，local表示局部游标，global表示全局游标（默认值，可以省略）。当指定forward_only（默认值，可以省略）时，游标是只进的，也就是说只能从头到尾地提取记录，如果需要在行之间来回跳跃，需要指定为scroll。【使用游标】只创建游标但是不使用它，就没有任何意义了。下面我们先举个最简单的例子来演示创建好游标之后的几步使用过程：--【创建游标】declareC1cursorforselectxingmingfromyiren

declare@xingmingvarchar(20)

--【打开游标】openC1--【读取游标】fetchnextfromC1into@xingming--while的特点就是要先写一次

while(@@FETCH_STATUS=0)begin

print'姓名：'+@xingming

fetchnextfromC1into@xingmingend

--【关闭游标】closeC1

--【删除游标】deallocateC1游标的使用方法是不是和Java中的whle(rs.next()){}很像呢？实际上rs.next()执行时就直接在结果集中向后移动一条了，如果没有到达结果集的末端，仍然会执行循环体。在这里使用游标也是一样，@@FETCH_STATUS的值为0时，游标尚未走到结尾。当它不为0了，游标就走到了结尾，将退出循环。fetchnextfrom游标名into变量名列表是一种固定形式的读取游标内容的方法。当查询语句选择了多个字段的时候，读取时也需要借助这句话向多个变量赋值。于是写成变量名列表。【全局游标和scroll游标】前面提到全局游标和scroll游标，下面举个例子：if(CURSOR_STATUS('global','CURSOR_2')!=-3)deallocateCURSOR_2declareCURSOR_2cursorscroll--全局的scroll游标forselectxingming,nicheng,xingbiefromyiren--第一个T-SQL批开始openCURSOR_2declare@seqint,@xingmingvarchar(20),@nichengvarchar(50),@xingbiencharset@seq=4fetchabsolute@seqfromCURSOR_2into@xingming,@nicheng,@xingbieif(@@FETCH_STATUS=0)begin

print'第'+cast(@seqasvarchar)+'个艺人是：'+@xingming

printcase@xingbiewhen'男'then'他'when'女'then'她'end+'的昵称是：'+@nichengendcloseCURSOR_2go--第二个T-SQL批开始openCURSOR_2declare@seqint,@xingmingvarchar(20),@nichengvarchar(50),@xingbiencharset@seq=5--分成了两个批，需要再次定义@seqfetchabsolute@seqfromCURSOR_2into@xingming,@nicheng,@xingbieif(@@FETCH_STATUS=0)begin

print'第'+cast(@seqasvarchar)+'个艺人是：'+@xingming

printcase@xingbiewhen'男'then'他'when'女'then'她'end+'的昵称是：'+@nichengendcloseCURSOR_2go--在第三个批中删除游标deallocateCURSOR_2当开启了scroll选项后，fetch可以用于读next（后移）、prior（前移）、first（第一行）、last（最后一行）、absolute（以数值定位到绝对行）、relative（以数值定位到相对行）。全局游标一旦被定义就会一直存在，所以每个批处理都能看到它。直到使用deallocate来删除它，它才会消失。CURSOR_STATUS('global','CURSOR_2')可以检查它的状态。这个例子可以写成存储过程来调用，你试试吧。【游标的嵌套】由于大大影响系统性能，简单了解一下即可。if(CURSOR_STATUS('global','CURSOR_3')!=-3)deallocateCURSOR_3declareCURSOR_3cursorfor

selectyanchuidfromyanchu

openCURSOR_3declare@ycidintfetchnextfromCURSOR_3

into@ycidwhile(@@FETCH_STATUS=0)begin

print'参加第'+cast(@ycidasvarchar)+'次演出的艺人是：'

declareCURSOR_4cursorfor

selectxingmingfromyirenwhereyirenidin

(selectyirenidfromyanchuyirenwhereyanchuid=@ycid)

--这句使用了子查询，实际上可以再嵌套一个游标

declare@xingmingvarchar(50)

openCURSOR_4

fetchnextfromCURSOR_4into@xingming

while(@@FETCH_STATUS=0)

begin

print@xingming

fetchnextfromCURSOR_4into@xingming

end

closeCURSOR_4

deallocateCURSOR_4

fetchnextfromCURSOR_3

into@ycid

print''end