【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