SqlServer和MySQL游标学习

一 sqlserver游标使用

/*** 游标的使用  讲了这个多游标的优点，现在我们就亲自来揭开游标的神秘的面纱。  使用游标的顺序： 声名游标、打开游标、读取数据、关闭游标、删除游标。

1.3.1声明游标 最简单游标声明：DECLARE <游标名>CURSOR FOR<SELECT语句>；

其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询 例子：

[已表2 AddSalary为例子] Declare mycursor cursor for select * from AddSalary 这样我就对表AddSalary申明了一个游标mycursor

【高级备注】 DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT语句>

这里我说一下游标中级应用中的[INSENSITIVE]和[SCROLL] INSENSITIVE 表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。

对该游标的读取操作皆由临时表来应答。

因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。 另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。

a.在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；

b.使用OUTER JOIN；

c.所选取的任意表没有索引；

d.将实数值当作选取的列。 SCROLL 表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再 重开游标。

1.3.2 打开游标 非常简单，我们就打开刚才我们声明的游标mycursor OPEN mycursor

1.3.3读取数据 FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名  | @游标变量名 } [ INTO @变量名 [，…] ] 参数说明： NEXT   取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。 INTO @变量名[,…]  把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。 现在我们就取出mycursor游标的数据吧！ 当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列操作读取第1行数据：

Eg: Fetch next from mycursor 或则 Fetch first from mycursor 这样我就取出了游标里的数据，但是光光这样可不够，我们还需要将取出的数据赋给变量

--声明2个变量 declare @O_ID NVARCHAR(20)

declare @A_Salary float --将取出的值传入刚才声明的2个变量

Fetch next from mycursor into @ O_ID,@ A_Salary  

1.3.4关闭游标 CLOSE mycursor          

1.3.5删除游标 DEALLOCATE mycursor       

1.3.6 实例训练 **/ CREATE PROCEDURE PK_Test AS

 --声明2个变量  

declare @O_ID nvarchar(20)   

declare @A_Salary float   --声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同  

declare mycursor cursor for select O_ID,A_Salary from AddSalary 

 --打开游标

 open mycursor 

 --从游标里取出数据赋值到我们刚才声明的2个变量中

 fetch next from mycursor into @O_ID,@A_Salary 

 --判断游标的状态  -

- 0 fetch语句成功     

---1 fetch语句失败或此行不在结果集中   

 ---2 被提取的行不存在

 while (@@fetch_status=0)  begin 

 --显示出我们每次用游标取出的值    

print ‘游标成功取出一条数据‘   

  print @O_ID   

  print @A_Salary 

 --用游标去取下一条记录   

  fetch next from mycursor into @O_ID,@A_Salary  end  -

-关闭游标

 close mycursor

 --撤销游标

 DEALLOCATE mycursor

GO -

---------------------------------------------------------------

create  table  testyoubiao(

id int not null primary key ,

names nvarchar(50),

address nvarchar(50)

)

select top 0 * into testyoubiao2  FROM  testyoubiao

---testyoubiao表中有8条记录

create  PROCEDURE  testpp

as

begin

   declare @names nvarchar(20) 

  declare @address nvarchar(50) 

  declare mycursor cursor for select names,address from testyoubiao 

 --打开游标  

open mycursor   

--从游标里取出数据赋值到我们刚才声明的2个变量中

 fetch   next from  mycursor into @names,@address 

  --while (@@fetch_status=0)  

--begin   

--显示出我们每次用游标取出的值  

   print ‘1‘   

  print @names  

   print @address 

 --用游标去取下一条记录  

--  fetch  mycursor into @names,@address   

   insert into testyoubiao2(names,address) values(@names,@address)

 --end  

--关闭游标

 close mycursor

 --撤销游标

 DEALLOCATE mycursor end

exec testpp

--这样执行的结果testyoubiao2表只有一条记录 就是说没有循环就是只去第一条

--fetch   next from  mycursor into @names,@address  "next from"这个可以不写结果是一样的  

--------------------------------------------------------------------

  create  PROCEDURE  testpp

as begin

 declare @names nvarchar(20) 

declare @address nvarchar(50) 

 declare mycursor cursor for select names,address from testyoubiao 

 --打开游标

 open mycursor 

 --从游标里取出数据赋值到我们刚才声明的2个变量中

 fetch  next  from   mycursor into @names,@address    

-- insert into testyoubiao2(names,address) values(@names,@address) 会增加一条数据与最后一条相同  

while (@@fetch_status=0)

 begin   

--显示出我们每次用游标取出的值

    print ‘1‘   

  print @names  

   print @address 

 --用游标去取下一条记录  

fetch next  from  mycursor into @names,@address    

  insert into testyoubiao2(names,address) values(@names,@address)

 end  

--关闭游标  

close mycursor  

--撤销游标

 DEALLOCATE mycursor end

exec testpp

select * from testyoubiao2

truncate  table    testyoubiao2

drop  PROCEDURE  testpp    

 #----------------------------------------------------------------------

二 MySQL游标使用

DELIMITER $$

create PROCEDURE testp16(in $id int )

BEGIN declare  _uname varchar(20) ;

declare _uaddress VARCHAR(20);

DECLARE D_cursor CURSOR FOR select uname,uaddress from testa where id=$id;

OPEN D_cursor; FETCH NEXT FROM D_cursor  into  _uname, _uaddress;

