|
几乎任何可写成批处理的 TransactSQL 代码都可用于创建存储过程 存储过程的设计规则 存储过程的设计规则包括 CREATE PROCEDURE 定义本身可包括除下列 CREATE 语句以外的任何数量和类型的 SQL 语句存储过程中的任意地方都不能使用下列语句 可在存储过程中创建其它数据库对象可以引用在同一存储过程中创建的对象前提是在创建对象后再引用对象 可以在存储过程内引用临时表 如果在存储过程内创建本地临时表则该临时表仅为该存储过程而存在退出该存储过程后临时表即会消失 如果执行调用其它存储过程的存储过程那么被调用存储过程可以访问由第一个存储过程创建的包括临时表在内的所有对象 如果执行在远程 Microsoft® SQL Server&#; 实例上进行更改的远程存储过程则不能回滚这些更改远程存储过程不参与事务处理 存储过程中参数的最大数目为 存储过程中局部变量的最大数目仅受可用内存的限制 根据可用内存的不同存储过程的最大大小可达 MB 有关创建存储过程的规则的更多信息请参见 CREATE PROCEDURE 限定存储过程内的名称 在存储过程内部如果用于诸如 SELECT 或 INSERT 这样的语句的对象名没有限定用户那么用户将默认为该存储过程的所有者在存储过程内部如果创建存储过程的用户没有限定 SELECTINSERTUPDATE 或 DELETE 语句中引用的表名那么通过该存储过程对这些表进行的访问将默认地受到该过程的创建者权限的限制 如果有其他用户要使用存储过程则用于语句 ALTER TABLECREATE TABLEDROP TABLETRUNCATE TABLECREATE INDEXDROP INDEXUPDATE STATISTICS 和 DBCC 的对象名必须用该对象所有者的名称限定例如Mary 拥有表 marytab如果她希望其他用户能够执行使用该表的存储过程必须在该表用于上述某一条语句时对其表名进行限定 此规则是必需的因为运行存储过程时将解析对象的名称如果未限定 marytab而 John 试图执行该过程SQL Server 将查找 John 所拥有的名为 marytab 的表 加密过程定义 如果要创建存储过程并且希望确保其他用户无法查看该过程的定义那么可以使用 WITH ENCRYPTION 子句这样过程定义将以不可读的形式存储 存储过程一旦加密其定义即无法解密任何人（包括存储过程的所有者或系统管理员）都将无法查看存储过程定义 SET 语句选项 当 ODBC 应用程序与 SQL Server 连接时服务器将自动设置会话的下列选项 SET QUOTED_IDENTIFIER ON SET TEXTSIZE SET ANSI_DEFAULTS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF 这些设置将提高 ODBC 应用程序的可移植性由于基于 DBLibrary 的应用程序通常不设置这些选项所以应在上述所列 SET 选项打开和关闭的情况下都对存储过程进行测试这样可确保存储过程始终能正确工作而不管特定的连接在唤醒调用该存储过程时可能设置的选项需要特别设置其中一个选项的存储过程应在开始该存储过程时发出一条 SET 语句此 SET 语句将只对该存储过程的执行保持有效当该存储过程结束时将恢复原设置 示例 A 创建使用参数的存储过程 下例创建一个在 pubs 数据库中很有用的存储过程给出一个作者的姓和名该存储过程将显示该作者的每本书的标题和出版商 CREATE PROC au_info @lastname varchar() @firstname varchar() AS SELECT au_lname au_fname title pub_name FROM authors INNER JOIN titleauthor ON authorsau_id = titleauthorau_id JOIN titles ON titleauthortitle_id = titlestitle_id JOIN publishers ON titlespub_id = publisherspub_id WHERE au_fname = @firstname AND au_lname = @lastname GO 将出现一条说明该命令未返回任何数据也未返回任何行的消息这表示已创建该存储过程 现在执行 au_info 存储过程 EXECUTE au_info Ringer Anne GO 下面是结果集 ( row(s) affected) B 创建使用参数默认值的存储过程 下例创建一个存储过程 pub_info该存储过程显示作为参数给出的出版商所出版的某本书的作者姓名如果未提供出版商的名称该存储过程将显示由 Algodata Infosystems 出版的书籍的作者 CREATE PROC pub_info @pubname varchar() = Algodata Infosystems AS SELECT au_lname au_fname pub_name FROM authors a INNER JOIN titleauthor ta ON aau_id = taau_id JOIN titles t ON tatitle_id = ttitle_id JOIN publishers p ON tpub_id = ppub_id WHERE @pubname = ppub_name 执行未指定参数的 pub_info EXECUTE pub_info GO 下面是结果集 ( row(s) affected) C 执行用显式值替代参数默认值的存储过程 在下例中存储过程 showind 的 @table 参数默认值是 titles CREATE PROC showind @table varchar() = titles AS SELECT TABLE_NAME = sysobjectsname INDEX_NAME = sysindexesname INDEX_ID = indid FROM sysindexes INNER JOIN sysobjects ON sysobjectsid = sysindexesid WHERE sysobjectsname = @table 列标题（例如TABLE_NAME）可使结果更具可读性下面是该存储过程显示的 authors 表的情况 EXECUTE showind authors GO ( row(s) affected) 如果用户未提供值则 SQL Server 将使用默认表 titles EXECUTE showind GO 下面是结果集 ( row(s) affected) D 使用参数默认值 NULL 创建存储过程 参数默认值可以是 NULL 值在这种情况下如果未提供参数则 SQL Server 将根据存储过程的其它语句执行存储过程不会显示错误信息 过程定义还可指定当不给出参数时要采取的其它某种措施例如 CREATE PROC showind @table varchar() = NULL AS IF @table IS NULL PRINT Give a table name ELSE SELECT TABLE_NAME = sysobjectsname INDEX_NAME = sysindexesname INDEX_ID = indid FROM sysindexes INNER JOIN sysobjects ON sysobjectsid = sysindexesid WHERE sysobjectsname = @table E 使用包含通配符的参数默认值创建存储过程 如果存储过程将参数用于 LIKE 关键字那么默认值可包括通配符（%_[] 和 [^]）例如可将 showind 修改为当不提供参数时显示有关系统表的信息 CREATE PROC showind @table varchar() = sys% AS SELECT TABLE_NAME = sysobjectsname INDEX_NAME = sysindexesname INDEX_ID = indid FROM sysindexes INNER JOIN sysobjects ON sysobjectsid = sysindexesid WHERE sysobjectsname LIKE @table 在存储过程 au_info 的下列变化形式中两个参数都有带通配符的默认值 CREATE PROC au_info @lastname varchar() = D% @firstname varchar() = % AS SELECT au_lname au_fname title pub_name FROM authors INNER JOIN titleauthor ON authorsau_id = titleauthorau_id JOIN titles ON titleauthortitle_id = titlestitle_id JOIN publishers ON titlespub_id = publisherspub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname 如果执行 au_info 时不指定参数将显示姓以字母 D 开头的所有作者 EXECUTE au_info GO 下面是结果集 ( row(s) affected) 下例在两个参数的默认值已定义的情况下省略了第二个参数因此可找到姓为 Ringer 的所有作者的书和出版商 EXECUTE au_info Ringer GO ( row(s) affected) |
