日期:2014-05-17 浏览次数:21253 次
CREATE OR REPLACE PROCEDURE proc_test(table_name IN VARCHAR2,cjsj IN DATE,zryl IN NUMBER,jkgd IN NUMBER,zqwd IN NUMBER,zqsd IN NUMBER)AS
lzryl NUMBER(10);
ljkgd NUMBER(10);
lzqwd NUMBER(10);
lzqsd NUMBER(10);
BTime VARCHAR2(20);
ETime VARCHAR2(20);
sqlstr VARCHAR2(1000);
sqlAll VARCHAR2(1000);
BEGIN
BTime:=To_Char(Trunc(SYSDATE,'HH24'),'YYYY-MM-DD HH24:MI:SS');
ETime:=To_Char(Trunc(SYSDATE+1/24,'HH24'),'YYYY-MM-DD HH24:MI:SS');
sqlstr:='SELECT Nvl(Avg(zryl),'||To_Char(zryl)||'),Nvl(Avg(jkgd),'||To_Char(jkgd)||'),Nvl(Avg(zqwd),'||To_Char(zqwd)||'),Nvl(Avg(zqsd),'||To_Char(zqsd)||
') INTO lzryl,ljkgd,lzqwd,lzqsd FROM '||table_name||' WHERE cjsj BETWEEN To_Date('''||BTime||''',''YYYY-MM-DD HH24:MI:SS'') AND To_Date('''||ETime
||''',''YYYY-MM-DD HH24:MI:SS'')';
dbms_output.put_line(sqlstr);
EXECUTE IMMEDIATE sqlstr;
END;
/
EXECUTE proc_test('testwell_H',SYSDATE,0,0,0,0)
--你写的
sqlstr;=select col1,col2,col3 into var1,var2,var3 from table
EXECUTE IMMEDIATE sqlstr;
--修改为
sqlstr;=select col1,col2,col3 from table
EXECUTE IMMEDIATE sqlstr into var1,var2,var3;