存储过程学习三(创建存储过程实例)
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 GOau_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 GOau_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.使用 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)