SQL Server函数

创建自定义函数
create function getNameById (@tid int)
 returns varchar(100)
as
begin
 declare @tname varchar(100)
 select @tname=teacher_name from t_teacher_info
  where teacher_id=@tid
 return @tname
end
-------------------------------------------------------------------------------------------
调用自定义函数
select *,dbo.getNameById(teacher_id) teacher_name from t_course_info
-------------------------------------------------------------------------------------------
create function getTxtByCodeType(@param_code varchar(50),@param_type varchar(50))
       returns varchar(50)
as
begin
 declare @v_txt varchar(50)
    select @v_txt=txt from sys_dic
    where code=@param_code and type=@param_type
    return @v_txt
end
-------------------------------------------------------------------------------------------
调用自定义函数
print dbo.getTxtByCodeType(2,'status')
select dbo.getTxtByCodeType(2,'tradetwo')
-------------------------------------------------------------------------------------------
创建事务
declare @num float,@fromMoney float;
set @num=200;
--转帐200元
begin tran myTran
 update account set money=money-@num
  where username='Edie';
 update account set money=money+@num
  where username='Gabrielle';
 select @fromMoney=money from account where username='Edie';
 if @fromMoney <0
  rollback tran myTran;
 else
  begin
   insert into account_log (acc_from,acc_to,record_date,money_num)
   values('Edie','Gabrielle',getdate(),@num);
   commit tran myTran;
  end;
---------------------------------------------------------------------------------------------
创建过程存储
create proc p_course
  @course_name nvarchar(50)='%' --默认值
as
select * from t_course_info
   where course_name like @course_name
-----------------------------------------------------------------------------------------------
调用过程存储
exec p_course
exec p_course 's%'
------------------------------------------------------------------------------------------------
转账
create proc p_borrowMoney @num float,@fromName varchar(100),@toName varchar(100)
as
begin
 declare @fromMoney float;
 --转账
 begin tran myTran
  update account set money = money-@num where username=@fromName;
  update account set money = money+@num where username=@toName;
  select @fromMoney=money 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 dbo.p_borrowMoney 1000,'Edie','Gabrielle'
-----------------------------------------------------------------------------------------------------
创建触发器
create trigger trg_account
  on account after update
    as
insert into t_autolog(title,msg,time)
  values('帐户信息变更','account表变更',getdate())
------------------------------------------------------------------------------------------------------
行列转置(对于数字)
select
 sum(mon) as mon,
 sum(tur) as tur,
 sum(wed) as wed,
 sum(thu) as thu,
 sum(fri) as fri,
 sum(sat) as sat,
 sum(sun) as sun
from
(select
 case day when 'MON' then status else 0 end as mon,
 case day when 'TRU' then status else 0 end as tur,
 case day when 'WED' then status else 0 end as wed,
 case day when 'THU' then status else 0 end as thu,
 case day when 'FRI' then status else 0 end as fri,
 case day when 'SAT' then status else 0 end as sat,
 case day when 'SUN' then status else 0 end as sun
from dbo.t_duty) as week
--------------------------------------------------------------------------------------------------------
列转置(对于文本)
select mon,tru,web,thu,fri,sat,sun
from
(select case id when 1 then weather else 'a' end as 'mon' from dbo.weather)as m,
(select case id when 2 then weather else 'a' end as 'tru' from dbo.weather)as t,
(select case id when 3 then weather else 'a' end as 'web' from dbo.weather)as w,
(select case id when 4 then weather else 'a' end as 'thu' from dbo.weather)as th,
(select case id when 5 then weather else 'a' end as 'fri' from dbo.weather)as f,
(select case id when 6 then weather else 'a' end as 'sat' from dbo.weather)as sa,
(select case id when 7 then weather else 'a' end as 'sun' from dbo.weather)as su
where mon!='a' and tru!='a' and web!='a' and thu!='a' and fri!='a' and sat!='a' and sun!='a'