CLOSE D_cursor;

insert into testd(uname,uaddress)values(_uname,_uaddress);

end;

-----------------------------------------------------------

DELIMITER $$

create PROCEDURE testp17()

BEGIN  declare  _uname varchar(20) ;

declare _uaddress VARCHAR(20);

declare couts int ;

declare i int default 0 ;

DECLARE D_cursor CURSOR FOR select uname,uaddress from testa;

 set couts =(select count(id)from testa);

OPEN D_cursor;

-- FETCH NEXT FROM D_cursor  into  _uname, _uaddress;

-- insert into testd(uname,uaddress)values(_uname,_uaddress);这个地方加上这两句数据时对的但是会有1329 错误提示

while i<couts DO -- 1329 - No data - zero rows fetched, selected, or processed 超出范围了

FETCH NEXT FROM D_cursor  into  _uname, _uaddress;

insert into testd(uname,uaddress)values(_uname,_uaddress);

set i=1+i;

end while;

-- FETCH NEXT FROM D_cursor  into  _uname, _uaddress;

-- insert into testd(uname,uaddress)values(_uname,_uaddress);这个地方加上这两句数据时对的但是会有1329 错误提示 CLOSE D_cursor; select i; end;

select * from testa

CALL testp17();--1329 - No data - zero rows fetched, selected, or processed 超出范围了

CALL testp18()

select * from testd

TRUNCATE table testd

-----------------------------------------

/*为下面的理解说明 定义条件和处理程序 定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。

本小节中将详细讲解如何定义条件和处理程序。

1．定义条件 MySQL中可以使用DECLARE关键字来定义条件。

其基本语法如下：

DECLARE  condition_name  CONDITION  FOR  condition_value  condition_value: 

SQLSTATE [VALUE] sqlstate_value | mysql_error_code

其中，condition_name参数表示条件的名称；

condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。

例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

【示例14-6】

下面定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find。可以用两种不同的方法来定义，代码如下：

//方法一：使用sqlstate_value  DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  ‘42S02‘ ; 

//方法二：使用mysql_error_code  DECLARE  can_not_find  CONDITION  FOR  1146 ; 2．定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。

其基本语法如下： DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement  handler_type:   

   CONTINUE | EXIT | UNDO  condition_value:     

SQLSTATE [VALUE] sqlstate_value | condition_name  | SQLWARNING         | NOT FOUND  | SQLEXCEPTION  | mysql_error_code  

   其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；

EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。

但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。 condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

【示例14-7】 下面是定义处理程序的几种方式。

代码如下：

//方法一：捕获sqlstate_value  DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02‘ SET @info=‘CAN NOT FIND‘; 

//方法二：捕获mysql_error_code  DECLARE CONTINUE HANDLER FOR 1146 SET @info=‘CAN NOT FIND‘; 

//方法三：先定义条件，然后调用  DECLARE  can_not_find  CONDITION  FOR  1146 ; 

DECLARE CONTINUE HANDLER FOR can_not_find SET @info=‘CAN NOT FIND‘; 

//方法四：使用SQLWARNING  DECLARE EXIT HANDLER FOR SQLWARNING SET @info=‘ERROR‘; 

//方法五：使用NOT FOUND  DECLARE EXIT HANDLER FOR NOT FOUND SET @info=‘CAN NOT FIND‘; 

//方法六：使用SQLEXCEPTION  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=‘ERROR‘;

上述代码是6种定义处理程序的方法。

第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。

第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。

第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。

第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。

第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息。

第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。

*/ -----------------------------------

DELIMITER $$

CREATE PROCEDURE testp18()

BEGIN

 -- 遍历数据结束标志  

DECLARE done INT DEFAULT FALSE;  

-- 需要定义接收游标数据的变量  

declare  _uname varchar(20) ;  

declare _uaddress VARCHAR(20);  

-- 游标  

DECLARE D_cursor CURSOR FOR select uname,uaddress from testa;  

/* 遍历数据结束标志  

--DECLARE done INT DEFAULT FALSE;

-- #放到这里出现这个错误[Err] 1337 - Variable or condition declaration after cursor or handler declaration   根据错误的提示信息，游标定义需在变量/条件后，HANDLER 前。   */  

-- 将结束标志绑定到游标  

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  

-- 打开游标

  OPEN D_cursor;

  --FETCH  D_cursor INTO _uname, _uaddress;  

--insert into testd(uname,uaddress)values(_uname,_uaddress); 这里多加这两句不影响后面的数据  

-- 开始循环  

read_loop: LOOP    

-- 提取游标里的数据，这里只有一个，多个的话也一样；   

  FETCH next from  D_cursor INTO _uname, _uaddress;-- next from可以省略    

-- 声明结束的时候   

  IF done THEN     

    LEAVE read_loop;   

  END IF;   

  -- 这里做你想做的循环的事件

    insert into testd(uname,uaddress)values(_uname,_uaddress);

  END LOOP;  

-- FETCH  D_cursor INTO _uname, _uaddress;这里如果加上这两句会增加一条数据与最后的数据相同  

--insert into testd(uname,uaddress)values(_uname,_uaddress);  

-- 关闭游标

  CLOSE D_cursor;

END

-- testp17()和testp18()两个效果是一样的 -

----------------------------------------

SqlServer和MySQL游标学习,古老的榕树,5-wow.com