6.2.3.2CASE语句
--CASE 复制代码代码以下:
usepangu
updateemployee
sete_wage=
case
whenjob_level=1thene_wage*1.08
whenjob_level=2thene_wage*1.07
whenjob_level=3thene_wage*1.06
else
e_wage*1.05
end
6.2.4轮回语句(while)
--WHILE 复制代码代码以下:
declare@xint@yint@cint
select@x=1@y=1
while@x<3
begin
print@x--打印变量x的值
while@y<3
begin
select@c=100*@x+@y
print@c--打印变量c的值
select@y=@y+1
end
select@x=@x+1
select@y=1
end
//存储历程创建
语法:
createprocedure<存储历程名>(
[输出参数列表],[前往参数列表output]
)
as
[部分变量界说]
begin
{语句体}
end
代码:
createprocedurep_update_name_tb_hr_gz(@idint,@newnamevarchar(30))
as
begin
if(exists(select*fromtb_hr_gzwhereid=@id))
begin
updatetb_hr_gzsetname=@newnamewhereid=@id
end
end
//存储函数创建
语法:
CREATEFUNCTION<函数名>(参数变量列表)
[前往值RETURNS数据范例][WITHENCRYPTION]
AS
BEGIN
{函数代码体....}
END
代码: 复制代码代码以下:
//函数f_amt_to_eng()功效:数字金额转换为英笔墨母金额
CREATEFUNCTIONf_amt_to_eng(@numnumeric(15,2))
RETURNSvarchar(400)WITHENCRYPTION
AS
BEGIN
DECLARE@iint,@hundredsint,@tenthint,@oneint
DECLARE@thousandint,@millionint,@billionint
DECLARE@numbersvarchar(400),@svarchar(15),@resultvarchar(400)
SET@numbers=onetwothreefourfive
+sixseveneightnineten
+eleventwelvethirteenfourteenfifteen
+sixteenseventeeneighteennineteen
+twentythirtyfortyfifty
+sixtyseventyeightyninety
SET@s=RIGHT(000000000000000+CAST(@numASvarchar(15)),15)
SET@billion=CAST(SUBSTRING(@s,1,3)ASint)--将12位整数分红4段:十亿、百万、千、百十个
SET@million=CAST(SUBSTRING(@s,4,3)ASint)
SET@thousand=CAST(SUBSTRING(@s,7,3)ASint)
SET@result=
SET@i=0
WHILE@i<=3
BEGIN
SET@hundreds=CAST(SUBSTRING(@s,@i*3+1,1)ASint)--百位0-9
SET@tenth=CAST(SUBSTRING(@s,@i*3+2,1)ASint)
SET@one=(CASE@tenthWHEN1THEN10ELSE0END)+CAST(SUBSTRING(@s,@i*3+3,1)ASint)--个位0-19
SET@tenth=(CASEWHEN@tenth<=1THEN0ELSE@tenthEND)--十位0、2-9
IF(@i=1and@billion>0and(@million>0or@thousand>0or@hundreds>0))or
(@i=2and(@billion>0or@million>0)and(@thousand>0or@hundreds>0))or
(@i=3and(@billion>0or@million>0or@thousand>0)and(@hundreds>0))
SET@result=@result+,--百位不是0则每段之间加毗连符,
IF(@i=3and(@billion>0or@million>0or@thousand>0)and(@hundreds=0and(@tenth>0or@one>0)))
SET@result=@result+and--百位是0则加毗连符AND
IF@hundreds>0
SET@result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+hundred
IF@tenth>=2and@tenth<=9
BEGIN
IF@hundreds>0
SET@result=@result+and
SET@result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF@one>=1and@one<=19
BEGIN
IF@tenth>0
SET@result=@result+-
ELSE
IF@hundreds>0
SET@result=@result+and
SET@result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF@i=0and@billion>0
SET@result=@result+billion
IF@i=1and@million>0
SET@result=@result+million
IF@i=2and@thousand>0
SET@result=@result+thousand
SET@i=@i+1
END
IFSUBSTRING(@s,14,2)00
BEGIN
SET@result=@result+AND
IFSUBSTRING(@s,14,1)=0
SET@result=@result+zero
ELSE
SET@result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1)ASint)*10-9,10))
IFSUBSTRING(@s,15,1)0
SET@result=@result++RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1)ASint)*10-9,10))
END
RETURN(@result)
END
复制代码代码以下:
CREATEFUNCTIONf_amt_to_chn(@numnumeric(14,2))
RETURNSvarchar(100)WITHENCRYPTION
AS
BEGIN
DECLARE@n_dataVARCHAR(20),@c_dataVARCHAR(100),@n_strVARCHAR(10),@iint
SET@n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100)ASbigint)ASvarchar(20)),14)
SET@c_data=
SET@i=1
WHILE@i<=14
BEGIN
SET@n_str=SUBSTRING(@n_data,@i,1)
IF@n_str
BEGIN
IFnot((SUBSTRING(@n_data,@i,2)=00)or((@n_str=0)and((@i=4)or(@i=8)or(@i=12)or(@i=14))))
SET@c_data=@c_data+SUBSTRING(零壹贰叁肆伍陆柒捌玖,CAST(@n_strASint)+1,1)
IFnot((@n_str=0)and(@i4)and(@i8)and(@i12))
SET@c_data=@c_data+SUBSTRING(仟佰拾亿仟佰拾万仟佰拾元角分,@i,1)
IFSUBSTRING(@c_data,LEN(@c_data)-1,2)=亿万
SET@c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)
END
SET@i=@i+1
//触发器创建
语法:
createtrigger<触发器称号>on<表名>
[forinsertupdatedelete]
as
[界说变量]
begin
{代码块...}
end
代码0: 复制代码代码以下:
createtriggertg_tb_hr_bmontb_hr_bm
forinsert,update,delete
as
declare@bm_dvarchar(20)
declare@bm_ivarchar(20)
begin
set@bm_d=(selectbmfromdeleted)
set@bm_i=(selectbmfrominserted)
ifexists(select*fromtb_hr_gz,deletedwhere(tb_hr_gz.bm=deleted.bm))
begin
updatetb_hr_gzsetbm=wherebm=@bm_d
end
ifupdate(bm)
begin
updatetb_hr_gzsetbm=@bm_iwherebm=@bm_i
end
end