create or replace procedure proc_getnewfuhebyfacid(fac_id_s in varchar2,fac_id_e in varchar2,newfuhe_s out varchar2,newfuhe_e out varchar2)
is
tempaclnid varchar2(20);
temp_s_aclndotid varchar2(20);
temp_e_aclndotid varchar2(20);
table_names varchar2(20);
table_columns varchar2(20);
sqls varchar2(1000);--存放动态SQL的语句
begin
select acln_id into tempaclnid from acln_device where fac_id_s = fac_id_s and fac_id_e =fac_id_e and rownum <2;
select acln_dot_id into temp_s_aclndotid from acln_dot where acln_id = tempaclnid and fac_id=fac_id_s;
select acln_dot_id into temp_e_aclndotid from acln_dot where acln_id = tempaclnid and fac_id=fac_id_e;
select history_table_name,history_column_name into table_names,table_columns from svr_yc_sample_define where yc_id like '%'||temp_s_aclndotid||'%' and rownum<2;
sqls:='select '||table_columns||' from (select * from '||table_names||' where to_char(occur_time,''hh24'') = to_char(sysdate,''hh24'') order by occur_time desc) yt
where abs(to_char(sysdate,''mi'')-to_char(occur_time,''mi'')) = (
select min(abs(to_char(sysdate,''mi'')-to_char(occur_time,''mi''))) from '||table_names||')①';--这里就是动态SQL语句，结尾处不要添加 ; (分号) 这个分号的位置不是最后一个分号，是左边 ① 所处的位置
--dbms_output.put_line(sqls);--这个方法是用来打印值的
execute immediate sqls into newfuhe_s; --这里就是运行动态SQL语句的方法，这段代码的意思是执行sqls里存储的SQL语句，并将其中SQL语句查询的值赋予给newfuhe_s这个传出参数 [注意：动态SQL语句不要以 ; (分号)结尾，不然该存储过程会报错]
--newfuhe_s:=sql%rowcount;
--dbms_output.put_line(newfuhe_s);
select history_table_name,history_column_name into table_names,table_columns from svr_yc_sample_define where yc_id like '%'||temp_e_aclndotid||'%' and rownum<2;
sqls:='select '||table_columns||' from (select * from '||table_names||' where to_char(occur_time,''hh24'') = to_char(sysdate,''hh24'') order by occur_time desc) yt
where abs(to_char(sysdate,''mi'')-to_char(occur_time,''mi'')) = (
select min(abs(to_char(sysdate,''mi'')-to_char(occur_time,''mi''))) from '||table_names||')';
--dbms_output.put_line(sqls);
execute immediate sqls into newfuhe_e;
--newfuhe_e:=sql%rowcount;
--dbms_output.put_line(newfuhe_e);
end proc_getnewfuhebyfacid;
| 
转播
