SQL_存储过程
一、存储过程
系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。
二、存储过程运行流程
三、优势
- 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
- 当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作,如果用程序来完成,就变成了一条条的 SQL 语句,可能要多次连接数据库。而换成存储,只需要连接一次数据库就可以了。
- 存储过程可以重复使用,可减少数据库开发人员的工作量。
- 安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
- 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。
- 执行速度更快。有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用。
- 更强的适应性:由于存储过程对数据库的访问是通过存储过程来进行的,因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动,而这些改动不会对应用程序造成影响。
- 布式工作:应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。
四、创建一个存储过程
创建存储过程的方法除了他使用AS关键字外,和创建数据库中任何其他对象一样。存储过程的基本语法如下:
在语法中,PROC是PROCEDURE的缩写,两个选项的意思一样。在对存储过程命名完之后,接着是参数列表。参数是可选的。关键字AS其后就是实际的代码。
CREATE PROCEDURE|PROC <sproc name> [ [schema.] <data type> [VARYING] [=] [OUT[PUT]] [READONLY] [, [schema.] <data type> [VARYING] [=] [OUT[PUT]] [READONLY] [,... ... ]] [WITH RECOMPILE | ENCRYPTION | [EXECUTE AS { CALLER | SELF | OWNER | <'user name'>}] AS <code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>
简单的存储过程示例:
create proc sp_name as select * from table_name
执行存储过程:
exec sp_name
1.声明参数
声明参数需要以下几个部分的信息:名称、数据类型、默认值、方向、对于名称,有一组简单的规则。
①它必须以@符号(和变量一样)开始。此外,除了不能内嵌空格外,其规则与普通变量规则相同。
②数据类型和名称一样,必须像变量那样声明,采用SQL server内置的或者用户自定义的数据类型。
③声明需要类型时需要注意,当声明cursor类型参数时,必须也使用varying和output选项。同时,output可以简写为out。
语法:
@parameter_name [AS] datatype [=default|NULL] [VARYING] [OUTPUT | OUT]
一个需要传入参数的存储过程示例:
create proc sp_name @name nvarchar(50) as select name from person where name like @name + '%' ;
执行存储过程:
exec sp_name '王' ;
2.提供默认值
在默认值方面,参数与变量不同。对于同样的情况,变量一般初始化为null值,而参数不是。事实上,如果不提供默认值,则会假设参数是必须的,并且当调用存储过程时需要提供一个初始值。
创建一个存储过程如下:
create proc sp_name @name nvarchar(50)=null as if @name id not null select * from person where name=@name else select * from person where id=15
3.输出参数
一个获得output参数的存储过程:
create proc insert_person @id int output --必须注明为output as insert into person values('江南', '18', '188') set @id = @@identity
执行存储过程:
declare @id int --实际上,调用时名称可以不同,例如也可以为@num,@i等等。 exec insert_person @id output --注意此处也要有output select @id
4.返回值,返回值必须是整数
返回值可以用来确定存储过程执行的状态。
SQL Server默认会在完成存储过程时自动返回一个0值。
为了从存储过程向调用代码传递返回值,只需要使用return语句。
(1)return []
要特别注意的是:返回值必须是整数。
关于return语句,最重要的是知道它是无条件地从存储过程中退出的。无论运行到存储过程的哪个位置,子调用return语句之后将不会执行任何一行代码。
下面的存储过程,让其返回一个指定的值,以指示执行状态。
CREATE PROC spTestReturns AS DECLARE @MyMessage nvarchar(50); DECLARE @MyOtherMessage nvarchar(50); SELECT @MyMessage = '第一个RETURN'; PRINT @MyMessage; RETURN 100; --将这里改成返回100 SELECT @MyOtherMessage = '第二个RETURN'; PRINT @MyOtherMessage; RETURN;
执行之后,显示结果如下:
DECLARE @Return int EXEC @Return = spTestReturns //第一个RETURN SELECT @Return //返回100
5.执行存储过程
对于调用存储过程需要注意一下几点:
①对于存储过程声明中的输出参数,需要使用output关键字。
②和声明存储过程时一样,调用存储过程时,必须使用output关键字。这样就对SQL Server作了提前通知,告诉它参数所需要的特殊处理。但需要注意的是,如果忘记包含output关键字,不会产生运行时错误,但是输出的值不会传入变量中(变量很可能是NULL)。
③赋值给输出结果的变量不需要和存储过程中的内部参数拥有相同的名称。
④exec(或execute)关键字是必须的,因为对存储过程的调用并不是批处理要做的第一件事(如果存储过程的调用是批处理的第一件事,则可以不使用exec)。
6.with recompile选项
可以利用存储过程提供的安全性代码和代码封装方面的好处,但还是忽略了预编译代码方面的影响。可以回避未使用正确的查询计划的问题,因为可以确保为特定一次运行创建新的计划。方法就是使用with recompile选项。
(1)可以再运行时包含with recompile。这告诉SQL Server抛弃已有的执行计划并且创建一个新的计划,但只是这一次。也就是说,只是这次使用with recompile选项来执行存储过程。
EXEC spMySproc '1/1/2004' WITH RECOMPILE
(2)也可以通过在存储过程中包含with recompile选项来使之变的更持久。
如果使用这种方式,则在create proc或alter proc语句中的as语句前添加with recompile选项即可。如果通过该选项创建存储过程,那么无论在运行时选择了其他什么选项,每次运行存储过程都会重新编译它。
五、修改存储过程:alter proc
ALTER PROC spPerson AS SELECT * FROM Person WHERE Id = 45
六、删除存储过程:drop proc
DROP PROC|PROCEDURE <sproc name>[;]
七、常用存储过程
1.查询表信息:sp_help name
2.查看数据库信息:sp_helpdb name
3.查看有关表或视图上的索引的信息:sp_helpindex name
4.查看表上的约束信息:sp_helpconstraint name
八、示例
1、
create proc insUser as begin tran declare @username=convert(varchar(8),getdate(),112) +replace(convert(varchar(10),getdate(),8),':','') if not exists(select * from users where user_name=@username) insert into users values (@username,'20','男','新用户') commit tran --也可以写commit,但是建议不要去掉tran go
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器