SQL存储过程教程II

1、说明 SQL Server 是将空字符串解释为单个空格还是解释为真正的空字符串，由兼 容级别设置控制。如果兼容级别小于或等于65 ，SQL Server 就将空字符串解释为单个空格。如果兼容级别等于 70 ，则 SQL Server 将空字符串解释为空字符串。获得有关存储过程的信息 若要显示用来创建过程的文本，请在过程所在的数据库中执行 sp_helptext ， 并使用过程名作为参数。说明 使用 ENCRYPTION 选项创建的存储过程不能使用 sp_helptext 查看若要显示有关过程引用的对象的报表，请使用 sp_depends 若要为过程重命名，请使用 sp_rename 。引用对象

2、SQL Server 允许创建的存储过程引用尚不存在的对象。在创建时，只进行 语法检查。 执行时，如果高速缓存中尚无有效的计划， 则编译存储过程以生成执 行计划。 只有在编译过程中才解析存储过程中引用的所有对象。 因此，如果语法 正确的存储过程引用了不存在的对象，则仍可以成功创建，但在运行时将失败， 因为所引用的对象不存在。延迟名称解析和兼容级别SQL Server 允许 Transact-SQL 存储过程在创建时引用不存在的表。这种 能力称为延迟名称解析。 不过，如果 Transact-SQL 存储过程引用了该存储过程 中定义的表，而兼容级别设置(通过执行 sp_dbcmptlevel 来设

3、置)为 65 ，则 在创建时会发出警告信息。 而如果在运行时所引用的表不存在， 将返回错误信息。执行存储过程成功执行CREATBPROCEDURS句后，过程名称将存储在sysobjects 系统 表中，而 CREATE PROCEDURE的文本将存储在 syscomments中。第一次执 行时，将编译该过程以确定检索数据的最佳访问计划。使用 cursor 数据类型的参数存储过程只能将cursor数据类型用于OUTPUT参数。如果为某个参数指定 了 cursor数据类型，也必须指定 VARYING和OUTPUT参数。如果为某个参数 指定了 VARYING关键字，则数据类型必须是cursor，并且

4、必须指定OUTPUT关 键字。说明 cursor数据类型不能通过数据库 API (例如OLE DB ODBC ADO和 DB-Library)绑定到应用程序变量上。因为必须先绑定OUTPUT参数，应用程序 才可以执行存储过程，所以带有 cursor OUTPUT 参数的存储过程不能通过数据库 API 调用。只有将 cursor OUTPUT 变量赋值给 Transact-SQL 局部 cursor 变量时，才可以通过 Transact-SQL 批处理、存储过程或触发器调用这些过程。Cursor 输出参数在执行过程时，以下规则适用于 cursor 输出参数：对于只进游标，游标的结果集中返回的行只

5、是那些存储过程执行结束时处于 或超出游标位置的行，例如：在过程中的名为 RS 的 100 行结果集上打开一个非滚动游标。过程提取结果集 RS 的头 5 行过程返回到其调用者返回到调用者的结果集 RS 由 RS 的第 6 到 100 行组成，调用者中的游标 处于 RS 的第一行之前。对于只进游标， 如果存储过程完成后， 游标位于第一行的前面， 则整个结果 集将返回给调用批处理、 存储过程或触发器。 返回时，游标将位于第一行的前面。对于只进游标， 如果存储过程完成后， 游标的位置超出最后一行的结尾， 则 为调用批处理、存储过程或触发器返回空结果集。说明 空结果集与空值不同。则将空值传递回调用批处理

6、、 存储过 但该游标从未打开过， 也会出现这种对于可滚动游标， 在存储过程执行结束时， 结果集中的所有行均会返回给调 用批处理、 存储过程或触发器。 返回时，游标保留在过程中最后一次执行提取时 的位置。对于任意类型的游标， 如果游标关闭， 程或触发器。 如果将游标指派给一个参数， 情况。说明 关闭状态只有在返回时才有影响。 例如， 可以在过程中关闭游标， 稍 后再打开游标，然后将该游标的结果集返回给调用批处理、存储过程或触发器。临时存储过程SQL Server 支持两种临时过程：局部临时过程和全局临时过程。局部临时 过程只能由创建该过程的连接使用。 全局临时过程则可由所有连接使用。 局部临 时

7、过程在当前会话结束时自动除去。 全局临时过程在使用该过程的最后一个会话 结束时除去。通常是在创建该过程的会话结束时。临时过程用 # 和 # 命名，可以由任何用户创建。创建过程后，局部过程 的所有者是唯一可以使用该过程的用户。 执行局部临时过程的权限不能授予其他 用户。如果创建了全局临时过程， 则所有用户均可以访问该过程， 权限不能显式 废除。只有在tempdb数据库中具有显式CREATBPROCEDURfi限的用户，才可 以在该数据库中显式创建临时过程 （不使用编号符命名） 。可以授予或废除这些 过程中的权限。说明 频繁使用临时存储过程会在 tempdb 中的系统表上产生争用， 从而对 性能产

8、生负面影响。建议使用 sp_executesql 代替。 sp_executesql 不在系统 表中存储数据，因此可以避免这一问题。自动执行存储过程SQL Server 启动时可以自动执行一个或多个存储过程。 这些存储过程必须由系 统管理员创建，并在 sysadmin 固定服务器角色下作为后台过程执行。这些过程不 能有任何输入参数。对启动过程的数目没有限制，但是要注意，每个启动过程在执行时都会占用一 个连接。如果必须在启动时执行多个过程，但不需要并行执行，则可以指定一个过 程作为启动过程，让该过程调用其它过程。这样就只占用一个连接。在启动时恢复了最后一个数据库后，即开始执行存储过程。若要跳过这

