drop table account;
drop table account_log;
create database lable
create table account (
userid int primary key identity(1,1) not null,
username varchar(30) not null,
password varchar(128) not null,
mymoney float
);
create table account_log (
account_logid int primary key identity(1,1) not null,
acc_from varchar(30),
acc_to varchar(30),
record_date datetime,
money_num float
);
select * from account
select * from account_log
insert into account values('Tom','123456',30000);
insert into account values('Jim','123456',10000);
declare @num float;
set @num=20000;
--转帐20000元
begin transaction myTran
update account set mymoney=mymoney-@num
where username='Tom';
update account set mymoney=mymoney+@num
where username='Jim';
--记录日志信息
insert into account_log (acc_from,acc_to,record_date,money_num)
values('Tom','Jim',getdate(),@num);
commit transaction myTran;
select * from account;
select * from account_log;
declare @num float,@fromMoney float;
set @num=20000;
--转帐20000元
begin tran myTran
update account set mymoney=mymoney-@num
where username='Tom';
update account set mymoney=mymoney+@num
where username='Jim';
select @fromMoney=mymoney from account where username='Tom';
if @fromMoney <0
rollback tran myTran;
else
begin
insert into account_log(acc_from,acc_to,record_date,money_num)
values('Tom', 'Jim',getdate(),@num);
commit tran myTran;
end;
exec sp_databases
exec sp_stored_procedures
exec sp_tables
exec sp_help account
exec sp_rename account,newaccount
exec sp_rename newaccount,account
exec sp_renamedb less3,less
exec sp_renamedb less,less3
create procedure proc_query_account
as
select * from account where mymoney>=10000
exec proc_query_account
drop procedure proc_query_account_log
create procedure proc_query_account_log
@acc_from varchar(30)='T%' --默认值
as
select * from account_log
--where acc_from like @acc_from
exec proc_query_account_log
exec proc_query_account_log 'J%'
create proc borrowMoney
@num float,
@fromName varchar(100),
@toname varchar(100)
as
begin
declare @fromMoney float;
--转帐
begin tran myTran
update account set mymoney=mymoney-@num
where username=@fromName;
update account set mymoney=mymoney+@num
where username=@toname;
select @fromMoney=mymoney from account
where username=@fromName;
if @fromMoney <0
rollback tran myTran;
else
begin
insert into account_log (acc_from, acc_to,
record_date, money_num)
values(@fromName, @toname,getdate(),@num);
commit tran myTran;
end;
end;
exec borrowMoney 10000,'Jim','Tom'
select * from account;
select * from account_log;
drop proc proc_query_money
create procedure proc_query_money
@username varchar(30),@@mymoney float out
as
select @@mymoney=mymoney from account
where username=@username
declare @fromMoney float ;
exec proc_query_money 'Jim',@fromMoney out
print @fromMoney
create table t_auitlog(
logid int primary key identity(1,1) not null,
title varchar(30),
msg varchar(30),
changetime datetime
);
create trigger trg_account
on account after update
as
insert into t_auitlog(title,msg,changetime)
values ('帐户信息变更','account表变更',getdate())
exec borrowMoney 10000,'Tom','Jim'
select * from t_auitlog
create trigger trg_account1
on account after update
as
declare @oldmoney float,@newmoney float,@change float;
select * from deleted;
select * from inserted;
select @oldmoney=mymoney from deleted;
select @newmoney=mymoney from inserted;
set @change=@newmoney-@oldmoney;
insert into t_auitlog(title,msg,changetime)
values ('帐户信息变更',@change,getdate())
select * from t_auitlog
select * from account;
update account set mymoney=100000 where username='Tom'