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 V ARCHAR(100);
exec dbo.my_sp_test12,@OUTSTR_test out
print @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 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;
