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