9、些存储 过程的执行， 请将启动参数指定为跟踪标记 4022 。如果以最低配置启动 SQL Server （使用 -f 标记），则启动存储过程也不会执行。若要创建启动存储过程，必须作为 sysadmin 固定服务器角色的成员登录，并 在 master 数据库中创建存储过程。使用 sp_procoption 可以：将现有存储过程指定为启动过程。停止在 SQL Server 启动时执行过程查看 SQL Server 启动时执行的所有过程的列表 存储过程嵌套存储过程可以嵌套，即一个存储过程可以调用另一个存储过程。在被调用过程 开始执行时，嵌套级将增加，在被调用过程执行结束后，嵌套级将减少。如果超出 最

10、大的嵌套级， 会使整个调用过程链失败。可用NESTLEVE函数返回当前的嵌套级。若要估计编译后的存储过程大小，请使用下列性能监视计数器。* 各种分类的高速缓存对象均可以使用这些计数器，包括特殊 sql 、准备 sql 、过程、触发器等。sql_statement 限制除了 SET SHOWPLAN_TEX和SET SHOWPLAN_AL之外（这两个语句必须是批处 理中仅有的语句），任何 SET 语句均可以在存储过程内部指定。所选择的 SET 选 项在存储过程执行过程中有效，之后恢复为原来的设置。如果其他用户要使用某个存储过程，那么在该存储过程内部，一些语句使用的 对象名必须使用对象所有者的名称

11、限定。这些语句包括：ALTER TABLECREATE INDEXCREATE TABLE所有 DBCC 语句DROP TABLEDROP INDEXTRUNCATE TABLEUPDATE STATISTICS权限CREATE PROCEDUR的权限默认授予sysadmi n固定服务器角色成员和db_owner 和 db_ddladmin 固定数据库角色成员。 sysadmin 固定服务器角色成员和 db_owner固定数据库角色成员可以将 CREATE PROCEDURES限转让给其他用户。 执 行存储过程的权限授予过程的所有者，该所有者可以为其它数据库用户设置执行权 限。示例A. 使用带

12、有复杂 SELECT 语句的简单过程 下面的存储过程从四个表的联接中返回所有作者 (提供了姓名) 、出版的书 籍以及出版社。该存储过程不使用任何参数。USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = au_info_all AND type = P)DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor taO

13、N a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idGOau_info_all 存储过程可以通过以下方法执行：EXECUTE au_info_all- OrEXEC au_info_all如果该过程是批处理中的第一条语句，则可使用：au_info_all天涯风云 19:31:29B. 使用带有参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者 (提供了姓名) 、出版 的书籍以及出版社。该存储过程接受与传递的参数

14、精确匹配的值。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = au_info AND type = P) DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_infolastname varchar(40),firstname varchar(20) ASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INN

15、ER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idWHERE au_fname = firstnameAND au_lname = lastnameGOau_info 存储过程可以通过以下方法执行：EXECUTE au_info Dull, Ann- OrEXECUTE au_info lastname = Dull, firstname = Ann - OrEXECUTE au_info firstname = Ann, lastname = Dull - OrEX

16、EC au_info Dull, Ann- OrEXEC au_info lastname = Dull, firstname = Ann - OrEXEC au_info firstname = Ann, lastname = Dull如果该过程是批处理中的第一条语句，则可使用：au_info Dull, Ann- Orau_info lastname = Dull, firstname = Ann - Orau_info firstname = Ann, lastname = Dull天涯风云 19:31:41C. 使用带有通配符参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作

17、者 （提供了姓名） 、出版 的书籍以及出版社。 该存储过程对传递的参数进行模式匹配， 如果没有提供参数， 则使用预设的默认值。USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = au_info2 AND type = P)DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_info2lastname varchar(30) = D%,firstname varchar(18) = %ASSELECT au_lname, au_fname, title, pub_nameF

18、ROM authors a INNER JOIN titleauthor taON a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idWHERE au_fname LIKE firstnameAND au_lname LIKE lastnameGOau_info2 存储过程可以用多种组合执行。下面只列出了部分组合：EXECUTE au_info2- OrEXECUTE au_info2 Wh%- OrEXECUTE au_i

19、nfo2 firstname = A%- OrEXECUTE au_info2 CKarsOEn- OrEXECUTE au_info2 Hunter, Sheryl- OrEXECUTE au_info2 H%, S%天涯风云 19:32:22D. 使用OUTPUT参数OUTPUT数允许外部过程、批处理或多条 Tran sact-SQL语句访问在过程 执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum) ，并使用 一个可选的输入参数和一个输出参数。首先，创建过程：USE pubsGOIF EXISTS(SELECT name FROM sysobjectsWHERE n

20、ame = titles_sum AND type = P)DROP PROCEDURE titles_sumGOUSE pubsGOCREATEPROCEDURtitEles_sum TITLvEarchar(40) = %, SUmMoney OUTPUTASSELECT Title Name = titleFROM titlesWHERE title LIKE TITLESELECT SUM = SUM(price)FROM titlesWHERE title LIKE TITLEGO接下来，将该OUTPUT参数用于控制流语言。说明 OUTPUTS量必须在创建表和使用该变量时都进行定义参

21、数名和变量名不一定要匹配， 不过数据类型和参数位置必须匹配 (除非使 用 SUM = variable 形式)。DECLARE TOTALCOST moneyEXECUTE titles_sum The%, TOTALCOST OUTPUTIF TOTALCOST 200BEGINPRINT PRINT All of these titles can be purchased for less than $200. ENDELSESELECT The total cost of these titles is $+ RTRIM(CAST(TOTALCOST AS varchar(20)下面是结果集：Title NameThe Busy Executives Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking(3 row(s)