sql server 存储器使用

use studb
-- 如果该存储过程存在，则删除后再创建
if exists (select * from sysobjects where name = 'nopasscvb')
    drop proc nopasscvb
go
-- 创建存储过程
CREATE PROC nopasscvb
    @notpassSum  int output ,  -- 返回不及格的人数
    @writtenPass int = 60 ,       -- 参数1  笔试及格线
    @labPass     int = 60       -- 参数2  机试及格线
AS            -- AS 后写的东西相当于JAVA中的方法体
    if not( @writtenPass between 0 and 100
        and  @labPass between 0 and 100 )
        begin
            raiserror('及格线分数错误，必须在0 ~ 100 之间' , 16 , 1 )
            return
        end
        

    declare @writtenAvg float  -- 笔试平均分
    declare @labAvg float       -- 机试平均分
    select @writtenAvg=avg(writtenExam),@labAvg=avg(labExam)
    from   stuMarks
    print '笔试平均分:'  +  convert(varchar(5),@writtenAvg)
    print '机试平均分:'  +  convert(varchar(5),@labAvg)
    if (@writtenAvg > 70 and @labAvg > 70)
        print '本班考试成绩：优秀'
    else
        print '本班考试成绩：较差'
    print '-------------------------------------'
    print '                没有及格的人员名单如下：'

    select stuname,a.stuno,writtenExam,labExam from stuInfo a,stuMarks b
    where a.stuno = b.stuno and
          (b.writtenExam < @writtenPass or b.labExam < @labPass)
    select @notpassSum=count(*) from stumarks where writtenExam < @writtenPass or labExam < @labPass
go


declare @sum int
exec nopasscvb @sum output,170,170
print @sum
print @@error

declare @writtenPass int
declare @labPass int
set @writtenPass = 11
set @labPass = 600
if  not ( @writtenPass between 0 and 100
        and  @labPass between 0 and 100 )
    print '错误'
else
    print '正确'
    
use bank

create database newbank
go
use newbank
go
create table bank (
    name varchar(20),
    card varchar(8) primary key,
    money money check(money >= 1)
)

insert into bank values('张三','10010001',1000)
insert into bank values('李四','10010002',1)

select * from bank

create table transinfo(
    transDate datetime ,
    card varchar(8) foreign key references bank(card),
    transType varchar(4) check(transType in('存入','取出')),
    transMoney money
)

select * from bank

select * from transinfo

insert into transinfo values(getdate(),'10010001','存入',1000)

if exists (select * from sysobjects where name = 'trigger_1')
    drop trigger trigger_1
go
--通过触发器完成
create trigger trigger_1
    on transinfo
    for insert
as
    -- 从inserted 表中，取出存入的金额 用于修改bank表中用户的余额
    declare @money money           -- 金额
    declare @card  varchar(8)      -- 卡号
    declare @transType varchar(4)  -- 交易类型
    select @money = transmoney,@card = card,@transType = transType from inserted
    print '交易金额:'+convert(varchar,@money)
    print '交易卡号:'+@card
    print '交易类型:'+@transType
    if @transType = '存入'
        update bank set money = money + @money where card = @card
    else
        update bank set money = money - @money where card = @card
        
go

insert into transinfo values(getdate(),'10010001','存入',1888)


insert into transinfo values(getdate(),'10010001','取出',1999999)
select * from bank


if exists (select * from sysobjects where name = 'trigger_1')
    drop trigger trigger_1
go
--通过触发器完成
create trigger trigger_1
    on transinfo
    for insert
as
    -- 从inserted 表中，取出存入的金额 用于修改bank表中用户的余额
    declare @money money           -- 金额
    declare @card  varchar(8)      -- 卡号
    declare @transType varchar(4)  -- 交易类型
    declare @userMoney money       -- 用户交易前的余额
    select @money = transmoney,@card = card,@transType = transType from inserted
    select @userMoney = money from bank where card = @card
    print '交易金额:'+convert(varchar,@money)
    print '交易卡号:'+@card
    print '交易类型:'+@transType
    if @transType = '存入'
        update bank set money = money + @money where card = @card
    else
        begin
          if @userMoney >= @money + 1
            update bank set money = money - @money where card = @card
          else
            begin
                raiserror('用户余额不足',16,1)
                rollback transaction
            end
        end
go


select * from transinfo
-- 删除触发器
create trigger trigger_2
    on transinfo
    for delete
as            -- 如果删除数据，则将删除的数据存入backtable表中
    if exists( select * from sysobjects where name = 'backtable')
        insert into backtable select * from deleted
    else
        select * into backtable from deleted
go


select * from transinfo
select * from backtable

delete transinfo where transmoney=1999

create trigger trigger_3
    on bank
    for update
as
    declare @beforeMoney money
    declare @afterMoney  money
    select @beforeMoney = money from deleted
    select @afterMoney = money from inserted
    if abs(@afterMoney - @beforeMoney) > 20000
        begin
        raiserror('每笔交易金额不得大于20000元整，交易失败',16,1)
        rollback transaction
        end
go

insert into transinfo values(getdate(),'10010001','取出',29919)