SQL_Server存储过程学习总结

1、SQL Server数据库：存储过程学习总结1、 SQL Server生成唯一值的方法NEWID() - SQL Server中生成唯一序列值的函数。SYS_GUID() -Oracle中生成唯一序列值的函数。2、 事务的应用TransactionSQL Server中的Transaction，需显示开启，提交/回滚，且一个Transaction必须要有Commit Transaction/Rollback Transaction。且Commit/Rollback一定要在return之前。在存储过程中试用Transaction的示例：IF EXISTS (SELECT * FROM SYSOB

2、JECTS WHERE name=my_sp_test AND TYPE=P) BEGINDROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test i int, outstr varchar(100) out asbegin try begin transaction - 事务开启declare j int;if i10 begin set outstr = 直接Return,并未Commit或Rollback Transaction.; return;endelse begin set outstr = 抛出自定义异常,并在异常捕

3、获处Rollback Transaction.;RAISERROR (66666, - Message id. 16, - Severity, 1 - State, ) ; end;commit transaction; - 提交事务end trybegin catchif ERROR=66666 begin- 判断是否存在开启的事务，避免如果事务在这之前已提交或者已回滚，再次回滚会抛异常 if(TRANCOUNT 0) begin rollback transaction; - 事务回滚 end;end;return;end catch;go测试存储过程，如下代码：/* 第一个入参= 12,

4、不会产生异常 */DECLARE OUTSTR_test VARCHAR(100);exec dbo.my_sp_test 12,OUTSTR_test outprint OUTSTR_test ; - OUTSTR_test = 抛出自定义异常,并在异常捕获处Rollback Transaction./* 第一个入参= 8,执行后则会出现异常，异常信息如下行 * EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。 */DECLARE OUTSTR_test_1 VARCHAR(100);exec dbo.my_sp_test 8,O

5、UTSTR_test_1 outprint OUTSTR_test_1 ; - OUTSTR_test_1 = 直接Return,并未Commit或Rollback Transaction./* 入参为8的测试语句执行后，之所以会出现异常，是因为Begin Transaction后，在之后 * 的代码中未对这个Transaction进行Commit或者Rollback的操作。 */3、 游标的应用CursorSQL Server中的游标声名后，一定要显示的释放。若未释放，再次执行时，则会出现“游标XX已经存在”的异常。Open游标后，一定要显示的Close。在存储过程中试用Cursor的示例：

6、IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name=my_sp_test AND TYPE=P) BEGINDROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test i int, outstr varchar(100) out asdeclare loginName varchar(100);declare cur_user cursor for select ESUS_LOGIN_NAME from ES_USER where ESUS_ESCO_ID=;begin try open cu

7、r_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 (i10) beginset outstr = login name: +loginName;end;fetch next from cur_user into loginName;

8、 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 VARCHAR(100);exec dbo.my_sp_test 12,OUTSTR_test outprint OUTSTR_test ; - OUTSTR_test = login name: r

9、yan/* 第一个入参= 8,执行第二次后则会出现下行的异常 * 名为cur_user 的游标已存在。 */DECLARE OUTSTR_test_1 VARCHAR(100);exec dbo.my_sp_test 8,OUTSTR_test_1 outprint OUTSTR_test_1 ; - OUTSTR_test_1 = login name: vicky/* 入参为8的测试语句执行第二次，之所以会出现异常，是因为没有将游标释放就return了。所以有使用游标的存储过程，在return之前一定要显示的释放游标。 */4、 自定义异常的试用RaisError在使用SQL Server

10、存储过程或者触发器时，通常会使用自定义异常来处理一些特殊逻辑。例如游标的销毁，事务的回滚。接下来将会详细的介绍SQL Server自定义异常的使用。 使用“raiserror”来抛出自定义异常。如下代码：在存储过程中，抛出自定义异常，然后在catch块中捕获自定义异常。IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name=my_sp_test AND TYPE=P) BEGINDROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test i int, outstr varchar(100) out

11、 asbegin trydeclare j int;if i10 begin set outstr = system exception.; set j = 10/0;endelse beginset j = i;set outstr = customer exception ;RAISERROR (66666, - Message id. 16, - Severity, 1 - State, ) ; end;end trybegin catchif ERROR=66666 begin set outstr = outstr + - customer exception;end;return;

