SQLServer存储过程详解
SQL Server 存储过程详解
存储过程的优缺点
  ◆优点：
  执⾏速度更快。存储过程只在创造时进⾏编译，⽽⼀般SQL语句每执⾏⼀次就编译⼀次，所以使⽤存储过程执⾏速度更快。
  存储过程⽤于处理复杂的操作时，程序的可读性更强、⽹络的负担更⼩。
  使⽤存储过程封装事务性能更佳。
  能有效的放注⼊，安全性更好。
  可维护性⾼，在⼀些业务规则发⽣变化时，有时只需调整存储过程即可，⽽不⽤改动和重编辑程序。
  更好的代码重⽤。
  ◆缺点：
  存储过程将给服务器带来额外的压⼒。
  存储过程多多时维护⽐较困难。
  移植性差，在升级到不同的数据库时⽐较困难。
  调试⿇烦，SQL语⾔的处理功能简单。
  总之复杂的操作或需要事务操作的SQL建议使⽤存储过程，⽽参数多且操作简单SQL语句不建议使⽤存储过程。
存储过程定义
  存储过程是⼀组 Transact-SQL 语句，它们只需编译⼀次，以后即可多次执⾏。因为 Transact-SQL 语句不需要重新编译，所以执⾏存储过程可以提⾼性能。
  触发器是⼀种特殊的存储过程，不由⽤户直接调⽤。创建触发器时，将其定义为在对特定表或列进⾏特定类型的数据修改时激发。
存储过程的设计规则
  CREATE PROCEDURE 定义⾃⾝可以包括任意数量和类型的 SQL 语句，但以下语句除外。
  不能在存储过程的任何位置使⽤这些语句。
  CREATE AGGREGATE、 CREATE RULE、CREATE DEFAULT、 CREATE SCHEMA、CREATE 或 ALTER FUNCTION、CREATE 或 ALTER TRIGGER、CREATE 或 ALTER PROCEDURE、CREATE  或 ALTER VIEW、SET PARSEONLY、SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、 SET SHOWPLAN_XML、USE database_name
  其他数据库对象均可在存储过程中创建。可以引⽤在同⼀存储过程中创建的对象，只要引⽤时已经创建了该对象即可。
  可以在存储过程内引⽤临时表。
  如果在存储过程内创建本地临时表，则临时表仅为该存储过程⽽存在；退出该存储过程后，临时表将消失。
  如果执⾏的存储过程将调⽤另⼀个存储过程，则被调⽤的存储过程可以访问由第⼀个存储过程创建的所有对象，包括临时表在内。
  如果执⾏对远程 Microsoft SQL Server 2005 实例进⾏更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。
  存储过程中的参数的最⼤数⽬为 2100。
  存储过程中的局部变量的最⼤数⽬仅受可⽤内存的限制。
  根据可⽤内存的不同，存储过程最⼤可达 128 MB
