013_存储_触发_游标

013_存储_触发_游标

 

/*

【】--procedure_循环插入临时表

*/

alter procedure [dbo].[test_liwz]

as

declare @tmp int ,

                   @tmp_ varchar(10)

set @tmp=0

set @tmp_='a'

create table #test_liwz(

a int primary key,

b varchar(20)

)

while @tmp<30

begin

         insert into #test_liwz values(@tmp,@tmp_)

         set @tmp=@tmp+1

         --set @tmp_=convert(varchar(10),chr(@tmp_)+1)

end 

select * from #test_liwz

 

 

exec test_liwz

 

 

/*

【】--procedure_return

*/

create procedure [dbo].[test_liwz_2](@tmp varchar(10))

as

if @tmp like '%li%'

         return 0

else

begin

         print '这只是测试！hollo world！'

         return

end

 

--执行

--1.返回参数

declare @test int

exec @test=test_liwz_2 '3li8'

select 'return_status'=@test

--2.退出

declare @test_ int

exec @test_=test_liwz_2 'lsfd'

 

 

 

 

/*

【】--cursor

*/

--创建临时表 #test_liwz

create table #test_liwz(

a int primary key,

b varchar(20)

)

insert into #test_liwz

select 0,'a'

union

select 1,'b'

union

select 2,'c'

--游标

declare     @test_a int,

                   @test_b varchar(10)

declare cursor_test cursor for

         select a,b from #test_liwz

open cursor_test

fetch next from cursor_test into @test_a,@test_b

while @@fetch_status=0

begin

         select @test_b=@test_b+'test';

         select @test_a=@test_a+1;

         print @test_b;

         --update #test_liwz set a=a+1

         fetch next from cursor_test into @test_a,@test_b--注意不要忘，否则为无限循环

end

close cursor_test

deallocate cursor_test

 

--select * from #test_liwz

--drop table #test_liwz

 

/*

【】--trigger

*/

alter trigger test_liwz_3

         --on #test_liwz  --不能在临时对象上创建 触发器。

         on zd_unit_code

after update,insert,delete

as

begin

         --print 'can''t do it!'

         raiserror('can''t do it!',10,1)

         rollback --回滚

end

 

 --执行

 update zd_unit_code set code='' where unit_sn='1010105'

 

 

 

/*

【】--transaction

*/

--@@error:默认为0，上一条语句执行不成功，则不唯一，每次执行下一条语句前置为0

--@@rowcount:受影响的数据行数

--@@trancount:begin transaction - commit transaction 的值

 

--创建临时表 #test_liwz

create table #test_liwz(

a int primary key,

b varchar(20)

)

insert into #test_liwz

select 0,'a'

union

select 1,'b'

union

select 2,'c'

--select * from #test_liwz

--truncate table #test_liwz

 

declare @temp int

begin transaction test_transaction_liwz

set @temp=@@ROWCOUNT

print @temp

if @@ERROR<>0 goto rollback_

insert into #test_liwz values(5,'d')

if @@ERROR<>0

         goto commit_

else

         print N'insert success！'

insert into #test_liwz values(6,'d')

if @@ERROR<>0

         goto commit_

else

         print N'insert success！'

COMMIT TRANSACTION

 

rollback_:

         IF (@@TRANCOUNT > 0)

         begin

                   print N'transaction!'

                   rollback transaction test_transaction_liwz

         end

commit_:

         IF (@@TRANCOUNT > 0)

         begin

                   print N'error!'

                   commit transaction test_transaction_liwz

         end

转载于:https://www.cnblogs.com/heibaitan/p/4285006.html