sql循环语句怎么写
- 综合知识
- 关注:1.94W次
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 @局部变量=变量值。
2. SQL 语句简单的循环怎么写啊**************
修改了一下:
**************
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');
---
以上,希望对你有所帮助。
3. oracle sql怎么写循环语句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;
试试上面的代码看一下能不能满意你的要求先呗。
4. 看下这个SQL循环语句怎么写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。
5. 帮忙写个SQL循环语句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
6. sql中类似循环方法语句怎么写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;
7. SQL 循环语句可以使用游标。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
- 文章版权属于文章作者所有,转载请注明 https://dongkepu.com/zonghezhishi/5o5nw0.html