SQL_Server存储过程学习总结教案.docVIP

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 @i10 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. 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