水 顿 智 能
内控资料
MSSqlserver 和 Oracle版本的存储过程（表名作为存储过程的参数） 非常感谢 SD-In Recovery♂ ，广州-cryking，上海-bill gates 这三位好友的帮助！！！
向各位大侠，大牛，高手请教个问题？能不能帮我写一个下面要求的Oracle版本的存储过程！！！ 要求如下：
我有两个表分表为A000001和newestData A000001表为：
create table A000001(ID int,getInfoTime date,WaterHeight numeric(18,2)); 向表中插入数据：
insert into A000001 values(1,to_date('2012-12-31 10:00:00','YYYY-MM-DD HH24:MI:SS'),1); insert into A000001 values(2,to_date('2012-12-31 10:02:00','YYYY-MM-DD HH24:MI:SS'),5);
newestData表为：
创建表的语句：
create table newestData(WellCode varchar(20),getInfoTime date,waterHeight numeric(18,2));
向表中插入数据：
insert into newestData values('A000001',to_date('2012-12-31 10:00:00','YYYY-MM-DD HH24:MI:SS'),9);
--这是数据库的实现思路（执行时，是错误的） create procedure upTabValue @tabName varchar(50), @ID int ,
@curTime datetime AS
declare @preWHeight float declare @curWHeight float declare @time datetime 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
--修改为正确的SQLServer代码为： alter procedure upTabValue @tabName nvarchar(200), @ID varchar(200), @curTime datetime as
declare @curWHeight numeric(18,2) declare @preWHeight numeric(18,2) declare @time datetime
declare @curStrSql Nvarchar(2000) ----NVARCHAR型
第 1 页 共 3 页
水 顿 智 能
内控资料
declare @preStrSql Nvarchar(2000) declare @upStrSql Nvarchar(2000) declare @timeStrSql Nvarchar(2000) declare @curTimeStrSql Nvarchar(2000)
declare @upStrSqlNewestData Nvarchar(2000)
set @curStrSql = N'select @curWHeight=waterHeight from '+@tabName + ' ' + 'where ID= ' + @ID + ' ' exec sp_executesql @curStrSql,
N'@curWHeight int out', @curWHeight out select @curWHeight
set @preStrSql = N'select @preWHeight=waterHeight from '+@tabName + ' ' + 'where ID= ' + @ID +' -1 ' + ' ' exec sp_executesql @preStrSql,
N'@preWHeight int out', @preWHeight out select @preWHeight
set @timeStrSql = N'select @time = getInfoTime from newestData where WellCode = ' +'''' + @tabName + '''' exec sp_executesql @timeStrSql,
N'@time datetime out', @time out select @time if abs(@curWHeight - @preWHeight) > 2 and @curWHeight is not null and @preWHeight is not null BEGIN set @upStrSql = N'update ' +@tabName + ' ' + 'set waterHeight = ' + convert(nvarchar,@preWHeight) + ' ' + 'where ID= ' + @ID + ' ' exec (@upStrSql) if convert(nvarchar,@time,25) = convert(nvarchar,@curTime,25) BEGIN set @upStrSqlNewestData = N'update newestData set waterHeight = ' + convert(nvarchar,@preWHeight) + ' ' +' where WellCode = ' +'''' + @tabName + '''' exec (@upStrSqlNewestData) END END go
Oracle版本的存储过程：
create or replace procedure p_getWaterHeight(p_tabName in varchar2,p_ID in int,p_curTime in date) is
p_curWHeight numeric(18,2); p_preWHeight numeric(18,2); p_time date; BEGIN
execute immediate 'select waterHeight from '||p_tabName||' where ID= '||p_ID into p_curWHeight; execute immediate 'select waterHeight from '||p_tabName||' where ID= '||p_ID||' -1 ' into p_preWHeight; execute immediate 'select getInfoTime from newestData where wellcode='''||p_tabName||'''' into p_time;
第 2 页 共 3 页
水 顿 智 能 dbms_output.put_line(p_curWHeight); dbms_output.put_line(p_preWHeight);
内控资料
if abs(p_curWHeight-p_preWHeight)>2 and p_preWHeight!=1000 and p_curWHeight is not null and p_preWHeight is not null then
execute immediate 'update '||p_tabName||' set waterHeight = '||p_preWHeight||' where ID='||p_ID; if to_char(p_time,'YYYY-MM-DD HH24:MI:SS') = to_char(p_curTime,'YYYY-MM-DD HH24:MI:SS') then execute end if; end if; END;
immediate
'update
newestData
set
waterHeight=
'||p_preWHeight||'
where
wellcode='''||p_tabName||'''';
--执行存储过程
begin
P_GETWATERHEIGHT('A000001',2,to_date('2012-12-31 10:00:00','YYYY-MM-DD HH24:MI:SS')); end;
第 3 页 共 3 页