12、end catch;go如上代码，raiserror参数说明：(1) . Message id：异常的唯一标识，且这个值会被赋值给SQLServer的系统变量Error。自定义异常的Message Id建议使用50000以后的，因为50000以内的会被系统异常占用。(2) . Severity：异常的级别。 可输入119的数值。110之间不会被catch捕获。19以后是非常严重的级别。(3) . State ： 如果输入负值或大于255 的值会生成错误，产生错误则会中断数据库的连接。执行该存储过程，看看自定义异常是否成功捕获：DECLARE OUTSTR11 VARCHAR(100);exec

13、 dbo.my_sp_test 12,OUTSTR11 outprint OUTSTR11;5、 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:/:1433;DatabaseName=CTU_WMS;String username=CTU_W

14、MS_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();finallytry cn.close(); catch (SQLException e) e.pr

15、intStackTrace();/* * 调用返回单个结果集的存储过程 */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.registerOut

16、Parameter(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(return

17、Code); catch (SQLException e) e.printStackTrace();finallytry 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(

18、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); cat

19、ch (SQLException e) e.printStackTrace();finallytry cstmt.close(); catch (SQLException e) e.printStackTrace();SQL存储过程代码：IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name=my_sp_test AND TYPE=P) BEGINDROP PROCEDURE my_sp_test;END;GOcreate procedure my_sp_test i int, outstr varchar(100) out,returnCode varc

20、har(100) out asbegin try begin trandeclare j int;set returnCode = returnCode +;OUT PUT 参数测试;if i=20 beginset outstr = customer exception 22222;set j = 10/0; end else beginset j = i; set outstr = customer exception ; select * from CD_BILL_NO_RULE where CREATOR = 150 AND REC_VER =0; -select * from ES_

21、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 ; end catch; end;commit tran;end trybegin catchif ERROR=66666 begin set outstr = outstr + - customer exception;end;rollback tran

22、;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,Parameter

23、izedBeanPropertyRowMapper.newInstance(entityTypeClass); 注：(1). SQL Server定义返回结果集的存储过程，只需在存储过程中执行一个查询语句即可。但是这个查询语句必 须在transaction中。 (2). SQL Server存储过程返回的结果集因为没有定义具体的变量名，所以我们在获取结果集的时候，直接写“default”即可。 sp.addResultSetParameter(default, CdBillNoRuleModel.class); Map result = sp.execute(); List results =

24、 (List)result.get(default); (3). 如果存储过程中有返回多个结果集，则不能直接用“default”来获取结果集，而是用“result-set-*”。sp.addResultSetParameter(result-set-1, CdBillNoRuleModel.class);sp.addResultSetParameter(result-set-2, EsUserModel.class);Map result = sp.execute();List results = (List)result.get(result-set-1);List users = (Lis

25、t)result.get(result-set-2);Java代码 测试类代码：public class SpringInvokeSP public static void main(String args) String driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver;String url=jdbc:sqlserver:/:1433;DatabaseName=CTU_WMS;String username=CTU_WMS_user;String password=ctuwms;/ 创建dataso

26、urceDriverManagerDataSource 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 SQLSto

27、redProcedure(ds,my_sp_test);sp.addParameter(i, 11);sp.addOutStringParameter(outstr);sp.addInOutParameter(returnCode, Hello vicky);sp.addOutDateParameter(date);sp.addOutDateParameter(datetime);sp.addResultSetParameter(result-set-1, CdBillNoRuleModel.class);sp.addResultSetParameter(result-set-2, EsUse

28、rModel.class);Map result = sp.execute();String resultStr = (String)result.get(outstr);String returnCode = (String)result.get(returnCode);System.out.println(resultStr);System.out.println(returnCode);System.out.println(Date)result.get(date);System.out.println(Date)result.get(datetime);List results = (

29、List) result.get(result-set-1);if(null != results)try for(CdBillNoRuleModel model : results)System.out.println(model.getCdbrDesc(); catch (Exception e) e.printStackTrace();List users = (List)result.get(result-set-2);/if(users != null)for(EsUserModel user:users)System.out.println(user.getEsusLoginNam

30、e();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);para

31、meters.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

32、);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

33、 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 v

34、oid 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) su

35、per.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 addRe

36、sultSetParameter(String name,Class entityTypeClass)super.declareParameter(new SqlReturnResultSet(name,ParameterizedBeanPropertyRowMapper.newInstance(entityTypeClass);public Map execute() return super.execute(parameters);Overridepublic Map execute(Map inParams) throws DataAccessException Map allParams = new HashMap();allParams