MSSQL网页设计SQL Server联机丛书：实行存储历程
上面我们说了DML的闪回方案。但对于DDL却无能为力，对于大多数的DDL，即使是rowbase格式，二进制日志binlog中仍只记录语句本身。对于删表操作，只记录一个语句droptablet。仅凭这句话，无法还原表的数据。server|存储历程|实行EXECUTE实行标量值的用户界说函数、体系历程、用户界说存储历程或扩大存储历程。同时撑持Transact-SQL批处置内的字符串的实行
若要叫醒挪用函数，请利用EXECUTEstored_procedure中形貌的语法。
语法
实行存储历程：[]
{
[@return_status=]
{procedure_name[;number]|@procedure_name_var
}
[[@parameter=]{value|@variable|]
[,...n]
实行字符串：EXEC({@string_variable|tsql_string}[+...n])参数
@return_status
是一个可选的整型变量，保留存储历程的前往形态。这个变量在用于EXECUTE语句前，必需在批处置、存储历程或函数中声明过。
在用于叫醒挪用标量值用户界说函数时，@return_status变量能够是任何标量数据范例。
procedure_name
是拟挪用的存储历程的完整正当大概不完整正当的称号。历程称号必需切合标识符划定规矩。有关更多信息，请拜见利用标识符。不管服务器的代码页或排序体例怎样，扩大存储历程的称号老是辨别巨细写。
用户能够实行在另外一数据库中创立的历程，只需该用户具有此历程或有在该数据库中实行它的得当的权限。用户能够在另外一台运转Microsoft®SQLServer™的服务器上实行历程，只需该用户有得当的权限利用该服务器（远程会见），并能在数据库中实行该历程。假如指定了服务器称号但没有指定命据库称号，SQLServer会在用户默许的数据库中寻觅该历程。
;number
是可选的整数，用于将不异称号的历程举行组合，使得它们能够用一句DROPPROCEDURE语句撤除。该参数不克不及用于扩大存储历程。
在统一使用程序中利用的历程一样平常都以该体例组合。比方，在订购使用程序中利用的历程能够orderproc;1、orderproc;2等来定名。DROPPROCEDUREorderproc语句将撤除全部组。在对历程分组后，不克不及撤除组中的单个历程。比方，DROPPROCEDUREorderproc;2是不同意的。有关历程组的更多信息，请拜见CREATEPROCEDURE。
@procedure_name_var
是部分界说变量名，代表存储历程称号。
@parameter
是历程参数，在CREATEPROCEDURE语句中界说。参数称号前必需加上标记(@)。在以@parameter_name=value格局利用时，参数称号和常量纷歧定依照CREATEPROCEDURE语句中界说的按次呈现。可是，假如有一个参数利用@parameter_name=value格局，则别的一切参数都必需利用这类格局。
默许情形下，参数可为空。假如传送NULL参数值，且该参数用于CREATE或ALTERTABLE语句中不同意为NULL的列（比方，拔出至不同意为NULL的列），SQLServer就会报错。为制止将NULL参数值传送给不同意为NULL的列，能够在过程当中增加程序计划逻辑或接纳默许值（利用CREATE或ALTERTABLE语句中的DEFAULT关头字）。
value
是过程当中参数的值。假如参数称号没有指定，参数值必需以CREATEPROCEDURE语句中界说的按次给出。
假如参数值是一个工具称号、字符串或经由过程数据库称号或一切者称号举行限定，则全部称号必需用单引号括起来。假如参数值是一个关头字，则该关头字必需用双引号括起来。
假如在CREATEPROCEDURE语句中界说了默许值，用户实行该历程时能够不用指定参数。假如该历程利用了带LIKE关头字的参数称号，则默许值必需是常量，而且能够包括%、_、[]及[^]通配符。
默许值也能够为NULL。一般，历程界说会指定当参数值为NULL时应当实行的操纵。
@variable
是用来保留参数大概前往参数的变量。
OUTPUT
指定存储历程必需前往一个参数。该存储历程的婚配参数也必需由关头字OUTPUT创立。利用游标变量作参数时利用该关头字。
假如利用OUTPUT参数，目标是在挪用批处置或历程的别的语句中利用其前往值，则参数值必需作为变量传送（即@parameter=@variable）。假如一个参数在CREATEPROCEDURE语句中不是界说为OUTPUT参数，则对该参数指定OUTPUT的历程不克不及实行。不克不及利用OUTPUT将常量传送给存储历程；前往参数必要变量称号。在实行历程之前，必需声明变量的数据范例并赋值。前往参数能够是text或image数据范例之外的恣意数据范例。
DEFAULT
依据历程的界说，供应参数的默许值。当历程必要的参数值没有事前界说好的默许值，或短少参数，或指定了DEFAULT关头字，就会堕落。
n
是占位符，暗示在它后面的项目能够屡次反复实行。比方，EXECUTE语句能够指定一个大概多个@parameter、value或@variable。
WITHRECOMPILE
强迫编译新的企图。假如所供应的参数为非典范参数大概数占有很年夜的改动，利用该选项。在今后的程序实行中利用变动过的企图。该选项不克不及用于扩大存储历程。倡议只管少利用该选项，由于它损耗较多体系资本。
@string_variable
是部分变量的称号。@string_variable能够是char、varchar、nchar或nvarchar数据范例，最年夜值为服务器的可用内存。假如字符串长度凌驾4,000个字符，则把多个部分变量串连起来用于EXECUTE字符串。有干系统供应的SQLServer数据范例更多的信息，请拜见数据范例。
tsql_string
是一个常量，tsql_string能够是nvarchar或varchar数据范例。假如包括N，则该字符串将注释为nvarchar数据范例，最年夜值为服务器的可用内存。假如字符串长度凌驾4,000个字符，则把多个部分变量串连起来用于EXECUTE字符串。
正文
假如历程称号的前三个字符为sp_，SQLServer会在Master数据库中寻觅该历程。假如没能找到正当的历程称号，SQLServer会寻觅一切者称号为dbo的历程。若要将存储历程称号剖析为与体系存储历程同名的用户界说存储历程，请供应一个完整正当的历程称号。
参数能够经由过程使用value或@parameter_name=value来供应。参数不是事件的一个部分；因此假如事件中的参数值变动，且该事件在今后回滚，该参数值不会退回到之前的值。前往给挪用方的值老是历程前往时的值。
当一个存储历程挪用另外一个存储历程时，会发生嵌套。当挪用的历程入手下手实行时，嵌套级会增添，当挪用历程实行停止时，嵌套级则会削减。嵌套级最高为32级，凌驾32级时，会招致全部挪用历程链失利。以后的嵌套级存储在@@NESTLEVEL函数中。
SQLServer今朝利用前往值0到-14来暗示存储历程的实行形态。值–15到-99留作后用。有关保存的前往形态值的列表的更多信息，请拜见RETURN。
由于远程存储历程和扩大存储历程不在事件的感化域中（除非在BEGINDISTRIBUTEDTRANSACTION语句中收回大概是和分歧的设置选项一同利用），以是经由过程挪用实行的命令不克不及回滚。有关更多信息，请拜见体系存储历程和BEGINDISTRIBUTEDTRANSACTION。
当利用游标变量时，假如实行的历程传送一个分派有游标的游标变量，就会堕落。
在实行存储历程时，假如语句是批处置中的第一个语句，则纷歧定要指定EXECUTE关头字。
利用带字符串的EXECUTE命令
利用字符串串连运算符(+)为静态实行创立长字符串。每一个字符串表达式能够是Unicode与non-Unicode数据范例的夹杂。
只管每一个tsql_string或@string_variable不得凌驾8,000个字节，SQLServer语法剖析器中对这类串连只举行逻辑处置而不占用物理内存。比方，该语句决不会天生长16,000个串连起来的字符串：EXEC(name_of_8000_char_string+another_name_of_8000_char_string)
在EXECUTE语句实行前，不会编译EXECUTE语句内的语句。
数据库情况的变动只在EXECUTE语句停止前无效。比方，在这个例子的EXEC后，数据库情况是master：USEmasterEXEC("USEpubs")SELECT*FROMauthors
权限
存储历程的EXECUTE权限默许给该存储历程的一切者，该一切者能够将此权限让渡给其他用户。当碰到EXECUTE语句时，即便EXECUTE语句是在存储过程当中，也会反省在EXECUTE字符串内利用该语句的权限。当运转一个实行字符串的存储历程时，体系会在实行该历程的用户情况中，而不是在创立该历程的用户情况中反省权限。可是，假如某用户具有两个存储历程，而且第一个历程挪用第二个历程，则在第二个过程当中不举行EXECUTE权限反省。
示例A.利用EXECUTE传送单个参数
showind存储历程必要参数(@tabname)，它是一个表的称号。上面这个例子实行showind存储历程，以titles为参数值。
申明showind存储历程只是用来作为一个例子，pubs数据库并没有此历程。
EXECshowindtitles
在实行过程当中变量能够显式定名：
EXECshowind@tabname=titles
假如这是isql剧本或批处置中第一个语句，则EXEC语句能够省略：
showindtitles
-或-
showind@tabname=titles
B.利用多个参数与一个输入参数
这个例子实行roy_check存储历程，传送三个参数。第三个参数@pc是输入参数。历程实行完后，前往变量能够从变量@percent
失掉。
申明roy_check存储历程只是用作举例，pubs数据库中并没有此历程。
DECLARE@percentintEXECUTEroy_checkBU1032,1050,@pc=@percentOUTPUTSETPercent=@percent
C.利用带一个变量的EXECUTEtsql_string语句
这个例子显现EXECUTE语句怎样处置静态天生的、含有变量的字符串。这个例子创立tables_cursor游标来保留一切用户界说表
(type=U)的列表。
申明此例子只用作举例。DECLAREtables_cursorCURSOR
FOR
SELECTnameFROMsysobjectsWHEREtype=U
OPENtables_cursor
DECLARE@tablenamesysname
FETCHNEXTFROMtables_cursorINTO@tablename
WHILE(@@FETCH_STATUS-1)
BEGIN
/*A@@FETCH_STATUSof-2meansthattherowhasbeendeleted.
Thereisnoneedtotestforthisbecausethisloopdropsall
user-definedtables.*/.
EXEC(DROPTABLE+@tablename)
FETCHNEXTFROMtables_cursorINTO@tablename
END
PRINTAlluser-definedtableshavebeendroppedfromthedatabase.
DEALLOCATEtables_cursor
D.利用带远程存储历程的EXECUTE语句
这个例子在远程服务器SQLSERVER1上实行checkcontract存储历程，在@retstat中保留前往形态，申明运转乐成或失利。
DECLARE@retstatint
EXECUTE@retstat=SQLSERVER1.pubs.dbo.checkcontract409-56-4008
E.利用带扩大存储历程的EXECUTE语句
下例利用xp_cmdshell扩大存储历程列出文件扩大名为.exe的一切文件的目次。
USEmaster
EXECUTExp_cmdshelldir*.exe
F.利用带一个存储历程变量的EXECUTE语句
这个例子创立一个代表存储历程称号的变量。
DECLARE@proc_namevarchar(30)
SET@proc_name=sp_who
EXEC@proc_name
G.利用带DEFAULT的EXECUTE语句
这个例子创立了一个存储历程，过程当中第一个和第三个参数为默许值。当运转该历程时，假如挪用时没有传送值大概指定了默许值，
这些默许值就会赋给第一个和第三个参数。注重DEFAULT关头字有多种利用办法。
USEpubs
IFEXISTS(SELECTnameFROMsysobjects
WHEREname=proc_calculate_taxesANDtype=P)
DROPPROCEDUREproc_calculate_taxes
GO
--Createthestoredprocedure.
CREATEPROCEDUREproc_calculate_taxes(@p1smallint=42,@p2char(1),
@p3varchar(8)=CAR)
AS
SELECT*
FROMmytable
proc_calculate_taxes存储历程能够以多种组合体例实行：EXECUTEproc_calculate_taxes@p2=A
EXECUTEproc_calculate_taxes69,B
EXECUTEproc_calculate_taxes69,C,House
EXECUTEproc_calculate_taxes@p1=DEFAULT,@p2=D
EXECUTEproc_calculate_taxesDEFAULT,@p3=Local,@p2=E
EXECUTEproc_calculate_taxes69,F,@p3=DEFAULT
EXECUTEproc_calculate_taxes95,G,DEFAULT
EXECUTEproc_calculate_taxesDEFAULT,H,DEFAULT
EXECUTEproc_calculate_taxesDEFAULT,I,@p3=DEFAULT
使为了数据安全，我们搭建了主从。但实时主从备份只能防止硬件问题，比如主库的硬盘损坏。但对于误操作，则无能为力。比如在主库误删一张表，或者一个update语句没有指定where条件，导致全表被更新。 同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。 不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理? 这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？ 财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的.. 以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了 groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。 大家注意一点。如下面的例子：
页:
[1]