存储过程续３

GO
L&G ]"w\r;^ Xx5fj0USE pubs51Testing软件测试网1Z,@Oe$b9T
GO51Testing软件测试网Ui2E3Yc?,a
CREATE PROCEDURE encrypt_this
XS9N"]0WM7G0WITH ENCRYPTION
x*p2y.~WY8d'H0AS
e(Bh;Q|4L0SELECT *51Testing软件测试网p'C$A!vhq
FROM authors
,fl#j-C+hdwI0GO51Testing软件测试网'DQ2y@,uqN

!l5ZZ5Y*`8Z/EP051Testing软件测试网IHJ9~1Wl P,N _!m

EXEC sp_helptext encrypt_this

[|(Iig/Z(D{k EsT051Testing软件测试网
a m.D#Vl V6k;j?1G

下面是结果集：

;m5ex [3@|051Testing软件测试网-\+t
KgNP?~P

The object's comments have been encrypted.51Testing软件测试网_(d1X~#P#]N[

51Testing软件测试网?7Q#Na g4CKL

接下来，选择加密存储过程内容的标识号和文本。51Testing软件测试网\G4ft z#O4I{ H

51Testing软件测试网;mJnJ.cy5U1}Y

SELECT c.id, c.text51Testing软件测试网%g7tU2ed+N
FROM syscomments c INNER JOIN sysobjects o
~AP%\ W-fb0G[0   ON c.id = o.id51Testing软件测试网b
}.]MP
WHERE o.name = 'encrypt_this'

i Y+ES"v9k,E R+s0

aC:wQ"Tw-H,[+I0下面是结果集：51Testing软件测试网6x2vzqiZ7k2E

e:@2X
QD!j O051Testing软件测试网tm
rH$Pc
说明 text 列的输出显示在单独一行中。执行时，该信息将与 id 列信息出现在同一行中。51Testing软件测试网7Tz!n&_/QK

51Testing软件测试网V N8sc4~*U

:Z#K'qo2w)P0id         text                                                       51Testing软件测试网4k.@!X:BS&q
---------- ------------------------------------------------------------
wO,r3y-oWLsS(V01413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????51Testing软件测试网!Y*p i:s,q1u|t

51Testing软件测试网 }A7n{o'^['k cx

(1 row(s) affected)

!\E2sm|](I"H0

d"WJ9O%AC N0NJ0H. 创建用户定义的系统存储过程
1[2mC(JIO }.~!a0

{^V|(_6Ns-YBS0下面的示例创建一个过程，显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以 sys 开头的所有表（及索引）。

7o!WVyt'A,v/E051Testing软件测试网1W_L:Y3{:O

IF EXISTS (SELECT name FROM sysobjects51Testing软件测试网:m8KZN!p7HJk!\
      WHERE name = 'sp_showindexes' AND type = 'P')51Testing软件测试网_;jOWO
   DROP PROCEDURE sp_showindexes51Testing软件测试网 ^b |1H!d;s sbJ
GO
m4|&d9Vx,R:s0USE master

yK w N R#r U0W0GO
4\6QQnv(o*uP0CREATE PROCEDURE sp_showindexes51Testing软件测试网:Ga\$Zv6N!rMdV"x*q
   @@TABLE varchar(30) = 'sys*'51Testing软件测试网U QIPJ^
AS51Testing软件测试网$s}7C!@z?)g?
SELECT o.name AS TABLE_NAME,
i|~6IF5O!tO0   i.name AS INDEX_NAME,
_K!J3|bk,{8O!e k0   indid AS INDEX_ID
_.HL M8]0lC9q0FROM sysindexes i INNER JOIN sysobjects o51Testing软件测试网 DR;Dx)t
   ON o.id = i.id51Testing软件测试网LjC,?o`
WHERE o.name LIKE @@TABLE51Testing软件测试网$U ouY&@y4{dM
GO        51Testing软件测试网8Pu6BR;w&Ic~*|*A
USE pubs
TC+_V
O\3oI0EXEC sp_showindexes 'emp*'
{(ez$A4o R0GO51Testing软件测试网~A,A&~3Js|q

51Testing软件测试网0x(b-Dv,en6^ j

下面是结果集：51Testing软件测试网&i%l~h,v

8Uh(| u]M v0TABLE_NAME       INDEX_NAME       INDEX_ID
O?2Z2E1s(c*bO5P2O f0---------------- ---------------- ----------------51Testing软件测试网+hb7F8C`

51Testing软件测试网Y&iY+}:eR0p3r^

employee         employee_ind     151Testing软件测试网^!pT C%I
employee         PK_emp_id        251Testing软件测试网7VZ5JQ Q*BZ!`

u] [\5T;[}0

i9M:|bQ:rV0

j#nI|IC/sV0(2 row(s) affected)

e8|(]P7O0

#Tf&|a2@-C0I. 使用延迟名称解析51Testing软件测试网7l1d&P%w&Q2ubK)_x"UOz
下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在，但每个存储过程都可创建。

U%Vy-q P ] i/y|051Testing软件测试网/s3cDA/h7D)J

IF EXISTS (SELECT name FROM sysobjects51Testing软件测试网!p0PQK*qHPqq
      WHERE name = 'proc1' AND type = 'P')
$Qy;pH'N0M0@0   DROP PROCEDURE proc151Testing软件测试网-B+Hghr5h"]4x*z
GO51Testing软件测试网bSa.R.BR
-- Creating a procedure on a nonexistent table.
1@`/z8{I W0C0USE pubs
GR1TEFlE/V#H0GO
3_:_*WTIh#ME q0CREATE PROCEDURE proc1
t+U#n8gz;Z0AS
(z.}8xC8i5M&a0   SELECT *51Testing软件测试网8NhVLL(LT
   FROM does_not_exist51Testing软件测试网D,xLah$@D
GO51Testing软件测试网2` Nj-Z
}z
-- Here is the statement to actually see the text of the procedure.51Testing软件测试网K9_yM c,d2D j{
SELECT o.id, c.text
El,e7mw*tI*k0FROM sysobjects o INNER JOIN syscomments c
%k$CcbF0z0   ON o.id = c.id51Testing软件测试网6@CY|+EK
WHERE o.type = 'P' AND o.name = 'proc1'
s!K!@i8Cu*uG0GO
!J-jIIN#{0USE master
lnPM u8O;\#R:zTRC3w0GO
AW)mJfa3v.e0IF EXISTS (SELECT name FROM sysobjects51Testing软件测试网}$N N |t0P
      WHERE name = 'proc2' AND type = 'P')
8p3`?f.^0   DROP PROCEDURE proc251Testing软件测试网W0|7^[O7O/d-Mf P i
GO
#[6_*V"P{"Xt3T0-- Creating a procedure that attempts to retrieve information from a
7d]5snt8V
x0-- nonexistent column in an existing table.
\ b6[#H/{0?0USE pubs51Testing软件测试网z\"}*v+Za6?f
GO
0wc1bU5pl0DzO G0CREATE PROCEDURE proc251Testing软件测试网KP8RL,RG2i!o ~
AS
&nx2{2|[J9W0   DECLARE @middle_init char(1)51Testing软件测试网1Hq3L N}"B]z |
   SET @middle_init = NULL51Testing软件测试网I3z3J~!r(Jy;g(\6m
   SELECT au_id, middle_initial = @middle_init
$j*b|'d*{z)l ]6L0   FROM authors51Testing软件测试网#o+A(].[W8G)G:O.|0]
GO
%n ]Q[K^0-- Here is the statement to actually see the text of the procedure.

Zh'`)J4UT"bK0SELECT o.id, c.text51Testing软件测试网? O?m9a}C KZ-wuj
FROM sysobjects o INNER JOIN syscomments c51Testing软件测试网0ZGFN ^u~
   ON o.id = c.id51Testing软件测试网I-J4YJ(g-JS#G]9U4P
WHERE o.type = 'P' and o.name = 'proc2'
"VzG u)F-fTq!?0获得有关存储过程的信息
l? dU8bs^0rY0若要显示用来创建过程的文本，请在过程所在的数据库中执行 sp_helptext，并使用过程名作为参数。51Testing软件测试网G)jU W9G)M%x!t

51Testing软件测试网;Nw:g:\P7{

说明 使用 ENCRYPTION 选项创建的存储过程不能使用 sp_helptext 查看。
wmG$p!f/a\bN0若要显示有关过程引用的对象的报表，请使用 sp_depends。51Testing软件测试网"Pk{h9k7poB.a
若要为过程重命名，请使用 sp_rename。51Testing软件测试网{2nK,K)F;c'u
执行存储过程51Testing软件测试网)Z"E v"c)hUp&Q7D(RZ

51Testing软件测试网?4j1kZ1[ Gb@U*v

成功执行 CREATE PROCEDURE 语句后，过程名称将存储在 sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本将存储在 syscomments 中。第一次执行时，将编译该过程以确定检索数据的最佳访问计划。51Testing软件测试网.weO!o[5rR0i*S