首页 / MSSQL / SqlServer存储过程实例讲解

SqlServer存储过程实例讲解

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SqlServer存储过程实例讲解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5764字，纯文字阅读大概需要9分钟。

内容图文

一、遇到的问题我们在之前学习的课程写了不少的批处理语句，这些批处理语句存在两个问题： 1.没法像函数那样传参数运行(自定义化) 2.没法像函数那样可以反复地调用(功能化模块化) 说到这里，我们可以猜到，数据库中应该可以建立函数形式的数据库对象来解决

　　一、遇到的问题

　　我们在之前学习的课程写了不少的批处理语句，这些批处理语句存在两个问题：

　　1.没法像函数那样传参数运行(自定义化)

　　2.没法像函数那样可以反复地调用(功能化模块化)

　　说到这里，我们可以猜到，数据库中应该可以建立函数形式的数据库对象来解决这样的问题。但是介绍这种数据库对象之前，我们再来看几个需要解决的问题：

　　我们已经学会把一条select语句封装在视图中，但是它只能用来查询，如果我们希望进行其它操作，比如增删改记录、建删库表等，是不能用视图来完成的。

　　另一方面，如果把一个带有更改操作的批处理整个存储成数据库对象，还可以把处理数据的程序移动到离数据尽可能近的地方，而不是总把这些操作写在客户端程序(如Java、C#)中。通过将处理数据的程序从客户应用程序移动到服务器，可以降低网络流量，并提高性能和数据的完整性。

　　二、存储过程的概念

　　解决上面的问题，我们可以使用一种叫做“存储过程”的数据库对象。

　　存储过程(Stored Procedure) 把我们经常用到的一串复杂sql语句保存成一个数据库对象，并给它起一个名字。每次使用存储过程只需要使用如下的形式即可：

　　exec proc 存储过程名

　　存储过程还可以带参数运行：

　　exec proc 存储过程名参数值[, 参数值...]

　　? 存储过程并不神秘，它就是批处理。之前提到视图是保存在服务器上的命名select语句，与之类似，存储过程是保存在服务器上的命名批处理，，系统将预先对它进行编译。

　　? 存储过程可以包含几乎所有的T-SQL语句，如数据存取语句、流程控制语句、错误处理语句等，使用起来弹性很大。

　　? 数据库中也存在着系统函数和用户定义函数这两种对象，用户定义函数的功能和存储过程很像，但是有一定的区别。

　　【存储过程的分类】

　　- 系统存储过程 system stored procedure 前缀sp_ 例如sp_help sp、helpdb

　　- 扩展存储过程 extended stored procedure 前缀xp_ 例如xp_cmdshell

　　- 用户自定义存储过程 user-defined stored procedure 也就是我们自己创建的

　　三、用户自定义存储过程的创建、修改、删除

　　【创建存储过程】

　　create proc 存储过程名 --无参数的存储过程

　　批处理语句

　　【修改存储过程】

　　alter proc 存储过程名 --无参数的存储过程

　　批处理语句

　　可以看到，修改存储过程的语法和创建的语法只差一个单词，把create换成alt即可。

　　【删除存储过程】

　　drop proc 存储过程名

　　我们还可使用management studio来管理存储过程，展开菜单树中的“可编程性”，在“存储过程”的子节点中可以进行各种操作。这里要说一下创建：当点击“新建存储过程”之后，会出现一个基于模板的创建语句。这时点击菜单中的“查询→指定模板参数的值”，即可弹出对话框来对模板进行设置，从而建立我们想要的存储过程。另外，点击菜单中的“视图→模板资源管理器”，可以看到SQL SERVER为我们提供的各种SQL语句模板。

　　【一个简单的例子】

　　--插入一个以时间为用户名的用户

　　create proc insUser

　　begin tran

　　declare @username varchar(20)

　　set @username=convert(varchar(8),getdate(),112)

　　+replace(convert(varchar(10),getdate(),8),':','')

　　if not exists(select * from yonghu where yonghuming=@username)

　　insert into yonghu values

　　(@username,'111111','@163.com','新用户')

　　commit tran --也可以写commit，但是建议不要去掉tran

　　然后使用exec执行这个存储过程：

　　exec insUser

　　选中exec这一行，然后按F5快速地反复执行，你会发现在同一秒内只能插入一个用户。

　　这个存储过程一旦建立就不能再次执行这段代码了，可以把create改成alter来修改。

　　注意存储过程的代码中不能go语句，因为go是用来提交批的，一旦遇到go系统会认为这个存储过程的代码已经书写完毕，会提交create或者alter的批处理。如果希望在存储过程中执行另一个批处理，请把该批处理写成另一个存储过程并调用。

　　四、用户自定义存储过程的参数传递和返回值

　　【传递参数】

　　create|alter proc 存储过程名

　　@参数名参数类型[,

　　@参数名参数类型...]

　　批处理语句

　　还记得我们前面做过的一个案例吗?

　　declare @tablename nvarchar(10),@id varchar(10),@idvalue int

　　declare @sql varchar(100)

　　set @tablename='yiren'

　　set @id='yirenid'

　　set @idvalue=10

　　set @sql='select * from '+@tablename+' where '+@id+' = '+cast(@idvalue as varchar)

　　print @sql

　　exec(@sql)

　　现在我们把它写成存储过程。这样我们每次都可以从一个指定表中提取我们想要的记录了

　　create proc queryItem

　　@tablename nvarchar(10),

　　@id varchar(20),

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

　　declare @sql varchar(100)

　　set @sql='select * from '+@tablename+'

　　where '+@id+' = '+cast(@idvalue as varchar)

　　exec(@sql)

　　调用的方法：

　　exec queryItem 'yiren','yirenid',@idvalue=10

　　queryItem 'jingjiren','jingjirenid',1

　　存储过程不使用exec也可以调用，但是不推荐这么做。存储过程参数的名字可以在调用时写出来，但是这是完全没必要的，所以@idvalue=10直接写成10就可以了。

　　exec加不加括号效果不一样。加括号是执行sql语句，不加括号是执行存储过程。

　　【返回值】

　　1.以retrun返回，始终是整数值

　　return只能返回整数，即使不显式写出“return 整数值”这样的语句，存储过程也会自动返回一个数值0表示成功。我们可以在发生错误时返回非0值，表示有错误发生。不要试图使用return返回一个在存储过程中处理的结果，比如姓名、生日之类的内容，因为它是整数，功能极为有限。我们只用它返回存储过程执行的状态就足够了。请看例子：

　　create proc returnProc

　　begin tran

　　declare @error int

　　insert into yiren (xingming) values ('王美丽')

　　set @error=@@error

　　insert into yiren (yirenid) values (1)

　　set @error=@error+@@error

　　if @error>0

　　rollback tran

　　else

　　commit tran

　　return @error

　　调用的方法：

　　declare @error int

　　--set @error=exec returnProc --这样写是错误的……

　　exec @error=returnProc

　　select '返回值'=@error

　　如果returnProc有个参数@xingming希望传入'王美丽'，可以这样调用：

　　exec @error=returnProc '王美丽'

　　--或者：

　　exec @error=returnProc @xingming='王美丽'

　　2.以output参数返回数据

　　output可以用来返回任何类型的数据，严格来说，它并不是一个“返回值”，而是一个能够被存储过程调用代码处看到的“外部变量”。这样说的原因看下面的例子就明白了：

　　--通过id查询艺人的姓名和年龄

　　create proc queryProfile

　　@id int,

　　@xingming varchar(50) output, --必须有output

　　@nianling int output

　　select @xingming=xingming,@nianling=nianling

　　from yiren where yirenid=@id

　　调用的方法：

　　declare @xingming varchar(50),@nianling int

　　exec queryProfile 1,@xingming output,@nianling output --必须有output

　　print '1号艺人的姓名是'+@xingming

　　+'，年龄是'+cast(@nianling as varchar)+'岁'

内容总结

以上是互联网集市为您收集整理的SqlServer存储过程实例讲解全部内容，希望文章能够帮你解决SqlServer存储过程实例讲解所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/558400.html

来源：【匿名】

【上一篇】SQLServer工作技巧【下一篇】一个odbc连mssql分页的类

更多 ►

【SqlServer存储过程实例讲解】教程文章相关的互联网学习教程文章

MSSQL存储过程实现拼接sql的注意点【代码】

这里我昨天碰到的问题就是执行一段根据变量tableName对不同的表进行字段状态的更改。由于服务器原因，我不能直接在数据访问层写SQL，所以只好抽离出来放到存储过程里面。这里就出现了一个问题，我花费了好久才弄通！　　其实就是很简单的一个SQL语句：　　update table1 set field1=value1,field2 = value2 where id = id　　我写成什么样子了呢？大家且看：　declare@tableNamenvarchar(50),@field1int,@field2nvarchar,@idintdec...

MSSQL手札三 MSSQL存储过程【代码】

--存储过程完成一段sql代码的封装createproc trim --参数列表，多个间用逗号分隔@strvarchar(10) as--自定义代码段declare@str1varchar(10) set@str1=LTRIM(RTRIM(@str)) print@str1--使用存储过程exec trim ‘ abc ‘--‘abc‘ --===带输出参数的存储过程 --求两个数的和createproc sum1 @num1int, @num2int, @resultint output--表示这个参数可以将结果带出存储过程asset@result=@num1+@num2declare@r1intexec sum1 1,2,@r1 outp...

MSSQL存储过程1【代码】

ALTER PROCEDURE SP_TEST ASBEGINif 1=0SELECT UserName,TrainerID,TrainerInfoID FROM T_TrainerInfo;elseselect * from T_UserInfo; ENDGO原文：http://my.oschina.net/0x4ad/blog/413895

SQL Server 存储过程【代码】

Transact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。? 存储过程的概念存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返...

sql server存储过程简单使用【代码】【图】

1 创建存储过程　　1.1 创建简单的存储过程　　　创建语句：createproc usp_helloworld asbeginprint‘Hello World‘end　　创建完后，调用语句格式 exec + 存储过程名：exec usp_helloworld执行结果：　　1.2 创建带参数的存储过程　　　语句：createproc usp_book @namenvarchar(50), @authornvarchar(50) asbeginselect*from book where name=@nameand author=@authorend　　表中的数据：　　调用存储过程，因为定义两个参数，在...

SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写【代码】

一.SqlServer 中 1. 创建表 createtable testuser(id int, --primary key,names varchar(50),address varchar(50),paw varchar(50) )2.创建存储过程createproc testp12(@idint ,@namesvarchar(50),@addressvarchar(50),@pawvarchar(50)) asbegindeclare@maxidintif@idisnullor@id<5beginreturn-1;end--set IDENTITY_INSERT testuser oninsertinto testuser(id,names,address,paw)values(@id,@names,@address,@paw) --set IDENTITY_...

SQL server 变量if，while，存储过程【图】

一.变量1.if循环 2.3.while循环declare @ss int 　　set @ss =2while @ss<10begin 　　print ‘Hello‘ 　　set @ss=@ss+1end　　--break 跳出循环　　declare @sss int 　　set @sss=2　　while @sss<10begin 　　print‘Hello‘ 　　set @sss=@sss+1if @sss=6 　　breakend　　--continue 跳出本次循环，继续下次循环　　declare @ssss int 　　set @ssss=2while @ssss<10begin 　　set @ssss=@ssss+1 if @ssss>4an...

sqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询计划】

复制代码代码如下:--代码一DECLARE @cc INT SELECT NewsId,ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex INTO #tb FROM news WITH(NOLOCK) WHERE NewsTypeId=@NewsTypeId AND IsShow=1 SET @cc = @@ROWCOUNT SELECT n.* FROM news AS n WITH(NOLOCK), #tb As t WHERE t.RowIndex>@PageIndex*@PageSize AND t.RowIndex<=(@PageIndex+1)*@PageSize AND t.newsid=n.newsid SELECT @cc DROP TABLE #tb 复制代码代码如下:--...

SQL server 2008里面通过sys.dm_exec_procedure_stats得到存储过程的执行信息--转【代码】

--转自：http://blogs.msdn.com/b/apgcdsd/archive/2011/05/13/sql-server-2008-sys-dm-exec-procedure-stats.aspx很多DBA都希望能够得到某个存储过程的执行信息,比如: 1) 执行了多少次2) 执行的执行计划如何3) 执行的平均读写如何4) 执行平均需要多少时间等等. 幸运的是SQL server 2008 提供了一个这样的DMV，使得我们比较容易就得到上面的信息。这个DMV是sys.dm_exec_procedure_stats，它输出了下面的信...

SQL Server常用系统存储过程大全

1. sp_help 报告有关数据库对象（sys.sysobjects 兼容视图中列出的所有对象）　 sp_help 表名称,存储过程名称等2. sp_helpdb 报告有关数据库的有效信息 sp_helpdb 数据库名称3. sp_helptext 默认值,未加密的transact-SQL存储过程.用户定义Transaction-SQL函数.触发器.计算列,CHECK约束,视图活系统对象　　sp_helptext 存储过程名称等4 sp_helpindex 查看有关表或视图上的索引的信息　 sp_helpindex ...

.net SQLSERVER 分页存储过程

create PROCEDURE [dbo].[proc_SplitPage]@tblName varchar(255), -- 表名@strFields varchar(1000) = ‘*‘, -- 需要返回的列，默认*@strOrder varchar(255)=‘‘, -- 排序的字段名，必填@strOrderType varchar(10)=‘ASC‘, -- 排序的方式,默认ASC@PageSize int = 10, -- 页尺寸,默认10@PageIndex int = 1, -- 页码,默认1@strWhere varchar(1500) = ‘‘ -- 查询条件 (注意: 不要加 where)ASd...

一次性压缩Sqlserver2005中所有库日志的存储过程

通过下面这个可以执行单个数据库日志压缩，问题每次都要写数据库名字，日志大的话执行速度没有那么快有没有办法更快一点？有没有办法一次性收缩所有数据库？代码如下:alter database 数据库名 set recovery simple go dbcc shrinkdatabase (数据库名) go alter database 数据库名 set recovery full go 目前也有压缩日志的工具，一个B/S界面形式的操作压缩数据库的，就是在选择数据库的时候老需要重新去选择具体的库，而且数据...

压缩SQLServer数据库日志的一个存储过程

use master --注意,此存储过程要建在master数据库中 go if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_compdb]) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure [dbo].[p_compdb] GO create proc p_compdb @dbnause master --注意,此存储过程要建在master数据库中go if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_compdb]) and OBJECTPROPERTY(id, NIsProcedu...

一次性压缩Sqlserver2005中所有库日志的存储过程

有没有办法更快一点？有没有办法一次性收缩所有数据库？代码如下: alter database 数据库名 set recovery simple go dbcc shrinkdatabase (数据库名) go alter database 数据库名 set recovery full go 目前也有压缩日志的工具，一个B/S界面形式的操作压缩数据库的，就是在选择数据库的时候老需要重新去选择具体的库，而且数据库数量很大的时候，有些库被压缩了，并没有自动排序; 目前需要的是被压缩后的数据库自动滚到最后面，...

SQLSERVER临时表导致存储过程重编译(recompile)的一些探讨_MySQL【图】

SQLSERVER为了确保返回正确的值，或者处于性能上的顾虑，有意不重用缓存在内存里的执行计划，而重新编译执行计划的这种行为，被称为重编译（recompile）。那么引发存储过程重编译的条件有哪一些呢？下面罗列了一些导致重编译（recompile）的条件：- 对查询所引用的表或视图进行更改（ALTER TABLE 和 ALTER VIEW）。- 对执行计划所使用的任何索引进行更改。- 对执行计划所使用的统计信息进行更新，这些更新可能是从语句（如 UPDATE ...

首页 / MSSQL / SqlServer存储过程实例讲解

SqlServer存储过程实例讲解

内容导读

内容图文

内容总结

内容备注

内容手机端

【SqlServer存储过程实例讲解】教程文章相关的互联网学习教程文章

SQLSERVER - 相关标签

存储过程 - 相关标签

实例 - 相关标签

MSSQL - 最新教程

MSSQL - 最热教程