各位，大牛，大侠请帮个忙，把下面的SQLServer（注：下面的SQLServer是错误的，大家看逻辑就可以）转化成Oracle 谢谢！
各位，大牛，大侠，高手，请帮个忙，把下面的SQLServer（注：下面的SQLServer是错误的，大家看逻辑就可以）转化成Oracle 谢谢！
create procedure upTabValue
@tabName varchar(50),
@ID int ,
@curTime datetime
AS
declare @curWHeight float
declare @preWHeight float
BEGIN
set @curWHeight = (select waterHeight from @tabName where ID = @ID);
set @preWHeight = (select waterHeight from @tabName where ID = @ID -1);
set @time = (select getInfoTime from newestData where WellCode=@tabName);
if @preWHeight is not NULL and @curWHeight is NOT NULL and abs(@curWHeight-@preWHeight) > 2
BEGIN
update @tabName set waterHeight = @preWHeight where ID = @ID;
if @time = @curTime
update newestData set waterHeight = @preWHeight;
END
END
[解决办法]
怎么删除不掉呢，你上面的写法应该没有错啊
[解决办法]
-- 原代码：
Oracle 谢谢！
create procedure upTabValue
@tabName varchar(50),
@ID int ,
@curTime datetime
AS
declare @curWHeight float
declare @preWHeight float
BEGIN
set @curWHeight = (select waterHeight from @tabName where ID = @ID);
set @preWHeight = (select waterHeight from @tabName where ID = @ID -1);
set @time = (select getInfoTime from newestData where WellCode=@tabName);
if @preWHeight is not NULL and @curWHeight is NOT NULL and abs(@curWHeight-@preWHeight) > 2
BEGIN
update @tabName set waterHeight = @preWHeight where ID = @ID;
if @time = @curTime
update newestData set waterHeight = @preWHeight; -- 应该有where 条件吧？否则整个表的数据都更新的哦！
END
END
-- 对应Oracle代码
CREATE OR REPLACE PROCEDURE uptabvalue(
i_tabname varchar2,
i_id number,
i_curtime date
)
is
v_curWHeight number(38,4);
v_preWHeight number(38,4);
v_time date;
BEGIN
v_curWHeight := null;
v_curWHeight := null;
v_time := null;
BEGIN
EXECUTE IMMEDIATE 'SELECT waterHeight from '
------解决方案--------------------
i_tabname
[解决办法]
' where ID = :i_id1' INTO v_curWHeight USING i_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'SELECT waterHeight from '
[解决办法]
i_tabname
[解决办法]
' where ID = :i_id2' INTO v_preWHeight USING i_id-1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'SELECT getInfoTime from newestData where WellCode=:i_tabname' INTO v_time USING i_tabname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
IF v_preWHeight is not null AND v_curWHeight IS NOT NULL AND abs(v_curWHeight-v_preWHeight) > 2 THEN
BEGIN
EXECUTE IMMEDIATE 'UPDATE '
[解决办法]
i_tabname
[解决办法]
' set waterHeight = '
[解决办法]
to_char(v_preWHeight)
[解决办法]
' where id = :i_id1' USING i_id;
IF v_time=i_curtime THEN
UPDATE newestData set waterHeight = uptabvalue.v_preWHeight; -- 应该有where 条件吧？否则整个表的数据都更新的哦！
END IF;
COMMIT;
END;
END IF;
END;
/
