类型:

  1.家常便饭游标   唯有NEXT操作

  2.轮转游标 有二种操作

       
 sqlserver的上学成才之路,每八个本事的就学进度都是值得让人认知的,今后百度上关于sqlser的素材相当多,然则都太杂,希望能为我们大饱眼福一点简易易懂的干货,跟大家一道发展学习。

明日境遇一同有关遍布式事务错误的案例,如下所示,执行SQL脚本时报错,
错误音信具体如下所示:

1.不以为奇游标

DECLARE @username varchar(20),@UserId varchar(100)
DECLARE cursor_name CURSOR FOR --定义游标
    SELECT TOP 10 UserId,UserName FROM UserInfo
    ORDER BY UserId DESC
OPEN cursor_name --打开游标
FETCH NEXT FROM cursor_name INTO  @UserId,@username  --抓取下一行游标数据
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '用户ID:'+@UserId+'            '+'用户名:'+@username
        FETCH NEXT FROM cursor_name INTO @UserId,@username
    END
CLOSE cursor_name --关闭游标
DEALLOCATE cursor_name --释放游标

结果:

用户ID:zhizhi            用户名:邓鸿芝
用户ID:yuyu            用户名:魏雨
用户ID:yujie            用户名:李玉杰
用户ID:yuanyuan            用户名:王梦缘
用户ID:YOUYOU            用户名:lisi
用户ID:yiyiren            用户名:任毅
用户ID:yanbo            用户名:王艳波
用户ID:xuxu            用户名:陈佳绪
用户ID:xiangxiang            用户名:李庆祥
用户ID:wenwen            用户名:魏文文

 一、建表

 

2.轮转游标

--带SCROLL选项的游标
SET NOCOUNT ON
DECLARE C SCROLL CURSOR FOR  --SCORLL 后,有了更多的游标操作(滚动游标)
    SELECT TOP 10 UserId,UserName FROM UserInfo
    ORDER BY UserId DESC
OPEN C 
FETCH LAST FROM C   --最后一行的数据,并将当前行为指定行
FETCH ABSOLUTE 4 FROM C  --从第一行开始的第4行数据,并将当前行为指定行  这里的n可正可负,n>0 往下翻,n<0 往上翻
FETCH RELATIVE 3 FROM C  --相对于当前行的后3行数据,并将当前行为指定行  这里的n可正可负
FETCH RELATIVE -2 FROM C --相对于当前行的前2行数据,并将当前行为指定行
FETCH PRIOR FROM C   ----相对于当前行的前1行数据
FETCH FIRST FROM C   --刚开始第一行的数据,并将当前行为指定行
FETCH NEXT FROM C   --相对于当前行的后1行数据

CLOSE C
DEALLOCATE C

结果(能够参见第三个结果剖判):

图片 1

    1、创建表stu_paper(各类常用的档期的顺序的字段都有)

[OLE/DB
provider returned message: 新业务不能够注册到钦命的业务处理器中。
]

具体FETCH用法:

FETCH   
          [ [ NEXT | PRIOR | FIRST | LAST   
                    | ABSOLUTE { n | @nvar }   
                    | RELATIVE { n | @nvar }   
               ]   
               FROM   
          ]   
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }   
[ INTO @variable_name [ ,...n ] ]
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'stu_paper' AND TYPE = 'U')
    BEGIN
    --问卷表
    CREATE TABLE stu_paper(
        P_ID int IDENTITY(1,1) primary key NOT NULL,
        TITLE VARCHAR(200) default '' NOT NULL,                  
        START_TIME datetime default getdate() NOT NULL,   
        SHOW_CONTENT int default 0 NOT NULL,             
        USER_NUM bigint default 0  NOT NULL,         
        FLAG int default 0 NOT NULL ,             
        NTAG1 numeric(10,4),
    )
    END
    GO

OLE
DB error trace [OLE/DB Provider ‘SQLOLEDB’
ITransactionJoin::JoinTransaction returned 0x8004d00a].

Arguments

NEXT
Returns the result row immediately following the current row and
increments the current row to the row returned. If FETCH NEXT is the
first fetch against a cursor, it returns the first row in the result
set. NEXT is the default cursor fetch option.

PRIOR
Returns the result row immediately preceding the current row, and
decrements the current row to the row returned. If FETCH PRIOR is the
first fetch against a cursor, no row is returned and the cursor is left
positioned before the first row.

FIRST
Returns the first row in the cursor and makes it the current row.

LAST
Returns the last row in the cursor and makes it the current row.

ABSOLUTE { n| @nvar}
If n or @nvar is positive, returns the row n rows from the front of the
cursor and makes the returned row the new current row. If n or @nvar is
negative, returns the row n rows before the end of the cursor and makes
the returned row the new current row. If n or @nvar is 0, no rows are
returned. n must be an integer constant and @nvar must be smallint,
tinyint, or int.

RELATIVE { n| @nvar}
If n or @nvar is positive, returns the row n rows beyond the current row
and makes the returned row the new current row. If n or @nvar is
negative, returns the row n rows prior to the current row and makes the
returned row the new current row. If n or @nvar is 0, returns the
current row. If FETCH RELATIVE is specified with n or @nvar set to
negative numbers or 0 on the first fetch done against a cursor, no rows
are returned. n must be an integer constant and @nvar must be smallint,
tinyint, or int.

GLOBAL
Specifies that cursor_name refers to a global cursor.

cursor_name
Is the name of the open cursor from which the fetch should be made. If
both a global and a local cursor exist with cursor_name as their name,
cursor_name to the global cursor if GLOBAL is specified and to the
local cursor if GLOBAL is not specified.

@cursor_variable_name
Is the name of a cursor variable referencing the open cursor from which
the fetch should be made.

INTO @variable_name[ ,…n]
Allows data from the columns of a fetch to be placed into local
variables. Each variable in the list, from left to right, is associated
with the corresponding column in the cursor result set. The data type of
each variable must either match or be a supported implicit conversion of
the data type of the corresponding result set column. The number of
variables must match the number of columns in the cursor select list.

 

   2、创建表stu

Msg
7391, Level 16, State 1, Procedure UpdatePermissionSystem, Line
18

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'stu' AND TYPE = 'U')
    BEGIN
    --问卷表
    CREATE TABLE stu(
        P_ID int IDENTITY(1,1) primary key NOT NULL,
        TITLE VARCHAR(200) default '' NOT NULL,                  
        START_TIME datetime default getdate() NOT NULL,   
        SHOW_CONTENT int default 0 NOT NULL,             
        USER_NUM bigint default 0  NOT NULL,         
        FLAG int default 0 NOT NULL ,             
        NTAG1 numeric(10,4),
    )
    END
    GO

The
operation could not be performed because the OLE DB provider ‘SQLOLEDB’
was unable to begin a distributed transaction.

二、为表stu_paper扩大字段

 

1 alter table dbo.stu_paper add age int default 0 not null;
2 alter table dbo.stu_paper add name VARCHAR(200) default '' not null;

 
检查了数据库选项以及DTC配置,最后发现是“事务管理器通讯”的挑选没有选用“不供给实行表明(No
Authentication Required)”,而是勾选了“须求相互实行身份验证(Mutual
Authentication Required)”导致.
选取了“不供给进行验证”
后,重启DTC服务,就可以缓和那些荒唐。

三、修改表stu_paper中的字段name 的尺寸为varchar(256)

 

1 alter table stu_paper  alter column name varchar(256);

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注