存储过程续2

上一篇 / 下一篇  2008-04-28 14:27:32 / 个人分类：数据库

查看( 315 ) / 评论( 0 ) / 评分( 0 / 0 )
IF EXISTS (SELECT name FROM sysobjects51Testing软件测试网i7@c+Df%dN b
         WHERE name = 'au_info' AND type = 'P')
EA|Jn-a4p0   DROP PROCEDURE au_info51Testing软件测试网'c/G%R3A3G7B4_
GO51Testing软件测试网8h0u'KnD!m2@ GI
USE pubs51Testing软件测试网[r%Z/~ nMq$a
GO51Testing软件测试网2Z~,xd#{/Y.v
CREATE PROCEDURE au_info51Testing软件测试网l%j.@U5H
   @lastname varchar(40),51Testing软件测试网@2fF0LS1Y@
   @firstname varchar(20)51Testing软件测试网 K$nLl4k0_
AS
g#B"C_RE[?0SELECT au_lname, au_fname, title, pub_name
xr ~.U%b9h#j{(C0   FROM authors a INNER JOIN titleauthor ta51Testing软件测试网LI*^%j'yI2~lw|X@/~
      ON a.au_id = ta.au_id INNER JOIN titles t51Testing软件测试网&olfm\1? XuZ
      ON t.title_id = ta.title_id INNER JOIN publishers p
9?uU6Lv c*`0      ON t.pub_id = p.pub_id
'@%vWsKq0S0   WHERE au_fname = @firstname
7SOi\k9_XbQT+@0      AND au_lname = @lastname
D*X.WP,a/u/X0GO51Testing软件测试网b A1d ifA&DR

,s |w-EAJ0h1@0

$w/Xu Y?0au_info 存储过程可以通过以下方法执行：

b#|pUyk R \8|"D!]051Testing软件测试网5Z+X6aM_Cx B X

EXECUTE au_info 'Dull', 'Ann'51Testing软件测试网+dN&E zf${g2h
-- Or51Testing软件测试网rp&vkDJ]}4|
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
J2l!^c)Q|0-- Or
2k*v,u!J7} l"S5V0EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
\@sPGZh,A2N0-- Or51Testing软件测试网ZuP-T#I fe l
EXEC au_info 'Dull', 'Ann'51Testing软件测试网p@?:k#S^
51Testing软件测试网%od"M"~L2U%`
-- Or
b`P!Bhc+}w1q0EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
|P*E"Yr+U#a0-- Or51Testing软件测试网2g&]4xd:DC.r
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

d tRCj,r:^{0

!v(e*|8~ J9Y'vFe3r0如果该过程是批处理中的第一条语句，则可使用：51Testing软件测试网"mZ6C{s f

6T4H.?hc&G0au_info 'Dull', 'Ann'
7y%s]4vOQ;Ib vo0-- Or
flm O4Cplr^)|0au_info @lastname = 'Dull', @firstname = 'Ann'51Testing软件测试网R"x/c/^1yzuG!D5h2[
-- Or
FgO-SJC5rzz1\6]0au_info @firstname = 'Ann', @lastname = 'Dull'

9k1yU+?,y JF`051Testing软件测试网*hMub.G2Zr

C. 使用带有通配符参数的简单过程51Testing软件测试网[?WF,fc2rg @B7X
下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。

%?#n%yY+x2J~\B051Testing软件测试网.y%xa1N$~

USE pubs51Testing软件测试网[#o^#GtI:gXnOC
IF EXISTS (SELECT name FROM sysobjects
k^V8nY!M0      WHERE name = 'au_info2' AND type = 'P')51Testing软件测试网T'h;JH!` j&ds
   DROP PROCEDURE au_info2
`W;^m#VT!C(m0GO
0_u sE R E2N4Wk0USE pubs
b5BnU] z6JPT0GO
)_6RwV9a7yV0a$LP0CREATE PROCEDURE au_info2
_L,yK*Bo0   @lastname varchar(30) = 'D*',
x!}E kd!BJ0   @firstname varchar(18) = '*'51Testing软件测试网&?*^x9J&b
AS51Testing软件测试网6j+U!FUcV*y{
SELECT au_lname, au_fname, title, pub_name
}K C(_O7[)B I0FROM authors a INNER JOIN titleauthor ta
2ZH p%sI P _ c"MI0   ON a.au_id = ta.au_id INNER JOIN titles t
W1[1C)e9ck:_\q!G$z a y0   ON t.title_id = ta.title_id INNER JOIN publishers p51Testing软件测试网yB x%d3}/F
   ON t.pub_id = p.pub_id51Testing软件测试网W3O2} ^(wF6]]
WHERE au_fname LIKE @firstname
2gOIcC~H0   AND au_lname LIKE @lastname
bLAi)E2_0GO

"Yft;r|c;i)]Z,|%T2V0

Kn KD/J|` W0

?YV1`I3N0

~M~q!m0au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：51Testing软件测试网"j!CMA1Bd#OUG1t

51Testing软件测试网wN A"T*ct3Wf

EXECUTE au_info251Testing软件测试网8o.@3Gvj
-- Or51Testing软件测试网v;D%E,b9g(K
EXECUTE au_info2 'Wh*'
dc,M^8i/b[0-- Or
^n~-]5sN0EXECUTE au_info2 @firstname = 'A*'51Testing软件测试网j1HT ^J(z E1q
-- Or
9`"T;D!{ @z?0EXECUTE au_info2 '[CK]ars[OE]n'
;fjG ME$j WI.i0-- Or
eW$a(P0o5o(]0EXECUTE au_info2 'Hunter', 'Sheryl'
O-F\'|"v&Fqy!o r0-- Or51Testing软件测试网$Q+aO3G*c J
EXECUTE au_info2 'H*', 'S*'

$T]7T;k6xg8d051Testing软件测试网$w [0Fy.x+n(`

D. 使用 OUTPUT 参数
l*X%CYl0OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)，并使用一个可选的输入参数和一个输出参数。51Testing软件测试网QFK%Qrpta

| o-_/O[G@0首先，创建过程：

,S)e xnOB x}0

fD3p J^)r0USE pubs
'uceB!_h I(F;ja0GO
wD6@w;Z'a0IF EXISTS(SELECT name FROM sysobjects51Testing软件测试网4Q2m1^:pS
      WHERE name = 'titles_sum' AND type = 'P')51Testing软件测试网S7R7J?3i'Km
   DROP PROCEDURE titles_sum51Testing软件测试网2\].L m*R$\8y"L'I
GO51Testing软件测试网uw"Y)r*i@fIj
USE pubs51Testing软件测试网.MR fJJ |p
GO51Testing软件测试网 _O n@/s _'KY0gl
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '*', @@SUM money OUTPUT
#x7zc+P0t4e6] n|0AS51Testing软件测试网qZPn D6ws
SELECT 'Title Name' = title
GWM*X^Fn$q0FROM titles51Testing软件测试网9_EazU2O9F
WHERE title LIKE @@TITLE
X8]/]#^2`o!lT&Rk2C0SELECT @@SUM = SUM(price)
K?0rP3Mtpb T0|0FROM titles51Testing软件测试网4w!Q _3ZG:w
WHERE title LIKE @@TITLE
t6j/oJ!y&spN0GO

'@:P,\B4_-w^$p0

"g,_z[5V0接下来，将该 OUTPUT 参数用于控制流语言。51Testing软件测试网f {| WF8HPED

51Testing软件测试网-z ~"Qz+utn3}

51Testing软件测试网 F7oZ}x7e!^.J
说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。51Testing软件测试网d*\ b n"O8t(w3K

51Testing软件测试网7t _*GB6?\ ]"_


ktIaW0参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 @@SUM = variable 形式）。

YY"f'G\5Ct0

'mH'O%g5u,EH0DECLARE @@TOTALCOST money
pxd|*W"L b0EXECUTE titles_sum 'The*', @@TOTALCOST OUTPUT
vr cdm#@I3K0IF @@TOTALCOST < 20051Testing软件测试网nD+R&uO/y/Yin5x
BEGIN
U9n)P)rI)J@VCw0   PRINT ' '51Testing软件测试网 M$Ms"tHwEYn t
   PRINT 'All of these titles can be purchased for less than $200.'51Testing软件测试网D]9M@,B4sS6K fm5k
