SQL_Server存储过程学习总结

SQLServer数据库：存储过程学习总结一、SQLServer生成唯一值的方法NEWID()--SQLServer中生成唯一序列值的函数。SYS_GUID()--Oracle中生成唯一序列值的函数。二、事务的应用TransactionSQLServer中的Transaction，需显示开启，提交/回滚，且一个Transaction必须要有CommitTransaction/RollbackTransaction。且Commit/Rollback一定要在return之前。在存储过程中试用Transaction的示例：IFEXISTS(SELECT*FROMSYSOBJECTSWHEREname='my_sp_test'ANDTYPE='P')BEGINDROPPROCEDUREmy_sp_test;END;GOcreateproceduremy_sp_test@iint,@outstrvarchar(100)outasbegintrybegintransaction--事务开启declare@jint;if@i10beginset@outstr='直接Return,并未Commit或RollbackTransaction.';return;endelsebeginset@outstr='抛出自定义异常,并在异常捕获处RollbackTransaction.';RAISERROR(66666,--Messageid.16,--Severity,1--State,);end;committransaction;--提交事务endtrybegincatchif@@ERROR=66666begin--判断是否存在开启的事务，避免如果事务在这之前已提交或者已回滚，再次回滚会抛异常if(@@TRANCOUNT0)beginrollbacktransaction;--事务回滚end;end;return;endcatch;go测试存储过程，如下代码：/*第一个入参=12,不会产生异常*/DECLARE@OUTSTR_testVARCHAR(100);execdbo.my_sp_test12,@OUTSTR_testoutprint@OUTSTR_test;--@OUTSTR_test='抛出自定义异常,并在异常捕获处RollbackTransaction.'/*第一个入参=8,执行后则会出现异常，异常信息如下行*'EXECUTE后的事务计数指示BEGIN和COMMIT语句的数目不匹配。上一计数=0，当前计数=1。'*/DECLARE@OUTSTR_test_1VARCHAR(100);execdbo.my_sp_test8,@OUTSTR_test_1outprint@OUTSTR_test_1;--@OUTSTR_test_1='直接Return,并未Commit或RollbackTransaction.'/*入参为8的测试语句执行后，之所以会出现异常，是因为BeginTransaction后，在之后*的代码中未对这个Transaction进行Commit或者Rollback的操作。*/三、游标的应用CursorSQLServer中的游标声名后，一定要显示的释放。若未释放，再次执行时，则会出现“游标XX已经存在”的异常。Open游标后，一定要显示的Close。在存储过程中试用Cursor的示例：IFEXISTS(SELECT*FROMSYSOBJECTSWHEREname='my_sp_test'ANDTYPE='P')BEGINDROPPROCEDUREmy_sp_test;END;GOcreateproceduremy_sp_test@iint,@outstrvarchar(100)outasdeclare@loginNamevarchar(100);declarecur_usercursorforselectESUS_LOGIN_NAMEfromES_USERwhereESUS_ESCO_ID='100004';begintryopencur_user;--开启游标fetchnextfromcur_userinto@loginName;while@@FETCH_STATUS=0beginif(@i=10)beginset@outstr='loginname:'+@loginName;RAISERROR(66666,--Messageid.16,--Severity,1--State,);endelseif(@i10)beginset@outstr='loginname:'+@loginName;end;fetchnextfromcur_userinto@loginName;end;closecur_user;--关闭游标return;endtrybegincatchif@@ERROR=66666beginclosecur_user;--关闭游标deallocatecur_user;--释放游标end;return;endcatch;go测试存储过程，如下代码：/*第一个入参=12,不会产生异常*/DECLARE@OUTSTR_testVARCHAR(100);execdbo.my_sp_test12,@OUTSTR_testoutprint@OUTSTR_test;--@OUTSTR_test='loginname:ryan'/*第一个入参=8,执行第二次后则会出现下行的异常*'名为'cur_user'的游标已存在。'*/DECLARE@OUTSTR_test_1VARCHAR(100);execdbo.my_sp_test8,@OUTSTR_test_1outprint@OUTSTR_test_1;--@OUTSTR_test_1='loginname:vicky'/*入参为8的测试语句执行第二次，之所以会出现异常，是因为没有将游标释放就return了。所以有使用游标的存储过程，在return之前一定要显示的释放游标。*/四、自定义异常的试用RaisError在使用SQLServer存储过程或者触发器时，通常会使用自定义异常来处理一些特殊逻辑。例如游标的销毁，事务的回滚。接下来将会详细的介绍SQLServer自定义异常的使用。使用“raiserror”来抛出自定义异常。如下代码：在存储过程中，抛出自定义异常，然后在catch块中捕获自定义异常。IFEXISTS(SELECT*FROMSYSOBJECTSWHEREname='my_sp_test'ANDTYPE='P')BEGINDROPPROCEDUREmy_sp_test;END;GOcreateproceduremy_sp_test@iint,@outstrvarchar(100)outasbegintrydeclare@jint;if@i10beginset@outstr='systemexception.';set@j=10/0;endelsebeginset@j=@i;set@outstr='customerexception11111111111111111';RAISERROR(66666,--Messageid.16,--Severity,1--State,);end;endtrybegincatchif@@ERROR=66666beginset@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,@OUTSTR11outprint@OUTSTR11;五、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