T-SQL编程练习(带注释)

 1 use test;
 2 GO
 3 /*创建自定义函数的格式:
 4 * create function 函数名(参数 数据类型)
 5 * returns 返回数据类型 as
 6 * begin
 7 *     代码
 8 * end  
 9 */
10 
11 --创建一个自定义函数,计算两个整数的和
12 create function Addition(@num1 int , @num2 int)
13 returns int as
14 begin
15     declare @result int;
16     set @result = @num1 + @num2;
17     return @result;
18 end
19 GO
20 select [dbo].Addition(200,100)
21 --使用print调用
22 print [dbo].Addition(200,100)
23 --使用exec调用函数(参数不用括号)
24 declare @myresult int;
25 exec @myresult = [dbo].Addition 200,100
26 --使用drop删除Addition函数
27 drop function [dbo].Addition

 

练习(完整)

 

--创建表
create table Class130(
    id int primary key identity,
    stuName varchar(12),
    stuAge int,
    stuSex nchar(1) default (''),
    Brithday datetime default(getdate()),
    teacherName varchar(12) defualt ('Young Sir')
)

--插入数据
insert into Class130(StuName) values ('Viusuangio')

--函数查询表数据(传参)
create function getTeacherName(@stuname varchar(12))
returns table as
return(
    select stuName , teacherName from Class130
    where stuName = @stuname
)
GO
select * from [dbo].getTeacherName('Viusuangio');


--创建函数,传入整数,与1024比较;
create function judgeWith1024(@num int)
returns varchar(20) as
begin
    declare @output varchar(20);
    if @num > 1024
        set @output = '判断:'+@num+'大于1024'if @num = 1024
        set @output = '判断:'+@num+'等于1024'else
        set @output = '判断:'+@num+'小于1024'return @output;
end
GO

/*根据学号判断学生的出生日期是否是闰年
* 1.普通年能被4整除且不能被100整除的为闰年.
* 2.世纪年能被400整除的是闰年
*/

create function judgeLeapYear(@stuno varchar(12))
returns varchar(20) as
begin
    declare @year int , @output varchar(20);
    set @year = (select year(Brithday) from StuInfo where stuNo = @stuno);
    if @year%4 = 0 and @year%100<>0
        set @output = '该学生出生年是闰年';
    else
        set @output = '该学生出生年非闰年';
    return @output;
end
GO

--计算1+2+3+……+100的和,并用print显示计算结果
create function additionFrom1To100()
returns int as
begin
    declare @result int , @addnum int;
    set @result = 0;
    set @addnum = 1;
    while @num <= 100 begin
        set @result += @addnum;
        set @addnum += 1;
    end
    return @result;
end
GO
print [dbo].additionFrom1To100()

 

posted @ 2016-12-22 19:01  木栩  阅读(652)  评论(0编辑  收藏  举报