请教存储过程中的循环出现的问题
fb数据库的存储过程,i是一个变量,K是一个传入的参数,:tzhuyi也是一个传入的日期参数,以下代码希望在一段时期内插入或更新rj表的zhuyi字段。
create or alter procedure NEW_PROCEDURE (
TZHUYI date,
K integer,
ZHUYI varchar(10),
JILU varchar(10),
SHIJIAN date)
as
declare variable I integer;
begin
i=0;
while (i<=k) do
begin
if (exists (select shijian from rj where shijian=:tzhuyi+i)) then //这里出了问题。
insert into rj (shijian, jilu, zhuyi) values (:shijian,:jilu,:zhuyi);
else
update rj
set zhuyi=:zhuyi;
i=i+1;
end
end
Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
I.
At line 13, column 58.
改成下面这样,还是不行。
create or alter procedure NEW_PROCEDURE (
TZHUYI date,
K integer,
ZHUYI varchar(10),
JILU varchar(10),
SHIJIAN date not null)
as
declare variable I integer;
begin
i=0;
while (i<=k) do
begin
if (exists (select shijian from rj where shijian=:Tzhuyi+i)) then
insert into rj (shijian, jilu, zhuyi) values (:shijian,:jilu,:zhuyi);
else
update rj
set zhuyi=:zhuyi
where
shijian=:shijian;
i=i+1;
end
end
Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
I.
At line 13, column 58.