用户定义的存储过程只能在当前数据库中创建(临时过程除外,临时过程总是在 tempdb 中创建)。在单个批处理中,CREATE PROCEDURE 语句不能与其它 Transact-SQL 语句组合使用。 默认情况下,参数可为空。如果传递 NULL 参数值并且该参数在 CREATE 或 ALTER TABLE 语句中使用,而该语句中引用的列又不允许使用 NULL,则 SQL Server 会产生一条错误信息。为了防止向不允许使用 NULL 的列传递 NULL 参数值,应向过程中添加编程逻辑或为该列使用默认值(使用 CREATE 或 ALTER TABLE 的 DEFAULT 关键字)。 建议在存储过程的任何 CREATE TABLE 或 ALTER TABLE 语句中都为每列显式指定 NULL 或 NOT NULL,例如在创建临时表时。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项控制 SQL Server 为列指派 NULL 或 NOT NULL 特性的方式(如果在 CREATE TABLE 或 ALTER TABLE 语句中没有指定的话)。如果某个连接执行的存储过程对这些选项的设置与创建该过程的连接的设置不同,则为第二个连接创建的表列可能会有不同的为空性,并且表现出不同的行为方式。如果为每个列显式声明了 NULL 或 NOT NULL,那么将对所有执行该存储过程的连接使用相同的为空性创建临时表。 在创建或更改存储过程时,SQL Server 将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。执行存储过程时,将使用这些原始设置。因此,所有客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行存储过程时都将被忽略。在存储过程中出现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储过程的功能。 其它 SET 选项(例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS)在创建或更改存储过程时不保存。如果存储过程的逻辑取决于特定的设置,应在过程开头添加一条 SET 语句,以确保设置正确。从存储过程中执行 SET 语句时,该设置只在存储过程完成之前有效。之后,设置将恢复为调用存储过程时的值。这使个别的客户端可以设置所需的选项,而不会影响存储过程的逻辑。 [示例] A. 使用带有复杂 SELECT 语句的简单过程 下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = /'au_info_all/' AND type = /'P/') DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO au_info_all 存储过程可以通过以下方法执行: EXECUTE au_info_all -- Or EXEC au_info_all 如果该过程是批处理中的第一条语句,则可使用: au_info_all ------------------------- B. 使用带有参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = /'au_info/' AND type = /'P/') DROP PROCEDURE au_info GO USE pubs GO CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO au_info 存储过程可以通过以下方法执行: EXECUTE au_info /'Dull/', /'Ann/' -- Or EXECUTE au_info @lastname = /'Dull/', @firstname = /'Ann/' -- Or EXECUTE au_info @firstname = /'Ann/', @lastname = /'Dull/' -- Or EXEC au_info /'Dull/', /'Ann/' -- Or EXEC au_info @lastname = /'Dull/', @firstname = /'Ann/' -- Or EXEC au_info @firstname = /'Ann/', @lastname = /'Dull/' 如果该过程是批处理中的第一条语句,则可使用: au_info /'Dull/', /'Ann/' -- Or au_info @lastname = /'Dull/', @firstname = /'Ann/' -- Or au_info @firstname = /'Ann/', @lastname = /'Dull/'
------------------------- C. 使用带有通配符参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则使用预设的默认值。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = /'au_info2/' AND type = /'P/') DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE au_info2 @lastname varchar(30) = /'D%/', @firstname varchar(18) = /'%/' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GO au_info2 存储过程可以用多种组合执行。下面只列出了部分组合: EXECUTE au_info2 -- Or EXECUTE au_info2 /'Wh%/' -- Or EXECUTE au_info2 @firstname = /'A%/' -- Or EXECUTE au_info2 /'[CK]ars[OE]n/' -- Or EXECUTE au_info2 /'Hunter/', /'Sheryl/' -- Or EXECUTE au_info2 /'H%/', /'S%/' ------------------------- D. 使用 OUTPUT 参数 OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。 首先,创建过程: USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = /'titles_sum/' AND type = /'P/') DROP PROCEDURE titles_sum GO USE pubs GO CREATE PROCEDURE titles_sum @@TITLE varchar(40) = /'%/', @@SUM money OUTPUT AS SELECT /'Title Name/' = title FROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price) FROM titles WHERE title LIKE @@TITLE GO 接下来,将该 OUTPUT 参数用于控制流语言。 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。 参数名和变量名不一定要匹配,不过数据类型和参数位置必须匹配(除非使用 @@SUM = variable 形式)。 DECLARE @@TOTALCOST money EXECUTE titles_sum /'The%/', @@TOTALCOST OUTPUT IF @@TOTALCOST < 200 BEGIN PRINT /' /' PRINT /'All of these titles can be purchased for less than $200./' END ELSE SELECT /'The total cost of these titles is $/' + RTRIM(CAST(@@TOTALCOST AS varchar(20))) 下面是结果集: Title Name ------------------------------------------------------------------------ The Busy Executive/'s Database Guide The Gourmet Microwave The Psychology of Computer Cooking (3 row(s) affected) Warning, null value eliminated from aggregate. All of these titles can be purchased for less than $200.
------------------------- E. 使用 OUTPUT 游标参数 OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。 首先,创建以下过程,在 titles 表上声明并打开一个游标: USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = /'titles_cursor/' and type = /'P/') DROP PROCEDURE titles_cursor GO CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT AS SET @titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titles OPEN @titles_cursor GO 接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。 USE pubs GO DECLARE @MyCursor CURSOR EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor GO
------------------------- F. 使用 WITH RECOMPILE 选项 如果为过程提供的参数不是典型的参数,并且新的执行计划不应高速缓存或存储在内存中,WITH RECOMPILE 子句会很有帮助。 USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = /'titles_by_author/' AND type = /'P/') DROP PROCEDURE titles_by_author GO CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = /'%/' WITH RECOMPILE AS SELECT RTRIM(au_fname) + /' /' + RTRIM(au_lname) AS /'Authors full name/', title AS Title FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_id = t.title_id WHERE au_lname LIKE @@LNAME_PATTERN GO
------------------------- G. 使用 WITH ENCRYPTION 选项 WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息。 IF EXISTS (SELECT name FROM sysobjects WHERE name = /'encrypt_this/' AND type = /'P/') DROP PROCEDURE encrypt_this GO USE pubs GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM authors GO EXEC sp_helptext encrypt_this 下面是结果集: The object/'s comments have been encrypted. 接下来,选择加密存储过程内容的标识号和文本。 SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE o.name = /'encrypt_this/' 下面是结果集: 说明 text 列的输出显示在单独一行中。执行时,该信息将与 id 列信息出现在同一行中。 id text ---------- ------------------------------------------------------------ 1413580074 ?????????????????????????????????e?????????????????????????????????????????????????????????????????????????? (1 row(s) affected)
-------------------------
H. 创建用户定义的系统存储过程 下面的示例创建一个过程,显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数,该过程将返回表名以 sys 开头的所有表(及索引)。 IF EXISTS (SELECT name FROM sysobjects WHERE name = /'sp_showindexes/' AND type = /'P/') DROP PROCEDURE sp_showindexes GO USE master GO CREATE PROCEDURE sp_showindexes @@TABLE varchar(30) = /'sys%/' AS SELECT o.name AS TABLE_NAME, i.name AS INDEX_NAME, indid AS INDEX_ID FROM sysindexes i INNER JOIN sysobjects o ON o.id = i.id WHERE o.name LIKE @@TABLE GO USE pubs EXEC sp_showindexes /'emp%/' GO 下面是结果集: TABLE_NAME INDEX_NAME INDEX_ID ---------------- ---------------- ---------------- employee employee_ind 1 employee PK_emp_id 2 (2 row(s) affected)
------------------------- I. 使用延迟名称解析 下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在,但每个存储过程都可创建。 IF EXISTS (SELECT name FROM sysobjects WHERE name = /'proc1/' AND type = /'P/') DROP PROCEDURE proc1 GO -- Creating a procedure on a nonexistent table. USE pubs GO CREATE PROCEDURE proc1 AS SELECT * FROM does_not_exist GO -- Here is the statement to actually see the text of the procedure. SELECT o.id, c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = /'P/' AND o.name = /'proc1/' GO USE master GO IF EXISTS (SELECT name FROM sysobjects WHERE name = /'proc2/' AND type = /'P/') DROP PROCEDURE proc2 GO -- Creating a procedure that attempts to retrieve information from a -- nonexistent column in an existing table. USE pubs GO CREATE PROCEDURE proc2 AS DECLARE @middle_init char(1) SET @middle_init = NULL SELECT au_id, middle_initial = @middle_init FROM authors GO -- Here is the statement to actually see the text of the procedure. SELECT o.id, c.text FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE o.type = /'P/' and o.name = /'proc2/'
|