CREATE or replace PROCedure P_GET_ITABB_AVG
ITABB in varchar2(20),
TABLENAME in varchar2(50),
STCD in varchar2(50),
StartTime in datetime,
EndTime in datetime,
Return out float output
--WITH ENCRYPTION 加密
as
begin
declare sAVG nvarchar2(4000);
declare WQGRD float;
declare Count number;
declare CountString nvarchar2(4000);
begin
sAVG:=decode(ITABB,'TPHK','select WQGRD = avg(TP) from WQ_NM_D where STCD='''||STCD||''' and GETM>='''||to_char(StartTime,'yyyy-mm-dd')||''' and GETM<='''||to_char(EndTime,'yyyy-mm-dd')||''' ' ,
'WT',' select WQGRD= NULL ' ,
' select WQGRD= avg('||ITABB||') from '||TABLENAME||' where STCD='''||STCD||''' and GETM>='''||to_char(StartTime,'yyyy-mm-dd')||''' and GETM<='''||to_char(EndTime,'yyyy-mm-dd')||''' ');
execute IMMEDIATE sAVG,'WQGRD as float out',Return out --不知道后面的参数是做什么的
Return:=round(Return,6);
end;end;
ITABB in varchar2(20),
TABLENAME in varchar2(50),
STCD in varchar2(50),
StartTime in datetime,
EndTime in datetime,
Return out float output
--WITH ENCRYPTION 加密
as
begin
declare sAVG nvarchar2(4000);
declare WQGRD float;
declare Count number;
declare CountString nvarchar2(4000);
begin
sAVG:=decode(ITABB,'TPHK','select WQGRD = avg(TP) from WQ_NM_D where STCD='''||STCD||''' and GETM>='''||to_char(StartTime,'yyyy-mm-dd')||''' and GETM<='''||to_char(EndTime,'yyyy-mm-dd')||''' ' ,
'WT',' select WQGRD= NULL ' ,
' select WQGRD= avg('||ITABB||') from '||TABLENAME||' where STCD='''||STCD||''' and GETM>='''||to_char(StartTime,'yyyy-mm-dd')||''' and GETM<='''||to_char(EndTime,'yyyy-mm-dd')||''' ');
execute IMMEDIATE sAVG,'WQGRD as float out',Return out --不知道后面的参数是做什么的
Return:=round(Return,6);
end;end;
ITABB in varchar2(20),
TABLENAME in varchar2(50),
STCD in varchar2(50),
StartTime in datetime,
EndTime in datetime,
Return out float output
--WITH ENCRYPTION 加密
as
begin
declare sAVG nvarchar2(4000);
declare WQGRD float;
declare Count number;
declare CountString nvarchar2(4000); --不是每次定义都要声明,声明一
次就够了
begin
sAVG:=decode(ITABB,'TPHK','select WQGRD = avg(TP) from WQ_NM_D where STCD='''||STCD||''' and GETM>='''||to_char(StartTime,'yyyy-mm-dd')||''' and GETM<='''||to_char(EndTime,'yyyy-mm-dd')||''' ' ,
'WT',' select WQGRD= NULL ' ,
' select WQGRD= avg('||ITABB||') from '||TABLENAME||' where STCD='''||STCD||''' and GETM>='''||to_char(StartTime,'yyyy-mm-dd')||''' and GETM<='''||to_char(EndTime,'yyyy-mm-dd')||''' ');
execute IMMEDIATE sAVG,'WQGRD as float out',Return out --不知道后面的参数是做什么的 --如果是字符串,从一开头就得带 ''
Return:=round(Return,6); --"return" 不是变量。
end;end; --加函数名
老兄,你看清楚我的回帖好不好!!我楼上的回帖你能运行?
oracle里还有float??懒得说你了,看见写得多的给分就多,对人对己都不负责任。