SQL Server 存储过程详解.docx
- 文档编号:6430153
- 上传时间:2023-01-06
- 格式:DOCX
- 页数:11
- 大小:22.33KB
SQL Server 存储过程详解.docx
《SQL Server 存储过程详解.docx》由会员分享,可在线阅读,更多相关《SQL Server 存储过程详解.docx(11页珍藏版)》请在冰豆网上搜索。
SQLServer存储过程详解
SQLServer存储过程详解
◆优点:
执行速度更快。
存储过程只在创造时进行编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程执行速度更快。
存储过程用于处理复杂的操作时,程序的可读性更强、网络的负担更小。
使用存储过程封装事务性能更佳。
能有效的放注入,安全性更好。
可维护性高,在一些业务规则发生变化时,有时只需调整存储过程即可,而不用改动和重编辑程序。
更好的代码重用。
◆缺点:
存储过程将给服务器带来额外的压力。
存储过程多多时维护比较困难。
移植性差,在升级到不同的数据库时比较困难。
调试麻烦,SQL语言的处理功能简单。
总之复杂的操作或需要事务操作的SQL建议使用存储过程,而参数多且操作简单SQL语句不建议使用存储过程。
存储过程定义
存储过程是一组Transact-SQL语句,它们只需编译一次,以后即可多次执行。
因为Transact-SQL语句不需要重新编译,所以执行存储过程可以提高性能。
触发器是一种特殊的存储过程,不由用户直接调用。
创建触发器时,将其定义为在对特定表或列进行特定类型的数据修改时激发。
存储过程的设计规则
CREATEPROCEDURE定义自身可以包括任意数量和类型的SQL语句,但以下语句除外。
不能在存储过程的任何位置使用这些语句。
CREATEAGGREGATE、CREATERULE、CREATEDEFAULT、CREATESCHEMA、CREATE或ALTERFUNCTION、CREATE或ALTERTRIGGER、CREATE或ALTERPROCEDURE、CREATE 或ALTERVIEW、SETPARSEONLY、SETSHOWPLAN_ALL、SETSHOWPLAN_TEXT、SETSHOWPLAN_XML、USEdatabase_name
其他数据库对象均可在存储过程中创建。
可以引用在同一存储过程中创建的对象,只要引用时已经创建了该对象即可。
可以在存储过程内引用临时表。
如果在存储过程内创建本地临时表,则临时表仅为该存储过程而存在;退出该存储过程后,临时表将消失。
如果执行的存储过程将调用另一个存储过程,则被调用的存储过程可以访问由第一个存储过程创建的所有对象,包括临时表在内。
如果执行对远程MicrosoftSQLServer2005实例进行更改的远程存储过程,则不能回滚这些更改。
远程存储过程不参与事务处理。
存储过程中的参数的最大数目为2100。
存储过程中的局部变量的最大数目仅受可用内存的限制。
根据可用内存的不同,存储过程最大可达128MB
实现存储过程
CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[;number]
[{@parameter[type_schema_name.]data_type}[VARYING][=default][[OUT[PUT]]--名称、类型、默认值、方向
[,...n]
[WITH
[FORREPLICATION]
AS
{
[;]
:
=
[ENCRYPTION]
[RECOMPILE]--运行时编译
[EXECUTE_AS_Clause]
:
={[BEGIN]statements[END]}
:
=EXTERNALNAMEassembly_name.class_name.method_name
执行存储过程
使用Transact-SQLEXECUTE语句。
如果存储过程是批处理中的第一条语句,那么不使用EXECUTE关键字也可以执行存储过程
使用sp_procoption让SQLSERVER自动执行存储过程
sp_procoption[@ProcName=]'procedure',[@OptionName=]'option' ,[@OptionValue=]'value'--过程的名称、option的唯一值为startup、设置为开启(true或on)还是关闭(false或off)。
用TSQL语句编写存储过程
一、变量和参数
DECLARE语句通过以下操作初始化Transact-SQL变量:
指定名称。
名称的第一个字符必须为一个@。
指定系统提供的或用户定义的数据类型和长度。
对于数值变量还指定精度和小数位数。
对于XML类型的变量,可以指定一个可选的架构集合。
将值设置为NULL。
如:
DECLARE@MyCounterint
第一次声明变量时,其值设置为NULL。
若要为变量赋值,请使用SET语句。
这是为变量赋值的首选方法。
也可以通过SELECT语句的选择列表中当前所引用值为变量赋值。
参数用于在存储过程和函数以及调用存储过程或函数的应用程序或工具之间交换数据:
输入参数允许调用方将数据值传递到存储过程或函数。
输出参数允许存储过程将数据值或游标变量传递回调用方。
用户定义函数不能指定输出参数。
每个存储过程向调用方返回一个整数返回代码。
如果存储过程没有显式设置返回代码的值,则返回代码为0。
二、流程控制语句
1、BEGIN和END语句
BEGIN和END语句用于将多个Transact-SQL语句组合为一个逻辑块。
在控制流语句必须执行包含两条或多条Transact-SQL语句的语句块的任何地方,都可以使用BEGIN和END语句。
如:
IF(@@ERROR<>0)
BEGIN
SET@ErrorSaveVariable=@@ERROR
PRINT'Errorencountered,'+
CAST(@ErrorSaveVariableASVARCHAR(10))
END
2、GOTO语句
GOTO语句使Transact-SQL批处理的执行跳至标签。
不执行GOTO语句和标签之间的语句。
IF(1=1)
GOTOcalculate_salary
print'goon'--条件成立则跳过此句。
calculate_salary:
print'goto'
3、IF...ELSE语句
IF语句用于条件的测试。
得到的控制流取决于是否指定了可选的ELSE语句:
if(1=1)
print1
elseif(2=2)
print2
elseif(3=3)
print3
else
print0
4、RETURN语句
RETURN语句无条件终止查询、存储过程或批处理。
存储过程或批处理中RETURN语句后面的语句都不执行。
当在存储过程中使用RETURN语句时,此语句可以指定返回给调用应用程序、批处理或过程的整数值。
如果RETURN未指定值,则存储过程返回0
5、WAITFOR语句
WAITFOR语句挂起批处理、存储过程或事务的执行,直到发生以下情况:
已超过指定的时间间隔。
到达一天中指定的时间。
指定的RECEIVE语句至少修改一行或并将其返回到ServiceBroker队列。
WAITFOR语句由下列子句之一指定:
DELAY关键字后为time_to_pass,是指完成WAITFOR语句之前等待的时间。
完成WAITFOR语句之前等待的时间最多为24小时。
如:
WAITFORDELAY'00:
00:
02'
SELECTEmployeeIDFROM Employee;
TIME关键字后为time_to_execute,指定WAITFOR语句完成所用的时间。
GO
BEGIN
WAITFORTIME'22:
00';
DBCCCHECKALLOC;
END;
GO
RECEIVE语句子句,从ServiceBroker队列检索一条或多条消息。
使用RECEIVE语句指定WAITFOR时,如果当前未显示任何消息,该语句将等待消息到达队列。
TIMEOUT关键字后为timeout,指定ServiceBroker等待消息到达队列的时间长度(毫秒)。
可以在RECEIVE语句或GETCONVERSATIONGROUP语句中指定TIMEOUT。
6、WHILE...BREAK或CONTINUE语句
只要指定的条件为True时,WHILE语句就会重复语句或语句块。
REAK或CONTINUE语句通常和WHILE一起使用。
BREAK语句退出最内层的WHILE循环,CONTINUE语句则重新开始WHILE循环。
go
declare@Numint
declare@IDint
declare@iint
set@i=1
while(exists(select*fromTwhereNum<5 ))--获取数量小于5的记录
begin
select@Num=Num,@ID=IDfromTwhereNum<5orderbyIDdesc
printStr(@i)+'编号:
'+Str(@ID)+'值'+str(@Num)
updateTsetNum=Num*2whereID=@ID
set@i=@i+1
if(@i>3)
break--退出循环
end
7、CASE语句
CASE函数用于计算多个条件并为每个条件返回单个值。
CASE函数通常的用途是将代码或缩写替换为可读性更强的值
--用法一:
selectID,
Grade=CaseNum
when1then'不及格'
when2then'不及格'
when3then'不及格'
when4then'良好'
else'优秀'
end
fromT
---用法二:
selectID,
Grade=Case
when Num<3then'不及格'
when Num=3then'及格'
when Num=4then'良好'
when Num>4then'优秀'
end
fromT
三、运行时生成语句
Transact-SQL支持使用下列两种方法于运行时在TTransact-SQL脚本、存储过程和触发器中生成SQL语句:
使用sp_executesql系统存储过程执行Unicode字符串。
sp_executesql支持与RAISERROR语句类似的参数替换。
使用EXECUTE语句执行字符串。
EXECUTE语句不支持已执行字符串中的参数替换。
四、处理数据库引擎错误
在Transact-SQL中有两种方式可以获取错误信息:
1、在TRY...CATCH构造的CATCH块的作用域内,您可以使用以下系统函数:
ERROR_LINE(),返回出现错误的行号。
ERROR_MESSAGE(),返回将返回给应用程序的消息文本。
该文本包括为所有可替换参数提供的值,如长度、对象名或时间。
ERROR_NUMBER()返回错误号。
ERROR_PROCEDURE(),返回出现错误的存储过程或触发器的名称。
如果在存储过程或触发器中未出现错误,该函数返回NULL。
ERROR_SEVERITY()返回严重性。
ERROR_STATE(),返回状态。
2、在执行任何Transact-SQL语句之后,您可以立即使用@@ERROR函数测试错误并检索错误号。
RAISERROR
RAISERROR用于将与SQLServerDatabaseEngine生成的系统错误或警告消息使用相同格式的消息返回到应用程序中。
3、PRINT
PRINT语句用于将消息返回到应用程序。
PRINT采用字符或Unicode字符串表达式作为参数,并将字符串作为消息返回到应用程序。
1使用不带参数的存储过程
使用JDBC驱动程序调用不带参数的存储过程时,必须使用callSQL转义序列。
不带参数的call转义序列的语法如下所示:
{callprocedure-name}
作为实例,在SQLServer2005AdventureWorks示例数据库中创建以下存储过程:
CREATEPROCEDUREGetContactFormalNames
AS
BEGIN
SELECTTOP10Title+''+FirstName+''+LastNameASFormalName
FROMPerson.Contact
END此存储过程返回单个结果集,其中包含一列数据(由Person.Contact表中前十个联系人的称呼、名称和姓氏组成)。
在下面的实例中,将向此函数传递AdventureWorks示例数据库的打开连接,然后使用executeQuery方法调用GetContactFormalNames存储过程。
publicstaticvoidexecuteSprocNoParams(Connectioncon)...{
try...{
Statementstmt=con.createStatement();
ResultSetrs=stmt.executeQuery("{calldbo.GetContactFormalNames}");
while(rs.next())...{
System.out.println(rs.getString("FormalName"));
}
rs.close();
stmt.close();
}
catch(Exceptione)...{
e.printStackTrace();
}
}2使用带有输入参数的存储过程
使用JDBC驱动程序调用带参数的存储过程时,必须结合SQLServerConnection类的prepareCall方法使用callSQL转义序列。
带有IN参数的call转义序列的语法如下所示:
{callprocedure-name[([parameter][,[parameter]]...)]}
构造call转义序列时,请使用?
(问号)字符来指定IN参数。
此字符充当要传递给该存储过程的参数值的占位符。
可以使用SQLServerPreparedStatement类的setter方法之一为参数指定值。
可使用的setter方法由IN参数的数据类型决定。
向setter方法传递值时,不仅需要指定要在参数中使用的实际值,还必须指定参数在存储过程中的序数位置。
例如,如果存储过程包含单个IN参数,则其序数值为1。
如果存储过程包含两个参数,则第一个序数值为1,第二个序数值为2。
作为如何调用包含IN参数的存储过程的实例,使用SQLServer2005AdventureWorks示例数据库中的uspGetEmployeeManagers存储过程。
此存储过程接受名为EmployeeID的单个输入参数(它是一个整数值),然后基于指定的EmployeeID返回雇员及其经理的递归列表。
下面是调用此存储过程的Java代码:
publicstaticvoidexecuteSprocInParams(Connectioncon)...{
try...{
PreparedStatementpstmt=con.prepareStatement("{calldbo.uspGetEmployeeManagers(?
)}");
pstmt.setInt(1,50);
ResultSetrs=pstmt.executeQuery();
while(rs.next())...{
System.out.println("EMPLOYEE:
");
System.out.println(rs.getString("LastName")+","+rs.getString("FirstName"));
System.out.println("MANAGER:
");
System.out.println(rs.getString("ManagerLastName")+","+rs.getString("ManagerFirstName"));
System.out.println();
}
rs.close();
pstmt.close();
}
catch(Exceptione)...{
e.printStackTrace();
}
}3使用带有输出参数的存储过程
使用JDBC驱动程序调用此类存储过程时,必须结合SQLServerConnection类的prepareCall方法使用callSQL转义序列。
带有OUT参数的call转义序列的语法如下所示:
{callprocedure-name[([parameter][,[parameter]]...)]}
构造call转义序列时,请使用?
(问号)字符来指定OUT参数。
此字符充当要从该存储过程返回的参数值的占位符。
要为OUT参数指定值,必须在运行存储过程前使用SQLServerCallableStatement类的registerOutParameter方法指定各参数的数据类型。
使用registerOutParameter方法为OUT参数指定的值必须是java.sql.Types所包含的JDBC数据类型之一,而它又被映射成本地SQLServer数据类型之一。
有关JDBC和SQLServer数据类型的详细信息,请参阅了解JDBC驱动程序数据类型。
当您对于OUT参数向registerOutParameter方法传递一个值时,不仅必须指定要用于此参数的数据类型,而且必须在存储过程中指定此参数的序号位置或此参数的名称。
例如,如果存储过程包含单个OUT参数,则其序数值为1;如果存储过程包含两个参数,则第一个序数值为1,第二个序数值为2。
作为实例,在SQLServer2005AdventureWorks示例数据库中创建以下存储过程:
根据指定的整数IN参数(employeeID),该存储过程也返回单个整数OUT参数(managerID)。
根据HumanResources.Employee表中包含的EmployeeID,OUT参数中返回的值为ManagerID。
在下面的实例中,将向此函数传递AdventureWorks示例数据库的打开连接,然后使用execute方法调用GetImmediateManager存储过程:
publicstaticvoidexecuteStoredProcedure(Connectioncon)...{
try...{
CallableStatementcstmt=con.prepareCall("{calldbo.GetImmediateManager(?
?
)}");
cstmt.setInt(1,5);
cstmt.registerOutParameter(2,java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGERID:
"+cstmt.getInt
(2));
}
catch(Exceptione)...{
e.printStackTrace();
}
}本示例使用序号位置来标识参数。
或者,也可以使用参数的名称(而非其序号位置)来标识此参数。
下面的代码示例修改了上一个示例,以说明如何在Java应用程序中使用命名参数。
请注意,这些参数名称对应于存储过程的定义中的参数名称:
CREATEPROCEDUREGetImmediateManager
@employeeIDINT,
@managerIDINTOUTPUT
AS
BEGIN
SELECT@managerID=ManagerID
FROMHumanResources.Employee
WHEREEmployeeID=@employeeID
END存储过程可能返回更新计数和多个结果集。
Microso
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server 存储过程详解 存储 过程 详解