实现存储过程
CREATE { PROC|PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type } [ VARYING ][ = default ][ [ OUT
[ PUT ] ] --名称、类型、默认值、⽅向
[ ,...n ]
[ WITH <procedure_option> [ ,...n ]
[ FOR REPLICATION ]
AS
{ <sql_statement>[;][ ...n ]|<method_specifier> } --SQL语句
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]--运⾏时编译
[ EXECUTE_AS_Clause ]
<sql_statement> ::= { [ BEGIN ] statements [ END ] }
<method_specifier> ::= EXTERNAL NAME assembly_name.hod_name
执⾏存储过程
  使⽤ Transact-SQL EXECUTE 语句。如果存储过程是批处理中的第⼀条语句，那么不使⽤ EXECUTE 关键字也可以执⾏存储过程
  使⽤ sp_procoption 让SQLSERVER ⾃动执⾏存储过程
sp_procoption [ @ProcName = ] 'procedure' , [ @OptionName = ] 'option'    , [ @OptionValue = ] 'value' --过程的名称、option 的唯⼀值为 startup、设置为开启（true 或 on）还是关闭（false 或 off）。
⽤TSQL语句编写存储过程
⼀、变量和参数
DECLARE 语句通过以下操作初始化 Transact-SQL 变量：
指定名称。名称的第⼀个字符必须为⼀个 @。
指定系统提供的或⽤户定义的数据类型和长度。对于数值变量还指定精度和⼩数位数。对于 XML 类型的变量，可以指定⼀个可选的架构集合。
将值设置为 NULL。
如：DECLARE @MyCounter int
第⼀次声明变量时，其值设置为 NULL。若要为变量赋值，请使⽤ SET 语句。这是为变量赋值的⾸选⽅法。也可以通过 SELECT 语句的选择列表中当前所引⽤值为变量赋值。
参数⽤于在存储过程和函数以及调⽤存储过程或函数的应⽤程序或⼯具之间交换数据：
输⼊参数允许调⽤⽅将数据值传递到存储过程或函数。
输出参数允许存储过程将数据值或游标变量传递回调⽤⽅。⽤户定义函数不能指定输出参数。
每个存储过程向调⽤⽅返回⼀个整数返回代码。如果存储过程没有显式设置返回代码的值，则返回代码为 0。
⼆、流程控制语句
1、BEGIN 和 END 语句
BEGIN 和 END 语句⽤于将多个 Transact-SQL 语句组合为⼀个逻辑块。在控制流语句必须执⾏包含两条或多条 Transact-SQL 语句的语句块的任何地⽅，都可以使⽤ BEGIN 和 END 语句。
如：
IF (@@ERROR <> 0)
BEGIN
SET @ErrorSaveVariable = @@ERROR
PRINT 'Error encountered, ' +
CAST(@ErrorSaveVariable AS VARCHAR(10))
END
2、GOTO 语句
GOTO 语句使 Transact-SQL 批处理的执⾏跳⾄标签。不执⾏ GOTO 语句和标签之间的语句。
IF(1=1)
GOTO calculate_salary
print 'go on' --条件成⽴则跳过此句。
calculate_salary:
print 'go to'
3、IF...ELSE 语句
IF 语句⽤于条件的测试。得到的控制流取决于是否指定了可选的 ELSE 语句：
if(1=1)
print 1
else if(2=2)
print 2
print 3
else
print 0
4、RETURN 语句
RETURN 语句⽆条件终⽌查询、存储过程或批处理。存储过程或批处理中 RETURN 语句后⾯的语句都不执⾏。当在存储过程中使⽤ RETURN 语句时，此语句可以指定返回给调⽤应⽤程序、批处理或过程的整数值。如果 RETURN 未指定值，则存储过程返回 0
5、WAITFOR 语句
WAITFOR 语句挂起批处理、存储过程或事务的执⾏，直到发⽣以下情况：
已超过指定的时间间隔。
到达⼀天中指定的时间。
指定的 RECEIVE 语句⾄少修改⼀⾏或并将其返回到 Service Broker 队列。
WAITFOR 语句由下列⼦句之⼀指定：
DELAY 关键字后为 time_to_pass，是指完成 WAITFOR 语句之前等待的时间。完成 WAITFOR 语句之前等待的时间最多为 24 ⼩时。
如：
WAITFOR DELAY '00:00:02'
SELECT EmployeeID FROM    Employee;
TIME 关键字后为 time_to_execute，指定 WAITFOR 语句完成所⽤的时间。
GO
BEGIN
WAITFOR TIME '22:00';
DBCC CHECKALLOC;
END;
GO
RECEIVE 语句⼦句，从 Service Broker 队列检索⼀条或多条消息。使⽤ RECEIVE 语句指定 WAITFOR 时，如果当前未显⽰任何消息，该语句将等待消息到达队列。
TIMEOUT 关键字后为 timeout，指定 Service Broker 等待消息到达队列的时间长度（毫秒）。可以在 RECEIVE 语句或 GET CONVERSATION GROUP 语句中指定 TIMEOUT。
6、BREAK 或 CONTINUE 语句
只要指定的条件为 True 时，WHILE 语句就会重复语句或语句块。REAK 或 CONTINUE语句通常和WHILE⼀起使⽤。BREAK 语句退出最内层的WHILE 循环，CONTINUE 语句则重新开始 WHILE 循环。
go
declare@Num int
declare@ID int
declare@i int
set@i=1
while(exists(select*from T where Num<5    )) --获取数量⼩于5的记录
begin
select@Num=Num,@ID=ID from T where Num<5order by ID desc
print Str(@i)+'编号：'+Str(@ID)+'值'+str(@Num)
update T set Num=Num*2where ID=@ID
set@i=@i+1
if(@i>3)
break--退出循环
end
7、CASE 语句
CASE 函数⽤于计算多个条件并为每个条件返回单个值。CASE 函数通常的⽤途是将代码或缩写替换为可读性更强的值
--⽤法⼀：
select ID,
Grade=Case Num
when1then'不及格'
when2then'不及格'
when3then'不及格'
when4then'良好'
else'优秀'
end
from T
---⽤法⼆：
select ID,
Grade=Case
when    Num<3then'不及格'
when    Num=3then'及格'
when    Num=4then'良好'
when    Num>4then'优秀'
end
from T
三、运⾏时⽣成语句
Transact-SQL ⽀持使⽤下列两种⽅法于运⾏时在 TTransact-SQL 脚本、存储过程和触发器中⽣成 SQL 语句：
使⽤ sp_executesql 系统存储过程执⾏ Unicode 字符串。sp_executesql ⽀持与 RAISERROR 语句类似的参数替换。
使⽤ EXECUTE 语句执⾏字符串。EXECUTE 语句不⽀持已执⾏字符串中的参数替换。
四、处理数据库引擎错误
在 Transact-SQL 中有两种⽅式可以获取错误信息：
1、在 CATCH 构造的 CATCH 块的作⽤域内，您可以使⽤以下系统函数：
ERROR_LINE()，返回出现错误的⾏号。
continue语句执行过程ERROR_MESSAGE()，返回将返回给应⽤程序的消息⽂本。该⽂本包括为所有可替换参数提供的值，如长度、对象名或时间。
ERROR_NUMBER() 返回错误号。
ERROR_PROCEDURE()，返回出现错误的存储过程或触发器的名称。如果在存储过程或触发器中未出现错误，该函数返回 NULL。            ERROR_SEVERITY() 返回严重性。
ERROR_STATE()，返回状态。
2、在执⾏任何 Transact-SQL 语句之后，您可以⽴即使⽤ @@ERROR 函数测试错误并检索错误号。
RAISERROR
RAISERROR ⽤于将与 SQL Server Database Engine ⽣成的系统错误或警告消息使⽤相同格式的消息返回到应⽤程序中。
3、PRINT
PRINT 语句⽤于将消息返回到应⽤程序。PRINT 采⽤字符或 Unicode 字符串表达式作为参数，并将字符串作为消息返回到应⽤程序。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。