T-SQL学习笔记

分支

T-SQL支持IF,IF-ELSE,IF-ELSE IF等多种方式

其中IF的条件判断有IF,IF NOT,IF EXISTS三种形式

DECLARE @num int 
　　set @num = 5 
IF @num = 0     
　　PRINT '000000000000' 
ELSE IF @num = 1     
　　PRINT '11111111' 
ELSE     
　　PRINT '222222222' 
go

CASE WHEN用法

CASE input_expression

WHEN when_expression THEN result_expression

[,...n]

[ELSE else_expression]

END

循环

WHILE <条件表达式>

　　BEGIN

　　　　<代码块>

　　　　[BREAK]

　　　　[CONTINUE]

　　END

延迟执行

WAITFOR {TIME|DELAY} '00:00:00'

其中 TIME是指定时间，DELAY是延迟时间

动态SQL语句

在T-SQL中可以先拼接要执行的SQL，然后调用系统存储过程执行，动态SQL的类型必须是NVARCHAR类型的，如果是字符串常量的话为：N'SQL'

调用的系统存储过程为：SP_EXECUTESQL

DECLARE @STR NVARCHAR(100) 
    SET @STR = 'PRINT 11111' 
execute sp_executesql @STR 
GO

除此以外，还有EXECUTE命令也可以执行动态的SQL语句

DECLARE @STR NVARCHAR(100) 
    SET @STR = 'sp_help headgmp_systemuser' 
execute(@STR) 
GO

execute('sp_help headgmp_systemuser')

execute命令和sp_executesql存储过程的一个主要区别是，sp_executesql支持参数替换，而execute命令不支持，只能传入一个字符串

--sp_executesql参数替换 
declare @sqlString nvarchar(100) 
declare @paramDefine nvarchar(50) 
declare @paramValue nvarchar(50) 
set @paramDefine = N'@userIdValue varchar(10)'    --声明替换参数类型 
set @paramValue = N'qiuwei'                        --实际参数值 
set @sqlString = N'select * from headgmp_systemuser where userid = @userIdValue';    --动态SQL字符串 
execute sp_executesql @sqlString,@paramDefine,@userIdValue=@paramValue                --调用方式 
go

游标

游标的使用步骤：

DECLARE --声明

OPEN --打开，并填充数据

FETCH --读取单行数据

_______ --执行具体操作

CLOSE --停止关闭游标，可以一使用OPEN重新填充打开

DEALLOCATED --释放资源

游标的声明

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]    --局部游标（存在创建的批处理、存储过程或触发器中）或全局游标
[FORWARD_ONLY | SRCOLL]    --前者只能从第一行滚动到最后一行，且只能用FETCH NEXT提取内容,而SCROLL可以前后滚动
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]    --STATIC：创建游标使用数据的临时副本；KEYSET：游标打开时，游标中行的资格和顺序已固定；DYNAMIC：滚动游标时对行进行的修改更新到表中；FAST_FORWARD：启动性能优化
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --READ_ONLY:禁止更新数据表；SCROLL_LOCKS：确保通过游标的更新、删除可以成功；OPTIMISTIC：如果行读入游标后被更新，则对该行的更新、删除不成功
[TYPE_WARNING] --游标从请求的类型银饰转换成另一种类型，给client发出warning msg
FOR select_statement
[FOR UPDATE [OF column_name [,....n]]] --指定可被更新的列

游标变量声明

declare @curVal cursor
set @curVal = cursor for 
select name from headgmp_systemuser

游标的打开

OPEN cursor_name

游标的读取

