20071220 sql2005 游标 cursor  rand
http://www.yippeesoft.com

declare   @stockmount  float
declare authors_cur cursor
for
select stockmount from foodsinfo$
for update of stockmount
open authors_cur /* open then cursor */
fetch next from authors_cur into @stockmount
while @@fetch_status = 0 /* loop the rows in the cursor */
begin
update foodsinfo$
set stockmount = cast(ceiling(rand() * 200) as int)
where current of authors_cur
/* get next row */
fetch next from authors_cur into @stockmount
end
deallocate authors_cur /* close the cursor */

例：下面是一个完整的定位更新的例子。
 declare @au_id int ( 11 ), @au_lname varchar ( 40 ), @au_fname varchar ( 20 )
 declare authors_cur cursor
 for
 select au_id,au_lname,au_fname from authors
 for update of au_id,aulname,au_fname
  open authors_cur /* open then cursor */
 fetch next from authors_cer into @au_id , @au_lname , @au_fname
  while @@fetch_status = 0 /* loop the rows in the cursor */
 begin
 if @au_id = \’ 172-32-1176 \’
 update authors
 set au_lname = \’ smith \’ ,au_fname = \’ jake \’
 where current of authors_cur
  /* get next row */
 fetch next from authors_cer into @au_id , @au_lname , @au_fname
 end
  deallocate authors_cur /* close the cursor */

 四、释放游标
 1 .关闭游标
 使用close命令关闭游标，在处理完游标中数据之后，发布关闭游标来释放数据结果集和定位于数据记录上的锁，close语句关闭游标但不释放游标占用的数据结构。如果准备在随后的使用中再次打开游标，则应使用open命令。
 语法： close 游标名称
 2 .释放游标
 在使用游标时各种针对游标的操作或者引用游标各或者引用指向游标的游标变量，当close命令关闭游标时并没有释放游标占用的数据结构。因此常使用deallocate命令删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。
 语法： deallocate 游标名称

declare       @UserID       int 
declare       cur_getUserID       cursor       for 
            select       [id]       from       user       where       flag=0 
open       cur_getUserID 
fetch       next       from       cur_getUserID       into       @UserID     
while(@@fetch_status       =       0) 
begin 

print       @UserID               –执行结果每次打印都是同一个ID 
update       [user]       set       flag=-99       where       id=@UserID 
–其它事务处理 

fetch       next       from       cur_getUserID       into       @UserID     
end 
close     cur_getUserID
deallocate     cur_getUserID

WHILE
　
该条件判断式是设定SQL陈述式或陈述式区块重复执行的条件。只要指定条件为TRUE，陈述式会一直重复执行。这就是所谓的WHILE循环（Loop），因为WHILE结构中的程序代码在循环中重复。语法显示如下：
WHILEBoolean_expression
SQL_statement &line; block_of_statements
[BREAK]SQL_statement &line; block_of_statements
[CONTINUE]
同上述的IF…ELSE，使用BEGIN和END来指定WHILE循环中陈述式的起始和结束。BREAK关键词则将导致从WHILE循环中跳出，然后接着执行WHILE循环结束后的陈述式。如果一个WHILE循环与另一个WHILE循环是巢状结构，BREAK关键词只会从被包含的WHILE循环中跳出，继续执行该循环外所有的的陈述式和循环。CONTINUE关键词指定WHILE循环重新执行BEGIN和END之间的陈述式，忽略CONTINUE之后的其它陈述式。
现在使用简单的WHILE循环重复执行UPDATE陈述式。WHILE循环的检测条件为：royalty资料行的平均值是否少于20。如果检测传回TRUE，所有royalty数据列的值都会增加5%。接着再次检测WHILE条件，重复更新直到royalty字段的平均值为20或更多。程序代码如下：
WHILE (SELECT AVG(royalty) FROM roysched) < 20
UPDATE roysched SET royalty = royalty * 1.05
GO
由于royalty数据行原来的平均值是15，因此在平均值达到20前，WHILE循环共执行了21次，直到检测条件回传值为FALSE时才停止。
现在看一个在WHILE循环中使用BREAK、CONTINUE、BEGIN和END的范例。我们将执行循环直到royalty的平均值达到25。但如果在循环中，royalty的最大值超过27，将中断循环，并在WHILE循环结束后加一个SELECT陈述式。下面是T-SQL程序代码：
WHILE (SELECT AVG(royalty) FROM roysched) < 25
BEGIN
UPDATE roysched SET royalty = royalty * 1.05
IF (SELECT MAX(royalty) FROM roysched) > 27
BREAK
ELSE
CONTINUE
END
SELECT MAX(royalty) AS "MAX royalty" FROM roysched
GO
数据表中已经存在的royalty值大于27，因此只执行一次循环，而royalty的平均值小于25%，因此UPDATE执行一次；接着IF陈述式检测并传回TRUE，所以执行BREAK，跳出WHILE循环；最后执行END关键词后的陈述式，也就是SELECT陈述式。
您还能使用巢状的WHILE循环，但记得BREAK或CONTINUE关键词只应用于呼叫它们的循环，而不是外部的WHILE循环。

declare @i int
set @i=1
while @i<30
begin
   insert into test (userid) values(@i)
   set @i=@i+1
end

 MSSQL中的随机函数

随机函数：rand()
在查询分析器中执行：select rand()，可以看到结果会是类似于这样的随机小数：0.36361513486289558，像这样的小数在实际应用中用得不多，一般要取随机数都会取随机整数。那就看下面的两种随机取整数的方法：

1、
A：select  floor(rand()*N)  —生成的数是这样的：12.0
B：select cast( floor(rand()*N) as int)  —生成的数是这样的：12

2、
A：select ceiling(rand() * N)  —生成的数是这样的：12.0
B：select cast(ceiling(rand() * N) as int)  —生成的数是这样的：12

    其中里面的N是一个你指定的整数，如100，可以看出，两种方法的A方法是带有.0这个的小数的，而B方法就是真正的整数了。
    大致一看，这两种方法没什么区别，真的没区别？其实是有一点的，那就是他们的生成随机数的范围：
方法1的数字范围：0至N-1之间，如cast( floor(rand()*100) as int)就会生成0至99之间任一整数
方法2的数字范围：1至N之间，如cast(ceiling(rand() * 100) as int)就会生成1至100之间任一整数
对于这个区别，看SQL的联机帮助就知了：
————————————————————————————
比较 CEILING 和 FLOOR

CEILING 函数返回大于或等于所给数字表达式的最小整数。FLOOR 函数返回小于或等于所给数字表达式的最大整数。例如，对于数字表达式 12.9273，CEILING 将返回 13，FLOOR 将返回 12。FLOOR 和 CEILING 返回值的数据类型都与输入的数字表达式的数据类型相同。
———————————————————————————-

历史博文

标签：, ,
十月 11, 2008 at 10:10 上午 by yippee 1,020 次
Category: Dev
Tags: , ,