sql存储过程完全教程
目录
1.sql存储过程概述
2.SQL存储过程创建E2C~,x0@iC|4W7c*H
3.sql存储过程及应用)z,e1A{3o
4.各种存储过程使用指南A~k?/L cS`MdRE_
5.ASP中存储过程调用的两种方式及比较,Q0_'{ a&b&J M a}
6.SQL存储过程在.NET数据库中的应用
7.使用SQL存储过程要特别注意的问题
2006-8-5 19:54 天涯风云
1.sql存储过程概述
在大型数据库系统中存储过程和触发器具有很重要的作用。无论是存储过程还是触发器都是SQL 语句和流程控制语句的集合。就本质而言触发器也是一种存储过程。存储过程在运算时生成执行方式所以以后对其再运行时其执行速度很快。SQL Server 2000 不仅提供了用户自定义存储过程的功能而且也提供了许多可作为工具使用的系统存储过程。5li1`8F2i8QD
存储过程的概念
2]{F ]9\l/d
存储过程Stored Procedure是一组为了完成特定功能的SQL 语句集经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行它。1cz$PxXC5g;I
yyRyrf
在SQL Server 的系列版本中存储过程分为两类系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀并且系统存储过程主要是从系统表中获取信息从而为系统管理员管理SQL Server 提供支持。通过系统存储过程MS SQL Server 中的许多管理性或信息性的活动如了解数据库对象、数据库信息都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中但是仍可以在其它数据库中对其进行调用在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能如查询用户所需数据信息的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。lO3h4h0WQ6P
tv+xR*P#[n9b
存储过程的优点QsYcs @
当利用MS SQL Server 创建一个应用程序时Transaction-SQL 是一种主要的编程语言。若运用Transaction-SQL 来进行编程有两种方法。其一是在本地存储Transaction- SQL 程序并创建应用程序向SQL Server 发送命令来对结果进行处理。其二是可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中并创建应用程序来调用存储过程对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集结果集的格式由调用者确定返回状态值给调用者指明调用是成功或是失败包括针对数据库的操作语句并且可以在一个存储过程中调用另一存储过程。
Pjs8k8yZKP
我们通常更偏爱于使用第二种方法即在SQL Server 中使用存储过程而不是在客户计算机上调用Transaction-SQL 编写的一段程序原因在于存储过程具有以下优点3o0z5UO u3o#\D/\*i
6K \R1i(j6S r
1 存储过程允许标准组件式编程u*pE8RsV5mI
spzPj6N[K&]
存储过程在被创建以后可以在程序中被多次调用而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改但对应用程序源代码毫无影响因为应用程序源代码只包含存储过程的调用语句从而极大地提高了程序的可移植性。9M7|+KS;`TM
YF\8`0p EHTa-gT9G6a
2 存储过程能够实现较快的执行速度!k2T B"{Y ?
|0F%j)S-A Tw(M
如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的在首次运行一个存储过程时查询优化器对其进行分析、优化并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化因此速度相对要慢一些。3kU.Y&h%Wk$X
l`%C3{UO6n,`
3 存储过程能够减少网络流量)@(ZVg^(bXDSM
对于同一个针对数据数据库对象的操作如查询、修改如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程那么当在客户计算机上调用该存储过程时网络中传送的只是该调用语句否则将是多条SQL 语句从而大大增加了网络流量降低网络负载。.B5g/M.Z!b8\1qv!{3R
4 存储过程可被作为一种安全机制来充分利用va.n(|p{y/F
系统管理员通过对执行某一存储过程的权限进行限制从而能够实现对相应的数据访问权限的限制避免非授权用户对数据的访问保证数据的安全。我们将在14 章“SQLServer 的用户和安全性管理”中对存储过程的这一应用作更为清晰的介绍
注意存储过程虽然既有参数又有返回值但是它与函数不同。存储过程的返回值只是指明执行是否成功并且它不能像函数那样被直接调用也就是在调用存储过程时在存储过程名字前一定要有EXEC保留字。
2006-8-5 19:54 天涯风云
2.SQL存储过程创建
创建存储过程存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。
可以创建一个过程供永久使用或在一个会话中临时使用局部临时过程或在所有会话中临时使用全局临时过程。
Z"SDbC;THr-n GV
也可以创建在 Microsoft? SQL Server? 启动时自动运行的存储过程。
m4Y1x+VbAB"z
语法!F+}-CjGe Y)r^$q8y
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
%h"ueu R~W
[ WITH'i:uA&MX \7LI1A
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] "?Ib+QS.B
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
2006-8-5 19:55 天涯风云
参数
procedure_name!JWN(?0x
新存储过程的名称。过程名必须符合标识符规则且对于数据库及其所有者必须唯一。-~D8q-~'g/FRy
要创建局部临时过程可以在 procedure_name 前面加一个编号符 (#procedure_name)要创建全局临时过程可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称包括 # 或 ##不能超过 128 个字符。指定过程所有者的名称是可选的。W"^2igZ~"sE^
Z m{\"cA)QW:Js
;number
是可选的整数用来对同名的过程分组以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符则数字不应包含在标识符中只应在 procedure_name 前后使用适当的定界符。2D|r8}Z [[q
@parameter)EA'X|J
过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值除非定义了该参数的默认值。存储过程最多可以有 2.100 个参数。B.tZ"O;b}x!`f'Dd
n1xg5xK9z!cC:X
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身相同的参数名称可以用在其它过程中。默认情况下参数只能代替常量而不能用于代替表名、列名或其它数据库对象的名称。
data_type
i+sbm'Tz\| N5@)P
参数的数据类型。所有数据类型包括 text、ntext 和 p_w_picpath均可以用作存储过程的参数。不过cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor也必须同时指定 VARYING 和 OUTPUT 关键字。
说明 对于可以是 cursor 数据类型的输出参数没有最大数目的限制。
"tTqqH8mN:yK.P i
VARYING
指定作为输出参数支持的结果集由存储过程动态构造内容可以变化。仅适用于游标参数。
default
参数的默认值。如果定义了默认值不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字那么默认值中可以包含通配符%、_、[] 和 [^]。0M`x x[y5c+R
;T+m+ls"d-pc TH
OUTPUT$ht qK4yL
表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 p_w_picpath 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。Q*h E:}3nn+S
cDM&rP]S
n
表示最多可以指定 2.100 个参数的占位符。'Dc#@7d.za+K
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}1^a1\lETq7Y
RECOMPILE 表明 SQL Server 不会缓存该过程的计划该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时请使用 RECOMPILE 选项。
j yu'r6d-WAI:Gm
ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。X^$q8AL
P#s k&D1lR
说明 在升级过程中SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。 L njZ%u f
xW6D$Yz {KZJ
(F0v{I7H D!D7ObP
FOR REPLICATION5oau'PbV%U
.znT2j"D1e&R
指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。:cM|8x1@9Hss9bq
AS~&LU @$Y!{jhY
En B$h:N(q*e/fQ2c
指定过程要执行的操作。
(rp|{%f!vd$?H3n
sql_statementAW$S8ZR0W1s/~
CS5h-HOEI'a
过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。
n
cIA1F0q'x
是表示此过程可以包含多条 Transact-SQL 语句的占位符。
8g @n,DW'A
注释_]aGs,L
存储过程的最大大小为 128 MB。
2006-8-5 19:55 天涯风云
用户定义的存储过程只能在当前数据库中创建临时过程除外临时过程总是在 tempdb 中创建。在单个批处理中CREATE PROCEDURE 语句不能与其它 Transact-SQL 语句组合使用。 *Z.wlj&?
R z1H!d`
默认情况下参数可为空。如果传递 NULL 参数值并且该参数在 CREATE 或 ALTER TABLE 语句中使用而该语句中引用的列又不允许使用 NULL则 SQL Server 会产生一条错误信息。为了防止向不允许使用 NULL 的列传递 NULL 参数值应向过程中添加编程逻辑或为该列使用默认值使用 CREATE 或 ALTER TABLE 的 DEFAULT 关键字)。
建议在存储过程的任何 CREATE TABLE 或 ALTER TABLE 语句中都为每列显式指定 NULL 或 NOT NULL例如在创建临时表时。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项控制 SQL Server 为列指派 NULL 或 NOT NULL 特性的方式如果在 CREATE TABLE 或 ALTER TABLE 语句中没有指定的话。如果某个连接执行的存储过程对这些选项的设置与创建该过程的连接的设置不同则为第二个连接创建的表列可能会有不同的为空性并且表现出不同的行为方式。如果为每个列显式声明了 NULL 或 NOT NULL那么将对所有执行该存储过程的连接使用相同的为空性创建临时表。
在创建或更改存储过程时SQL Server 将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。执行存储过程时将使用这些原始设置。因此所有客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行存储过程时都将被忽略。在存储过程中出现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储过程的功能。
}$R0_ YG a2PG
其它 SET 选项例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS在创建或更改存储过程时不保存。如果存储过程的逻辑取决于特定的设置应在过程开头添加一条 SET 语句以确保设置正确。从存储过程中执行 SET 语句时该设置只在存储过程完成之前有效。之后设置将恢复为调用存储过程时的值。这使个别的客户端可以设置所需的选项而不会影响存储过程的逻辑。~Ky6Nz p Io N E)u
5O3\ \k-t's
天涯风云 19:30:43
说明 SQL Server 是将空字符串解释为单个空格还是解释为真正的空字符串由兼容级别设置控制。如果兼容级别小于或等于 65SQL Server 就将空字符串解释为单个空格。如果兼容级别等于 70则 SQL Server 将空字符串解释为空字符串。,vR6eb F0N
获得有关存储过程的信息
若要显示用来创建过程的文本请在过程所在的数据库中执行 sp_helptext并使用过程名作为参数。
+DTDMt:\c n
;ov"}x@Nj
说明 使用 ENCRYPTION 选项创建的存储过程不能使用 sp_helptext 查看。w"yT0nC x)@$@
若要显示有关过程引用的对象的报表请使用 sp_depends。
若要为过程重命名请使用 sp_rename。
2006-8-5 19:55 天涯风云
引用对象W$v$u;H,\%l*j!U
SQL Server 允许创建的存储过程引用尚不存在的对象。在创建时只进行语法检查。执行时如果高速缓存中尚无有效的计划则编译存储过程以生成执行计划。只有在编译过程中才解析存储过程中引用的所有对象。因此如果语法正确的存储过程引用了不存在的对象则仍可以成功创建但在运行时将失败因为所引用的对象不存在。0[5] t y%S v@J
延迟名称解析和兼容级别V[4[#`%J L[M5v
SQL Server 允许 Transact-SQL 存储过程在创建时引用不存在的表。这种能力称为延迟名称解析。不过如果 Transact-SQL 存储过程引用了该存储过程中定义的表而兼容级别设置通过执行 sp_dbcmptlevel 来设置为 65则在创建时会发出警告信息。而如果在运行时所引用的表不存在将返回错误信息。9sOy]T _#b q)]
执行存储过程
成功执行 CREATE PROCEDURE 语句后过程名称将存储在 sysobjects 系统表中而 CREATE PROCEDURE 语句的文本将存储在 syscomments 中。第一次执行时将编译该过程以确定检索数据的最佳访问计划。
SP)t LEQ?
使用 cursor 数据类型的参数0Nj2R3V~2IRl
存储过程只能将 cursor 数据类型用于 OUTPUT 参数。如果为某个参数指定了 cursor 数据类型也必须指定 VARYING 和 OUTPUT 参数。如果为某个参数指定了 VARYING 关键字则数据类型必须是 cursor并且必须指定 OUTPUT 关键字。
说明 cursor 数据类型不能通过数据库 API例如 OLE DB、ODBC、ADO 和 DB-Library绑定到应用程序变量上。因为必须先绑定 OUTPUT 参数应用程序才可以执行存储过程所以带有 cursor OUTPUT 参数的存储过程不能通过数据库 API 调用。只有将 cursor OUTPUT 变量赋值给 Transact-SQL 局部 cursor 变量时才可以通过 Transact-SQL 批处理、存储过程或触发器调用这些过程。 V-H!djK ^7|
c0h!l O G:~:BUD
Cursor 输出参数
在执行过程时以下规则适用于 cursor 输出参数
对于只进游标游标的结果集中返回的行只是那些存储过程执行结束时处于或超出游标位置的行例如 6k7D$I"B@
在过程中的名为 RS 的 100 行结果集上打开一个非滚动游标。 "cY$~ Zni
过程提取结果集 RS 的头 5 行。9u C;U| r9w"?9R4w
rTkB$L#et
+qqsIB?}
过程返回到其调用者。9@E-m)T&Ru E&OCM
返回到调用者的结果集 RS 由 RS 的第 6 到 100 行组成调用者中的游标处于 RS 的第一行之前。
对于只进游标如果存储过程完成后游标位于第一行的前面则整个结果集将返回给调用批处理、存储过程或触发器。返回时游标将位于第一行的前面。k:x W P{@6f.ao
对于只进游标如果存储过程完成后游标的位置超出最后一行的结尾则为调用批处理、存储过程或触发器返回空结果集。 ^8VL8h8NlU
{ N6I2l;_Xz;Q#U1p
说明 空结果集与空值不同。za9y:W$E
对于可滚动游标在存储过程执行结束时结果集中的所有行均会返回给调用批处理、存储过程或触发器。返回时游标保留在过程中最后一次执行提取时的位置。
o*fM9O:l
对于任意类型的游标如果游标关闭则将空值传递回调用批处理、存储过程或触发器。如果将游标指派给一个参数但该游标从未打开过也会出现这种情况。 6V} o4r6Q
]u {c&gsP#LQ
说明 关闭状态只有在返回时才有影响。例如可以在过程中关闭游标稍后再打开游标然后将该游标的结果集返回给调用批处理、存储过程或触发器。n,O'Ma2Al E%qv
临时存储过程l7|jr@
SQL Server 支持两种临时过程局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。
b!v$_N7\h,VC
临时过程用 # 和 ## 命名可以由任何用户创建。创建过程后局部过程的所有者是唯一可以使用该过程的用户。执行局部临时过程的权限不能授予其他用户。如果创建了全局临时过程则所有用户均可以访问该过程权限不能显式废除。只有在 tempdb 数据库中具有显式 CREATE PROCEDURE 权限的用户才可以在该数据库中显式创建临时过程不使用编号符命名。可以授予或废除这些过程中的权限。 z{|k-BAJ6H
'Y-GB*{n;a'? c
说明 频繁使用临时存储过程会在 tempdb 中的系统表上产生争用从而对性能产生负面影响。建议使用 sp_executesql 代替。sp_executesql 不在系统表中存储数据因此可以避免这一问题。
2006-8-5 19:56 天涯风云
自动执行存储过程
SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建并在 sysadmin 固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。 ,r_/T&p`-?w
&T*H^7[H
对启动过程的数目没有限制但是要注意每个启动过程在执行时都会占用一个连接。如果必须在启动时执行多个过程但不需要并行执行则可以指定一个过程作为启动过程让该过程调用其它过程。这样就只占用一个连接。
s+PgfKCbP(@
在启动时恢复了最后一个数据库后即开始执行存储过程。若要跳过这些存储过程的执行请将启动参数指定为跟踪标记 4022。如果以最低配置启动 SQL Server使用 -f 标记则启动存储过程也不会执行。v J8tH+Ur/O:O
若要创建启动存储过程必须作为 sysadmin 固定服务器角色的成员登录并在 master 数据库中创建存储过程。
使用 sp_procoption 可以
"[C6XZ,H#~~u$M
将现有存储过程指定为启动过程。
&A)C8VPJL
停止在 SQL Server 启动时执行过程。TP|.F:^!T0U}
-YIf9ni
$F QCxpz
查看 SQL Server 启动时执行的所有过程的列表。
存储过程嵌套
存储过程可以嵌套即一个存储过程可以调用另一个存储过程。在被调用过程开始执行时嵌套级将增加在被调用过程执行结束后嵌套级将减少。如果超出最大的嵌套级会使整个调用过程链失败。可用 @@NESTLEVEL 函数返回当前的嵌套级。
3N]MT0`r7l"{
若要估计编译后的存储过程大小请使用下列性能监视计数器。 gl1m.Ub#L7d$N
[X%[?IrRV
GL0zr%i a
* 各种分类的高速缓存对象均可以使用这些计数器包括特殊 sql、准备 sql、过程、触发器等。
sql_statement 限制u&hob4E
除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外这两个语句必须是批处理中仅有的语句任何 SET 语句均可以在存储过程内部指定。所选择的 SET 选项在存储过程执行过程中有效之后恢复为原来的设置。 1[qyOso7g
如果其他用户要使用某个存储过程那么在该存储过程内部一些语句使用的对象名必须使用对象所有者的名称限定。这些语句包括
k:{z?;f:_jtb
ALTER TABLE
0Zzg _%bE"C
CREATE INDEX
CREATE TABLE
*Nij@%_$~JU
所有 DBCC 语句]?5R{H o&@2CI
DROP TABLEXr A I(ZL%R;m
%LL:ronxM/j
DROP INDEX
Cg)m}}%E9el
TRUNCATE TABLEs,p){9SHJ7u4\
,NX+y%p"V1l
UPDATE STATISTICS r&m,|'h [
权限
CREATE PROCEDURE 的权限默认授予 sysadmin 固定服务器角色成员和 db_owner 和 db_ddladmin 固定数据库角色成员。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员可以将 CREATE PROCEDURE 权限转让给其他用户。执行存储过程的权限授予过程的所有者该所有者可以为其它数据库用户设置执行权限。
2006-8-5 19:57 天涯风云
示例.PWN W M2t;Cr
A. 使用带有复杂 SELECT 语句的简单过程
下面的存储过程从四个表的联接中返回所有作者提供了姓名、出版的书籍以及出版社。该存储过程不使用任何参数。 B m ]},w'?7T
USE pubs
IF EXISTS (SELECT name FROM sysobjects [] H#XF#bW/Cln
WHERE name = \'au_info_all\' AND type = \'P\')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS&r;jbRBx u}9cD
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'Y,a4Yq5HK
GO#}}n V(r#LS
*U1|na/Vo H\
au_info_all 存储过程可以通过以下方法执行
EXECUTE au_info_all fikmN | a
-- Or@Gd|G7Zx
EXEC au_info_all(t-tL eN7T5?*{I
2e3e|\:e
如果该过程是批处理中的第一条语句则可使用:Ml.~ n5fU
au_info_alljB E4z2HW ~ av2W7mXkm
天涯风云 19:31:29J2NN2B;TL3Y
B. 使用带有参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者提供了姓名、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。eV0S\O;Q ar%]c
USE pubs8@4X"A2ldWvA H4guZ
IF EXISTS (SELECT name FROM sysobjects A y[:nXHC
WHERE name = \'au_info\' AND type = \'P\')q6Z"v0C\ Jt
DROP PROCEDURE au_info"Q0rV:U)V\
GO
USE pubs
GO
CREATE PROCEDURE au_info ,Z-TKcw&b0@ O
@lastname varchar(40), :{9e7^M*RPa
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta-S3\0R&f^zT$dOe
ON a.au_id = ta.au_id INNER JOIN titles t"{ syaizU
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
mzu a kN{6[\{&`
au_info 存储过程可以通过以下方法执行
G8?N3N#J7b1YT+|%b
EXECUTE au_info \'Dull\', \'Ann\' z?&L3L m t6wz:F
-- Ora5q y.w;C8H M9x
EXECUTE au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or
EXECUTE au_info @firstname = \'Ann\', @lastname = \'Dull\'
-- OrZy'@7C4{"u.j
EXEC au_info \'Dull\', \'Ann\'L1|*t @nF ID
-- Or
EXEC au_info @lastname = \'Dull\', @firstname = \'Ann\'~;ERQ:R5X4p
-- Or|5H0]eO*Fo)r4FMO
EXEC au_info @firstname = \'Ann\', @lastname = \'Dull\'
:p q;d8q0L(n
如果该过程是批处理中的第一条语句则可使用'XF m%e\K+I%a
au_info \'Dull\', \'Ann\'
-- Or3d)[N/`'w z*\ZF1o,K0Y
au_info @lastname = \'Dull\', @firstname = \'Ann\'
-- Or,NrpK0} l
au_info @firstname = \'Ann\', @lastname = \'Dull\'U$FtC V&v n,Uw$`
天涯风云 19:31:41
C. 使用带有通配符参数的简单过程
下面的存储过程从四个表的联接中只返回指定的作者提供了姓名、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配如果没有提供参数则使用预设的默认值。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'au_info2\' AND type = \'P\')O1zl#N^l
DROP PROCEDURE au_info2JLo{*|J g m
GO
USE pubs(t-gLcp6n
GOboTZ3nI7T
CREATE PROCEDURE au_info2| c_^N#zwx#LR)PH)j
@lastname varchar(30) = \'D%\',3}|ZLNb5o5t
@firstname varchar(18) = \'%\'
AS },U'SX'JB(SS
SELECT au_lname, au_fname, title, pub_name3e^4H.xM]
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles tQe iO!xO
ON t.title_id = ta.title_id INNER JOIN publishers p7muq-G"US/L,I
ON t.pub_id = p.pub_id&R6k/d M|4}k$[
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
au_info2 存储过程可以用多种组合执行。下面只列出了部分组合
x)]y8KB
EXECUTE au_info2
-- Or&Wj9pejtMC+M
EXECUTE au_info2 \'Wh%\' US oP6j.eA]H(@+]
-- Or
EXECUTE au_info2 @firstname = \'A%\'
-- Or
EXECUTE au_info2 \'[CK]ars[OE]n\' v&x!q Pvdb~%Y
-- OrV+t"R"o)N4h+y9S)N
EXECUTE au_info2 \'Hunter\', \'Sheryl\'2iL:J,~K w5Zs1m'y
-- Or
EXECUTE au_info2 \'H%\', \'S%\'
--------------------------------------------------------------------------------,o \O Le|
[2@ Lr([
4fmuv A
0]c$[E!F`
天涯风云 19:32:22 P1rp)m|/`#u~o
D. 使用 OUTPUT 参数 so!dRT#Z"|1Q4n
OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)并使用一个可选的输入参数和一个输出参数。!M&s!lYa}!a*z8g5\
首先创建过程
6TVsa,S-]
USE pubs
GO9n8R){2Z$R5q$~ H%t
IF EXISTS(SELECT name FROM sysobjects
WHERE name = \'titles_sum\' AND type = \'P\'),{:^_/atA
DROP PROCEDURE titles_sum
GO$t7L'AY$xGh&G4b
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)-{a K\7X-Oo
FROM titlese}V h7R4`
WHERE title LIKE @@TITLE
GO
接下来将该 OUTPUT 参数用于控制流语言。
}/oA1cK|tI'B
d^:Y;uS%C a:Q
? h y3_[1Nz
说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。_+fU*M.b,k
参数名和变量名不一定要匹配不过数据类型和参数位置必须匹配除非使用 @@SUM = variable 形式。 A^`x)V0nR.E
MCBa4N
DECLARE @@TOTALCOST money
EXECUTE titles_sum \'The%\', @@TOTALCOST OUTPUT!mv b8BsPX4k8c
IF @@TOTALCOST < 200
BEGIN'r)b/omr
PRINT \' \'
PRINT \'All of these titles can be purchased for less than $200.\'
END.ov {Z,zc*G"d%[/m
ELSEje$iP w?
SELECT \'The total cost of these titles is $\' hh,Idh}-n6xb
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
9w&?'Ik-p
下面是结果集
iZ#si/i
Title Name
------------------------------------------------------------------------
The Busy Executive\'s Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
(3 row(s) affected))t8yFH!a5M"Ps
A$h5kQyT G
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.b&r~v2dj%^;u3L([&w3P
天涯风云 19:32:33ez#}(K/IL~`
E. 使用 OUTPUT 游标参数
OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。8b5s p2XMMH FX
首先创建以下过程在 titles 表上声明并打开一个游标Z#aI"[2`j#DIO
*[7^;q(J1n
USE pubs%LC"D Z"{6hTK%P7L
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'titles_cursor\' and type = \'P\')b:f7p:O.u UX
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT!X(wG%vI{l"Z/l
AS)e\y%@0W-OV
SET @titles_cursor = CURSORY'iwO V0r*]
FORWARD_ONLY STATIC FOR9D#V F a0Uj#Q O
SELECT *
FROM titles
}O1Io g0A'Y%{ y/~
OPEN @titles_cursor
GO
1DUc"G:A B
接下来执行一个批处理声明一个局部游标变量执行上述过程以将游标赋值给局部变量然后从该游标提取行。
USE pubs4Z{!~x Z N9g |L
GO5xM:SJTV%Y
DECLARE @MyCursor CURSORCsQa y9]0a$j
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor;V{:@,U^7`.^ u&O
END
CLOSE @MyCursor9Ac*O?F^0Z wGX
DEALLOCATE @MyCursor0z z8N[5PhT N\
GO
F6E#k0hC9bd;f
天涯风云 19:32:43)Zw\V/l$i
F. 使用 WITH RECOMPILE 选项Mf`1t2p.hGcO;e
如果为过程提供的参数不是典型的参数并且新的执行计划不应高速缓存或存储在内存中WITH RECOMPILE 子句会很有帮助。Q[[c&C1Y-K~k
?7AdSl2s
USE pubs
IF EXISTS (SELECT name FROM sysobjectsp U6r%m)B\
WHERE name = \'titles_by_author\' AND type = \'P\')
DROP PROCEDURE titles_by_authorY@n Z:J/u
GO|n/n W%m:Ja
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = \'%\'
WITH RECOMPILEW2P h)K_2fFa9a
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 {/L-N$o5R ?
A-H1f:LCSU
天涯风云 19:32:59
G. 使用 WITH ENCRYPTION 选项
WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程使用 sp_helptext 系统存储过程获取关于加密过程的信息然后尝试直接从 syscomments 表中获取关于该过程的信息。
IF EXISTS (SELECT name FROM sysobjectsTZ9`i/td O#J
WHERE name = \'encrypt_this\' AND type = \'P\') X{}Zs
DROP PROCEDURE encrypt_this
GO
USE pubs k$[8y NU)J4P
GO.vJ-kT.Z
CREATE PROCEDURE encrypt_thisn9WfR"M0\S
WITH ENCRYPTIONd?9zHC:nC
AS
SELECT *
FROM authors
GO
EXEC sp_helptext encrypt_this[`Y4Pj/|8d
xF+l;q3B
下面是结果集
The object\'s comments have been encrypted.HehN H
o$DG%qL3pMie6b@;U
接下来选择加密存储过程内容的标识号和文本。t:H7N+i7Q
SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects oZK8C'q F1ko.}0C*G%Xx
ON c.id = o.id
WHERE o.name = \'encrypt_this\'
QC$v qe"IlV0r8C
下面是结果集 \J!S G-w @ksN
说明 text 列的输出显示在单独一行中。执行时该信息将与 id 列信息出现在同一行中。^u2X `T:F@H,`
2Z](`[1@(g
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????.KH(pJ,}"G`
!d Q%xvx*_C
(1 row(s) affected)
K I xoyG;K3uC$K
天涯风云 19:33:105F L-pb9?
H. 创建用户定义的系统存储过程
下面的示例创建一个过程显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数该过程将返回表名以 sys 开头的所有表及索引。I'A8bO9]6kc#q
K+e cdV$E1FN-b+D
IF EXISTS (SELECT name FROM sysobjects.B*HX"a ~ pV
WHERE name = \'sp_showindexes\' AND type = \'P\')oW\i{^7\6V
DROP PROCEDURE sp_showindexes+R(pe4m`
GO
USE master0_D&F ~k8^x,kd
GO
CREATE PROCEDURE sp_showindexes3O'v`5MTD:H
@@TABLE varchar(30) = \'sys%\'
AS
SELECT o.name AS TABLE_NAME,U&@s U0T;r*]
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects oCG6q Bc
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO 5RfD#kmF Bi^
USE pubs
EXEC sp_showindexes \'emp%\'
GO~u|)bl#Z
n On|0AjL]
下面是结果集
AFG!C!_:aY
TABLE_NAME INDEX_NAME INDEX_ID
---------------- ---------------- ----------------
employee employee_ind 1
employee PK_emp_id 2P'p6jC G6B
R6["bZl
(2 row(s) affected)[cI#r c5Y
%g8y_*z9Mm(N5G#[Mx
天涯风云 19:33:24
I. 使用延迟名称解析5eIz9`8q
下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在但每个存储过程都可创建。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = \'proc1\' AND type = \'P\')(Y~ X3P7vnZ
DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.LYaQwoH
USE pubsJ[M:X,wpY:@a
GO
CREATE PROCEDURE proc1
AS
SELECT *YZj-~0Aw/Sy't0uB
FROM does_not_exist)]3Ed#\)j*a5M J
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text jet1y5Jz)IJC
FROM sysobjects o INNER JOIN syscomments c 7M`3R/|4gH
ON o.id = c.idq!^of#Q2e8q D
WHERE o.type = \'P\' AND o.name = \'proc1\'9rw X$OJFm,aQ{
GO
USE masterA)M X0f!dMq,u)~
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#OUa6}4dA
GOM1r i `1r8Qd
CREATE PROCEDURE proc2{ k)]I3f/@
AS#}tK"r'L7F5wT ~(OB C
DECLARE @middle_init char(1)3{%k:@u'Ui}
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init)@:n!b6c!d8zd
FROM authorsOgu%Kx9O @&Y)|
GO 1F-U4A1IR0U.o
-- Here is the statement to actually see the text of the procedure.B/W]kp3b
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\'
[[i] 本帖最后由 天涯风云 于 2006-8-9 10:08 编辑 [/i]]
2006-8-5 19:59 天涯风云
3.sql存储过程及应用
一、简介1aEF`Z+lcf
3J tsId T { Rn$x:y
存储过程Stored Procedure 是一组为了完成特定功能的SQL 语句集经编译后
存储在数据库中用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行T!c{4Q*W3|
它 F-M#}*zi B
在SQL Server 的系列版本中存储过程分为两类系统提供的存储过程和用户自定义存储过程
。(]Q4D}!Sj
系统SP主要存储master 数据库中并以sp_为前缀并且系统存储过程主要是从系统表中获取j J$ec-}}3|v
信息从而为系统管理员管理SQL Server。 用户自定义存储过程是由用户创建并能完成'}4|u"m3c{xc-U~
某一特定功能如查询用户所需数据信息的存储过程。F2RQ H d DAo
存储过程具有以下优点
1.存储过程允许标准组件式编程(模块化设计) ALz1h4l;b
存储过程在被创建以后可以在程序中被多次调用而不必重新编写该存储过程的SQL语句而
且数
据库专业人员可随时对存储过程进行修改但对应用程序源代码毫无影响。因为应用程序源代!s {@F `r)oL
码只包含存Hs7D?m(eO,y
储过程的调用语句从而极大地提高了程序的可移植性。
e{"?h8E?)vW~
2.存储过程能够实现快速的执行速度
如果某一操作包含大量的Transaction-SQL 代码,或分别被多次执行那么存储过程要比批处理
的
执行速度快很多因为存储过程是预编译的在首次运行一个存储过程时查询优化器对其进
K'eps U-_z7Ix
行分析优 oZjea
化并给出最终被存在系统表中的执行计划而批处理的Transaction-SQL 语句在每次运行时
都要进行
编译和优化因此速度相对要慢一些。'L n"ww5q
3.存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作如查询修改如果这一操作所涉及到的Transaction-SQL e A;C%Gx k&I
语句被组织成一存储过程那么当在客户计算机上调用该存储过程时网络中传送的只是该调
aMP3L udp/{j
用语句否
则将是多条SQL 语句从而大大增加了网络流量降低网络负载。 VhPa-_3O9M/O
4.存储过程可被作为一种安全机制来充分利用
系统管理员通过对执行某一存储过程的权限进行限制从而能够实现对相应的数据访问权限的8NV}lG
限
制。
6pX(Id|O8Q6M`
二、变量R-I^Aw:~(`
@IyQP5B L{]|.PA5iJ
'D7FO9j r
三、流程控制语句(if else | select case | while )
Select ... CASE 实例WY_7S X F%X X r
DECLARE @iRet INT, @PKDisp VARCHAR(20)
SET @iRet = '1'#m-x_%`3?(jtO2N
Select @iRet = "J[{vC6C
CASE2{ \y]b,R.I#hB
WHEN @PKDisp = '一' THEN 1
WHEN @PKDisp = '二' THEN 2
WHEN @PKDisp = '三' THEN 3Upm5[T
WHEN @PKDisp = '四' THEN 4V`bJ*KQ
WHEN @PKDisp = '五' THEN 5vKgx&vBQ i@\8E
ELSE 100
ENDq As9K?3l'k3l0w
四、存储过程格式;g;[3V4o3p2s
创建存储过程
Create Proc dbo.存储过程名
存储过程参数
AS A;N"WC!@e"i6R
执行语句
RETURN
执行存储过程
GOU?:DTv9YWs/r/W
*********************************************************/3|@TJ*P^/C
-- 变量的声明,sql里面声明变量时必须在变量前加@符号VC6Ut7H
DECLARE @I INT y@W h y2c T4T-k
h:aCS0Sm?-T4z
-- 变量的赋值变量赋值时变量前必须加setr$[`iT K*o
SET @I = 30Be ^!U(U$p
on6f4gwg9Lg'j
-- 声明多个变量
DECLARE @s varchar(10),@a INT
'lT~x#m$bq
-- Sql 里if语句
IF 条件 BEGINb_:Q?IAo }
执行语句
END
ELSE BEGINw2@z^(Q }4a%d y,Ls
执行语句W!r L$lUR{
ENDFo2_9\*Ig)Yz ZLO
:x)?1|Y$N:^%}
DECLARE @d INT
set @d = 1`0@'L"IbY ~9R'h
IF @d = 1 BEGIN
_;f)u`u4r
-- 打印
PRINT '正确'v3h9Iw~
END
ELSE BEGIN$?kmWiM
PRINT '错误'
END
xA4CoW!^/D
-- Sql 里的多条件选择语句.%kU"w'b}/W?m"d
DECLARE @iRet INT, @PKDisp VARCHAR(20)
SET @iRet = 1
Select @iRet =
CASEvH6|s$osFz A&MJe
WHEN @PKDisp = '一' THEN 1
WHEN @PKDisp = '二' THEN 2/N2zT&K"]2@8W
WHEN @PKDisp = '三' THEN 3
WHEN @PKDisp = '四' THEN 47O/yD7O ]0grKC*q
WHEN @PKDisp = '五' THEN 5+a$D.]%Lk A
ELSE 1009F7K7Ue;Fa D
END
Dr r$@:I.W
-- 循环语句
WHILE 条件 BEGIN
执行语句
END7t;^pml
DECLARE @i INT2PI1a S!lY,n
SET @i = 1
WHILE @i<1000000 BEGINmRGs D!Z _GKsG
set @i=@i+1yu.?6` rQ)j+G)O-m
END
-- 打印
PRINT @i
G Iy|@t {q
1ss#J&`$dc IxU
-- TRUNCATE 删除表中的所有行而不记录单个行删除操作不能带条件 n#~-S VY
+Ky0OW^8Bv&U
/*
TRUNCATE TABLE 在功能上与不带 Where 子句的 Delete 语句相同二者均删除表中的全部行
+??;y.g9XP9nL
。但 TRUNCATE TABLE 比 Delete 速度快且使用的系统和事务日志资源少。
Delete 语句每次删除一行并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过
释放存储表数据所用的数据页来删除数据并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行但表结构及其列、约束、索引等保持不变。新行标识所用"ah2z:h*uw v?TyMT
的计数值重置为该列的种子。如果想保留标识计数值请改用 Delete。如果要删除表定义及其数据请|uu.zW,D
使用 Drop TABLE 语句。,HN"\N,shG x Ei"af
对于由 FOREIGN KEY 约束引用的表不能使用 TRUNCATE TABLE而应使用不带 Where 子句的 Zk"c p:FH8j|
Delete 语句。由于 TRUNCATE TABLE 不记录在日志中所以它不能激活触发器。 ^4Z(Iq H,j
TRUNCATE TABLE 不能用于参与了索引视图的表。)x:P}7L l~n
示例"q,C1u#t&~%s,J
下例删除 authors 表中的所有数据。*/ R&Br?8{pwR
sx8v\OF[
TRUNCATE TABLE authors
-- Select INTO 从一个查询的计算结果中创建一个新表。 数据并不返回给客户端这一点和普通的
-- Select 不同。 新表的字段具有和 Select 的输出字段相关联相同的名字和数据类型。 N4y'ztO n
NZq[|&g
select * into NewTable0|@*qfN#?J8je
from UnameVq X sP8U
-- Insert INTO Select
-- 表ABC必须存在
-- 把表Uname里面的字段Username复制到表ABC6~F+v:M3{ p
Insert INTO ABC Select Username FROM UnameZ,kq H_
-- 创建临时表
Create TABLE #temp(
UID int identity(1, 1) PRIMARY KEY,
UserName varchar(16),y8pZVx;ii
Pwd varchar(50),GBQ$r(~3c'qo
Age smallint,
Sex varchar(6)
)
-- 打开临时表
Select * from #temp m#HWG o
1R0x5Wa0j-Y+e
-- 存储过程1^L H| TO|%uM
-- 要创建存储过程的数据库 Rz gA2N b'}'e{`*@(d
Use Test
-- 判断要创建的存储过程名是否存在 v$l+q DN)dFb R5sg
if Exists(Select name From sysobjects Where name='csp_AddInfo' And g.] S#hN
)X'Hwb+_Z
type='P')
-- 删除存储过程(OA @ vR;E+L
Drop Procedure dbo.csp_AddInfolF[YY[2Exa[
Go
#\,b*IQc:@"iy
-- 创建存储过程
Create Proc dbo.csp_AddInfoO*C$O a7x*zv
-- 存储过程参数
@UserName varchar(16),\w2W'Aj0]
@Pwd varchar(50),-p"^5~ Iui1m%X
@Age smallint,
@Sex varchar(6)
AS
-- 存储过程语句体 F {x[?"|h
insert into Uname (UserName,Pwd,Age,Sex)
values (@UserName,@Pwd,@Age,@Sex)
RETURN7CF1N#Yj
-- 执行
GO!F$WB*]2eb
u%h Y*]:\H3~'A2O
-- 执行存储过程
EXEC csp_AddInfo 'Junn.A','123456',20,'男'
2006-8-5 20:01 天涯风云
4.各种存储过程使用指南
<%@ Language=VBScript %>
<%
'---开始链接数据库
Dim strConnString
strConnString = "driver={SQL Server};server=songhp;uid=sa;pwd=;database=XVZDemo"n*k_6P7A T3BwI
set Conn = Server.CreateObject("ADODB.Connection").h`viM&N
Conn.Open strConnstring,B.ab$}1D.\
'---结束链接数据库
'---开始为输入参数赋值
Dim SelectSql , SelectRs
Dim SelectID , SelectName , SelectReturn&Zp#{,DK9H(ra
SelectSql = "Select Max(CompanyID) From Dim_Company"k"]!a?e
Set SelectRs = Conn.Execute(SelectSql)
SelectID = SelectRs(0)&T3l*D6bA8bW5@
'---结束为输入参数赋值
Dim TiggerType7m0}6T*@2Y'`
TiggerType = 3
Set Cmd = Server.CreateObject("ADODB.Command") 4w,mT IC9ykQD
Set Cmd.ActiveConnection = Conn)f,r#x_3y
Cmd.CommandType = 4 '---声明此过程为存储过程
$n-F3VL9zU.`W a
If TiggerType = 1 thenL2x wR|7I nM3l]B7[-Ku
.NoTA.?:O.Go;VdH
'---开始一个输入参数的存储过程调用
Cmd.CommandText = "TransCompany1"
Set CmdParam = Cmd.CreateParameter("@TransID",3,1)Ly/s3~Hi|6mNk
Cmd.Parameters.Append CmdParam
Cmd("@TransID") = SelectID
Cmd.Executek*j5hi0N
'---结束一个输入参数的存储过程调用R8yP!]f"WE
Elseif TiggerType = 2 then
'---开始一个输入参数一个输出参数的存储过程调用a N^z-LDx9p k
Cmd.CommandText = "TransCompany2"
!a{$u7xk%n(l%h
Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)0T%y4U.F2A![
Cmd.Parameters.Append CmdParamID
Cmd("@TransID") = SelectIDyMZ6g ke u2O
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamNameiceYY+u/l
Cmd.Execute
SelectName = Cmd("@TransName")
'---结束一个输入参数一个输出参数的存储过程调用8~ x }1i'Nm
Elseif TiggerType = 3 then4KA#j1GN
#MU+TK0P6upv
'---开始一个输入参数一个输出参数一个返回值的存储过程调用
Cmd.CommandText = "TransCompany3"
Set CmdParamReturn = Cmd.CreateParameter("Return_Value",3,4).T\zCQif
Cmd.Parameters.Append CmdParamReturn5Z5dBzv$s(r c D
Set CmdParamID = Cmd.CreateParameter("@TransID",3,1)/Q|Cd1|;?e
Cmd.Parameters.Append CmdParamID%AL{ `;s+dz?l!];C
Cmd("@TransID") = SelectID
Set CmdParamName = Cmd.CreateParameter("@TransName",202,2,50)
Cmd.Parameters.Append CmdParamName
Cmd.Execute:SH5Ne!sme0z |)p$m
SelectName = Cmd("@TransName")(_X t$`MpM
SelectReturn = Cmd("Return_Value")
'---结束一个输入参数一个输出参数一个返回值的存储过程调用.?h'V}*yC$V;N1F.U
~4Ln$elF
End if!Bq/J\4S*Q
Conn.Close
Set Conn = Nothing
Set Cmd = Nothing
Set CmdParamID = Nothing
Set CmdParamname = Nothing['xj+XhqVh3f
Set CmdParamReturn = Nothing,Ea-@kJ%ub
%>
2006-8-5 20:02 天涯风云
5.ASP中存储过程调用的两种方式及比较
本人用sql server 和asp写了一个简单的留言本在不断的尝试中发现分页显示留言的时候不同的执行方式时间上的一些差别。
*F#gJl\
下面通过对比来看看几种方式的用时对比。 | T[{0x+B_%p0T-sb Uj
一使用存储过程分页这种情况又分为两种方式 nQ-p7NA@Ua
第一种使用command对象如下@'C {1qR]2n
Set Cmd=server.CreateObject("Adodb.Command")
Cmd.ActiveConnection=conn
Cmd.CommandText="ycuu_gb_getmsg"
Cmd.CommandType=4'adCmdStoredProc:{}L f%f P7V
cmd.prepared=true'
set param=Cmd.CreateParameter("@iPageNo",adInteger,1,2,Page)
Cmd.Parameters.Append param
set param=Cmd.CreateParameter("@iPageSize",adInteger,1,2,PageSizeConst)S2K^'eK_x3Ff
Cmd.Parameters.Append param
set rs=Cmd.execute
第二种使用connection对象的执行方法直接执行具体如下,Zm3f0G"|R*^R+HO$W
set rs=conn.execute ("execute ycuu_gb_getmsg "&page&", "&pagesizeConst)ch} l3waNf
Ft1V(c,OC(Ew~
二不使用存储过程直接使用ADODB.RecordSet的功能来分页具体代码如下2M']-V[ b6[ b Va e
;Q7|V2}+HTa
Set rs = Server.CreateObject("ADODB.Recordset"))aL3V|%eiiO
sql = "Select * FROM Guestbook Order By dateandtime Desc"4L;_'oSDZ4J.p~P/i
rs.open sql,conn,1,1JJ U#K!hM4u
rs.pagesize = 150'每页显示的留言数量
total = rs.RecordCount
mypagesize = rs.pagesize$t qXLL7q
rs.absolutepage = page
为了更加明显地显示出速度我把每页显示的留言数量加大到150(事实上当然不会设置这么大的数值啦)。至于我机器的配置就省略不说了因为主要是速度对比。
发现执行的时候时间分别如下ka"xb^
{J v7yDn7W)S:dUD
第一种稳定于0.1953125 秒到0.2109375 秒之间平均值大概是0.20秒
7I+ZD3kh0Guk'U
第二种稳定于0.1716875 秒到0.1857秒之间平均值大概是0.177秒
G7[#drf9i]w6|
第三种稳定于0.4375 秒到0.4632秒之间平均值大概是0.45秒'EJVJf ]_
6Ts&Z$H&K9e8|-Vt
但是当读取的记录条数为20的时候结果如下1?? u1K!j
发现执行的时候时间分别如下#q8I'j.t;o,c
第一种稳定于.0390625 秒到.0546875 秒之间平均值大概是0.045秒6y8R'h|p5A.z
0\ j t_n[3HZy-Q$q
第二种稳定于0.046875 秒到.0546875 秒之间平均值大概是0.050秒%k.D{ {:yhr
第三种稳定于.09375 秒到0.1015625 秒之间平均值大概是0.97秒}qqA2Y7c
在这样看来似乎conn.execute和command.execute这两种方式似乎差别并不大
而前者的调用方式好像更加简单一点。-T/KC O1j4^1r
同时在这里可以看出分页的存储过程速度确实比recordset的分页速度要快很多。f9o9` ^F3^"p9T
+_^7qv\
PS小弟第一次发文呜呜呜发现写一篇好的真难我以后会努力的了。希望大家包涵我这次写得不好。对了我还想问问各位大侠conn.execute和command.execute这两种方式中那种更加好的呵呵因为我在网上找到的都是后者这种方式执行存储过程的。不知道为什么不用前面那种那么简单的。
2006-8-5 20:03 天涯风云
6.SQL存储过程在.NET数据库中的应用
一前言 4o6fto ~@
存储过程Stored Procedure是一组为了完成特定功能的SQL语句集经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行它。存储过程是数据库中的一个重要对象任何一个设计良好的数据库应用程序都应该用到存储过程。总的来说存储过程具有以下一些优点 -?PBIxmo4b:ohC
`%Wlz.](m1Y Ljj
◆存储过程允许标准组件式编程 &?.c'af1l%S Kj"?EN
◆存储过程能够实现较快的执行速度
"FwHTS4vby
◆存储过程能够减少网络流量 /qX w!JW
"O,xM%X;ec JS
◆存储过程可被作为一种安全机制来充分利用
本文作者将向大家介绍.NET数据库应用程序中存储过程的应用以及如何将它与ADO.NET中的SqlDataAdapter对象、DataSet对象等结合使用以提高.NET数据库应用程序的总体性能。
5T/_c,t$mVA_
二系统要求
WNyfO0l Jy
开发工具Visual Studio.NET 1@0Gg&`x&edG
数据库管理系统SQL Server 2000其中包含了示例程序所用到的Pubs数据库 0E,fx:H{nl6Cj
三创建一个简单的存储过程
这里我将向大家介绍如何运用Visual Studio.NET IDE来创建存储过程。运用Visual Studio.NET IDE创建存储过程是非常容易和直观的你只要在服务器资源管理器中导向到Pubs数据库并展开节点就会发现包括存储过程在内的各种数据库对象如图1所示。
'M U-@|&_/t;A
在存储过程节点上点击右键便可弹出一个菜单其中包含了“新建存储过程”的命令。新建一个存储过程后IDE中的代码编辑窗口便出现如下所示的代码模板 "Z7vR!^I9I
C&IH,d(vTNOA
!n0\$O:r U'Y_
CREATE PROCEDURE dbo.StoredProcedure1 1r+w#g!TP [/w`
/*
(
@parameter1 datatype = default value, E!xL2f S6|%v,^ y8NF_
@parameter2 datatype OUTPUT ) #CU~1ugE/@ R;yT
*/
AS )?rI@6o(J
/* SET NOCOUNT ON */
RETURN
[p2qgb5@_8C_
上面的代码模板符合简化的创建存储过程的语法规则完整的语法规则如下
6x/@y*dl5\
CREATE PROC [ EDURE ] procedure_name [ ; number ] !P DO`aH+z
[ { @parameter data_type } m9\ NVhX)z
[ VARYING ] [ = default ] [ OUTPUT ] j4Y/F b'w K+H(i)v
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ] /v3I3c&{(TO6C
AS sql_statement [ ...n ] 2Z(fDE?Bd
限于篇幅各个参数的含义在此就不多作介绍了有兴趣的读者可以参考有关SQL Server 2000数据库管理系统的资料。 wP3L%SjH
下面我对该代码模板中的各个语法成分略作介绍。CREATE PROCEDURE声明创建一个存储过程后面跟着该存储过程的名称。“/*……*/”中的成分是该存储过程的参数可包括输入参数和输出参数。AS关键字后面的内容是该存储过程的主体部分其中是任何数量和类型的包含在存储过程中的SQL语句。RETURN关键字表明存储过程结束并能返回整型状态值给调用者。下面我们就来创建一个简单的不带参数的存储过程并运用之
3S p@4Z'f*JF
CREATE PROCEDURE dbo.up_GetPublisherInfo G#`5T a|!c\
AS
SELECT pub_id, pub_name, city, state, country ,|y:b ^.w`/B8`Z]gEY
FROM publishers "KO6L-xk+J"U;bM
RETURN 0?Sa;C&T#DT l6R#f
SR!A&j']%axf d
R0|%LP8uSIqiDJ.y-r
.i%[`6i{\Y
创建以上存储过程后保存之。保存完毕与该存储过程相对应的节点就会出现在服务器资源管理器中。同时请注意代码编辑窗口中的CREATE关键字变为ALTER关键字了该关键字是用于更改任何现有的存储过程的。要运行上述存储过程只要点击其节点并在右键弹出菜单中选择“运行存储过程”运行的结果图示如下 ~:o4eT&s:_Z
Lt[:\/`h5v
6VEod-o~x
四创建一个带参数的存储过程 bTs X d5i a
S)D}n ^/G(y?Pd
以上我们创建了一个简单的不带参数的存储过程而在实际的应用中往往会用到很多带有参数的存储过程。带有参数的存储过程一般是用于更新数据或是插入数据的。下面我们可以运用同样的操作方法创建一个带参数的存储过程
,l [\d e6i
F)P4k?H7P
CREATE PROCEDURE dbo.up_UpdatePublisherInfo
( +^?s\7Z0\%[3w4o
@pub_id char (4), tPCV zp e]g.Z
@pub_name varchar (40),
@city varchar (20), B5aG/CwC
@state char (2),
@country varchar (30) x| x/b'|k{5p
) /N!Jq z~:Sg2g\K-V
AS
UPDATE publishers
SET pub_name = @pub_name, city = @city, state = @state, D7Rp+_D r$fp
country = @country 'H,x1Y{ SbD7K
WHERE ( pub_id = @pub_id ) U7xRKrm
RETURN 6IX0f^W
6}1w7n]v5Z
6^&o$j GT.@ r3w/l:X
9i6g,xlBxb"xv{lU
0x(}-UwT` QC4REO
在上面的创建存储过程的代码中我们通过在名称前添加一个“@”标志来声明存储过程的局部变量参数同时还声明了各个参数的类型确定了各个参数的方向值也即表明该参数是输入型的还是输出型的或者是输入输出型的或者是返回值型的。用户通过相应的存储过程名称以及正确有效的参数便可调用该存储过程了。还有你可以通过运用OUTPUT关键字在参数中添加输出型的参数具体方法请参考上面的语法规则。输出型的参数能返回给调用者相关的信息。 1v1W-I EK[ |1a t
上面的存储过程能更新publishers表中相应出版商的信息。你可以通过点击该存储过程的节点在右键弹出菜单中选择“运行存储过程”来执行它。一旦执行IDE中便弹出一个输入出版商信息的对话框如图3所示。在该对话框中填入正确有效的更新信息注意pub_id的值在原来的表中必须存在然后点击“确定”按钮便可更新数据了。
2006-8-5 20:03 天涯风云
7.使用SQL存储过程要特别注意的问题
存储过程是一个运行于SQL数据库之中最核心的事务,它通过长驻内存的形式,进行读取\处理\写入最为频繁处理的数据.
ASP虽然在微软的ASPX的侵袭下仍旧是中小企业继续在用的一种网页语言但是当要读取海量数据的时候,如果仍旧使用普通的SQL进行读取运行与写入,将导致系统资源的严重浪费所以我们在ASP中使用存储过程,以提高数据的存取速度,同时通过SQL核心的获取数据的方法,可以有效的减少垃圾数据(不被立即使用,而且也不备较短的时间里被使用的数据)操作.
在ASP中使用存储过程也是相当的容易,例如以下一个通过SQL存储过程进行分页的方法: D5v0a6M({Y
存储过程: 6ISh;@x#t({'l'CW
CREATE procedure p_splitpage
@sql nvarchar(4000), --要执行的sql语句 Hh/I]'U
@page int=1, --要显示的页码 up1qH"M@
@pageSize int, --每页的大小 NeRqm`
@pageCount int=0 out, --总页数 J!_f#Y3x
@recordCount int=0 out --总记录数
as
set nocount on -_;b`dgU)V Z
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1 @d ssW9aJ'i`
exec sp_cursorfetch @p1,16,@page,@pagesize M6?^*`*WO-v
exec sp_cursorclose @p1 ZoX$_4_.oR
GO
ASP页面的内容
sql = "Select id, c_s_name from tabNews where deleted<>1 Order By id Desc" 5[Y+_a"}I3vxA
page = cint(page_get) -N rZT'b M:Z#G!NHHe
if page_post<>""then
page = cint(page_post)
end if
if not page > 0 then qW^\p)Ld8Z[e
page = 1 g0w jBg7DZ2s
end if
pagesize=20’每页的条数 /N+Qei2s a
set cmd = server.CreateObject("adodb.command") [:PHj-U#f6G
cmd.ActiveConnection = conn W)n(ML7L*Q4f#z
cmd.CommandType = 4 w'qt,o4Rjbe_
cmd.CommandText = "p_SplitPage" WU Gc] JVd
cmd.Parameters.Append cmd.CreateParameter("@sql",8,1, 4000, sql) 7b,`|x q0cLtGW+q
cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, page)
cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1, 4, pageSize)
cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, pageCount) oJ/|ht
cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, recordCount)
set rs = cmd.Execute p&N_3{cuK!nJ7G
set rs = rs.NextRecordSet 1l!_xpf"A
pageCount = cmd.Parameters("@pageCount").value
recordCount = cmd.Parameters("@recordCount").value `f9_*j-kC+X0zY
if pageCount = 0 then pageCount = 1 *FVu]N&K|
if page>pageCount then X3iN)eu b PH
response.Redirect("?page="&pageCount)
end if
set rs = cmd.Execute
我们如此就可以实现对数据的读取并可以进行有效的分页,但是我们往往会发现一个问题,如果我们构造的SQL语句如果使用的是select * from tab ...的话就经常出现无法读取数据的错误,或者是读取出来,但是有的数据无法显示的错误经过仔细的检查发现,如果是排列在SQL语句的前列的数据可以被读取,而如果不按照SQL读取出来的字段进行顺序读取,就会出现数据丢失的情况,所以唯一的途径就是进行顺序读取.如:
对于select id, newsTitle, newsContent from tabNews where ...的SQL语句,就应当将所有的数据读取到变量上来并且要求是按照SQL语句的顺序进行读取然后这些数据就可以自由的使用了. R`Ec.pT/b,` pk
id = rs("id") 4`(_L0_$pq~.S
newsTitle = rs("newsTitle") Ee.e;b#tu
... ;de&H1dkF
分析出现这个的原因是:SQL数据库在构造虚拟表的时候就是以一种先进先出的原则,把所有的数据排列在一个内存段之中,通过顺序的读取,将数据逐一的读取,而如果跳过某个具体的字段获取下一个字段的信息,系统就会将原来的那个字段的信息丢失,以释放内存,这是出于系统构造的简单性和系统的内存最低化的要求,所以这样也保证了有限的内存资源得到最充分的发挥,这也是为什么存储过程比普通的SQL要快的原因。
原文网址链接