FETCH
[
    [NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
    FROM
]
{
    {[GLOBAL] cursor_name} | @cursor_var_name
}
[
    INTO @var_name [,...n]
]

游标的关闭

CLOSE cursor_name

游标的释放

DEALLOCATE cursor_name

--游标练习：判断名字长度，用数字标注
--声明游标
DECLARE name_cursor CURSOR
LOCAL
FORWARD_ONLY
KEYSET
READ_ONLY
FOR select top 20 name from headgmp_systemuser
--声明游标结束
DECLARE @tName varchar(20) --声明临时变量
OPEN name_cursor --打开游标
FETCH NEXT FROM name_cursor INTO @tName
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @tName
        FETCH NEXT FROM name_cursor INTO @tName
        UPDATE headgmp_systemuser set nameLen = len(@tName) where name = @tName
    END

CLOSE name_cursor --关闭游标，可再次进行OPEN

DEALLOCATE name_cursor --释放

存储过程

存储过程的基本语法

create {procedure | proc} [schame_name.] proc_name [ ; number]

[

{@parameter dataType [varying] [=default] [out [ put ] ] }

] [,...n]

.......

无参procedure

--无参procedure 
create proc demo01_none_arguments 
as 
select * from headgmp_systemuser go 
--exec demo01_none_arguments

--带输入参数

create proc demo02_with_arguments     
    @deptId char(32),     
    @name varchar(10) 
as     
    select * from headgmp_systemuser 
    where deptid = @deptId and namelike '%'+@name+'%' 
go 
--exec demo02_with_arguments '4028817b3bcb585b013bd0b3b56d074e',''
--exec demo02_with_arguments @name='李',@deptId='4028817b3bcb585b013bd0b3b56d074e'

--带输出参数

create proc demo03_with_output     
    @deptId char(32),    --部门ID     
    @userCount int out    --部门下用户数量 
as     
    select @userCount=COUNT(id) from headgmp_systemuser where deptid = @deptId 
go
/* 

    declare @count int 
    set @count = 1 
    exec demo03_with_output '4028817b3bcb585b013bd0b3b56d074e',@count out 
    print @count

*/

临时表与表变量

关于临时表的区别和优缺点网络上有很多的介绍说明还有分析,我没有自己做分析就不详细介绍了,像了解的可以自己google

--临时表
create table #userInf(
    id varchar(32),
    name varchar(50)
)
insert into #userInf select id,name from headgmp_systemuser
select * from #userInf
drop table #userInf
go

--表变量
declare @tab table(
    id varchar(32),
    name varchar(50)
)
insert into @tab select id,name from headgmp_systemuser
select * from @tab
go

函数

--标量函数
--创建语法
CREATE FUNCTION fun_name
(
    [
        {@param_name param_data_type [= default]} --声明函数的参数
        [,...n] 
    ]
)
returns 
    return_data_type    --返回值类型
    [WITH <function_opt> [,...n]]    --设置函数选项
    [AS]
    BEGIN    
        function_body    --函数体
        return expression --返回值
    END
[;]

--简单标量函数范例:返回部门ID下的人数
create function getUserCountByDept(
    @deptId varchar(32) = ''
)
returns int
as
begin
    declare @count int
    select @count = count(id) from headgmp_systemuser where deptid = @deptId
    return @count
end

--调用标量函数，此处需要加上schema名称
print dbo.getUserCountByDept('4046e4963f1c19c4013f1d73b68f0071')




--简单表值函数声明
CREATE FUNCTION [schema_name.] function_name
(
    [
        {@param_name param_data_type [= default]} --声明函数的参数
        [,...n] 
    ]
)
RETURNS TABLE
    [WITH <function_opt> [,...n]]    --设置函数选项
    [AS]
    RETURN [(] select_stmt [)]


--查询指定部门下的用户信息
create function getUserInfByDept(
    @deptId varchar(32)
)
returns table 
as
    return 
    (select b.name 部门名称,a.name 用户名称 from headgmp_systemuser a left join headgmp_systemdept b
    on a.deptid = b.id
    where b.id = @deptId)


--调用表值函数
select * from  getUserInfByDept('4046e4963f1c19c4013f1d73b68f0071')


--自定义格式表值函数声明
CREATE FUNCTION [schema_name.] function_name
(
    [
        {@param_name param_data_type [= default]} --声明函数的参数
        [,...n] 
    ]
)
RETURNS @cus_table_var TABLE < table definetion > --设置返回值为自定义的table变量
    [WITH <function_opt> [,...n]]    --设置函数选项
    [AS]
    BEGIN
        function_body
        RETURN 
    END
    
--查询指定部门下的用户信息,使用自定义表值类型
create function getUserInfByDept2(
    @deptId varchar(32)
)
returns @res  table (dname varchar(50),uname varchar(50))
as
    begin
    insert into @res
    
    select b.name,a.name  from headgmp_systemuser a left join
         headgmp_systemdept b
        on a.deptid = b.id
    where b.id = @deptId
    return
    end
go

posted on 2013-07-28 15:58 lylyalong 阅读(340) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

lylyalong

T-SQL学习笔记

分支

循环

延迟执行

动态SQL语句

存储过程

导航

公告