在存储过程内部定义了一个变量,想在sql语句中使用它的值,是这样写的:
CREATE OR REPLACE PROCEDURE "GETMAXFLUXINWITHHYID" (
startTime in date,endTime in date
)
as
minHyId number;
begin
WHILE (minHyId<=20) LOOP
SELECT * FROM TABLE1 WHERE HY_ID=minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
minHyId:=minHyId+1
END LOOP;
end;可编译总是要提示错误。请问应该怎么写才正确?
不知道存储过程中的变量传递可以采用哪几种写法?
CREATE OR REPLACE PROCEDURE "GETMAXFLUXINWITHHYID" (
startTime in date,endTime in date
)
as
minHyId number;
begin
WHILE (minHyId<=20) LOOP
SELECT * FROM TABLE1 WHERE HY_ID=minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
minHyId:=minHyId+1
END LOOP;
end;可编译总是要提示错误。请问应该怎么写才正确?
不知道存储过程中的变量传递可以采用哪几种写法?
set @str='select * from tablename'
exec(@str)
startTime in date,endTime in date
)
as
minHyId number;
inum number;
begin
WHILE (minHyId<=20) LOOP
SELECT count(*) into inum FROM TABLE1 WHERE HY_ID=minHyId AND PROBE_TIME BETWEEN startTime AND endTime;--查询结果没有保存到变量里面
minHyId:=minHyId+1; --少分号
END LOOP;
end;
declare @str varchar(1000)
set @str='select * from tablename'
exec(@str)
--
彩霞你这是sqlserver语法阿
WHILE (minHyId<=20) LOOP
SELECT * FROM TABLE1 WHERE HY_ID=minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
minHyId:=minHyId+1
END LOOP;
---
select 前面or后面没有into阿
肯定不对SELECT * FROM TABLE1 WHERE HY_ID=minHyId AND PROBE_TIME BETWEEN startTime AND endTime;
这样写就用到了输入变量