sql server的存储过程例子

最新推荐文章于 2024-09-22 11:17:00 发布

weixin_33989058

最新推荐文章于 2024-09-22 11:17:00 发布

阅读量80

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/haik/archive/2010/05/10/1732035.html

版权

  1 示例
  2 A. 使用带有复杂 SELECT 语句的简单过程
  3 下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。
  4
  5 USE pubs
  6 IF EXISTS ( SELECT name FROM sysobjects
  7           WHERE name = ' au_info_all ' AND type = ' P ' )
  8     DROP PROCEDURE au_info_all
  9 GO
10 CREATE PROCEDURE au_info_all
11 AS
12 SELECT au_lname, au_fname, title, pub_name
13     FROM authors a INNER JOIN titleauthor ta
14        ON a.au_id = ta.au_id INNER JOIN titles t
15        ON t.title_id = ta.title_id INNER JOIN publishers p
16        ON t.pub_id = p.pub_id
17 GO
18
19 au_info_all 存储过程可以通过以下方法执行：
20
21 EXECUTE au_info_all
22 -- Or
23 EXEC au_info_all
24
25 如果该过程是批处理中的第一条语句，则可使用：
26
27 au_info_all
28
29 B. 使用带有参数的简单过程
30 下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。
31
32 USE pubs
33 IF EXISTS ( SELECT name FROM sysobjects
34           WHERE name = ' au_info ' AND type = ' P ' )
35     DROP PROCEDURE au_info
36 GO
37 USE pubs
38 GO
39 CREATE PROCEDURE au_info
40     @lastname varchar ( 40 ),
41     @firstname varchar ( 20 )
42 AS
43 SELECT au_lname, au_fname, title, pub_name
44     FROM authors a INNER JOIN titleauthor ta
45        ON a.au_id = ta.au_id INNER JOIN titles t
46        ON t.title_id = ta.title_id INNER JOIN publishers p
47        ON t.pub_id = p.pub_id
48     WHERE   au_fname = @firstname
49        AND au_lname = @lastname
50 GO
51
52 au_info 存储过程可以通过以下方法执行：
53
54 EXECUTE au_info ' Dull ' , ' Ann '
55 -- Or
56 EXECUTE au_info @lastname = ' Dull ' , @firstname = ' Ann '
57 -- Or
58 EXECUTE au_info @firstname = ' Ann ' , @lastname = ' Dull '
59 -- Or
60 EXEC au_info ' Dull ' , ' Ann '
61 -- Or
62 EXEC au_info @lastname = ' Dull ' , @firstname = ' Ann '
63 -- Or
64 EXEC au_info @firstname = ' Ann ' , @lastname = ' Dull '
65
66 如果该过程是批处理中的第一条语句，则可使用：
67
68 au_info ' Dull ' , ' Ann '
69 -- Or
70 au_info @lastname = ' Dull ' , @firstname = ' Ann '
71 -- Or
72 au_info @firstname = ' Ann ' , @lastname = ' Dull '
73
74 C. 使用带有通配符参数的简单过程
75 下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。
76
77 USE pubs
78 IF EXISTS ( SELECT name FROM sysobjects
79        WHERE name = ' au_info2 ' AND type = ' P ' )
80     DROP PROCEDURE au_info2
81 GO
82 USE pubs
83 GO
84 CREATE PROCEDURE au_info2
85     @lastname varchar ( 30 ) = ' D% ' ,
86     @firstname varchar ( 18 ) = ' % '
87 AS
88 SELECT au_lname, au_fname, title, pub_name
89 FROM authors a INNER JOIN titleauthor ta
90     ON a.au_id = ta.au_id INNER JOIN titles t
91     ON t.title_id = ta.title_id INNER JOIN publishers p
92     ON t.pub_id = p.pub_id
93 WHERE au_fname LIKE @firstname
94     AND au_lname LIKE @lastname
95 GO
96
97 au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：
98
99 EXECUTE au_info2
100 -- Or
101 EXECUTE au_info2 ' Wh% '
102 -- Or
103 EXECUTE au_info2 @firstname = ' A% '
104 -- Or
105 EXECUTE au_info2 ' [CK]ars[OE]n '
106 -- Or
107 EXECUTE au_info2 ' Hunter ' , ' Sheryl '
108 -- Or
109 EXECUTE au_info2 ' H% ' , ' S% '
110
111 D. 使用 OUTPUT 参数
112 OUTPUT 参数允许外部过程、批处理或多条 Transact - SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)，并使用一个可选的输入参数和一个输出参数。
113
114 首先，创建过程：
115
116 USE pubs
117 GO
118 IF EXISTS ( SELECT name FROM sysobjects
119        WHERE name = ' titles_sum ' AND type = ' P ' )
120     DROP PROCEDURE titles_sum
121 GO
122 USE pubs
123 GO
124 CREATE PROCEDURE titles_sum @@TITLE varchar ( 40 ) = ' % ' , @@SUM money OUTPUT
125 AS
126 SELECT ' Title Name ' = title
127 FROM titles
128 WHERE title LIKE @@TITLE
129 SELECT @@SUM = SUM (price)
130 FROM titles
131 WHERE title LIKE @@TITLE
132 GO
133
134 接下来，将该 OUTPUT 参数用于控制流语言。
135
136
137
138 说明  OUTPUT 变量必须在创建表和使用该变量时都进行定义。
139
140
141 参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 @@SUM = variable 形式）。
142
143 DECLARE @@TOTALCOST money
144 EXECUTE titles_sum ' The% ' , @@TOTALCOST OUTPUT
145 IF @@TOTALCOST < 200
146 BEGIN
147     PRINT ' '
148     PRINT ' All of these titles can be purchased for less than $200. '
149 END
150 ELSE
151     SELECT ' The total cost of these titles is $ '
152           + RTRIM ( CAST ( @@TOTALCOST AS varchar ( 20 )))
153
154 下面是结果集：
155
156 Title Name
157 -- ----------------------------------------------------------------------
158 The Busy Executive ' s Database Guide
159 The Gourmet Microwave
160 The Psychology of Computer Cooking
161
162 (3 row(s) affected)
163
164 Warning, null value eliminated from aggregate.
165
166 All of these titles can be purchased for less than $200.
167
168 E. 使用 OUTPUT 游标参数
169 OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。
170
171 首先，创建以下过程，在 titles 表上声明并打开一个游标：
172
173 USE pubs
174 IF EXISTS (SELECT name FROM sysobjects
175       WHERE name = ' titles_cursor ' and type = ' P ' )
176 DROP PROCEDURE titles_cursor
177 GO
178 CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
179 AS
180 SET @titles_cursor = CURSOR
181 FORWARD_ONLY STATIC FOR
182 SELECT *
183 FROM titles
184
185 OPEN @titles_cursor
186 GO
187
188 接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。
189
190 USE pubs
191 GO
192 DECLARE @MyCursor CURSOR
193 EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
194 WHILE (@@FETCH_STATUS = 0)
195 BEGIN
196    FETCH NEXT FROM @MyCursor
197 END
198 CLOSE @MyCursor
199 DEALLOCATE @MyCursor
200 GO
201
202 F. 使用 WITH RECOMPILE 选项
203 如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在内存中，WITH RECOMPILE 子句会很有帮助。
204
205 USE pubs
206 IF EXISTS (SELECT name FROM sysobjects
207       WHERE name = ' titles_by_author ' AND type = ' P ' )
208    DROP PROCEDURE titles_by_author
209 GO
210 CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = ' % '
211 WITH RECOMPILE
212 AS
213 SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS ' Authors full name ' ,
214    title AS Title
215 FROM authors a INNER JOIN titleauthor ta
216    ON a.au_id = ta.au_id INNER JOIN titles t
217    ON ta.title_id = t.title_id
218 WHERE au_lname LIKE @@LNAME_PATTERN
219 GO
220
221 G. 使用 WITH ENCRYPTION 选项
222 WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。
223
224 IF EXISTS (SELECT name FROM sysobjects
225       WHERE name = ' encrypt_this ' AND type = ' P ' )
226    DROP PROCEDURE encrypt_this
227 GO
228 USE pubs
229 GO
230 CREATE PROCEDURE encrypt_this
231 WITH ENCRYPTION
232 AS
233 SELECT *
234 FROM authors
235 GO
236
237 EXEC sp_helptext encrypt_this
238
239 下面是结果集：
240
241 The object ' s comments have been encrypted.
242
243 接下来，选择加密存储过程内容的标识号和文本。
244
245 SELECT c.id, c. text
246 FROM syscomments c INNER JOIN sysobjects o
247     ON c.id = o.id
248 WHERE o.name = ' encrypt_this '
249
250 下面是结果集：
251
252
253
254 说明   text 列的输出显示在单独一行中。执行时，该信息将与 id 列信息出现在同一行中。
255
256
257 id          text
258 -- -------- ------------------------------------------------------------
259 1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????
260
261 ( 1 row(s) affected)
262
263 H. 创建用户定义的系统存储过程
264 下面的示例创建一个过程，显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以 sys 开头的所有表（及索引）。
265
266 IF EXISTS ( SELECT name FROM sysobjects
267        WHERE name = ' sp_showindexes ' AND type = ' P ' )
268     DROP PROCEDURE sp_showindexes
269 GO
270 USE master
271 GO
272 CREATE PROCEDURE sp_showindexes
273     @@TABLE varchar ( 30 ) = ' sys% '
274 AS
275 SELECT o.name AS TABLE_NAME,
276    i.name AS INDEX_NAME,
277    indid AS INDEX_ID
278 FROM sysindexes i INNER JOIN sysobjects o
279     ON o.id = i.id
280 WHERE o.name LIKE @@TABLE
281 GO
282 USE pubs
283 EXEC sp_showindexes ' emp% '
284 GO
285
286 下面是结果集：
287
288 TABLE_NAME       INDEX_NAME       INDEX_ID
289 -- -------------- ---------------- ----------------
290 employee         employee_ind      1
291 employee         PK_emp_id         2
292
293 ( 2 row(s) affected)
294
295 I. 使用延迟名称解析
296 下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在，但每个存储过程都可创建。
297
298 IF EXISTS ( SELECT name FROM sysobjects
299        WHERE name = ' proc1 ' AND type = ' P ' )
300     DROP PROCEDURE proc1
301 GO
302 -- Creating a procedure on a nonexistent table.
303 USE pubs
304 GO
305 CREATE PROCEDURE proc1
306 AS
307     SELECT *
308     FROM does_not_exist
309 GO
310 -- Here is the statement to actually see the text of the procedure.
311 SELECT o.id, c. text
312 FROM sysobjects o INNER JOIN syscomments c
313     ON o.id = c.id
314 WHERE o.type = ' P ' AND o.name = ' proc1 '
315 GO
316 USE master
317 GO
318 IF EXISTS ( SELECT name FROM sysobjects
319        WHERE name = ' proc2 ' AND type = ' P ' )
320     DROP PROCEDURE proc2
321 GO
322 -- Creating a procedure that attempts to retrieve information from a
323 -- nonexistent column in an existing table.
324 USE pubs
325 GO
326 CREATE PROCEDURE proc2
327 AS
328     DECLARE @middle_init char ( 1 )
329     SET @middle_init = NULL
330     SELECT au_id, middle_initial = @middle_init
331     FROM authors
332 GO
333 -- Here is the statement to actually see the text of the procedure.
334 SELECT o.id, c. text
335 FROM sysobjects o INNER JOIN syscomments c
336     ON o.id = c.id
337 WHERE o.type = ' P ' and o.name = ' proc2 '
338
339

转载于:https://www.cnblogs.com/haik/archive/2010/05/10/1732035.html