【SQL Sever】 函数的创建

--函数的创建
create function getname (@stunumber char(6))
returns varchar(20)
with encryption
begin
declare @stuname varchar(20)
select @stuname=stuname from student
 where stunumber=@stunumber
return @stuname
end 

select * from student

create function getstudent(@claid int)
returns table
with encryption
return
select * from student
 where claid=@claid



select dbo.getname('200401') as '学生姓名'

select * from getstudent(6)

drop function getstudent


CREATE TABLE bank
(
    customerName CHAR(10),  --顾客姓名
    currentMoney MONEY     --当前余额
)
ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney    
       CHECK(currentMoney>=1)
INSERT INTO bank(customerName,currentMoney)
        VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney)
        VALUES('李四',1)

select * from bank

declare @err1 int,@err2 int
begin transaction 
update bank set currentMoney=currentMoney-1
set @err1=@err1+@@ERROR
save transaction transfer
UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三'
set @err2=@err2+@@ERROR
UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四'
set @err2=@err3+@@ERROR
if @err1 <> 0
  begin
rollback transaction
print '操作失败。。'
  end
else
  begin
if @err2 <> 0
  begin
rollback transaction transfer
print '转账失败。。'
  end
   end