sql循環語句怎麼寫

Transact-SQL附加語言Transact-SQL附加語言元素不是SQL的標準內容，而是Transact-SQL語言爲了編程方便而增加的語言元素。

這些語言元素包括變量、運算符、函數、流程控制語句和註釋等內容。1. 變量變量對1種語言來講是必不可少的組成部份。

Transact-SQL語言允許使用兩種變量：1種是用戶自己定義的局部變量（Local Variable），另外一種是系統提供的全局變量（Global Variable）。(1) 局部變量局部變量使用戶自己定義的變量，它的作用範圍近在程序內部。

通常只能在1個批處理中或存儲進程中使用，用來存儲從表中查詢到的數據，或當作程序履行進程中暫存變量使用。局部變量使用DECLARE語句定義，並且指定變量的數據類型，然後可使用SET或SELECT語句爲變量初始化；局部變量必須以“@”開頭，而且必須先聲明後使用。

其聲明格式以下：DECLARE @變量名 變量類型[，@變量名 變量類型…]其中變量類型可以是SQL Server 2000支持的所有數據類型，也能夠是用戶自定義的數據類型。局部變量不能使用“變量=變量值”的格式進行初始化，必須使用SELECT或SET語句來設置其初始值。

初始化格式以下：SELECT @局部變量=變量值SET @局部變量=變量值。

**************

修改了一下：

**************

declare @month_tmp varchar(2);

declare @day_tmp varchar(2);

set @month_tmp = '1';

set @day_tmp = '1';

while(@month_tmp < '13')

begin

while(@day_tmp < '30')

begin

select * from table1 where month=@month_tmp and day=@day_tmp

set @day_tmp = @day_tmp + 1

end

set @month_tmp = @month_tmp + 1

set @day_tmp = 1

end

*********************************************************

select * from table1 where

month in('1','2','3','4','5','6','7','8','9','10','11','12'

and

day in('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30');

---

以上，希望對你有所幫助。

declare

sql_tem Varchar2(4000);

a number;

b number;

i number;

begin

a := 1;

for i in 1 .. 3 loop

b := a + 4;

sql_tem := 'insert into A2 (ID,NAME) (select ID,NAME from A1 WHERE ROWNUM between :1 and :2)';

EXECUTE IMMEDIATE sql_tem

USING a, b;

commit;

a := a + 5;

end loop;

end;

試試上面的代碼看一下能不能滿意你的要求先唄。

tsql（就是sqlserver的），親測成功：-----------------------------------declare @res varchar(100)declare @c varchar(10)set @res=''declare c1 cursor for select B from topen c1fetch next from c1 into @cwhile @@fetch_status=0begin set @res=@res+@c fetch next from c1 into @cendclose c1deallocate c1select @res。

DECLARE @IDS VARCHAR(100)

SET @IDS = '100,200,30,8,3459,1'

DECLARE @ID INT

DECLARE @INDEX INT

WHILE LEN(@IDS) > 0

BEGIN

SET @INDEX = CHARINDEX(',',@IDS,1)

IF @INDEX > 1

BEGIN

SET @ID = CAST(LEFT(@IDS,@INDEX - 1) AS INT)

SET @IDS = SUBSTRING(@IDS,@INDEX+1, LEN(@IDS) - @INDEX)

END

ELSE

BEGIN

SET @ID = CAST(@IDS AS INT)

SET @IDS = NULL

END

INSERT INTO TABLES(ID) VALUES(@ID)

SET @INDEX = CHARINDEX(',',@IDS,1)

END

create table a_lyh_test

as

select 'A' as "字段1" , 500 as "字段2" from dual

union all

select 'B' as "字段1" , 300 as "字段2" from dual

union all

select 'B' as "字段1" ,400 as "字段2" from dual

union all

select 'B' as "字段1" , 600 as "字段2" from dual

union all

select 'A' as "字段1" , 200 as "字段2" from dual

;

select f.字段1

,ltrim(max(sys_connect_by_path(f.字段2,','))

keep (dense_rank last order by f.pnum),',') as 字段2

from

(

select t.字段1

,t.字段2

,row_number() over(partition by t.字段1 order by t.字段1) as pnum

,row_number() over(partition by t.字段1 order by t.字段1)-1 as lnum

from

(

select a.字段1,a.字段2

from a_lyh_test a

) t

) f

group by f.字段1

connect by f.lnum = prior f.pnum and f.字段1 = prior f.字段1

start with f.pnum = 1;

可以使用遊標。MS SQL語句如下：

--定義一個接收數值及一個累加數值的臨時變量

DECLARE @level INT,

@levelcount INT

SET @levelcount = 0

--定義遊標，表名及列名根據實際進行修改

DECLARE cus_count CURSOR FOR

SELECT level FROM dbo.grouptemp

--打開遊標

OPEN cus_count

--定位到下一行。打開後未定位遊標定位到首行

FETCH NEXT FROM cus_count INTO @level

--循環遊標，條件爲小於某個數值並且遊標循環未結束。@@FETCH_STATUS = 0表示遊標未循環結束。

WHILE (@levelcount < 100) AND (@@FETCH_STATUS = 0)

BEGIN

SET @levelcount = @levelcount+@level

FETCH NEXT FROM cus_count INTO @level

END

--關閉遊標

CLOSE cus_count

--釋放遊標

DEALLOCATE cus_count

--數值打印出來以便調試，正式方法中可不打印

PRINT @levelcount