常用的sql server 函数、存储过程、临时表总结

最近写T-sql语句比较多， 想把这段时间遇到过的貌似有用的一些sql server的函数，存储过程，临时表等总结一下，以便不时之需，当然不会一下子所有都总结，但会分段总结；

 

1)存储过程定义：

 

create proc [proc_name]
@para_1 nvarchar(250),
@para_2 int=1,
@para_3 nchar(10) output
as

 

上面这段sequal就是创建一个名为proc_name的存储过程，她接受两个输入参数，分别为@para_1和有默认值为1的@para_2,且返回一个nchar类型的值；
@para_2 int=1, 指明了@para_2参数在调用该存储过程时可以不提供，系统会给个默认值为1；
@para_3 nchar(10) output, 说明@para_3是一个输出参数，存储过程可以指明多个输出参数，如还可以在后面加一个输出参数：@para_4 bit output,要在退出存储过程前给她赋值，如果不赋值她一直保持为null;
可以像下面那样去调用：

declare @arg_3 nchar(10)
exec proc proc_name 'staff_001',0,@arg_3 output

-- 或者不指供有默认值的para_2, 如
declare @arg_3 nchar(10)
exec proc proc_name 'staff_001',@arg_3 output

 

 

 

2)临时表：

2种临时表，分别为：全局，局部
全局和局部和平时学的编程语言里的全局变量和局部变量语义是一样，只是意思上有点差别;
如全局临时表一旦创建了，她就像一个普通的表一样，只要创建她的那个连接未断开，当前任何的连接都可以访问该全局临时表，如果创建她的连接断开了，那么已经引用她的其它任务，可以继续引用她，但新的任务或连接不能再引用她，一旦没有任何任务引用她时，该临时表即被系统释放掉；
局部临时表只能在创建她的任务里引用， 比喻创建了一个连接，如平时那样连接上一个数据库，然后创建一个临时表，那么该临时表，可能被在该连接里的所有任务引用，但如果在该连接里的某个存储过程创建，则只在被该存储过程引用,其它存储过程或函数(这些统称为任务)都不能引用她；

创建全局临时表， 全局临时表都是以##开头的,这是sql server 内定的，不能改：

create table [##g_temp_name](
    staff_no nvarchar(250) primary key,
    staff_name nvarchar(250) null,
    state int default 0
    )
-- 或者
select * into [##g_temp_name] from real_table

-- 或者
exec('select * into [##g_temp_name] from real_table')

 

 

上面3种试创建的全局临时表效果是一样的，只要连接未断开，都可以像访问普通表一样访问她：

 

select * from ##g_temp_name

-- or

exec('select * from ##g_temp_name')

 

 

 

 

创建局部临时表， 局部临时表都以单个#开头的，也是sql server 内定的：

 

create table [#temp_name](
    staff_no nvarchar(250) primary key,
    staff_name nvarchar(250) not null,
    state int default 0
    )

-- 或者
select * into #temp_name from real_table

-- 或者

exec ('select * into #temp_name from real_table')

 

 

 

 

注意局部临时表只能在一个批处理里有效，如用第3种方式创建的局部临时表，是无法在这个批外面访问到的，所以用字符串拼接方式创建的临时表，意义不大，除非访问临时表也拼接到该字符串里去；
如果确实要用到拼接字符串也作查询数据的，这里有两个解决方案，一个是用全局临时表放到拼接字符串里，因为全局临时表一旦创建了，只要连接还没断开都可以随便访问的，如：

 

exec ('select field_1, field_2 into ##g_temp_name from real_table')
select * from ##g_temp_name

 

 

 

 

当然用全局临时表，有个缺点是当前的任务连接都可以访问到她，所以当出现并发访问时，就出问题了，可能会被另一个连接把她的数据修改了。下面有另一种解决方案可以完美的处理这种问题，就是用局部临时表，但局部临时表不写的拼接字符串去，而把拼接字符串查询语句返回的记录插入到局部临时表里， 这种方法屡试不爽：

 

create table #temp_name(
    staff_no nvarchar(250)
    )

insert into #temp_name exec('select field_1 from real_table')
-- 再扩展一下
select * into #temp_name_2 from real_table_2 A where A.field_1 in(select B.staff_no from #temp_name where B.staff_no <> '')
-- 
select * from #temp_name_2

 

 

 

在写存储过程时经常用到临时表，如果对一个临时表名只用一次，可以不手动删除它，在调用完存储过程系统自动会把它删除，但如果要多只用到，有时就要进行手动删除了，如果删除了一个不存在的临时表，系统会报错的，所以在手动删除前最好判断该临时表是否存在；
临时表都不保存在当前数据库的，而是在tempdb里，所以删除时要注意：

 

if object_id('tempdb..[#temp_name]') is not null
begin
    drop table [#temp_name]
end

 

 

 

3) 游标：
游标是很有用的工作，特别是用在逐行访问表里的记录时，就像C#里的foreach一样，对一个集合进行迭代访问，但游标却特耗资源，如能不用尽量不要用，只有在用其它方法无法做到，或做起来特麻烦时就要考虑用游标了; 创建一个游标：

 

declare [cursor_name] cursor from select staff_no, staff_name from real_table

-- 声明两个用来存储no 和name的变量 
declare @staff_no nvarchar(250), @staff_name nvarchar(250)

-- 打开游标
open [cursor_name]

-- 移动游标到第一行
fetch [cursor_name] into @staff_no, @staff_name

-- 判断游标是否已迭代完@@fetch_status = 0说明还未完
while @@FETCH_STATUS = 0
begin
    -- Do something with @staff_no, @staff_name

    -- 继续移动游标到下一行
    fetch [cursor_name] into @staff_no, @staff_name
end

-- 关闭游标
close [cursor_name]
-- 释放资源
deallote [cursor_name]

 

 

 

******说明：　方括号指的是要创建的对象名称：如[cursor_name]可以自定义名为my_cur_name,不要加方括号;还有游标的名字前不要加其它修饰符，如@my_cur_name或#my_cur_name, 单纯my_cur_name就可以了******