SQL_Server存储过程学习总结讲解
- 格式：doc
- 大小：182.50 KB
- 文档页数：12
SQL Server数据库：存储过程学习总结一、SQL Server生成唯一值的方法NEWID() -- SQL Server中生成唯一序列值的函数。
SYS_GUID() --Oracle中生成唯一序列值的函数。
二、事务的应用TransactionSQL Server中的Transaction，需显示开启，提交/回滚，且一个Transaction必须要有Commit Transaction/Rollback Transaction。
且Commit/Rollback一定要在return之前。
在存储过程中试用Transaction的示例：IF EXISTS(SELECT*FROM SYSOBJECTS WHERE name='my_sp_test'AND TYPE='P')BEGIN DROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test @i int, @outstr varchar(100)out asbegin trybegin transaction-- 事务开启declare @j int;if @i<10 beginset @outstr ='直接Return,并未Commit或Rollback Transaction.';return;endelse beginset @outstr ='抛出自定义异常,并在异常捕获处Rollback Transaction.';RAISERROR (66666,-- Message id.16,-- Severity,1 -- State,);end;commit transaction;-- 提交事务end trybegin catchif@@ERROR=66666 begin-- 判断是否存在开启的事务，避免如果事务在这之前已提交或者已回滚，再次回滚会抛异常if(@@TRANCOUNT<> 0)beginrollback transaction;-- 事务回滚end;end;return;end catch;go测试存储过程，如下代码：/* 第一个入参= 12,不会产生异常*/DECLARE @OUTSTR_test V ARCHAR(100);exec dbo.my_sp_test12,@OUTSTR_test outprint @OUTSTR_test ;-- @OUTSTR_test = '抛出自定义异常,并在异常捕获处Rollback Transaction.'/* 第一个入参= 8,执行后则会出现异常，异常信息如下行* 'EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。
上一计数= 0，当前计数= 1。
'*/DECLARE @OUTSTR_test_1 V ARCHAR(100);exec dbo.my_sp_test8,@OUTSTR_test_1 outprint @OUTSTR_test_1 ;-- @OUTSTR_test_1 = '直接Return,并未Commit或Rollback Transaction.'/* 入参为8的测试语句执行后，之所以会出现异常，是因为Begin Transaction后，在之后* 的代码中未对这个Transaction进行Commit或者Rollback的操作。
*/三、游标的应用CursorSQL Server中的游标声名后，一定要显示的释放。
若未释放，再次执行时，则会出现“游标XX已经存在”的异常。
Open游标后，一定要显示的Close。
在存储过程中试用Cursor的示例：IF EXISTS(SELECT*FROM SYSOBJECTS WHERE name='my_sp_test'AND TYPE='P')BEGIN DROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test @i int, @outstr varchar(100)out asdeclare @loginName varchar(100);declare cur_user cursor forselect ESUS_LOGIN_NAME from ES_USER where ESUS_ESCO_ID='100004';begin tryopen cur_user;-- 开启游标fetch next from cur_user into @loginName;while@@FETCH_STATUS= 0 beginif(@i >=10)beginset @outstr ='login name: '+@loginName;RAISERROR (66666,-- Message id.16,-- Severity,1 -- State,);end else if (@i<10)beginset @outstr ='login name: '+@loginName;end;fetch next from cur_user into @loginName;end;close cur_user;-- 关闭游标return;end trybegin catchif@@ERROR= 66666 beginclose cur_user;-- 关闭游标deallocate cur_user;-- 释放游标end;return;end catch;go测试存储过程，如下代码：/* 第一个入参= 12,不会产生异常*/DECLARE @OUTSTR_test V ARCHAR(100);exec dbo.my_sp_test12,@OUTSTR_test outprint @OUTSTR_test ;-- @OUTSTR_test = 'login name: ryan'/* 第一个入参= 8,执行第二次后则会出现下行的异常* '名为'cur_user' 的游标已存在。
'*/DECLARE @OUTSTR_test_1 V ARCHAR(100);exec dbo.my_sp_test8,@OUTSTR_test_1 outprint @OUTSTR_test_1 ;-- @OUTSTR_test_1 = 'login name: vicky'/* 入参为8的测试语句执行第二次，之所以会出现异常，是因为没有将游标释放就return了。
所以有使用游标的存储过程，在return之前一定要显示的释放游标。
*/四、自定义异常的试用RaisError在使用SQL Server存储过程或者触发器时，通常会使用自定义异常来处理一些特殊逻辑。
例如游标的销毁，事务的回滚。
接下来将会详细的介绍SQL Server自定义异常的使用。
使用“raiserror”来抛出自定义异常。
如下代码：在存储过程中，抛出自定义异常，然后在catch块中捕获自定义异常。
IF EXISTS(SELECT*FROM SYSOBJECTS WHERE name='my_sp_test'AND TYPE='P')BEGIN DROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test @i int, @outstr varchar(100)out asbegin trydeclare @j int;if @i<10 beginset @outstr ='system exception.';set @j = 10/0;endelse beginset @j = @i;set @outstr ='customer exception 11111111111111111';RAISERROR (66666,-- Message id.16,-- Severity,1 -- State,);end;end trybegin catchif@@ERROR=66666 beginset @outstr = @outstr +'---------------- customer exception';end;return;end catch;go如上代码，raiserror参数说明：(1). Message id：异常的唯一标识，且这个值会被赋值给SQLServer的系统变量@@Error。
自定义异常的Message Id 建议使用50000以后的，因为50000以内的会被系统异常占用。
(2). Severity：异常的级别。
可输入1—19的数值。
1—10之间不会被catch捕获。
19以后是非常严重的级别。
(3). State ：如果输入负值或大于255 的值会生成错误，产生错误则会中断数据库的连接。
执行该存储过程，看看自定义异常是否成功捕获：DECLARE @OUTSTR11 V ARCHAR(100);exec dbo.my_sp_test12,@OUTSTR11 outprint @OUTSTR11;五、Java调用SQL Server存储过程1、JDBC方式调用Java代码：public class invokeSP {public static void main(String[] args) {String driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver";String url="jdbc:sqlserver://192.168.0.3:1433;DatabaseName=CTU_WMS";String username="CTU_WMS_user";String password="ctuwms";Connection cn = null;try {Class.forName(driverClassName);cn = DriverManager.getConnection(url,username,password);// 返回單個結果的存儲過程// outputProcedure(cn);// 返回结果集的存储过程resultProcedure(cn);} catch (Exception e) {e.printStackTrace();}finally{try {cn.close();} catch (SQLException e) {e.printStackTrace();}}}/***调用返回单个结果集的存储过程*/public static void resultProcedure(Connection conn){String sql = "{call my_sp_test(?,?,?)}";CallableStatement cstmt = null;ResultSet rs = null;try {cstmt = conn.prepareCall(sql);cstmt.setInt(1, 12);cstmt.setString(2, "");cstmt.setString(3, "hello vicky");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 (SQLException e) {e.printStackTrace();}finally{try {rs.close();cstmt.close();} catch (SQLException e) {e.printStackTrace();}}}/***调用只返回单个字段的存储过程*/public static void outputProcedure(Connection conn){String sql = "{call my_sp_test(?,?,?)}";CallableStatement cstmt = 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 (SQLException e) {e.printStackTrace();}finally{try {cstmt.close();} catch (SQLException e) {e.printStackTrace();}}}}SQL存储过程代码：IF EXISTS(SELECT*FROM SYSOBJECTS WHERE name='my_sp_test'AND TYPE='P')BEGIN DROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test @i int, @outstr varchar(100)out,@returnCode varchar(100)out asbegin trybegin trandeclare @j int;set @returnCode = @returnCode +';OUT PUT 参数测试';if @i<10 beginset @outstr ='system exception.';set @j = 10/0;--rollback tran;--return;endelse beginbegin tryif @i>=20 beginset @outstr ='customer exception 2222222222222222';set @j = 10/0;endelse beginset @j = @i;set @outstr ='customer exception 11111111111111111';select*from CD_BILL_NO_RULE where CREATOR ='150'AND REC_VER =0;--select * from ES_USER where ESUS_ESCO_ID = 100;RAISERROR (66666,-- Message id.16,-- Severity,1 -- State,);end;end trybegin catchraiserror(66666,16,1);set @outstr ='customer exception 333333333333333333';end catch;end;commit tran;end trybegin catchif@@ERROR=66666 beginset @outstr = @outstr +'---------------- customer exception';end;rollback tran;return;end catch;go2、Spring方式调用使用Spring调用SQLServer存储过程与Oracle存储过程的差别:1、调用一个返回结果集的存储过程SQL Server：super.declareParameter(new SqlReturnResultSet(name,ParameterizedBeanPropertyRowMapper.newInstance(entityTypeClass)));Oracle：super.declareParameter(new SqlOutParameter(name,OracleTypes.CURSOR,ParameterizedBeanPropertyRowMapper.newInstance(entityTypeClass));注：(1). SQL Server定义返回结果集的存储过程，只需在存储过程中执行一个查询语句即可。
