游标
游标
概念
游标可以对一个select的结果集进行处理，或是不需要全部处理，就会返回一个对记录集进行处理之后的结果。
游标实际上是一种能从多条数据记录的结果集中每次提取一条记录的机制。游标可以完成：
# 允许定位到结果集中的特定行
# 从结果集的当前位置检索一行或多行数据
# 支持对结果集中当前位置的进行修改
由于游标是将记录集进行一条条的操作，所以这样给服务器增加负担，一般在操作复杂的结果集的情况下，才使用游标。SQL Server 2005有三种游标：T-SQL游标、API游标、客户端游标。
游标的基本操作
游标的基本操作有定义游标、打开游标、循环读取游标、关闭游标、删除游标。
定义游标
declare cursor_name --游标名称
cursor [local | global] --全局、局部
[forward only | scroll] --游标滚动方式
[read_only | scroll_locks | optimistic] --读取方式
for select_statements --查询语句
[for update | of column_name ...] --修改字段
参数：
forward only | scroll：前一个参数，游标只能向后移动；后一个参数，游标可以随意移动
read_only：只读游标
scroll_locks：游标锁定，游标在读取时，数据库会将该记录锁定，以便游标完成对记录的操作
optimistic：该参数不会锁定游标；此时，如果记录被读入游标后，对游标进行更新或删除不会超过
打开游标
open cursor_name;
游标打开后，可以使用全局变量@@cursor_rows显示读取记录条数
检索游标
fetch cursor_name;
检索方式如下：
fetch first; 读取第一行
fetch next; 读取下一行
fetch prior; 读取上一行
fetch last; 读取最后一行
fetch absolute n; 读取某一行
如果n为正整数，则读取第n条记录
如果n为负数，则倒数提取第n条记录
如果n为，则不读取任何记录
fetch pelative n
如果n为正整数，则读取上次读取记录之后第n条记录
如果n为负数，则读取上次读取记录之前第n条记录
如果n为，则读取上次读取的记录
关闭游标
close cursor_name;
删除游标
deallocate cursor_name;
游标操作示例
创建一个游标
declare cursor_stu cursor scroll for
select id, name, age from student;
打开游标
open cursor_stu;
存储读取的值
declare @id int,
@name nvarchar(20),
@age varchar(20);
--读取第一条记录
fetch first from cursor_stu into @id, @name, @age;
--循环读取游标记录
print '读取的数据如下：';
--全局变量
while (@@fetch_status = 0)
begin
print '编号：' + convert(char(5), @id) + ', 名称：' + @name + ', 类型：' + @age;
--继续读取下一条记录
fetch next from cursor_stu into @id, @name, @age;
end
关闭游标
close area_cursor;
删除游标
deallocate area_cursor;
视图
概念
视图就是一个虚拟的数据表，该数据表中的数据记录是有一条查询语句的查询结果得到的。
创建视图准则
创建视图需要考虑一下准则：
# 视图名称必须遵循标识符的规则，该名称不得与该架构的如何表的名称相同
# 你可以对其他视图创建视图。允许嵌套视图，但嵌套不得超过32层。视图最多可以有1024个字段
# 不能将规则和default定义于视图相关联
# 视图的查询不能包含compute子句、compute by子句或into关键字
# 定义视图的查询不能包含order by子句，除非在select 语句的选择列表中还有top子句
下列情况必须指定视图中每列的名称：
# 视图中的如何列都是从算术表达式、内置函数或常量派生而来
# 视图中有两列或多列具有相同名称（通常由于视图定义包含联接，因此来自两个或多个不同的列具有相同的名称）
# 希望视图中的列指定一个与其原列不同的名称（也可以在视图中重命名列）。无论是否重命名，视图列都回继承原列的数据类型
创建视图语法
if (exists (select * from sys.objects where name = 'v_stu'))
drop view v_stu
go
create view v_stu
as
select id, name, age, sex from student;
修改视图
alter view v_stu
as
select id, name, sex from student;
alter view v_stu(编号, 名称, 性别)
as
select id, name, sex from student
go
select * from v_stu;
select * from information_schema.views;
加密视图
if (exists (select * from sys.objects where name = 'v_student_info'))
drop view v_student_info
go
create view v_student_info
with encryption --加密
as
select id, name, age from student
go
--view_definition is null
select * from information_schema.views
where table_name like 'v_stu';
自定义函数
查询所有已创建函数
select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
and type in('fn', 'if', 'tf');
创建函数
if (object_id('fun_add', 'fn') is not null)
drop function fun_add
go
create function fun_add(@num1 int, @num2 int)
returns int
with execute as caller
as
begin
declare @result int;
if (@num1 is null)
set @num1 = 0;
if (@num2 is null)
set @num2 = 0;
set @result = @num1 + @num2;
return @result;
end
go
调用函数
select dbo.fun_add(id, age) from student;
--自定义函数，字符串连接
if (object_id('fun_append', 'fn') is not null)
drop function fun_append
go
create function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
returns nvarchar(2048)
as
begin
return @args + @args2;
end
go
select dbo.fun_append(name, 'abc') from student;
修改函数
alter function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
returns nvarchar(1024)
as
begin
declare @result varchar(1024);
--coalesce返回第一个不为null的值
set @args = coalesce(@args, '');
set @args2 = coalesce(@args2, '');;
set @result = @args + @args2;
return @result;
end
go
select dbo.fun_append(name, '#abc') from student;
返回table类型函数
select name, object_id, type from sys.objects where type in ('fn', 'if', 'tf') or type like '%f%';
if (exists (select * from sys.objects where type in ('fn', 'if', 'tf') and name = 'fun_find_stuRecord'))
drop function fun_find_stuRecord
go
create function fun_find_stuRecord(@id int)
returns table
as
return (select * from student where id = @id);
go
select * from dbo.fun_find_stuRecord(2);
