SQLServer存储过程学习总结.docx
- 文档编号:9914962
- 上传时间:2023-02-07
- 格式:DOCX
- 页数:19
- 大小:21.57KB
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);
Map
List
result.get("default");
(3).如果存储过程中有返回多个结果集,则不能直接用“default”来获取结果集,而是用“result-set-*”。
sp.addResultSetParameter("result-set-1",CdBillNoRuleModel.class);
sp.addResultSetParameter("result-set-2",EsUserModel.class);
Map
List
result.get("result-set-1");
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);
Map
StringresultStr=(String)result.get("outstr");
StringreturnCode=(Strin
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 存储 过程 学习 总结