SQL Server 存储过程详解.docx

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 ,.n FOR REPLICATION AS ; .n | -SQL语句 ; := ENCRYPTION RECOMPILE -运行时编译 EXECUTE_AS_Clause := BEGIN statements END := EXTERNAL NAME assembly_name.class_name.method_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 else if(3=3) 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、WHILE.BREAK 或 CONTINUE 语句 只要指定的条件为 True 时，WHILE 语句就会重复语句或语句块。REAK 或 CONTINUE语句通常和WHILE一起使用。BREAK 语句退出最内层的 WHILE 循环，CONTINUE 语句则重新开始 WHILE 循环。go declare Num intdeclare ID intdeclare i intset i=1while(exists(select * from T where Num5 ) -获取数量小于5的记录begin select Num=Num,ID=ID from T where Num3) break -退出循环 end 7、CASE 语句 CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是将代码或缩写替换为可读性更强的值-用法一：select ID, Grade=Case Num when 1 then 不及格 when 2 then 不及格 when 3 then 不及格when 4 then 良好else 优秀end from T-用法二：select ID, Grade=Case when Num4 then 优秀end from T三、运行时生成语句 Transact-SQL 支持使用下列两种方法于运行时在 TTransact-SQL 脚本、存储过程和触发器中生成 SQL 语句：使用 sp_executesql 系统存储过程执行 Unicode 字符串。sp_executesql 支持与 RAISERROR 语句类似的参数替换。 使用 EXECUTE 语句执行字符串。EXECUTE 语句不支持已执行字符串中的参数替换。四、处理数据库引擎错误 在 Transact-SQL 中有两种方式可以获取错误信息： 1、在 TRY.CATCH 构造的 CATCH 块的作用域内，您可以使用以下系统函数： ERROR_LINE()，返回出现错误的行号。 ERROR_MESSAGE()，返回将返回给应用程序的消息文本。该文本包括为所有可替换参数提供的值，如长度、对象名或时间。 ERROR_NUMBER() 返回错误号。 ERROR_PROCEDURE()，返回出现错误的存储过程或触发器的名称。如果在存储过程或触发器中未出现错误，该函数返回 NULL。 ERROR_SEVERITY() 返回严重性。 ERROR