SQL高级编程

 

-------------SQL Function-------------

---判断数据库是否存在

if exists (select * from sys.databases where name = '数据库名')

drop database [数据库名]

 

--判断表是否存在

if exists (select * from sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [表名]

 

 

--判断存储过程是否存在

if exists (select * from sysobjects where id = object_id(N'[pro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

print 'exists'

else

print 'not exists'

 

--判断函数是否存在

IF OBJECT_ID (N'函数名') IS NOT NULL

DROP FUNCTION dnt_split

 

 

--left(),right(),charindex()----

begin

declare @str varchar(20)

set @str='hello every body';

print left(@str,7) --print result->hello e

print right(@str,7) --print result->ry body

print charindex(' ',@str) --print result->6

end

 

-- cast()数据类型转换函数---------

begin

select CAST('123' as int)

select CAST(123.0 as varchar(6))

select CAST('12.5' as decimal(5,0))  --四舍五入

select CAST('12' as float)

end

 

-- 将查询出来的多列组成为一列的字符串----

begin

select '学生编号：'+scStuNo+'成绩:'+cast(scScore as varchar(6)) as 学生成绩 from score_tb

end

 

--SQL While循环 计算1加到100的和-----

 begin

declare @sum int,@num int

set @num=1

set @sum=0

while @num<101

begin

set @sum=@sum+@num

set @num=@num+1

end

print @sum

 end

 

 --SQL While循环 计算1~100之间奇数的和

  begin

declare @sum int,@num int

set @sum=0

set @num=1

while @num<101

begin

set @num=@num+1

if((@num%2)=0)

continue

else

begin

set @sum=@sum+@num

if(@num=49)

break

end

end

print @sum

 end

 

 --根据查询的每个字段内容给予自定义数据--

 select stuName,cursName,

  case 

  when scScore>=85 then '优秀'

  when scScore>=75 and scScore<85 then '良好'

  when scScore>=60 and scScore<75 then '合格'

  when scScore<60 then '不合格' 

  end

from score_tb sc,student_tb stu,course_tb cur where sc.scStuNo=stu.stuNo and cur.cursNo=sc.scCursNo

 

--自定义函数---

create function funSelectScore(@score float)

returns varchar(12)

as

begin

return

case 

when @score>=85 then '优秀'

when @score>=75 and @score<85 then '良好'

when @score>=60 and @score<75 then '合格'

when @score<60 then '不合格' 

end

end

--调用函数

 select stuName,cursName,dbo.funSelectScore(scScore)  --注意：调用函数是一定要加上 dbo.前缀

 from score_tb sc,student_tb stu,course_tb cur where sc.scStuNo=stu.stuNo and cur.cursNo=sc.scCursNo