SQLServer存储过程学习总结.docx
《SQLServer存储过程学习总结.docx》由会员分享，可在线阅读，更多相关《SQLServer存储过程学习总结.docx（19页珍藏版）》请在冰豆网上搜索。
SQLServer存储过程学习总结
SQLServer数据库：
存储过程学习总结
1、SQLServer生成唯一值的方法
NEWID（）--SQLServer中生成唯一序列值的函数。
SYS_GUID（）--Oracle中生成唯一序列值的函数。
2、事务的应用Transaction
SQLServer中的Transaction，需显示开启，提交/回滚，且一个Transaction必须要有CommitTransaction/RollbackTransaction。
且Commit/Rollback一定要在return之前。
在存储过程中试用Transaction的示例：
IFEXISTS（SELECT*FROMSYSOBJECTSWHEREname='my_sp_test'ANDTYPE='P'）BEGIN
DROPPROCEDUREmy_sp_test;
END;
GO
createproceduremy_sp_test@iint,@outstrvarchar（100）outas
begintry
begintransaction--事务开启
declare@jint;
if@i<10begin
set@outstr='直接Return,并未Commit或RollbackTransaction.';
return;
end
elsebegin
set@outstr='抛出自定义异常,并在异常捕获处RollbackTransaction.';
RAISERROR（66666,--Messageid.
16,--Severity,
1--State,
）;
end;
committransaction;--提交事务
endtry
begincatch
if@@ERROR=66666begin
--判断是否存在开启的事务，避免如果事务在这之前已提交或者已回滚，再次回滚会抛异常
if（@@TRANCOUNT<>0）begin
rollbacktransaction;--事务回滚
end;
end;
return;
endcatch;
go
测试存储过程，如下代码：
/*第一个入参=12,不会产生异常
*/
DECLARE@OUTSTR_testVARCHAR（100）;
execdbo.my_sp_test12,@OUTSTR_testout
print@OUTSTR_test;
--@OUTSTR_test='抛出自定义异常,并在异常捕获处RollbackTransaction.'
/*第一个入参=8,执行后则会出现异常，异常信息如下行
*'EXECUTE后的事务计数指示BEGIN和COMMIT语句的数目不匹配。
上一计数=0，当前计数=1。
'
*/
DECLARE@OUTSTR_test_1VARCHAR（100）;
execdbo.my_sp_test8,@OUTSTR_test_1out
print@OUTSTR_test_1;
--@OUTSTR_test_1='直接Return,并未Commit或RollbackTransaction.'
/*入参为8的测试语句执行后，之所以会出现异常，是因为BeginTransaction后，在之后
*的代码中未对这个Transaction进行Commit或者Rollback的操作。
*/
3、游标的应用Cursor
SQLServer中的游标声名后，一定要显示的释放。
若未释放，再次执行时，则会出现“游标XX已经存在”的异常。
Open游标后，一定要显示的Close。
在存储过程中试用Cursor的示例：
IFEXISTS（SELECT*FROMSYSOBJECTSWHEREname='my_sp_test'ANDTYPE='P'）BEGIN
DROPPROCEDUREmy_sp_test;
END;
GO
createproceduremy_sp_test@iint,@outstrvarchar（100）outas
declare@loginNamevarchar（100）;
declarecur_usercursorfor
selectESUS_LOGIN_NAMEfromES_USERwhereESUS_ESCO_ID='100004';
begintry
opencur_user;--开启游标
fetchnextfromcur_userinto@loginName;
while@@FETCH_STATUS=0begin
if（@i>=10）begin
set@outstr='loginname:
'+@loginName;
RAISERROR（66666,--Messageid.
16,--Severity,
1--State,
）;
endelseif（@i<10）begin
set@outstr='loginname:
'+@loginName;
end;
fetchnextfromcur_userinto@loginName;
end;
closecur_user;--关闭游标
return;
endtry
begincatch
if@@ERROR=66666begin
closecur_user;--关闭游标
deallocatecur_user;--释放游标
end;
return;
endcatch;
go
测试存储过程，如下代码：
/*第一个入参=12,不会产生异常
*/
DECLARE@OUTSTR_testVARCHAR（100）;
execdbo.my_sp_test12,@OUTSTR_testout
print@OUTSTR_test;
--@OUTSTR_test='loginname:
ryan'
/*第一个入参=8,执行第二次后则会出现下行的异常
*'名为'cur_user'的游标已存在。
'
*/
DECLARE@OUTSTR_test_1VARCHAR（100）;
execdbo.my_sp_test8,@OUTSTR_test_1out
print@OUTSTR_test_1;
--@OUTSTR_test_1='loginname:
vicky'
/*入参为8的测试语句执行第二次，之所以会出现异常，是因为没有将游标释放就return了。
所以有使用游标的存储过程，在return之前一定要显示的释放游标。
*/
4、自定义异常的试用RaisError
在使用SQLServer存储过程或者触发器时，通常会使用自定义异常来处理一些特殊逻辑。
例如游标的销毁，事务的回滚。
接下来将会详细的介绍SQLServer自定义异常的使用。
使用“raiserror”来抛出自定义异常。
如下代码：
在存储过程中，抛出自定义异常，然后在catch块中捕获自定义异常。
IFEXISTS（SELECT*FROMSYSOBJECTSWHEREname='my_sp_test'ANDTYPE='P'）BEGIN
DROPPROCEDUREmy_sp_test;
END;
GO
createproceduremy_sp_test@iint,@outstrvarchar（100）outas
begintry
declare@jint;
if@i<10begin
set@outstr='systemexception.';
set@j=10/0;
end
elsebegin
set@j=@i;
set@outstr='customerexception11111111111111111';
RAISERROR（66666,--Messageid.
16,--Severity,
1--State,
）;
end;
endtry
begincatch
if@@ERROR=66666begin
set@outstr=@outstr+'----------------customerexception';
end;
return;
endcatch;
go
如上代码，raiserror参数说明：
（1）.Messageid：
异常的唯一标识，且这个值会被赋值给SQLServer的系统变量@@Error。
自定义异常的MessageId建议使用50000以后的，因为50000以内的会被系统异常占用。
（2）.Severity：
异常的级别。
可输入1—19的数值。
1—10之间不会被catch捕获。
19以后是非常严重的级别。
（3）.State：
如果输入负值或大于255的值会生成错误，产生错误则会中断数据库的连接。
执行该存储过程，看看自定义异常是否成功捕获：
DECLARE@OUTSTR11VARCHAR（100）;
execdbo.my_sp_test12,@OUTSTR11out
print@OUTSTR11;
5、Java调用SQLServer存储过程
1、JDBC方式调用
Java代码：
publicclassinvokeSP{
publicstaticvoidmain（String[]args）{
StringdriverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
Stringurl="jdbc:
sqlserver:
//192.168.0.3:
1433;DatabaseName=CTU_WMS";
Stringusername="CTU_WMS_user";
Stringpassword="ctuwms";
Connectioncn=null;
try{
Class.forName（driverClassName）;
cn=DriverManager.getConnection（url,username,password）;
//返回單個結果的存儲過程
//outputProcedure（cn）;
//返回结果集的存储过程
resultProcedure（cn）;
}catch（Exceptione）{
e.printStackTrace（）;
}finally{
try{
cn.close（）;
}catch（SQLExceptione）{
e.printStackTrace（）;
}
}
}
/**
*调用返回单个结果集的存储过程
*/
publicstaticvoidresultProcedure（Connectionconn）{
Stringsql="{callmy_sp_test（?
?
?
）}";
CallableStatementcstmt=null;
ResultSetrs=null;
try{
cstmt=conn.prepareCall（sql）;
cstmt.setInt（1,12）;
cstmt.setString（2,""）;
cstmt.setString（3,"hellovicky"）;
cstmt.registerOutParameter（"outstr",java.sql.Types.VARCHAR）;
cstmt.registerOutParameter（"returnCode",java.sql.Types.VARCHAR）;
rs=cstmt.executeQuery（）;
while（rs.next（））{
System.out.println（"--------"+rs.getString（"CDBR_NAME"））;
}
System.out.println（"outstr========"+cstmt.getString（"outstr"））;
System.out.println（"returnCode==="+cstmt.getString（"returnCode"））;
}catch（SQLExceptione）{
e.printStackTrace（）;
}finally{
try{
rs.close（）;
cstmt.close（）;
}catch（SQLExceptione）{
e.printStackTrace（）;
}
}
}
/**
*调用只返回单个字段的存储过程
*/
publicstaticvoidoutputProcedure（Connectionconn）{
Stringsql="{callmy_sp_test（?
?
?
）}";
CallableStatementcstmt=null;
try{
cstmt=conn.prepareCall（sql）;
cstmt.setInt（1,12）;
cstmt.setString（2,""）;
cstmt.setString（3,""）;
cstmt.registerOutParameter（"outstr",java.sql.Types.VARCHAR）;
cstmt.registerOutParameter（3,java.sql.Types.VARCHAR）;
cstmt.execute（）;
System.out.println（"outstr:
"+cstmt.getString
（2））;
System.out.println（"returnCode:
"+cstmt.getString（3））;
}catch（SQLExceptione）{
e.printStackTrace（）;
}finally{
try{
cstmt.close（）;
}catch（SQLExceptione）{
e.printStackTrace（）;
}
}
}
}
SQL存储过程代码：
IFEXISTS（SELECT*FROMSYSOBJECTSWHEREname='my_sp_test'ANDTYPE='P'）BEGIN
DROPPROCEDUREmy_sp_test;
END;
GO
createproceduremy_sp_test@iint,@outstrvarchar（100）out,@returnCodevarchar（100）outas
begintry
begintran
declare@jint;
set@returnCode=@returnCode+';OUTPUT参数测试';
if@i<10begin
set@outstr='systemexception.';
set@j=10/0;
--rollbacktran;
--return;
end
elsebegin
begintry
if@i>=20begin
set@outstr='customerexception2222222222222222';
set@j=10/0;
end
elsebegin
set@j=@i;
set@outstr='customerexception11111111111111111';
select*fromCD_BILL_NO_RULEwhereCREATOR='150'ANDREC_VER=0;
--select*fromES_USERwhereESUS_ESCO_ID=100;
RAISERROR（66666,--Messageid.
16,--Severity,
1--State,
）;
end;
endtry
begincatch
raiserror（66666,16,1）;
set@outstr='customerexception333333333333333333';
endcatch;
end;
committran;
endtry
begincatch
if@@ERROR=66666begin
set@outstr=@outstr+'----------------customerexception';
end;
rollbacktran;
return;
endcatch;
go
2、Spring方式调用
使用Spring调用SQLServer存储过程与Oracle存储过程的差别:
1、调用一个返回结果集的存储过程
SQLServer：
super.declareParameter（newSqlReturnResultSet（name,
ParameterizedBeanPropertyRowMapper.newInstance（entityTypeClass）））;
Oracle：
super.declareParameter（newSqlOutParameter（name,OracleTypes.CURSOR,
ParameterizedBeanPropertyRowMapper.newInstance（entityTypeClass））;
注：
（1）.SQLServer定义返回结果集的存储过程，只需在存储过程中执行一个查询语句即可。
但是这个查询语句必须在transaction中。
（2）.SQLServer存储过程返回的结果集因为没有定义具体的变量名，所以我们在获取结果集的时候，直接写“default”即可。
sp.addResultSetParameter（"default",CdBillNoRuleModel.class）;
Mapresult=sp.execute（）;
Listresults=（List）
result.get（"default"）;
（3）.如果存储过程中有返回多个结果集，则不能直接用“default”来获取结果集，而是用“result-set-*”。
sp.addResultSetParameter（"result-set-1",CdBillNoRuleModel.class）;
sp.addResultSetParameter（"result-set-2",EsUserModel.class）;
Mapresult=sp.execute（）;
Listresults=（List）
result.get（"result-set-1"）;
Listusers=（List）
result.get（"result-set-2"）;
Java代码——测试类代码：
publicclassSpringInvokeSP{
publicstaticvoidmain（String[]args）{
StringdriverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
Stringurl="jdbc:
sqlserver:
//192.168.0.3:
1433;DatabaseName=CTU_WMS";
Stringusername="CTU_WMS_user";
Stringpassword="ctuwms";
//创建datasource
DriverManagerDataSourceds=newDriverManagerDataSource（）;
ds.setDriverClassName（driverClassName）;
ds.setUrl（url）;
ds.setUsername（username）;
ds.setPassword（password）;
resultSP（ds）;
}
/**
*返回多个结果集的存储过程
*@paramds
*/
publicstaticvoidresultSP（DriverManagerDataSourceds）{
SQLStoredProceduresp=newSQLStoredProcedure（ds,"my_sp_test"）;
sp.addParameter（"i",11）;
sp.addOutStringParameter（"outstr"）;
sp.addInOutParameter（"returnCode","Hellovicky"）;
sp.addOutDateParameter（"date"）;
sp.addOutDateParameter（"datetime"）;
sp.addResultSetParameter（"result-set-1",CdBillNoRuleModel.class）;
sp.addResultSetParameter（"result-set-2",EsUserModel.class）;
Mapresult=sp.execute（）;
StringresultStr=（String）result.get（"outstr"）;
StringreturnCode=（Strin
升级会员 