MSSQL之二十 函数

--===================  标量函数  ========================--函数功能：计算员工的月薪，接收 支付率*8*30*alter function MonthlySal(@PayRate float,@n tinyint)--, floatreturns float--返回值类型asbeginreturn (@PayRate * 8 * @n)--* ；具体的返回值end--===========declare @PayRate floatset @PayRate  = dbo.MonthlySal(2,28)--,100print @PayRate--==============================create table 工资2(name varchar(8),PayRate float,days tinyint    )insert into 工资 values('王飞',13,30)insert into 工资 values('肖捷',13,23)insert into 工资 values('邓玉',13,20)select name as '姓名',dbo.MonthlySal(PayRate,days) as '月工资' from 工资drop function MonthlySal--=============================================create database 范例use 范例--===================================================create table bankcount           (CountIDint primary key,--帐号idCountName char(10),--帐户CountMoneymoney,--金额Accrualmoney,--利息Yeardatedatetime,--存款日期MoneyKindvarchar(20)--币种)goinsert into bankcount (CountID,CountName,CountMoney,Accrual,Yeardate,MoneyKind) values (1,'张三',1200,0.20,'2000-04-04','人民币')insert into bankcount (CountID,CountName,CountMoney,Accrual,Yeardate,MoneyKind) values (2,'李四',2300,0.40,'2001-06-12','日圆')insert into bankcount (CountID,CountName,CountMoney,Accrual,Yeardate,MoneyKind) values (3,'王五',4500,0.50,'2002-06-17','人民币')insert into bankcount (CountID,CountName,CountMoney,Accrual,Yeardate,MoneyKind) values (4,'赵六',6400,0.15,'2003-11-6','美圆')insert into bankcount (CountID,CountName,CountMoney,Accrual,Yeardate,MoneyKind) values (5,'王麻子',2800,0.12,'2002-07-04','法郎')insert into bankcount (CountID,CountName,CountMoney,Accrual,Yeardate,MoneyKind) values (6,'赵前',3200,0.22,'2005-06-024','欧圆')insert into bankcount (CountID,CountName,CountMoney,Accrual,Yeardate,MoneyKind) values (7,'孙八',3700,0.44,'2004-09-01','英镑')select * from bankcountdelete from bankcount--==========定义 表值 函数:内联表值函数============================================alter function fx_GetMoney (@CardID nvarchar(20))returns table--表值函数的返回值是个table，是个表.asreturn (select CountId 帐号,CountName 姓名,CountMoney 余额,Accrual 利息,YearDate 存款日期,MoneyKind 存款类型 from bankcountwhere CountID = @CardID)go--==================================select * from fx_GetMoney(2)--==========================================--==========定义 表值函数:多语句表值函数=========alter function PayRate(@rate money) --@rate:表示一个人的存款金额,即余额returns @table table--返回也是一个表？自定义的表(帐号int not null,姓名   char(20) not null,余额  money not null)as --指定函数处理.的代码begin--当在PayRate函数中执行查询金额>@ate的时候，返回.insert @table select CountID ,CountName ,CountMoney from bankcountwhere CountMoney > @ratereturn -- 返回被调用处end--==============================================select * from bankCountselect * from PayRate(4000)--==============================================--1.使用函数通常在什么地方？--2.不使用函数如何做？--3.通过计算列方式实现上述求某月某天工资功能

--金额阿拉伯数字转换为中文的自定义函数CREATE FUNCTION ChangeBigSmall(@ChangeMoney money)  RETURNS VarChar(100) AS   BEGIN     Declare    @String1    char(20)    Declare    @String2    char(30)    Declare    @String4    Varchar(100)    Declare @String3     Varchar(100)    --从原A值中取出的值    Declare @i         int        --循环变量    Declare @J         Int        --A的值乘以100的字符串长度    Declare @Ch1         Varchar(100)    --数字的汉语读法    Declare @Ch2         Varchar(100)    --数字位的汉字读法    Declare @Zero         Int        --用来计算连续有几个零    Declare    @Returnvalue    VarChar(100)    Select  @Returnvalue = ''    Select    @String1 = '零壹贰叁肆伍陆柒捌玖'    Select    @String2 = '万仟佰拾亿仟佰拾万仟佰拾元角分'    Select @String4 = Cast(@ChangeMoney*100 as int)        select @J=len(cast((@ChangeMoney*100) as int))    Select @String2=Right(@String2,@J)    Select    @i = 1     while    @i<= @j Begin        Select @String3 = Substring(@String4,@i,1)        if @String3<>'0' Begin            Select     @Ch1 = Substring(@String1, Cast(@String3 as Int) + 1, 1)            Select    @Ch2 = Substring(@String2, @i, 1)            Select    @Zero = 0                    --表示本位不为零        end        else Begin            If (@Zero = 0) Or (@i = @J - 9) Or (@i = @J - 5) Or (@i = @J - 1)                        Select @Ch1 = '零'                     Else                        Select @Ch1 = ''                    Select @Zero = @Zero + 1             --表示本位为0                                    --如果转换的数值需要扩大，那么需改动以下表达式 I 的值。            Select @Ch2 = ''                If @i = @J - 10  Begin                        Select @Ch2 = '亿'                        Select @Zero = 0            end                                If @i = @J - 6 Begin                        Select @Ch2 = '万'                        Select @Zero = 0            end                                if @i = @J - 2 Begin                        Select @Ch2 = '元'                        Select @Zero = 0            end                                If @i = @J                         Select @Ch2 = '整'                                end            Select @Returnvalue = @Returnvalue + @Ch1 + @Ch2        select @i = @i+1    end    --最后将多余的零去掉    If CharIndex('仟仟',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '仟仟', '仟')    If CharIndex('佰佰',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '佰佰', '佰')        If CharIndex('零元',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '零元', '元')            If CharIndex('零万',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '零万', '万')           If CharIndex('零亿',@Returnvalue) <> 0            Select @Returnvalue = Replace(@Returnvalue, '零亿', '亿')            If CharIndex('零整',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '零整', '整')        If CharIndex('零佰',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '零佰', '零')    If CharIndex('零仟',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '零仟', '零')    If CharIndex('元元',@Returnvalue) <> 0             Select @Returnvalue = Replace(@Returnvalue, '元元', '元')    return @ReturnvalueENDselect dbo.ChangeBigSmall(2000.01)