游标和视图

游标

游标思维方式是单条，SQL语句思维方式是集合。

由标的生命周期有5部分：

1.游标定义

游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在Select数据集上的。

游标定义：
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
    [ FORWARD_ONLY | SCROLL ] 
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
    [ TYPE_WARNING ] 
    FOR select_statement 
    [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

看起来不太好理解，一步一步说：

LOCAL和GLOBAL二选一： LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文，全局内有效。默认作用域为GLOBAL

FORWARD_ONLY 和 SCROLL 二选一：FORWARD_ONLY意味着游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项，而SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动。默认FORWARD_ONLY

STATIC KEYSET DYNAMIC 和 FAST_FORWARD 四选一：

STATIC意味着，当游标被建立时，将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响到游标的内容.

DYNAMIC是和STATIC完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变

KEYSET可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据

FAST_FORWARD可以理解成FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划，而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划，大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好.

READ_ONLY SCROLL_LOCKS OPTIMISTIC 三选一

READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作

SCROLL_LOCKS是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功

OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新

2.打开游标

注意，当全局游标和局部游标变量重名时，默认会打开局部变量游标

OPEN test_Cursor

3.使用游标

游标的使用分为两部分,一部分是操作游标在数据集内的指向，另一部分是将游标所指向的行的部分或全部内容进行操作

支持6种移动选项,分别为到第一行（FIRST),最后一行(LAST),下一行(NEXT),上一行(PRIOR),直接跳到某行(ABSOLUTE(n)),相对于目前跳几行(RELATIVE(n)),

FETCH NEXT FROM cur INTO @a --下一行
FETCH LAST FROM cur INTO @a --最后一行
FETCH FIRST FROM cur INTO @a --第一行
FETCH PRIOR FROM cur INTO @a --上一行
FETCH ABSOLUTE 3 FROM cur INTO @a --第三行
FETCH RELATIVE -1 FROM cur INTO @a --当前行上一行

未指定SCROLL选项的游标来说，只支持NEXT取值.

INTO为取这行的值到变量

游标经常会和全局变量@@FETCH_STATUS与WHILE循环来共同使用,以达到遍历游标所在数据集的目的。

@@fetch_status是MicroSoft SQL SERVER的一个全局变量
其值有以下三种，分别表示三种不同含义：【返回类型integer】

0 FETCH 语句成功
-1 FETCH 语句失败或此行不在结果集中
-2 被提取的行不存在

4.关闭游标

在游标使用完之后，一定要记得关闭,只需要一行代码:CLOSE+游标名称

CLOSE test_Cursor

5.释放游标

当游标不再需要被使用后，释放游标，只需要一行代码:DEALLOCATE+游标名称

DEALLOCATE test_Cursor

小例子：

CREATE table  tmp_check([nf] varchar(10),[yf] varchar(10))
INSERT into tmp_check(nf,yf)values('2020','12')
INSERT into tmp_check(nf,yf)values('2021','01')
    /*遍历tmp_check的年份和月份*/

    -- 为某个查询集合定义游标：
    DECLARE cur CURSOR FOR (SELECT nf,yf FROM tmp_check)--declare 定义关键字，cur游标名，cursor 定义的是游标，for为某个数据集
    OPEN cur --打开游标,处理该数据集
        DECLARE @year_ AS VARCHAR(4) --定义变量
	DECLARE @month_ AS VARCHAR(2)--定义变量
        FETCH NEXT FROM cur INTO @year_,@month_ --取第一行年月放进变量并改变@@fetch_status状态从-1-》0
        --处理
        WHILE @@fetch_status=0 --开始遍历
        BEGIN
            PRINT('年份:'+@year_+',月份：'+@month_)
            --//在这做其它的事
            FETCH NEXT FROM cur INTO @year_,@month_
        END
    CLOSE cur DEALLOCATE cur--关闭游标，释放游标
				
DROP TABLE tmp_check

对于游标一些优化建议

如果能不用游标，尽量不要使用游标
用完用完之后一定要关闭和释放
尽量不要在大量数据上定义游标
尽量不要使用游标上更新数据
尽量不要使用insensitive, static和keyset这些参数定义游标
如果可以，尽量使用FAST_FORWARD关键字定义游标
如果只对数据进行读取，当读取时只用到FETCH NEXT选项，则最好使用FORWARD_ONLY参数
declare open fetch while...fetch close deallocate

视图

视图可以看作定义在SQL Server上的虚拟表.视图正如其名字的含义一样，是另一种查看数据的入口.常规视图本身并不存储实际的数据，而仅仅存储一个Select语句和所涉及表的metadata,视图可以加密。

视图在SQL中可以分为三类

普通视图(Regular View)
索引视图(Indexed View)
分割视图(Partitioned View)

普通视图(Rugular View)

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

ENCRYPTION：视图是加密的，如果选上这个选项，则无法修改.创建视图的时候需要将脚本保存，否则再也不能修改了
SCHEMABINDING(架构绑定)：和底层引用到的表进行定义绑定。这个选项选上的话，则视图所引用到的表不能随便更改构架(比如列的数据类型)，如果需要更改底层表构架，则先drop或者alter在底层表之上绑定的视图.
VIEW_METADATA：这个是个很有意思的选项.正如这个选项的名称所指示，如果不选择，返回给客户端的metadata是View所引用表的metadata,如果选择了这个选项，则返回View的metadata.再通俗点解释，VIEW_METADATA可以让视图看起来貌似表一样。View的每一个列的定义等直接告诉客户端，而不是所引用底层表列的定义。

创建视图除了需要符合上面的语法规则之外，还有一些规则需要遵守：

在View中，除非有TOP关键字，否则不能用Order By子句（如果你一意孤行要用Order by,这里有个hack是使用Top 100 percent…..)
View在每个Schema中命名必须独一无二
View嵌套不能超过32层（其实实际工作中不超过2层)
Compute,compute by,INTO关键字不允许出现在View中
View不能建立在临时表上
View不能对全文索引进行查询

例子：

CREATE VIEW 100_studentNameAge
AS
SELECT TOP 100 name,age FROM student

索引视图（Indexed View）

视图+聚集索引=索引视图

聚集索引简单来说理解成主键。数据库中中的数据按照主键的顺序物理存储在表中，就像新华字典，默认是按照ABCD….的方式进行内容排序。ABCD….就相当于主键，避免了整表扫描从而提高了性能。因此一个表中只能有一个聚集索引。

原查询：

SELECT p.Name,s.OrderQty
FROM Production.Product p
 inner join Sales.SalesOrderDetail s
ON p.ProductID=s.ProductID

posted @ 2020-10-30 16:44 Neroi 阅读(495) 评论(0) 编辑收藏举报

刷新页面返回顶部

Neroi

学海无涯