SQL - Server存储过程学习总结讲解

SQL Server数据库：存储过程学习总结

一、SQL Server生成唯一值的方法

NEWID() -- SQL Server中生成唯一序列值的函数。 SYS_GUID() --Oracle中生成唯一序列值的函数。

二、事务的应用Transaction

SQL 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; GO create procedure my_sp_test @i int, @outstr varchar(100) out as begin try begin transaction -- 事务开启 declare @j int; if @i<10 begin set @outstr = '直接Return,并未Commit或Rollback Transaction.'; return; end else begin set @outstr = '抛出自定义异常,并在异常捕获处Rollback Transaction.'; RAISERROR (66666, -- Message id. 16, -- Severity, 1 -- State, ) ; end; commit transaction; -- 提交事务 end try begin catch if @@ERROR=66666 begin -- 判断是否存在开启的事务，避免如果事务在这之前已提交或者已回滚，再次回滚会抛异常 if(@@TRANCOUNT <> 0) begin rollback transaction; -- 事务回滚 end; end; return; end catch; go 测试存储过程，如下代码： /* 第一个入参= 12,不会产生异常 */ DECLARE @OUTSTR_test VARCHAR(100); exec dbo.my_sp_test 12,@OUTSTR_test out print @OUTSTR_test ; -- @OUTSTR_test = '抛出自定义异常,并在异常捕获处Rollback Transaction.' /* 第一个入参= 8,执行后则会出现异常，异常信息如下行 * 'EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。' */ DECLARE @OUTSTR_test_1 VARCHAR(100); exec dbo.my_sp_test 8,@OUTSTR_test_1 out print @OUTSTR_test_1 ; -- @OUTSTR_test_1 = '直接Return,并未Commit或Rollback Transaction.' /* 入参为8的测试语句执行后，之所以会出现异常，是因为Begin Transaction后，在之后 * 的代码中未对这个Transaction进行Commit或者Rollback的操作。 */ 三、游标的应用Cursor

SQL 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; GO create procedure my_sp_test @i int, @outstr varchar(100) out as declare @loginName varchar(100); declare cur_user cursor for select ESUS_LOGIN_NAME from ES_USER where ESUS_ESCO_ID='100004'; begin try open cur_user; -- 开启游标 fetch next from cur_user into @loginName; while @@FETCH_STATUS = 0 begin if(@i >=10) begin set @outstr = 'login name: '+@loginName; RAISERROR (66666, -- Message id. 16, -- Severity, 1 -- State, ) ; end else if (@i<10) begin set @outstr = 'login name: '+@loginName; end; fetch next from cur_user into @loginName; end; close cur_user; -- 关闭游标 return; end try begin catch if @@ERROR = 66666 begin close cur_user; -- 关闭游标 deallocate cur_user;-- 释放游标 end; return; end catch; go 测试存储过程，如下代码： /* 第一个入参= 12,不会产生异常 */ DECLARE @OUTSTR_test VARCHAR(100); exec dbo.my_sp_test 12,@OUTSTR_test out print @OUTSTR_test ; -- @OUTSTR_test = 'login name: ryan' /* 第一个入参= 8,执行第二次后则会出现下行的异常 * '名为'cur_user' 的游标已存在。' */ DECLARE @OUTSTR_test_1 VARCHAR(100); exec dbo.my_sp_test 8,@OUTSTR_test_1 out print @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; GO create procedure my_sp_test @i int, @outstr varchar(100) out as begin try declare @j int; if @i<10 begin set @outstr = 'system exception.'; set @j = 10/0; end else begin set @j = @i; set @outstr = 'customer exception 11111111111111111'; RAISERROR (66666, -- Message id. 16, -- Severity, 1 -- State, ) ; end; end try begin catch if @@ERROR=66666 begin set @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 VARCHAR(100); exec dbo.my_sp_test 12,@OUTSTR11 out print @OUTSTR11; 五、Java调用SQL Server存储过程 1、JDBC方式调用

