SQL SERVER 视图，事务练习

 

select * from book

----------建立视图-----------
create view b_view
as
select * from book


----------调用视图-------------
select * from b_view


--------局部变量声明和赋值-----------

declare @num int
set @num=22
select @num=count(*) from book
print @num


--------全局变量（系统变量）---------
select @@version
select @@identity
select @@error


----------if else使用--------------

select * from T_person4

declare @Fsalary numeric(10,2)
select @Fsalary=avg(Fsalary) from T_person4
if @Fsalary<6000
 begin
  select 'A'
  select top 2 * from T_Person4 order by Fsalary desc
 end 
else
 begin
  select 'B'
  select top 2 * from T_person4 order by Fsalary ASC
 end

---------------while 使用----------------------
--看每位员工的薪水是否大于5000，不大于就加500，再查看是否全部大于，否则继续加500。
declare @Fsalary numeric(10,2)
declare @num int
while(1=1)
select @num=count(*) from T_person4 where Fsalary>5000
begin
if @num>0
update T_person4 set Fsalary=Fsalary-500
 else
  begin
   return
  end
end

select * from deposit
select * from payment
---------事务(一个程序的执行单元,多条SQL语句，要不然同时执行，要不然都不执行)----
Begin Transaction  -----开始事务
declare @fund money ----不能直接declare @fund money=400，切记
set @fund=400
declare @depositor_id char(4)
set @depositor_id='1234'
declare @errnum int
set @errnum=0
update deposit set fund=fund-@fund where depositor_id=@depositor_id
set @errnum=@errnum+@@error  ----全局变量@@error，每次使用后，自动变为0
insert into payment(depositor_id,depositor_name,fund)values(@depositor_id,'李三三',@fund)
set @errnum=@errnum+@@error
if @errnum=0
  begin
    commit Transaction  ----事务提交
    select '取款成功'
  end
else
 begin
  rollback Transaction  -----事务回滚
  select '取款失败'
 end