SQL存储过程实例 (有点乱！！)
SQL实例讲解
一、 创建存储过程结构 CREATE PROCEDURE 创建存储过程存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。可以创建一个过程供永久使用或在一个会话中临时 使用局部临时过程或在所有会话中临时使用全局临时过程。也可以创建 在 Microsoft SQL Server启动时自动运行的存储过程。 语法 CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
二、 存储过程实例讲解 1. 使用带有复杂 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 2. 使用带有参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者提供了姓名、出版的书籍 以及出版社。该存储过程接受与传递的参数精确匹配的值。 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' 3. 使用带有通配符参数的简单过程 下面的存储过程从四个表的联接中只返回指定的作者提供了姓名、出版的书籍 以及出版社。该存储过程对传递的参数进行模式匹配如果没有提供参数则使用 预设的默认值。 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*' 4. 使用 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. 5. 使用 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 6. 使用 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 7. 使用 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) 8. 使用延迟名称解析 下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或 列在编译时不存在但每个存储过程都可创建。 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'