Java代码： public class invokeSP { public static void main(String[] args) { String driverClassName=\; String url=\; String username=\; String password=\; 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 = \; CallableStatement cstmt = null; ResultSet rs = null; try { cstmt = conn.prepareCall(sql); cstmt.setInt(1, 12); cstmt.setString(2, \); cstmt.setString(3, \); cstmt.registerOutParameter(\, java.sql.Types.VARCHAR); cstmt.registerOutParameter(\, java.sql.Types.VARCHAR); rs = cstmt.executeQuery(); while(rs.next()){ System.out.println(\+rs.getString(\)); } System.out.println(\+cstmt.getString(\)); System.out.println(\+cstmt.getString(\)); } catch (SQLException e) { e.printStackTrace(); }finally{ try { rs.close(); cstmt.close();

} } catch (SQLException e) { e.printStackTrace(); } } } /** * 调用只返回单个字段的存储过程 */ public static void outputProcedure(Connection conn){ String sql = \; CallableStatement cstmt = null; try { cstmt = conn.prepareCall(sql); cstmt.setInt(1, 12); cstmt.setString(2, \); cstmt.setString(3, \); cstmt.registerOutParameter(\, java.sql.Types.VARCHAR); cstmt.registerOutParameter(3, java.sql.Types.VARCHAR); cstmt.execute(); System.out.println(\ + cstmt.getString(2)); System.out.println(\ + 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; GO create procedure my_sp_test @i int, @outstr varchar(100) out,@returnCode varchar(100) out as begin try begin tran declare @j int; set @returnCode = @returnCode +';OUT PUT 参数测试'; if @i<10 begin set @outstr = 'system exception.'; set @j = 10/0; --rollback tran; --return; end else begin begin try if @i>=20 begin set @outstr = 'customer exception 2222222222222222'; set @j = 10/0; end else begin set @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 try begin catch raiserror(66666,16,1); set @outstr = 'customer exception 333333333333333333'; end catch; end; commit tran; end try begin catch if @@ERROR=66666 begin set @outstr = @outstr + '---------------- customer exception'; end; rollback tran; return; end catch; go 2、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定义返回结果集的存储过程，只需在存储过程中执行一个查询语句即可。但是这个查询语句必 须在transaction中。

(2). SQL Server存储过程返回的结果集因为没有定义具体的变量名，所以我们在获取结果集的时候，直接写 “default”即可。

sp.addResultSetParameter(\, CdBillNoRuleModel.class); Map result = sp.execute();

List results = (List)

result.get(\); (3). 如果存储过程中有返回多个结果集，则不能直接用“default”来获取结果集，而是用“result-set-*”。

sp.addResultSetParameter(\, CdBillNoRuleModel.class); sp.addResultSetParameter(\, EsUserModel.class); Map result = sp.execute();

List results = (List)

result.get(\);

List users = (List)

result.get(\); Java代码 —— 测试类代码：

public class SpringInvokeSP { public static void main(String[] args) { String driverClassName=\; String url=\; String username=\; String password=\; // 创建datasource DriverManagerDataSource ds = new DriverManagerDataSource(); ds.setDriverClassName(driverClassName); ds.setUrl(url); ds.setUsername(username); ds.setPassword(password); resultSP(ds); } /** * 返回多个结果集的存储过程 * @param ds */ public static void resultSP(DriverManagerDataSource ds){ SQLStoredProcedure sp = new SQLStoredProcedure(ds,\); sp.addParameter(\, 11); sp.addOutStringParameter(\); sp.addInOutParameter(\, \); sp.addOutDateParameter(\); sp.addOutDateParameter(\); sp.addResultSetParameter(\, CdBillNoRuleModel.class); sp.addResultSetParameter(\, EsUserModel.class); Map result = sp.execute(); String resultStr = (String)result.get(\); String returnCode = (String)result.get(\); System.out.println(resultStr); System.out.println(returnCode); System.out.println((Date)result.get(\)); System.out.println((Date)result.get(\)); List results = (List) result.get(\); if(null != results){ try { for(CdBillNoRuleModel model : results){ System.out.println(model.getCdbrDesc()); } } catch (Exception e) { e.printStackTrace(); } } List users = (List)result.get(\);// if(users != null){ for(EsUserModel user:users){ System.out.println(user.getEsusLoginName()); } } } } Java代码 —— 工具类代码： public class SQLStoredProcedure extends StoredProcedure { private Map parameters = new HashMap(); public SQLStoredProcedure(DataSource ds,String spName){ super(ds,spName); } public void addParameter(String name, String value) { super.declareParameter(new SqlParameter(name, Types.VARCHAR)); parameters.put(name, value); } public void addParameter(String name, int value) { super.declareParameter(new SqlParameter(name, Types.INTEGER)); parameters.put(name, value); } public void addParameter(String name, double value) { super.declareParameter(new SqlParameter(name, Types.DOUBLE)); parameters.put(name, value); } public void addParameter(String name, Date value) { super.declareParameter(new SqlParameter(name, Types.TIMESTAMP)); parameters.put(name, value); } public void addInOutParameter(String name, String value) { super.declareParameter(new SqlInOutParameter(name, Types.VARCHAR)); parameters.put(name, value); } public void addInOutParameter(String name, int value) { super.declareParameter(new SqlInOutParameter(name, Types.INTEGER)); parameters.put(name, value); } public void addInOutParameter(String name, double value) { super.declareParameter(new SqlInOutParameter(name, Types.DOUBLE)); parameters.put(name, value); } public void addInOutParameter(String name, Date value) { super.declareParameter(new SqlInOutParameter(name, Types.TIMESTAMP)); parameters.put(name, value); } public void addOutStringParameter(String name) { super.declareParameter(new SqlOutParameter(name, Types.VARCHAR)); } public void addOutIntParameter(String name) { super.declareParameter(new SqlOutParameter(name, Types.INTEGER)); }

} public void addOutDoubleParameter(String name) { super.declareParameter(new SqlOutParameter(name, Types.DOUBLE)); } public void addOutDateParameter(String name) { super.declareParameter(new SqlOutParameter(name, Types.TIMESTAMP)); } public void addResultSetParameter(String name,Class entityTypeClass){ super.declareParameter(new SqlReturnResultSet(name, ParameterizedBeanPropertyRowMapper.newInstance(entityTypeClass))); } public Map execute() { return super.execute(parameters); } @Override public Map execute(Map inParams) throws DataAccessException { Map allParams = new HashMap(); allParams.putAll(parameters); allParams.putAll(inParams); return super.execute(allParams); } @Override public Map execute(final ParameterMapper inParamMapper) throws DataAccessException { return super.execute(new ParameterMapper() { public Map createMap(Connection con) throws SQLException { Map allParams = new HashMap(); allParams.putAll(parameters); allParams.putAll(inParamMapper.createMap(con)); return allParams; } }); } SQL存储过程代码：

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='my_sp_test' AND TYPE='P') BEGIN DROP PROCEDURE my_sp_test; END; GO create procedure my_sp_test @i int, @outstr varchar(100) out,@returnCode varchar(100) out as begin try begin tran declare @j int; set @returnCode = @returnCode +';OUT PUT 参数测试'; if @i<10 begin set @outstr = 'system exception.'; set @j = 10/0; end else begin begin try if @i>=20 begin set @outstr = 'customer exception 2222222222222222'; set @j = 10/0; end else begin set @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 try begin catch raiserror(66666,16,1); set @outstr = 'customer exception 333333333333333333'; end catch; end; commit tran; end try begin catch if @@ERROR=66666 begin set @outstr = @outstr + '---------------- customer exception'; end; rollback tran; return; end catch; go

本文来源：https://www.bwwdw.com/article/xbsr.html