END51Testing软件测试网]I)p7Bc{
ELSE
iaG0h+RCB0   SELECT 'The total cost of these titles is $'
L ?E;\;o0         + RTRIM(CAST(@@TOTALCOST AS varchar(20)))

L!F;J,uUD-E051Testing软件测试网#pY j/m,e&]$R

下面是结果集：51Testing软件测试网)s6q;v!rs Pu

,Y#Xk AM]n)KnSw0Title Name   51Testing软件测试网([y"n"Gx,^;v

51Testing软件测试网/j$ZaV_$y+^

The Gourmet Microwave
6Lz.P+R6wT2N4gr#f0The Psychology of Computer Cooking

(_ak#u;V-k'pCt0

/T#?Y Ci051Testing软件测试网"Ddxqe F"WM

51Testing软件测试网n1F;A-rP;i3D

(3 row(s) affected)

iuLl5eS:zI&|051Testing软件测试网cV:j!l*Z F

Warning, null value eliminated from aggregate.

,Eojr8g7oJ?051Testing软件测试网p \Xc0a)Y

All of these titles can be purchased for less than $200.

)H,`}b2E7}(r(T051Testing软件测试网V kA]4ga

E. 使用 OUTPUT 游标参数51Testing软件测试网$j:}R"f-b {*C
OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。51Testing软件测试网jR!{_~5_|9MA

'Pg-Z/JGrK0首先，创建以下过程，在 titles 表上声明并打开一个游标：51Testing软件测试网nRa6UvUOV

7KZG.H.p0USE pubs51Testing软件测试网'e!U*?v0hK
IF EXISTS (SELECT name FROM sysobjects
6Sj/EZh(n)g#T0      WHERE name = 'titles_cursor' and type = 'P')
p'\[2ljRWj0DROP PROCEDURE titles_cursor
6Z@?1c`6P0GO51Testing软件测试网#@K(t8m(_s7` t Zu)q0H
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
ZSx |p2Y0AS
'?#f0MNa)CPD0SET @titles_cursor = CURSOR51Testing软件测试网 e mbH{l
FORWARD_ONLY STATIC FOR
LLE0}A0SELECT *51Testing软件测试网4m b1RPP~2~1`5d1GU
FROM titles51Testing软件测试网;eO!g5v jC

51Testing软件测试网,d*us(sN)hN-vh6mV

OPEN @titles_cursor
}]qmZ1`h:N_0GO51Testing软件测试网-?6gNF]

