17,082
社区成员
发帖
与我相关
我的任务
分享
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)
CREATE OR REPLACE PROCEDURE 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);
type c_type is ref cursor;
c1 c_type;
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) ||
') FROM ' || table_name || ' WHERE to_date(' ||
to_char(cjsj, 'yyyymmdd') ||
',''yyyymmdd'') 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);
open c1 for sqlstr;
loop
fetch c1
into lzryl, ljkgd, lzqwd, lzqsd;
exit when c1%notfound;
dbms_output.put_line(lzryl);
dbms_output.put_line(ljkgd);
end loop;
close c1;
END;
/
set serverout on;
EXECUTE test('dual',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;
SELECT Nvl(Avg(zryl),0),Nvl(Avg(jkgd),0),Nvl(Avg(zqwd),0),Nvl(Avg(zqsd),0) INTO lzryl,ljkgd,lzqwd,lzqsd FROM testwell_H WHERE cjsj BETWEEN To_Date('2013-12-18 21:00:00','YYYY-MM-DD HH24:MI:SS') AND To_Date('2013-12-18 22:00:00','YYYY-MM-DD HH24:MI:SS')
这个就是字符串连接完成后的SQL语句,我单独执行这条查询语句的时候,报的错是一样的。缺失关键字,但是我不知道为什么,因为把这条语句放进存储过程里执行的时候就不会出错。
select To_Char(Trunc(SYSDATE,'HH24'),'YYYY-MM-DD HH24:MI:SS') into BTime from dual;
select To_Char(Trunc(SYSDATE+1/24,'HH24'),'YYYY-MM-DD HH24:MI:SS') into ETimefrom dual;