<p>使用SQL的Agent可以执行计划任务,把写好的SQL语句放在计划任务里,可以达到奇妙的效果,如定时备份数据,定时执行特定操作等等,当涉及循环操作很多条数据时,这里就要使用游标了,当然SQL中也有循环语句,如使用While。不过while的功能只能实现一般的操作,游标的功能更为强大些,可在一个指定的一个集合内循环操作数据,实现动态操作,那就更牛了,呵呵,以下资料供存档用。</p> <p>WHILE<br />设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真,就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。</p> <p>语法<br />WHILE Boolean_expression<br /> { sql_statement | statement_block }<br /> [ BREAK ]<br /> { sql_statement | statement_block }<br /> [ CONTINUE ]</p> <p>参数<br />Boolean_expression</p> <p>返回 TRUE 或 FALSE 的表达式。如果布尔表达式中含有 SELECT 语句,必须用圆括号将 SELECT 语句括起来。</p> <p>{sql_statement | statement_block}</p> <p>Transact-SQL 语句或用语句块定义的语句分组。若要定义语句块,请使用控制流关键字 BEGIN 和 END。</p> <p>BREAK</p> <p>导致从最内层的 WHILE 循环中退出。将执行出现在 END 关键字后面的任何语句,END 关键字为循环结束标记。</p> <p>CONTINUE</p> <p>使 WHILE 循环重新开始执行,忽略 CONTINUE 关键字后的任何语句。</p> <p>注释<br />如果嵌套了两个或多个 WHILE 循环,内层的 BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句,然后下一个外层循环重新开始执行。</p> <p>示例</p> <p>declare @i int<br />set @i=1<br />while @i<30<br />begin<br />insert into test (userid) values(@i)<br />set @i=@i+1<br />end</p> <p>------------------------------------------------------------</p> <p>while 条件<br />begin<br />执行操作<br />set @i=@i+1<br />end</p> <p>A. 在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE<br />在下例中,如果平均价格少于 $30,WHILE 循环就将价格加倍,然后选择最高价。如果最高价少于或等于 $50,WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50,然后退出 WHILE 循环并打印一条消息。</p> <p>USE pubs<br />GO<br />WHILE (SELECT AVG(price) FROM titles) < $30<br />BEGIN<br /> UPDATE titles<br /> SET price = price * 2<br /> SELECT MAX(price) FROM titles<br /> IF (SELECT MAX(price) FROM titles) > $50<br /> BREAK<br /> ELSE<br /> CONTINUE<br />END<br />PRINT 'Too much for the market to bear'</p> <p>B. 在带有游标的过程中使用 WHILE<br />以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。下例中,该 WHILE 结构测试用于游标的函数 @@FETCH_STATUS 的返回值。因为 @@FETCH_STATUS 可能返回 –2、-1 或 0,所以,所有的情况都应进行测试。如果某一行在开始执行此存储过程以后从游标结果中删除,将跳过该行。成功提取 (0) 后将执行 BEGIN...END 循环内部的 SELECT 语句。</p> <p><strong>单变量循环</strong><br />USE pubs<br />DECLARE tnames_cursor CURSOR<br />FOR<br /> SELECT TABLE_NAME<br /> FROM INFORMATION_SCHEMA.TABLES<br />OPEN tnames_cursor<br />DECLARE @tablename sysname<br />--SET @tablename = 'authors'<br />FETCH NEXT FROM tnames_cursor INTO @tablename<br />WHILE (@@FETCH_STATUS <> -1)<br />BEGIN<br /> IF (@@FETCH_STATUS <> -2)<br /> BEGIN <br /> SELECT @tablename = RTRIM(@tablename)<br /> EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '<br /> + @tablename )<br /> PRINT ' '<br /> END<br /> FETCH NEXT FROM tnames_cursor INTO @tablename<br />END<br />CLOSE tnames_cursor<br />DEALLOCATE tnames_cursor</p> <p><strong>多变量循环</strong></p> <p>CREATE PROCEDURE my_FeeCount AS<br />declare @到期时间 char(10)<br />declare @片区 char(20)<br />declare @缴费用户数 char(10)<br />declare @sql char(2000)</p> <p>declare cur_data cursor for<br /> select convert(varchar(10),到期时间,120) as 到期时间 ,片区,count(distinct main_id) as 缴费用户数<br /> from V_aipu_fee where 提交时间>=convert(varchar(10),getdate()-90,120) and 提交时间<convert(varchar(10),getdate()+1-90,120)<br /> and 收费类型='续费收费'<br /> Group by convert(varchar(10),到期时间,120),片区<br /> order by convert(varchar(10),到期时间,120)<br />open cur_data</p> <p>fetch next from cur_data into @到期时间,@片区,@缴费用户数 <br />while(@@fetch_status = 0)<br />begin<br /> set @sql='update '+RTRIM(@片区)+'实收='+RTRIM(@片区)+'实收+'+RTRIM(@缴费用户数)+' where 收费日期='''+RTRIM(@到期时间)+''''</p> <p> print @sql</p> <p> fetch next from cur_data into @到期时间,@片区,@缴费用户数<br />end</p> <p>close cur_data<br />deallocate cur_data</p> Loading... <p>使用SQL的Agent可以执行计划任务,把写好的SQL语句放在计划任务里,可以达到奇妙的效果,如定时备份数据,定时执行特定操作等等,当涉及循环操作很多条数据时,这里就要使用游标了,当然SQL中也有循环语句,如使用While。不过while的功能只能实现一般的操作,游标的功能更为强大些,可在一个指定的一个集合内循环操作数据,实现动态操作,那就更牛了,呵呵,以下资料供存档用。</p> <p>WHILE<br />设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真,就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。</p> <p>语法<br />WHILE Boolean_expression<br /> { sql_statement | statement_block }<br /> [ BREAK ]<br /> { sql_statement | statement_block }<br /> [ CONTINUE ]</p> <p>参数<br />Boolean_expression</p> <p>返回 TRUE 或 FALSE 的表达式。如果布尔表达式中含有 SELECT 语句,必须用圆括号将 SELECT 语句括起来。</p> <p>{sql_statement | statement_block}</p> <p>Transact-SQL 语句或用语句块定义的语句分组。若要定义语句块,请使用控制流关键字 BEGIN 和 END。</p> <p>BREAK</p> <p>导致从最内层的 WHILE 循环中退出。将执行出现在 END 关键字后面的任何语句,END 关键字为循环结束标记。</p> <p>CONTINUE</p> <p>使 WHILE 循环重新开始执行,忽略 CONTINUE 关键字后的任何语句。</p> <p>注释<br />如果嵌套了两个或多个 WHILE 循环,内层的 BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句,然后下一个外层循环重新开始执行。</p> <p>示例</p> <p>declare @i int<br />set @i=1<br />while @i<30<br />begin<br />insert into test (userid) values(@i)<br />set @i=@i+1<br />end</p> <p>------------------------------------------------------------</p> <p>while 条件<br />begin<br />执行操作<br />set @i=@i+1<br />end</p> <p>A. 在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE<br />在下例中,如果平均价格少于 $30,WHILE 循环就将价格加倍,然后选择最高价。如果最高价少于或等于 $50,WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50,然后退出 WHILE 循环并打印一条消息。</p> <p>USE pubs<br />GO<br />WHILE (SELECT AVG(price) FROM titles) < $30<br />BEGIN<br /> UPDATE titles<br /> SET price = price * 2<br /> SELECT MAX(price) FROM titles<br /> IF (SELECT MAX(price) FROM titles) > $50<br /> BREAK<br /> ELSE<br /> CONTINUE<br />END<br />PRINT 'Too much for the market to bear'</p> <p>B. 在带有游标的过程中使用 WHILE<br />以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。下例中,该 WHILE 结构测试用于游标的函数 @@FETCH_STATUS 的返回值。因为 @@FETCH_STATUS 可能返回 –2、-1 或 0,所以,所有的情况都应进行测试。如果某一行在开始执行此存储过程以后从游标结果中删除,将跳过该行。成功提取 (0) 后将执行 BEGIN...END 循环内部的 SELECT 语句。</p> <p><strong>单变量循环</strong><br />USE pubs<br />DECLARE tnames_cursor CURSOR<br />FOR<br /> SELECT TABLE_NAME<br /> FROM INFORMATION_SCHEMA.TABLES<br />OPEN tnames_cursor<br />DECLARE @tablename sysname<br />--SET @tablename = 'authors'<br />FETCH NEXT FROM tnames_cursor INTO @tablename<br />WHILE (@@FETCH_STATUS <> -1)<br />BEGIN<br /> IF (@@FETCH_STATUS <> -2)<br /> BEGIN <br /> SELECT @tablename = RTRIM(@tablename)<br /> EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '<br /> + @tablename )<br /> PRINT ' '<br /> END<br /> FETCH NEXT FROM tnames_cursor INTO @tablename<br />END<br />CLOSE tnames_cursor<br />DEALLOCATE tnames_cursor</p> <p><strong>多变量循环</strong></p> <p>CREATE PROCEDURE my_FeeCount AS<br />declare @到期时间 char(10)<br />declare @片区 char(20)<br />declare @缴费用户数 char(10)<br />declare @sql char(2000)</p> <p>declare cur_data cursor for<br /> select convert(varchar(10),到期时间,120) as 到期时间 ,片区,count(distinct main_id) as 缴费用户数<br /> from V_aipu_fee where 提交时间>=convert(varchar(10),getdate()-90,120) and 提交时间<convert(varchar(10),getdate()+1-90,120)<br /> and 收费类型='续费收费'<br /> Group by convert(varchar(10),到期时间,120),片区<br /> order by convert(varchar(10),到期时间,120)<br />open cur_data</p> <p>fetch next from cur_data into @到期时间,@片区,@缴费用户数 <br />while(@@fetch_status = 0)<br />begin<br /> set @sql='update '+RTRIM(@片区)+'实收='+RTRIM(@片区)+'实收+'+RTRIM(@缴费用户数)+' where 收费日期='''+RTRIM(@到期时间)+''''</p> <p> print @sql</p> <p> fetch next from cur_data into @到期时间,@片区,@缴费用户数<br />end</p> <p>close cur_data<br />deallocate cur_data</p> 最后修改:2012 年 03 月 02 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 支持就是力量
1 条评论
很强大,支持一下!!