use pangu
update employee
set e_wage =
case
when job_level = '1' then e_wage*1.08
when job_level = '2' then e_wage*1.07
when job_level = '3' then e_wage*1.06
else
e_wage*1.05
end

declare @x int @y int @c int
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

/*帶游標(biāo)的存儲(chǔ)過(guò)程*/
create procedure p_fill_remark_tb_hr_gz
as
declare @id1 int
declare @name1 varchar(30)
declare @bm1 varchar(20)

begin
declare cursor1 cursor for select id,name,bm from tb_hr_gz
open cursor1
fetch next from cursor1 into @id1,@name1,@bm1

while @@fetch_status > 0
begin
update tb_hr_gz set remark=@name1+'-'+@bm1 where id=@id1
fetch next from cursor1 into @id1,@name1,@bm1
end
close cursor1
end

//測(cè)試帶游標(biāo)的存儲(chǔ)過(guò)程
EXEC dbo.p_fill_remark_tb_hr_gz

//函數(shù)f_amt_to_eng()功能:數(shù)字金額轉(zhuǎn)換為英文字母金額
CREATE FUNCTION f_amt_to_eng(@num numeric(15,2))
RETURNS varchar(400) WITH ENCRYPTION
AS
BEGIN

DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int
DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
SET @numbers='one two three four five '
+'six seven eight nine ten '
+'eleven twelve thirteen fourteen fifteen '
+'sixteen seventeen eighteen nineteen '
+'twenty thirty forty fifty '
+'sixty seventy eighty ninety '
SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--將12位整數(shù)分成4段:十億、百萬(wàn)、千、百十個(gè)
SET @million=CAST(SUBSTRING(@s,4,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,7,3) AS int)
SET @result=''
SET @i=0
WHILE @i=3
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)--百位0-9
SET @tenth=CAST(SUBSTRING(@s,@i*3+2,1) AS int)
SET @one=(CASE @tenth WHEN 1 THEN 10 ELSE 0 END)+CAST(SUBSTRING(@s,@i*3+3,1) AS int)--個(gè)位0-19
SET @tenth=(CASE WHEN @tenth=1 THEN 0 ELSE @tenth END)--十位0、2-9
IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
SET @result=@result+', '--百位不是0則每段之間加連接符,
IF (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))
SET @result=@result+' and '--百位是0則加連接符AND
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' hundred'
IF @tenth>=2 and @tenth=9
BEGIN
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF @one>=1 and @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=0 and @billion>0
SET @result=@result+' billion'
IF @i=1 and @million>0
SET @result=@result+' million'
IF @i=2 and @thousand>0
SET @result=@result+' thousand'
SET @i=@i+1
END
IF SUBSTRING(@s,14,2)>'00'
BEGIN
SET @result=@result+' AND '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
IF SUBSTRING(@s,15,1)>'0'
SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
END
RETURN(@result)
END

CREATE FUNCTION f_amt_to_chn (@num numeric(14,2))
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int
SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)
SET @c_data=''
SET @i=1
WHILE @i=14
BEGIN
SET @n_str=SUBSTRING(@n_data,@i,1)
IF @n_str>' '
BEGIN
IF not ((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_str AS int)+1,1)
IF not ((@n_str='0') and (@i>4) and (@i>8) and (@i>12))
SET @c_data=@c_data+SUBSTRING('仟佰拾億仟佰拾萬(wàn)仟佰拾元角分',@i,1)
IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='億萬(wàn)'
SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)
END
SET @i=@i+1

END

IF @num0
SET @c_data='負(fù)'+@c_data

IF @num=0
SET @c_data='零元'

IF @n_str='0'
SET @c_data=@c_data+'整'

RETURN(@c_data)

END

create trigger tg_tb_hr_bm on tb_hr_bm
for insert,update,delete
as
declare @bm_d varchar(20)
declare @bm_i varchar(20)
begin
set @bm_d=(select bm from deleted)
set @bm_i=(select bm from inserted)
if exists(select * from tb_hr_gz ,deleted where(tb_hr_gz.bm =deleted.bm ))
begin
update tb_hr_gz set bm='' where bm =@bm_d
end

if update(bm)
begin
update tb_hr_gz set bm=@bm_i where bm =@bm_i
end
end