51Testing软件测试网| G0VnDeW ]

接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。

|p3|.l ]GUB051Testing软件测试网naA5^2Ahtk:S$z

USE pubs51Testing软件测试网"K#C6{Le {x1`
GO
3c:_?)k y0DECLARE @MyCursor CURSOR
L'k Np0x0j esL0EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT51Testing软件测试网kM5Si'L
WHILE (@@FETCH_STATUS = 0)51Testing软件测试网7d-B0O;q:E
BEGIN51Testing软件测试网v| {&DXaPx
   FETCH NEXT FROM @MyCursor
*z]X)Wm!p;P0END51Testing软件测试网$[FE&W0I/^,II
CLOSE @MyCursor51Testing软件测试网7Xc8sW N%M!W
DEALLOCATE @MyCursor
E2HSN;il~L yC d0GO

&O Z s%{!pH0

J S9b+_/{NF;X0F. 使用 WITH RECOMPILE 选项51Testing软件测试网0zsjw;C$|?
如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在内存中，WITH RECOMPILE 子句会很有帮助。

I \mY sZ5n;_0

D%|Ew f,U0USE pubs51Testing软件测试网C(H|Rb]-rU%f,b
IF EXISTS (SELECT name FROM sysobjects51Testing软件测试网lZ B#]p,|D`[@P
      WHERE name = 'titles_by_author' AND type = 'P')51Testing软件测试网 iTPO7L1K)Wd7D R
   DROP PROCEDURE titles_by_author51Testing软件测试网9y'@GeGJ6ON.@
GO51Testing软件测试网T0^xL'STB3N8OUM
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '*'51Testing软件测试网SEI Fn8B
WITH RECOMPILE51Testing软件测试网[T1d]:Tq)EN
AS51Testing软件测试网Oy2NYNN
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',51Testing软件测试网| h\N?m
   title AS Title51Testing软件测试网uW"B%`Ok@
FROM authors a INNER JOIN titleauthor ta51Testing软件测试网;|*L8t3nFLn8\a
   ON a.au_id = ta.au_id INNER JOIN titles t51Testing软件测试网?0MPIR,{C
   ON ta.title_id = t.title_id51Testing软件测试网E X'U4qtq8R2o(o"|
WHERE au_lname LIKE @@LNAME_PATTERN51Testing软件测试网ax/f Mv9MTvt
GO51Testing软件测试网\](a2Y[$x!R9n.g

#V3w6VM3?1@:ag&^0G. 使用 WITH ENCRYPTION 选项
8a6H;V#xS3i%h"P0WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。51Testing软件测试网&y@ |6Y up.Qg'e

51Testing软件测试网6bEKJA'pY$z

IF EXISTS (SELECT name FROM sysobjects51Testing软件测试网7K\1zp3D-r2R^q
      WHERE name = 'encrypt_this' AND type = 'P')
rj e;l4^.[t0   DROP PROCEDURE encrypt_this51Testing软件测试网@'X ja,J/tFOe!P^:E


收藏 举报

TAG: 数据库

 

评分：0

我来说两句

Open Toolbar