SQL Server 常用代码大全

这篇文章主要为大家详细介绍了SQL Server 常用代码大全，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

 
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 

代码如下:

 
/*带游标的存储过程*/ 
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 

//测试带游标的存储过程 
EXEC dbo.p_fill_remark_tb_hr_gz 

代码如下:

 
//函数f_amt_to_eng()功能:数字金额转换为英文字母金额 
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位整数分成4段:十亿、百万、千、百十个 
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)--个位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('仟佰拾亿仟佰拾万仟佰拾元角分',@i,1) 
IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='亿万' 
SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1) 
END 
SET @i=@i+1 

END 

IF @num<0 
SET @c_data='负'+@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 

注：关于SQL Server 常用代码大全的内容就先介绍到这里，更多相关文章的可以留意